Database Design and Implementation

This document provides a detailed explanation of database design and implementation. It includes an enhanced entity relationship diagram, database entities, script, views, and queries. The document also includes a bibliography.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Enhanced Entity Relationship Diagram:.........................................................................................2
Database Entities:............................................................................................................................2
Script:...............................................................................................................................................5
Views:............................................................................................................................................18
Queries:..........................................................................................................................................19
Bibliography:.................................................................................................................................22

2DATABASE DESIGN AND IMPLEMENTATION
Enhanced Entity Relationship Diagram:
Figure 1: EERD of FIT CLUB Health Centre
(Source: Created by Author)
Database Entities:
Classes Entity:

3DATABASE DESIGN AND IMPLEMENTATION
Club Entity:
Manager Entity:
Facility Entity:

4DATABASE DESIGN AND IMPLEMENTATION
Clubfacilities Entity:
Classclub Entity:
Timetable Entity:

5DATABASE DESIGN AND IMPLEMENTATION
Trainers Entity:
Timetable Entity:
Script:
-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/

6DATABASE DESIGN AND IMPLEMENTATION
--
-- Host: 127.0.0.1
-- Generation Time: Feb 15, 2019 at 07:51 AM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.3.1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET
@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET
@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `fchc`
--
CREATE DATABASE IF NOT EXISTS `fchc` DEFAULT CHARACTER SET latin1
COLLATE latin1_swedish_ci;
USE `fchc`;
-- --------------------------------------------------------
--
-- Table structure for table `classclub`

