![Page 1: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/1.jpg)
B. Information TechnologyCMPB245:
Database Design
Normalization
Pt. 2
![Page 2: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/2.jpg)
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)
![Page 3: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/3.jpg)
The Boyce-CoddNormal Form
Objective 1
![Page 4: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/4.jpg)
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
![Page 5: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/5.jpg)
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
![Page 6: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/6.jpg)
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
![Page 7: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/7.jpg)
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
![Page 8: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/8.jpg)
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
![Page 9: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/9.jpg)
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
![Page 10: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/10.jpg)
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
![Page 11: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/11.jpg)
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
![Page 12: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/12.jpg)
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
![Page 13: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/13.jpg)
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
![Page 14: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/14.jpg)
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
![Page 15: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/15.jpg)
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
![Page 16: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/16.jpg)
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
![Page 17: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/17.jpg)
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
![Page 18: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/18.jpg)
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
![Page 19: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/19.jpg)
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
![Page 20: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/20.jpg)
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
![Page 21: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/21.jpg)
Review of NormalizationProcess (UNF to BCNF)
Objective 2
![Page 22: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/22.jpg)
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
![Page 23: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/23.jpg)
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
![Page 24: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/24.jpg)
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
![Page 25: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/25.jpg)
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
![Page 26: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/26.jpg)
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
![Page 27: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/27.jpg)
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
![Page 28: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/28.jpg)
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
![Page 29: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/29.jpg)
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
![Page 30: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/30.jpg)
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
![Page 31: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/31.jpg)
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
![Page 32: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/32.jpg)
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
![Page 33: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/33.jpg)
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
![Page 34: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/34.jpg)
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
![Page 35: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/35.jpg)
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
![Page 36: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/36.jpg)
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
![Page 37: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/37.jpg)
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
![Page 38: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/38.jpg)
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
![Page 39: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/39.jpg)
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
![Page 40: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/40.jpg)
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
![Page 41: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/41.jpg)
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
![Page 42: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/42.jpg)
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
![Page 43: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/43.jpg)
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
![Page 44: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/44.jpg)
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
![Page 45: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/45.jpg)
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:
![Page 46: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/46.jpg)
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
![Page 47: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/47.jpg)
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
![Page 48: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/48.jpg)
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
![Page 49: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/49.jpg)
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
![Page 50: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/50.jpg)
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
![Page 51: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/51.jpg)
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
![Page 52: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/52.jpg)
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
![Page 53: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/53.jpg)
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
![Page 54: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/54.jpg)
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
![Page 55: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/55.jpg)
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
![Page 56: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/56.jpg)
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
![Page 57: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/57.jpg)
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
![Page 58: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/58.jpg)
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
![Page 59: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/59.jpg)
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
![Page 60: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/60.jpg)
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
![Page 61: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/61.jpg)
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
![Page 62: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/62.jpg)
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
![Page 63: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/63.jpg)
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
![Page 64: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/64.jpg)
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
![Page 65: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/65.jpg)
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:
![Page 66: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/66.jpg)
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
![Page 67: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/67.jpg)
THE END
College of Information Technology
![Page 68: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/68.jpg)
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
![Page 69: B. Information Technology CMPB245: Database Design](https://reader035.vdocuments.us/reader035/viewer/2022070411/56814722550346895db45631/html5/thumbnails/69.jpg)
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