Normalisation (Relational Algebra)

databases

Definition

Normalisation (Relational Algebra)

Normalisation is the process of reducing the amount of data anomalies iteratively.

Forms

First Normal Form

First Normal Form

A relation is in first normal form if it contains only atomic values and each value in the table is of a single type.

Example:

Book ID	Book Title	Authors	PublisherAndYear
1	Database Systems	Some Author, Another Author	O’Reilly, 2024

Authors: The Authors column contains multiple values (an array of values) and should be divided into multiple rows. You could divide in into two columns, however, it is unknown that each book in that table will have only two authors.

PublisherAndYear: The PublisherAndYear column contains two columns: Publisher and Publish Year. This is definitely not atomic and must be divided into smaller atomic columns (Publisher and Publish Year) to fulfil the first normal form.

Applying the first normal form to the above, the following table would be the result:

Table: Books

Book ID	Book Title	Author	Publisher	Publish Year
1	Database Systems	Some Author	O’Reilly	2024
1	Database Systems	Another Author	O’Reilly	2024

Atomicity

Atomicity does not necessarily mean that columns which are divisible must be divided at any cost. For example, a date is already atomic whereby it can be divided into day, month and year. The same applies for values, such as addresses. If not necessary due to business rules, the address must not be divided into smaller atomic values.

Second Normal Form

Second Normal Form

A relation (table) is in the second normal if it is in the first normal form and all non-key attributes are fully functional dependent on the primary key.

There must be no partial dependencies (no non-key attribute depends only on a part of the primary key, if the primary key is composite).

Example:

Order ID	Quantity	Product ID	Product Name
1	12	1	Widget A

Applying the second normal form to the above, the following table would be the result:

Table: Orders

Order ID	Quanity	Product ID
1	12	1

Table: Products

Product ID	Product Name
1	Widget A

Third Normal Form

Third Normal Form

A relation (table) is in the third normal form if it is in the first and second normal form and every non-key attribute is non-transitively dependent on the table’s primary key.

Example:

Student ID (PK)	Student Name	Department ID	Department Name	Department Location
1	Alice Anderson	10	Computer Science	Building A
2	Bob Brown	10	Computer Science	Building A
3	Carol Chapman	20	Electrical Engineering	Building B

Applying the third normal form to the above, the following tables would be the result:

Table: Students

Student ID (PK)	Student Name	Department ID (FK)
1	Alice Anderson	10
2	Bob Brown	10
3	Carol Chapman	20

Table: Departments

Department ID (PK)	Department Name	Department Location
10	Computer Science	Building A
20	Electrical Engineering	Building B