7DATABASE DESIGN AND IMPLEMENTATION
--
CREATE TABLE `classclub` (
`clubID` int(11) NOT NULL,
`classID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `classclub`
--
INSERT INTO `classclub` (`clubID`, `classID`) VALUES
(1, 3),
(1, 5),
(2, 2),
(2, 4),
(3, 2),
(3, 3),
(4, 1),
(4, 5),
(5, 1),
(5, 5);
-- --------------------------------------------------------
--
-- Table structure for table `classes`
--
CREATE TABLE `classes` (

8DATABASE DESIGN AND IMPLEMENTATION
`classID` int(11) NOT NULL,
`className` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `classes`
--
INSERT INTO `classes` (`classID`, `className`) VALUES
(1, 'Active Adults'),
(2, 'Active Kids'),
(3, 'Active Core'),
(4, 'HIIT'),
(5, 'Yoga');
-- --------------------------------------------------------
--
-- Table structure for table `clubfacilities`
--
CREATE TABLE `clubfacilities` (
`clubID` int(11) NOT NULL,
`facilityID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `clubfacilities`
--

9DATABASE DESIGN AND IMPLEMENTATION
INSERT INTO `clubfacilities` (`clubID`, `facilityID`) VALUES
(1, 1),
(2, 2),
(3, 5),
(4, 3),
(5, 4);
-- --------------------------------------------------------
--
-- Table structure for table `clubs`
--
CREATE TABLE `clubs` (
`clubID` int(11) NOT NULL,
`branchName` varchar(150) NOT NULL,
`address` varchar(150) NOT NULL,
`contactNumber` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `clubs`
--
INSERT INTO `clubs` (`clubID`, `branchName`, `address`, `contactNumber`) VALUES
(1, 'Rydalmere', '19 Cecil Street, NSW, 2116', '[phone]'),
(2, 'Parramatta', '19 Cecil Street, NSW, 2116\r\n', '[phone]'),
(3, 'Essendon', '7 Creedon Street, VIC, 3040\r\n', '[phone]'),
(4, 'Kingston', '68 Yarra Street, TAS, 3364\r\n', '[phone]'),
(5, 'Mt Gravatt', '37 Kintyre Street, QLD, 4122\r\n', '[phone]');

10DATABASE DESIGN AND IMPLEMENTATION
-- --------------------------------------------------------
--
-- Table structure for table `facilities`
--
CREATE TABLE `facilities` (
`facilityID` int(11) NOT NULL,
`facilityName` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `facilities`
--
INSERT INTO `facilities` (`facilityID`, `facilityName`) VALUES
(1, 'kids playroom'),
(2, 'Cardio Theatre'),
(3, 'Free Parking'),
(4, 'Weights Areas'),
(5, 'Outdoor Training');
-- --------------------------------------------------------
--
-- Table structure for table `manager`
--
CREATE TABLE `manager` (

11DATABASE DESIGN AND IMPLEMENTATION
`managerID` int(11) NOT NULL,
`clubID` int(11) NOT NULL,
`managerName` varchar(50) NOT NULL,
`address` varchar(150) NOT NULL,
`contactNumber` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `manager`
--
INSERT INTO `manager` (`managerID`, `clubID`, `managerName`, `address`,
`contactNumber`) VALUES
(1, 1, 'David Burrell', '16 Cecil Street, MELROSE PARK, NSW\r\n', '0451 413 230'),
(2, 3, 'Kai Hogarth', '89 Village Drive, CABRAMATTA, NSW\r\n', '0439 586 907'),
(3, 4, 'Eliza Oride', '92 Begley Street, ADELAIDE, SA\r\n', '0483 828 965'),
(4, 2, 'Maddison Beak', '13 Ross Street, MERMAID BEACH, QLD\r\n', '0493 246 635'),
(5, 5, 'Bianca Wekey', '58 Savages Road, EIGHT MILE PLAINS, QLD\r\n', '0470 609
437');
-- --------------------------------------------------------
--
-- Table structure for table `member`
--
CREATE TABLE `member` (
`memberID` int(11) NOT NULL,
`clubID` int(11) NOT NULL,
`memberName` varchar(150) NOT NULL,

12DATABASE DESIGN AND IMPLEMENTATION
`address` varchar(150) NOT NULL,
`contactNumber` varchar(50) NOT NULL,
`membershipStatus` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `member`
--
INSERT INTO `member` (`memberID`, `clubID`, `memberName`, `address`,
`contactNumber`, `membershipStatus`) VALUES
(1, 1, 'Ella Cook', '673-4905 Donec Road', '0415 088 044', 'Active'),
(2, 5, 'Indi Innes', '831 Non Avenue', '0415 234 573', 'Active'),
(3, 2, 'Collette Dudley', '6176 Nec Rd.', '0415 265 800', 'Inactive'),
(4, 3, 'Bronte Connor', '5515 Rutrum Street', '0415 496 955', 'On Leave'),
(5, 4, 'Hannah Roy', '7531 Hymenaeos. Rd.', '0415 775 765', 'Active');
-- --------------------------------------------------------
--
-- Table structure for table `timetable`
--
CREATE TABLE `timetable` (
`classID` int(11) NOT NULL,
`trainerID` int(11) NOT NULL,
`classDate` date NOT NULL,
`time` time NOT NULL,
`status` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

13DATABASE DESIGN AND IMPLEMENTATION
--
-- Dumping data for table `timetable`
--
INSERT INTO `timetable` (`classID`, `trainerID`, `classDate`, `time`, `status`) VALUES
(1, 2, '2019-02-21', '10:00:00', 'Pending'),
(1, 3, '2019-02-01', '13:00:00', 'Completed'),
(2, 4, '2019-02-02', '09:00:00', 'Completed'),
(2, 5, '2019-02-01', '11:00:00', 'Completed'),
(3, 1, '2019-02-01', '09:00:00', 'Completed'),
(3, 5, '2019-02-04', '15:00:00', 'Completed'),
(4, 4, '2019-02-09', '16:00:00', 'Terminated'),
(5, 1, '2019-02-08', '12:00:00', 'Completed'),
(5, 2, '2019-02-23', '15:00:00', 'Pending'),
(5, 3, '2019-02-19', '09:00:00', 'Pending');
-- --------------------------------------------------------
--
-- Table structure for table `trainers`
--
CREATE TABLE `trainers` (
`trainerID` int(11) NOT NULL,
`clubID` int(11) NOT NULL,
`trainerName` varchar(150) NOT NULL,
`address` varchar(150) NOT NULL,
`contactNumber` varchar(50) NOT NULL,
`specialized` varchar(150) NOT NULL,

14DATABASE DESIGN AND IMPLEMENTATION
`type` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `trainers`
--
INSERT INTO `trainers` (`trainerID`, `clubID`, `trainerName`, `address`,
`contactNumber`, `specialized`, `type`) VALUES
(1, 1, 'Tommy Tanny', '3459 Orci Avenue', '0415 328 722', 'Performance Enhancement',
'Instructors'),
(2, 5, 'Innes Indy', '241-8201 Eu Street', '0415 243 270', 'Woman Fitness, nutrition',
'PersonalTrainer'),
(3, 4, 'Jammin Juice', '254-7049 Pede. Street', '0415 184 363', 'Performance
Enhancement', 'PersonalTrainer'),
(4, 2, 'Therese Tong', '3672 Augue, Street', '0415 781 343', 'Conditioning, nutrition',
'Instructors'),
(5, 3, 'Saint Helen', '295 A Rd.', '0415 986 775', 'Woman Fitness, nutrition', 'Instructors');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `classclub`
--
ALTER TABLE `classclub`
ADD PRIMARY KEY (`clubID`,`classID`),
ADD KEY `classID` (`classID`);
--

15DATABASE DESIGN AND IMPLEMENTATION
-- Indexes for table `classes`
--
ALTER TABLE `classes`
ADD PRIMARY KEY (`classID`);
--
-- Indexes for table `clubfacilities`
--
ALTER TABLE `clubfacilities`
ADD PRIMARY KEY (`clubID`,`facilityID`),
ADD KEY `facilityID` (`facilityID`);
--
-- Indexes for table `clubs`
--
ALTER TABLE `clubs`
ADD PRIMARY KEY (`clubID`);
--
-- Indexes for table `facilities`
--
ALTER TABLE `facilities`
ADD PRIMARY KEY (`facilityID`);
--
-- Indexes for table `manager`
--
ALTER TABLE `manager`
ADD PRIMARY KEY (`managerID`),
ADD KEY `clubID` (`clubID`);

16DATABASE DESIGN AND IMPLEMENTATION
--
-- Indexes for table `member`
--
ALTER TABLE `member`
ADD PRIMARY KEY (`memberID`),
ADD KEY `clubID` (`clubID`);
--
-- Indexes for table `timetable`
--
ALTER TABLE `timetable`
ADD PRIMARY KEY (`classID`,`trainerID`,`classDate`),
ADD KEY `trainerID` (`trainerID`);
--
-- Indexes for table `trainers`
--
ALTER TABLE `trainers`
ADD PRIMARY KEY (`trainerID`),
ADD KEY `clubID` (`clubID`);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `classclub`
--
ALTER TABLE `classclub`

17DATABASE DESIGN AND IMPLEMENTATION
ADD CONSTRAINT `classclub_ibfk_1` FOREIGN KEY (`classID`) REFERENCES
`classes` (`classID`),
ADD CONSTRAINT `classclub_ibfk_2` FOREIGN KEY (`clubID`) REFERENCES
`clubs` (`clubID`);
--
-- Constraints for table `clubfacilities`
--
ALTER TABLE `clubfacilities`
ADD CONSTRAINT `clubfacilities_ibfk_1` FOREIGN KEY (`clubID`)
REFERENCES `clubs` (`clubID`),
ADD CONSTRAINT `clubfacilities_ibfk_2` FOREIGN KEY (`facilityID`)
REFERENCES `facilities` (`facilityID`);
--
-- Constraints for table `manager`
--
ALTER TABLE `manager`
ADD CONSTRAINT `manager_ibfk_1` FOREIGN KEY (`clubID`) REFERENCES
`clubs` (`clubID`);
--
-- Constraints for table `member`
--
ALTER TABLE `member`
ADD CONSTRAINT `member_ibfk_1` FOREIGN KEY (`clubID`) REFERENCES
`clubs` (`clubID`);
--
-- Constraints for table `timetable`
--
ALTER TABLE `timetable`

18DATABASE DESIGN AND IMPLEMENTATION
ADD CONSTRAINT `timetable_ibfk_1` FOREIGN KEY (`classID`) REFERENCES
`classes` (`classID`),
ADD CONSTRAINT `timetable_ibfk_2` FOREIGN KEY (`trainerID`) REFERENCES
`trainers` (`trainerID`);
--
-- Constraints for table `trainers`
--
ALTER TABLE `trainers`
ADD CONSTRAINT `trainers_ibfk_1` FOREIGN KEY (`clubID`) REFERENCES
`clubs` (`clubID`);
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT
*/;
/*!40101 SET
CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION
*/;
Views:
View 1: CREATE OR REPLACE VIEW AAclubmember AS SELECT 'all-access
members', c1.branchName, c1.state, man1.managerName, Count(mem1.clubID) AS 'Count of
members' FROM clubs c1, member mem1, Manager man1 WHERE c1.clubID = mem1.clubID
AND c1.clubID = man1.clubID AND mem1.type = 'AAMembers' GROUP BY c1.branchName;
View 2: CREATE OR REPLACE VIEW COclubmember AS SELECT 'class-only
members', c2.branchName, c2.state,man2.managerName, Count(mem2.clubID) AS 'Count of
members' FROM clubs c2, member mem2, Manager man2 WHERE c2.clubID = mem2.clubID
AND c2.clubID = man2.clubID AND type = 'COMember' GROUP BY c2.branchName;

19DATABASE DESIGN AND IMPLEMENTATION
View 3: CREATE OR REPLACE VIEW ActiveMember AS SELECT 'Active Member',
branchName, state, COUNT(member.clubID) AS 'Total number of members' FROM clubs
INNER JOIN member ON clubs.clubID = member.clubID WHERE member.membershipStatus
= 'Active' GROUP BY branchName;
View 4: CREATE OR REPLACE VIEW OnLeave AS SELECT 'Hold Member',
branchName, state, COUNT(member.clubID) AS 'Total number of members' FROM clubs
INNER JOIN member ON clubs.clubID = member.clubID WHERE member.membershipStatus
= 'On Leave' GROUP BY branchName;
View 5: CREATE OR REPLACE VIEW InactiveMember AS SELECT 'Inactive
Member', branchName, state, COUNT(member.clubID) AS 'Total number of members' FROM
clubs INNER JOIN member ON clubs.clubID = member.clubID WHERE
member.membershipStatus = 'Inactive' GROUP BY branchName;
Queries:
Query 1: 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;
Query 2: Select memberName, address, contactNumber FROM member WHERE type =
'COMember' ORDER BY memberName;

20DATABASE DESIGN AND IMPLEMENTATION
Query 3: SELECT * FROM aaclubmember UNION SELECT * FROM coclubmember;
Query 4: SELECT clubs.branchName, trainers.trainerName,
COUNT(personaltraining.trainerID) AS 'Numbers of members trained' FROM personaltraining
INNER JOIN trainers ON personaltraining.trainerID = trainers.trainerID INNER JOIN clubs ON
trainers.clubID = clubs.clubID GROUP BY trainerName ORDER BY clubs.state;
Query 5: SELECT managerName, email, manager.contactNumber, branchName FROM
manager INNER JOIN clubs ON manager.managerID = clubs.clubID ORDER BY
managerName;
Query 6: 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;

21DATABASE DESIGN AND IMPLEMENTATION
Query 7: SELECT * FROM activemember UNION SELECT * FROM onleave UNION
SELECT * FROM inactivemember
Query 8: 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`;

22DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Dionisio, K.L., Phillips, K., Price, P.S., Grulke, C.M., Williams, A., Biryol, D., Hong, T. and
Isaacs, K.K., 2018. The Chemical and Products Database, a resource for exposure-relevant data
on chemicals in consumer products. Scientific data, 5, p.180125.
Elbourne, L.D., Tetu, S.G., Hassan, K.A. and Paulsen, I.T., 2016. TransportDB 2.0: a database
for exploring membrane transporters in sequenced genomes from all domains of life. Nucleic
acids research, 45(D1), pp.D320-D324.
Fabregat, A., Korninger, F., Viteri, G., Sidiropoulos, K., Marin-Garcia, P., Ping, P., Wu, G.,
Stein, L., D’Eustachio, P. and Hermjakob, H., 2018. Reactome graph database: Efficient access
to complex pathway data. PLoS computational biology, 14(1), p.e1005968.
Oktafianto, M.R., Al Akbar, Y.F., Zulkifli, S. and Wulandari, A.M., 2018. Dismissal Working
Relationship using Analytic Hierarchy Process Method. International Journal of Pure and
Applied Mathematics, 118(7), pp.177-184.
Stanescu, L., Brezovan, M. and Burdescu, D.D., 2016, September. Automatic mapping of
MySQL databases to NoSQL MongoDB. In Computer Science and Information Systems
(FedCSIS), 2016 Federated Conference on (pp. 837-840). IEEE.
Sun, Q., Fu, L., Qiu, W. and Sun, J., 2016. An automatic anti-attack scheme for mysql
database. Advances in Intelligent Systems Research, 113, pp.397-400.

23DATABASE DESIGN AND IMPLEMENTATION
Trivedi, D., Zavarsky, P. and Butakov, S., 2016. Enhancing relational database security by
metadata segregation. Procedia Computer Science, 94, pp.453-458.
Vaneman, W.K., 2016, April. Enhancing model-based systems engineering with the Lifecycle
Modeling Language. In Systems Conference (SysCon), 2016 Annual IEEE (pp. 1-7). IEEE.
Yang, L. and Cao, L., 2016. The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and
Computer Science, 8(7), p.1.