Normalisation to 3NFDatabase Systems Lecture 11Natasha Alechina ... - PDF document

Normalisation to 3NFDatabase Systems Lecture 11Natasha Alechina •Normalisation to 3NF•Data redundancy•Functional dependencies•Normal forms•First, Second, and Third Normal Forms•For more information•Connolly and Beggchapter 13•Ullmanand Widomch.3.6.6 (2 •Redundant data•Can be determined •Leads to various •INSERT anomalies•UPDATE anomalies•DELETE anomalies•Normalisation•Aims to reduce data •Redundancy is •Normal forms are •In most definitions •All data values should •This means that table •A relation is said to Normalisation to 1NFUnnormalisedModule Dept Lecturer TextsM2 D1 L1 T1, T3 1NFModule Dept Lecturer TextM1 D1 L1 T1M1 D1 L1 T2 To convert to a 1NF relation, split up any non-atomic values •INSERT anomalies•Can't add a module •UPDATE anomalies•To change lecturer for •DELETE anomalies•If we remove M3, we •Redundancy is often •A functional dependency •We can normalise a •A set of attributes, A, between A •{ID, modCode} •{modCode} •{ID} IDmodCodemodNameFirstLast 111G51PRGProgrammingJoeBloggs222G51DBSDatabasesAnneSmith FDsand Normalisation•We define a set of •Each normal form has fewer FDsthan the •Since FDsrepresent •Not all FDscause a •We identify various •Each normal form •We will also need a •In any relation•The primary key FDsis the primary key, •Same for candidate •Any set of attributes •Rules for FDs•Reflexivity: If •Augmentation: If •Transitivity: •The primary key is •'Trivial' FDs, eg:{Module} •Other FDsare•{Module} •{Module} {Dept}•{Lecturer} {Dept}•These are non-trivial Partial FDsand 2NF•Partial FDs:•A FD, is a partial •Formally, there is some •Let us call attributes •A relation is in second •In other words, no •1NF is not in 2NF•We have the FD•But also•And so Lecturer and •Suppose we have a •Let C = S –(A U B)•In other words:•A –attributes on the left •B –attributes on the •C –all other attributes•It turns out that we can •R1, with scheme C U A•R2, with scheme A U B•The original relation can •R = R1 NATURAL JOIN R2 1NF to 2NF –Example 2NFaModule Dept LecturerM1 D1 L1M2 D1 L1 2NFbModule TextM1 T1M1 T2 •Problems in 1NF•INSERT –Can't add a •UPDATE –To change •DELETE –If we •In 2NF the first two •INSERT anomalies•Can't add lecturers •UPDATE anomalies•To change the •DELETE anomalies•If we delete M3 we Transitive FDsand 3NF•Transitive FDs:•A FD, •We have•Third normal form •A relation is in third •2NFa is not in 3NF•We have the FDs{Lecturer}{Dept}•So there is a primary key {Module} 2NF to 3NF –Example 3NFaLecturer DeptL1 D1L2 D1 3NFbModule LecturerM1 L1M2 L1 •Problems in 2NF•INSERT –Can't add •UPDATE –To change •DELETE –If we delete •In 3NF all of these are resolved (for this relation –anomalies!) 3NFbModule LecturerM1 L1M2 L1 •Normalisation is •A database should •If your design leads to •When you find you •Identify the FDsthat •Think if they will lead •Try to remove them •More normalisation•Lossless decomposition; why our reduction •Boyce-Coddnormal form (BCNF)•Higher normal forms•Denormalisation•For more information•Connolly and Beggchapter 14•Ullmanand Widomchapter 3.6