boyce-codd normal form kelvin nishikawa se157a-03 fall 2006 kelvin nishikawa se157a-03 fall 2006
Post on 15-Jan-2016
228 views
TRANSCRIPT
![Page 1: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/1.jpg)
Boyce-Codd Normal Form
Kelvin NishikawaSE157a-03Fall 2006
![Page 2: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/2.jpg)
OverviewOverview
• 1NF, 2NF, and 3NF• BCNF• Conclusion
• 1NF, 2NF, and 3NF• BCNF• Conclusion
![Page 3: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/3.jpg)
Database NormalizationDatabase Normalization
• Each datum in the database should represented once.
• Purpose is to eliminate update, insert, and deletion anomalies.
• Normal Forms are increasingly strict subset of 1NF.
• Each datum in the database should represented once.
• Purpose is to eliminate update, insert, and deletion anomalies.
• Normal Forms are increasingly strict subset of 1NF.
![Page 4: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/4.jpg)
First Normal FormFirst Normal Form
• Eliminate Repeating Groups• Require uniquely identified rows
• Eliminate Repeating Groups• Require uniquely identified rows
![Page 5: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/5.jpg)
First Normal formFirst Normal form
• 1 girl per row• 2 Pamelas are uniquely identifiable.
• 1 girl per row• 2 Pamelas are uniquely identifiable.
![Page 6: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/6.jpg)
Second Normal FormSecond Normal Form
• Eliminate Redundant Data• Eliminate Redundant Data
![Page 7: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/7.jpg)
Second Normal FormSecond Normal Form
• Non-key columns are dependent on all columns of the primary key.
• Non-key columns are dependent on all columns of the primary key.
![Page 8: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/8.jpg)
2NF/3NF2NF/3NF
• A Relation without non-key attributes is 2NF and 3NF.
• A Relation without non-key attributes is 2NF and 3NF.
![Page 9: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/9.jpg)
Third Normal FormThird Normal Form
• Eliminate Columns Not Dependent On Key
• i.e. if a column is in a relation, then it must be dependent on the key.
• Eliminate Columns Not Dependent On Key
• i.e. if a column is in a relation, then it must be dependent on the key.
![Page 10: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/10.jpg)
Third Normal FormThird Normal Form
• Move non-key-dependent attributes to a new table.
• Move non-key-dependent attributes to a new table.
![Page 11: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/11.jpg)
Boyce-Codd Normal Form (BCNF)
Boyce-Codd Normal Form (BCNF)
• 3NF, but…• All functional dependencies imply
the only whole key.• "The key, the whole key, and nothing
but the key, so help me Codd."
• 3NF, but…• All functional dependencies imply
the only whole key.• "The key, the whole key, and nothing
but the key, so help me Codd."
![Page 12: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/12.jpg)
Identifying (non)BCNFIdentifying (non)BCNF
• A Relation can be 3NF and not BCNF if:• There are multiple candidate keys• The keys are composed of multiple
attributes• There are common attributes
between the keys.
• A Relation can be 3NF and not BCNF if:• There are multiple candidate keys• The keys are composed of multiple
attributes• There are common attributes
between the keys.
![Page 13: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/13.jpg)
Example of 3NF and BCNFExample of 3NF and BCNF
Given: R(A,B,C,D,E) AB –> CAB –> DAB –> EDE –> CDE –> ADE –> B
With the key AB
Given: R(A,B,C,D,E) AB –> CAB –> DAB –> EDE –> CDE –> ADE –> B
With the key AB
• Holds true for 3NF• All left hands of
functional dependencies are candidate keys.
• Breaks for BCNF• There exist
dependencies between candidate keys.
• Holds true for 3NF• All left hands of
functional dependencies are candidate keys.
• Breaks for BCNF• There exist
dependencies between candidate keys.
![Page 14: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/14.jpg)
SummarySummary
• BCNF if:• No dependencies
between candidate keys.
• 3NF
• BCNF if:• No dependencies
between candidate keys.
• 3NF
• 3NF• No attributes
dependent on non-keys.
• 2NF• Attributes
dependent on all parts of the key
• 1NF• No repeating
groups• Each row uniquely
identifiable.
• 3NF• No attributes
dependent on non-keys.
• 2NF• Attributes
dependent on all parts of the key
• 1NF• No repeating
groups• Each row uniquely
identifiable.
![Page 15: Boyce-Codd Normal Form Kelvin Nishikawa SE157a-03 Fall 2006 Kelvin Nishikawa SE157a-03 Fall 2006](https://reader030.vdocuments.us/reader030/viewer/2022033101/56649d4b5503460f94a28e56/html5/thumbnails/15.jpg)
Questions?Questions?