Eliminate Columns Not Dependent On Key
The Employee Relation satisfies -
First normal form - As it has no repeating groups.
Second normal form - As it do not have multi-attribute key.
The employee relation is in 2NF but not 3NF. So we take this table only later than adding the required attributes.
|
Employee Relation
|
|
Emp-ID
|
Emp-Name
|
Department
|
Department-Loc
|
|
1
|
Gurpreet Malhotra
|
A
|
N-Delhi
|
|
2
|
Faisal Khan
|
A
|
N-Delhi
|
|
3
|
Manisha Kukreja
|
B
|
Agra
|
|
4
|
Sameer Singh
|
C
|
Mumbai
|
The key is Emp-ID, and the Dept-Name and location defines only about Department, not an Employee. To get the third normal form, they must be moved into a individual relation. Since they define a department, therefore the attribute Department becomes the key of the new "Department" relation.
The motivation for this is the similar for the second normal form: we want not to use or avoid update, insertion and deletion anomalies.;
|
Department-Relation
|
|
Dept-ID
|
Department
|
Department Loc
|
|
1
|
A
|
N-Delhi
|
|
2
|
B
|
Agra
|
|
3
|
C
|
Mumbai
|
|
Employee-List
|
|
Emp-ID
|
Emp-Name
|
|
1
|
Gurpreet Malhotra
|
|
2
|
Faisal Khan
|
|
3
|
Manisha Kukreja
|
|
4
|
Sameer Singh
|
The rest of the Relation remains similar.
The last two steps: Isolate Semantically Related Multiple Relationships and Isolate Independent Multiple Relationships, changes the relations to higher normal form and are thus not discussed here. They are not even needed for the current example.