CIS 336 iLab 1 Normal Forms and Entity Relationship Diagrams CIS 336 iLab 1 Normal Forms and Entity Relationshi | Page 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.
cust_idfnamelnamedef_bill_addr_iddef_ship_addr_idaddr_idstreet_addr city state zip
country_codecountry_name
202 John Smith 1096 2039 1096 123 Happy Ave. Orlando FL 32801 76 United States
202 John Smith 1096 2039 2039 2024 Shorline Dr. Seattle WA 98101 76 United States
202 John Smith 1096 2039 8053 100 N. Kent Ave. St. James WA 34 Jamaica
175 Hilda Yeager 2172 2172 3879 48 Spatzel Ct. Dusseldorf 5111 29 Germany
175 Hilda Yeager 2172 2172 3921 162 Rue Moritz Paris 75001 28 France
321 Siri Apple 1881 1881 1881 1 Infinite Loop Cupertino CA 95014 76 United States
Using the example solution as a guide, perform the following steps.