CIS336 Lab 1: Normal Forms and Entity Relationship Diagrams
Introduction
In this exercise, you will analyze a de-normalized data set presented in the form of a spreadsheet. You will next construct a series of dependency diagrams, transforming the evolving data model from First Normal Form (1NF), to Second Normal Form (2NF), and finally to Third Normal Form (3NF). When the model has reached 3NF, you will construct the Entity Relationship Diagram (ERD) depicting the logical design of the database. Your ERD will use Crow’s Foot notation to denote the relationships between tables.
Sample Exercise, With Solution
Before completing your lab, please review this example problem. The diagram below is a partial depiction of a business spreadsheet for a retail store operation. The store sells books. Books have a 13-digit International Standard Book Number (ISBN), a title, a publisher, and a unit price. Invoices track sales of books. An Invoice contains one or many line items, with each line item reflecting the sale of one or more copies of a specific book. Every publisher has a company name, and a publisher code.
InvNum ISBN13 InvDate BookTitle PubCode PubName Qty unitPrice
1022 9781291940336 2015-03-09 MYSQL Functions 1001 Lulu.com 3 4.99
1022 9780321833877 2015-03-09 MySQL, Fifth Edition 1029 Addison-Wesley 5 33.95
1022 9781890774820 2015-03-09 Murach’s MySQL, 2nd Edition 1032 Murach, Mike & Associates, Inc 2 48.95
1249 9781449374020 2015-02-22 MySQL Cookbook: Solutions for Database Developers and Administrators 1118 O’Reilly Media, Incorporated 9 50.59
1249 9781449325572 2015-02-22 PHP & MySQL: The Missing Manual 1118 O’Reilly Media, Incorporated 6 29.95
1249 9781890774790 2015-02-22 Murach’s PHP and MySQL, 2nd Edition 1032 Murach, Mike & Associates, Inc 1 48.95
BookSales
1. Using the BookSales table structure shown above, draw the dependency diagram, and show all dependencies, full, partial, and transitive.
Figure 1 -solution 1
Discussion: A composite primary key consisting of InvNum + ISBN13 can be constructed, ensuring that all rows are unique. All remaining attributes are shown to be dependent (at least partially) on this composite key, so the table is 1NF. Further analysis shows that InvDate is dependent on only part of the key (InvNum), and that unitPrice, PubCode, and BookTitle also depend on part of the key (ISBN13). A transitive relationship is also revealed: PubName truly depends on PubCode, which in turn depends on ISBN13.
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
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_id fname lname def_bill_addr_id def_ship_addr_id addr_id street_addr city state zip country_code country_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.
1. Using the CustomerAddressCountry spreadsheet show above, draw the dependency diagram, and show all dependencies, full, partial, and transitive. Paste your solution into the answer sheet, and label it, Answer #1.
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). Paste your solution into the answer sheet, and label it, Answer #2.
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. Paste your solution into the answer sheet, and label it, Answer #3.
4. Draw the ERD for exercise 3, using Crow’s Foot notation. Paste your solution into the answer sheet, and label it, Answer #4.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at [email protected].
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at [email protected] and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at [email protected].
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.