HIM 361 WEEK 10 ASSIGNMENT DATABASE DESIGN
11
On page 174, TAL Distributors Exercises, complete question 1.
The following exercises are based on the TAL Distributors database shown in Figure 5-1.
1. Using your knowledge of TAL Distributors, determine the functional dependencies that
exist in the following table. After determining the functional dependencies, convert this
table to an equivalent collection of tables that are in third normal form.
Item (ItemNum, Description, OnHand, Category, Price, (OrderNum,OrderDate, CustomerNum,
CustomerName, RepNum, LastName, FirstName, NumOrdered, QuotedPrice))
Step 1:
Breaking the group of Columns to create 1st normalization form.
Item (ItemNum, Description, OnHand, Category, Price, OrderNum, OrderDate, CustomerNum,
CustomerName, RepNum, LastName, FirstName, NumOrdered, QuotedPrice)
Step 2:
Converting into the 2nd normalization form.
Identifying the primary keys for each subset;
(ItemNum,
(OrderNum,
(CustomerNum,
(RepNum,
Regrouping the Columns as per dependencies;
(ItemNum, Description, Category, OnHand, Price)
(OrderNum, OrderDate, CustomerNum, CustomerName, RepNum, NumOrdered, QuotedPrice)
(CustomerNum, CustomerName)
(RepNum, LastName, FirstName)
Assigning the names for the newly assigned tables;
Item (ItemNum, Description, Category, OnHand, Price)
Orders (OrderNum, OrderDate, CustomerNum, CustomerName, RepNum, NumOrdered,
QuotedPrice)
Customer (CustomerNum, CustomerName)
