Data Normalization
This homework is worth 70 points (70/1000, or 7%) toward your final grade.
Create 3rd Normal Form Schemas for the business documents In Appendix A below. This includes the “Customer Record” and the “Retail Sales Ticket” documents used by Eden Landscaping. Your final 3NF column should show entities, attributes, primary keys and foreign keys for all the data appearing in these documents.
Submission
You should present your finished results in a “Spreadsheet” format as provided in the example below that we looked at in class. Please save your spreadsheet as a PDF and submit the PDF via Canvas where the assignment appears in the Week Three materials.
Spreadsheet Template for Results
Record your results in columnar format imitating the spreadsheet template provided. Your spreadsheet should have four columns:
Unnormalized 1st Normal Form 2nd Normal Form 3rd Normal Form
Begin by listing, for each document, all data elements (“attributes”) on the document in the “unnormalized” column.
List the document name in UPPER CASE and HIGHLIGHT it in yellow.
For example:
Unnormalized
CUSTOMER ORDER
Order Number
Customer Number
Customer Name
Etc.
Etc.
After listing all data elements (“attributes”) in the “unnormalized” column for these two documents, then go through the list and put all data into First Normal Form. Replace each document name with an entity name as needed. If an attribute appears on multiple documents (for example, CustomerName), then you should only list it once in your First Normal Form column.
Identify the Primary Keys by highlighting them in a different color other than yellow (as you see in grey in the example below.)
Identify the Foreign Keys by adding a “(FK)” tag after the foreign key attribute name (as you see in the example below.)
Do the same for the second and third normal form columns.
Your final 3NF column should contain all the data in all the documents organized by entity, listing all attributes in each entity with primary keys and foreign keys defined.
If an entity does not change from first to third normal form, then simply copy and paste the data in the second normal form column to show that the data is already in second normal form. HINT: you’ll know this if the entity has an atomic key – that is, NOT concatenated/compound. Without a compound key, the data is already in 2NF.
Example:
Appendix A – Eden Landscaping Documents
Customer Record
Retail Sales Ticket
