Normalisation

🔴First Normal Form (1NF): 

  

                The relation has a Primary Key, which uniquely identifies each row in the relation. Every column stores Atomic Values, and there are no Repeating Groups.

Atomic means the column only stores one thing. A column called, FullName, which stores the customer’s first & last name is not atomic. You should have 2 atomic columns, one for FirstName, and another for LastName. Columns storing comma-separated values are also non-atomic.

If you’ve ever seen

 Phone1, Phone2, and Phone3 

 in your Customers table (each phone column more sparse than the prior), then this table contains a Repeating Group. 

🔵Second Normal Form (2NF):

            The relation meets 1NF, and there are No Partial Functional Dependencies. That is, each non-key attribute is Functionally Dependent on the full Primary Key. For a Partial Functional Dependency to occur, the Primary Key must be a Composite Primary Key. A Composite Primary Key is a Primary Key that consists of 2 or more columns.

            

Suppose your OrderLines table has a Composite Primary Key of OrderID + ProductID. Any other columns in this table that describe Products are only Functionally Dependent on the ProductID, and have nothing to do with the OrderID. These columns are Partially Functionally Dependent on the Primary Key, and should be removed from the OrderLines table and placed in the Products table.

🔴Third Normal Form (3NF): 

         The relation meets 2NF, and there are No Transitive Functional Dependencies. A Transitive Functional Dependency is when a non-key column is Functionally Dependent on another non-key column, which is Functionally Dependent on the Primary Key.

         

Suppose your Employees table has StateCode, a CHAR(2) column that stores the state the employee lives in. The StateCode column is not   part of the Employees table Primary Key. Suppose this table also has columns describing the state, such as the full state name, and information about the state’s geography. Any columns describing the state, beyond StateCode, are Transitively Dependent on the Primary Key (via StateCode), and should be removed from the Employees table and added to the States table.

A common saying people use to remember the first 3 normal forms is,

“Every non-key attribute must provide a fact about the key (1NF), the whole key (2NF), and nothing but the key (3NF).”