database management - boyce-codd normalization
TRANSCRIPT
-
8/7/2019 Database Management - Boyce-Codd Normalization
1/18
BoyceBoyce--Codd NFCodd NFTakahiko SaitoTakahiko Saito
Spring 2005Spring 2005CS 157ACS 157A
-
8/7/2019 Database Management - Boyce-Codd Normalization
2/18
Purpose of NormalizationPurpose of Normalization
To reduce the chances for anomaliesTo reduce the chances for anomaliesto occur in a database.to occur in a database.
normalizationnormalization prevents the possibleprevents the possiblecorruption of databases stemmingcorruption of databases stemming
from what are called "insertionfrom what are called "insertionanomalies," "deletion anomalies," andanomalies," "deletion anomalies," and"update anomalies.""update anomalies."
-
8/7/2019 Database Management - Boyce-Codd Normalization
3/18
Insertion AnomalyInsertion Anomaly
A failure to place a new databaseA failure to place a new databaseentry into all the places in theentry into all the places in thedatabase where that new entry needsdatabase where that new entry needsto be stored.to be stored.
In a properly normalized database, aIn a properly normalized database, anew entry needs to be inserted intonew entry needs to be inserted into
only one place in the databaseonly one place in the database
-
8/7/2019 Database Management - Boyce-Codd Normalization
4/18
Deletion AnomalyDeletion Anomaly
A failure to remove an existingA failure to remove an existingdatabase entry when it is time todatabase entry when it is time toremove that entry.remove that entry.
In a properly normalized database,In a properly normalized database,
an old, toan old, to--bebe--gottengotten--ridrid--of entryof entryneeds to be deleted from only oneneeds to be deleted from only oneplace in the databaseplace in the database
-
8/7/2019 Database Management - Boyce-Codd Normalization
5/18
Update anomalyUpdate anomaly
An update of a database involvesAn update of a database involvesmodifications that may be additions,modifications that may be additions,
deletions, or both. Thus "updatedeletions, or both. Thus "updateanomalies" can be either of the kinds ofanomalies" can be either of the kinds ofanomalies discussed above.anomalies discussed above.
-
8/7/2019 Database Management - Boyce-Codd Normalization
6/18
1st Normal Form1st Normal Form
A table (relation) is in 1NF ifA table (relation) is in 1NF if1. There are no duplicated rows in the1. There are no duplicated rows in the
table.table.
2. Each cell is single2. Each cell is single--valuedvalued
3. Entries in a column are of the same3. Entries in a column are of the samekind.kind.
-
8/7/2019 Database Management - Boyce-Codd Normalization
7/18
2nd Normal Form2nd Normal Form
A table is in 2NF if it is in 1NF and if allA table is in 2NF if it is in 1NF and if allnonnon--key attributes are fully dependent onkey attributes are fully dependent on
each candidate key.each candidate key.
A partial dependency occurs when a nonA partial dependency occurs when a non--key attribute is dependent on only a partkey attribute is dependent on only a part
of the (composite) keyof the (composite) key
-
8/7/2019 Database Management - Boyce-Codd Normalization
8/18
1NF but not 2NF1NF but not 2NF
Supplier (Supplier (supplier#,supplier#, status, city,status, city, part#,part#,quantity)quantity)
(supplier#, part#)(supplier#, part#) --> quantity> quantity supplier#supplier# --> status> status
supplier#supplier# --> city> city
citycity --> status> status
=> status and city are dependent on just=> status and city are dependent on justpart of the key, namely supplier#.part of the key, namely supplier#.
-
8/7/2019 Database Management - Boyce-Codd Normalization
9/18
1NF but not 2NF (cont1NF but not 2NF (contd)d)
Decomposition (into 2NF):Decomposition (into 2NF):Supplier (supplier#, status, city)Supplier (supplier#, status, city)
Supplier_Part (supplier#, part#,Supplier_Part (supplier#, part#,quantity)quantity)
-
8/7/2019 Database Management - Boyce-Codd Normalization
10/18
3rd Normal Form (3NF)3rd Normal Form (3NF)
A table is in 3NF if it is in 2NF and if it hasA table is in 3NF if it is in 2NF and if it hasno transitive dependencies.no transitive dependencies.
Transitive dependency is a functionalTransitive dependency is a functionaldependency between nondependency between non--key attributes.key attributes.
-
8/7/2019 Database Management - Boyce-Codd Normalization
11/18
2NF but not 3NF2NF but not 3NF
Supplier (supplier#, status, city)Supplier (supplier#, status, city) supplier#supplier# --> status> status
supplier#supplier# --> city> city
citycity --> status> status
=> Lacks mutual independence among non=> Lacks mutual independence among non--keykey
attributes.attributes.
-
8/7/2019 Database Management - Boyce-Codd Normalization
12/18
2NF but not 3NF (cont2NF but not 3NF (contd)d)
Decomposition (into 3NF):Decomposition (into 3NF): SUPPLIER_CITY (supplier#, city)SUPPLIER_CITY (supplier#, city)
CITY_STATUS (city, status)CITY_STATUS (city, status)
-
8/7/2019 Database Management - Boyce-Codd Normalization
13/18
BoyceBoyce--Codd NFCodd NF
A table is in BCNF if it is in 3NF and ifA table is in BCNF if it is in 3NF and ifevery determinant is a candidate key.every determinant is a candidate key.
the definition of 3NF does not deal with athe definition of 3NF does not deal with arelation that:relation that:
has multiple candidate keys, wherehas multiple candidate keys, where
Those candidate keys are composite, andThose candidate keys are composite, and the candidate keys overlap (i.e., have at leastthe candidate keys overlap (i.e., have at least
one common attribute)one common attribute)
-
8/7/2019 Database Management - Boyce-Codd Normalization
14/18
3NF but not boyce3NF but not boyce--codd NFcodd NF
SUPPLIER_PART (supplier#, supplier_name,SUPPLIER_PART (supplier#, supplier_name,part#, quantity)part#, quantity) Two candidate keys:Two candidate keys:
(supplier#, part#) and (supplier_name, part#)(supplier#, part#) and (supplier_name, part#) (supplier#, part#)(supplier#, part#) --> quantity> quantity
(supplier#, part#)(supplier#, part#) --> supplier_name> supplier_name
(supplier#, part#)(supplier#, part#) --> quantity> quantity
(supplier#, part#)(s
upplier#, part#) --> s
upplier#> s
upplier# supplier_namesupplier_name --> supplier#> supplier#
supplier#supplier# --> supplier_name> supplier_name
-
8/7/2019 Database Management - Boyce-Codd Normalization
15/18
Another example of boyceAnother example of boyce--codd NFcodd NF
titletitle yearyear lengthlength filmTypefilmType studioNamstudioNamee
starNamestarName
Star WarsStar Wars 19771977 124124 colorcolor FoxFox FisherFisher
Star WarsStar Wars 19771977 124124 colorcolor FoxFox HamillHamill
Star WarsStar Wars 19771977 124124 colorcolor FoxFox FordFord
Mighty DucksMighty Ducks 19911991 104104 colorcolor DisneyDisney EstevesEsteves
WayneWaynes Worlds World 19921992 9595 colorcolor ParamountParamount CarveyCarvey
WayneWaynes Worlds World 19921992 9595 colorcolor ParamountParamount MeyersMeyers
-
8/7/2019 Database Management - Boyce-Codd Normalization
16/18
Example (contExample (contd)d)
{title, year, starName} as candidate key{title, year, starName} as candidate key title, yeartitle, year length, filmType, studioNamelength, filmType, studioName The above FD (Functional Dependency)The above FD (Functional Dependency)
violates the BCNF condition becauseviolates the BCNF condition because titletitleand yearand year do not determine the sixthdo not determine the sixth
attribute, starNameattribute, starName
-
8/7/2019 Database Management - Boyce-Codd Normalization
17/18
Example (contExample (contd)d)
We solve this BCNF violation by decomposingWe solve this BCNF violation by decomposingrelation Movies intorelation Movies into
1. The schema with all the attributes of the F
D1. The schema with all the attrib
utes of the F
D
{title, year, length, filmType,{title, year, length, filmType,studioName}studioName}
2. The schema with all attributes of Movies2. The schema with all attributes of Movies
except the three that appear on the right ofexcept the three that appear on the right ofthe FDthe FD
{title, year, starName}{title, year, starName}
-
8/7/2019 Database Management - Boyce-Codd Normalization
18/18
Summary of BoyceSummary of Boyce--Codd NFCodd NF
When there is more than one candidate key, a relationalWhen there is more than one candidate key, a relationaltable may be in 3NF and anomalies may still result.table may be in 3NF and anomalies may still result.
This occurs when there is a composite primary key, andThis occurs when there is a composite primary key, and
there are two equally valid candidates to make up partthere are two equally valid candidates to make up partof this composite primary key. If there is an attributeof this composite primary key. If there is an attribute(one or more columns) on which any other attribute is(one or more columns) on which any other attribute isfullyfully dependent, and this attribute is NOT itself adependent, and this attribute is NOT itself acandidate key, then the table is not incandidate key, then the table is not in BoyceBoyce--CoddCoddNormal formNormal form (BCNF).(BCNF).
We fix this by breaking the table up into two tables, bothWe fix this by breaking the table up into two tables, bothin BCNF.in BCNF.