Question

The following table is a non-normalized table for a Rental Property Management Database.  Data is included to help you to perform the normalization.  One client may have multiple rental records.

Identify the functional dependencies and normalize the table to 1NF, 2NF, and 3NF respectively. You also need to show the steps to reach normal form and populate each relation with sample data. As a result, you can see that duplicate data will be removed through the normalization process.

CLIENT_RENTAL

ClientNo	ClientName	PropertyNo	PropertyAddress	RentStart	RentEnd	RentFee	OwnerNo	OwnerName
CN12	John Smith	PN21 PN52 PN89	4 Market St. Bowie 12 S. Main St. Chevy Chase 4871 East St. Silver Spring	12/01/2011 7/01/2014 4/01/2018	6/30/2013 3/31/2016	1500 1750 1900	ON68 ON77 ON120	Kent Shaw Tina Short Tim Johnson
CN18	Mary Helen	PN36 PN73 PN82	123 Ease St. Rockville 6528 Ohio Ave. Penn Park 21 West Point St. Bethesda	3/01/2010 3/01/2012 6/01/2016	2/28/2011 5/31/2015 12/31/2018	1450 1660 1820	ON68 ON77 ON82	Kent Shaw Tina Short Steven Gates
CN20	Wendy King	PN66	2332 Main Street, Fulton	9/01/2019		1880	ON10	Ben Johnson

ClientNo: Client Number (PK)

ClientName: Client (who rents a property) Name

PropertyNo: Property Number

PropertyAddress: Property Address (Do not need to break this attribute)

RentStart: Rent Start Date

RentEnd: Rent Ended Date

RentFee: Monthly Rental Fee

OwnerNo: Property Owner Number

OwnerName: Property Owner Name

Notes:

1. Can multiple clients rent the same property at the same time? For example, co-signers on a lease.  No
2. Can one client rent multiple properties at the same time? I.e. if he rented a home and an office.  Yes
3. Can one client rent the same property multiple consecutive times, requiring multiple records? For instance, he extends a lease for another year at a different rent.  No
4. A property cannot have multiple owners.
5. The RentFee is not fixed for each property and can change from one Client to the next.
6. A different client can rent the same property for a different time period creating another record for the same property.

Answer 1

As per the requirement stated above please find the solution solved below.

Normalization: Normalization is process of dividing large and complicated tables into simple and smaller one's. Due to normalization duplicate data will be removed.

1NF:

The given table is not in the 1NF because some of the rows contains multiple values for a column.below is the table converted in 1NF.

2NF:

The given table needs to normalize into 2NF to remove partial dependency.

Client :This table stores client.

Property :This table stores PropertyNo and PropertyAddress.

Owner :This table stores ownerNo and ownerName.

Below are tables in 2NF.

1.Table Name :Client

Schema :Client(ClientNo,ClientName)

FD:ClientNo --> ClientName

Below is the table data

2.Table Name :Property

Schema :Property(PropertyNo,PropertyAddress)

FD: PropertyNo --> PropertyAddress

Below is the table data

3.Table Name :Owner

Schema :Owner(OwnerNo,OwnerName)

FD: OwnerNo --> OwnerName

Below is the table data

3NF:

The given table needs to normalize into 3NF to remove transitive dependency.Also primary key of one table is used as foreign key another table.

1.Table Name :Client

Schema :Client(ClientNo,ClientName)

FD:ClientNo --> ClientName

Below is the table data

2.Table Name :Property

Schema :Property(PropertyNo,PropertyAddress)

FD: PropertyNo --> PropertyAddress

Below is the table data

Note: if you like the answer please upvote for me thankyou..