Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 1 of 25

Data Admin Concepts & Database Management

Table of Contents Data Admin Concepts & Database Management …………………………………………………………………………….. 1

Lab 05 – Physical Design and DDL ………………………………………………………………………………………………. 1

Overview …………………………………………………………………………………………………………………………….. 1

Learning Objectives …………………………………………………………………………………………………………… 2

Lab Goals …………………………………………………………………………………………………………………………. 2

What You Will Need to Begin ……………………………………………………………………………………………… 2

Part 1 – Creating Tables ………………………………………………………………………………………………………… 2

Setup ………………………………………………………………………………………………………………………………. 2

Formatting Note ……………………………………………………………………………………………………………….. 2

SQL Server Management Studio …………………………………………………………………………………………. 3

Your First Table ………………………………………………………………………………………………………………. 10

Another Table with a Foreign Key ……………………………………………………………………………………… 17

Add Some Data ………………………………………………………………………………………………………………. 19

Creating the Follower List Table ……………………………………………………………………………………….. 20

Part 2 – The Rest of the Tables ……………………………………………………………………………………………… 22

What to Submit ………………………………………………………………………………………………………………….. 24

Appendix A – VidCast Logical Model Diagram ………………………………………………………………………… 25

Lab 05 – Physical Design and DDL

Overview This lab is the fifth of ten labs in which we will build a database using the systematic approach covered in the asynchronous material. Each successive lab will build upon the one before and can be a useful guide for building your own database projects.

In this lab, we will use a diagram of properly normalized tables to create a set of tables in SQL Server.

Read this lab document once through before beginning.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 2 of 25

Learning Objectives In this lab you will

• Demonstrate data definition language (DDL) proficiency • Demonstrate ability to convert from diagrams to SQL code

Lab Goals This lab consists of two sections. The first section is a walkthrough of creating tables, columns, and constraints. In the second part, you will use similar code to build the rest of the database.

In both parts, you will code a few data manipulation (DML) statements to see your tables in action.

What You Will Need to Begin • This document • An active Internet connection (if using iSchool Remote lab) • A blank Word (or similar) document into which you can place your answers. Please include your

name, the current date, and the lab number on this document. Please also number your responses, indicating which part and question of the lab to which the answer pertains. Word docx format is preferred. If using another word processing application, please convert the document to pdf before submitting your work to ensure your instructor can open the file.

• To have completed Lab 04 – Normalization • Understanding of database tables • One of the following means of accessing a SQL Server installation

