Normalisation to BCNF
Database Systems
Dr. Jihan A. Rasool
Last Friday’s Hit
• The vital topic of Normalisation
– Data redundancy
– Functional dependencies
– Normal forms
– First, Second, and Third Normal Forms
• For more information(references)
– Connolly and Begg chapter 13
Repeat: Mornings are fun
• Today, more normalisation
– Relations with multiple candidate keys
– FD’s, 2NF and 3NF better explained
• For more info:
– Connolly and Begg chapter 14
1. Normalization refresher
• Normalization reduces data redundancy in a database
• By doing so it eliminates serious manipulation anomalies.
• Normalization is ultimately just rearranging propositions to a
better structure.
• This is done by identifying and removing damaging functional
dependencies.
What on google’s green earth was
an FD again?
• In a table, a set of columns X, functionally determines
another column Y…
X→Y
… if and only if each X value is associated with at most one
Y value in a table.
• i.e. if you know X then there is only one
possibility for Y.
Normal forms so Far…
• First normal form
– All data values are atomic, • Third normal form
and so everything fits into a
– As 2NF plus no non-primary-
mathematical relation.
key attribute depends
transitively on the primary
• Second normal form key.
– As 1NF plus no non-primary- – Transitive dependency
key attribute is partially
dependant on the primary
key
2. Normalization Example
• Consider a table • Columns
representing orders in an – Order
online store – Product
– Customer
– Address
• Each entry in the table
– Quantity
represents an item on a
– UnitPrice
particular order. (thinking in
terms of records. Yuk.)
• Primary key is {Order,
Product}
Functional Dependencies
Each order is for a single customer {Order} {Customer}
Each customer has a single address {Customer} {Address}
Each product has a single price {Product} {UnitPrice}
FD’s 1 and 2 are transitive {Order} {Address}
Example – FD Diagram
1NF
R
Order Product Customer Address Quantity UnitPrice
Normalisation to 2NF
• Remember 2nd normal form means no partial dependencies on the
key. But we have:
{Order} {Customer, Address}
{Product} {UnitPrice}
And a primary key of: {Order, Product}
• So to get rid of the first FD we project over:
{Order, Customer, Address}
and
{Order, Product, Quantity and UnitPrice}
Normalisation to 2NF
1NF
R
Order Product Customer Address Quantity UnitPrice
R1 Order Customer Address
R2 Order Product Quantity UnitPrice
Normalisation to 2NF
• R1 is now in 2NF, but there is still a partial FD in R2:
{Product} {UnitPrice}
Order Product Quantity UnitPrice
• To remove this we project over:
{Product, UnitPrice} and {Order, Product, Quantity}
Normalisation to 2NF
1NF R2
Order Product Quantity UnitPrice
2NF
R3 R4
Product UnitPrice Order Product Quantity
Now let’s go 3NF…
• R has now been split into 3 relations - R1, R3, and R4… but R1 has a
transitive FD on its key…
R1 Order Customer Address
{Order} {Customer} {Address}
• To remove this problem we project R1 over:
{Order, Customer} and {Customer, Address}
So more chopping…
2NF
R1 Order Customer Address
3NF
R5 Order Customer R6 Customer Address
Let’s summarize that:
• 1NF:
{Order, Product, Customer, Address, Quantity, UnitPrice}
• 2NF:
{Order, Customer, Address}
{Product, UnitPrice}
{Order, Product, Quantity}
• 3NF:
{Product, UnitPrice}
{Order, Product, Quantity}
{Order, Customer}
{Customer, Address}
So this…
0NF
R
Order Product Customer Address Quantity UnitPrice
has become this…
3NF
Prices Product UnitPrice
Amounts Order Product Quantity
Purchase Order Customer
Details Customer Address
