Rationale

This assignment has been designed to assess students’ ability to model a database, by constructing an Entity-Relationship Diagram (ERD) for a particular business scenario. This assignment addresses  the following learning objectives for this subject.

•    Develop adatabase model using the Entity-Relationship Model

•    Apply the techniques of normalisation

Requirements (Tasks)

You are given a business scenario and are required to draw an ERD for the scenario.

• Task 1 : Draw an Entity-Relationship Diagram (ERD), which is fully labelled and

implementable,  based on the business descriptions. Include all entities, attributes,

relationships, optionalities, connectivities, captions, cardinalities and constraints. You must use Crow’s foot notation and MySQL Workbench to create the ERD. A Hand-drawn ERD

will NOT be accepted.

• Task 2 : In a one-page Word document, write a short summary in point-form to describe   the major justifications, assumptions and limitations related to your database design. For example: Assumption/justifications for optionality, connectivities, constraints data type

and data domain; and Special cases or data integrity issues that cannot be handled.

Maximum number of pages is One - A4 size. There will no marks granted for contents from page 2 onwards.

Submission Items (3):

Include the following in your submission (via LearnJCU):

•    The original Workbench file containing your ERD (.mwb). Name the file as

LastnameFirstname-A1.mwb

•    Export the ERD diagram as a picture PNG file. Name the file as LastnameFirstname- A1.PNG.

•    A document file (DOCX or PDF format) which contains your answers for Task-2. Name the file as LastnameFirstname-A1.docxor LastnameFirstname-A1.pdf

Business Description (Scenario)

Victor is a rich businessman in Singapore. He decided to launch a group of dog-clinics

using the name of ‘HappyDogs Pte Ltd’. Over time, he plans to open more branches. He

decided to develop a new central database system to store, integrate and manage all

relevant data and to computerize the overall business operations. Once this central system is in operation, each branch clinic will be connected to this central database and their data- related operations will be governed by this central database system. You have been hired

to design a database that satisfies several business requirements provided by Victor for his HappyDogs business.

Below are the various business requirements :

1.  The database will keep information about branches, employees, gadgets, customers, their dogs and their clinic-visit information.

2.  There will be a headquarters clinic, while the rest of the clinics are just branches. Each branch has an ID number that uniquely identifies the branch, along with

branch’s name, location, etc.

3.  Every branch will have employees working there. Each branch is supervised by a branch manager, who is also an employee. Employees may be transferred to

different branches over time to gain more job experience.

4.  Employees are of two employment types – permanent and contract. Permanent

employees are either vets or admin staff. A vet is a doctor for animals. There is a

standard list of vet-certifications that HappyDogs recognizes, identified by unique

ID, title and description. The certifications that the vets acquire over time are

tracked in the system, with acquired date and expiry date. Admin staff attend to

administrative matters and each of them are designated a room each. Contract staff are hired on a need basis, when there is a surge in workload. General information

such as name, gender, birth-date, address, mobile number, etc should be recorded,  for all employees. Permanent staff have grade level, annual and sick leave. Contract staff have specific pay rate and work hours per week.

5.  Employees salary history needs to be kept track of. Whenever the salary of an employee changes, the amount and the dates are recorded.

6.  In order to enable employees’ productivity, certain technical gadgets (such as

mobile phones and smart tablets) are issued to employees. Gadget information

such as date of purchase, purchase price, warranty expiry dates, brand, model,

colour, etc. are tracked. All employees are given a mobile phone for official use.

However, only permanent employees are given a smart tablet using which they can access various IT systems and applications of the company. Contract staff do not

get such tablets. All gadgets have a unique number for stock-take purposes. Mobile phones have a specific telecom subscription and payment plan. Tablets are of

specific screen size and different weights.

7.  The mobile phone and tablets are assigned to the employee when they join to work. An employee may be assigned a maximum of 2 gadgets only at any point of time.

When the employee leaves the company, the gadgets need to be returned. The new database system should keep track of the dates of gadget assignment and gadget

return from the first day of operations.

8.  Gadgets can go faulty over time due to improper usage. Faulty gadgets are taken for repairs by the company and this information is tracked. Details of every such  faulty occurrence is recorded such as date, fault description, repair cost, etc.

9.  All customer-relevant data should be stored so that the business activities can use it to build good customer relationships and grow the brand. Customer information includes  the  usual  particulars,  date  of  first  visit  and  contact  details  to  enable newsletters and other information to be mailed to the customer.

10. There are different discount rates for different customer, based on how often they visit and how many dogs they bring to the clinics.

11. Regarding  the  dogs  that  the  customers  bring  to  the  clinics,  certain  essential information about them is also collected and stored, such as the dog’s name, age, breed, colour, weight, medical conditions, etc.

12. Customers can bring their dogs to any clinic branch for treatment. Visit information should be recorded in the system, such as visit date, the vet who attended to the dog and the invoice generated for that visit. The vet may also record general comments about each visit. A customer may bring more than one dog to the clinic, but a specific visit is recorded for each dog.

13. Every clinic visit by the dog must have atleast one consultation aspect and it may have a medication aspect, if the vet prescribes medication. The system must record details  about   consultations   made   and   medications   dispensed   for   all  visits. Consultation and medication information can be structured in the database.

14. All customers receive invoices for their visits. If the customer visited the clinic with more than one dog on the same day, the invoice would cover the multiple visits, and show the breakup of consultation and medication costs for each dog. All visits incur a Goods and Services Tax(GST) which is also shown on the invoice. A sample invoice is provided in the Appendix at the end of this document.

15. Based on the information stored in the database, several of the following reports are expected to be generated for use by the management.

•     List of new customers acquired during a particular year – to evaluate customer growth.

•     List of dogs and their owners, with contact details.

•     List of all current employee records of a specific branch.

•     Show the employee history of a branch including all records of previous managers.

•     List of all mobile phones, its features and the employee to whom it has been assigned to.

•     List of all faulty smart tablets over a particular date range with details of repair and repair-costs.

•     List of all medications and consultations prescribed during a particular month in a year – to understand specific common problems that the clinics handled

during that period.

•    Above are just some report samples. Staff may need various other reports

based on situation needs. Once the database is fully implemented, it should be possible for new reports to be generated.