Database Design Assignment 3 Part B - Database Prototype - CSC72001

This document presents a comprehensive solution for a database design and development assignment. The project focuses on creating a relational database in MySQL, incorporating an Enhanced Entity Relationship Diagram (EERD) to model the database structure, including entities such as Members, Facility, Club, Staffs, Classes, and Enrollment. The solution details the database design, naming conventions, and sample data, along with thorough testing of primary and foreign key constraints, as well as deletion operations to ensure data integrity. The assignment also includes a variety of SQL queries designed to retrieve specific information, such as club facilities, member lists, member statistics, and club timetables. These queries demonstrate the practical application of SQL for data retrieval and reporting. The solution concludes with a bibliography of relevant resources used in the development of the database.

Running head: DATABASE DESIGNS
Database Designs
Assignment 3 Part B
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGNS
Table of Contents
Assignment 3 Part B – Develop a Database Prototype....................................................................2
Database Development................................................................................................................2
Database Design..........................................................................................................................2
Sample Data.................................................................................................................................2
Proof of Testing...........................................................................................................................6
Testing of Primary key............................................................................................................6
Testing of Foreign key.............................................................................................................7
Deletion Testing.......................................................................................................................8
SQL Queries................................................................................................................................9
Club facilities...........................................................................................................................9
Members list for a class.........................................................................................................10
Counting club members.........................................................................................................10
Personal trainers.....................................................................................................................11
Information about club managers..........................................................................................12
Trainers specialized in weight loss........................................................................................13
Members statistic...................................................................................................................13
Club timetable........................................................................................................................14
Bibliography..................................................................................................................................16

2
DATABASE DESIGNS
Assignment 3 Part B – Develop a Database Prototype
Database Development
Database Design
The naming conventions used for the development of the design for the database are
identified by “_”. The entities and the attributes having the double phrased names have been
subjected to this naming convention. In addition to this, the Illustrated EERD has been developed
using the chen’s notations. The notation is useful in identifying the weak entities and the
composite data which are required to be identified in the database. In addition to this, the
prominent relationships in between are also identified using the notation. Most of the
relationships in the database are one to many hence, inferring that the database is normalized.
Sample Data
Members

3
DATABASE DESIGNS
Facility
Club
Club_Facilities

4
DATABASE DESIGNS
Staffs

5
DATABASE DESIGNS
Classes
Enrollment

6
DATABASE DESIGNS
Proof of Testing
Testing of Primary key
The following command has been used for testing the primary key constraints:
INSERT INTO `asindh10Ass3`.`Club` (`Club_ID`, `Club_Name`, `State`, `Phone_Number`)
VALUES ('1', 'Terry Club', 'Venice', '784438939');
The following result is fetched from the test:

7
DATABASE DESIGNS
Hence, from the testing performed above it can be safely said that the primary key
implementations have been done correctly.
Testing of Foreign key
The following command has been used for testing the foreign key constraints:
INSERT INTO `asindh10Ass3`.`Classess` (`Class_ID`, `Staff_ID`, `Day`, `Time`) VALUES ('7',
'19', 'Saturday', '11:00:00');
The following result is fetched from the test:

8
DATABASE DESIGNS
Hence, from the testing performed above it can be safely said that the foreign key
implementations have been done correctly.
Deletion Testing
The following command has been used for testing the primary key constraints:
"DELETE FROM `asindh10Ass3`.`Club` WHERE `Club`.`Club_ID` = 5";
The following result is fetched from the test:

9
DATABASE DESIGNS
Hence, from the testing performed above it can be safely said that the relationship
implementations have been done correctly.
SQL Queries
Club facilities
SELECT Club.Club_Name, Club.State, Club.Phone_Number
From Club
Inner Join Club_Facilities On Club.Club_ID = Club_Facilities.Club_ID
Inner Join Facilities On Facilities.Facility_ID = Club_Facilities.Facility_ID
Where Facilities.Facility_Name = "Kid's Playroom"
Order by Club.State;

10
DATABASE DESIGNS
Members list for a class
SELECT `First_Name` , `Last_Name` , `Member_Contact`
FROM `Members`
WHERE Member_Type = "class-only"
ORDER BY Last_Name;
Counting club members
SELECT Club.`Club_Name`, Club.`State`, Club.`Phone_Number`, Club.`all_access`,
Club.`class_only`, Staffs.First_Name, Staffs.Last_Name FROM `Club`
Inner Join Staffs On Staffs.Club_ID = Club.Club_ID
Where Staffs.Type = "Manager"
Order By Club.`State`;

11
DATABASE DESIGNS
Personal trainers
Select Club.Club_Name, Staffs.Last_Name, Staffs.First_Name,
COUNT(Enrollment.Enrollment_ID), Club.State
From Club
Inner Join Staffs On Club.Club_ID = Staffs.Club_ID
Inner Join Classess On Classess.Staff_ID = Staffs.Staff_ID
Inner Join Enrollment On Enrollment.Class_ID = Classess.Class_ID
Where Staffs.Type = "Personal Trainer"
Group By Club.Club_Name, Staffs.Last_Name, Staffs.First_Name,Club.State;