CSC72001 Database Systems Assessment 3 Report - Query Analysis

This report presents the solution for Assessment 3 of the CSC72001 Database Systems unit at Southern Cross University. The assignment involved analyzing a scenario to design, develop, and test a database using MySQL and PhpMyAdmin. The report includes an Enhanced Entity Relationship Diagram (EERD) and a series of SQL queries designed to retrieve specific information from the database, such as club facilities, member lists, member statistics, personal trainers, club managers, and class timetables. Each query is accompanied by its script and the resulting output. The report demonstrates the student's ability to create and execute SQL queries to address various data retrieval requirements, showcasing proficiency in database design and implementation.

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 2
REPORT
BY <Insert your name>
<Student name and ID> CSC72001 Assessment 3 Report Page 2

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Enhanced Entity Relationship Diagram........................................................................................................4
Part B: Testing Queries................................................................................................................................5
Query 1 Club facilities........................................................................................................................5
Query 2 Members list for a class.......................................................................................................5
Query 3 Counting club members.......................................................................................................6
Query 4 Personal trainers..................................................................................................................7
Query 5 Information about club managers.......................................................................................8
Query 6 Trainers specialised in weight loss.......................................................................................8
Query 7 Members statistic................................................................................................................9
Query 8 Club timetable....................................................................................................................10
<Student name and ID> CSC72001 Assessment 3 Report Page 3

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

Part B: Testing Queries
Query 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.
Query script:
SELECT c.clubname, c.state, c.phone
FROM Club c
INNER JOIN ClubFacility cf ON cf.clubid = c.clubid
INNER JOIN Facility f ON f.facilityid = cf.facilityid
AND f.facilityname = 'kids playroom';
Query Result:
Query 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.
Query script:
SELECT m.Firstname, m.Lastname, m.email, m.phone, m.address, m.city, m.state,
m.postcode, c.classname
FROM Member m
INNER JOIN ClassOnlyMember com ON com.Memberid = m.memberid
INNER JOIN Class c ON c.classid = com.classid
AND c.classname = 'yoga'
<Student name and ID> CSC72001 Assessment 3 Report Page 5

ORDER BY m.lastname;
Query Result:
Query 3 Counting club 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.
Query script:
SELECT c.clubname, c.State, CONCAT( ma.firstname, '', ma.lastname ) AS Manager, (
SELECT COUNT( * )
FROM Member m
WHERE c.clubid = m.ClubID
) AS Total, (
SELECT COUNT( * )
FROM Member m
WHERE m.MemberType = 'all access member'
AND c.clubid = m.ClubID
) AS AllAccess, (
SELECT COUNT( * )
FROM Member m
WHERE m.MemberType = 'class only'
AND c.clubid = m.ClubID
) AS ClassOnly
FROM Club c, Manager ma
WHERE ma.ManagerID = c.ManagerID
ORDER BY c.State;
Query Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 6

Query 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.
Query script:
SELECT c.clubname, c.state, CONCAT( t.firstname, '', t.lastname ) AS PersonalTrainer,
COUNT( aam.memberid ) AS NumberOfMembers
FROM Club c, Trainer t, AllAccessMember aam, Member m
WHERE c.clubid = m.clubid
AND m.memberid = aam.memberid
AND aam.trainerid = t.trainerid
GROUP BY t.firstname
ORDER BY c.state, NumberOfMembers;
Query Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 7

Query 5 Information about 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.
Query script:
SELECT CONCAT( m.firstname, ' ', m.lastname ) AS Managers, m.email, m.phone,
c.clubname
FROM Club c, Manager m
WHERE m.managerid = c.managerid
ORDER BY m.lastname;
Query Result:
Query 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.
Query script:
SELECT CONCAT( t.firstname, ' ', t.lastname ) AS Trainers, c.clubname, t.email, t.phone,
t.city, t.state, t.postcode
FROM Trainer t, Club c, TimeTable tt
WHERE c.clubid = tt.clubid
AND tt.trainerid = t.trainerid
AND t.status = 'Active'
AND t.specialization LIKE '%weight loss%'
ORDER BY t.lastname;
<Student name and ID> CSC72001 Assessment 3 Report Page 8

Query Result:
Query 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.
Query script:
SELECT c.clubname, c.State, (
SELECT COUNT( * )
FROM Member m
WHERE c.clubid = m.ClubID
) AS Total, (
SELECT COUNT( * )
FROM Member m
WHERE m.status = 'Active'
AND c.clubid = m.ClubID
) AS ActiveMember, (
SELECT COUNT( * )
FROM Member m
WHERE m.status = 'Inactive'
<Student name and ID> CSC72001 Assessment 3 Report Page 9

AND c.clubid = m.ClubID
) AS InactiveMember, (
SELECT COUNT( * )
FROM Member m
WHERE m.status = 'On Leave'
AND c.clubid = m.ClubID
) AS OnHold
FROM Club c
ORDER BY c.State;
Query Result:
Query 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.
Query script:
SELECT cl.clubname, tt.day, tt.timeslot, c.classname, CONCAT( t.firstname, ' ', t.lastname )
AS Trainers
FROM Trainer t
INNER JOIN TimeTable tt ON tt.trainerid = t.trainerid
INNER JOIN Class c ON c.classid = tt.classid
INNER JOIN Club cl ON cl.clubid = tt.clubid
<Student name and ID> CSC72001 Assessment 3 Report Page 10

AND cl.clubname = 'Kingsway'
ORDER BY tt.day, tt.timeslot;
Query Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 11