Conceptual Modelling of Joanne Myers Apartments

This report discusses the solution for the conceptual modelling of the Joanne Myers Apartments. It includes business rules, entity relationship diagram, and summary.

COVER PAGE

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

1 Discussion
This report discusses the solution for the conceptual modelling of the Joanne Myers Apartments.
Conceptual modelling involves following a few steps in order to achieve the final conceptual model for
the database. The first step is thoroughly studying the case study to get information on all the entities
and their attributes and different relationships that exist between the entities. After identifying the
entities, the next step is to define the business rules to come up with the types of relationships that exist
between different entities. After identifying the entities, relationships and the type of relationships an
entity relationship can be drawn. To draw the entity relationship diagram, Microsoft Visio 2013 is used.
The entity relationship shows all the entities and their respective relationships. Primary keys and foreign
keys are clearly in the ERD. The last step is providing a justification and explanation for the relationships
achieved in the ERD. The justifications should be aligned with the case study and for cases where the
case study is not clear, the assumptions are stated.
While performing the task some problems rose that limited the final conceptual design from being
absolutely perfect. This problem experienced while drawing the diagrams using Microsoft Visio.
Arranging the diagrams so that the relationships do not overlap was lot of work as it involved doing a lot
of rearrangements to make sure all the relationships are flowing fine without overlapping.
2 Business rules
Based on the case study, the following are the business rules derived;
Entity: COMPLEX
Each complex has one or more APARTMENT
Each complex is managed by one LESSEES.
The manager of a complex must a tenant in the complex.
Entity: 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
Entity: PREISNTALLED_EQUIPMENT
Each PREINSTALLED_EQUIMENT is allocated to only one APARTMENT
Entity: PARKING_LOT
Each PARKING_LOT is associated with only one APARTMENT
Each PARKING_LOT belongs to only one building COMPLEX.

Entity: 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
Entity: 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
Entity: MAITENANCE_COMPANY
Each MAINTENANCE_COMPANY can be associated with none or many MAINTENANCE_JOBS
Entity: MAINTENANCE_JOB
Each MAINTENANC_JOB is associated with only one APARTMENT
Each MAINTEANCE_JOB is done by only one MAINTENANCE_COMPANY
Entity: KEY
Each KEY results to one or more KEY_ALLOCATION records
Entity: RENTAL_EQUIPMENT
Each RENTAL_EQUIPMENT is associated with none or many RENTAL
Entity: RENTAL
Each RENTAL is associated with one and only RENTAL_EQUIPMENT
Entity: RENT PAYMENT
Each RENT_PAYMENT is done by one and only one LESSEE
Each RENT_PAYMENT is for a LEASE
Entity: LOCKUP_GARAGE
Each LOCKUP_GARAGE is associated with one and only APARTMENT

3 Entity relationship diagram
4 Summary
The following are the justifications explaining why the database design ended up as shown in the entity
relationship diagram shown above;
 The relationship between entity BUILDING and APARTMENT is one to many mandatory because
a building has one or more apartments. No building has no apartments and that is why the
relationship is mandatory.
 The relationship between entity APARTMENT and PARKING_LOT is one to many mandatory
because every apartment is allocated one or more parking slots. There is no apartment without
a parking lot but some apartments are allocated more than one parking slot.

 The relationship between entity APARTMENT and LOCKUP_GARAGE is one to one optional
because not all apartments are allocated a lockup garage. Only some selected apartments are
allocated lock-up garages.
 The relationship between entity APARTMENT and EQUIPMENT is one to many mandatory
because an apartment is allocated one or more equipment.
 The relationship between entity LEASE and APARTMENT is one to one mandatory because an a
lease is created for one and only one apartment and that is why the relationship is one to one
mandatory.
 The relationship between LEASE and LESEE is one to many mandatory because a lease has one
or more lessees. A lease cannot exist at least one lessee thus its mandatory for a lease to have at
least one lessee. A lease can have a maximum of 5 lessees.
 The relationship between APARTMENT and MAINTENANCE_JOB one to many optional because
an apartment none or many maintenance jobs.
 The relationship between MAINTEANCE_JOB and MAINTENANCE_COMPANY is one to one
mandatory because a maintenance job is done by one and only one maintenance company.
 The relationship between the LESSEE and KEY_ALLOCATIONS is one to many mandatory because
a lessee is allocated to one or more keys.
 The relationship between KEY and KEY_ALLOCATIONS is one to one mandatory because a key is
allocated only once for every key allocation record created.
 The relationship between LESSEE and RENT_PAYMENT is one to many mandatory because a
LESSEE pays makes one or more rent payments while staying in an apartment.
 The relationship between LESEE and rentals is one to many optional because a LESEE can make
none or many rentals for rental equipment as it not a must for a lessee to rent a rental
equipment.
 The relationship between a RENTAL_EQUIPMENT and RENTALS is one to many optional because
an equipment can be rented be none or many times. A rental equipment may exist and not
rented even once.
While modelling the database, a special case of data integrity that cannot be handled was realized. This
case of data integrity is for entity KEY specifically opens attribute. The opens attribute is saves what the
key opens for example if the key opens apartment 1 then the opens attribute is supposed to have
apartment 1 as the data. To identify the apartment that a key opens there should exist a relationship
between the key attribute and the apartment attribute. However, this is not possible because not all
keys open a specific apartment. Some key open different places like the gym.