Normalisation to 3NF
Introduction
Normalisation is an analytical technique used in database design. It aims to create a database design that has two key characteristics:
- Redundant data is minimised.
- The chance of making the data in the database inconsistent is minimised.
To 'normalise' a database, you take a flat file version of it and apply three rules, one after the other. After each rule has been applied you may see that a table gets broken into two or even more tables, although the records in them will all still be related. As you saw in the very first section, this will help overcome all kinds of problems. Approached in the right way, normalisation is a very straightforward, very mechanical process. Approached in the wrong way and you will end up confused!
An example of normalisation
‘DVD Now’ is a DVD club. A typical member will have a membership card with their ID on, their name, address, phone number and join date on. When they want to take a DVD out, they present their membership card along with the empty DVD box from the shelf in the shop. The assistant gets out their Member's Record and the following details are written onto it: the ID of the DVD, the name of the DVD, the date it is due back on, the certification of the DVD and what the certification means. Up to 3 DVDs can be borrowed. A typical record looks like this:
You have been asked to analyse this database using normalisation. Where do you start? As was said earlier, normalisation is a very mechanical process. It follows a number of pre-defined steps. We will now go through in detail the steps that you must follow. We will start with the setting-up step, which we will call STEP 0.