2NF
Nonkey Price depends on Nonkey Area
Property
PropertyID
|
County |
Lot# |
Area |
Price |
|
1 |
LM |
1 |
2500 |
100,000 |
|
2 |
UM |
5 |
2500 |
100,000 |
|
3 |
UM |
17 |
2500 |
100,000 |
|
4 |
LM |
25 |
4000 |
300,000 |
|
5 |
GL |
5 |
4500 |
300,000 |
Insertion
Errors and limitations:
1.
When
adding another property with Area = 2500, a mistake in the Price will make the
database inconsistent. 3NF prevents
this.
2.
You
can’t add a new area that has no property, because you can’t make PropertyID
null
Deletion Errors:
1.
If
you delete PropertyID = 4, then the relationship of Area = 4000, Price =
300,000 is lost.
Update Errors:
1.
If
we change the Price of a particular area, then you have to find all records
that have that price and change them as well.
Don’t
join records in a select on columns that are not in a foreign
key – key relation. I.e., One of
the columns (or column combinations) in the Where Clause must be unique.
SELECT
OwnerName, PropertyID FROM PrincipalResidence, Property WHERE PrincipalResidence.Price
= Property.Price
PrincipalResidence
SSN
|
OwnerName
|
Price |
Occupation |
|
111-22-33333 |
Carter |
100,000 |
Butcher |
|
444-55-6666 |
Baker |
300,000 |
Baker |
|
777-88-9999 |
Dolan |
100,000 |
Candlestick
Maker |
Property
PropertyID
|
County |
Lot# |
Area |
Price |
|
1 |
LM |
1 |
2500 |
100,000 |
|
2 |
UM |
5 |
2500 |
100,000 |
|
3 |
UM |
17 |
2500 |
100,000 |
|
4 |
LM |
25 |
4000 |
300,000 |
|
5 |
GL |
5 |
4500 |
300,000 |
Result
|
OwnerName |
PropertyID |
|
Carter |
1 |
|
Carter |
2 |
|
Carter |
3 |
|
Baker |
4 |
|
Baker |
5 |
|
Dolan |
1 |
|
Dolan |
2 |
|
Dolan |
3 |