HW-1 Normalization

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
HW-1 Normalization


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
HW-1 Normalization


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

HW-1 Normalization














Customer:














































CSCI 3287: Design and Analysis of Data Systems    Page 4