The dependency diagram in Figure Q6.7 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.
Figure Q6.7 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.
b. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.
To keep track of office furniture, computers, printers, and so on, the FOUNDIT Company uses the table structure shown in Table P6.5.
Table P6.5 Sample ITEM Records
|
Attribute Name
|
Sample Value
|
Sample Value
|
Sample Value
|
|
ITEM_ID
|
231134-678
|
342245-225
|
254668-449
|
|
ITEM_LABEL
|
HP DeskJet 895Cse
|
HP Toner
|
DT Scanner
|
|
ROOM_NUMBER
|
325
|
325
|
123
|
|
BLDG_CODE
|
NTC
|
NTC
|
CSF
|
|
BLDG_NAME
|
Nottooclear
|
Nottoclear
|
Canseefar
|
|
BLDG_MANAGER
|
I. B. Rightonit
|
I. B. Rightonit
|
May B. Next
|
a. Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive and/or partial dependencies.
b. Write the relational schema and create a set of dependency diagrams that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and attributes as necessary.
c. Draw the Crow's Foot ERD.