Normalize following table up to BCNF In this example we exte

Normalize following table up to BCNF

In this example we extend the PropertyInspection database to include property inspection by members of staff. When staffs are required to undertake the inspections, they are allocated a company car for use on the day of the inspections. However, a car may be allocated to several members of staff as required throughout the working day. A member of staff may inspect several properties on a given date, but a property is only inspected once on a given date.

1)Find all the candidate keys.

2)Determine the primary key

3)Find all the functional dependencies.

4)Get the first normal form

5)Get the second normal form

6)Get the third normal form

7)Gent the BCNF normal form.

StaffPropertylnspection propertyNoiDatee iTime pAddress comments staffNo sName carReg 18-Oct-00 10.00 6 Lawrence St, Need to replace crockery SG37 Ann Beech M231 JGR 22-Apr-01 09.00 6 Lawrence St, |In good order 1-Oct-0112.006 Lawrence St, |Damp rot in bathroom 22-Apr-01 13.005 24-Oct-01 14.005 Novar Dr,Good condition PG4 Glasgow Glasgow Glasgow PG4 SG14David Ford M533 HDR PG4 SG14David Ford N721 HFR PG16 SG14 David Ford M533 HDR var Dr,Replace living room carpet 5 No Glasgow PG16 SG37 Ann Beech N721 HFR Glasgow

Solution

2)Determine the primary key:

FD1 is Primary key

primary can apply all fields except paddress column

FD 1= client_no,Interview_date-> Interview_time,Staff_No,Room_No

1)candudate key:

FD 5 is candidate key.

here all fields conncet ro each other with candidate key.

3)functional dependencies:

FD 2 is partial dependency

the connection between \"property_no\" filed and \"pAddress\",both fileds connected partial.

FD 3 is tansitive Dependency

The Staff_no and Sname fields are connected with transitive dependecy

4) 1st Normal form:

The DreamHome company manages property on behalf of the owners, and as part of this service the company undertakes regular inspections of the property by members of staff. When staff are required to undertake these inspections, they are allocated a company car for use on the day of the inspections. However, a car may be allocated to several members of staff, as required throughout the working day. A member of staff may inspect several properties on a given date, but a property is only inspected once on a given date.

6 Lawrence St,

18-Oct-96

22-Apr-97

1-Oct-98

10:00

09:00

12:00

Need to replace crockery

In good order

Damp rot in bathroom

SG37

SG14

SG14

Ann Beech

David Ford

David Ford

M231 JGR

M533 HDR

N721 HFR

5 Norwar Dr

22-Apr-96

24-Oct-97

13:00

14:00

Replace room carpet

Good condition

SG14

SG37

David Ford

Ann Beech

M533 HDR

N721 HFR

2) second normal form:

=> HERE remove partial dependency to obtain 2NF

3) third normal form:

=> Here remove transitive dependency to obtain 3NF

propertyno

idate

itime

paddress

comments

staffno

sname

car reg