DTB91001: Practical Skills - Database Development and Testing Report

This report presents a comprehensive solution for DTB91001 Assignment 3, focusing on the development and testing of a relational database for the FIT CLUB Health Centre. The assignment involves creating an Enhanced-Entity Relationship Diagram (EERD) to model the database schema, followed by an analysis and design phase that defines client business rules, assumptions, naming conventions, and data types. The core of the report consists of testing various SQL queries to retrieve specific data, such as club facilities, member lists, and trainer information, demonstrating the database's functionality. The queries cover a range of scenarios, including counting club members, finding personal trainers, and analyzing member statistics. The report includes the EERD diagram, data type choices, and the results of the SQL queries. This solution provides a detailed overview of the database development process, from initial design to practical testing, ensuring the database meets the requirements of the fitness center scenario. The report also includes an originality report showing a 45% similarity index.

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: Data Management Systems
Unit Code: DTB91001
Tutor’s name:
Assignment No.: Assignment 3
Assignment Title: Practical Skills – Database Development and Testing
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:
DTB91001: Assignment 3 Report Page 1

Assignment 3
REPORT
BY <Insert your name>
DTB91001: Assignment 3 Report Page 2

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Enhanced-Entity Relationship Diagram (EERD)............................................................................................4
Part A: Analysis and Design.........................................................................................................................5
1. Client Business Rules.......................................................................................................................5
2. Assumptions Made..........................................................................................................................5
3. Naming Conventions........................................................................................................................5
4. Data Types Chosen..........................................................................................................................6
Part B: Testing Queries................................................................................................................................6
1. Club Facilities.......................................................................................................................................6
2. Members list for a class.......................................................................................................................7
3. Counting club members.......................................................................................................................7
4. Personal trainers..................................................................................................................................7
5. Club manager information...................................................................................................................8
6. Trainers specialised in weight loss.......................................................................................................8
7. Members statistic................................................................................................................................9
8. Club timetable.....................................................................................................................................9
DTB91001: Assignment 3 Report Page 3

Enhanced-Entity Relationship Diagram (EERD)
An EERD is an Enhanced version of regular Entity Relationship Diagram. The diagram shows the
real-world objects as entities, with attributes and relationships between them. On other hand, the
enhanced version shows the generalization and specialization of the data in the schema. The
specialisation for the trainer table and generalization of employees into manager and trainer can be
seen in figure 1.
Figure 1: FIT Club EERD
Source: created by author
DTB91001: Assignment 3 Report Page 4

Part A: Analysis and Design
1. Client Business Rules
 The business has multiple clubs on different locations.
 Each club have zero or multiple employees.
 Each employee is either a manager of the club or trainer.
 The business keeps a track record of its employee’s name, address, contact along with
the status.
 Each trainer has one or more specialization in fitness field.
 A club is having one or multiple facilities provided to its members.
 Each club is having zero or multiple members.
 Each member’s name, contact, address, status etc. is recorded.
 Each member is associated with a 24/7 access or class only access.
 Each member is associated with zero or many personal training instructors.
 Each member is associated with zero or multiple training classes.
 Each employee is enrolled with zero or multiple training classes.
 Each class is assigned a time table on different club locations.
 Each time table is associated with the class type for example, boxing, yoga etc.
2. Assumptions Made
The assumptions have made in the schema of the database that a club may have 0
employees, 0 members and 0 facilities. Apart from that the, data for each member has been
included in a way that a member can have 0 membership and personal trainers. The
members can have 0 enrolled classes. A club can have 0 or more training classes. The
classes can have one or more than one specifications of its type. A trainer can have one or
more classes on the different club locations.
3. Naming Conventions
The following naming conventions have been used in the implementation:
- <Table names>
The tables names are having one word for the parent tables and for the normalized
tables the tables names use the “_” for word separation. For example, the time table
for the class has been named (class_timetable).
DTB91001: Assignment 3 Report Page 5

