DTB91001: Data Management Systems

A. Task Description

A.1. Overview

In this assignment, your task is to develop and test a relational database based on the ERD created in ASSIGNMENT 2 in MySQL using PhpMyAdmin. You are provided with a scenario and supporting documents, describing the requirements that FIT CLUB Health Centre may have for a database to handle their daily business. You will also need to add sample data and create SQL queries to provide results suitable for reporting. You further need to make relational joins and evidence of correct working via database queries and screenshots of result sets to ensure your prototype works. Finally, you need to submit a report documenting the end product using report template 3B and SQL script of your database.

A.2 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 instructor’s 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.

B. Assessment Requirements

B.1 Database Development and Relationships

Build your database in MySQL using PhpMyAdmin. Your database should be created on the Infotech server and be named with your username followed by A3 (e.g., aalaeiA3). Referential integrity and any other constraints must be created, as required by your design.

B.2 Data Type and Naming

Create a MySQL database using phpMyAdmin. You must name this database as your username followed by A3. For example: aalaei10A3.

Choice and consistency of table and attribute naming conventions used, selection of data types, primary and foreign keys.

B.3 Test/Sample Data

You must provide enough valid data in your database to run the SQL queries below successfully with at least 5-10 rows in the result set.

B.4 Privileges

Provide access to your database for the user eyuwono or another student user account of your choice. Details of how to do this can be seen in the provided screencast.

B.5 Use of Views

The database should be capable of producing the query results below. To get the required results, you may need to build underlying queries or views. Create at least four views and where appropriate, these views should incorporate the use of standard practices, informative column headings that fit the expected use of the view.

B.6 Export Script

Once your database is finalised, you must create an export script (.sql) to create a backup of your database, including all database structures, table definitions and data. Name this SQL script as yourusernameA3.sql e.g. aalaeiA3.sql.

B.7 Proof of Testing

The results of your queries should be presented in a Word document using the provided report template with a title page, page numbers etc. The query that you create for each of the parts below should be pasted into your report (not screenshots), followed by a screenshot of your results. Snipping Tool on Windows is useful for taking screenshots of query results.

B.8 SQL Queries

Note: Your result sets should not display any ‘extra’ columns – such as surrogate keys. Use surrogate keys in your progressive testing by all means, but your finished test results should not include surrogate keys unless specified in the question.

B.8.1 Club facilities

Create a query that displays the list of clubs that provide kids playroom as one of their facilities, showing club name, state, club phone number sorted by club state.

B.8.2 Members list for a class

Create a list of class-only members for one of the class. Results should be sorted by the last name of the member and should include all contact details.

B.8.3 Counting clubs’ members

Create a query that displays all clubs and the number of members for each of those clubs. Your query should select the club name, state, manager name, number of total members, number of all-access members, number of class-only members sorted by club state.

B.8.4 Personal trainers

FIT CLUB management would like to identify trainers that have experiences as personal trainers. Create a query that selects these trainers only, showing the club name, trainer name, the number of members trained sorted by the club state, followed by the number of members.


B.8.5 Information of club managers

Create a query that displays a list of managers, with the name, email, phone number, and the club he/she belongs to, sorted by manager last name.
 
B.8.6 Trainers specialised in weight loss

Create a query to display the list of active trainers who are currently active and specialised in weight loss. The query results need to show trainers name, clubs name, and trainers contact details, sorted by last name of trainers.

B.8.7 Members statistic

Create a query that displays members statistics for each club showing club name, state, the total number of members, number of active members, number of on hold members, number of inactive members.

B.8.8 Club timetable

Create a query that displays the classes timetable for a club. The query result has to display day, time, class name, instructor name sorted by day, followed by time.