Slide 1Database NormalizationNormal FormsFirst Normal Form ( 1NF )Second normal Form ( 2NF )2NF Example2NF Example (Con’t)Third normal form3NF ExampleExampleSlide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Boyce-Codd normal form (BCNF)Slide 19Fourth normal form (4NF)4NF Example4NF Expample (con’t)Fifth normal form (5NF and also PJ/NF)Domain/key normal form(DKNF)Slide 25Sixth normal form(6NF)Database Database NormalizationNormalizationIl-Han YooCS 157AProfessor: Sin-Min Lee Database normalization relates to the level of redundancy in a relational database’s structure. The key idea is to reduce the chance of having multiple different version of the same data. Well-normalized databases have a schema that reflects the true dependencies between tracked quantities. Any increase in normalization generally involves splitting existing tables into multiple ones, which must be re-joined each time a query is issued.Database NormalizationDatabase NormalizationNormal FormsNormal Forms Edgar F. Codd originally established threEdgar F. Codd originally established three normal forms: 1NF, 2NF and 3NF.e normal forms: 1NF, 2NF and 3NF. 3NF is widely considered to be sufficien3NF is widely considered to be sufficient. t. Normalizing beyond 3NF can be tricky wiNormalizing beyond 3NF can be tricky with current SQL technology as of 2005th current SQL technology as of 2005Full normalization is considered a good eFull normalization is considered a good exercise to help discover all potential interxercise to help discover all potential internal database consistency problems.nal database consistency problems.First Normal Form ( 1NF )First Normal Form ( 1NF )““What is your favorite color?”What is your favorite color?”““What food will you not eat?”What food will you not eat?”TABLE 1TABLE 1 Person / Favorite ColorPerson / Favorite Color Bob / blue Bob / blue Jane / green Jane / green TABLE 2TABLE 2 Person / Foods Not EatenPerson / Foods Not Eaten Bob / okra Bob / okra Bob / brussel sprouts Bob / brussel sprouts Jane / peas Jane / peasSecond normal Form ( 2NF )Second normal Form ( 2NF ) 2NF prescribes full functional dependency on 2NF prescribes full functional dependency on the primary key.the primary key. It most commonly applies to tables that havIt most commonly applies to tables that have composite primary keys, where two or more composite primary keys, where two or more attributes comprise the primary key.e attributes comprise the primary key.It requires that there are no non-trivial functiIt requires that there are no non-trivial functional dependencies of a non-key attribute on onal dependencies of a non-key attribute on a part (subset) of a candidate key. A table is sa part (subset) of a candidate key. A table is said to be in the 2NF if and only if it is in the 1aid to be in the 2NF if and only if it is in the 1NF and every non-key attribute is irreducibly NF and every non-key attribute is irreducibly dependent on the primary key dependent on the primary key2NF Example2NF ExamplePART_NUMBER PART_NUMBER (PRIMARY KEY) (PRIMARY KEY) SUPPLIER_NAMESUPPLIER_NAME (PRIMARY KEY) (PRIMARY KEY) PRICE PRICE SUPPLIER_ADDRESS SUPPLIER_ADDRESS • The PART_NUMBER and SUPPLIER_NAME form the composite primary key.• SUPPLIER_ADDRESS is only dependent on the SUPPLIER_NAME, and therefore this table breaks 2NF.SUPPLIER_NAME (PRIMARY KEY) SUPPLIER_NAME (PRIMARY KEY) SUPPLIER_ADDRESS SUPPLIER_ADDRESS 2NF Example (Con’t)2NF Example (Con’t)•In order to find if a table is in 2NF, ask whether any of the non-key attributes of the table could be derived from a subset of the composite key, rather than the whole composite key. •If the answer is yes, it's not in 2NF. •This is solved sometimes by using a correlation file, such as the supplier table above.Third normal formThird normal form• 3NF requires that there are no non-trivial functional dependencies of non-key attributes on something other than a superset of a candidate key.• A table is in 3NF if none of the non-primary key attributes is a fact about any other non-primary key attribute. • In summary, all non-key attributes are mutually independent.3NF Example3NF ExamplePART_NUMBER (PRIMARY KEY) PART_NUMBER (PRIMARY KEY) MANUFACTURER_NAME MANUFACTURER_NAME MANUFACTURER_ADDRESS MANUFACTURER_ADDRESS MANUFACTURER_NAME (PRIMARY KEY) MANUFACTURER_NAME (PRIMARY KEY) MANUFACTURER_ADDRESS MANUFACTURER_ADDRESS PART_NUMBER (PRIMARY KEY) PART_NUMBER (PRIMARY KEY) MANUFACTURER_NAME MANUFACTURER_NAMEExampleExampleProblems ?1.Not very efficient with storage2.This design does not protect data integrity3.This table does not scale wellFirst Normal FormDefining Relationships•One to One•One to Many•Many to ManySecond Normal FormThird Normal Form•This new table violate Second Normal Form as the street and city will be verically redundant.•Province will need to be in its own table which the city table will refer to as a foreign key.Boyce-Codd normal form (BCNF)Boyce-Codd normal form (BCNF)• BCNF requires that there are no non-trivial functional dependencies of attributes on something other than a superset of a candidate key (called a superkey). • All attributes are dependent on a key, a whole key and nothing but a key (excluding trivial dependencies, like A->A).• A table is said to be in the BCNF if and only if it is in the 3NF and every non-trivial, left-irreducible functional dependency has a candidate key as its determinant. • In more informal terms, a table is in BCNF if it is in 3NF and the only determinants are the candidate keys.Fourth normal form Fourth normal form (4NF)(4NF)4NF4NF requires that there are no non-tr requires that there are no non-trivial multi-valued dependencies of attivial multi-valued dependencies of attribute sets on something else than a ribute sets on something else than a superset of a candidate key (called a superset of a candidate key (called a superkey).superkey). A table is said to be in 4NF if and onlA table is said to be in 4NF if and only if it is in the BCNF and multi-valued y if it is in the BCNF and multi-valued dependencies are functional dependedependencies are functional dependencies. ncies.4NF Example4NF ExampleEMPLOYEE_ID EMPLOYEE_ID QUALIFICATION_ID QUALIFICATION_ID TRAINING_COURSE_ID TRAINING_COURSE_ID employee_qualification table: EMPLOYEEemployee_qualification
View Full Document
Unlocking...