4 Normal FormOverviewDatabase NormalizationFirst Normal Form (1 NF)1st Normal Form ExampleSecond Normal Form (2 NF)2nd Normal Form ExampleThird Normal Form (3 NF)transitive dependencySlide 10Boyce-Codd Normal Form (BCNF)BCNF ExampleBCNF Example (Cont...)4th Normal Form (4 NF)Definition of MVDMVD ExampleSlide 174 NF Example4 NF Example (conti...)Slide 20Slide 21Not 4 NF ExampleNot 4 NF Example (conti...)Slide 24Conclusion of Steps in NormalizationReferences4 Normal FormNathanael ChowCS 157AFall 2006 Dr. LeeOverview ●1 NF●2 NF●3 NF●BCNF●4 NF ●ConclusionDatabase Normalization ●Each data in the database should represent once●Purpose of normalization: to eliminate insert, update, and delete anomaliesFirst Normal Form (1 NF)●All values in the columns are atomic (simple, indivisible). This is, they contain no repeating values.●There are no repeating groups: two columns do not store similar information in the same table.●Basically: 1 NF is to eliminate duplicate columns1st Normal Form ExampleUn-normalized Students table:Normalized Students table:102-8209-0467Smith123B124104-9102-8555James123A123Class2Class1AdvRoomAdvNameAdvIDStudent#209-0467Smith123B124104-9555James123A123102-8467Smith123B124102-8555James123A123Class#AdvRoomAdvNameAdvIDStudent#Second Normal Form (2 NF)●A relation is in 2 NF if it is in 1 NF and every non-key attribute is fully functionally dependant on the primary key2nd Normal Form ExampleStudents tableRegistration table467Smith123B124555James123A123AdvRoomAdvNameAdvIDStudent#209-0124104-9123102-8124102-8123Class#Student#Third Normal Form (3 NF)●A relation is in 3 NF if it is 2 NF and no transitive dependencies exist. ●Transitive dependency is a functional dependency between non-key attribute●Basically: 3 NF is to eliminate column not depend upon the primary key.transitive dependency Region Salesperson Name Cust_IDtransitivedependencySalesperson Name Cust_ID RegionSalesperson Region Salesperson Name Cust_IDBoyce-Codd Normal Form (BCNF)●A relation is in BCNF if it is in 3 NF and every determinant is a candidate key; in other words, each determinant can be used as a primary key. ●Determinant: an attribute on which some other attribute is fully functionally dependentEx: A --> B (A is called the determinant)BCNF Example●Given: R (A, B, C , D)●A --> B, C, D ●B --> A, C, D●C --> A , B, D●D --> A, B, CBCNF Example (Cont...)●Determinants:A, B, C, and D●Candidate keys:A, B, C, and D●Since all the determinants are candidate keys, this is BCNF.4th Normal Form (4 NF)●A relation is a 4 NF if it is BCNF and There is no multivalued dependency in the relation orThere are multivalued dependency but the attributes, which are multivalued dependent on a specific attribute, are dependent between themselves●What is a multivalued dependency (MVD)?Definition of MVD ●A multivalued dependency X->-> Y is an assertion that if two tuples of a relation agree on all the attributes of X, then their components in the set of attributes Y may be swapped, and the result will be two tuples that are also in the relationMVD ExampleX Y ZA B1 C1A B2 C2X Y ZA B1 C1A B2 C2X ->-> YR(x, y, z)MVD ExampleX Y ZA B1 C1A B2 C2X Y ZA B1 C1A B2 C2X ->-> YA B2 C1A B1 C2R(x, y, z)4 NF ExampleSingingArabic200CookingEnglish200CookingFrench100PoliticKurdish100TeachingEnglish 100SkillLanguageEidAssume the following relation:Employee (Eid:pk1, Language:pk2, Skill:pk3)4 NF Example (conti...)SingingArabic200CookingEnglish200CookingFrench100PoliticKurdish100TeachingEnglish 100SkillLanguageEidRecall that a relation is in BCNF if all its determinant are candidate keys. Because relation Employee has only one determinant (Eid, Language, Skill), which is the composite primary key.Since the primary is a candidate key, R is in BCNF.Assume that there is no MVD, then this relation is 4 NF.4 NF Example (conti...)Assume the following relation with multi-value dependency:4Employee (Eid:pk1, Languages:pk2, Skills:pk3) Eid --->> Languages Eid --->> SkillsLanguages and Skills are dependent.This says an employee speaks several languages and has several skills. However for each skill, a specific language is used when that skill is practiced.4 NF Example (conti...)Thus employee 100 when she teaches, she uses English; but when she cooks, she uses French. This relation is in fourth normal form.SingingArabic200CookingEnglish200CookingFrench100PoliticKurdish100TeachingEnglish 100SkillLanguageEidNot 4 NF ExampleAssume the following relation with multivalued dependency: Employee (Eid:pk1, Languages:pk2, Skills:pk3) Eid --->> Languages Eid --->> SkillsLanguages and Skills are independent.Not 4 NF Example (conti...)SingingArabic200CookingFrench100PoliticKurdish100TeachingEnglish 100SkillLanguageEidInsertion anomaly: To insert row (200 English Cooking) we have to insert two extra rows (200 Arabic cooking), and (200 English Singing) otherwise the database will be inconsistent.SingingEnglish200CookingArabic200CookingEnglish200SingingArabic200TeachingKurdish100PoliticsEnglish100PoliticsKurdish100TeachingEnglish100SkillLanguageEidNot 4 NF Example (conti...)Here is the table after the insertion:Conclusion of Steps in Conclusion of Steps in
View Full Document
Unlocking...