CSC72001: Database Systems - Assessment 3 Report, Design, and Testing

This report presents the analysis, design, and testing of a relational database system, likely for a fitness club scenario, as part of a CSC72001 Database Systems assessment. It begins with an Entity Relationship Diagram (ERD) illustrating the database structure and relationships. Part A focuses on the analysis and design phase, detailing client business rules, assumptions made during the design process, and naming conventions used. Part B covers testing, including the execution of various SQL queries to retrieve specific data from the database, such as club facilities, member lists, member counts, personal trainers, club manager information, trainers specializing in weight loss, member statistics, and club timetables. The document includes the results of each query, demonstrating the database's functionality. Additionally, the report contains a sample member application form and SQL commands for granting privileges to other users, providing a practical overview of database management.

SOUTHERN CROSS UNIVERSITY
ASSIGNMENT COVER SHEET
For use with online submission of assignments
Please complete all of the following details and then make this sheet the first page of each file
of your assignment – do not send it as a separate document.
Your assignments must be submitted as either Word documents, text documents with .rtf
extension or as .pdf documents. If you wish to submit in any other file format please discuss
this with your lecturer well before the assignment submission date.
Student Name:
Student ID No.:
Unit Name: Database Systems
Unit Code: CSC72001
Tutor’s name:
Assignment No.: Assessment 3
Assignment Title:
Due date:
Date submitted:
Declaration:
I have read and understand the Rules Relating to Awards (Rule 3 Section 18 –
Academic Misconduct Including Plagiarism) as contained in the SCU Policy
Library. I understand the penalties that apply for plagiarism and agree to be bound
by these rules. The work I am submitting electronically is entirely my own work.
Signed:
(please type
your name)
Date:
<Student name and ID> CSC72001 Assessment 3 Report Page 1

ASSESSMENT 3A
REPORT
BY <Insert your name>
<Student name and ID> CSC72001 Assessment 3 Report Page 2

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Entity Relationship Diagram........................................................................................................................4
Part A: Analysis and Design.........................................................................................................................5
1. Client Business Rules.......................................................................................................................5
2. Assumptions Made..........................................................................................................................5
3. Naming Conventions........................................................................................................................5
<Remove this text before submission of your assessment>
<After you have completed your assessment, right-click on the table of contents above and click
“Update Field” then “Update Page numbers only”. This will build the table of contents for you.>
<Student name and ID> CSC72001 Assessment 3 Report Page 3

Entity Relationship Diagram
<Student name and ID> CSC72001 Assessment 3 Report Page 4

Part A: Analysis and Design
1. Client Business Rules
 One club can have many classes and one class can be run by many clubs
 One member can select only one club as home club and one club can be selected as
home club by many members
 One member can select many trainers as personal trainer and one trainer can train
many members
 One club can provide many facilities and one facility can be provided by many clubs
 One club can run a class on fixed time on a specific day of week but one class can be run
on different times on different day of week by different clubs
<Student name and ID> CSC72001 Assessment 3 Report Page 5

 One class will be conducted by a specific instructor and one instructor can take many
classes
 One member can access all classes provided by club
 Customer state can be either of active, on hold or inactive
2. Assumptions Made
At the time of designing the EERD it is assumed that membership types are only divided by
names only. The charges are only different for these two membership types. That is why
sub-entities do not have any attribute of their own. Database will store which trainer is
taking which class. Same trainer can act as personal trainer. The generalization is created
based on the roles each trainer perform. It does not mean if one trainer is instructor then
he/she cannot attend personal training classes.
3. Naming Conventions
To keep consistency, the database will use strict naming conventions that will allow easy
implementation and maintenance of the database when completed. The following
conventions apply:
- Tables except child-entities have names in plural form
- All attributes are in singular form
- First letter of attribute starts with small caption but all the following words are
started with capital word
- First letter in Table name is always in capital
4. Data Types Chosen
The Integer is chosen for all the primary keys within the entities. Each primary key represent
a positive number that is why integer is a good choice. Varchar is chosen for strings. Date is
for storing onLeaveStart date. Time is chosen to record duration of classes.
<Student name and ID> CSC72001 Assessment 3 Report Page 6

