Logical Database Design

The Normalization Process

The Normalization is a mechanism that used to remove all kinds of anomalies (errors or the problems that can disturb the database handling in a table) of a relational database table. In general normalization has three levels those are;

• 1st Normalization
• 2nd Normalization
• 3rd Normalization.

Anomalies

There are three kinds of anomalies those can be found in an un-normalized table. They are;

Insertion anomaly: when ever we try to insert a record if the table does not satisfy the consistency in the table it is called as an Insertion Anomaly

Update anomaly: when ever we try to update a record if the table does not satisfy the consistency in the table it is called as an Update Anomaly

Deletion anomaly: when ever we try to Delete a record if the table does not satisfy the consistency in the table it is called as an Delete Anomaly

Advantages of Normalization

1. Reduces data duplication (redundancies) 
2. Ensures consistency and certain form of integrity
3. Prevents update anomalies 
4. Provides for faster and more efficient access to data

Example:

We'll see a real example of students database.

Un-normalized form of the database is like this contains following fields:

• Student Number
• Student Name
• Student Date of Birth
• Course Number
• Course Name
• Lecture Number
• Lecture Name

1st Normal Form (We have to remove Many - Many relationships by removing repeating attributes). In this database Student can enroll in one or more courses as well as a course can have zero or more students. Also  a course can have one or more lecturers as well as a lecturer can conduct one or more courses.

Student

• Student ID
• Student Name
• Date of Birth
• Course Number

Course

• Course Number
• Course Name
• Lecture Number

Lecture

• Lecture Number
• Lecture Name
• Course Number

2nd Normal Form (After removing partial dependencies). We have to add intermediate entities called Student-Course and Course-Lecture.

 Student

• Student ID
• Student Name
• Date of Birth
• Course Number

Course

• Course Number
• Course Name

Student-Course

• Student ID
• Course Number

Lecture

• Lecture Number
• Lecture Name
• Course Number

Course-Lecture

• Course Number
• Lecture Number

3rd Normal Form (After removing transitive dependencies)

Student

• Student ID
• Student Name
• Date of Birth

Course

• Course Number
• Course Name

Student-Course

• Student ID
• Course Number

Lecture

• Lecture Number
• Lecture Name

Course-Lecture

• Course Number
• Lecture Number

• A table is said to be in 1st normal form if it contains no repeating attributes or group of repeating attributes.

• A table is said to be in 2nd normal form if it is in 1st normal form and no column that is not part of the primary key is dependent on only a portion of the primary key.

• a table is said to be in 3rd normal form if it is in 2nd normal form and all attributes are depend on the key, the whole key and nothing but the key.