Description
Instructions
-
Read the attached ‘Eden Landscaping’ customer forms to become familiar with the business details. There are 2 forms: ‘Customer Record’ and ‘Customer Invoice’, extracted from case study workbook.
-
Go through all data elements/attributes listed in the forms and organize all attributes to create 3NF design so that it reduces redundancy and dependency of data.
-
Your un-normalized, First and Second Normal Forms will NOT be scrutinize for grading purposes. They are the means to the end (3NF). However, you need to display unnormalized, 1st NF, 2nd NF and 3rd NF.
-
Identify each ENTITY names by highlighting them in yellow color and list each attributes.
-
Identify the Primary Keys and Foreign Keys, if any, and show them within a bracket next to the field name, e.g. Order_ID (PK), Product_ID (FK), etc. and highlighting them in a different color other than yellow e.g. grey color.
-
If an entity does not change from first to second normal form, then simply copy and paste the data into the second normal form column to show that the data is already in second normal form, and so on.
-
Your final 3NF column should contain all the data in all the documents organized by entity, listing all attributes in each entity with primary and foreign keys, if any.
-
Ensure that each attribute is functionally dependent on the primary key for that entity. If an attribute appears multiple times, list it only once in your final normalization design, e.g. Customer Name.
CSCI 3287: Design and Analysis of Data Systems Page 1
Example of sample output, from class practice: EZ Chair Company
UNNORMALIZED |
FIRST NORMAL FORM |
SECOND NORMAL FORM |
THIRD NORMAL FORM |
||||||
Customer Order |
Customer Order |
Customer Order |
Order |
||||||
Order Number |
Order Number |
Order Number |
Order Number (PK) |
||||||
Order Date |
Order Date |
Order Date |
Order Date |
||||||
Delivery Date |
Delivery Date |
Delivery Date |
Delivery Date |
||||||
Customer Discount |
Customer Discount |
Customer Discount |
discount amount |
||||||
discount amount |
discount amount |
discount amount |
invoiced amount |
||||||
invoiced amount |
invoiced amount |
invoiced amount |
customer number (FK) |
||||||
customer number |
customer number |
customer number |
order total |
||||||
customer name |
customer name |
customer name |
|||||||
Contact |
Contact |
Contact |
Customer |
||||||
ContactType |
ContactType |
ContactType |
customer number (PK) |
||||||
bill to address |
bill to address |
bill to address |
customer name |
||||||
bill to city |
bill to city |
bill to city |
Contact |
||||||
bill to state |
bill to state |
bill to state |
ContactType |
||||||
bill to zip |
bill to zip |
bill to zip |
bill to address |
||||||
ship to address |
ship to address |
ship to address |
bill to city |
||||||
ship to city |
ship to city |
ship to city |
bill to state |
||||||
ship to state |
ship to state |
ship to state |
bill to zip |
||||||
ship to zip |
ship to zip |
ship to zip |
ship to address |
||||||
** |
Product Number |
order total |
order total |
ship to city |
|||||
** |
Description |
ship to state |
|||||||
** |
quantity ordered |
OrderDeail |
OrderDeail |
ship to zip |
|||||
** |
unit price |
Order number |
Order number |
||||||
order total |
Product Number |
Product Number |
OrderDeail |
||||||
Product Description |
Quantity |
Order number (PK) / (FK) |
|||||||
Quantity |
Total |
Product Number (PK) / (FK) |
|||||||
unit price |
Quantity |
||||||||
Product |
total |
||||||||
Product Number |
|||||||||
Product Description |
Product |
||||||||
unit price |
Product Number (PK) |
||||||||
Product Description |
|||||||||
unit price |
|||||||||
CSCI 3287: Design and Analysis of Data Systems |
Page 2 |
INPUT
Please use below 2 forms for this Normalization homework assignment:
Your Normalization design should put together info from these 2 forms. DO NOT carried away with normalization, i.e. do not create unnecessary entities.
CSCI 3287: Design and Analysis of Data Systems Page 3
Customer:
CSCI 3287: Design and Analysis of Data Systems Page 4