The simplest form of Normalization is the First Normal Form (1NF)
The following rules must be followed to make a database table 1NF conformed.
- Each column must have a unique name.
- The ordering or rows and columns in the table is irrelevant.
- Every column must have a single data type - that is if a column holds string values, then it cannot hold integer values.
- Each row must hold unique values - no two rows can be the same.
- Each column must contain a single value.
- Columns cannot contain repeating groups.
CustID Name SSN-4 Bank Name1 Bank Name2 Bank Name3 A123 Tom Brockaw 1234 BoFA WellsFargo Chase
Instead the bank data should be put in a seperate table
CustID Name SSN-4
A123 Tom Brockaw 1234
Cust ID Bank name
Next step is to convert a database table to 2NF compliance. A table is considered 2NF when it satisfies
- 1NF compliant
- All of the non-key fields depend on all of the key elements.
|Name||Languages||Current Home Location|
|Sean||C++||268 San Bruno|
|Sean||Java||268 San Bruno|
|Sean||Perl||268 San Bruno|
|Jim||C||114 Wall Street|
|Jim||Java||114 Wall Street|
|Jim||C#||114 Wall Street|
Here the key is Name + Languages but the "Current Home Location" is based on part of the key - Name and not on "Languages". Therefore the non-key field ("Current Home Location") can be taken out of the table and placed in a separate table.
Now the 2 NF table is
Name Current Home Location
Sean 268 San Bruno
Jim 114 Wall Street.
A 3NF complaint table is
- 2NF compliant
- It contains no transitive dependencies - in this case a non key is dependent on another non key.
Employee Project Department
Alison Java Computer Science
Jim C# Computer Science
John SIP Electronics & Telecommunications
Tim CTI Electronics & Telecommunications
Robert Long Jump Athletics
Sean Sprint Athletics
Tara Java Computer Science
In this case Department is dependent on the Project an employee takes and both Project and Department are non-keys. In order to remove the transitive relationship, we take out the dependent field (Department) into a seperate table and link the dependent field with the other non-key (Project)
Employee (PK) Project
Robert Long Jump
Project (PK) Department
Java Computer Science
C# Computer Science
SIP Electronics & Telecommunications
CTI Electronics & Telecommunications
Long Jump Athletics
Boyce Codd Normal Form - 3 NF + Every determinant is a candidate key ( fields that may be dependent on part of the primary key)
Note: - A candidate key is a "minimal" superkey.
4NF complaint table - which is BCNF compliant + It does not contain an unrelated multi-valued dependency
5NF - 4NF + does not contain any related multi-valued dependency
DKNF (Domain/Key Normal Form) - the table contains no constraints except domain and key constraints
5NF - 4 NF + does not contain any related multi-valued dependency