Conceptual Database Modeling for Apartment Complex - CP2404/CP5633

This assignment presents a solution to a database modeling project for an apartment complex, fulfilling the requirements of CP2404/CP5633 Assignment 1. The solution begins with an analysis of the provided case study to identify entities and attributes. The database design is normalized up to 3NF, resulting in a relational model that defines entities, attributes, and their relationships. An Entity-Relationship Diagram (ERD) is then developed based on the relational model. The document includes a discussion of the modeling approach, addressing potential challenges. It also meticulously outlines the applicable business rules, specifying relationships, optionalities, connectivities, cardinalities, and constraints for each entity. The ERD visually represents the database structure, with justifications provided for each relationship. The assignment covers building, apartment, parking lot, lease, lessee, maintenance, and other relevant entities, detailing their interactions and constraints within the apartment complex database.

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.