KHALID MAHDI FARAH 22/1172/BSSE-J
1. Identifying Entities
Entities:
o Members
o Membership Types
o Payments
o Instructors
o Machinery
o Workouts
2. Relationships:
o One Member has One Membership Type (One-to-One)
o One Member can have Many Payments (One-to-Many)
o One Workout can have Many Members (Many-to-Many) through a separate table
(Workout_Members)
o One Workout can have One Instructor (One-to-Many)
o One Workout can have Many Machinery (Many-to-Many) through a separate
table (Workout_Machinery)
pg. 1
KHALID MAHDI FARAH 22/1172/BSSE-J
3. Database and Table Creation (MySQL)
Here are the SQL CREATE statements for each table:
1. Members Table:
SQL
CREATE TABLE Members (
MemberID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
DateOfBirth DATE NOT NULL,
Password VARCHAR(255) NOT NULL,
Gender VARCHAR(10) NOT NULL,
PhoneContact VARCHAR(20) NOT NULL,
DateJoined DATE NOT NULL,
Photo BLOB,
FOREIGN KEY (MembershipTypeID) REFERENCES MembershipTypes(MembershipTypeID)
);
2. Membership Types Table:
SQL
CREATE TABLE MembershipTypes (
MembershipTypeID INT AUTO_INCREMENT PRIMARY KEY,
Description TEXT NOT NULL,
Fees DECIMAL(10,2) NOT NULL
);
3. Payments Table:
SQL
CREATE TABLE Payments (
PaymentID INT AUTO_INCREMENT PRIMARY KEY,
PaymentDate DATE NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
MemberID INT NOT NULL,
pg. 2
KHALID MAHDI FARAH 22/1172/BSSE-J
MembershipTypeID INT NOT NULL,
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
FOREIGN KEY (MembershipTypeID) REFERENCES MembershipTypes(MembershipTypeID)
);
4. Instructors Table:
SQL
CREATE TABLE Instructors (
InstructorID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Gender VARCHAR(10) NOT NULL,
PhoneContact VARCHAR(20) NOT NULL,
Address TEXT,
Email VARCHAR(255) NOT NULL,
DateJoined DATE NOT NULL,
Photo BLOB,
Availability VARCHAR(255)
);
5. Machinery Table:
SQL
CREATE TABLE Machinery (
MachineID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Model VARCHAR(255),
NumberOfEquipment INT,
Description TEXT,
Price DECIMAL(10,2) NOT NULL,
DatePurchased DATE NOT NULL,
Photo BLOB
);
6. Workouts Table:
pg. 3
KHALID MAHDI FARAH 22/1172/BSSE-J
SQL
CREATE TABLE Workouts (
WorkoutID INT AUTO_INCREMENT PRIMARY KEY,
WorkoutName VARCHAR(255) NOT NULL,
Description TEXT,
Date DATETIME NOT NULL,
InstructorID INT NOT NULL,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
7. Workout_Members (Many-to-Many Relationship Table):
SQL
CREATE TABLE Workout_Members (
WorkoutID INT NOT NULL,
MemberID INT NOT NULL,
FOREIGN KEY (WorkoutID) REFERENCES Workouts(WorkoutID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID),
PRIMARY KEY (WorkoutID, MemberID)
);
8. Workout_Machinery (Many-to-Many Relationship Table):
SQL
CREATE TABLE Workout_Machinery (
WorkoutID INT NOT NULL,
MachineID INT NOT NULL,
FOREIGN KEY (WorkoutID) REFERENCES Workouts(WorkoutID),
FOREIGN KEY (MachineID) REFERENCES Machinery(MachineID),
PRIMARY KEY (WorkoutID
pg. 4
KHALID MAHDI FARAH 22/1172/BSSE-J
4. Defining Table Structures:
Members Table:
Field Name Data Type Description Not Null Primary Key
MemberID INT Auto-incrementing ID (primary key) YES YES
Name VARCHAR Member's full name YES
DateOfBirth DATE Member's date of birth YES
Password VARCHAR Member's login password (hashed) YES
Gender VARCHAR Member's gender (e.g., Male, Female) YES
PhoneContact VARCHAR Member's phone number YES
DateJoined DATE Date the member joined the gym YES
Photo BLOB Optional: Member's profile picture NO
MembershipTypeID INT Foreign Key referencing MembershipTypes table YES
Membership Types Table:
Field Name Data Type Description Not Null Primary Key
MembershipTypeID INT Auto-incrementing ID (primary key) YES YES
Description TEXT Description of the membership type YES
Payments Table:
Field Name Data Type Description Not Null Primary Key
PaymentID INT Auto-incrementing ID (primary key) YES YES
PaymentDate DATE Date the payment was made YES
Amount DECIMAL Amount paid by the member YES
MemberID INT Foreign Key referencing Members table YES
MembershipTypeID INT Foreign Key referencing MembershipTypes table YES
Machinery Table:
Field Name Data Type Description Not Null Primary Key
MachineID INT Auto-incrementing ID (primary key) YES YES
Name VARCHAR Name of the equipment YES
pg. 5
KHALID MAHDI FARAH 22/1172/BSSE-J
Instructors Table:
Field Name Data Type Description Not Null Primary Key
InstructorID INT Auto-incrementing ID (primary key) YES YES
Name VARCHAR Instructor's full name YES
Gender VARCHAR Instructor's gender (e.g., Male, Female) YES
PhoneContact VARCHAR Instructor's phone number YES
Address TEXT Instructor's address (optional) NO
Email VARCHAR Instructor's email address YES
DateJoined DATE Date the instructor joined the gym YES
Photo BLOB Optional: Instructor's profile picture NO
Availability VARCHAR Days the instructor is available to work (optional) NO
5. Take a screenshot for every table showing each table and the records in that table
screenshot membership type table.
Screenshot members table.
pg. 6
KHALID MAHDI FARAH 22/1172/BSSE-J
Screenshot of payment table
Screenshot of trainers’ table
Screenshot of machine table
Screenshot of workout table
pg. 7
KHALID MAHDI FARAH 22/1172/BSSE-J
6.
A. All members of the gym:
SQL
SELECT *
FROM Members;
B. All members of the gym that are students:
SQL
SELECT *
FROM Members
WHERE MembershipTypeID = (SELECT MembershipTypeID FROM MembershipTypes WHERE
Description LIKE '%Student%');
C. All members of the gym that are students over the age of 25 years:
SQL
SELECT *
FROM Members
WHERE MembershipTypeID = (SELECT MembershipTypeID FROM MembershipTypes WHERE
Description LIKE '%Student%')
AND CURDATE() >= DATE_ADD(DateOfBirth, INTERVAL 25 YEAR);
D. All members registered for the "Combat" class and the instructor in charge:
SQL
SELECT m.Name AS MemberName, i.Name AS InstructorName
FROM Members m
INNER JOIN Workout_Members wm ON m.MemberID = wm.MemberID
INNER JOIN Workouts w ON wm.WorkoutID = w.WorkoutID
INNER JOIN Instructors i ON w.InstructorID = i.InstructorID
WHERE w.WorkoutName = 'Combat'
AND DAYNAME(w.Date) = 'TUESDAY'
AND HOUR(w.Date) = 18;
E. All machinery needed for the "Combat" class workout:
SQL
SELECT mn.Name AS MachineName
FROM Machinery m
pg. 8
KHALID MAHDI FARAH 22/1172/BSSE-J
INNER JOIN Workout_Machinery wm ON m.MachineID = wm.MachineID
INNER JOIN Workouts w ON wm.WorkoutID = w.WorkoutID
WHERE w.WorkoutName = 'Combat';
F. The total amount of money paid by all current members of the gym:
SQL
SELECT SUM(Amount) AS TotalAmountPaid
FROM Payments p
INNER JOIN Members m ON p.MemberID = m.MemberID
WHERE CURDATE() BETWEEN DATE(MIN(p.PaymentDate)) AND CURDATE();
G. All instructors not available on Mondays:
SQL
SELECT *
FROM Instructors
WHERE Availability NOT LIKE '%Monday%';
pg. 9
