b. information technology cmpb245: database design

B. Information TechnologyCMPB245:

Database Design

Normalization

Pt. 2

2

Objectives

Identify the Boyce-Codd Normal Form (BCNF)

Normalize a given table from Unnor-malized Form (UNF) to Boyce-Codd Nor-mal Form (BCNF)

The Boyce-CoddNormal Form

Objective 1

4

Boyce-Codd Normal Form(BCNF)

A relation is in BCNF– If and only if every determinant is a candidate key

Identify all the determinants Make sure they are candidate keys

– If this is true, then the relation is in BCNF

5

Boyce-Codd Normal Form(BCNF)

BCNF is violated when a relation– contains two or more composite candidate keys– that overlaps and share at least one common

attribute

6

CLIENT_INTERVIEWCLIENT_INTERVIEWCLIENT_INTERVIEWCLIENT_INTERVIEW

Client_NoClient_NoInterview_DateInterview_Date Interview_TimeInterview_Time Staff_NoStaff_No Room_NoRoom_No

CR76CR76 13-May-9513-May-95 10.3010.30 SG5SG5 G101G101



CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102






Boyce-Codd Normal FormExample

Consider the Client_Interview relation

7


Identify all the candidate keys, i.e. which will produce unique rows












8


By itself, all attributes cannot be a candidate key












9


(Client_No, Interview_Date) both identifies a unique row and is a candidate key -- (Set 1)












10


What about (Staff_No, Interview_Date) together?– No, because there are two rows












11


What about (Staff_No, Interview_ Date, Interview_ Time) together?– Yes, they can be another candidate key - (Set 2)












12


Since there are two sets of composite candidate key, then BCNF could be vio-lated

5555












13


Try use (Client_No, Interview_Date) as the primary key












14


Then, identify all functional dependen-cies Identify all the determinants












15


Client_No, Interview_DateClient_No, Interview_DateClient_No, Interview_DateClient_No, Interview_Date Interview_TimeInterview_TimeInterview_TimeInterview_TimeStaff_NoStaff_NoStaff_NoStaff_No Room_NoRoom_NoRoom_NoRoom_No

Staff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_Date Room_NoRoom_NoRoom_NoRoom_No

Staff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_Time Client_NoClient_NoClient_NoClient_No












16


The candidate keys overlap and share a common attribute Interview_Date

Hence, the BCNF is violated




Client_No, Interview_DateClient_No, Interview_DateClient_No, Interview_DateClient_No, Interview_Date

Staff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_TimeStaff_No, Interview_Date, Interview_Time

17


It is also not in BCNF– Due to the presence of the (Staff_No, Inter-

view_Date) determinant– Which is not a candidate key 4444

Staff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_Date


Room_NoRoom_NoRoom_NoRoom_No


Staff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_Date

18


Normalize Client_Interview relation to BCNF by– removing the violating functional dependency based on

(Staff_No, Interview_Date) determinant




19













20


Client_NoClient_NoInterview_DateInterview_DateInterview_TimeInterview_Time Staff_NoStaff_NoClient_NoClient_NoInterview_DateInterview_DateInterview_TimeInterview_Time Staff_NoStaff_NoCR76CR76 13-May-9513-May-95 10.3010.30 SG5SG5

CR56CR56 13-May-9513-May-95 12.0012.00 SG5SG5

CR74CR74 13-May-199513-May-1995 12.0012.00 SG37SG37

CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5

CR76CR76 13-May-9513-May-95 10.3010.30 SG5SG5

CR56CR56 13-May-9513-May-95 12.0012.00 SG5SG5

CR74CR74 13-May-199513-May-1995 12.0012.00 SG37SG37

CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5

Staff_NoStaff_No Interview_DateInterview_DateRoom_NoRoom_NoStaff_NoStaff_No Interview_DateInterview_DateRoom_NoRoom_NoSG5SG5 13-May-9513-May-95 G101G101

SG5SG5 13-May-9513-May-95 G101G101

SG37SG37 13-May-199513-May-1995 G102G102

SG5SG5 1-Jul-951-Jul-95 G102G102

SG5SG5 13-May-9513-May-95 G101G101

SG5SG5 13-May-9513-May-95 G101G101

SG37SG37 13-May-199513-May-1995 G102G102

SG5SG5 1-Jul-951-Jul-95 G102G102

INTERVIEWINTERVIEWINTERVIEWINTERVIEW

