Agenda and Objectives. Agenda Examine normalized and non-normalized ERDs/Tables Objectives Given an ERD, you will be able to determine whether or not it is in 3NF. Given a non-normalized ERD (or database table), you will be able to revise it to make it 3NF compliant. Resource
IS 310Dr. Jean A. Pratt
Only those attributes that describe the entity are included in that entity
Each entity has a primary key (PK)
Each attribute is the smallest unit that would be included in a query
Data is not repeated in different entities
Each attribute would contain a single value in the database table cell
Redundant data is minimizedFirst Normal Form (1NF)
Note that we will still have redundant data (although not multivalued) if students live at the same address. We’ll want to remove that redundancy later.
What about employees with more than 3 children?
Entities meet 1NF requirements in that entityPLUS
All attributes are functionally dependent upon the entire primary key.Second Normal Form (2NF)
Entities meet 2NF requirements in that entityBUT
One non-key attribute determines another non-key attributeThird Normal Form (3NF) Violation
Know the difference between 1NF, 2NF and 3NF in that entity
Ensure your ERD (and resulting database) is in at least 2NF
3NF is the standard (with purposeful violations based on performance)Review
Scenario: Chain stores (e.g., Target) that contain several departments and sell many products to many customers.