Transform a Lease relation into Third Normal Form (3NF) [closed]

Posted on

Question :

Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rental rate:

RELATION = LEASE (PersonID, BuildingID, Rent), Primary Key = {PersonID}

I try:

LEASE ( PersonID, BuildingID )
BUILDING ( BulidingID, RentID)
RENT ( RentID, Rent)

Is it right?

Answer :

In the real world a person can obviously hold more than one lease at the same time – a primary residence, holiday home and business premises, for example. Then the primary key of lease would be both PersonID and BuildingID together. If in your case you artificially constrain this to only one lease per person at a point in time then you are mostly there. However

  • You need some sub-division of Building. Otherwise a person must
    lease the entire building. This may be true for houses; it is
    unlikely for office blocks.

  • You state “a building can charge only one rental rate.” Remove rent from LEASE. It can be found via the relationship through BUILDING.

  • You will likely want a PERSON relation
    to hold things like name, date of birth etc.

  • RENT is only useful if there is some external agency defining a small set of rent values which it is permissible to charge. If each building’s owner can charge what he likes, remove RENT and add an attribute to BUILDING. Just because Building A’s owner charges the same rate as Building B’s owner does not make them the same thing. If Building A’s rent changes there is no rule which says Building B’s must also change at the same time.

  • LEASE will require a unique constraint on PersonID to ensure a person does indeed rent only once.

Leave a Reply

Your email address will not be published. Required fields are marked *