Normalisation to 3NF - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Normalisation to 3NF - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Normalisation to 3NF

Redundancy and Normalisation

• Redundant data
  • Can be determined from other data in the database
  • Leads to various problems - INSERT anomalies - UPDATE anomalies - DELETE anomalies - Normalisation - Aims to reduce data redundancy - Redundancy is expressed in terms of dependencies - Normal forms are defined that do not have certain types of dependency

Normalisation to 1NF

Unnormalised

Module Dept Lecturer Texts

M1 D1 L1 T1, T M2 D1 L1 T1, T M3 D1 L2 T M4 D2 L3 T1, T M5 D2 L4 T

1NF

Module Dept Lecturer Text M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

To convert to a 1NF relation, split up any

non-atomic values

Problems in 1NF

• INSERT anomalies
  • Can't add a module with no texts
• UPDATE anomalies
  • To change lecturer for M1, we have to change two rows
• DELETE anomalies
  • If we remove M3, we remove L2 as well

1NF

Module Dept Lecturer Text

M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

Example

• {ID, modCode} → {First, Last, modName}
• {modCode} → {modName}
• {ID} → {First, Last}

ID First Last modCode modName

111 Joe Bloggs G51PRG Programming

222 Anne Smith G51DBS Databases

FDs and Normalisation

• We define a set of

'normal forms'

• Each normal form has fewer FDs than the last
• Since FDs represent redundancy, each normal form has less redundancy than the last - Not all FDs cause a

problem

• We identify various sorts of FD that do
• Each normal form removes a type of FD that is a problem
• We will also need a way to remove FDs

FD Example

• The primary key is

{Module, Text} so

{Module, Text} → {Dept, Lecturer}

• 'Trivial' FDs, eg: {Text, Dept} → {Text} {Module} → {Module} {Dept, Lecturer} → { }

1NF

Module Dept Lecturer Text

M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

FD Example

• Other FDs are
  • {Module} → {Lecturer}
  • {Module} → {Dept}
  • {Lecturer} → {Dept}
  • These are non-trivial and determinants (left hand side of the dependency) are not keys.

1NF

Module Dept Lecturer Text

M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

Second Normal Form

• 1NF is not in 2NF
  • We have the FD {Module, Text} → {Lecturer, Dept}
  • But also {Module} → {Lecturer, Dept}
  • And so Lecturer and Dept are partially dependent on the primary key

1NF

Module Dept Lecturer Text

M1 D1 L1 T M1 D1 L1 T M2 D1 L1 T M2 D1 L1 T M3 D1 L2 T M4 D2 L3 T M4 D2 L3 T M5 D2 L4 T

Removing FDs

• Suppose we have a relation R with scheme S and the FD A → B where A ∩ B = { }
• Let C = S – (A U B)
• In other words:
  • A – attributes on the left hand side of the FD
  • B – attributes on the right hand side of the FD
  • C – all other attributes
    • It turns out that we can split R into two parts:
    • R1, with scheme C U A
    • R2, with scheme A U B
    • The original relation can be recovered as the natural join of R1 and R2:
    • R = R1 NATURAL JOIN R

Problems Resolved in 2NF

• Problems in 1NF
  • INSERT – Can't add a module with no texts
  • UPDATE – To change lecturer for M1, we have to change two rows
  • DELETE – If we remove M3, we remove L2 as well - In 2NF the first two

are resolved, but not

the third one

2NFa Module Dept Lecturer M1 D1 L M2 D1 L M3 D1 L M4 D2 L M5 D2 L

Problems Remaining in 2NF

• INSERT anomalies
  • Can't add lecturers who teach no modules
• UPDATE anomalies
  • To change the department for L1 we must alter two rows
• DELETE anomalies
  • If we delete M3 we delete L2 as well

2NFa Module Dept Lecturer M1 D1 L M2 D1 L M3 D1 L M4 D2 L M5 D2 L

Third Normal Form

• 2NFa is not in 3NF
  • We have the FDs {Module} → {Lecturer} {Lecturer} → {Dept}
  • So there is a transitive FD from the primary key {Module} to {Dept}

2NFa

Module Dept Lecturer

M1 D1 L M2 D1 L M3 D1 L M4 D2 L M5 D2 L

2NF to 3NF – Example

2NFa

Module Dept Lecturer

M1 D1 L M2 D1 L M3 D1 L M4 D2 L M5 D2 L

3NFa Lecturer Dept L1 D L2 D L3 D L4 D

3NFb Module Lecturer M1 L M2 L M3 L M4 L M5 L