· 9 years ago · Aug 10, 2017, 12:56 AM
1/*
2Rob made this for our database.
3This is what we are aiming for!
4*/
5
6# 1) A users has a MembershipType
7# 2) There are three/four/two types of Memberships?
8# 3) A session has a unique id which describes a particular session in the pool: Fun Time, Adults only etc.
9# 4) A booking is linked to a userID. The user who made the booking!
10# 5) A Timetable is Mon-Sun. It is linked to a session id!
11#
12# The timetable run by the pool is made up of a number of hour long sessions.
13# Currently this system does not implement lanes.
14
15USE swimdb; # select the database
16
17# DROP TABLE COMMANDS
18DROP TABLE IF EXISTS bookings;
19DROP TABLE IF EXISTS users;
20DROP TABLE IF EXISTS news;
21DROP TABLE IF EXISTS MembershipType;
22DROP TABLE IF EXISTS Booking;
23DROP TABLE IF EXISTS Timetable;
24DROP TABLE IF EXISTS Session;
25
26# MembershipType Table
27CREATE TABLE MembershipType(
28 MembershipID integer NOT NULL AUTO_INCREMENT,
29 mTitle char(15) NOT NULL,
30 mDescription char(50) NOT NULL,
31 mFee float NOT NULL,
32 PRIMARY KEY (MembershipID)
33);
34
35INSERT INTO MembershipType (mTitle, mDescription, mFee) values('None','Not a paying member',00.00);
36INSERT INTO MembershipType (mTitle, mDescription, mFee) values('Individual','An individual private membership',9.99);
37INSERT INTO MembershipType (mTitle, mDescription, mFee) values('Group','A group member',29.99);
38
39# users Table
40CREATE TABLE users(
41 user_id integer NOT NULL AUTO_INCREMENT,
42 first_name char(15) NOT NULL,
43 last_name char(20) NOT NULL,
44 email char(30) NOT NULL,
45 address1 char(30),
46 address2 char(30),
47 town char(30),
48 county char(30),
49 post_code char(10),
50 pass char(40) NOT NULL,
51 MembershipID integer NOT NULL REFERENCES MembershipType(MembershipID),
52 is_admin BOOLEAN NOT NULL DEFAULT '0',
53 registration_date DATETIME NOT NULL,
54 PRIMARY KEY (user_id),
55 UNIQUE KEY (email),
56 INDEX login (email,pass)
57);
58
59INSERT INTO users (first_name, last_name, email, pass, MembershipID, is_admin, registration_date) values('Admin','Admin','admin@admin.com',SHA1('admin'),1,1,NOW());
60INSERT INTO users (first_name, last_name, email, address1, address2, town, county, post_code, pass, MembershipID, is_admin, registration_date) values('Robert','Carey','robcarey1990@gmail.com','Mirfield House, Shotley Road','Chelmondiston','Ipswich','Suffolk','IP9 1EE',SHA1('password'),3,0,NOW());
61INSERT INTO users (first_name, last_name, email, address1, address2, town, county, post_code, pass, MembershipID, is_admin, registration_date) values('Ahmet','Abdi','ahmetabdi@hotmail.com','','','','','',SHA1('password'), 1,0,NOW());
62INSERT INTO users (first_name, last_name, email, address1, address2, town, county, post_code, pass, MembershipID, is_admin, registration_date) values('David','Patterson','cutz2@hotmail.com','','','','','',SHA1('password'),1,0,NOW());
63
64# News Table
65
66CREATE TABLE news
67(
68 news_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
69 heading VARCHAR(20) NOT NULL,
70 date_added DATETIME NOT NULL,
71 text VARCHAR(150) NOT NULL,
72 PRIMARY KEY (news_id)
73);
74
75INSERT INTO news(heading, date_added, text) values('Sample news', NOW(),'This is a sample news article. Please log in as an admin user to modify this article or add more news.');
76
77# Session Table
78CREATE TABLE Session(
79 SessionID integer NOT NULL AUTO_INCREMENT,
80 sTitle char(25) NOT NULL,
81 sDescription char(60) NOT NULL,
82 sMaxSwimmers integer NOT NULL,
83 PRIMARY KEY(SessionID)
84);
85
86INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Elderly Tuition','Lessons for the over 60s',50);
87INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Adults Only Lanes','Lane swimming for all swimmers aged 18 and over',60);
88INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Adults Only Free Swim','No lanes swimming for all swimmers aged 18 and over',70);
89INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Kids Tuition','Lessons for the under 16s',50);
90INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Members Only Free Swim','No lanes swimming for members only',70);
91INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Members Only Lanes','Lane swimming for members only',60);
92INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Splash Time!','Fun session with floats for any swimmer',50);
93INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Disabled Access','Session for the disabled',30);
94INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('Swim Team','Competitive session for team members only',50);
95INSERT INTO Session (sTitle,sDescription,sMaxSwimmers) values('[CLOSED]','Pool closed during this time',0);
96
97# Booking Table
98CREATE TABLE Booking(
99 BookingID integer NOT NULL AUTO_INCREMENT,
100 user_id integer NOT NULL REFERENCES users(user_id),
101 bDate date NOT NULL,
102 bStartTime time NOT NULL,
103 bParty integer NOT NULL,
104 PRIMARY KEY (BookingID),
105 INDEX dateTime(bDate,bStartTime)
106);
107
108# Timetable Table
109CREATE TABLE Timetable(
110 tDay char(9) NOT NULL,
111 tTime time NOT NULL,
112 SessionID integer NOT NULL REFERENCES Session(SessionID),
113 PRIMARY KEY (tDay,tTime)
114);
115
116INSERT INTO Timetable values('Monday','09:00:00',1);
117INSERT INTO Timetable values('Monday','10:00:00',2);
118INSERT INTO Timetable values('Monday','11:00:00',3);
119INSERT INTO Timetable values('Monday','12:00:00',4);
120INSERT INTO Timetable values('Monday','13:00:00',5);
121INSERT INTO Timetable values('Monday','14:00:00',6);
122INSERT INTO Timetable values('Monday','15:00:00',7);
123INSERT INTO Timetable values('Monday','16:00:00',8);
124INSERT INTO Timetable values('Monday','17:00:00',9);
125INSERT INTO Timetable values('Monday','18:00:00',4);
126INSERT INTO Timetable values('Monday','19:00:00',8);
127INSERT INTO Timetable values('Monday','20:00:00',3);
128INSERT INTO Timetable values('Monday','21:00:00',5);
129
130INSERT INTO Timetable values('Tuesday','09:00:00',4);
131INSERT INTO Timetable values('Tuesday','10:00:00',3);
132INSERT INTO Timetable values('Tuesday','11:00:00',6);
133INSERT INTO Timetable values('Tuesday','12:00:00',4);
134INSERT INTO Timetable values('Tuesday','13:00:00',8);
135INSERT INTO Timetable values('Tuesday','14:00:00',2);
136INSERT INTO Timetable values('Tuesday','15:00:00',7);
137INSERT INTO Timetable values('Tuesday','16:00:00',1);
138INSERT INTO Timetable values('Tuesday','17:00:00',3);
139INSERT INTO Timetable values('Tuesday','18:00:00',7);
140INSERT INTO Timetable values('Tuesday','19:00:00',9);
141INSERT INTO Timetable values('Tuesday','20:00:00',2);
142INSERT INTO Timetable values('Tuesday','21:00:00',5);
143
144INSERT INTO Timetable values('Wednesday','09:00:00',4);
145INSERT INTO Timetable values('Wednesday','10:00:00',7);
146INSERT INTO Timetable values('Wednesday','11:00:00',2);
147INSERT INTO Timetable values('Wednesday','12:00:00',5);
148INSERT INTO Timetable values('Wednesday','13:00:00',3);
149INSERT INTO Timetable values('Wednesday','14:00:00',6);
150INSERT INTO Timetable values('Wednesday','15:00:00',1);
151INSERT INTO Timetable values('Wednesday','16:00:00',4);
152INSERT INTO Timetable values('Wednesday','17:00:00',5);
153INSERT INTO Timetable values('Wednesday','18:00:00',8);
154INSERT INTO Timetable values('Wednesday','19:00:00',6);
155INSERT INTO Timetable values('Wednesday','20:00:00',5);
156INSERT INTO Timetable values('Wednesday','21:00:00',9);
157
158INSERT INTO Timetable values('Thursday','09:00:00',3);
159INSERT INTO Timetable values('Thursday','10:00:00',5);
160INSERT INTO Timetable values('Thursday','11:00:00',1);
161INSERT INTO Timetable values('Thursday','12:00:00',9);
162INSERT INTO Timetable values('Thursday','13:00:00',7);
163INSERT INTO Timetable values('Thursday','14:00:00',2);
164INSERT INTO Timetable values('Thursday','15:00:00',4);
165INSERT INTO Timetable values('Thursday','16:00:00',6);
166INSERT INTO Timetable values('Thursday','17:00:00',8);
167INSERT INTO Timetable values('Thursday','18:00:00',3);
168INSERT INTO Timetable values('Thursday','19:00:00',6);
169INSERT INTO Timetable values('Thursday','20:00:00',9);
170INSERT INTO Timetable values('Thursday','21:00:00',2);
171
172INSERT INTO Timetable values('Friday','09:00:00',3);
173INSERT INTO Timetable values('Friday','10:00:00',2);
174INSERT INTO Timetable values('Friday','11:00:00',1);
175INSERT INTO Timetable values('Friday','12:00:00',5);
176INSERT INTO Timetable values('Friday','13:00:00',4);
177INSERT INTO Timetable values('Friday','14:00:00',3);
178INSERT INTO Timetable values('Friday','15:00:00',9);
179INSERT INTO Timetable values('Friday','16:00:00',8);
180INSERT INTO Timetable values('Friday','17:00:00',7);
181INSERT INTO Timetable values('Friday','18:00:00',6);
182INSERT INTO Timetable values('Friday','19:00:00',5);
183INSERT INTO Timetable values('Friday','20:00:00',1);
184INSERT INTO Timetable values('Friday','21:00:00',2);
185
186INSERT INTO Timetable values('Saturday','09:00:00',4);
187INSERT INTO Timetable values('Saturday','10:00:00',5);
188INSERT INTO Timetable values('Saturday','11:00:00',7);
189INSERT INTO Timetable values('Saturday','12:00:00',6);
190INSERT INTO Timetable values('Saturday','13:00:00',1);
191INSERT INTO Timetable values('Saturday','14:00:00',2);
192INSERT INTO Timetable values('Saturday','15:00:00',2);
193INSERT INTO Timetable values('Saturday','16:00:00',8);
194INSERT INTO Timetable values('Saturday','17:00:00',9);
195INSERT INTO Timetable values('Saturday','18:00:00',6);
196INSERT INTO Timetable values('Saturday','19:00:00',2);
197INSERT INTO Timetable values('Saturday','20:00:00',5);
198INSERT INTO Timetable values('Saturday','21:00:00',9);
199
200INSERT INTO Timetable values('Sunday','09:00:00',10);
201INSERT INTO Timetable values('Sunday','10:00:00',3);
202INSERT INTO Timetable values('Sunday','11:00:00',8);
203INSERT INTO Timetable values('Sunday','12:00:00',7);
204INSERT INTO Timetable values('Sunday','13:00:00',1);
205INSERT INTO Timetable values('Sunday','14:00:00',9);
206INSERT INTO Timetable values('Sunday','15:00:00',6);
207INSERT INTO Timetable values('Sunday','16:00:00',9);
208INSERT INTO Timetable values('Sunday','17:00:00',7);
209INSERT INTO Timetable values('Sunday','18:00:00',2);
210INSERT INTO Timetable values('Sunday','19:00:00',10);
211INSERT INTO Timetable values('Sunday','20:00:00',10);
212INSERT INTO Timetable values('Sunday','21:00:00',10);