b. information technology cmpb245: database design
Post on 14-Jan-2016
32 Views
Preview:
DESCRIPTION
TRANSCRIPT
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
Boyce-Codd Normal FormExample
Consider the Client_Interview relation
7
Boyce-Codd Normal FormExample
Identify all the candidate keys, i.e. which will produce unique rows
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
8
Boyce-Codd Normal FormExample
By itself, all attributes cannot be a candidate key
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
9
Boyce-Codd Normal FormExample
(Client_No, Interview_Date) both identifies a unique row and is a candidate key -- (Set 1)
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
10
Boyce-Codd Normal FormExample
What about (Staff_No, Interview_Date) together?– No, because there are two rows
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
11
Boyce-Codd Normal FormExample
What about (Staff_No, Interview_ Date, Interview_ Time) together?– Yes, they can be another candidate key - (Set 2)
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
12
Boyce-Codd Normal FormExample
Since there are two sets of composite candidate key, then BCNF could be vio-lated
5555
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
13
Boyce-Codd Normal FormExample
Try use (Client_No, Interview_Date) as the primary key
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
14
Boyce-Codd Normal FormExample
Then, identify all functional dependen-cies Identify all the determinants
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
15
Boyce-Codd Normal FormExample
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
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
16
Boyce-Codd Normal FormExample
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 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
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
Boyce-Codd Normal FormExample
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
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
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
Staff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_DateStaff_No, Interview_Date
18
Boyce-Codd Normal FormExample
Normalize Client_Interview relation to BCNF by– removing the violating functional dependency based on
(Staff_No, Interview_Date) determinant
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
19
Boyce-Codd Normal FormExample
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
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 13-May-9513-May-95 12.0012.00 SG5SG5 G101G101
CR74CR74 13-May-9513-May-95 12.0012.00 SG37SG37 G102G102
CR56CR56 1-Jul-951-Jul-95 10.3010.30 SG5SG5 G102G102
20
Boyce-Codd Normal FormExample
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
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
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLE
25
First Normal Form (1NF)First Approach
Identify the repeating group in the unnormalized table
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLE
26
First Normal Form (1NF)First Approach
The repeating attributes are– IDate, ITime, Comments, Staff_No, SName,
Car_Reg
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
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
David FordDavid Ford M533 HDRM533 HDR
GlasgowGlasgow carpetcarpet
24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLEPROPERTY_INSPECTION TABLE
27
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
First Normal Form (1NF)First Approach
Normalize to 1NF – Enter the appropriate property details
(non-repeating data) into each row
28
First Normal Form (1NF)First Approach
The resulting relation is in First Normal Form (1NF)– There is a single value at the intersection of each
row and column
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
29
First Normal Form (1NF)First Approach
Identify the candidate keys for the rela-tion– Candidate keys are (Property_No, IDate) and
(Staff_No, IDate, ITime)
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
30
First Normal Form (1NF)First Approach
Let’s select (Property_No, IDate) as the primary key
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
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?
First Normal Form (1NF)Second Approach
33
First Normal Form (1NF)Second Approach
Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
IDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
MOVEMOVEMOVEMOVE
34
First Normal Form (1NF)Second Approach
Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
IDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
MOVEMOVEMOVEMOVE
35
First Normal Form (1NF)Second Approach
Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
IDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Copy theCopy theprimary keyprimary key
Copy theCopy theprimary keyprimary key
36
First Normal Form (1NF)Second Approach
Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
IdentifyIdentifyprimary keyprimary key
IdentifyIdentifyprimary keyprimary key
37
First Normal Form (1NF)Second Approach
Unnormalized Form (UNF)Unnormalized Form (UNF) First Normal Form (1NF)First Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_Noand IDateand IDate
Property_NoProperty_Noand IDateand IDate
38
First Normal Form (1NF)Second Approach
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
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_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
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
41
Second Normal FormFirst Approach
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
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
Second Normal FormFirst Approach
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
Property_NoProperty_No PAddressPAddress IDateIDate ITimeITime CommentsComments Staff_NoStaff_No
SNameSName Car_RegCar_Reg
PG4PG4 6 Lawrence Street6 Lawrence Street 18-Oct-9418-Oct-94 10.0010.00 Need to replace Need to replace
crockerycrockery SG37SG37 Anne BeechAnne Beech M231 JGRM231 JGR
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
SG14SG14 David FordDavid Ford M533 HDRM533 HDR
PG16PG16 5 Novar Drive5 Novar Drive 24-Oct-9524-Oct-95 14.0014.00 Good conditionGood condition SG37SG37
Ann BeechAnn Beech N721 HFRN721 HFR
PROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATIONPROPERTY_INSPECTION RELATION
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
Second Normal FormFirst Approach
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
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
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_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
Second Normal FormFirst Approach
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
Second Normal FormFirst Approach
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)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
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
Second Normal FormSecond Approach
First Normal Form (UNF)First Normal Form (UNF) Second Normal Form (1NF)Second Normal Form (1NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
MOVEMOVEMOVEMOVE
MOVEMOVEMOVEMOVE
48
Second Normal FormSecond Approach
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)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
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
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName Car_RegCar_RegCar_RegCar_Reg
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
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_No CommentsCommentsCommentsComments
51
Third Normal FormFirst Approach
SName is dependent on Staff_ No– A case of transitive dependency
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName Car_RegCar_RegCar_RegCar_Reg
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
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_No CommentsCommentsCommentsComments
52
Third Normal FormFirst Approach
Remove SName with a copy of its deter-minant to a new relation, Staff
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName Car_RegCar_RegCar_RegCar_Reg
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
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_No CommentsCommentsCommentsComments
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)
Third Normal FormFirst Approach
The new relations are
54
Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
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
Third Normal FormSecond Approach
Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_No
Car_RegCar_Reg
SNameSName
MOVEMOVEMOVEMOVE
56
Third Normal FormSecond Approach
Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg
SNameSName
Copy theCopy thedeterminantdeterminant
Copy theCopy thedeterminantdeterminant
MOVEMOVEMOVEMOVE
MOVEMOVEMOVEMOVE
57
Third Normal FormSecond Approach
Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg
Staff_NoStaff_NoSNameSName
IdentifyIdentifyprimary keyprimary key
IdentifyIdentifyprimary keyprimary key
58
Third Normal FormSecond Approach
Second Normal Form (2NF)Second Normal Form (2NF) Third Normal Form (3NF)Third Normal Form (3NF)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg
Staff_NoStaff_NoSNameSName
Staff_NoStaff_NoStaff_NoStaff_No
59
Third Normal FormSecond Approach
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)
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoSNameSNameCar_RegCar_Reg
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg
Staff_NoStaff_NoSNameSName
Property_NoProperty_NoPAddressPAddress
Property_NoProperty_NoIDateIDateITimeITimeCommentsCommentsStaff_NoStaff_NoCar_RegCar_Reg
Staff_NoStaff_NoSNameSName
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
Boyce-Codd Normal Form (BCNF)
The functional dependencies are:
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No Car_RegCar_RegCar_RegCar_Reg
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_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
Boyce-Codd Normal Form (BCNF)
Prop and Staff relations are already in BCNF – The determinants are candidate keys
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No Car_RegCar_RegCar_RegCar_Reg
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_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
63
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No Car_RegCar_RegCar_RegCar_Reg
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_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
Boyce-Codd Normal Form (BCNF)
Prop_Inspect relation is not in BCNF– (Staff_No, IDate) is not a candidate key
64
Boyce-Codd Normal Form (BCNF)
Remove (Staff_No, IDate) determinant with its dependents to a new relation, Staff_Car
Staff_NoStaff_NoStaff_NoStaff_No SNameSNameSNameSName
Property_NoProperty_NoProperty_NoProperty_No PAddressPAddressPAddressPAddress
Property_No, IDateProperty_No, IDateProperty_No, IDateProperty_No, IDate ITimeITimeITimeITime CommentsCommentsCommentsComments Staff_NoStaff_NoStaff_NoStaff_No Car_RegCar_RegCar_RegCar_Reg
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_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
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)
Boyce-Codd Normal Form (BCNF)
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
top related