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.