- Unnormalized Table:
Student# Advisor
Adv-Room Class1 Class2
Class3
-------------------------------------------------------
1022 Jones
412 101-07
143-01 159-02
4123 Smith
216 201-01
211-02 214-01
- 1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes,
and give each table a primary key.
Each field contains at most one value from its attribute
domain.
Tables should have only two dimensions. Since one student
has several classes, these classes should be listed
in a separate table. Fields Class1, Class2, & Class3
in the above record are indications of design trouble.
Student# Advisor Adv-Room Class#
---------------------------------------
1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 201-01
4123 Smith 216 211-02
4123 Smith 216 214-01
- 2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued
key, remove it to a separate table.
Note the multiple Class# values for each Student# value
in the above table. Class# is not functionally dependent
on Student# (primary key), so this relationship is not
in second normal form.
Students: Student# Advisor Adv-Room
------------------------------
1022 Jones 412
4123 Smith 216
Registration: Student# Class#
------------------
1022 101-07
1022 143-01
1022 159-02
4123 201-01
4123 211-02
4123 214-01
- 3NF: Eliminate Columns Not Dependent On
Key
If attributes do not contribute to a description of
the key, remove them to a separate table.
All attributes must be directly dependent on the primary
key.
In the last example, Adv-Room (the advisor's office
number) is functionally dependent on the Advisor attribute.
The solution is to move that attribute from the Students
table to the Faculty table, as shown below:
Students: Student# Advisor
-------------------
1022 Jones
4123 Smith
Faculty: Name Room Dept
--------------------
Jones 412 42
Smith 216 42
