Lab 3 Solution

# 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 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.

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)
2nd
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 2nd NF.
R1 | R1FD3 | Yes | No partial dependency, hence in 2nd 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 2nd NF.

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

B)

Hence, the following relations do not have any partial dependencies, and are in 2nd 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)
2nd | 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 2nd NF. Transient dependency hence violates 3rd NF.
R1 | R1FD3 | Yes | No | No partial dependency, hence in 2nd NF. Transient dependency hence violates 3rd 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 3rd NF, but R1 is not.

C)

Hence, the following relations do not have any partial nor transitive dependencies, and are in 3rd 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)
2nd | 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 3rd 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