Assignment 1 Database Conceptual Modelling
Due date: 20th april 2018
Assessment Weight: 20%
This assignment has been designed to assess students’ ability to model data, by constructing an entity-relationship diagram for a particular business scenario. This assignment addresses the following learning objectives for this subject:
§ Develop a database model using the entity-relationship model
§ Apply the techniques of normalisation
1) You are to write a brief discussion of your solution, i.e. how you approached the modeling problem and any issues you may have encountered (maximum of ½ page)
2) You are to write all applicable business rules necessary to establish entities, relationships, optionalities, connectivities, cardinalities and constraints. If a many–to-many relationship is involved, state the business rules regarding the bridging entities after breaking down the many-to-many relationship. An example business rules format can be found in Appendix A of this document. Business rules you write are expected to be in the same format as presented in Appendix A.
3) Based on these business rules,* draw a fully labeled and implementable Entity-Relationship Diagram (ERD). Include all entities, relationships, optionalities, connectivities, cardinalities and constraints. You must use Crow’s foot notation and MS Visio to create the ERD. A Hand-drawn ERD will NOT be accepted. A sample ERD can be found in Appendix A of this document. (Note: The ERD created using Visio will need to be saved as an image file and then be included in your document file to be submitted)
4) A summary to describe the major justifications, assumptions and limitations related to your database design. For example:
· Assumption/justifications for optionalitiy, connectivities, constraints data type and data domain; and
· Special cases or data integrity issues that cannot be handled.
*NOTE: Designing the database is an iterative process; you may find yourself going back and forth between Tasks 1 and 3 to revise the design. Make sure that your final submission shows consistent design in the business rules and ERD.
· You need to submit a single document file (MS Word or PDF format) to LearnJCU. The document should include all the answers for Task 1-4. Please name the file as LastnameFirstnameA1.doc or LastnameFirstnameA1.pdf.
· Timestamp shown on LearnJCU assignment submission will be used to determine if the assignment is late or not. Refer to the subject guide for the policy for late submission.
Business Description (Scenario)
Joanne Myers Apartments (JMA) owns 20 different apartment complexes in Sydney, Brisbane, Gold Coast, Townsville and Cairns. Each apartment complex contains anywhere from 10 to 60 separate apartments, of varying sizes. All apartments are leased with a six month or year-long lease.
It is the company’s practice to hire one of the tenants to manage each apartment complex. As managers they need to admit new tenants to the building, collect rents from existing tenants, and finalise leases.
The manager also needs to maintain the apartments conducting any repairs, replacements, or renovations. These can be billed back to the parent company (JMA). For acting as manager, the tenant gets free rent and a stipend. The stipend varies depending on the size of the apartment complex managed.
Each manager is expected to send a report to the JMA company head office in Brisbane every quarter. This report summarizes the occupancy rate, the total revenues in rent, the total expenses in maintenance and repairs, etc.. Currently managers fill out a paper form and mail it back to head office. Many apartment managers have complained that preparing this report is a very difficult and time consuming process. Also, the managers at the head-office also have expressed concerns about the accuracy and verifiability of the reports.
To reduce these concerns and to improve the ease and efficiency with which the apartment managers conduct their daily business, the company is proposing to development a centralized database that can be used by the managers to track the daily business of their apartment building and to prepare their reports. You have been asked to design a database that satisfies general business description and various user requirements summarized above.
JMA also showed you some samples of various forms and reports to enhance your modeling job. The first example is of a spreadsheet to keep track of leases at one apartment complex:
There are more business descriptions/requirements provided by JMA and they are summarized here:
- When a lease can be made, all adult tenants (over 18 year olds) should be registered as a lessee and the basic information like name, date of birth, and contact number of each lessee should be kept. The maximum number of registered lessees for one lease is six.
- Some apartment complexes have parking spaces dedicated for each apartment unit. Each parking lot has its own number specified and is allocated for each unit. Most parking lots are in open spaces but some are under-roof. Some complexes provide a limited number of lock-up garages (single or double) and they are allocated for some specific units.
- For maintenance, the company keeps a list of local maintenance companies (e.g. electricians, plumbers, cleaners) contracted to JMA and the apartment manager selects one from the list to allocate a maintenance job. Sometimes a maintenance job can be made not for a specific unit but for general purpose of a complex – e.g. gardening, lighting in public areas, cleaning, maintaining the complex pool (if available) or other public equipments etc. – and all maintenance records will be kept and reported to the head office (quarterly)
- As managing all keys used for each complex (for individual units, lock-up garages, storages, electrical units etc.) is an important but complicated job, managers need to keep records of all keys issued by the complex. Each key has its master and some copies. Each of registered tenants is provided a key set of unit entrance door key, complex gate key, unit mail box key, public gym key (if available), etc. when the lease commences and is returned back when the lease ends. Keys issued for public spaces or equipments of the complex are kept and managed by the complex manager.
- Each apartment is equipped with a number of pre-installed equipments like a range hood, fire alarms, air-conditioners, a dryer, a dishwasher and some furnished apartments have additional furniture and equipments provided by the complex. Details of each asset item is recorded and kept to be managed. These asset items are inspected and maintenance periodically.
- JSM also runs a rental service for their tenants. Various house-goods and equipments are owned and stored by the company and rented to tenants. When the company purchases a new item, the item is assigned with its number and the database should keep relevant rental information like ‘which item was rented to whom living in which apartment for what period in how much rental price etc.’. JAM owns one storage at one each city (area) for this rental service thus tenants can rent necessary goods from the nearest storage.
Holding a PhD degree in Finance, Dr. John Adams is experienced in assisting students who are in dire need...
55 - Completed Orders
Canada, Toronto I have acquired my degree from Campion College at the University of Regina Occuption/Desi...
52 - Completed Orders
Even since I was a student in Italy I had a passion for languages, in fact I love teaching Italian, and I...
102 - Completed Orders
To work with an organization where I can optimally utilize my knowledge and skills for meeting challenges...
109 - Completed Orders
JOB OBJECTIVE Seeking entry level assignments in Marketing & Business Development with an organization...
202 - Completed Orders
Current work profile Project manager- The Researchers Hub (2nd Jan 2016 to presently working) Researc...
20 - Completed Orders
Sales Assistant, Mito Marina Assigned to the Stationery dept – assisted in merchandising, stock taking...
100 - Completed Orders
Personal Profile Dedicated and highly experienced private chauffeur. High energy, hardworking, punctua...
200 - Completed Orders
I'm Lizzy, full time education specialist in English, Essay Writing, Economics and Maths. Having Assi...
109 - Completed Orders