Modelling the Database for a Case Study

This document discusses the process of modelling a database for a case study, including analyzing the case study, normalizing entities, and developing a relational model and entity relationship diagram. It also provides business rules for various entities such as building, apartment, lease, lessee, and more.

COVER PAGE

Contents
1 Discussion.................................................................................................................................................3
2 Business rules...........................................................................................................................................3
3 Entity relationship diagram......................................................................................................................6
4 Summary..................................................................................................................................................6

1 Discussion
The process of modelling the database using the provided case study involved performing some steps
before finalizing on the final design of the database. The steps followed are;
 The first step was to analyze the case study and getting all possible entities and their attributes.
 The entities obtained are then normalized up to 3NF and a relational model with all entities,
their attributes and the relationships between them is developed.
 Based on the relational model, an ERD is developed.
2 Business rules
BUILDING
Each building has one or more APARTMENT
Each building is managed by one LESSEES.
The manager of a complex must be a tenant in the complex.
APARTMENT
Each APARTMENT belongs to only one COMPLEX
Each APARTMENT can have only one LEASE at a time
Each APARTMENT is allocated one or more PARTKING_LOT
Each APARTMENT have one or more MAINTENANCE_JOB
Each APARTMENT has one or more PREINSTALLED_EQUIPMENT
PREISNTALLED_EQUIPMENT
Each PREINSTALLED_EQUIMENT is allocated to only one APARTMENT
PARKING_LOT
Each PARKING_LOT is associated with only one APARTMENT
Each PARKING_LOT belongs to only one building COMPLEX.
LEASE
Each LEASE has one or more LESSEE
Each LEASE is signed by a maximum of 5 LESSEE.
Each LEASE is associated with only one APARTMENT
LESSEE
Each LESSEE is involved in only one LEASE
Each LESSEE rents only one APARTMENT

Each LESSEE makes one or more RENT_PAYMENTS
Each LESSEE is allocated one or more KEYS
Each LESSEE can make none or many RENTALS
MAITENANCE_COMPANY
Each MAINTENANCE_COMPANY can be associated with none or many MAINTENANCE_JOBS
MAINTENANCE_JOB
Each MAINTENANC_JOB is associated with only one APARTMENT
Each MAINTEANCE_JOB is done by only one MAINTENANCE_COMPANY
KEY
Each KEY results to one or more KEY_ALLOCATION records
RENTAL_EQUIPMENT
Each RENTAL_EQUIPMENT is associated with none or many RENTAL
RENTAL
Each RENTAL is associated with one and only RENTAL_EQUIPMENT
RENT PAYMENT
Each RENT_PAYMENT is done by one and only one LESSEE
Each RENT_PAYMENT is for a LEASE
LOCKUP_GARAGE
Each LOCKUP_GARAGE is associated with one and only APARTMENT

3 Entity relationship diagram
4 Summary
The entity relationship diagram shown in the figure above is based on the following justifications.
 BULDING and APARTMENT have a one to many mandatory relationship because a building is
made up of one or more apartments.
 APARTMENT and PARKING_LOT have a one to many mandatory relationship because every
apartment is allocated one or more parking slots.

 APARTMENT and LOCKUP_GARAGE have one to one optional relationship because not every
apartment is allocated a lockup garage.
 APARTMENT and EQUIPMENT have a one to many mandatory relationship because each
apartment is allocated one or more equipment.
 LEASE and APARTMENT have a one to one mandatory relationship because every lease is for one
and only one apartment,
 LEASE and LESEE entities have a one to many mandatory relationship because a lease can have
one to six lessees.
 APARTMENT and MAINTENANCE_JOB entities have a one to many optional relationship because
an apartment can get none or many maintenance jobs.
 MAINTENANCE_JOB and MAINTENANCE_COMPANY entities have a one to one mandatory
relationship because each maintenance job is performed by one maintenance company.
 LESEE and KEY_ALLOCATION entities have a one to many mandatory relationship because a
lessee must be allocated one or more keys.
 KEY and KEY_ALLOCATION entities have a one to one mandatory relationship because a key is
associated to only one key allocation record.
 LESSEE and RENT_PAYMENT entities have a one to many mandatory relationship because a
lessee makes many rent payments.
 LESEE and RENTAL entities have a one to many optional relationship because a lessee can make
none or many rentals.
 RENTAL_EQUIPMENT and RENTALS entities have a one to many optional relationship because a
rental equipment can be rented once or many times or can be not rented even once.
The key_allocations entity is a special case because to get a key allocation record you need the key
number and the ID of the lessee as the primary key is a composite primary key made up of the
combination of the two attributes.