In this assignment (assignment-1), you are to implement the data modelling for a database that is based on the following application scenario.
The application scenario
The Best Home real estate company needs to implement a database to manage properties. The real estate has delegated this task to an IT service company. As an officer of the IT service company, you are delegated to be the designer and developer of the database. To accomplish this task, you will divide the design and development tasks into two assignments. In this assignment (assignment-1), you will do the data modelling. In assignment-2, you will design and implement the database and relevant SQL queries. Consequently, a successful data modelling of this assignment is also important for assignment-2.
The application scenario of The Best Home real estate is as follows.
- The Best Home manages two types of property, property for rent and property for sale. A property could be available at the moment, rented or sold.
- The people who need to be managed by the database include the property managers of The Best Home, the property owners, the customers (buyers and tenants) and the solicitors.
- A property manager can manage sale properties and/or rental properties. A property manager can supervise other property managers.
- A customer can make an application for a rental property or give an offer to a sale property.
- A solicitor may involve in a property sale.
- A property manager, a property owner, a customer or a solicitor could have a phone, a mobile and/or email contact.
The above application scenario was not presented in very detail for the entities or relations because one of your tasks for this assignment is to analyse and model the data in real estates. You may need to do some research about the data in real estate management and model the data with necessary attributes under some assumptions.
The assignment specification
You are to develop an ER model for the data management of The Best Home. You will need to provide the ER model to cover the data and transactions of The Best Home. Your tasks are:
- Identification and justification of entities. You need to identify all the necessary entities including super/sub type entities and justify your assumptions on those entities for the given application scenario.
- Identification and justification of relationships. You need to identify all the necessary relationships and their cardinalities and justify your assumptions on those relationships/cardinalities for the given application scenario.
- An ER diagram. You need to use the standard notations (as given in the textbook) to present the ER diagram, clearly representing entities and relationships, cardinalities and primary and 2 foreign keys and using super types and sub types. The M:N relationship between entities is not allowed for the diagram. Thus you will need to translate an M:N relationship into multiple 1:M relationships with the aid of associative entities.
Note: use of a formal modelling tool such as Microsoft Visio is not required for the ER modelling. You can use any drawing tools to create the ER diagram and put it into the document.
Your assignment must be detailed in and submitted as a Word document via the online submission system. Any email or hardcopy submission will not be accepted. Any late submissions of assignment will not be accepted after the marked assignment has been released.
The Marking Criteria
|Marking Criteria||Available Marks|
|The identification and justification of entities||8|
|1. Whether the necessary entities have been identified||4|
|2. Whether the composite attributes and multi-valued attributes have been identified||2|
|3. Whether the primary keys and foreign keys have been identified.||2|
|The identification and justification of relationships||8|
|1. Whether the necessary relationships have been identified||4|
|2. Whether the cardinalities of the relationships are correct||4|
|The ER diagram||12|
|1. Whether the standard notations have been used||2|
|2. Whether entities and associative entities have been differentiated||2|
|3. Whether the primary keys and foreign keys have been correctly represented||4|
|4. Whether super types and sub types have been used||2|
|5. Whether the M:N relationships have been translated into multiple 1:M relationships||2|
|Whether the modelling has covered the application scenario||2|
|Sub Total for Assignment-1||30|
|Late Penalty||-1.5 (5% ) for each calendar day (either full or part)|