Reference no: EM132444937

CSC72001 - Database Systems - Southern Cross UniversityAssessment - Enhanced Entity Relationship Diagram

A. Overview

Your task is to analyse a scenario, and design, develop and test a database in MySQL using PhpMyAdmin. You are provided with the scenario and supporting documents. The intent of this assignment is to give you experience in design and development. It is based on a fictitious scenario.

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. Your focus will be to:

• Produce a fully normalised database design, modelled in an EERD, showing businessrules;
• Develop entities (tables) with correct attributesincluded;
• Demonstrate supertypes and their associatedsubtypes;
• Demonstrate normalisedrelations;
• Make relational joins to ensure this prototypeworks;
• Produce evidence of correct working via database queries and screenshots of resultsets.

Assignment 3 Part A - Enhanced Entity Relationship Diagram

Design and produce an Enhanced Entity Relationship Diagram (EERD) using a modelling or drawing tool. Present your assignment in a Word or PDF document with a title page, the EERD and business rule clarification (if necessary).

B. Scenario

The 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.

CLUBS
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 club 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 acrossAustralia.

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.

CLASSES
FIT Club has set a standard list of classes provided in all clubs. However, every club may have a different class timetable depending on instructor availability. Class instructors are assigned from club owned trainers.

TRAINERS
A trainer is employed by the centre and may be assigned to multiple clubs at a 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.

Your task

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 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.

The file CSC72001_2018_S1_Assignment3_SampleForms.docx provides you with sample forms and information. The file CSC72001_2018_S1_Assignment3_example_data provides you with further details and some specific examples of the current manual system data to get you started. Note that this data is provided "as is" and is not transferable directly to tables in the new database. The entities and attributes mentioned in the scenario and on the sample forms must be included. You will be required to normalise some of the above into smaller relations/tables prior to implementing your design into a database. You may add more attributes to help improve the design and efficiency of thedatabase.

C. Assessment Requirements

a. EERD Drawing
Using the scenario and the data requirements provided, you must analyse the data storage needs of the client. Identify all entities and attributes and the relationships between them. Using a drawing software tool (Visio, Lucidchart or similar - NOT Word), draw an Enhanced Entity Relationship Diagram (EERD) of your design using crows foot notation. Your EERD must show all entities, attributes, relationships and cardinality as well as any business rules identified. Supertypes and subtypes are also expected in the design.

b. Database Design
Your final design choices should allow you to create your relational database in fully normalised form. Following the normalisation process, you will determine the tables, primary keys, foreign keys and columns needed to provide all functionality required by the client as outlined in the scenario above. Identify your database schema using standard formatting for table names, keys, and foreign keys.

Attachment:- Enhanced Entity Relationship Diagram.rar