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 (Monday, 30th October 2023)
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.

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

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.

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.

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 beconsidered; 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).

Business Description (Scenario)

Joanne Lee (J) owns three vet clinics and two pet care centres in the far north Queensland area. So far each clinic or centre has been running independently but she recently decided to reform her business to build an extended pet care group. Under this new business system, existing vet clinics and pet care centres will be combined as one business group named “FNQ Pet Care Group (FNQPCG)” and each clinic will be run as a branch of this giant company. Joanne has a plan to extend her business by building more

branch clinics or day care centres for pets throughout Queensland and has decided to develop a new central database system to store, integrate, and manage all relevant data and to computerize its operations. Once this central system is in operation, each branch clinic or centre will be connected to this central database and their data-related operations will be governed by this central database system. You have been asked to design a database that satisfies many user requirements provided by this company. General business description and various user requirements are summarized here:

FNQPCG runs a head office in Cairns while their branches (vet clinics and centres) are located in multiple places including Cairns and other regional towns. The central database keeps information about branches, staff, equipment, customers (pet owners) and their pets, and visit information. Each branch has an ID number that uniquely identifies the branch and the authorized users of the database should be able to track the branch’s name, location, and details of employees (staff) who currently work or had worked previously for the specific branch.

Each branch of FNQPCG is supervised by a branch manager. It is expected that the FNQPCG database will be used to generate reports for various HR (Human Resource) related jobs. For example, using the company’s central database, an authorized user of FNQPCG should be able to generate all the information required for various HR reports. For such reporting purposes, they may need the current employee records of a specific branch, the employee history of a branch including all records of previous managers and other employees, the total salary amount the company paid for each branch in a specific year, etc.

FNQPCG employees are professional veterinarian staff or non-veterinarian staff such as admin staff. To meet the industrial legislation, the company must keep the relevant qualification or license records of each professional veterinarian staff. Most of FNQPCG staff are permanently employed by the company and their employment is governed by the central group, but some local staff are employed at a branch level temporarily (called “casual workers”). An authorized user of the database should be able to generate a summary report to show the status of professional human resources FNQPCG keeps currently. The information about casual workers is recorded in the database but only basic information (name, address, DOB, start date, end date, payment rate, work hours, etc.) is required to be kept and managed.

All FNQPCG employees (including veterinarian staff) have to wear the company uniform during their working time and the uniform varies on their type of work or practice role in the company. Some employees have to use a portable tablet computer for specific purposes jobs. Uniforms and computers are provided at no cost when they join to work, and they need to be returned when the staff leave the company or do not need to work for the specific job. Casual workers are also provided a uniform but not a tablet computer. Each uniform and computer has a unique number for stock-take purposes. Uniforms or computers are fully managed by FNQPCG for any issue including repair. Employees can return their hired items when needed to replace them with other items. The authorized user of the database should be able to generate a report showing, for each uniform, the uniform’s number, its size (small, medium, large, etc.), type (clinical, admin, etc.), and the name of the employee it has been supplied to. In a similar way, the user should be able to generate a report that shows for each computer: the computer number, type, model, other specifications, history of repairs (if exists), and the name of the employee(s) it has been hired by. In order to manage and restrict unnecessary hiring of uniforms or computers by one staff, every staff can hire up to a maximum of 5 uniforms and up to 2 tablet computers even though they could have hired and returned an unlimited number of hiring items previously.

FNQPCG also wants to keep all customer-relevant data central so that the head office can manage the status of all customers (pet owners) and their pets of each branch and can generate the end-of-year (or term) report to compare each branch’s customer status (in terms of current numbers, growing rate, etc.). FNQPCG runs an incentive system to reward annually the branch that achieved the most significant growth rate throughout the year.

Customer information (usually pet owners) includes contact details to enable newsletters and other information to be mailed to the customer. There are different billing rates depending on the customer’s membership status. Staff members receive a 25% discount on all services and medications while V.I.P. customers may qualify for a 10% discount. The discount rates are reviewed each year and are open to change (e.g. Staff discount rate was 20% but at the last wage bargaining discussions it was agreed to increase the rate to 25% in lieu of a wage rise).

Information stored on pets (whoever has visited a clinic or care centre) includes name, type, breed, length, and weight. The clinic or centre takes a digital photo of all pets and would like to store this information in the database. The vets would also like to be able to store some clinical comments about the animal with their records as well. If a pet has not been in for a visit in the last three years, then the animal’s records are removed from the database. The clinic encourages yearly check-ups of all their clients - if a pet has not been in for a visit in a year, then a reminder notice is mailed to the owner.

For the vet clinic branch, every visit information should be recorded to keep details about treatments performed, and medications dispensed. All customers receive one invoice for each visit. Each invoice should show all items involved in the visit. Some items are treatments, and some others may be medications. If the visit involves more than one pet, each pet’s treatment and medications appear grouped together with a subtotal. All visits incur a 10% Goods and Services Tax which is shown on the invoice. The company’s central database keeps all information on all treatments and medications with the charge amount, thus they are automatically applied to the invoice.

As each pet-care branch provides overnight accommodation service or day-care service, both booking records and actual visit records are managed by the database system. As each pet care centre has a limited number of spaces (cages) for overnight accommodation and has limitations in the maximum number of pets they can care for each day (for day-care), the central database system will be really useful for each centre to manage their facilities and services provided to their customers for booking and daily operations.

The database system you design will help the company’s authorized staff to produce a number of reports that can be used by authorized users. These include a current Pets and Owners directory, visit invoicing reports, and mailing labels. Samples of what the client expects these reports to look like are shown on the next page of this document. Please note that these are just some report samples and more various reports should be able to be created by the branch staff or head office once the database is fully implemented.