Give an example of a column A and a column B such that B is functionally dependent on A. Give an example of a column C and a column D such that D is not fictionally dependent on C.
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 invoicenum), there will be a single customer. The customer's 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, Customerum, 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. I addition, TAL Distributors needs to know the number
view full and name of each supplier as well as the expected lead time for delivering each item. (Lead time is the amount of time a suppliers, and each supplier can supply many items. Using this information, convert the following unormalized 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 table 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 manger last name, and a manager first name.
5. TAL distributors is considering changing its business model so that many sales reps can represent one customer and one sales rep can represent many cutomers. Using this information, convert the following unnormalized relation to fourth normal form.
Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, (RepNum, LastName, FirstName, Street, City, State, PostalCode, Comission, Rate))
6. Convert the following unnormailzed relation to fourth normal form using the same requirements as in Question 5 (many sales reps can represent one customer and one sales rep can represent many customers)
Rep (RepNum, LastName, FirstName, Street, City, State, Postalcode,Commission, Rate, (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit))
7. Is there any difference between the tables you created in Question 5 and 6? Why or Why not?