IT360 Lab 2:  Relational Model

DUE: February 3, 2009, BEFORE start of lab

In this lab you will develop your data modeling skills by translating Entity-Relationship diagrams into relational model specifications, creating the tables in SQL Server, using SQL, and normalizing tables to be in Boyce-Codd Normal Form.

Using SQL Server:

Follow the tutorial on the website to connect to the SQL Server.

To write SQL queries in SQL Server:

1. Open the database created for you.
2. From the toolbar, select New Query.
3. Write the SQL statements in the window opened. You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /* multi-line comment here */.
4. Click the checkmark button in the toolbar to check the syntax of the SQL statement.
5. Click the  in the toolbar to execute all SQL statements in the file. If you want to execute only one statement, highlight it (select it with the mouse) and then click on !.

To save SQL queries: From the menu ΰ Fileΰ Save As...

SQL Server Data Types

These are the data types supported by SQL Server. Remember that you should use the most specific data type, when creating your tables.

 Data Types Description bigint Integer data from -2^63 through 2^63-1 int Integer data from -2^31 through 2^31 - 1 smallint Integer data from -2^15 through 2^15 - 1 tinyint Integer data from 0 through 255 bit Integer data with either a 1 or 0 value decimal Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 numeric Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 money Monetary data values from -2^63 through 2^63 - 1 smallmoney Monetary data values from -214,748.3648 through +214,748.3647 float Floating precision number data from -1.79E + 308 through 1.79E + 308 real Floating precision number data from -3.40E + 38 through 3.40E + 38 datetime Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds smalldatetime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute char Fixed-length character data with a maximum length of 8,000 characters varchar Variable-length data with a maximum of 8,000 characters text Variable-length data with a maximum length of 2^31 - 1 characters nchar Fixed-length Unicode data with a maximum length of 4,000 characters nvarchar Variable-length Unicode data with a maximum length of 4,000 characters ntext Variable-length Unicode data with a maximum length of 2^30 - 1 characters binary Fixed-length binary data with a maximum length of 8,000 bytes varbinary Variable-length binary data with a maximum length of 8,000 bytes

Exercise 1: ER to relational

Transform the following Entity-Relationship model (from lab 1) into a relational model. Write the SQL statements to create each of the resulting tables, and execute them in SQL Server, to actually create the tables. Make sure that all columns have the appropriate data type, null status, default values (if appropriate), primary key, and foreign keys constraints are defined, including suitable referential integrity actions for delete and update. Document each requirement from the ER model that cannot be captured by SQL (such as minimum cardinality of 1 for child entity).

The Commodore of the Yard Patrol Squadron volunteered you to create a database to help manage squadron information.  The following ER diagram models the YP operations data. Transform the ER model into a relational model.

Exercise 2: 1st Normal Form (1NF)

Consider the Students table, with the primary key underlined, and the following data:

Students:

 Alpha Name Email Courses GradePoints 100111 John Doe doe@usna.edu NN204, SI204, IT221 2,3,3 092244 Matt Smith smith@usna.edu SM223, EE301 4,4 113221 Melinda Black black@usna.edu SI204 3 090112 Tom Johnson Johnson@usna.edu NN204, SI204, IT221 4,2,3

a)      Is the Students table in 1NF? Why?

b)      If the table is not in 1NF, redesign the Students table to be in 1NF. Give the table name, column names, primary keys and foreign keys for each of the resulting tables.

Exercise 3: Boyce-Codd Normal Form (BCNF)

For a table to be in Boyce-Codd normal form, the determinants of all the functional dependencies in that table must be candidate keys (either primary key or alternate key).   Below are two tables created for a video store.

Customers:

 CustID CustName CustCity CustState CustZip 1 John Doe Pittsburgh PA 15136 2 Matt Smith Columbia SC 29210 3 Melinda Black Stevensville MD 21666 4 Tom Johnson Annapolis MD 21210

Rentals:

 RentalID Title CustID CheckOutDate Director Category Price 1 Die Hard 1 3/3/2006 John McTiernan Old \$4.25 1 The last man standing 1 3/3/2006 Walter Hill Old \$4.25 1 Wedding Crashers 1 3/3/2006 David Dobkin New \$5.50 2 Dodgeball 2 3/4/2007 Rawson Marshall Thurber New \$5.50 2 Die Hard 2 3/4/2007 John McTiernan Old \$4.25 3 As good as it gets 3 6/7/2008 James Brooks Old \$4.25 4 Forest Gump 1 6/7/2008 Robert Zemeckis Old \$4.25

The primary key of the Customers table is CustID. The primary key of the Rentals table is the composite key (RentalID, Title).

a)      Determine the functional dependencies for Rentals table, based on the data in the table, and your basic knowledge of video rentals.

b)      The Rentals table in not in BCNF. Why not?

c)      We discussed insertion anomalies, deletion anomalies and modification anomalies as examples of problems that can appear in tables that are not normalized. For example, if we want to create a new category of videos, Must See, there is no way to store the price of this type of videos in the database, until someone rents a video in this category, and the rental information is recorded into the Rentals table. Give one example of a deletion anomaly in the Rentals table.

d)      Decompose the Rentals table into BCNF. Give the table name, column names, primary keys and foreign keys for each of the resulting tables.

Exercise 4 (Extra Credit):

Transform the following Entity-Relationship model into a relational model. Write the SQL statements to create each of the resulting tables, and run them in SQL Server, to actually create the tables.

Turn in BOTH electronic and paper copies (due before start of lab on February 3, 2009):

Electronic:

Upload a file with all the SQL statements to create the tables for exercise 1, and all answers for exercises 2 and 3 to Lab 2 assignment on Blackboard. The file should be named yourlastname_lab2.doc.

Hard-copies:

1. The completed assignment coversheet. Your comments will help us improve the course.
2. Hard copy of answers for all exercises, including the SQL statements to create the tables in exercise 1.