Create 3rd Normal Form Schemas for the business documents on pages 38 and 40 of your “Case Study” text. This includes the “Customer Record” and the “Customer Invoice” forms used by the Landscaping company. Your final 3NF column should show entities, attributes and primary keys for all the data appearing in these documents.
Spreadsheet
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
Order Date
Delivery Date
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.)
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 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:
