Database Normalization

5 min readFeb 21, 2021

Normalization is a database design technique.

Outlines :

Introduction
Keys and Relationships in DBMS
Different forms of Normalization
i. 1NF (First normal form)
ii. 2NF (Second normal form)
iii. 3NF (Third normal form)
iv. BCNF (Boyce-codd normal form)
v. 4NF (Fourth normal form)
vi. 5NF (Fifth normal form)
vii. 6NF (Sixth normal form)
Conclusion
References

Introduction :

Normalization is a database design technique that is used to reduce data redundancy and eliminates undesirable operations like Insertion, Updation and Deletion Anomalies. The Normalization process divides larger tables into small tables and then links them by relationships and logically.

Keys and Relationships in DBMS :

Key :
A Key is a constraint that is used to identify a column uniquely. It can be a single key or composite key. Single key applied at a time only one column/attribute whereas Composite key applied more then one column. There are four types of keys in the database :
i. Super key.
ii. Candidate key.
iii. Primary key.
iv. Foreign key.

i. Super key :
Super key is a set of an attribute (or set of attributes) that is used to uniquely identify all attributes in a relation. Super key is a superset of a candidate key.
In a relation, the number of super keys is more than the number of candidate keys.

ii. Candidate key :
Candidate Key is a proper subset of a super key. A candidate key is an attribute or set of an attribute which can also uniquely identify a tuple. A table can have many candidate keys. It can have null values.

iii. Primary key :
Primary key is a minimal set of attributes of candidate key. Primary Key is a unique and non-null key which identifies a record uniquely in table. A table can have only one primary key. It can not have null value.

iv. Foreign key :
Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

In Department table department_id is Primary key and department_id of Employee table is Foreign key with respect to Department table.

note : Secondary key : After selection of Primary key among Candidate key, rest keys are Secondary keys. It can have null values.

Relationship in DBMS :
Any association between two entity types is called a relationship. Entities take part in the relationship. It is represented by a diamond shape.
There are three types of relationships :
i. one to one relation.
ii. one to many relation.
iii. many to many relations.

i. One to One relation :
Such a relationship exists when each record of one table is related to only one record of the other table.

For a person there is only one Passport.

ii. One to Many related :
Such a relationship exists when each record of one table can be related to one or more than one record of the other table. A one-to-many relationship can also be said as a many-to-one relationship depending upon the way we view it.

One Customer can have more than one Accounts.

iii. Many-to-Many Relationship :
Such a relationship exists when each record of the first table can be related to one or more than one record of the second table and a single record of the second table can be related to one or more than one record of the first table.

Many Customer can have a Product and Many Product can purches by a Customer.

Different forms of Normalization :

There are six normal forms to which We can divide larger table into small and more simple relationships :
i. 1NF (First normal form)
ii. 2NF (Second normal form)
iii. 3NF (Third normal form)
iv. BCNF (Boyce-codd normal form)
v. 4NF (Fourth normal form)
vi. 5NF (Fifth normal form)
vii. 6NF (Sixth normal form)

i. 1NF (First normal form) :
— Each table cell should contain a single value.
— Each record needs to be unique.
— Divide larger table into smaller table.
— One to One relationship.

.

ii. 2NF (Second normal form) :
— Table should be already in 1NF.
— All attributes within the entity should depend solely on the unique identifier of the entity.
— First table must have a Primary key column.
— Foreign key relationship from first table to another table.
— Remove partial dependency.

Second Normal Form, Foreign key relationship.

iii. 3NF (Third normal form) :
— Tables are already in 2NF.
— There are no transitive functional dependencies between tables.
— Table2 membership id dependent on Table1 membership id but Table3 solution id not dependent either of any table.

Third Normal Form, There is no transitive dependency.

iv. BCNF (Boyce-codd normal form) :
— Tables are already in 3NF.
— Prime attribute should not be dependent on Non-Prime attribute.

v. 4NF (Fourth normal form) :
— Tables are already in BCNF.
— There is no multivalued dependency on the Primary key.
— There are no non-trivial multivalued dependencies other than a candidate key.

Fourth Normal Form, There is no multivalued attribute dependency.

vi. 5NF (Fifth normal form) :
— Tables are already in 4NF.
— A composite key shouldn’t have any cyclic dependencies.
— It can not be decomposed into any number of smaller tables without loss of data.

Fifth Normal Form, There is no Cyclic dependency.

vii. 6NF (Sixth normal form) :
— Tables already in 5NF.
— Sixth Normal Form is not standardized, yet however, it is being discussed by database experts.

Conclusion :

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. There are five Normal forms 1NF, 2NF, 3NF, BCNF, 4NF and 5NF. Sixth Normal Form is not standardized, yet however, it is being discussed by database experts.
Based on discussion We can say that :
— Normalization Process in DBMS helps produce database systems that are cost-effective and have better security models.
— Most database systems are normalized databases up to the third normal forms.
— A foreign key helps connect tables and references a primary key.

References :

References for further more study :
— guru99.com/database-normalization.html
— https://www.w3schools.in/dbms/database-normalization/#first-normal-form
— https://www.tutorialspoint.com/dbms/database_normalization.htm
— https://www.javatpoint.com/dbms-normalization
— https://www.studytonight.com/dbms/database-normalization.php