Database Management System

The dependency diagram in figure below indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book.

Book royalty dependency diagram

a. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table

CR [10]

b. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.

CR[15]

QUESTION 2

Hillar Furniture Enterprise is a furniture store owned by Dr. Hillar Addo of AIT. The materials manager of this company maintains a list of suppliers for each of the materials items purchased by the company from outside vendors. The following table shows the essential data required for this application.

Hillar Furniture Company Purchasing Data is shown below:

Attribute Name Material_ID Material_ Name Unit_of_Measure Standard_Cost Vendor_ID Vendor_name Unit_price Terms_Code Terms

Sample Value 3792 Hinges 3” locking Each GHC 5.00 V300 Apex Hardware GHC 4.75 1 COD

a) Using the dependency diagram, identify the functional dependencies for this data. You may assume the following:

i. Each material has one or more suppliers. Each supplier may have supply

one or more items or may not supply many items.

ii. The unit price for a material may vary from one vendor to another

iii. The terms of a code for a supplier uniquely identifies the terms of a sale

(e.g. code 2 means 10% net 30 days, etc.). The terms for a supplier are the same for all material items ordered from that supplier.

[E][9]

b) Convert your data into a set of 3NF relations, identifying all the primary and foreign keys