You are going to re-design a database to manage a number of rental properties in Smithfield area and kept historical/current records about all rental properties under their management and corresponding details about each rental contract - including customer details, property details, owner details, contract periods and weekly rent. The table below shows some sample records of the current database. 

a.   Identify all partial dependencies and transitive dependencies.

b.   What normal form is the relation currently in? State the reasons for your answer.

c.    Normalize the table to third normal form (3NF) and present or draw the dependency diagrams of the final 3NF tables.

Transcribed Image Text:

Customer Property Customer No No Name CR85 PG16 CR56 CR76 CR56 CR56 PG4 CR56 CR76 PG16 PG16 John Kay Aline Stewart Aline Stewart PG4 CR76 PG16 John Kay Aline Stewart PG36 James Matt PG4 Aline Stewart PG4 Property Address 5 Novar Dr. Smithfield 6 Lawrence St. Smithfield 5 Novar Dr. Smithfield 5 Novar Dr. Smithfield 6 Lawrence St. Smithfield 5 Novar Dr. Smithfield 2 Manor Rd. Smithfield 6 Lawrence St. Smithfield John Kay Aline 6 Lawrence St. Stewart Smithfield Rent Start 1/Dec/08 1/Nov/07 CR56 Primary keys: Customer_No, Property_No, Rent_Start Rent Finish Property Owner Rent ($) No 450 375 1/Sep/07 1/Sep/08 1/Sep/06 10/Aug/07 1/Dec/05 10/June/06 350 1/Dec/05 31/Aug/06 420 10/Oct/04 15/Nov/05 375 1/July/04 31/Nov/05 325 1/Sep/02 10/June/04 325 Owner Name CO99 Janie Edwards CO99 Janie Edwards 450 CO93 Tony Shaw 450 CO93 Tony Shaw CO40 Tina Murphy CO93 Tony Shaw CO93 Tony Shaw CO40 Tina Murphy Customer Property Customer No No Name CR85 PG16 CR56 CR76 CR56 CR56 PG4 CR56 CR76 PG16 PG16 John Kay Aline Stewart Aline Stewart PG4 CR76 PG16 John Kay Aline Stewart PG36 James Matt PG4 Aline Stewart PG4 Property Address 5 Novar Dr. Smithfield 6 Lawrence St. Smithfield 5 Novar Dr. Smithfield 5 Novar Dr. Smithfield 6 Lawrence St. Smithfield 5 Novar Dr. Smithfield 2 Manor Rd. Smithfield 6 Lawrence St. Smithfield John Kay Aline 6 Lawrence St. Stewart Smithfield Rent Start 1/Dec/08 1/Nov/07 CR56 Primary keys: Customer_No, Property_No, Rent_Start Rent Finish Property Owner Rent ($) No 450 375 1/Sep/07 1/Sep/08 1/Sep/06 10/Aug/07 1/Dec/05 10/June/06 350 1/Dec/05 31/Aug/06 420 10/Oct/04 15/Nov/05 375 1/July/04 31/Nov/05 325 1/Sep/02 10/June/04 325 Owner Name CO99 Janie Edwards CO99 Janie Edwards 450 CO93 Tony Shaw 450 CO93 Tony Shaw CO40 Tina Murphy CO93 Tony Shaw CO93 Tony Shaw CO40 Tina Murphy

Answer rating: 100% (QA)

a Partial Dependencies and Transitive Dependencies 1 PropertyNo PropertyAddress RentStart RentFinish ... View the full answer