STAFF_ROOMSTAFF_ROOMSTAFF_ROOMSTAFF_ROOM

Review of NormalizationProcess (UNF to BCNF)

Objective 2

22

A DreamHomeScenario

DreamHome Co. manages property on behalf of the owners

The company’s staffs inspect properties A staff may inspect several properties at a

given date A property is inspected once on that date A staff is allocated a car for the day

23

Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No

SNameSName Car_RegCar_Reg

PG4PG4 6 Lawrence Street,6 Lawrence Street, 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace

crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR

GlasgowGlasgow

22-Apr-9522-Apr-95 09.0009.00 In good orderIn good order SG14SG14

David FordDavid Ford M533 HDRM533 HDR

1-Oct-951-Oct-95 12.0012.00 Damp rot in bathroomDamp rot in bathroom SG14SG14 David David

FordFord N721 HFRN721 HFR

PG16PG16 5 Novar Drive,5 Novar Drive, 22-Apr-9522-Apr-95 13.0013.00 Replace living roomReplace living roomSG14SG14


GlasgowGlasgow carpetcarpet

24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37

Ann BeechAnn Beech N721 HFRN721 HFR





GlasgowGlasgow










PROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLE

Unnormalized Form(UNF)

Transfer some sample information from the report into a table– This table is the Unnormalized (UNF) table

24

Unnormalized Form(UNF)

Identify the key attributes for table– The key attribute is Property_No





GlasgowGlasgow














GlasgowGlasgow











25

First Normal Form (1NF)First Approach

Identify the repeating group in the unnormalized table





GlasgowGlasgow














GlasgowGlasgow











26


The repeating attributes are– IDate, ITime, Comments, Staff_No, SName,

Car_Reg





GlasgowGlasgow














GlasgowGlasgow











27



PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace


PG4PG4 6 Lawrence Street 6 Lawrence Street 22-Apr-9522-Apr-95 09.0009.00 In good orderIn good order

SG14SG14 David FordDavid Ford M533 HDRM533 HDR

PG4PG4 6 Lawrence Street 6 Lawrence Street 1-Oct-951-Oct-95 12.0012.00 Damp rot in Damp rot in

bathroombathroom SG14SG14 David FordDavid Ford N721 HFRN721 HFR

PG16PG16 5 Novar Drive5 Novar Drive 22-Apr-9522-Apr-95 13.0013.00 Replace room carpetReplace room carpet


PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37














PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION


Normalize to 1NF – Enter the appropriate property details

(non-repeating data) into each row

28


The resulting relation is in First Normal Form (1NF)– There is a single value at the intersection of each

row and column


























29


Identify the candidate keys for the rela-tion– Candidate keys are (Property_No, IDate) and

(Staff_No, IDate, ITime)


























30


Let’s select (Property_No, IDate) as the primary key


























31

First Normal Form (1NF)Second Approach

Normalize to 1NF by removing the repeating group to form a new relation

Copy the primary key from the main relation to the new relation

Identify the primary key for the new relation

32

Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)

Property_NoProperty_NoPAddressPAddressIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg

Which attributesWhich attributesare repeating?are repeating?

Which attributesWhich attributesare repeating?are repeating?


33



Property_NoProperty_NoPAddressPAddress

IDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg

MOVEMOVEMOVEMOVE

34





MOVEMOVEMOVEMOVE

35





Copy theCopy theprimary keyprimary key

Copy theCopy theprimary keyprimary key

36




Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg

IdentifyIdentifyprimary keyprimary key


37





Property_NoProperty_Noand IDateand IDate

Property_NoProperty_Noand IDateand IDate

38


Unnormalized Form Unnormalized Form (UNF)(UNF)Unnormalized Form Unnormalized Form (UNF)(UNF)

First Normal Form First Normal Form (1NF)(1NF)First Normal Form First Normal Form (1NF)(1NF)

Property_NoProperty_NoPAddressPAddressProperty_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg

Property_NoProperty_NoPAddressPAddressProperty_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg





39

Second Normal Form(2NF)

Normalization to 2NF is associated with – Composite primary key only!

The normalization to 2NF involves– The removal of partial dependencies on the pri-mary key

If partial dependencies exist– Remove the dependent attributes to a new relation– Copy their determinant to the new relation

40

Second Normal FormFirst Approach

Identify the functional dependen-cies in the Property_Inspection rela-tion


