- <PrimaryKeys>
Primary keys are defined and namd here adding ‘ID’ in suffix of the attribute’s
names.
- <Attributes>
The attributes having a more than one word is defined as “FirstName”.
- <ForiegnKeys>
Foreign keys are named after its parent keys for better implementation. However,
the name of the constraints has its parent tables names. For example,
“club_clubid1”.
4. Data Types Chosen
The datatypes used for the characters are VARCHAR, INT for the integer values, DECIMAL (10,2)
for the price related values, DATE for the dates. In this assignment, the primary keys are chosen as
VARCHAR to add its table name’s first letter as prefix in the attribute’s name. The time range showing
the time in the “class_timetable” table is also stored using Varchar. The data types are having valid
length and unique constraints for better retrieval of data. The data types for the primary and foreign
keys are kept same for the referral integrity of the foreign key. The referral integrity of the data helped
in inserting the correct values into the child tables.
Part B: Testing Queries
1. Club Facilities
Query:
select clubName, state, phone from club
inner join facility_list
on club.ClubID=facility_list.ClubID inner join facility ON
facility.Facility_Type=facility_list.Facility_Type where facility.Description='kids playroom' ORDER by
club.state;
Result:
DTB91001: Assignment 3 Report Page 6

2. Members list for a class
Query:
select firstname, lastname, address, phone, state, postcode from member inner join
class_enrollment on class_enrollment.MemberID=member.MemberID and classenrollmentID='CE25'
order by LastName;
Result:
3. Counting club members
Query:
select c.clubName, c.state, concat(e.FirstName, " ",e.LastName) as manager, count(me.memberid) as
total, count(case when mem.MembershipType='all access 24/7' then 1 end) as all_access,
count(case when mem.MembershipType='class only' then 1 end) as class
from club c inner join employee e ON
c.ClubID=e.ClubID inner join manager m ON
m.EmployeeID=e.EmployeeID inner join member me ON
me.ClubID=c.ClubID inner join membership mem ON
mem.MembershipType=me.MembershipType GROUP by c.ClubName;
Result:
4. Personal trainers
Query:
DTB91001: Assignment 3 Report Page 7

select c.state, c.clubname, concat(e.FirstName," ",e.LastName) as trainer, COUNT(pt.MemberID) as
`trained members` from club c inner join member m on m.ClubID=c.ClubID
inner join personal_trainer pt on pt.MemberID=m.MemberID inner join employee e ON
e.EmployeeID=pt.EmployeeID group by c.ClubName ORDER by c.State;Result:
5. Club manager information
Query:
select e.firstname, e.lastname, e.emailaddress, e.phone, c.clubname from employee e inner JOIN
club c on c.ClubID=e.ClubID inner join manager m ON
m.EmployeeID=e.EmployeeID order by lastname;
Result:
6. Trainers specialised in weight loss
Query:
select concat(e.firstname," ",e.LastName) as trainer, c.ClubName, e.EmailAddress,e.Phone from club c
inner join employee e on
e.ClubID=c.ClubID inner join trainer t on t.EmployeeID=e.EmployeeID inner join specialisation s
DTB91001: Assignment 3 Report Page 8

on s.SpecialisationType=t.SpecialisationType and e.Status='active' and s.Description='weight loss' order
BY
e.LastName;
Result:
7. Members statistic
Query:
select c.clubName, c.state, count(m.MemberID) as `total_member`,
count(case when m.Status='active' then 1 end) as all_access,
count(case when m.Status='on leave' then 1 end) as `On hold`,
count(case when m.Status='inactive' then 1 end) as inactive
from club c inner join member m on m.ClubID=c.ClubID GROUP by c.ClubName;
Result:
8. Club timetable
Query:
select c.ClubName,ct.Day, ct.Time, cty.description, concat(e.FirstName," ",e.LastName) as trainer
from class_timetable ct inner JOIN
class_type cty on cty.ClassType=ct.ClassType INNER join employee e ON
DTB91001: Assignment 3 Report Page 9

e.EmployeeID=ct.EmployeeID inner join club c ON
c.ClubID=e.ClubID where c.ClubName='kingsway' order by ct.day,ct.Time;
Result:
DTB91001: Assignment 3 Report Page 10