Please know that you will need to download the free program: MySQL Workbench 8.0 CE to complete this.
You have been commissioned to design a database to manage the i - Page 1 - 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. Assessment 1 – Database (conceptual) modelling Due date: End of Week 8 (Sunday, 13th November 2022) Assessment Weight: CP2404 - 30% CP5633 - 25% Rationale This assignment has been designed to assess students’ ability to model a database, 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 and normalisation techniques Requirements You are given a business scenario and are required to draw an ERD for each scenario. You must use crow’s foot notation and are only allowed to use MySQL Workbench to draw your ERDs. A hand-drawn ERD or ERD drawn using other tools will NOT be accepted. The completed ERD must be fully labelled and implementable by presenting all necessary components including entities, relationships, connectivities, cardinalities, optionalities and constraints. Each entity should contain basic but necessary attributes including primary keys (PKs), foreign keys (FKs) and other attributes. You do not need to add unnecessarily additional attributes to an entity by your own imagination. To decide some optionalities, you may need to make your own assumptions due to the lack of information you can achieve from the given text scenarios. You are required to accompany the list of assumptions you made to the ERD. Submission Include the following in your submission (via LearnJCU): • A document file (MS Word or PDF format) which contains the image of ERD and list of assumptions. • The original Workbench file containing your ERD (.mwb) You can submit as many times as you want until the submission due, but only the last submission (made before the submission due) is graded. If your (first) submission is made after the submission due, only the first submission is graded and the late submission penalty will be applied as per the policy. Refer to the subject outline for the policy on late submissions. - Page 2 - To help your understanding about the requirements of this assignment, a sample ERD solution for a given sample scenario is provided below. Sample Task and Sample Solution o Task (sample) Imagine you are asked to create a conceptual database model by drawing an ERD for a business scenario as follows: A caravan park has several types of accommodation: On-site vans ($30), Standard cabins ($33), En-suite cabins ($45) and Deluxe cabins ($49). For each type of accommodation, there are several sites available in the caravan park. For example, sites 2, 3 and 11 are all Standard cabins. When a customer first arrives or makes a booking, they are assigned a customer code. They are asked their name and expected departure date and are booked into a site. o Solution ERD (for the sample task) The sample solution ERD and all assumptions made for this model are as follows: Note: The red and green star symbols are not part of the ERD, but are used here to indicate points about which an assumption has been made. The optionality side (green star symbol) was decided based on the information provided in the business scenario. For the decision on the red star symbol sections, we needed to create our own assumptions. Three assumptions made for this sample solution are described below. Assumptions: 1) A site can have zero or more booking records. There can exist brand new sites that have never been booked before. In these cases, the site record is stored in the SITE entity but no corresponding booking record exists in the BOOKING entity. 2) A site can only be included in one or no type. Some sites can exist without being recorded as any type of accommodation. The site’s record is stored in the SITE entity but no corresponding TYPE record may exist. 3) An accommodation type can have zero or more corresponding sites. Some accommodation types are recorded with a description and price in the database (TYPE entity), but the caravan park may not have any sites allocated to that type. - Page 3 - To reach a solution, the following steps must be followed: 1. Identify all necessary entities. By reading through the scenario, you will find essential entities to be included in your model: CUSTOMER, TYPE and SITE 2. Consider relationships between any pair of entities. If you find a relationship between two entities, those two entities must be connected via the relationship line and proper name (generally ‘has’) in the ERD. You also have to consider the relationship’s cardinality (generally 1:1, 1:M or M:N or a specific number of occurrences should be considered; e.g., 1:M (but minimum 3, maximum 10)). For example, for the given scenario, you found that a relationship must exist between CUSTOMER and SITE considering that a customer may have booked (and stayed) one or many times. Here, do not forget that a relationship is always bi-directional. This means that you should consider both the relationship CUSTOMER to SITE, determined as 1:M, and the other direction of the relationship, from SITE to CUSTOMER. Consider how many customers can be related to one site. Obviously, one site may have been booked by many customers; thus the one-directional relationship from SITE to CUSTOMER should also be 1:M. As a result, a combined bi-directional relationship between SITE and CUSTOMER would be M:N. In the same way, you can determine the relationship between TYPE and SITE as 1:M considering that an accommodation type can have many corresponding sites and one site must be allocated as one accommodation type only. For 1:M relationships, you do not need to consider further. For M:N relationships, however, you need to implement the M:N relationship on your ERD by creating two separate 1:M relationships, which forces creation of a new intermediate bridge (composite) entity. For this bridging purpose, a new entity BOOKING is created. Consequently, the relationship between CUSTOMER and BOOKING is set as 1:M, as is the relationship between SITE and BOOKING. 3. Identify attributes for each entity (including a bridge entity if created in the previous step). Set PK first and then set other necessary attributes by referring to the scenario outlined. Also, add FKs to entities appropriately to ensure entities are integrated correctly. 4. Consider optionality for each side of the relationship. Once a relationship has been determined in terms of cardinality (1:1, 1:M or M:N), it is time to determine the optionality for each side of the relationship. For example, the relationship between CUSTOMER and BOOKING is connected by 1:M relationship. There are two optionalities to be determined: one for the CUSTOMER side and the other for the BOOKING side. For the CUSTOMER side, you need to consider two possibilities: 1) a booking record may have no corresponding customer (the customer value can be ‘null’ for one booking record), or 2) a booking record must have the corresponding customer value filled in (the customer value should not be ‘null’ in every booking records). The decision should be made on the basis of the business scenario provided. Does the scenario clearly indicate that a booking record must be created only when the customer is identified? If yes, the optionality of the CUSTOMER side should be ‘mandatory’ and annotated as ‘I’ on the ERD. Alternatively, can a booking record be stored without customer details? If yes, you should put ‘o’ to denote the optional participation of the relationship. If you are not sure, you will need to make your own assumptions for each decision about optionality. The assumptions must be clearly described. 5. Check and present weak/strong relationships. Finally, check if the relationship line is correctly presented (dotted line or solid line). For this sample solution, the relationship between BOOKING and SITE is presented as a solid line (strong relationship because the PK of SITE is used as a part of the PK of BOOKING). - Page 4 - Business Description (Scenario) A company named FNQ UNITED APARTMENTS (FUA) currently owns twelve (12) different apartment complexes in many locations spread over northern Queensland areas - Cairns, Townsville, Port Douglas, Ingham, Tully, etc. Each apartment complex has a number of separate apartments, of varying sizes. All apartments are leased with a six month or a 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. For most cases, the manager needs to arrange relevant local trade person or company to conduct necessary works. These can be billed back to the parent company (FUA). 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 FUA company head office in Townsville every quarter. This report summarizes the occupancy rate, the total revenues in rent, the total expenses in maintenance and repairs, etc. As FUA does not run an established database system, 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 develop 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