Good design of a database will overcome the problems of redundancy and inconsistency.

An entity-relationship diagram shows the links between tables

Tables

There is a standard way of writing down the design of a table:

The table (entity) name is shown in capital letters, followed by the fields in brackets. The key field is identified by underlining it.

Example :

Any foreign fields would be shown in italics. A foreign field is a keyfield from another table - and provides a link between the two tables.

Example :

Normalisation

Normalisation is the process undertaken to make sure a database has no redundant data or inconsistencies.

Example:
A number of pupils are sitting some exams in a school.
The raw data is shown below in un-normalised form.

First Normal Form

A table is in First Normal Form (1NF) if there are no repeating groups. In other words, each column must contain only a single value and each row must have an item in every column.

This can usually be done by putting the data into two tables ... separating the repeated data into a separate group.

The two tables are now...

A record of the pupil table can only be identified uniquely if we know the Pupil Number and the Exam Number. So, the keyfield of the Pupil Table is a combination of Pupil Num and Exam Num. 

The table definitions are...

Second Normal Form (2NF)

A table is in Second Normal Form if it is in 1NF and it has no partial dependencies.

If you look at the PUPIL table above, the fields Name and DOB depend on the Pupil Number - which is only part of the keyfield. (They are not at all dependent on the Exam Number.). These are called partial dependencies.

To convert the tables into 2NF we may have to create another table which links the others.

Many-to-many relationships can be replaced by one-many-one relationships. 

can be converted to...

An extra table is created :

The tables would be as follows..

Put simply, any table which has a combined keyfield should not have any other fields in it.

Third Normal Form (3NF)

For a table to be in 3NF it must be in 2NF and have no transitive dependencies.

If you look at the EXAM table above, the Room Name field is dependent on the Room Code and not on the keyfield of the table (Exam Num). This is called a transitive dependency. These can be removed by transferring the fields into a separate table and leaving behind a foreign field (which is the keyfield of the new table).

So, the EXAM table would be split into these two tables..

The table definitions are now...

The tables are now in 3NF.

For simple people (like me!) -

For Third Normal Form, make sure that...

• the fields in a table are only related to that entity (ie only data about the pupil should be in the Pupil table)...except for fields which are keyfields of other tables.