Case Study: CQUnited Property Management database system
CQUnited Property Management (CQUPM) is an Australian-owned business located in a large city that specializes in management of rental property and relevant services. The management of CQUPM has approached you to build a relational database that will be used as the most critical backend software component in their enterprise management system so that the relevant business data will be recorded appropriately and further information query will be processed efficiently. The proposed database system requires efficiently to record and track all listed properties, customers, staff and relevant rental activities. Consider the following user requirements carefully and design a database conceptual schema (i.e. E-R model) to support such an application.
The major business of CQUPM is to manage the property rentals, where the property managers accept entrusted rental management of the real estate from property owners. The property could be a house or an apartment. Each property should be described and recorded with the information including the property type, address, the number of bedrooms, the number of bathrooms, the listed weekly rental, property photo, as well as the available date. When a customer wishes to rent a property, he/she is required to make an application, which will be processed by a property manager. If an application is approved, the contract start date, end date, and weekly rental etc. must be recorded in the system. Usually, a property is managed by one property manager while a manager could take care of multiple listed properties. A property manager also can supervise other managers. From time to time, the properties under management may need some maintenance service or minor repair jobs (for example window glass broken and replacement, electrical fault, water pipe leaking etc.). The company has contracted the maintenance tasks to the qualified external employee, who will undertake the repair jobs as requested. The external employee could be a licensed electrician, plumber or handyman. The details of the repair job and complete date must be entered the system.
The people that the system needs to record include the property managers of CQUPM, the owners of the property, the customers and the contracted external employees. Their names, addresses, and contact phones are essential data in the database system. In addition, you should consider one or two extra attribute(s) to describe the property manager and the external employee.
The specification of above application scenario may be not presented explicitly with every detail on the entities or relationships in the required E-R model, because one of your tasks for this assignment is to analyze the requirement of users, identify necessary entities and relationships, and build the E”R data model for the case study. You may need to do some research on the data and possible model in general property management with complete attributes or under some assumptions, which should make sense for such an application scenario.
In this assignment, you need to create an Entity-Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations.
The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study.
Your ERD must
• show all necessary entities, attributes and relationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes, if any
• show participation and cardinality
• show associative entities, if appropriate
• show weak entities, if any
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes as specified in the unit text book
Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ERD accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about
what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly. You should justify each assumption in terms of the business, for example: in the case study regarding the E-R model for customers placing an order it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order. To get yourself started, ask yourself, 'If I were running this business, what things I need to keep a list of?' Write those things down. For each thing, what information would you need to record about it? How can it be identified? The answers to these questions will help you to develop your ERD.
2 Assumptions and Business rules
Provide assumptions and business rules relevant to your ERD.
3 Create Logical Design (Optional):
If you would like to perform this optional component of this assignment, you can map your Entity Relationship Diagram (ERD) into relations and make sure that all the relations are in 3NF. Provide all the relations in the following format (for example):
Customer (CustomerId, CustomerName, Street, Suburb, State, Postcode, Email)
Invoice (InvoiceNumber, InvoiceDate, Total, CustomerId)
foreign key (CustomerId) references Customer(CustomerId)