41



























Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName

Car_RegCar_RegCar_RegCar_Reg

Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress

Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName

42



























Staff_No, IDateStaff_No, IDateStaff_No, IDateStaff_No, IDate Car_RegCar_RegCar_RegCar_Reg

Staff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITime Property_NoProperty_NoProperty_NoProperty_NoPAddressPAddressPAddressPAddressCommentsCommentsCommentsComments

43


PAddress is functionally dependent on Pro-perty_No– Remove it to form a new relation with a copy its determinant,

Property_No

Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName Car_RegCar_RegCar_RegCar_Reg




Staff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITime Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress CommentsCommentsCommentsComments

44

Primary KeyPrimary Key

IDateIDateIDateIDate

Staff_NoStaff_NoStaff_NoStaff_No

SNameSNameSNameSName

ITimeITimeITimeITime

PAddressPAddressPAddressPAddress

CommentsCommentsCommentsComments

Car_RegCar_RegCar_RegCar_Reg

Alternate KeyAlternate Key


Property_NoProperty_NoProperty_NoProperty_No

45

PropProp ((Property_NoProperty_No, , Address)Address)PropProp ((Property_NoProperty_No, , Address)Address)Prop_Prop_ ((Property_NoProperty_No, , IDateIDate, ITime,, ITime,InspectionInspectionComments, Staff_No, Comments, Staff_No,

SName, Car_Reg)SName, Car_Reg)

Prop_Prop_ ((Property_NoProperty_No, , IDateIDate, ITime,, ITime,InspectionInspectionComments, Staff_No, Comments, Staff_No,

SName, Car_Reg)SName, Car_Reg)Which is the same as the 1NF Which is the same as the 1NF generated from the second generated from the second approachapproach

Which is the same as the 1NF Which is the same as the 1NF generated from the second generated from the second approachapproach


The relations have the form:

46

Second Normal FormSecond Approach

First Normal Form (UNF)First Normal Form (UNF) Second Normal Form (1NF)Second Normal Form (1NF)



Which of theWhich of thenon-key attributesnon-key attributesdepend on part ofdepend on part ofthe primary key?the primary key?

Which of theWhich of thenon-key attributesnon-key attributesdepend on part ofdepend on part ofthe primary key?the primary key?

All the non-keyAll the non-keyattributes areattributes are

dependent on bothdependent on bothkey-attributeskey-attributes

All the non-keyAll the non-keyattributes areattributes are

dependent on bothdependent on bothkey-attributeskey-attributes

47


First Normal Form (UNF)First Normal Form (UNF) Second Normal Form (1NF)Second Normal Form (1NF)



MOVEMOVEMOVEMOVE

MOVEMOVEMOVEMOVE

48


First Normal Form First Normal Form (1NF)(1NF)First Normal Form First Normal Form (1NF)(1NF)

Second Normal Form Second Normal Form (2NF)(2NF)Second Normal Form Second Normal Form (2NF)(2NF)









49

Third Normal Form(3NF)

Normalization to 3NF involves– The removal of transitive dependencies

If a transitive dependency exists– Remove the dependent attributes to a new

relation– Copy their determinant to the new relation

50

Third Normal FormFirst Approach

Identify the functional dependencies in the Prop and Prop_Inspection relation





Staff_No, Idate, ITimeStaff_No, Idate, ITimeStaff_No, Idate, ITimeStaff_No, Idate, ITime Property_NoProperty_NoProperty_NoProperty_No CommentsCommentsCommentsComments

51


SName is dependent on Staff_ No– A case of transitive dependency






52


Remove SName with a copy of its deter-minant to a new relation, Staff






53

PropProp ((Property_NoProperty_No, , PAddress)PAddress)PropProp ((Property_NoProperty_No, , PAddress)PAddress)

Prop_Prop_ ((Property_NoProperty_No, , IDateIDate, ITime,, ITime,InspectionInspectionComments, Staff_No,Comments, Staff_No,

Car_Reg)Car_Reg)

Prop_Prop_ ((Property_NoProperty_No, , IDateIDate, ITime,, ITime,InspectionInspectionComments, Staff_No,Comments, Staff_No,

Car_Reg)Car_Reg)

StaffStaff ((Staff_NoStaff_No, SName), SName)StaffStaff ((Staff_NoStaff_No, SName), SName)


The new relations are

54

Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)



Third Normal FormSecond Approach

