Lab 3

# 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