Normal FormsOverviewNormalizationNormal FormFirst Normal Form (1NF)First Normal Form ExampleFirst Normal Form ExampleSlide 8First Normal FormSecond Normal Form (2NF)Second Normal Form ExampleSecond Normal Form Example1NF and 2NFThird Normal Form (3NF)Third Normal Form ExampleSlide 16Boyce-Codd Normal Form (BCNF)Boyce-Codd Normal Form ExampleOther Normal FormsLossless-Join DecompositionSlide 21Slide 22Slide 23Normal FormsNormal FormsBy Christopher ArchibaldBy Christopher ArchibaldOctober 16October 16thth 2007 2007OverviewOverviewDatabase NormalizationDatabase Normalization11stst Normal Form Normal Form22ndnd Normal Form Normal Form33rdrd Normal Form Normal FormBoyce- Codd Normal Form (BCNF)Boyce- Codd Normal Form (BCNF)Lossless-JoinLossless-JoinNormalizationNormalizationNormalization is a technique for designing Normalization is a technique for designing relational table to:relational table to:Minimize duplication of informationMinimize duplication of informationReduce the potential for data anomaliesReduce the potential for data anomaliesNormal FormNormal FormNormal forms provide a stepwise Normal forms provide a stepwise progression toward the goal of fully progression toward the goal of fully normalized relation schema that are free normalized relation schema that are free for data redundancies.for data redundancies.First Normal Form (1NF)First Normal Form (1NF)1NF definition: A schema R is in 1NF only 1NF definition: A schema R is in 1NF only when the attributes comprising the when the attributes comprising the schema are atomic and single-valuedschema are atomic and single-valuedNo Multi-valued attributesNo Multi-valued attributesNo composite attributesNo composite attributesNo repeating groups (2 columns can not store No repeating groups (2 columns can not store similar information)similar information)Can’t have a Null AttributeCan’t have a Null AttributeMust have a Primary KeyMust have a Primary KeyFirst Normal Form First Normal Form ExampleExampleThis is in 1NF (Has primary Key, no repeating This is in 1NF (Has primary Key, no repeating group, No Null attributes and No multivariablegroup, No Null attributes and No multivariableWhat happens if James gets a Second Phone What happens if James gets a Second Phone Number?Number?First Normal FormFirst Normal FormExampleExampleNo longer in 1NF because Telephone Number No longer in 1NF because Telephone Number has a multivariable.has a multivariable.Now we need to redesign our tableNow we need to redesign our tableFirst Normal FormFirst Normal FormExampleExampleNot in First Normal forumNot in First Normal forumTel. No. 3 is a null attributeTel. No. 3 is a null attributeTel. No. 1-2 repeat similar information (Repeating Tel. No. 1-2 repeat similar information (Repeating group)group)First Normal FormFirst Normal FormThis is in First Normal This is in First Normal FormFormTelephone Number is Telephone Number is no long a repeating no long a repeating groupgroupNo MultivariableNo MultivariableNo Null AttributesNo Null AttributesHas a Primary KeyHas a Primary KeySecond Normal Form (2NF)Second Normal Form (2NF)2NF Definition: A relation schema R is in 2NF Definition: A relation schema R is in 2NF if every non-prime attribute in R is 2NF if every non-prime attribute in R is fully functionally dependent on the primary fully functionally dependent on the primary key of R.key of R.Must be 1NFMust be 1NFAn Attribute that is not part of the candidate An Attribute that is not part of the candidate key must be dependent on the candidate key key must be dependent on the candidate key and not a part of the candidate keyand not a part of the candidate keySecond Normal FormSecond Normal FormExampleExampleOnly Candidate key is Only Candidate key is (Employee, Skill)(Employee, Skill)Not in 2NFNot in 2NFCurrent Work Location is Current Work Location is dependent on Employeedependent on EmployeeCan Cause an AnomalyCan Cause an Anomaly Updating Jones Work location for Typing and Shorthand but not Whittling. Then asking “What is Jones current work location”, can cause a contradictory answer, because there are 2 different locations.Second Normal Form Second Normal Form ExampleExampleBoth tables are in Both tables are in 2NF2NFMeets 1NF Meets 1NF requirementsrequirementsNo non-primary key No non-primary key attribute is dependent attribute is dependent on part of a key.on part of a key.1NF and 2NF1NF and 2NF1NF and 2NF remove most anomalies1NF and 2NF remove most anomaliesFollowing table is in 2NFFollowing table is in 2NFThere is redundancy under Winner/Winner DoBThere is redundancy under Winner/Winner DoBAl Fredrickson and Chip MastersonAl Fredrickson and Chip MastersonCan cause an anomalyCan cause an anomalyThird Normal Form (3NF)Third Normal Form (3NF)3NF Definition: A relation schema R is in 3NF Definition: A relation schema R is in 3NF if no non-prime attribute is 3NF if no non-prime attribute is functionally dependent on another non-functionally dependent on another non-prime attribute in Rprime attribute in RTable must be in 2NFTable must be in 2NFEliminate field that do not depended on the Eliminate field that do not depended on the primary key by placing them in different tablesprimary key by placing them in different tablesThird Normal FormThird Normal FormExampleExampleTable is in 2NF but fails to meet 3NFTable is in 2NF but fails to meet 3NFWinner Date of Birth is Dependent on WinnerWinner Date of Birth is Dependent on WinnerIf Al Fredrickson Date of birth is update in the first row If Al Fredrickson Date of birth is update in the first row but not the second ask, “What Al Fredrickson Date of but not the second ask, “What Al Fredrickson Date of birth” will result in 2 different dates.birth” will result in 2 different dates.Third Normal FormThird Normal FormExampleExampleTable is in 3NFTable is in 3NFMeets 1NF and 2NFMeets 1NF and 2NFNo non-primary Key No non-primary Key attribute is Dependent attribute is Dependent on another non-on another non-primary Key attributeprimary Key attributeUpdate Anomalies Update Anomalies cannot occur in these cannot occur in these tablestablesBoyce-Codd Normal Form (BCNF)Boyce-Codd Normal Form (BCNF)BCNF Definition: A relation Schema R is BCNF Definition: A relation Schema R is in BCNF if for every non-trivial functional in BCNF if for every non-trivial functional dependency in R, the determinant is a dependency in R, the determinant
View Full Document
Unlocking...