1st, 2nd and 3rd normal form

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. 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

First Normal Form

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

 

2nd Normal Form

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).

 

 

3rd Normal Form

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

 

 

 

Teacher Resources