Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others.
Assignment 1 – Database (Conceptual) Modelling
Due date: 20th april 2018
Assessment Weight: 20%
Rationale
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
Requirements (Tasks)
- 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)
- 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.
- 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)
- 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.
Submission
- 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 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:
The second example is of a spreadsheet used to track rent payments.
The third is an example of tracking Maintenance requests and responses
Finally, here is an example of the report that each apartment manager must turn in to the main office quarterly.
JMA
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.
APPENDIX A SAMPLE BUSINESS RULES & ERD
Business Rules
Entity: SCHOOL
Each SCHOOL has at least ten or many STUDENTs.
Each SCHOOL has one or many SUBJECT.
Entity: STUDENT
Each STUDENT can take many SUBJECTs.
Each STUDENT has one or many ENROLMENT record.
Each ENROLMENT record is related to one STUDENT.
Each STUDENT is associated with one SCHOOL.
A STUDENT is resident of one CITY.
Entity: SUBJECT
Each SUBJECT is offered by one SCHOOL.
Each SUBJECT has zero or many STUDENTs.
Each SUBJECT has zero or many ENROLMENT records.
Each ENROLMENT record is related to one SUBJECT.
Entity: CITY
Each CITY has zero or many STUDENT lived in it.
Each CITY is in one COUNTRY.
Entity: COUNTRY
Each COUNTRY has one or many CITY.
Note:
If you want to present the supertype-subtype relationships using an extended-ERD (EERD), please refer the following link to get an idea about how to use Microsoft Visio to present the hierarchy. (Not available for Vision 2013. Only for pre version of Visio 2010)
Alternatively, you can present the superbype-subtype relationships using multiple 1:1 relationships and describe the special relationships in your document.
Assignment – Part 1 Database Modelling: Marking Criteria
| Criteria | Exemplary | Competent | Marginal | Unacceptable | |
| Solution Outline | A brief discussion of your solution outlining the approach taken. | 10
The solution is accurate, logical and desirable. The discussion of solution is appropriate and approaches for the solution chosen is properly detailed.
|
4-9 The solution is discussed but not completely desirable but the solution chosen is properly detailed.ORThe solution chosen is appropriate but the discussion of the solution is not properly detailed. |
1-3
The discussion of the solution is attempted but mostly incorrect and poorly detailed. |
0
Not attempted |
| Database Modelling
|
Entities
(presented in Business Rules and ERD) |
10
All required entities (based on the business scenario given) are included both in Business Rules and in ERD. All entities are in 3NF
|
5-9 All entities included are in 3NF but some required entities are missing either in Business Rules or in ERDORMost required entities are included but some entities are not in 3NF (need to further normalised) |
2-4
Many of required entities are missing but most of them are in 3NF OR Required entities are attempted to be included but most of them are not in 3NF
|
0-1
Most required entities are missing and most included entities are not in 3NF |
| Attributes (Fields)
(presented in ERD)
|
10
Attributes in each entity are relevant to desired information and correctly designed. All required attributes are included. Attributes are named correctly and logically |
5-9 Some of required attributes are missing but most of included attributes are named appropriatelyORMost of required attributes are included but named inappropriately |
2-4
Many of required attributes are missing OR Most of included attributes are named inappropriately |
0-1
Most of required attributes are missing or incorrectly designed (included in a wrong entity etc.) |
|
| Connectivities
(presented in Business Rules and ERD) |
10 All relationships are correctly designed and presented both in Business Rules and in ERD. |
5-9 Most but not all (more than 50%) relationships are correctly designed and presented in Business Rules and ERD |
2-4 Some (less than 50%) relationships are correctly designed and presented in Business Rules and ERD |
0-1
Most of required relationships between entities are missing or incorrectly designed. |
|
| Cardinalities & Optionalities
(presented in Business Rules and ERD) |
10
All cardinalities and optionalities are appropriately designed and presented both in Business Rules and in ERD. |
5-9 Most but not all (more than 50%) cardinalities and optionalities are correctly designed and presented in Business Rules and ERD |
2-4 Some (less than 50%) cardinalities and optionalities are correctly designed and presented in Business Rules and ERD |
0-1
Most of required cardinalities and optionalities are missing or incorrectly designed. |
|
| Relationship Names
(presented in ERD) |
5
All relationships presented in ERD are appropriately named and well corresponded to Business Rules |
3-4 Most but not all (more than 50%) relationships are named appropriately |
2
Some (less than 50%) relationships are appropriately named |
0-1
Most relationship names are missing |
|
| Relationship Strength
(presented in ERD) |
5
All relationship strength (weak or strong) is appropriately designed and correctly presented in ERD (dotted line or solid line) |
3-4 Most but not all (more than 50%) relationships are presented correctly corresponding with its strength |
2
Some (less than 50%) relationships are presented correctly corresponding with its strength |
0-1
Most relationship strengths are not presented appropriately |
|
| Primary keys | 10
All primary keys are correctly identified and unique |
5-9 Most but not all primary keys are correctly identified and unique |
2-4
Some primary keys are correctly identified and unique |
0-1
Most primary keys are not properly identified or unique |
|
| Foreign keys | 10
All necessary foreign keys are correctly identified |
5-9 Most but not all necessary foreign keys are correctly identified |
2-4
Some foreign keys are correctly identified |
0-1
Most foreign keys are not correctly identified |
|
| Presentation | ERD | 5
All components included in ERD are neatly and clearly presented without unnecessary complexity |
2-4
Some parts in ERD are not clear or easily visible due to inappropriate display of components |
1
Most of ERD are not clear or neatly presented. |
0
ERD is not included |
| Business Rules | 5
All Business Rules listed are well expressed without unnecessary padding |
2-4
Most but not all Business Rules are made and listed to meet requirements of the business scenario given. |
1
Some business rules are included but mostly inappropriate. |
0
Business Rules are not included |
|
| Assumptions and Justifications | 10
All necessary assumptions and justifications are appropriately made and listed. All assumptions are correctly incorporated in ERD |
5-9 Most but not all assumptions are made appropriately or correctly incorporated in ERD |
1-4
Some assumptions are made but not correctly incorporated in ERD. |
0
Assumptions are not made or listed at all. |
Previous answers to this question
This is a preview of an assignment submitted on our website by a student. If you need help with this question or any assignment help, click on the order button below and get started. We guarantee authentic, quality, 100% plagiarism free work or your money back.
Get The Answer