Normalisation

Normalisation = A techniques used to produce a set of tables with minimal redundancy, as well as support the data requirements of an organisation.

Reduces file size, creating more space.

It's applied at the logical design stage one the ER models have been mapped to an initial set of tables.

Update anomalies are inconsistencies within the data.

Tables with lots of data redundancy may suffer from update anomalies. The three types of update anomaly include:

• Insertion - E.g. inserting a new member of staff to a StaffDistributionCenter, would potentially mean re-entering the details for the distribution centre. 
• Deletion - E.g. removing the final member of staff working for a distribution centre would leave no record of that centre.
• Modification - E.g. all staff need their records updating in a single table form, which can lead to inconsistant data if their are input errors or if you have failed to modify all records.

The process of normalisation involves a series of tests on a table to check whether it satisfies/violates the rules given for a normal form. 

There are 3 commonly used normal form tests:

• First normal form (1NF) - most critical and only required NF
• Second normal form (2NF) - only required when primary key made up of more than 1 column
• Third normal form (3NF)

1NF is the only NF that needs to be satisfied in order to create appropriate tables

Specifies the rule that the intersecion of every and row in a table can contain only one value

2NF only applies to table with composite primary keys i.e. primary key is composed of two or more columns.

2NF table is seen as a table in 1NF and in which the values of each non-primary key column are determined by the values in all the columns that make up the primary key.

To see if the table breaks the 2NF form or not requires identification of the primary key and functional dependencies related with that table

A functional dependency is when an instance of 'a' (primary key column) uniquely identifies a particular instance of 'b'

Tables in 1NF or 2NF may be determined from only the primary key columns(s) and no other columns.

3NF = A table that is in 1NF and 2NF and in which no non-primary-key column is transitively dependent on the primary key.

Transistive dependency = Describes a relationship between columns a, b and c. For example, if a determines b and b determines c, then c is transitively dependent on a via b.

They occur when a non-primary-key column is functionally dependent on another column of the same type.