Part B: Testing Queries
1. Club Facilities
Query:
SELECT clubs.clubName, clubs.state FROM clubs INNER JOIN clubfacilities on clubs.clubID =
clubfacilities.clubID INNER JOIN facilities on clubfacilities.facilityID = facilities.facilityID WHERE
facilities.facilityName = 'Kids’ Playroom' ORDER BY clubs.state;
Result:
2. Members list for a class
Query:
SELECT members.* FROM members INNER JOIN classonlymember on members.memID =
classonlymember.memID ORDER BY members.lastName;
Result:
3. Counting club members
Query:
SELECT clubs.clubID, clubs.clubName, clubs.state, clubs.managerName, COUNT(members.homeClub) AS
`Number of Members`, '' AS `Class Only Members`, '' AS `All Access Members` FROM clubs INNER JOIN
members on clubs.clubID = members.homeClub GROUP BY(clubs.clubID)
UNION
<Student name and ID> CSC72001 Assessment 3 Report Page 7

SELECT clubs.clubID, clubs.clubName, clubs.state, clubs.managerName, '' AS `Number of Members`,
COUNT(members.homeClub) AS `Class Only Members`, '' AS `All Access Members` FROM clubs INNER
JOIN members on clubs.clubID = members.homeClub INNER JOIN classonlymember ON
members.memID = classonlymember.memID GROUP BY(clubs.clubID)
UNION
SELECT clubs.clubID, clubs.clubName, clubs.state, clubs.managerName, '' AS `Number of Members`, '' As
`Class Only Members`, COUNT(members.homeClub) AS `All Access Members` FROM clubs INNER JOIN
members on clubs.clubID = members.homeClub INNER JOIN allaccessmember ON members.memID =
allaccessmember.memID GROUP BY(clubs.clubID)
Result:
4. Personal trainers
Query:
SELECT clubs.clubName, trainers.fullName, COUNT(psersonaltrainer.trainerID) AS `Numbers of Member
Trained` FROM trainers INNER JOIN psersonaltrainer ON trainers.trainerID = psersonaltrainer.trainerID
INNER JOIN members ON psersonaltrainer.memID = members.memID INNER JOIN clubs ON
members.homeClub = clubs.clubID GROUP BY trainers.fullName ORDER BY clubs.state;
Result:
5. Club manager information
Query:
<Student name and ID> CSC72001 Assessment 3 Report Page 8

SELECT clubs.clubName, clubs.managerName FROM clubs
Result:
6. Trainers specialised in weight loss
Query:
SELECT DISTINCT trainers.fullName, clubs.clubName FROM trainers INNER JOIN instructors ON
trainers.trainerID = instructors.trainerID INNER JOIN clubs ON instructors.clubID = clubs.clubID ORDER
BY trainers.fullName;
Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 9

7. Members statistic
Query:
SELECT clubs.clubName, clubs.state, COUNT(members.homeClub) AS `Total Number of Members`, '' AS
`Total Active Members`, '' AS `Total On Hold`, '' AS `Total Inactive Members` from clubs INNER JOIN
members on clubs.clubID = members.homeClub
UNION
SELECT clubs.clubName, clubs.state, '' AS `Total Number of Members`, COUNT(members.homeClub) AS
`Total Active Members`, '' AS `Total On Hold`, '' AS `Total Inactive Members` from clubs INNER JOIN
members on clubs.clubID = members.homeClub WHERE members.status = 'active'
UNION
SELECT clubs.clubName, clubs.state, '' AS `Total Number of Members`, '' AS `Total Active Members`,
COUNT(members.homeClub) AS `Total On Hold`, '' AS `Total Inactive Members` from clubs INNER JOIN
members on clubs.clubID = members.homeClub WHERE members.status = 'On Hold'
UNION
SELECT clubs.clubName, clubs.state, '' AS `Total Number of Members`, '' AS `Total Active Members`, '' AS
`Total On Hold`, COUNT(members.homeClub) AS `Total Inactive Members` from clubs INNER JOIN
members on clubs.clubID = members.homeClub WHERE members.status = 'Inactive'
Result:
8. Club timetable
Query:
SELECT classschedule.dayOfWeek AS Day, classschedule.classStartTime AS `Time`, classes.className,
trainers.fullName FROM trainers INNER JOIN instructors ON trainers.trainerID = instructors.trainerID
INNER JOIN classschedule ON instructors.trainerID = classschedule.trainerID INNER JOIN classes ON
classschedule.classID = classes.classID ORDER BY Day, classschedule.classStartTime;
Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 10

<Student name and ID> CSC72001 Assessment 3 Report Page 11