Which ofWhich ofthe non-key attributesthe non-key attributes

depend on other non-keydepend on other non-keyattributes?attributes?

Which ofWhich ofthe non-key attributesthe non-key attributes

depend on other non-keydepend on other non-keyattributes?attributes?

SName dependsSName dependson Staff_Noon Staff_No

SName dependsSName dependson Staff_Noon Staff_No

55




Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_No

Car_RegCar_Reg

SNameSName

MOVEMOVEMOVEMOVE

56




Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg

SNameSName

Copy theCopy thedeterminantdeterminant

Copy theCopy thedeterminantdeterminant

MOVEMOVEMOVEMOVE

MOVEMOVEMOVEMOVE

57





Staff_NoStaff_NoSNameSName



58






Staff_NoStaff_NoStaff_NoStaff_No

59


Second Normal Form Second Normal Form (2NF)(2NF)Second Normal Form Second Normal Form (2NF)(2NF)

Third Normal Form Third Normal Form (3NF)(3NF)Third Normal Form Third Normal Form (3NF)(3NF)











60

Boyce-Codd Normal Form (BCNF)

A relation is in BCNF if every determinant is a candidate key

Identify all the determinants and see if they are candidate keys

If they are not, remove them with their dependents to form a new relation

61


The functional dependencies are:



Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No Car_RegCar_RegCar_RegCar_Reg


Staff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITimeStaff_No, IDate, ITime Property_NoProperty_NoProperty_NoProperty_NoCommentsCommentsCommentsComments Car_RegCar_RegCar_RegCar_Reg

PROP RELATIONPROP RELATIONPROP RELATIONPROP RELATION

STAFF RELATIONSTAFF RELATIONSTAFF RELATIONSTAFF RELATION

PROP_INSPECT RELATIONPROP_INSPECT RELATIONPROP_INSPECT RELATIONPROP_INSPECT RELATION

62


Prop and Staff relations are already in BCNF – The determinants are candidate keys









63










Prop_Inspect relation is not in BCNF– (Staff_No, IDate) is not a candidate key

64


Remove (Staff_No, IDate) determinant with its dependents to a new relation, Staff_Car









65

PropProp ((Property_NoProperty_No, , PAddress)PAddress)PropProp ((Property_NoProperty_No, , PAddress)PAddress)

InspectionInspection ((Property_NoProperty_No, , IDateIDate, , ITime,ITime,

Comments, Staff_No)Comments, Staff_No)

InspectionInspection ((Property_NoProperty_No, , IDateIDate, , ITime,ITime,

Comments, Staff_No)Comments, Staff_No)

StaffStaff ((Staff_NoStaff_No, SName), SName)StaffStaff ((Staff_NoStaff_No, SName), SName)

Staff_CarStaff_Car((Staff_NoStaff_No, , IDateIDate, , Car_Reg)Car_Reg)Staff_CarStaff_Car((Staff_NoStaff_No, , IDateIDate, , Car_Reg)Car_Reg)


The final normalized relations are:

66

1NF1NF1NF1NF

2NF2NF2NF2NF

3NF3NF3NF3NF

BCNFBCNFBCNFBCNF

Property_InspectionProperty_InspectionProperty_InspectionProperty_Inspection

UNF to BCNF

Prop_InspectionProp_InspectionProp_InspectionProp_Inspection

Prop_InspectProp_InspectProp_InspectProp_Inspect

StaffStaffStaffStaff Staff_CarStaff_CarStaff_CarStaff_Car InspectionInspectionInspectionInspection PropPropPropProp

THE END

College of Information Technology

68

SID Major Advisor Maj_GPA

123 Physics Hawking 4.0

123 Music Mahler 3.3

456 Literature Michener 3.2

789 Music Bach 3.7

678 Physics Hawking 3.5

STUDENT_ADVISOR

BCNFA relation is in BCNF if every determinant is a candidate key

SID, Major -> Advisor, Maj_GPAAdvisor -> Major

69

Update anomalies Modification anomaly

– Suppose that in Physics, the advisor Hawking is replaced by Einstein. This change must be made in two (or more) rows in the table

Insertion anomaly– Suppose we want to insert a row with the information that

John advises in Computer Science. This cannot be done until at least one student majoring in Computer Science is assigned John as an advisor

Deletion anomaly– If student number 789 withdraws from school, we lose the

information that Bach advises in Music

b. information technology cmpb245: database design

Documents