The University of Texas

CS 327e

Spring 2005

Lab 3 Solution

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 3^rd 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 1^st 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 2^nd 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 3^rd 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 3^rdNF 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.

SOLUTION:

A)

R1:

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

R1FD1: OrderNum à OrderDate, CashierNum, CashierName, CustomerNum, CustomerName, CustomerAddress, MethodOfPayment

R1FD2: CashierNum à CashierName

R1FD3: CustomerNum à CustomerName, CustomerAddress

R2:

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

R2FD1: OrderNum, ItemNum à ItemAuthor, ItemDesc, Quantity, UnitPrice

R2FD2: ItemNum à ItemAuthor, ItemDesc

Relation	FD	Normal Form	Reason(s)
		2^nd
R1	R1FD1	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R1	R1FD2	Yes	No partial dependency, hence in 2^nd NF.
R1	R1FD3	Yes	No partial dependency, hence in 2^nd NF.
R2	R2FD1	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R2	R2FD2	No	Partial dependency, hence violates 2^nd NF.

Looking at the table above, R1 is in 2^nd NF, but not R2.

B)

Hence, the following relations do not have any partial dependencies, and are in 2^nd NF.

R1:

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

R1FD1: OrderNum à OrderDate, CashierNum, CashierName, CustomerNum, CustomerName, CustomerAddress, MethodOfPayment

R1FD2: CashierNum à CashierName

R1FD3: CustomerNum à CustomerName, CustomerAddress

R21:

ORDER_ITEM (OrderNum, Quantity, UnitPrice, ItemNum)

R21FD1: OrderNum, ItemNum à Quantity, UnitPrice

R22:

ITEM (ItemAuthor, ItemDesc, ItemNum)

R22FD1: ItemNum à ItemAuthor, ItemDesc

Relation	FD	Normal Form		Reason(s)
		2^nd	3rd
R1	R1FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R1	R1FD2	Yes	No	No partial dependency, hence in 2^nd NF. Transient dependency hence violates 3^rd NF.
R1	R1FD3	Yes	No	No partial dependency, hence in 2^nd NF. Transient dependency hence violates 3^rd NF.
R21	R21FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R22	R22FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.

Looking at the table above, R21 and R22 are in 3^rd NF, but R1 is not.

C)

Hence, the following relations do not have any partial nor transitive dependencies, and are in 3^rd NF.

R11:

CUSTOMER_ORDER (OrderNum, OrderDate, CashierNum, CustomerNum, MethodOfPayment)

R11FD1: OrderNum à OrderDate, CashierNum, CustomerNum, MethodOfPayment

R12:

CASHIER (CashierNum, CashierName)

R12FD1: CashierNum à CashierName

R13:

CUSTOMER (CustomerNum, CustomerName, CustomerAddress)

R13FD1: CustomerNum à CustomerName, CustomerAddress

R21:

ORDER_ITEM (OrderNum, Quantity, UnitPrice, ItemNum)

R21FD1: OrderNum, ItemNum à Quantity, UnitPrice

R22:

ITEM (ItemAuthor, ItemDesc, ItemNum)

R22FD1: ItemNum à ItemAuthor, ItemDesc

Relation	FD	Normal Form		Reason(s)
		2^nd	3rd
R11	R11FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R12	R12FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R13	R13FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R21	R21FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.
R22	R22FD1	Yes	Yes	All non-key attributes are dependent on the key, the whole key, and nothing but the key.

Looking at the table above, all relations are in 3^rd NF w.r.t. their FDs, since there are no partial or transitive dependencies.

D)

NOTE: Change the data type for CUSTOMER_ORDER.MethodOfPayment to belong to a user-defined data type (domain) of PAYMENTTYPE, where

PAYMENTTYPE = { VISA, MASTERCARD, DISCOVER, AMERICANEXPRESS, CASH }

Last Updated: 2/27/2005 11:15:53 AM