2 . Using the answer to exercise 1 , remove all partial dependencies and draw the new dependency diagrams . For each new table created , specify its normal form ( 1NF , 2NF , 3NF ).
Figure 2- solution 2
Discussion : To achieve 2NF , we must remove partial dependencies . This is done by decomposing into three tables , and three dependency diagrams at this stage . Notice that the Transitive relationship has not yet been addressed .
3 . Using the answer to exercise 3 , remove all transitive dependencies , and draw the new dependency diagrams . For each new or revised table , specify its normal form .
Figure 3 – Solution 3
Discussion : The transitive relationship has been removed by creating the Publishers table , in which PubName is fully dependent upon PubCode . Pubcode also persists as a dependent attribute and Foreign Key in Books . All tables are now in 3NF , and have been given meaningful names reflecting the entities they represent . LINEITEMS represents the collection of line items for all invoices . Invoices represents the collection of Invoices for all customer orders . Books makes up the list of all books available for sale ( whether they have ever been ordered or not ). Each book has a publisher , and the publisher code and Name reside in the Publishers table .
4 . Draw the ERD for exercise 3 , using Crow ‘ s Foot notation . End of Sample Exercise .
Student Exercise