Normalisation

What is a good Database design?

Deciding on a suitable robust logical structure for the tables is
a key element of a good relational database design.
A good design has the following features:
Efficient
Avoids possible anomalies.

Normalisation

Normalisation theory is based on some simple ideas and helps guide us in developing a suitable set of tables for a given problem.
Normalisation theory allow us to recognise relations with possibly undesirable properties and design better relations
Function Dependency
Closure of Attributes
If A+ = all attributes, then A is a key.

1NF

A set of relations is in first normal form(1NF) if:
1. Tuples cannot have repeat groups of similar data(atomicity)
2. Each tuple(row of a table) must have a unique identifier(primary key)
generally speaking, we can easily avoid 1NF.

2NF ##

If there is no partial dependency on a concatenated key in any relation.
concatenated key: A key composed of more than one attribute
partial dependency: A functional dependency between an subset of a concatenated key and another set of attributes.

3NF & BCNF

3NF : if there is no dependencies on non-key attributes.
BCDF: if for each non-trivial functional dependency A → B in relation
R, A is a superkey of R.
• A is a superkey of R if A contains a key.
• BCNF is a slightly stronger version then 3NF that handles
relations with two or more overlapping candidate key.

Design by Decomposition
a good relational database design is to move through a series of designs that satisfy 1,2,3NF & BCNF and 4NF

4NF

if for each non-trivial A →→ B, A is a superkey.
Given a set of relations and a set of functional dependencies
FDs and multivalued dependencies MVDs:
• Repeat until all relations are in 4NF
• Select any relation R ′(A, B, C) that violates the 4NF condition.
• Decompose R ′ into R1(A, B) and R2(A, C).
• Compute the FDs and MVDs for R1 and R2.
• Compute keys for R1 and R2.

Disadvantage of BCNF and 4NF
there may be redundant information and anomalies.

To be continued…