Database Design and Implementation Report for FIT CLUB Health Centre

This report details the database design and implementation for the FIT CLUB Health Centre, a fictitious scenario. It begins with an Enhanced Entity Relationship Diagram (EERD) illustrating the database structure, followed by definitions of the database entities, including Classes, Club, Manager, Facility, Clubfacilities, Classclub, Timetable, and Trainers. The report includes the SQL script used to create the database, encompassing table creation, data insertion, and the definition of primary and foreign keys. Additionally, it presents several views created to simplify data retrieval and a series of queries demonstrating data manipulation and retrieval. The report concludes with a comprehensive bibliography of relevant resources, showcasing the theoretical basis and practical aspects of the database design process, providing a complete overview of the design, development, and querying of a relational database system.

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,