California State University, Northridge



IS 441 Week 8 Class Summary and HighlightsDr. Yue Zhang; revised October 30, 2016Class outline:Normalization solution process; Analysis of func dependencies;Correction of 2NF/3NF violations;Exam review: ERD; Chap 4 HW problem designation.Normalization Solution Process (“Big Steps”)Read, understand, examine data.Result: Functional dependencies in 1NF (“Big Table”)Details of this step is in the Main Contents Part II - “Analysis of functional dependencies”.3648075130175There is ALWAYS ONE FD; there does not have to be PD or TD0There is ALWAYS ONE FD; there does not have to be PD or TDApply normalization procedure on the relation with the functional dependencies identified in Part I.Result: Removal of PD - relations in 2NF Removal of TD – relations in 3NF – separate, smaller, “clean” relations/tables.Normalization procedure: details in Part III – “Correction of 2NF/3NF violations”.Analysis of Functional Dependencies (Take “Normalization Example 1” as example)1. Use common sense, identify “easy” functional dependencies, such as EmpID EmpLName, and ProjID PName, etc.In the remaining possible dependencies, first find repetitive values among a POSSIBLE determinant (“Determinant”, usually the determinants found in “1” above), examine the values of a POSSIBLE determinee (“Determinee”), to see whether the same value of the Determinant leads to the same value of a Determinee, and different Determinant values lead to different Determinee values. If yes, then there is a functional dependency between this pair of Determinant and Determinee; if not, move on to the next possible Determinee. Repeat the procedure for all POSSIBLE Determinants and Determinees.Result: PD (partial dependencies) and TD (transitive dependencies) are usually found directly in this step.Identifying full dependency in the case of composite primary keyIn conducting “2” above, *IF* there is a composite primary key (key that consists of two or more fields), pay attention to the special kind of Determinees whoDoes not depend on Determinant 1, ANDDoes not depend on Determinant 2.In this case, verify that Determinant 1 and Determinant 2 jointly determine specific determinee(s), if so, that functional dependency is the Full Dependency – the determine(s) depend(s) on the full key.right160655There is ALWAYS ONE and ONLY one FD00There is ALWAYS ONE and ONLY one FD***NOTE 1: ALL relations have ONE full dependency – cannot be more than one, cannot be no full dependency.3952875192405There does NOT have to be a PD00There does NOT have to be a PD*** Note 2: A full dependency does NOT have to have a composite key: a single field as the primary key of course fully determines the non-key fields depending on it. Normalization Procedure: Correction of 2NF/3NF violations Give every functional dependency its own “independence”: EVERY functional dependency becomes its own relation/table;Remove from the original relation the determinees in those violating functional dependencies.This applies to both 2NF and 3NF.Normalization to reach 2NF:581024184140017145088900021145502794000160019888900192405025400F00F153352525400E00E114300025400D00D76200025400C00C39052525400B00Bleft25400A00A9621085640500132397566675173355066675190500666755810256667558044568552001904996984900In the above relation, The purple is FD;The red is PD (since it establishes a relationship between the determinant A – which is PART of the COMPOSITE KEY - and the non-key fields D and E);The blue determinant is also PD (since it establishes a relationship between the determinant B – which is PART of the COMPOSITE KEY - and the non-key field C);Apply the above procedure:Give every functional dependency its own “independence”: EVERY functional dependency becomes its own relation/table;Red:153352525400E00E114300025400D00Dleft25400A00A132397566675173355066675190500666753236181145470So the 2NF consists the following relations – the Full, and the Partial dependencies:0So the 2NF consists the following relations – the Full, and the Partial dependencies:1904996984900143918513688400Blue:76200025400C00C39052525400B00B9715504762558102566675581025635002695491513500Remove from the original relation the determinees in those violating functional dependencies.16383001206501247775120650876300130175581024184140017145088900021145502794000160019888900192405025400F00F153352525400E00E114300025400D00D76200025400C00C39052525400B00Bleft25400A00A239077510160becomes0becomes132397566675173355066675190500666759715504762558102566675581025635001904996984900 581024184140017145088900021145502794000160019888900192405025400F00F39052525400B00Bleft25400A00ANormalization to reach 3NF (a NEW example, unrelated with the above one for 2NF):581024184140017145088900021145502794000160019888900192405025400R00R153352525400Q00Q114300025400P00P76200025400O00O39052525400N00Nleft25400M00M981075666750020764505715000173354966675001323974666750010001246350100In the above relation, The purple func dep is Full;The red func dep is Transitive (since it establishes a relationship between the determinant R [Non-key!!] and the non-key fields O, P and Q).Apply the above procedure:Give every functional dependency its own “independence”: EVERY functional dependency becomes its own relation/table;Red:192405025400R00R153352525400Q00Q114300025400P00P76200025400O00O981075666750020764505715000173354966675001323974666750010001257620000Remove from the original relation the determinees in those violating functional dependencies.166687518288089535017335558102418414001714508890002114550279400016001988890012668258255192405025400R00R153352525400Q00Q114300025400P00P76200025400O00O39052525400N00Nleft25400M00M27750067206Becomes 00Becomes 9810756667500207645057150001733549666750013239746667500100012463501003NF:581024184140017145088900021145502794000160019888900192405025400R00R39052525400N00Nleft25400M00M2133600635001609725117475Referential integrity constraintReferential integrity constraint192405025400R00R153352525400Q00Q114300025400P00P76200025400O00O981075666750020764505715000173354966675001323974666750010001257620000 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download