PropertyID PropertyName Type Street City Zip ServiceID ServiceDate Description A
ID: 3847592 • Letter: P
Question
PropertyID
PropertyName
Type
Street
City
Zip
ServiceID
ServiceDate
Description
Amount
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/5/2014
Lawn Mow
$ 42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
5/8/2014
Lawn Mow
$123.50
003
Jeferson Hill
Office
42 West 7th St
Bellevue
98040
2
5/8/2014
Garden Service
$53.00
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/10/2014
Lawn Mow
$42.50
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/12/2014
Lawn Mow
$42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
5/15/2014
Lawn Mow
$123.50
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/19/2014
Lawn Mow
$42.50
Property ID --> Property Name, Type, Street, City, Zip (The PropertyID will provide the general address of the property and the Type)
Service ID --> Description (The ServiceID defines description)
Property ID, Service ID, ServiceDate --> Amount (PropertyID and ServiceID will determine the amount charged to the customer)
We also assume that there could be two buildings in different areas with same name. Also even if the building names are same they may be of different types.
Based on above functional dependency normalize the above table.
Clearly show first, second and third normal form
PropertyID
PropertyName
Type
Street
City
Zip
ServiceID
ServiceDate
Description
Amount
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/5/2014
Lawn Mow
$ 42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
5/8/2014
Lawn Mow
$123.50
003
Jeferson Hill
Office
42 West 7th St
Bellevue
98040
2
5/8/2014
Garden Service
$53.00
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/10/2014
Lawn Mow
$42.50
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/12/2014
Lawn Mow
$42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
5/15/2014
Lawn Mow
$123.50
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
5/19/2014
Lawn Mow
$42.50
Explanation / Answer
For 1st Normal form, lets first review the rules of 1st normal form.
Each table cell should contain single value.
Each record needs to be unique.
As our given table already satisfies above rules, therefore it is already in 1st normal form. Therefore 1st normal form will be same as give table.
For 2nd Normal form, lets first review the rules of 2nd normal form.
Rule 1- Be in 1NF
Rule 2- Single Column Primary Key
Using above rules, 2NF will be:
PropertyID
PropertyName
Type
Street
City
Zip
ServiceID
Description
Amount
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
Lawn Mow
$ 42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
Lawn Mow
$123.50
003
Jeferson Hill
Office
42 West 7th St
Bellevue
98040
2
Garden Service
$53.00
Property ID
Service Date
001
5/5/2014
002
5/8/2014
003
5/8/2014
001
5/10/2014
001
5/12/2014
002
5/15/2014
001
5/19/2014
Now the tables comply with Second normal form (2NF).
For 3rd Normal form, lets first review the rules of 3rd normal form.
Rule 1- Be in 2NF
Rule 2- Has no transitive functional dependencies
A transitive functional dependency is when changing a non-key column , might cause any of the other non-key columns to change
PropertyID
PropertyName
Type
Street
City
Zip
ServiceID
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
003
Jeferson Hill
Office
42 West 7th St
Bellevue
98040
2
Property ID
Service Date
001
5/5/2014
002
5/8/2014
003
5/8/2014
001
5/10/2014
001
5/12/2014
002
5/15/2014
001
5/19/2014
ServiceID
Description
1
Lawn Mow
2
Garden Service
PropertyID
ServiceID
Amount
001
1
$ 42.50
002
1
$123.50
003
2
$53.00
Hope it helps!
PropertyID
PropertyName
Type
Street
City
Zip
ServiceID
Description
Amount
001
Eastlake Building
Office
123 Eastlake
Seattle
98119
1
Lawn Mow
$ 42.50
002
Elm St Apts
Apartment
4 East Elm
Lynnwood
98223
1
Lawn Mow
$123.50
003
Jeferson Hill
Office
42 West 7th St
Bellevue
98040
2
Garden Service
$53.00
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.