CSC72001 Database Systems

Assignment 3 CSC72001 – Database Systems Page 1
CSC72001 Database Systems
Assessment 3
Assessment 3A – Enhanced Entity Relationship Diagram (10%)
3B – Develop a Database Prototype (20%)
Due Date: 3A – 11.30 pm Friday Week 11
3B – 11.30 pm Wednesday Week 13
Submission: Submit Part A via MySCU
Submit Part B report and SQL script via MySCU, submit Database on Infotech server.
Weight: 30% of overall unit assessment.
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 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 with 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.
Assignment 3 CSC72001 – Database Systems Page 2
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 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.
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