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))

2. List the functional dependencies in the following table that concern invoicing (an application TAL Distributors is considering adding to its database), subject to the specified conditions. For a given invoice (identified by the Invoice- Num), there will be a single customer. The customers number, name, and complete address appear on the invoice, as does the date. Also, there may be several different items appearing on the invoice. For each item that appears, display the item number, description, price, and number shipped. Each customer that orders a particular item pays the same price. Convert this table to an equivalent collection of tables that are in third normal form.

Invoice (InvoiceNum, CustomerNum, LastName, FirstName, Street, City, State, PostalCode, Date, (ItemNum, Description, Price, NumShipped))

3. The requirements for TAL Distributors have changed. A number and a name now identify each storehouse. Units of each item may be stored in multiple storehouses, and it is important to know precisely how many items are stored in each storehouse. In addition, TAL Distributors now wants to manage information about the suppli- ers from which it purchases items. For each item, TAL Distributors needs to know the number and name of each supplier as well as the expected lead time for delivering each item. (Lead time is the amount of time a supplier is expected to take to deliver the item after TAL Distributors has ordered it.) Each item can have many suppliers, and each supplier can supply many items. Using this information, convert the following unnormalized relation to fourth normal form:

Item (ItemNum, Description, Category, Price, (StorehouseNum, StorehouseName, OnHand), (SupplierNum, SupplierName, LeadTime))

4. What changes would you need to make to the normalized tables in Exercise 3 to support the following additional requirements:

a. TAL needs to keep track of the price a supplier charges for an item. Different suppliers can charge

different prices for the same item.

b. Each storehouse has a manager who is identified by a manager number, a manager last name, and a

manager first name.

5. TAL Distributors is considering changing its business model so that many sales reps can represent one cus- tomer and one sales rep can represent many customers. Using this information, convert the following unnorma- lized relation to fourth normal form.

Customer (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, (RepNum, LastName, FirstName, Street, City, State, PostalCode, Commission, Rate))