Part 1 What does normalisation solve

Goal: to minimise redundant data, and prevent anomalies

It is also needed when mapping classes into a database

Update Anomalies

Tables with redundant data may have problems.

Deletion

The data for where the supplier address may only be on that row, so if we delete it, we will lose the data that we want to keep.

Insert

Inserted data maybe inconsistent with data that is already in the table.

What does the data mean?

Modification

Data is inconsistent since all rows have not been updated.

Part 2 Normal Forms

Normal Form - how to normalise using functional dependencies and determinacy diagrams

Normal Form - set of rules that prevent inconsistencies, by removing redundancies

Each higher level adds another constraint to the data, this does come at the cost of slower queries.

First Normal Form

where a primary key is made for each unique row of data

each cell must have a single value

manager is functionally dependent on name

Many to one - is allowed

One to Many - is not allowed

Many to many is not allowed

Determinacy Diagram

Product name and equipment name determine supplier

Product name determines manager

Primary key is the box

Second Normal Form

Part (primary) key dependency

Each non-primary key, must fully depend on a primary key (product manager does not follow this rule)

Only an issue if the PK is made up of a combination of cols

Must split part key dependencies into a new table

Third Normal Form

Removes transitive dependency

transitive dependency - when a column is dependent on a column that is not the primary key

To fix it just put the column in a new table