Extend existing database to add rentals and more property details

Justification of ER Model

TheER model is extended based on the updated case scenario of Garden Glory. Create entities and attributes as realistic as possible but within the scope of the requirements. Thus to support the additional requirements, following things are done:

  • Createan Equipment entity that would store all the equipment that can be used on a service. And decided to that a service can use one or more equipment because there is no one tool that does all the job in gardening. However, sometimes gardening does not need an equipment thus gave it an optional to many cardinality.
  • Create a new entity called “EquipmentTrainedEmployee” that would store and map the employee and the equipment they know how to use. The relationship between the Employee entity and the Equipment Entity is many-to-many because an employee can be trained how to use many equipment and an equipment can be learned by many employee. The cardinalities are optional to many because employees are not required to learn at least one equipment or equipment has to be learned by at least one employee.
  • The last entity created is called the “SubProperty” which stores the information about a property’s part. As the requirement has stated, a property can have many parts called “sub properties” so a relationship between the Property and SubProperty entity with a cardinality of one to many is placed. A Property is required to have at least 1 SubProperty and it’s not optional because the services are mapped to a SubProperty and not the overall property.
  • To become an owner, logically one has to own a property which is why “at least one” cardinality is used between the Owner entity and the Property entity. The model supports owner can own multiple properties. A property should only be owned by one registered owner although this does not represent the real case that a property can have multiple owners but this does not matter because the business is not about the legality of who really own and how many owns the property. The thing that only matters in the business is we need to at least know who is responsible for registering the property to the database that needs servicing and it does not necessarily mean that the person who registered the property for servicing is the actual owner.
  • Logically a sub property can be avail of many services and each service is handled by an employee. Employee can also service other sub properties as well.

Validating the Model Developed

Here is a bullet points on how the model developed will be validated:

  • Make sure that with the new extension, the previous design must not have been altered. All the previous data should still be captured by comparing the design to the requirements. After which, we need to validate the new extension if it also captures the required data based on the new requirement. It should be compatible and complete.
  • Next would be to validate the cardinalities and relationships between entities whether they are logical and appropriate based on the requirement. Check whether an entity is optional, required, and its multiplicities matches on what is required.
  • Finally to check whether it is normalized to avoid duplicate data. This can be visualized by using sample data for each column and check for duplicates. This also helps in checking whether the primary keys are referenced by its foreign key counter parts.

Business Rules and Assumptions

  1. An owner must register at least 1 or multiple properties but each property can be owned by only one owner.
  2. A property must have at least 1 sub property. A property can have multiple sub properties. Each sub property is only located to a single property.
  3. Each sub property can be serviced multiples times. A service is only applied to a single sub property.
  4. A service can optionally use 1 or more equipment to perform its job. An equipment can be used in other services.
  5. A service is executed by only employee who is trained to use the equipment. An employee can work on multiple services for different sub properties.
  6. An employee can optionally train 1 or more equipment. Equipment can be used to train other employees as well.

Relationship Properties

Relationship Cardinality
Parent Child Type Max Min
Owner Property Non-ID-dependent 1:N M-M
Property SubProperty ID-dependent 1:N M-M
SubProperty Service ID-dependent 1:N Mandatory-Optional
Equipment UsedEquipment Non-ID-dependent 1:N Mandatory-Optional
Service UsedEquipment ID-dependent 1:N Mandatory-Optional
Equipment EquipmentTrainedEmployee ID-dependent 1:N Mandatory-Optional
Employee EquipmentTrainedEmployee ID-dependent 1:N Mandatory-Optional
Employee Service ID-dependent 1:N Mandatory-Optional