uJuQe00p

1drop database if exists emc;
2create database emc;
3use emc;
4
5create table client_type (
6id			int(4) 			PRIMARY KEY,
7clientTypeName 		varchar(255) 	NOT NULL);
8
9create table client (
10id 			int(4) 			PRIMARY KEY,
11type 		int(4)			NOT NULL,
12name 		varchar(255)	NOT NULL,
13address		varchar(255)	NOT NULL,
14repName 	varchar(255)	NOT NULL,
15repContact 	int				NOT NULL UNIQUE,
16CONSTRAINT 	clientType_fk FOREIGN KEY (type) REFERENCES client_type(id));
17
18create table client_contact (
19name		varchar(255)	NOT NULL,
20contact 	int				NOT NULL UNIQUE,
21clientID 	int(4)			NOT NULL,
22email 		varchar(255)	NULL,
23CONSTRAINT 	clientID_fk FOREIGN KEY (clientID) REFERENCES client(id));
24
25create table acc_type (
26id 			int(4) 			PRIMARY KEY,
27name 		varchar(255)	NOT NULL);
28
29create table acc (
30id 			int(4) 			PRIMARY KEY,
31accType 	int(4)			NOT NULL,
32name 		varchar(255)	NOT NULL,
33maxOccupants int			NOT NULL,
34charge		smallint		NOT NULL,
35CONSTRAINT 	accType_fk FOREIGN KEY (accType) REFERENCES acc_type(id));
36
37create table advisor (
38id			int(4)			PRIMARY KEY,
39name		varchar(255)	NOT NULL);
40
41create table booking (
42id 			int(4) 			PRIMARY KEY,
43client 		int(4)			NOT NULL,
44advisor		int(4)			NULL,
45bookingDate date			NOT NULL,
46startDate 	date			NOT NULL,
47endDate 	date			NOT NULL,
48totalPeople int				NOT NULL,
49acc 		int(4)			NOT NULL,
50catering 	varchar(255)	NOT NULL,
51CONSTRAINT 	client_fk FOREIGN KEY (client) REFERENCES client(id),
52CONSTRAINT 	acc_fk FOREIGN KEY (acc) REFERENCES acc(id),
53CONSTRAINT 	advisor_fk FOREIGN KEY (advisor) REFERENCES advisor(id),
54CONSTRAINT 	my_catering CHECK (catering = 'RESTAURANT' OR catering = 'SELF'));
55
56create table rating (
57bookingId 	int(4)			NOT NULL,
58rating		int(1)			NOT NULL,
59note		varchar(255)	NULL,
60CONSTRAINT bookingIdr_fk FOREIGN KEY (bookingId) REFERENCES booking(id));
61
62create table facilities (
63id			int(4)			PRIMARY KEY,
64name		varchar(255)	NOT NULL,
65charge		smallint		NOT NULL);
66
67create table outdoors (
68id			int(4)			PRIMARY KEY,
69name		varchar(255)	NOT NULL,
70charge 		smallint		NOT NULL);
71
72create table staff (
73id			int(4)			PRIMARY KEY,
74name		varchar(255)	NOT NULL,
75contract	varchar(255)	NOT NULL,
76supervisor	int(4)			NOT NULL,
77CONSTRAINT my_contract CHECK (contract = 'PART-TIME' OR contract = 'FULL-TIME'),
78CONSTRAINT supervisor_fk FOREIGN KEY (supervisor) REFERENCES outdoors(id));
79
80create table booking_facilities (
81bookingID	int(4)			NOT NULL,
82facilities 	int(4)			NOT NULL,
83date		date			NOT NULL,
84CONSTRAINT bookingfacID_fk	FOREIGN KEY (bookingID) REFERENCES booking(id),
85CONSTRAINT facilities_fk FOREIGN KEY (facilities) REFERENCES facilities(id));
86
87create table booking_outdoors (
88bookingID	int(4)			NOT NULL,
89outdoors 	int(4)			NOT NULL,
90date		date			NOT NULL,
91CONSTRAINT bookingoutID_fk FOREIGN KEY (bookingID) REFERENCES booking(id),
92CONSTRAINT outdoors_fk FOREIGN KEY (outdoors) REFERENCES outdoors(id));
93
94create table booking_snoopyLupiz (
95bookingID	int(4)			NOT NULL,
96singleroom	tinyint			NULL,
97doubleroom 	tinyint			NULL,
98kingsuite	tinyint			NULL, 
99luxurysuite	tinyint			NULL,
100CONSTRAINT bookingID_fk FOREIGN KEY (bookingID) REFERENCES booking(id));
101
102
103insert into client_type values (0001, 'Family');
104insert into client_type values (0002, 'Business');
105insert into client_type values (0003, 'School');
106insert into client_type values (0004, 'Youth Group');
107
108insert into client values (1001, 3, 'Maira\'s International School', 'Washington', 'Tyson', 179184289);
109insert into client values (1002, 1, 'Devon and Family', 'Springfield', 'Aurore', 189373252);
110insert into client values (1003, 3, 'Verlie\'s International School', 'Springfield', 'Suk', 145797438);
111insert into client values (1004, 4, 'Daniela Youth Group', 'Bristol', 'Nichelle', 122719731);
112insert into client values (1005, 3, 'Dakota\'s International School', 'Dover', 'Mathilde', 177414314);
113insert into client values (1006, 1, 'Karlene and Family', 'Oxford', 'Myung', 124888779);
114insert into client values (1007, 4, 'Lavina Youth Group', 'Oxford', 'Lynda', 115942338);
115insert into client values (1008, 2, 'Tiffiny and Co.', 'Lexington', 'Constance', 115169518);
116insert into client values (1009, 2, 'Alfonso and Co.', 'Springfield', 'Jovita', 166417485);
117insert into client values (1010, 1, 'Avery and Family', 'Washington', 'Jana', 138441668);
118insert into client values (1011, 1, 'Damon and Family', 'Hudson', 'Joycelyn', 158374862);
119insert into client values (1012, 4, 'Damaris Youth Group', 'Bristol', 'Lorene', 129897562);
120insert into client values (1013, 1, 'Gil and Family', 'Oakland', 'Jama', 161398593);
121insert into client values (1014, 4, 'Ashton Youth Group', 'Madison', 'Sharda', 145352146);
122insert into client values (1015, 3, 'Marcell\'s International School', 'Oxford', 'Ashlee', 199326331);
123insert into client values (1016, 2, 'Francesco and Co.', 'Auburn', 'Garth', 132431419);
124insert into client values (1017, 1, 'Tricia and Family', 'Manchester', 'Ka', 174244989);
125insert into client values (1018, 3, 'Larry\'s International School', 'Washington', 'Wendie', 111884618);
126insert into client values (1019, 4, 'Clifton Youth Group', 'Springfield', 'Werner', 149512627);
127insert into client values (1020, 2, 'Serena and Co.', 'Manchester', 'Carlos', 141724562);
128insert into client values (1021, 4, 'Howard Youth Group', 'Burlington', 'Toshia', 157678296);
129insert into client values (1022, 1, 'Lourie and Family', 'Jackson', 'Bianca', 162998631);
130insert into client values (1023, 3, 'Yelena\'s International School', 'Oxford', 'Barb', 117572587);
131insert into client values (1024, 3, 'Krysta\'s International School', 'Washington', 'Cristine', 162822896);
132insert into client values (1025, 4, 'Dawn Youth Group', 'Bristol', 'Ashlea', 146453655);
133insert into client values (1026, 1, 'Magen and Family', 'Springfield', 'Thad', 136565338);
134insert into client values (1027, 2, 'Ardelia and Co.', 'Oxford', 'Omer', 158513664);
135insert into client values (1028, 3, 'Hiram\'s International School', 'Georgetown', 'Foster', 143265599);
136insert into client values (1029, 3, 'Rhea\'s International School', 'Auburn', 'Sharen', 121272177);
137insert into client values (1030, 4, 'Millard Youth Group', 'Madison', 'Rolanda', 129812855);
138
139insert into client_contact values ('Cinthia', 171684565, 1003, NULL);
140insert into client_contact values ('Sadie', 152444456, 1029, 'Sadie@gmail.com');
141insert into client_contact values ('Roger', 187659462, 1006, 'Roger@gmail.com');
142insert into client_contact values ('Kattie', 182222762, 1030, NULL);
143insert into client_contact values ('Lila', 185587429, 1027, NULL);
144insert into client_contact values ('Brice', 191253153, 1026, NULL);
145insert into client_contact values ('Rosamond', 146625519, 1020, 'Rosamond@gmail.com');
146insert into client_contact values ('Frank', 122615294, 1027, NULL);
147insert into client_contact values ('Tracy', 112837337, 1029, NULL);
148insert into client_contact values ('Shenika', 197844692, 1013, NULL);
149insert into client_contact values ('Criselda', 116755121, 1013, 'Criselda@gmail.com');
150insert into client_contact values ('Myron', 184451318, 1028, NULL);
151insert into client_contact values ('Tesha', 184689376, 1025, NULL);
152insert into client_contact values ('Virginia', 191517761, 1003, 'Virginia@gmail.com');
153insert into client_contact values ('Donald', 173973382, 1017, NULL);
154insert into client_contact values ('Salvador', 157119578, 1001, 'Salvador@gmail.com');
155insert into client_contact values ('Twanna', 182114668, 1010, NULL);
156insert into client_contact values ('Alycia', 145814292, 1006, NULL);
157insert into client_contact values ('Glory', 126996977, 1028, 'Glory@gmail.com');
158insert into client_contact values ('Hermila', 127735291, 1029, 'Hermila@gmail.com');
159insert into client_contact values ('Jan', 164172963, 1018, NULL);
160insert into client_contact values ('Neil', 182768369, 1020, NULL);
161insert into client_contact values ('Oralee', 132325859, 1024, 'Oralee@gmail.com');
162insert into client_contact values ('Rina', 186357158, 1005, NULL);
163insert into client_contact values ('Ingeborg', 177848639, 1026, 'Ingeborg@gmail.com');
164insert into client_contact values ('Kathrin', 145384391, 1006, 'Kathrin@gmail.com');
165insert into client_contact values ('Kerry', 112627879, 1027, 'Kerry@gmail.com');
166insert into client_contact values ('Marylou', 181319913, 1020, NULL);
167insert into client_contact values ('Jackie', 184521224, 1030, 'Jackie@gmail.com');
168insert into client_contact values ('Florencio', 127816595, 1006, 'Florencio@gmail.com');
169
170insert into acc_type values (0001, 'Hotel');
171insert into acc_type values (0002, 'Hostel');
172insert into acc_type values (0003, 'Camping Ground');
173
174insert into acc values (2001, 1, 'Snoopy Lupiz'   , 230, 100);
175insert into acc values (2002, 2, 'Laquanda\'s Hostel'   , 48, 70);
176insert into acc values (2003, 2, 'Nathanial\'s Hostel'   , 40, 70);
177insert into acc values (2004, 3, 'Joy\'s Camping Ground'   , 42, 40);
178insert into acc values (2005, 3, 'Lanie\'s Camping Ground'   , 39, 25);
179insert into acc values (2006, 2, 'Fletcher\'s Hostel'   , 46, 60);
180insert into acc values (2007, 2, 'Lesley\'s Hostel'   , 42, 50);
181insert into acc values (2008, 3, 'Berna\'s Camping Ground'   , 30, 40);
182insert into acc values (2009, 2, 'Terisa\'s Hostel'   , 39, 45);
183insert into acc values (2010, 3, 'Brigida\'s Camping Ground'   , 41, 30);
184insert into acc values (2011, 2, 'Viviana\'s Hostel'   , 39, 65);
185insert into acc values (2012, 2, 'Kecia\'s Hostel'   , 35, 85);
186insert into acc values (2013, 3, 'Ivan\'s Camping Ground'   , 44, 35);
187insert into acc values (2014, 3, 'Verdell\'s Camping Ground'   , 34, 20);
188insert into acc values (2015, 3, 'Nicola\'s Camping Ground'   , 34, 25);
189insert into acc values (2016, 3, 'Marie\'s Camping Ground'   , 43, 20);
190insert into acc values (2017, 3, 'Sherrill\'s Camping Ground'   , 49, 25);
191insert into acc values (2018, 2, 'Hyo\'s Hostel'   , 39, 70);
192insert into acc values (2019, 3, 'Madlyn\'s Camping Ground'   , 31, 25);
193insert into acc values (2020, 2, 'Veronica\'s Hostel'   , 36, 85);
194insert into acc values (2021, 3, 'Luvenia\'s Camping Ground'   , 42, 25);
195insert into acc values (2022, 2, 'Elijah\'s Hostel'   , 43, 55);
196insert into acc values (2023, 3, 'Jolanda\'s Camping Ground'   , 48, 30);
197insert into acc values (2024, 3, 'Renaldo\'s Camping Ground'   , 40, 30);
198insert into acc values (2025, 2, 'Vella\'s Hostel'   , 31, 65);
199insert into acc values (2026, 2, 'Edith\'s Hostel'   , 47, 60);
200insert into acc values (2027, 3, 'Millicent\'s Camping Ground'   , 39, 50);
201insert into acc values (2028, 3, 'Dotty\'s Camping Ground'   , 40, 35);
202insert into acc values (2029, 2, 'Samella\'s Hostel'   , 45, 60);
203insert into acc values (2030, 3, 'Darryl\'s Camping Ground'   , 31, 15);
204
205insert into advisor values (8001, 'Gordon');
206insert into advisor values (8002, 'Dottie');
207insert into advisor values (8003, 'Thomasine');
208insert into advisor values (8004, 'Brittany');
209insert into advisor values (8005, 'Nell');
210insert into advisor values (8006, 'Nannette');
211insert into advisor values (8007, 'Shery');
212insert into advisor values (8008, 'Randell');
213insert into advisor values (8009, 'Halley');
214insert into advisor values (8010, 'Martina');
215insert into advisor values (8011, 'Alvina');
216insert into advisor values (8012, 'Ester');
217insert into advisor values (8013, 'Letisha');
218insert into advisor values (8014, 'Brande');
219insert into advisor values (8015, 'Louanne');
220insert into advisor values (8016, 'Lawerence');
221insert into advisor values (8017, 'Camellia');
222insert into advisor values (8018, 'Bart');
223insert into advisor values (8019, 'Edith');
224insert into advisor values (8020, 'Adella');
225insert into advisor values (8021, 'Caroyln');
226insert into advisor values (8022, 'Mike');
227insert into advisor values (8023, 'Senaida');
228insert into advisor values (8024, 'Tania');
229insert into advisor values (8025, 'Odelia');
230insert into advisor values (8026, 'Melonie');
231insert into advisor values (8027, 'Herlinda');
232insert into advisor values (8028, 'Ellan');
233insert into advisor values (8029, 'Forrest');
234insert into advisor values (8030, 'Tyron');
235
236select bookingID from booking_outdoors where outdoors = 6001;
237
238insert into booking values (3001, 1001, 8001, '2013-2-1', '2013-3-2', '2013-3-4', 18, 2014, 'SELF');
239insert into booking values (3002, 1014, 8001, '2013-3-7', '2013-3-12', '2013-3-14', 25, 2010, 'RESTAURANT');
240insert into booking values (3003, 1007, 8001, '2013-3-17', '2013-3-20', '2013-3-24', 23, 2001, 'RESTAURANT');
241insert into booking values (3004, 1030, NULL, '2013-4-27', '2013-6-1', '2013-6-4', 15, 2008, 'RESTAURANT');
242insert into booking values (3005, 1016, 8003, '2013-6-6', '2013-6-9', '2013-6-13', 27, 2015, 'RESTAURANT');
243insert into booking values (3006, 1011, 8004, '2013-7-14', '2013-7-16', '2013-7-20', 21, 2015, 'RESTAURANT');
244insert into booking values (3007, 1030, 8005, '2013-8-22', '2013-8-27', '2013-9-1', 21, 2006, 'SELF');
245insert into booking values (3008, 1008, 8006, '2013-9-4', '2013-10-8', '2013-10-12', 20, 2003, 'SELF');
246insert into booking values (3009, 1013, NULL, '2013-10-13', '2013-10-16', '2013-10-20', 22, 2017, 'RESTAURANT');
247insert into booking values (3010, 1018, 8007, '2013-10-20', '2013-11-24', '2013-11-26', 15, 2030, 'RESTAURANT');
248insert into booking values (3011, 1003, 8007, '2014-1-28', '2014-3-1', '2014-3-4', 30, 2001, 'SELF');
249insert into booking values (3012, 1004, 8008, '2014-4-4', '2014-5-9', '2014-5-13', 21, 2010, 'SELF');
250insert into booking values (3013, 1028, 8009, '2014-5-16', '2014-5-21', '2014-5-24', 16, 2004, 'RESTAURANT');
251insert into booking values (3014, 1004, 8010, '2014-6-27', '2014-7-1', '2014-7-4', 21, 2016, 'SELF');
252insert into booking values (3015, 1007, 8010, '2014-7-5', '2014-7-8', '2014-7-12', 17, 2018, 'RESTAURANT');
253insert into booking values (3016, 1027, 8011, '2014-8-12', '2014-8-13', '2014-8-17', 18, 2001, 'RESTAURANT');
254insert into booking values (3017, 1002, NULL, '2014-9-17', '2014-10-21', '2014-10-23', 16, 2006, 'SELF');
255insert into booking values (3018, 1012, NULL, '2014-10-26', '2014-10-27', '2014-11-1', 19, 2006, 'RESTAURANT');
256insert into booking values (3019, 1025, 8012, '2015-1-2', '2015-1-5', '2015-1-8', 25, 2003, 'RESTAURANT');
257insert into booking values (3020, 1006, 8013, '2015-2-9', '2015-2-13', '2015-2-17', 16, 2020, 'SELF');
258insert into booking values (3021, 1010, 8013, '2015-3-17', '2015-3-22', '2015-3-24', 27, 2006, 'SELF');
259insert into booking values (3022, 1002, 8015, '2015-3-25', '2015-3-26', '2015-3-29', 15, 2024, 'RESTAURANT');
260insert into booking values (3023, 1018, 8016,'2015-4-1', '2015-4-4', '2015-4-6', 16, 2014, 'SELF');
261insert into booking values (3024, 1024, 8017, '2015-5-9', '2015-6-9', '2015-6-13', 16, 2016, 'SELF');
262insert into booking values (3025, 1017, 8018,'2015-6-13', '2015-7-16', '2015-7-19', 29, 2023, 'SELF');
263insert into booking values (3026, 1023, 8018, '2015-7-21', '2015-7-25', '2015-7-29', 26, 2025, 'RESTAURANT');
264insert into booking values (3027, 1030, 8019, '2015-9-1', '2015-9-3', '2015-9-5', 23, 2003, 'RESTAURANT');
265insert into booking values (3028, 1009, 8020, '2015-10-6', '2015-11-10', '2015-11-12', 19, 2005, 'SELF');
266insert into booking values (3029, 1001, NULL, '2015-11-14', '2015-11-14', '2015-11-18', 19, 2015, 'RESTAURANT');
267insert into booking values (3030, 1030, 8021,'2016-1-20', '2016-2-24', '2016-2-27', 29, 2005, 'RESTAURANT');
268insert into booking values (3031, 1005, 8021, '2016-2-29', '2016-3-1', '2016-3-3', 26, 2026, 'SELF');
269insert into booking values (3032, 1019, 8022, '2016-3-4', '2016-3-9', '2016-3-13', 17, 2019, 'SELF');
270insert into booking values (3033, 1006, 8023, '2016-4-13', '2016-5-13', '2016-5-16', 20, 2028, 'RESTAURANT');
271insert into booking values (3034, 1029, 8024, '2016-5-18', '2016-6-19', '2016-6-22', 30, 2018, 'RESTAURANT');
272insert into booking values (3035, 1005, NULL, '2016-6-25', '2016-7-25', '2016-7-29', 15, 2027, 'RESTAURANT');
273insert into booking values (3036, 1020, 8025, '2016-8-1', '2016-9-4', '2016-9-8', 20, 2028, 'SELF');
274insert into booking values (3037, 1021, 8026, '2016-9-9', '2016-10-12', '2016-10-14', 26, 2023, 'SELF');
275insert into booking values (3038, 1023, 8026, '2016-10-17', '2016-11-18', '2016-11-20', 30, 2024, 'SELF');
276insert into booking values (3039, 1003, 8027, '2016-11-20', '2016-11-25', '2016-11-27', 19, 2025, 'SELF');
277insert into booking values (3040, 1026, 8028, '2016-11-28', '2017-1-1', '2017-1-5', 29, 2013, 'SELF');
278insert into booking values (3041, 1009, NULL, '2017-2-6', '2017-2-6', '2017-2-8', 19, 2014, 'RESTAURANT');
279insert into booking values (3042, 1004, 8029, '2017-2-10', '2017-2-12', '2017-2-15', 27, 2005, 'SELF');
280insert into booking values (3043, 1004, 8030, '2017-3-18', '2017-3-20', '2017-3-24', 22, 2007, 'RESTAURANT');
281insert into booking values (3044, 1016, 8030, '2017-3-25', '2017-3-29', '2017-4-1', 21, 2012, 'SELF');
282insert into booking values (3045, 1011, 8001, '2017-4-4', '2017-4-8', '2017-4-12', 25, 2002, 'RESTAURANT');
283insert into booking values (3046, 1009, 8002, '2017-4-13', '2017-5-18', '2017-5-22', 17, 2025, 'RESTAURANT');
284insert into booking values (3047, 1015, 8002, '2017-6-25', '2017-7-1', '2017-7-3', 24, 2002, 'RESTAURANT');
285insert into booking values (3048, 1022, 8003, '2017-8-6', '2017-8-11', '2017-8-13', 29, 2012, 'SELF');
286insert into booking values (3049, 1014, 8004, '2017-8-15', '2017-8-20', '2017-8-23', 16, 2008, 'RESTAURANT');
287insert into booking values (3050, 1006, NULL, '2017-9-26', '2017-9-27', '2017-9-29', 22, 2018, 'RESTAURANT');
288	
289insert into rating values(3001, 4, "");
290insert into rating values(3002, 5, "");
291insert into rating values(3003, 4, "Hotel was extremely nice, will definitely visit again!");
292insert into rating values(3004, 5, "");
293insert into rating values(3005, 1, "Staff was very rude");
294insert into rating values(3006, 3, "");
295insert into rating values(3007, 4, "Conference Hall very spacious and well equipped, very glad we chose EMC.");
296insert into rating values(3008, 3, "");
297insert into rating values(3009, 4, NULL);
298insert into rating values(3010, 1, "Computers very slow and dated, please upgrade!");
299insert into rating values(3011, 4, NULL);
300insert into rating values(3012, 2, NULL);
301insert into rating values(3013, 3, "");
302insert into rating values(3014, 4, "");
303insert into rating values(3015, 4, NULL);
304insert into rating values(3016, 5, "Paintball was very fun, even got to shoot the boss!");
305insert into rating values(3017, 4, NULL);
306insert into rating values(3018, 3, "Paintball was fun, but pricing was a little bit expensive.");
307insert into rating values(3019, 4, "");
308insert into rating values(3020, 4, NULL);
309insert into rating values(3021, 3, "");
310insert into rating values(3022, 1, NULL);
311insert into rating values(3023, 2, "Computers in lab 2 very unresponsive and slow!");
312insert into rating values(3024, 5, "");
313insert into rating values(3025, 4, NULL);
314insert into rating values(3026, 2, NULL);
315insert into rating values(3027, 5, NULL);
316insert into rating values(3028, 4, NULL);
317insert into rating values(3029, 5, "");
318insert into rating values(3030, 3, NULL);
319insert into rating values(3031, 4, NULL);
320insert into rating values(3032, 2, "We stayed at Dotty's camping ground and it was very unsanitary and dirty.");
321insert into rating values(3033, 3, NULL);
322insert into rating values(3034, 3, NULL);
323insert into rating values(3035, 2, NULL);
324insert into rating values(3036, 1, "Camping ground very dirty, even saw multiple rats crawling about!");
325insert into rating values(3037, 2, NULL);
326insert into rating values(3038, 3, "");
327insert into rating values(3039, 5, "");
328insert into rating values(3040, 5, "");
329insert into rating values(3041, 4, NULL);
330insert into rating values(3042, 4, "");
331insert into rating values(3043, 4, NULL);
332insert into rating values(3044, 3, "");
333insert into rating values(3045, 3, NULL);
334insert into rating values(3046, 3, NULL);
335insert into rating values(3047, 5, NULL);
336insert into rating values(3048, 2, "");
337insert into rating values(3049, 2, NULL);
338insert into rating values(3050, 4, "");   
339
340insert into facilities values (5001, 'Board Room A', 500);
341insert into facilities values (5002, 'Board Room B', 350);
342insert into facilities values (5003, 'Conference Hall', 800);
343insert into facilities values (5004, 'Computing Lab 1', 200);
344insert into facilities values (5005, 'Computing Lab 2', 150);
345insert into facilities values (5006, 'Computing Lab 3', 150);
346insert into facilities values (5007, 'Multimedia Equipment', 70);
347
348insert into outdoors values (6001, 'Wall-climbing', 100);
349insert into outdoors values (6002, 'Paintball', 60);
350insert into outdoors values (6003, 'Canoeing', 60);
351insert into outdoors values (6004, 'Swimming', 60);
352insert into outdoors values (6005, 'Basketball', 40);
353insert into outdoors values (6006, 'Football', 40);
354
355insert into staff values (7001, 'Madaline', 'FULL-TIME', 6001);
356insert into staff values (7002, 'Daine', 'FULL-TIME', 6002);
357insert into staff values (7003, 'Ardelia', 'PART-TIME', 6003);
358insert into staff values (7004, 'Kellye', 'FULL-TIME', 6004);
359insert into staff values (7005, 'Noelia', 'FULL-TIME', 6005);
360insert into staff values (7006, 'Alexandria', 'PART-TIME', 6006);
361insert into staff values (7007, 'Anabel', 'PART-TIME', 6003);
362insert into staff values (7008, 'Breanne', 'FULL-TIME', 6004);
363insert into staff values (7009, 'Roselia', 'PART-TIME', 6002);
364insert into staff values (7010, 'Ronald', 'PART-TIME', 6002);
365
366insert into booking_facilities values (3001, 5003, '2013-3-3');
367insert into booking_facilities values (3002, 5004, '2013-3-13');
368insert into booking_facilities values (3003, 5003, '2013-3-21');
369insert into booking_facilities values (3004, 5002, '2013-6-3');
370insert into booking_facilities values (3005, 5007, '2013-6-10');
371insert into booking_facilities values (3006, 5001, '2013-7-17');
372insert into booking_facilities values (3007, 5003, '2013-8-27');
373insert into booking_facilities values (3008, 5004, '2013-10-9');
374insert into booking_facilities values (3009, 5006, '2013-10-16');
375insert into booking_facilities values (3010, 5005, '2013-11-25');
376insert into booking_facilities values (3011, 5001, '2014-3-2');
377insert into booking_facilities values (3012, 5003, '2014-5-9');
378insert into booking_facilities values (3013, 5002, '2014-5-22');
379insert into booking_facilities values (3014, 5006, '2014-7-3');
380insert into booking_facilities values (3015, 5001, '2014-7-8');
381insert into booking_facilities values (3016, 5002, '2014-8-14');
382insert into booking_facilities values (3017, 5003, '2014-10-21');
383insert into booking_facilities values (3018, 5004, '2014-10-29');
384insert into booking_facilities values (3019, 5004, '2015-1-6');
385insert into booking_facilities values (3020, 5002, '2015-2-13');
386insert into booking_facilities values (3021, 5007, '2015-3-22');
387insert into booking_facilities values (3022, 5001, '2015-3-27');
388insert into booking_facilities values (3023, 5005, '2015-4-5');
389insert into booking_facilities values (3024, 5004, '2015-6-9');
390insert into booking_facilities values (3025, 5003, '2015-7-17');
391insert into booking_facilities values (3026, 5003, '2015-7-26');
392insert into booking_facilities values (3027, 5007, '2015-9-4');
393insert into booking_facilities values (3028, 5007, '2015-11-11');
394insert into booking_facilities values (3029, 5002, '2015-11-14');
395insert into booking_facilities values (3030, 5007, '2016-2-25');
396
397insert into booking_outdoors values (3001, 6006, '2013-3-3');
398insert into booking_outdoors values (3002, 6006, '2013-3-12');
399insert into booking_outdoors values (3003, 6004, '2013-3-21');
400insert into booking_outdoors values (3003, 6003, '2013-3-22');
401insert into booking_outdoors values (3005, 6005, '2013-6-9');
402insert into booking_outdoors values (3006, 6005, '2013-7-17');
403insert into booking_outdoors values (3007, 6005, '2013-8-28');
404insert into booking_outdoors values (3007, 6005, '2013-8-28');
405insert into booking_outdoors values (3008, 6003, '2013-10-8');
406insert into booking_outdoors values (3010, 6005, '2013-11-25');
407insert into booking_outdoors values (3012, 6006, '2014-5-10');
408insert into booking_outdoors values (3014, 6005, '2014-7-2');
409insert into booking_outdoors values (3016, 6005, '2014-8-14');
410insert into booking_outdoors values (3016, 6001, '2014-8-15');
411insert into booking_outdoors values (3018, 6001, '2014-10-17');
412insert into booking_outdoors values (3019, 6004, '2015-1-6');
413insert into booking_outdoors values (3020, 6004, '2015-2-14');
414insert into booking_outdoors values (3021, 6004, '2015-3-23');
415insert into booking_outdoors values (3023, 6003, '2015-4-4');
416insert into booking_outdoors values (3023, 6004, '2015-4-5');
417insert into booking_outdoors values (3025, 6003, '2015-7-17');
418insert into booking_outdoors values (3025, 6004, '2015-7-17');
419insert into booking_outdoors values (3027, 6006, '2015-9-5');
420insert into booking_outdoors values (3029, 6002, '2015-11-15');
421insert into booking_outdoors values (3029, 6002, '2015-11-16');
422insert into booking_outdoors values (3030, 6001, '2016-2-24');
423insert into booking_outdoors values (3031, 6006, '2016-3-2');
424insert into booking_outdoors values (3032, 6002, '2016-3-10');
425insert into booking_outdoors values (3034, 6004, '2016-6-10');
426insert into booking_outdoors values (3036, 6004, '2016-9-5');
427
428insert into booking_snoopyLupiz values (3003, 3, 10, 0, 0);
429insert into booking_snoopyLupiz values (3011, 25, 0, 1, 1);
430insert into booking_snoopyLupiz values (3016, 0, 9, 0, 0);
431
432-- ///////////////////////////////////////////////////////////////////////////
433
434-- make sure to invoke procedure definition queries in procedure2.sql before calling these procedures
435
436call mostServiceTransaction(3);				-- 1 is FAMILY 	| 2 is BUSINESS		| 3 is SCHOOL 			| 4 is YOUTH GROUP
437call getClientList();
438call getAccTotal(2);						-- 1 is HOTEL 	| 2 is HOSTEL/DORM 	| 3 is CAMPING GROUND
439call increaseFacilities(0.05);
440
441call staffBookingMade();
442call maxSales;
443
444
445-- check for rating in (1, 2) and retrieve contact details to ask about experience and possibly provide compensation
446-- or print out all ratings (and contact details and comments) and sort by rating
447
448-- look at which facilities/accommodation/outdoors activities generated the most revenue and possibly add more instances of these
449-- retrieve contact details of clients and send vouchers/promotional news/marketing