Redundancy and Normalisation

Tutoring > Databases > Redundancy and Normalisation

Database redundancy occurs when a field gets repeated in two or more tables.

This has a few small benefits (easy to find data as there are repititions of it) but generally we want to avoid it as reduundanacy leads to poor integrity of the database.

Adding fields can cause redundancy

When a field (a row) is repeated in two or more tables, the new data (or the old) is said to be redundant.

An example of when this might happen is when a customer buys a product having previously been on record for buying a product. This can lead to inconsistency and data anomalies.

Integrity

Data integrity is a good thing. It refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle. To ensure data integrity in a database, database designers use normalisation.

Normalisation minimises redundancy

Normalisation generally increases the number of tables but reduces redundancy depending on which degree of normalisation is employed:

1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (Fourth Normal Form)

2NF - Second Normal Form

A database is in 2NF if:

It is in 1NF.
It contains no partial dependencies.

In a database, a partial dependency occurs when an attribute is dependent only partially on the primary key as opposed to the primary key in its entirety. In this case, the primary key is a composite key.

In the diagram below, the table from 1NF has been split into 3 seperate tables to eliminate partial dependencies.

BCNF - Boyce-Codd Normal Form

A database is in BCNF if:

It is in 3NF.
Each non-key attribute "must provide a fact about the key, the whole key, and nothing but the key.

Here the requirement is concerned with every attribute in the table, not just non-key attributes.

The diagram below shows decomposition to BCNF from 3NF by eliminating all redundancy based on functional dependency.

1NF - First Normal Form

A database is in 1NF if:

In the entity type there is a candidate key whose attributes never have NULL values, and one such key has been chosen as the primary key.
There are NO repeating groups. That is, there are no cells with repeated values in them.
In the diagram below, the table is in 1NF, but it has dependencies. The yellow boxes represent the Primary Key.

3NF - Third Normal Form

A database is in 3NF if:

It is in 2NF.
It contains no transitive dependencies.

Transitive dependencies occur when there is an indirect relationship that causes a functional dependency. For example, ”A -> C” is a transitive dependency when it is true only because both “A -> B” and

“B -> C” are true.

Here, "A -> C" means "C is dependent on A"

The diagram below shows further dividing of tables to eliminate transitive dependencies. The one at the bottom shows 3NF but not BCNF.

4NF - Fourth Normal Form

A database is in 4NF if:

It is in BCNF.
There are no non-trivial multivalued dependencies other than a candidate key.
A multivalued dependency is one where an item depends on more than one value.

Learn Computer Science

Developing a more interesting World

Free beginner - intermediate level computer science tutorials