Database Normalisation

Page 1

Normalisation is the formal process for creating the most efficient database structure

Normalised databases will: Minimize data repetition, Eliminate data redundancy, Eliminate update anomalies

Zero normal form

- A flat-file database

- All elements in a single table

- Similar to a spreadsheet.

- Lots of data repetition and redundancy

First normal form

- For a database to be in First Normal Form, there must be no groups of repeated fields.

- This database repeats the book data 3 times

- The repeated data is taken out and put into its own table with its own primary key

Second Normal Form

- there must be no partial key dependancies. This means that none of the fields in a table with a composite key must depend on only part of it.

- The elements dependant on part of the key are separated

Page 2

Third Normal Form

- For a database to be in Third Normal Form, there must be no non-key dependencies

Summary

- First Normal Form: No groups of repeated attributes

- Second Normal Form: No partial key dependencies

- Third Normal Form: No non-key dependencies

Advantages of normalisation:

- minimizes data repetition

- Eliminates data redundancy and inconsistency

- Eliminates update anomalies

State two reasons why database designs are usually normalised.

1. to minimize data repetition

2. eliminates update anomalies

State two properties that the relations in a fully normalised database must have.

1. no partial key dependencies

2. no non key dependencies