CSC72001 Database Systems: Assessment 3 Report Analysis and Design

This report, submitted for CSC72001 Database Systems at Southern Cross University, presents a comprehensive analysis and design of a database system. The report begins with an Entity Relationship Diagram and outlines client business rules, assumptions, and naming conventions used in the database design. It details the chosen data types and their rationale. The core of the report consists of the implementation and results of several SQL queries, including queries for club facilities, member lists, club member counts, personal trainers, club manager information, trainers specialized in weight loss, member statistics, and club timetables. The report demonstrates a strong understanding of database concepts and SQL query construction, providing a practical application of database design principles. The student has effectively addressed the assignment requirements, providing a clear and well-structured report.

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 may have many managers but one manager can work at one club only
 Many clubs provide many facilities but a facility may not be provided by any club
 One instructor can take class at specific club on specific time but many classes on same
time in different clubs can be taken by many instructors
 One club can provide many or only two compulsory facilities, one facility can be offered
by many clubs
 One club can provide many classes, one class can be offered by many clubs
 Each class must be instructed by an instructor
2. Assumptions Made
The main assumption made during the database design is that database will record personal
training count and class only membership count through views. The child entities of
member entity will not have any attribute as they only differ by name and other differences
<Student name and ID> CSC72001 Assessment 3 Report Page 5

happen in operational level which not concern of database. Third assumption is that
organization will not record which member is coming to which club, instead database will
only record the home club id.
3. Naming Conventions
- First letter of entity is always in capital
- Attributes are in singular form. It entails that attributes are atomic
- If combination of two or more words are used then first letter of attribute is small
caption and first letter of later words are in capital caption
4. Data Types Chosen
The database store a lot of words and sentences. In order to store those varchar has been
used. Varchar is variable character. It is a special data type that can store larger strings than
char data type. In order to store the primary keys of all tables, integer data type has been
used. Integer data types store positive numbers. As less than 12 values are saved in most of
tables byte could be used but integer supports a lot more numeric length. In case more data
is to be entered, integer is a suitable choice.
Part B: Testing Queries
1. Club Facilities
Query:
SELECT branchName, state, contactNumber FROM Clubs INNER JOIN ClubFacilities ON Clubs.clubID =
ClubFacilities.clubID INNER JOIN Facilities ON ClubFacilities.facilityID = facilities.facilityID ORDER BY state
ASC;
<Student name and ID> CSC72001 Assessment 3 Report Page 6

Result:
2. Members list for a class
Query:
Select memberName, address, contactNumber FROM member WHERE type = 'COMember' ORDER BY
memberName;
Result:
3. Counting club members
Query:
SELECT * FROM aaclubmember UNION SELECT * FROM coclubmember;
Result:
4. Personal trainers
Query:
SELECT clubs.branchName, trainers.trainerName, COUNT(personaltraining.trainerID) AS 'Numbers of
members trained' FROM personaltraining INNER JOIN trainers ON personaltraining.trainerID =
<Student name and ID> CSC72001 Assessment 3 Report Page 7

trainers.trainerID INNER JOIN clubs ON trainers.clubID = clubs.clubID GROUP BY trainerName ORDER BY
clubs.state;
Result:
5. Club manager information
Query:
SELECT managerName, email, manager.contactNumber, branchName FROM manager INNER JOIN clubs
ON manager.managerID = clubs.clubID ORDER BY managerName;
Result:
6. Trainers specialised in weight loss
Query:
Select trainerName, branchName, trainers.contactNumber FROM trainers INNER JOIN clubs ON
trainers.clubID = clubs.clubID WHERE specialized LIKE '%Weight Loss%' AND status = 'Active' ORDER BY
trainerName;
Result:
7. Members statistic
SELECT * FROM activemember UNION SELECT * FROM onleave UNION SELECT * FROM inactivemember
<Student name and ID> CSC72001 Assessment 3 Report Page 8

Result:
8. Club timetable
Query:
SELECT className, trainerName, classDate, `time` FROM Timetable INNER JOIN trainers ON
Timetable.trainerID = trainers.trainerID INNER JOIN classes ON Timetable.classID = classes.classID
ORDER BY classDate, `time`;
Result:
<Student name and ID> CSC72001 Assessment 3 Report Page 9