-
QUESTION
In this assignment, you are a database analyst with the task to analyse a scenario, design, develop and test a database in MySQL using phpMyAdmin.
It is your job to analyse the data requirements provided in the scenario and design and develop a relational database to meet the client needs. You will also need to add data and create SQL queries to provide results suitable for reporting. This assignment intends to give experience in database design and development. It is based on a fictitious scenario. Your focus will be to:
Produce a fully normalised database design, modelled in an EERD, showing business rules;
Develop entities (tables) with correct attributes included;
Demonstrate supertypes and their associated subtypes;
Demonstrate normalised relations;
Make relational joins to ensure this prototype works;
Produce evidence of correct working via database queries and screenshots of result sets.
Assignment 3 Part A – Enhanced Entity Relationship Diagram (10%)
Design and produce an Enhanced Entity Relationship Diagram (EERD) using a modelling or drawing tool. Present your assignment in a Word or PDF document using report template 3A. The report should include a title page, the EERD and business rule clarification (if necessary).
Assignment 3 Part B – Develop a Database Prototype (20%)
Build and test a database prototype based on your design in Part A. Submit a report documenting the end product using report template 3B and SQL script of your database.
- Scenario:
FIT CLUB Health Centre is a fitness centre with various facilities and has many branches across Australia. Every branch provides standard facilities of 24 hours gym and a swimming pool, and some additional facilities. A branch is called a club and supervised by a manager. The manager is responsible for the management of club facilities, members, trainers and classes arrangement.
CLUB
As of now, there are eight clubs across Australia: two in New South Wales, one in Western Australia, one in Victoria, two in Queensland, one in Tasmania, and a new club in Southern Australia. Every club owns many trainers as class instructors and personal trainers. The facilities for each club may differ depending on the manager preferences. However, the standard facilities for each club should are a 24 hours gym and a swimming pool. Other facilities that are available are cycle studio, kids playroom, sauna room, and outdoor training park.
MEMBERS
FIT CLUB uses two type of membership system: the first category is all access members who can access all facilities in clubs, the second category is class-only members who are only participating in the clubs classes. The fee for the all-access member is $15 per week and $5 per week for the class-only member. Members have to choose a home club when registering, but the membership allows them to visit any clubs across Australia.
If a member wishes to leave the club, the member data will still be stored although the membership status will be set to inactive. In another case, if a member will be away from Australia for more than a month (e.g. vacation, overseas work), the membership status can be put on hold, and it can be activated again.
CLASS
FIT CLUB has set a standard list of classes provided in all clubs. However, every club may have a different class timetable depending on instructors availability. Class instructors are assigned from club owned trainers.
TRAINERS
A club can employ as many trainers as required although two different clubs cannot employ the same trainer at the same time. Trainers will be responsible for being class instructors and offering personal training service. The personal training service is available for any members, although there is an additional charge for the service. The fee varies among trainers, but it is set on a weekly basis. Each trainer has his/her specialisation, ranging from nutrition, weight loss, bodybuilding, to corrective exercise.
This health club requires a database to store the data associated with all branches, facilities, members, trainers, classes, and personal training service. The system of spreadsheets and paper forms that FIT Health Club is currently using has many data inconsistencies that detract from their belief in the accuracy of the data and information provided. Your task is to analyse, design and develop a prototype database, also test it with queries which are likely to be used by the health centre management. You are not required to write an application to use with this database.
| Subject | Nursing | Pages | 4 | Style | APA |
|---|
Answer
Database Systems
The database system developed in this paper is of a scenario of the telemedicine in a healthcare facility. On admission, the patient’s and the Contact person’s records are taken, tests are done by a medical practitioner and are recorded in the information systems, as well as the prescription and visit dates. Further, details of the visit data are recorded in different class. The different classes of necessity are accessible and interconnected in the system to the administration and the respective clinicians. The classes in this EERD diagram have respective subclasses that are necessary for its database management.
The EERD diagram below has been created on LucidChart, with the entities and relationships well indicated. The database enhances process flow, information storage and information availability to reduce medical error. The development of this database system is poised to heighten patient-clinicians satisfaction through reducing the treatment timeline in the entire cycle by enhancing the relationships of different entities in the healthcare facility and thus enabling information coordination and exchange in a single cycle. The database developed in oriented towards out-patients only.
Fig 1: EERD for telemedicine in a hospital facility.
The client rules for this facility are:
- Every patient’s details must be fed in the database before treatment process can be initiated.
- Clinicians who attend to a client patient from the first visit are expected to be the ones to monitor their progress over time throughout the treatment. However, in case of unavailability of specific physicians and attendance has to be by another, duty transfer procedures are initiated. In such a case, the system is accessed through the administration for entirety.
- Administration is supposed to take information of patients and doctor from time to time, being the patrons of information exchange or custodians of all the information in the facility.
- Every out-patient in contact with the facility should at least have a contact person. Failure to this, patients are admitted for inpatient care. This is influenced by the fact that the facility is referral and deals with ambulatory patients.
- Every test and prescriptions have to be documented and uploaded in the system for future reference.
- Visit data are meant to ease the load of searching medical information (Therefore they are stored separately from the visit description class. The entities, though, relate to each other to each other in case of referral treatment).
- Contact persons are to be contacted strictly by the administration through the call office in cases of needs, unless urgency demands otherwise.