Understand the normalization process through the first three stages and be able to describe what happens in each step.
computer science
Description
Lectures (Files-->lectures):
- I308-Module-3-ch-5.pptx
- I308-Module-3-ch-6.pptx (not required)
- I308-Module-3-ch-7.pptx
After readings this chapter, you should be able to know these keys below to prepare for this exercise
Preparation:
- Explain the role of a determinant (key) in a database entity.
- Explain the types of keys.
- What are foreign keys meant to do?
- What does relational algebra have to do with relational databases?
- Understand the normalization process through the first three stages and be able to describe what happens in each step.
- 1NF
- 2NF
- 3NF
Exercise
Normalize this relation below. Take it through the 1NF, 2NF, and 3NF progression.
- Use MS Word to draw the tables and show the entire set of tables at each step.
- Express your thought process in a few sentences at each step.
You will need to create primary key and foreign key columns.
This step will be 0NF
| petName | petBirth | petType | placeOfPurchase | owner |
| Fido | 2007 | Dog | Pet Supplies Plus | John Boyd |
| Boots | 2010 | Cat | Bob's Pets | Elsa Harp |
| Rover | 2010 | Dog | Pet Supplies Plus | Jakko Muttainen |
| Prince | 2018 | Fish | Wal-Mart | John Boyd |
| Furby | 2001 | Hamster | Pet Supplies Plus | Elsa Harp |
1NF: First Normal Form – Eliminate repeating data and non-atomic values; add keys
The data is stored in a relation (table) and each attribute (column) contains atomic values. There are no repeating groups of attributes.
2NF: Second Normal Form – Eliminate partial dependencies.
The relation is in first normal form and all the attributes depend on the relation’s unique identifier (primary key). --All the nonkey columns are dependent on the table’s primary key. (All nonkey attributes are functionally dependent on the entire primary key.)
3NF: Third Normal Form – Eliminate transitive dependencies.
The relation is in second normal form and all of its attributes are not transitively dependent on the unique identifier. ----A table contains only columns that are non-transitively dependent on the primary key (i.e., all nonkey attributes depend only on the primary key)
