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
The student exercise for this lab is similar to the sample exercise presented above.
Consider the following spreadsheet containing information about customers, their shipping and billing addresses, and the countries corresponding to each of those addresses. A customer may be associated with zero to many addresses. A customer may have 0 or 1 default billing address. A customer may have 0 or 1 default shipping address. Each address may be associated with 0 or 1 countries, while each country may be associated with 0 to many addresses.