Normalisation to 3NF
Redundancy and Normalisation
Redundant data Normalisation
• The repetition of the same data • Aims to reduce data redundancy
field/attribute within the database. This may
happen either across a number of tables in • 1NF
the database or within the same table.
• Lead to various problems • 2NF
• INSERT anomalies (where do I need to insert
data)
• 3NF
• UPDATE anomalies (which pieces of data do I
need to update)
• DELETE anomalies (I may miss deleting all
occurrences of the data)
• Duplication of data so I am storing more data
than I need to
1NF – First Normal Form
• A table is in 1NF if all of the data values are atomic
• Atomic data is single entries, not sets or composite objects
Problems that can still occur in 1NF
• INSERT anomalies
• Can’t add a module with no text
• Update anomalies
• To change the lecturer for M1, we have to change it in two places
• Delete anomalies
• If we delete M3 we remove the details for L2 as well
2NF – Second Normal Form
• A table is in 2NF if the table is in 1NF AND all the non-key columns are
dependent on the table’s primary key.
• At this point we are putting in primary keys (and potentially splitting
the data into different tables)
• Remember a primary key uniquely identifies each record in a table
2NF
• 1NF is not in 2NF
• We have the dependcey of
Module, Text 🡪 Lecturer, Dept
• But also
• Module 🡪Lecturer, Dept
• So Lecturer and Dept are
partially dependent on the
primary key
Moving to 2NF
• Once you identify a table’s purpose,
then look at each of the table’s
columns and ask yourself, “Does this
column serve to describe what the
primary key identifies?”
• If you answer “yes,” then the
column is dependent on the primary
key and belongs in the table.
• If you answer “no,” then the column
should be moved different table.
Problems resolved in 2NF
• In 1NF we had these problems • In 2NF the first two are resolved,
• INSERT anomalies but not the third one
• Can’t add a module with no text
• Update anomalies
• To change the lecturer for M1, we
have to change it in two places
• Delete anomalies
• If we delete M3 we remove the
details for L2 as well
Problems remaining in 2NF
• INSERT anomalies
• Can’t add lectures who teach no
modules
• Update anomalies
• To changes the department for L1
we must alter two rows
• Delete anomalies
• If we delete M3 we remove the
details for L2 as well
3NF
• A table is in 3NF if it is in 2NF AND it contains only columns that are
non-transitively dependent on the primary key
Transitive
• When something is transitive, then a meaning or relationship is the same in
the middle as it is across the whole. If it helps think of the prefix trans as
meaning “across.” When something is transitive, then if something applies
from the beginning to the end, it also applies from the middle to the end.
• Since ten is greater than five, and five is greater than three, you can infer that
ten is greater than three.
• In this case, the greater than comparison is transitive. In general, if A is
greater than B, and B is greater than C, then it follows that A is greater than C.
• If you’re having a hard time wrapping your head around “transitive” I think for
our purpose it is safe to think “through” as we’ll be reviewing to see how one
column in a table may be related to others, through a second column.
Dependence
• An object has a dependence on another object when it relies upon it.
In the case of databases, when we say that a column has a
dependence on another column, we mean that the value can be
derived from the other. For example, my age is dependent on my
birthday.
Transitive Dependence
• Now let’s put the two words together to formulate a meaning for transitive
dependence that we can understand and use for database columns.
• I think it is simplest to think of transitive dependence to mean a column’s value
relies upon another column through a second intermediate column.
• Consider three columns: AuthorNationality, Author, and Book. Column values for
AuthorNationality and Author rely on the Book; once the book is known, you can
find out the Author or AuthorNationality. But also notice that the AuthorNationality
relies upon Author. That is, once you know the Author, you can determine their
nationality. In this sense then, the AuthorNationality relies upon Book, via Author.
This is a transitive dependence.
Some examples
Primary Key (PK) Column A Column B Transitive Dependence?
No, In Western cultures a person’s last name is based on
PersonID FirstName LastName their father’s LastName, whereas their FirstName is given
to them.
Yes, BMI over 25 is considered [Link] wouldn’t
PersonID BodyMassIndex IsOverweight make sense to have the value IsOverweight be true when
the BodyMassIndex was < 25.
No:There is no direct link between the weight of a person
PersonID Weight Sex
and their sex.
Yes:Manufacturers make specific models. For instance,
VehicleID Model Manufacturer Ford creates the Fiesta; whereas, Toyota manufacturers
the Camry.
3NF
• Why is table 2NFa not in 3NF?
• Nope
• We have the Dependicies of
• Module 🡪 Lecturer
• Lecturer 🡪 Module
• So there is a transitive dependency from the primary key Module to
Dept
2NF to 3NF
Problems resolved in 3NF
• INSERT anomalies
• Can’t add lectures who teach no
modules
• Update anomalies
• To changes the department for L1
we must alter two rows
• Delete anomalies
• If we delete M3 we remove the
details for L2 as well
I can’t remember all of these steps!!!!!!
• When you are given a non-3NF database
• Think will inserting, updating or deleting any data lead to problems
• Try to move the data into different tables until you have no problems
• Done