The University of Texas

CS 327e

Spring 2005

Lab 3

Due: 2005-02-25 Friday 8.30 a.m. in class. For this Lab, no late assignments will be accepted.

 

PURPOSE:

The purpose of this lab is to learn about Normalization and good database design.

 

IMPLEMENTATION:

 

For this lab, consider solving ALL of the following:

Consider a sample SALES ORDER data. Specifically, a typical order looks like this

 

 

 

Typical Sales Order Receipt

 

 

 

 

 

 

 

 

 

 

 

Chamber of Secrets, Inc.

 

 

 

 

 

2002, 57th Street,

 

 

 

 

 

Hogwarts, UK EXP934

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Order #

37

 

 

Customer #

2002

Order Date

2/20/2002

 

 

Customer Name

Lord Voldemort

Cashier #

934

 

 

Customer Address

1 Bar, Diagon Alley,

Cashier Name

Harry Potter

 

 

 

Azkaban, UK MEANME

 

 

Method of Payment

VISA

 

 

 

 

 

 

 

 

Item #

Item Author

Item Desc

Quantity

Unit Price

Total

100

Ron Weasley

Follow the Butterflies

10

9.75

97.50

101

Draco Malfoy

My father can afford the best

20

44.99

899.80

102

Tom Riddle

A songbird and an old hat

20

56.34

1,126.80

103

Professor Snapes

The circumstances are suspicious

30

123.23

3,696.90

104

Hermione

Look at my face

34

28.56

971.04

105

Gilderoy Lockhart

Order of Merlin

43

56.28

2,420.04

 

 

 

 

 

 

 

 

 

 

Grand Total

9,212.08

 

P.S. For those of you who are Harry Potter fans, you may recognize the Item descriptions to be the things the authors spoke in the movie “Harry Potter and The Chamber of Secrets”. This data was obtained from http://www.imdb.com/title/tt0295297/quotes

 

Your job is to break down this receipt into a set of normalized relations in 3rd Normal Form.

 

You can assume the following:

1)      The following attributes are identified from the receipt, that need to be stored in the database:

 

OrderNum

OrderDate

CashierNum

CashierName

CustomerNum

CustomerName

CustomerAddress

MethodOfPayment

ItemAuthor

ItemDesc

Quantity

UnitPrice

ItemNum

 

2)      The totals, as well as the store name and address (or URL), need not be stored in the database.

3)      The price of a given item could vary from order to order.

4)      A Customer may choose to use different method of payments for different orders.

5)      An Order must always have a Customer associated with it.

6)      An Order may or may not have a Cashier associated with it.

7)      An Order must contain at least 1 item.

8)      If there are more than 1 items purchased in an order, which have the same item number, they will be reflected in the quantity, and not as additional rows.

9)      The Quantity is always a non-zero value.

10)  The possible choices for “Method of Payment” are VISA, MASTERCARD, DISCOVER, AMERICANEXPRESS, and CASH. For credit cards, we are not tracking the credit card number, or its expiry date.

11)  Every Item has an Author and a Description.

12)  Assume that this table is broken down into 2 relations in order to be in 1st NF. In other words, the repeating group of items are eliminated as follows:

 

CUSTOMER_ORDER (OrderNum, OrderDate, CashierNum, CashierName, CustomerNum, CustomerName, CustomerAddress, MethodOfPayment)

 

ORDER_ITEM (OrderNum, ItemAuthor, ItemDesc, Quantity, UnitPrice, ItemNum)

 

A)    For each of these 2 relations, identify all functional dependencies (FD’s). Clearly state which NF are satisfied/not satisfied and why.

B)    Break these relations down into relation(s) that are in 2nd Normal Form (NF). Clearly state the FD’s and primary key for each of these relations. Clearly state which NF are satisfied/not satisfied and why.

C)    Break the above relation(s) into 3rd NF. Clearly state the FD’s and primary key for each of these relations. Clearly state which NF are satisfied/not satisfied and why.

D)    For the relations in 3rdNF above in C), draw the IDEF1X ER diagram. Clearly label all PK, FK, entity names, relationship names, attribute names, cardinalities, NULL/NOT NULL property on the attributes, Domains, and the referential integrity actions (e.g. delete cascade etc.).

 

In all of the above, CLEARLY STATE ANY ADDITIONAL ASSUMPTIONS THAT YOU MIGHT HAVE MADE.

 

 

 

SUBMISSION:

Please submit a paper copy in class by the Due Date/Time.

Make sure you have written the following on your paper submission:

·         First Name, Last Name

·         Course Name

·         Lab #

·         EID (optional)

MAKE SURE YOU STAPLE THE PAGES BEFORE SUBMISSION.

 

Consult the class web page for

-          Late submission policy

-          Academic Dishonesty Policy

 

 

 

Last Updated: 2/22/2005 5:01:35 PM