Normalisation
Database Normalisation
Normalisation is the process of removing redundancy within a database.
Doing so:
- Makes accessing and storing data easier and more efficient
- Makes maintaining the database contents quicker and easier
- Reduces the chances of data errors or duplication.
In order to a achieve this a set of rules were established to achieve efficiency – 1st, 2nd and 3rd normal form. Each rule builds on the last rule, but think of it as a mental process that you go through to improve your database.
Tutorial Video
1NF
First Normal Form “Unique and atomic values”
- Each Row in a database must be unique
- Easily achieved through the use of a Primary Key
- All cells must only contain one piece of data (no arrays stored in a cell)
- All column data must be of the same type
2NF
Second Normal Form “No Partial Dependencies”
- This is only applicable if the table contains a composite key.
- The database must be in First Normal Form
- All columns within a table must depend on the whole key (No partial Dependency).
3NF
Third Normal Form “No Transitive Dependencies”
- The database must be in Second Normal Form
- All fields must only be determinable directly by the primary key (no transitive dependency)
| Book | Genre | Author | Author Nationality |
|---|---|---|---|
| Twenty Thousand Leagues Under the Sea | Science Fiction | Jules Verne | French |
| Journey to the Center of the Earth | Science Fiction | Jules Verne | French |
| Leaves of Grass | Poetry | Walt Whitman | American |
| Anna Karenina | Literary Fiction | Leo Tolstoy | Russian |
| A Confession | Religious Autobiography | Leo Tolstoy | Russian |
In the table above the Author Nationality is dependent on the Book only via the Author
“The author of the book is French because the author of the book is Jules Verne”
Author Nationality —-> Author —-> Book
A great video explaining the process – https://www.youtube.com/watch?v=UrYLYV7WSHM
Resources
Past paper questions 1 – (pages 2 – 3)
Past paper questions 2- (pages 2 – 4) – Includes questions on 1nf,3nf