o A connection to the iSchool Remote Lab ( https://rds.syr.edu/ ) o A local installation of SQL Server (see Developer edition here

https://www.microsoft.com/en-us/sql-server/sql-server-downloads-free-trial) o Regardless of how you access SQL Server, you will need to use SQL Server Management

Studio to do so.

Part 1 – Creating Tables Setup After a thrilling round of design work, we have settled on a logical model design for the VidCast service. The current diagram is shown at the end of this document.

We will use this document along with some other narrative elements to build our database.

Formatting Note Look for the “To Do” icon to point out sections of the lab you will need to do to complete the tasks.

https://rds.syr.edu/
https://www.microsoft.com/en-us/sql-server/sql-server-downloads-free-trial
Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 3 of 25

SQL Server Management Studio Most Relational Database Management Systems (RDBMS) are not software that we can directly interact with. Instead, they are services that run on a computer, server or otherwise, that manage access to the database and are responsible for handling the internal management of database objects. They are “headless” in that there isn’t a user interface for them.

Instead, we use other applications to communicate with these services even if they are running on the same computer we’re using to connect to them. In the case of SQL Server, we can use SQL Server Management Studio (SSMS) to do most of our work.

If you’re running your own copy of SQL Server, you will have to ensure SSMS is installed (see links in “What You Will Need to Begin” above). If you are using the remote lab, this software is already installed on the remote computer you are accessing.

Screenshots used in this lab assume you are using the remote lab and are for the version of SSMS installed as of the last modified date of this document, but any differences in versions are mostly cosmetic.

Open SSMS by clicking the Window icon (formerly Start Button) in the lower left corner of the desktop and begin typing SQL Server Management Studio.

Click the SSMS icon that appears when typing. Note: this may appear before you’ve completed typing the name of the software and may have another version number than what is shown below.

Once SSMS loads, you will have to tell SSMS how to connect to the server. You will be prompted with this dialog:

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 4 of 25

Let’s unpack what SSMS is asking for here.

Server Type Make sure Database Engine is selected for Server Type. The SQL Server ecosystem has many services that do different things related to data. All but Database Engine are outside the scope for this class.

Server Name The value to enter here is, aptly, the name of the server. This name is a domain name services (DNS) name of the server on your network. It can also be an IP address if no name has been set for the computer on which the server is running.

If you are using the remote lab, the correct value for this is DBLab.ischool.syr.edu

If you are running the server on your own computer, you can simply enter . here. (That is a “dot” “period” or “full stop”) “.” (without the quotes) is a shorthand for saying “this computer”. You might also have seen this as localhost or the IP address 127.0.0.1. All of these are considered “loopback” names in that they tell the connection to use the current computer as the endpoint.

Authentication Because SQL Server is managing access to our data and ensuring that only the right connections get through, we must authenticate with the server when we connect. In a sense, we are telling the server who we are.

For now, set this to Windows Authentication (regardless of whether you’re using remote lab or your own hardware). Note that the user name and password are greyed out (cannot be edited) and that SSMS has set User Name to the domainuserid you used when logging into the local computer.

Subsequent Connections SSMS should remember your settings for the next time you login, so you shouldn’t have to reset all these values every time you connect, but it may forget what you used before, so keep these settings handy for future connections.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 5 of 25

Click Connect

SSMS connects to the SQL Server and presents us with the main interface, ready to go!

There are a few elements we’ll need to master to get our work done.

Menu Bar and Toolbar

We’ll discuss each of the menus and toolbar items as we need them, but if you’re unfamiliar with SSMS, take a moment to familiarize yourself with where the menu commands are and which toolbar buttons are available to you. Of interest to us are the New Query button, the save button, and the open File button.

Hover your mouse pointer over the buttons for tooltips that tell us what each button is.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 6 of 25

Object Explorer

The Object Explorer is where we can see all the objects on our server and in our database. This is a hierarchical tree view of objects. At its root is the server to which you’re connected and as what user you’ve authenticated.

There are several items below the server node, but the only one we’ll be working with this term is Databases. Click the + sign to expand Databases.

If you’re using the Remote Lab, you will see a long list of databases. They are listed alphabetically and, fortunately, they are named based on a pattern. Your database will follow the pattern, IST659_M999_netid where M999 is the IST659 section you’re registered for. For example, caharper registered for IST659, section M400. This is their database:

If you’re running your own server, you will not see any databases here except system databases. It’s best to leave these alone and instead create a new database. To do so, right click on your Databases folder in Object Explorer and click New Database. Give your database a fitting name, perhaps IST659_Labs, and click okay. As a rule, try to avoid spaces where possible.

Expand your database and take note of the folders contained within. SSMS groups the objects of your database by their type.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 7 of 25

Expand the Tables node. If you haven’t yet created any tables, your tree structure should look like this:

The rest of this lab is going to deal exclusively with the Tables in your database. We just have to create some.

In the toolbar, click the button. This opens a new blank query window.

Take note of a couple elements that are now available. Particularly, there is a new toolbar. Also, this document has a tab at the top that lists the file name, the current server, the current database, and the currently authenticated user. This is a lot of data for one tab, so you may have to hover the mouse pointer over the tab to see all the information.

The Editing Toolbar

One of the new elements to appear after we open any query file, including a new one, is the editing toolbar. There are some elements here we need to note for later use.

Available Databases

This is a drop-down list of all the available databases on this server. This is important to note because whatever you type in your query editor window and, subsequently, execute will affect the currently selected database specifically. If you are on the remote lab, this should default to your database. If you are on your own server, the default is the system database, master, a database we do not want to mess with.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 8 of 25

In either case, at this point, ensure the correct database is selected. If necessary, change to the correct database by clicking the drop-down and scrolling to the correct database. You can also type the name of the correct database here and it will change accordingly.

Execute and Parse

Just typing a query in the query editor window doesn’t automatically execute the query against the database. The file you’re viewing on the screen is merely a text file with a .sql extension, so SSMS knows how to set the font colors. Instead, we must type our SQL commands and click the Execute button to send those commands to the server.

If you have written particularly complex SQL code and would like to verify that it works before executing

it against the database, you can click the parse button to validate your code. You do not need to do this every time, but if you’re just starting out, it can be a good way to ensure your code is syntactically correct before executing it.

Try It In the blank query editor window, type the following line of SQL code:

Click the parse button to validate your code.

The Results pane appears and tells us this code is good to go.

If you do not get this message, double check that you have entered the code exactly as above.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 9 of 25

Click the Execute button in the toolbar to execute your code. A grid view results pane appears.

We will unpack this view later. For now, if you have never coded in SQL before, you just ran your first SQL query!

Line Numbers When you first use SSMS, new query windows will not show any line numbers. While you can still see the line number in the lower right portion of the window:

it is still helpful to have the line numbers next to the code in the editor window for many reasons. It will be helpful when screen sharing with a colleague or your section instructor. Also, when SQL reports an error in the code, it will tell you which line has the problem. It may be helpful to be able to see at a quick glance what that code is.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 10 of 25

To add line numbers to your query editor window, click the Tools menu, then Options.

Expand the Text Editor group and click on All Languages. On the right-hand side of the dialog, check the Line Numbers box below Display. Click OK to confirm.

Note: You may have to reset this next time you log in.

Your First Table

Delete any text you have in your query editor window. Also make sure the correct database is selected in Available databases.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 11 of 25

Comments A Comment is a line or lines of code that are written to let the reader of the code know what the code is intended to do. They are an important part of any programming exercise to help demystify the code for readers, identify the authors and their intent, and can help explain decisions made by the author.

Because comments are meant to be read by humans and not by a computer, they are not processed as part of the code when executing. Comments are also used on actual lines of code to prevent those lines from executing. This is helpful in debugging sections of code and for temporarily removing code from the execution plan.

It’s important to get into the habit of commenting code whenever possible, so let’s start on the right foot. The correct foot, as it were.

In SQL Server, anything after a double-hyphen, regardless of where it is on the line, is treated as a comment. SSMS will format commented text in a light green color.

You can also create a multi-line comment, usually several lines of code, by starting your comment block with /* and ending with */ and everything between those characters will be treated as comment text.

Add a comment block containing your name as author, Lab 05 as the title of the script, IST659 and your section number as the course number, and the current quarter term. Your code should like the following:

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 12 of 25

Save Your Work

Save your file by clicking the Save icon in the toolbar, selecting Save from the File menu, or pressing Ctrl+S on your keyboard. Keep in mind that a SQL file is just a text file with a .sql extension, so we can save it wherever we like. If you’re unsure where is best to save your file, the My Documents folder is a good starting point.

Save early, save often, especially if using the remote lab. There may be times when the remote lab ends your session, either because of a network error or from being logged out after an extended period of inactivity. If this happens, it is very likely you will lose any unsaved work. It’s a good idea to get into the habit of saving as often as possible.

Formatting and Code Conventions When we write code, we’re not only writing it to be executed by a computer program like SQL Server, we’re also writing it to be readable. Whether it is for you to diagnose an issue with your own code, or for someone else to try to make sense of what we’ve written after the fact, your code should be written for understanding by humans as well as computers. For this reason, it is important to devise and stick to a convention for formatting your code.

For example, SQL Server does not care overmuch for the format you’ve chosen for your code but consider the following two code blocks.

Versus

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 13 of 25

The first and second examples are both syntactically correct and both will achieve the same goal of creating a table called MyNewTable. The second example, however, makes use of white space in the form of new lines and tabs to indent internal code to help the code make visual sense.

By taking four lines of text for a single SQL statement, we have made the task of working with the code after the fact that much easier.

Lines vs Statements Part of understanding how to best format SQL code in the file is understanding the difference between a line in the file and a full SQL statement.

A line, simply put, starts at the beginning of a line of text and ends when a new line is reached (you create a new line by pressing the Enter key).

A statement, on the other hand, is the full text of a single SQL command. As we’ve seen in the preceding two examples, a statement is made up of several elements. When a statement is sent to SQL Server for execution (by way of clicking the Execute button in the toolbar), it will interpret the statement from its start, (mostly) ignoring hidden whitespace such as tabs and new lines, and proceeds until it has a complete statement with which to work. It then does the thing it was asked to do.

In basic terms, a SQL statement is made up of a few parts, all common to whatever the statement is trying to achieve.

We start with some verb that tells the RDBMS what we’re about to do (CREATE, DROP, DELETE, SELECT, and UPDATE, to name a few). In many cases, particularly in Data Definition Language (DDL) statements, this is followed by some object type (TABLE, PROCEDURE, VIEW, FUNCTION, etc.). For DDL commands, the object type is followed by the name of the object. The balance of the statement is made up of the specific code SQL Server needs to complete the command. The Code for the User Table Let’s have another look at our previous example.

Line 3 starts with the verb, CREATE. This tells SQL Server we’re going to create something new. This is followed by TABLE. This tells SQL Server we are creating a table. The name of that table is MyNewTable.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 14 of 25

The CREATE TABLE statement requires a comma-separated list of columns and constraints that comprise the table structure. These columns and constraints are contained within parentheses. Let’s unpack this statement and create our first table.

To start, we’re going to create the User table from our VidCast database. Let’s have another look at the User table from the diagram:

We have most of the information we need to create this table in this shape. The only thing not shown here is that we want to default the UserRegisteredDate column to whatever the current date is at the time a row is entered. We could force the person adding the record to provide it, but to be user-friendly, let’s use a SQL default that tells the database what to use if no UserRegisteredDate is provided.

If we decompose this diagram into the columns and constraints we’ll need to provide in our SQL CREATE TABLE statement, we can see the different components in the shape and how they might be coded.

vc_User Columns Column Name Data Type Properties vc_UserID int identity UserName varchar(20) not null EmailAddress varchar(50) not null UserDescription varchar(200) WebSiteURL varchar(50) UserRegisteredDate datetime not null default GetDate()

vc_User Constraints

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 15 of 25

Constraint Name Constraint Type Properties PK_vc_User PRIMARY KEY vc_UserID U1_vc_User UNIQUE UserName U2_vc_User UNIQUE EmailAddress

You don’t need to build these grids for every table you create. These tables are presented here to illustrate how the diagram shapes translate to the SQL CREATE TABLE code.

In your query editor window, type the following code:

TIP: Be mindful of the commas at the ends of lines 1-15 and 17-18 above. Also note that there is not a comma at the end of line 19 as it is the last item in this comma-separated list.

At this point, we can probably execute all our code without a problem, but let’s get into the habit of being selective about what we execute. Select the lines of code from your first comment through the last comment:

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 16 of 25

Now, click in the toolbar. SSMS will send your code to the SQL Server for execution.

It should take a fraction of a second for your code to execute and return the success message.

If you get any message other than this, double check that you have typed the code exactly as written in the sample image above. If you need to make any edits, simply do so and re-execute the code.

We’ve created a table! There’s nothing in it yet, but now it is ready for us to add data. Have a look at your Object Explorer to see your ta… wait…

Because the Object Explorer is built based on a snapshot of the database objects, it may not yet show your table. If it doesn’t, right-click the Tables folder and click Refresh

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 17 of 25

Much better. To see the columns and other properties we’ve defined for the vc_User table, expand the vc_User table, then expand Columns, then expand Keys. Your Object Explorer should look like this:

Take a screenshot of your Object Explorer with your table and its columns and keys expanded. Paste this screenshot into your answer doc labeled as “User Table”

Another Table with a Foreign Key Let’s code the UserLogin table. This table will track when a user logs in and keep track of what IP they connected from. We’ve isolated the table from the diagram here:

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 18 of 25

To aid in coding this as a table, we can look at it in grid format with the column properties translated. Let’s also default the timestamp to the current date and time.

vc_UserLogin Columns Column Name Data Type Properties vc_UserLoginID int identity vc_UserID Int not null UserLoginTimestamp datetime not null default GetDate() LoginLocation varchar(50) not null

vc_UserLogin Constraints Constraint Name Constraint Type Properties PK_vc_UserLogin PRIMARY KEY vc_UserLoginID FK1_vc_UserLogin FOREIGN KEY vc_UserID REFERENCES vc_User(vc_UserID)

Add the following code to your query editor window. Remember to pay attention to the parentheses and commas in this code. SQL is quite particular about those things. Highlight and execute only this code.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 19 of 25

Take a screenshot of your Object Explorer with your vc_UserLogin table and its columns and keys expanded. Paste this screenshot into your answer doc labeled as “User Login Table”

Add Some Data Before we proceed, let’s see our tables in action.

Copy and paste the following code into your query editor window (or type it out – that makes learning code easier!). Then highlight this code (and only this code!) and execute it.

— Adding Data to the User table INSERT INTO vc_User(UserName, EmailAddress, UserDescription) VALUES (‘RDwight’, ‘[email protected]’, ‘Piano Teacher’), (‘SaulHudson’, ‘[email protected]’, ‘I like Les Paul guitars’), (‘Gordon’, ‘[email protected]’, ‘Former cop’) SELECT * FROM vc_User

TIP: Again, be mindful of the quotation marks, commas, and parentheses in these statements. They are important!

TIP: After you have executed the INSERT statement, you will get an error message if you try to execute it again. This is by design. Once you run it, the data are there permanently, and we have

guarded against duplication using our UNIQUE constraints.

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 20 of 25

The first four lines are a single INSERT statement. When that statement is executed, it adds three rows to the database in the vc_User table. The last line is a SELECT statement that queries that table.

Upon completion of the execution, you should see a results grid like this:

“But, wait, we didn’t specify a vc_UserID or UserRegisteredDate value. Where did they come from?” The identity property we added to vc_UserID told SQL Server to provide the next available value for us. When the first row went in, 1 was the next value. Then 2. Then… you get it. Because we specified a default of GetDate() on the UserRegisteredDate, but didn’t include one in the INSERT statement, SQL Server ran the internal GetDate() function (more on that in a later lab) which returned the current date and time (down to the microsecond, actually).

Take a screenshot of your results grid and paste it into your answer document. Label it “User records”

Creating the Follower List Table When a user follows another user, we need to add a record to this table. Pay special attention to how the UNIQUE constraint differs from what we’ve done before. In previous statements, we have only put one column name in the constraint. Because we want to ensure that the combination of values in two different columns must be unique, we add them both, separated by a comma, to the constraint.

Also, this is the first time we have used a column name in a foreign key that differs from the referenced primary key in the other table. This is quite common. There is no hard and fast rule that says a foreign key column need be named the same as the referenced primary key column. In fact, it is more common to name them differently. When you do so, however, ensure that the meaning of the column name reflects the data you intend to keep. In our case, we have renamed our foreign key columns to indicate their semantic meaning.

FollowerID is the vc_UserID of the vc_User doing the following.

FollowedID is the vc_UserID of the vc_User being followed.

As before, we will default the datetime to GetDate().

Lab 05 – Physical Design and DDL Last Modified September 29, 2020

Page 21 of 25

Type and execute the following code in your query editor

TIP: Note line 54, above. This is how we create a composite unique constraint. Because both FollowerID and FollowedID are marked as U1. This means that they are part of the same Unique constraint. When you see the same constraint specification for more than one column on a table, you define them like this.

Your line numbers might be slightly different. That’s okay.

In the Object Explorer, refresh your Tables folder and take a screenshot of this portion of the screen. Paste it into your answers document and label it “User …

Read more
Applied Sciences
Architecture and Design
Biology
Business & Finance
Chemistry
Computer Science
Geography
Geology
Education
Engineering
English
Environmental science
Spanish
Government
History
Human Resource Management
Information Systems
Law
Literature
Mathematics
Nursing
Physics
Political Science
Psychology
Reading
Science
Social Science
Home
Homework Answers
Blog
Archive
Tags
Reviews
Contact
google+twitterfacebook
Copyright © 2021 SweetStudy.com