- 1 Database Normalization
- 2 Purpose of Database Normalization
- 3 Characteristics of Database Normalization
- 4 Types of Normal Forms
- 4.1 First Normal Form(1NF)
- 4.2 Problems in 1NF
- 4.3 Second Normal Form
- 4.4 Analysis of Second Normal Form (2NF)
- 4.5 Third Normal Form
- 4.6 Boyce-Codd Normal Form (BCNF)
- 4.7 Fourth Normal Form
- 4.8 Fifth Normal Form
- 5 Advantages and Disadvantages of OODBMS
- 1NF (First Normal Form)
- 2NF (Second Normal Form)
- 3NF (Third Normal Form)
- 4NF (Fourth Normal Form)
- 5NF (Fifth Normal Form)
- BCNF (Boyce-Codd Normal Form)
Purpose of Database Normalization
- It makes the database design efficient in performance.
- It reduces the amount of data if possible.
- It makes the database design free of update, insertion and deletion anomalies.
- It makes the design according to the rules of relational databases.
- It makes a design that allows simple retrieval of data.
- It identifies a relationship between entities
- It simplifies data maintenance and reduces the need to restructure data.
Characteristics of Database Normalization
- Each relation must have a key field.
- All fields must contain atomic data.
- There must be no repeating fields.
- Each table must contain information about a single entity.
- Each field in a relation must depend on key fields.
- All non-key fields must be mutually independent.
Types of Normal Forms
First Normal Form(1NF)
Problems in 1NF
Second Normal Form
The above in 1NF has some attributes which are not depending on the whole primary key.
For example, Accountant Name, Accounting Age and Group information is determined by Accountant Number and is not dependent on Skill.
The following relation can be created in which all attributes are fully dependent on the primary key Account Number.
Similarly, another relation Skill can be created in which all fields are fully dependent on the primary key as follows:
The attribute Proficiency in 1NF relation was fully dependent on which whole primary key.
The Proficiency requires to know the accountant number and skill number. The third relation will be created as follows:
There are three relations in second normal form (2NF).
The attributes of all relations are fully dependent on the primary keys,
Analysis of Second Normal Form (2NF)
The following analysis indicates whether the problems are eliminated in 2NF or not.
If the user needs to change the name of Accountant Number 35 to “M. Daud” in 1NF, he must change the name in every record in which Accountant number 35 appears. But in 2NF, the record of one accountant appears only once. The updating problem is eliminated in 2NF.
The record of one accountant appears only once in the database, the possibility of inconsistent data is automatically eliminated.
In 1NF, it was not possible to enter a new Skill Number until an Accountant with that skill existed.
In 2NF, any number of skills can be added in Skill relation without an accountant with that skill. It eliminates the addition problem.
In 2NF, if the record of Ghafoor is deleted, it does not delete any other record.
The analysis shows that the second normal form has solved all the problem of 1NF.
Third Normal Form
A relation is in third normal form if it is in 2NF and if no non-key attribute is dependent on another non-key attribute.
It means that all non-key attributes are functionally dependent only on the primary key. There should be no transitive dependency in a relation.
In order to convert a relation to 3NF:
- Remove all attributes from the 2NF record that depend on another non-key field.
- Place them into a new relationship with the other attributes as the primary key.
The Accountant table in 2NF contains some attributes which are depending on non-key attributes.
For example, Group City and Group Supervisor are depending on a non-key field Group Number. A new relation can be created as follows:
The Second table created to form the Accountant Table in 1NF is as follows:
Both the Account table and Group table contain the attribute Group Number. This attribute is used to join both tables.
The Skill table in 2NF contains no attributes, which is depending on non-key attributes. It is already in third normal form and will be used without any further change.
The proficiency table in 2NF also contains no attribute which is depending on the non-key attribute. It is already in third normal form and will be used without a further change.
Boyce-Codd Normal Form (BCNF)
A relation is in Boyce-code normal form if and only if every determinant is a candidate key.
It can be checked by identifying all determinants and then making sure that all these determinants are candidate keys.
BCNF is a stronger form of third normal form. A relation in BCNF also in third normal form. But a relation in 3NF may not be in BCNF.
Assume the PartName is unique. It means that no two parts can have the same name.
There are now two candidate keys(projectID, PartID)
and (projectID, PartName).
There are following dependencies:
This relation satisfies 2NF because there are no non-key attributes that are dependent on a subset of the primary key. PartName is not a non-key attribute, it is part of a candidate key. Therefore this relation is in 2NF.
There are no transitive dependencies so the relation is in 3NF. PartID and PartName are ignored by 3NF rule because they are both key attributes.
In order to convert this relation to BCNF, all functional dependencies must be removed which have a determinant that is a candidate key. The result is as follows:
Fourth Normal Form
A relation is in 4NF If it is in BCNF and has no multi-valued dependencies.
Fifth Normal Form
A relation is in fifth normal form if it has no join dependency.
Fifth normal form (5NF) is also called project-join normal form(PJNF).
The first four normal forms are based on the concept of functional dependency. The fifth normal is based on the concept of join dependency.
You May Like: