· 5 years ago · Jun 05, 2020, 06:46 PM
1-- ======================= TASK #2 - tables creation + FKs, data insertion, updates, triggers =======================
2
3-- 0. Create database
4USE master
5GO
6IF NOT EXISTS (
7 SELECT name
8 FROM sys.databases
9 WHERE name = N'ConferenceCenter2'
10)
11 CREATE DATABASE [ConferenceCenter2];
12GO
13IF SERVERPROPERTY('ProductVersion') > '12'
14 ALTER DATABASE [ConferenceCenter2] SET QUERY_STORE=ON;
15GO
16
17-- 1. Creates tables
18CREATE TABLE clients
19(
20 client_id INT IDENTITY NOT NULL,
21 address_id INT NOT NULL,
22 first_name VARCHAR(255) NOT NULL,
23 surname VARCHAR(255) NOT NULL,
24 email_address VARCHAR(255) NOT NULL,
25 phone_number VARCHAR(15) NOT NULL,
26 PRIMARY KEY (client_id)
27);
28
29CREATE TABLE addresses
30(
31 address_id INT IDENTITY NOT NULL,
32 street_name VARCHAR(50) NOT NULL,
33 street_number INT NOT NULL,
34 apartment_number INT NULL,
35 city VARCHAR(50) NOT NULL,
36 zip_code VARCHAR(10) NOT NULL,
37 country VARCHAR(50) NOT NULL,
38 PRIMARY KEY (address_id)
39);
40
41CREATE TABLE events
42(
43 event_id INT IDENTITY NOT NULL,
44 event_name VARCHAR(255) NOT NULL,
45 web_address VARCHAR(255) NULL,
46 event_details VARCHAR(255) NULL,
47 PRIMARY KEY (event_id)
48);
49
50CREATE TABLE rooms
51(
52 room_id INT IDENTITY NOT NULL,
53 seating_capacity INT NOT NULL,
54 room_type VARCHAR(10) NOT NULL,
55 is_reserved BIT NOT NULL,
56 PRIMARY KEY (room_id)
57);
58
59CREATE TABLE bookings
60(
61 booking_id INT IDENTITY NOT NULL,
62 client_id INT NOT NULL,
63 event_id INT NOT NULL,
64 room_id INT NOT NULL,
65 service_id INT NOT NULL,
66 booking_status_desc VARCHAR(20) NOT NULL,
67 reservation_start_date DATE NOT NULL,
68 reservation_end_date DATE NOT NULL,
69 day_time VARCHAR(10) NOT NULL,
70 booking_details VARCHAR(255) NULL,
71 PRIMARY KEY (booking_id, room_id, day_time)
72);
73
74CREATE TABLE room_facilities
75(
76 facility_id INT NOT NULL,
77 room_id INT NOT NULL
78);
79
80CREATE TABLE ref_facilities
81(
82 facility_id INT IDENTITY NOT NULL,
83 facillity_desc VARCHAR(255) NOT NULL,
84 PRIMARY KEY (facility_id)
85);
86
87CREATE TABLE services
88(
89 service_id INT IDENTITY NOT NULL,
90 catering BIT NOT NULL,
91 cocktail_party BIT NOT NULL,
92 PRIMARY KEY (service_id)
93);
94
95-- 2. Adds constraints
96ALTER TABLE clients
97 ADD CONSTRAINT fk_clients_adresses FOREIGN KEY (address_id)
98 REFERENCES addresses (address_id);
99
100ALTER TABLE room_facilities
101 ADD CONSTRAINT fk_room_facilities_rooms FOREIGN KEY (room_id)
102 REFERENCES rooms (room_id);
103
104ALTER TABLE room_facilities
105 ADD CONSTRAINT fk_room_facilities_ref_facilities FOREIGN KEY (facility_id)
106 REFERENCES ref_facilities (facility_id);
107
108ALTER TABLE bookings
109 ADD CONSTRAINT fk_bookings_rooms FOREIGN KEY (room_id)
110 REFERENCES rooms (room_id);
111
112ALTER TABLE bookings
113 ADD CONSTRAINT fk_bookings_clients FOREIGN KEY (client_id)
114 REFERENCES clients (client_id);
115
116ALTER TABLE bookings
117 ADD CONSTRAINT fk_bookings_services FOREIGN KEY (service_id)
118 REFERENCES services (service_id);
119
120ALTER TABLE bookings
121 ADD CONSTRAINT fk_bookings_events FOREIGN KEY (event_id)
122 REFERENCES events (event_id);
123
124GO;
125
126-- 3. Trigger to reject invalid bookings
127CREATE OR ALTER TRIGGER trg_reject_invalid_bookings
128ON bookings
129FOR INSERT, UPDATE
130
131AS
132DECLARE @v_count int
133
134BEGIN
135 SELECT @v_count = count(*) FROM bookings b1
136 WHERE EXISTS
137 (
138 SELECT *
139 FROM bookings b2
140 -- if overlapping
141 WHERE ((b2.booking_id <> b1.booking_id
142 AND b2.room_id = b1.room_id
143 AND b2.reservation_start_date < b1.reservation_end_date
144 AND b2.reservation_end_date > b1.reservation_start_date
145 AND b2.booking_status_desc = 'confirmed')
146 -- if during the same daytime
147 OR (b2.booking_id <> b1.booking_id
148 AND b2.room_id = b1.room_id
149 AND b2.reservation_start_date = b1.reservation_end_date
150 AND b2.reservation_end_date = b1.reservation_start_date
151 AND b2.day_time = b1.day_time
152 AND b2.booking_status_desc = 'confirmed')
153 -- if a whole center is occupied already
154 OR (b2.booking_id <> b1.booking_id
155 AND b2.reservation_start_date <= b1.reservation_end_date
156 AND b2.reservation_end_date >= b1.reservation_start_date
157 AND b2.day_time = 'whole')
158 )
159)
160 IF @v_count > 0
161 BEGIN
162 RAISERROR('Invalid booking!', 16, 1);
163 END
164END
165
166GO;
167
168-- 4. Inserts of some sample data
169INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
170VALUES ('Marszałkowska', 14, 2, 'Warsaw', '00-142', 'Poland');
171INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
172VALUES ('Floriańska', 43, 8, 'Krakow', '29-132', 'Poland');
173INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
174VALUES ('Lwowska', 41, 43, 'Warsaw', '01-342', 'Poland');
175INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
176VALUES ('Koszykowa', 75, null, 'Warsaw', '01-111', 'Poland');
177INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
178VALUES ('Wspólna', 99, 4, 'Warsaw', '02-853', 'Poland');
179INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
180VALUES ('Piękna', 13, 113, 'Warsaw', '00-539', 'Poland');
181INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
182VALUES ('Nowobogacka', 54, 42, 'Warsaw', '01-412', 'Poland');
183INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
184VALUES ('Chmielna', 4, 15, 'Warsaw', '02-543', 'Poland');
185INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
186VALUES ('Puławska', 84, 11, 'Warsaw', '00-713', 'Poland');
187INSERT INTO Addresses(street_name, street_number, apartment_number, city, zip_code, country)
188VALUES ('Francuska', 17, 4, 'Warsaw', '03-953', 'Poland');
189
190INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
191VALUES (1, 'Jan', 'Brzechwa', 'jan.brzechwa@gmail.com', '+48591847304');
192INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
193VALUES (2, 'Adam', 'Małysz', 'adam.malysz@gmail.com', '+48749275019');
194INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
195VALUES (3, 'Andrzej', 'Duda', 'andrzej.duda@gmail.com', '+48719485729');
196INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
197VALUES (4, 'Robert', 'Biedroń', 'robert.biedron@gmail.com', '+48571948573');
198INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
199VALUES (5, 'Kazik', 'Staszewski', 'kazik.staszewski@gmail.com', '+48719305718');
200INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
201VALUES (6, 'Edyta', 'Górniak', 'edyta.gorniak@gmail.com', '+48711933847');
202INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
203VALUES (7, 'Majka', 'Jeżowska', 'majka.jezowska@gmail.com', '+48798122093');
204INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
205VALUES (8, 'Anna', 'Mucha', 'anna.mucha@gmail.com', '+48559184953');
206INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
207VALUES (9, 'Weronika', 'Rosati', 'weronika.rosati@gmail.com', '+48519385940');
208INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
209VALUES (10, 'Joanna', 'Kożuchowska', 'joanna.kozuchowska@gmail.com', '+48105949304');
210INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
211VALUES (5, 'Radosław', 'Majdan', 'radoslaw.majdaj@gmail.com', '+48105943204');
212INSERT INTO Clients(address_id, first_name, surname, email_address, phone_number)
213VALUES (8, 'Artur', 'Kamień', 'artur.kamien@gmail.com', '+48106969304');
214
215INSERT INTO Events(event_name, web_address, event_details)
216VALUES ('V Ogólnopolskie Sympozjum Mikrobiologicznego pt. „Metagenomy różnych środowisk”', 'www.osm.pl', null);
217INSERT INTO Events(event_name, web_address, event_details)
218VALUES ('Międzynarodowa Konferencja pt. „Turystyka w zrównoważonym rozwoju miast metropolitarnych"', 'bit.ly/TurystykaWRozwojuMiast', null);
219INSERT INTO Events(event_name, web_address, event_details)
220VALUES ('"Państwo-Bezpieczeństwo-Ekonomia w XXI wieku"', null, null);
221INSERT INTO Events(event_name, web_address, event_details)
222VALUES ('The Challenges for Euro-Asia Past-Present-Future', 'www.eappf.pl', null);
223INSERT INTO Events(event_name, web_address, event_details)
224VALUES ('OKN "Smart City"', null, null);
225INSERT INTO Events(event_name, web_address, event_details)
226VALUES ('What’s New, What’s Next? Innovative Methods, New Sources, and Paradigm Shifts in Jewish Studies', null, null);
227INSERT INTO Events(event_name, web_address, event_details)
228VALUES ('Cyber Academy V', 'www.cyberacademy.com.pl', null);
229INSERT INTO Events(event_name, web_address, event_details)
230VALUES ('OKN "Skutki epidemii. Ujęcie prawne, ekonomiczne i społeczne"', 'www.seupeis.pl', null);
231INSERT INTO Events(event_name, web_address, event_details)
232VALUES ('Data Science Summit', 'www.dssconf.pl ', null);
233INSERT INTO Events(event_name, web_address, event_details)
234VALUES ('What The Hack', 'www.wthconf.pl', null);
235
236INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
237VALUES (200, 'seminar', 0);
238INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
239VALUES (30, 'meeting', 0);
240INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
241VALUES (150, 'seminar', 0);
242INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
243VALUES (250, 'seminar', 0);
244INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
245VALUES (20, 'meeting', 0);
246INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
247VALUES (10, 'meeting', 0);
248INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
249VALUES (20, 'meeting', 0);
250INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
251VALUES (100, 'terrace', 0);
252INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
253VALUES (100, 'terrace', 0);
254INSERT INTO Rooms(seating_capacity, room_type, is_reserved)
255VALUES (880, 'center', 0);
256
257INSERT INTO Services(catering, cocktail_party)
258VALUES (0, 0);
259INSERT INTO Services(catering, cocktail_party)
260VALUES (1, 0);
261INSERT INTO Services(catering, cocktail_party)
262VALUES (1, 0);
263INSERT INTO Services(catering, cocktail_party)
264VALUES (0, 0);
265INSERT INTO Services(catering, cocktail_party)
266VALUES (1, 0);
267INSERT INTO Services(catering, cocktail_party)
268VALUES (0, 0);
269INSERT INTO Services(catering, cocktail_party)
270VALUES (0, 0);
271INSERT INTO Services(catering, cocktail_party)
272VALUES (1, 1);
273INSERT INTO Services(catering, cocktail_party)
274VALUES (0, 1);
275INSERT INTO Services(catering, cocktail_party)
276VALUES (1, 1);
277
278INSERT INTO Ref_facilities(facillity_desc)
279VALUES ('WiFi, 2 projectors, 3 whiteboards');
280INSERT INTO Ref_facilities(facillity_desc)
281VALUES ('WiFi, 1 projector, 1 TV, 1 whiteboard');
282INSERT INTO Ref_facilities(facillity_desc)
283VALUES ('WiFi, video conferencing system, 2 TV, 2 whiteboards');
284INSERT INTO Ref_facilities(facillity_desc)
285VALUES ('WiFi, loudspeakers, 1 projector');
286INSERT INTO Ref_facilities(facillity_desc)
287VALUES ('WiFi, 1 projector');
288INSERT INTO Ref_facilities(facillity_desc)
289VALUES ('WiFi, 2 TV');
290INSERT INTO Ref_facilities(facillity_desc)
291VALUES ('WiFi, 1 TV, 1 whiteboard');
292INSERT INTO Ref_facilities(facillity_desc)
293VALUES ('WiFi');
294INSERT INTO Ref_facilities(facillity_desc)
295VALUES ('WiFi');
296INSERT INTO Ref_facilities(facillity_desc)
297VALUES ('WiFi, 5 projectors, video conferencing system, loudspeakers, 5 TV, 6 whiteboards');
298
299INSERT INTO Room_facilities(facility_id, room_id)
300VALUES (1, 1);
301INSERT INTO Room_facilities(facility_id, room_id)
302VALUES (2, 2);
303INSERT INTO Room_facilities(facility_id, room_id)
304VALUES (3, 3);
305INSERT INTO Room_facilities(facility_id, room_id)
306VALUES (4, 4);
307INSERT INTO Room_facilities(facility_id, room_id)
308VALUES (5, 5);
309INSERT INTO Room_facilities(facility_id, room_id)
310VALUES (6, 6);
311INSERT INTO Room_facilities(facility_id, room_id)
312VALUES (7, 7);
313INSERT INTO Room_facilities(facility_id, room_id)
314VALUES (8, 8);
315INSERT INTO Room_facilities(facility_id, room_id)
316VALUES (9, 9);
317INSERT INTO Room_facilities(facility_id, room_id)
318VALUES (10, 10);
319
320SET IDENTITY_INSERT bookings ON;
321INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
322VALUES (1, 1, 1, 1, 1, 'confirmed', '2020-06-05', '2020-06-05', 'morning', null);
323INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
324VALUES (1, 1, 1, 2, 1, 'confirmed', '2020-06-05', '2020-06-05', 'morning', null);
325INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
326VALUES (2, 2, 2, 1, 2, 'confirmed', '2020-06-05', '2020-06-05', 'afternoon', null);
327INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
328VALUES (2, 2, 2, 2, 2, 'confirmed', '2020-06-05', '2020-06-05', 'afternoon', null);
329INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
330VALUES (3, 3, 3, 1, 3, 'confirmed', '2020-06-06', '2020-06-06', 'morning', null);
331INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
332VALUES (3, 3, 3, 2, 3, 'confirmed', '2020-06-06', '2020-06-06', 'morning', null);
333INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
334VALUES (3, 3, 3, 3, 3, 'confirmed', '2020-06-06', '2020-06-06', 'morning', null);
335
336INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
337VALUES (4, 4, 4, 1, 4, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
338INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
339VALUES (4, 4, 4, 2, 4, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
340INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
341VALUES (4, 4, 4, 3, 4, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
342INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
343VALUES (4, 4, 4, 4, 4, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
344
345INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
346VALUES (5, 5, 5, 5, 5, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
347INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
348VALUES (6, 6, 6, 6, 6, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
349INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
350VALUES (7, 7, 7, 7, 7, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
351INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
352VALUES (8, 8, 8, 8, 8, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
353
354INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
355VALUES (9, 9, 9, 5, 5, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
356INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
357VALUES (9, 9, 9, 6, 6, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
358INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
359VALUES (10, 10, 10, 5, 5, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
360INSERT INTO Bookings(booking_id, client_id, event_id, room_id, service_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time, booking_details)
361VALUES (10, 10, 10, 6, 6, 'pending', '2020-06-07', '2020-06-07', 'afternoon', null);
362
363select * from rooms;
364select * from bookings;
365
366/*
367drop table bookings;
368CREATE TABLE bookings
369(
370 booking_id INT IDENTITY NOT NULL,
371 client_id INT NOT NULL,
372 event_id INT NOT NULL,
373 room_id INT NOT NULL,
374 service_id INT NOT NULL,
375 booking_status_desc VARCHAR(20) NOT NULL,
376 reservation_start_date DATE NOT NULL,
377 reservation_end_date DATE NOT NULL,
378 day_time VARCHAR(10) NOT NULL,
379 booking_details VARCHAR(255) NULL,
380 PRIMARY KEY (booking_id, room_id, day_time)
381);
382ALTER TABLE bookings
383 ADD CONSTRAINT fk_bookings_rooms FOREIGN KEY (room_id)
384 REFERENCES rooms (room_id);
385ALTER TABLE bookings
386 ADD CONSTRAINT fk_bookings_clients FOREIGN KEY (client_id)
387 REFERENCES clients (client_id);
388ALTER TABLE bookings
389 ADD CONSTRAINT fk_bookings_services FOREIGN KEY (service_id)
390 REFERENCES services (service_id);
391ALTER TABLE bookings
392 ADD CONSTRAINT fk_bookings_events FOREIGN KEY (event_id)
393 REFERENCES events (event_id);
394SET IDENTITY_INSERT bookings ON;
395*/
396
397-- 5. A few simple updates
398UPDATE bookings
399SET day_time = 'morning'
400WHERE client_id = 5;
401
402UPDATE bookings
403SET booking_status_desc = 'confirmed'
404WHERE client_id = 4;
405
406UPDATE services
407SET catering = 1
408WHERE service_id = 1;
409
410UPDATE rooms
411SET is_reserved = 1
412FROM Rooms
413INNER JOIN bookings
414ON (rooms.room_id = bookings.room_id)
415WHERE booking_status_desc = 'confirmed'
416
417-- ======================= TASK #3 - creating indices =======================
418-- All of the below indexes are non-clustered, since they will mostly help in
419-- retrieving data quicker from the tables when used e.g. in WHERE/GROUP BY/ORDER BY
420-- clausules and in avoiding overhead cost associated with the clustered index
421
422-- 1. Helpful when it comes to searching for dates, where some room is not occupied
423IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'ix_bookings_reservation_start_date')
424 DROP INDEX ix_bookings_reservation_start_date ON bookings;
425GO
426CREATE NONCLUSTERED INDEX ix_bookings_reservation_start_date ON bookings(reservation_start_date);
427GO
428
429-- 2. Useful when querying for particular room and this table is not susceptible to frequent CRUD operations
430IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'ix_rooms_room_id')
431 DROP INDEX ix_rooms_room_id ON rooms;
432GO
433CREATE NONCLUSTERED INDEX ix_rooms_room_id ON rooms(room_id);
434GO
435
436-- 3. Useful when querying using WHERE keyword and this table is not susceptible to frequent CRUD operations
437IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'ix_rooms_room_type')
438 DROP INDEX ix_rooms_room_type ON rooms;
439GO
440CREATE NONCLUSTERED INDEX ix_rooms_room_type ON rooms(room_type);
441GO
442
443-- 4. Querying for name and surname would be probably most frequent (surname alone could be unreliable)
444IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'ix_client_name_surname')
445 DROP INDEX ix_client_name_surname ON clients;
446GO
447CREATE NONCLUSTERED INDEX ix_client_name_surname ON clients(first_name, surname);
448GO
449
450-- 5. Quicker lookup e.g. when confirming a booking or querying whether some room is free
451IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'ix_bookings_booking_status')
452 DROP INDEX ix_bookings_booking_status ON bookings;
453GO
454CREATE NONCLUSTERED INDEX ix_bookings_booking_status ON bookings(booking_status_desc);
455GO
456
457-- ======================= TASK #4 - queries =======================
458
459-- 1. Displays the number of meeting and seminar rooms (each category separately) reserved each day - please include only days where there is any reservation
460SELECT b1.reservation_start_date, SUM(r1.room_id) as 'Meeting'
461FROM bookings b1
462JOIN rooms r1 ON b1.room_id = r1.room_id
463WHERE r1.room_type = 'meeting' AND r1.is_reserved = 1
464GROUP BY b1.reservation_start_date
465
466SELECT b1.reservation_start_date, SUM(r1.room_id) AS 'Seminar rooms'
467FROM bookings b1
468JOIN rooms r1 ON b1.room_id = r1.room_id
469WHERE r1.room_type = 'seminar' AND r1.is_reserved = 1
470GROUP BY b1.reservation_start_date
471
472-- 2. Displays the clients that actually booked anything, ordered by the number of total booked room-days
473SELECT c1.first_name as Name, c1.surname as Surname, SUM(DATEDIFF(DAY, b1.reservation_start_date, b1.reservation_end_date) + 1) as 'Room-days'
474FROM clients c1
475INNER JOIN bookings b1 ON b1.client_id = c1.client_id
476GROUP BY c1.first_name, c1.surname
477ORDER BY 'Room-days' DESC
478
479-- 3. Displays the clients ordered by their total number of unconfirmed room-days reservations
480SELECT c1.first_name as Name, c1.surname as Surname, SUM(DATEDIFF(DAY, b1.reservation_start_date, b1.reservation_end_date) + 1) as 'Room-days'
481FROM clients c1
482INNER JOIN bookings b1 ON b1.client_id = c1.client_id
483WHERE b1.booking_status_desc = 'pending'
484GROUP BY c1.first_name, c1.surname
485ORDER BY 'Room-days' DESC
486
487-- 4. Finds the nearest date with no reservations at all. Remember that ‘’Mermaid’’ is closed on Sundays,
488DECLARE @current_date DATE = CONVERT(date, SYSDATETIME())
489DECLARE @start_date DATE;
490DECLARE @end_date DATE;
491DECLARE @my_cursor CURSOR;
492BEGIN
493 SET @my_cursor = CURSOR FOR
494 SELECT reservation_start_date, reservation_end_date
495 FROM bookings
496
497 OPEN @my_cursor
498 FETCH NEXT FROM @my_cursor
499 INTO @start_date, @end_date
500
501 WHILE @@FETCH_STATUS = 0
502 BEGIN
503 WHILE @current_date BETWEEN @start_date AND @end_date
504 BEGIN
505 SET @current_date = DATEADD(day, 1, @current_date);
506 -- if Sunday - omit it, so add 1 more day
507 IF (DATENAME(WEEKDAY, @current_date) = 'Sunday')
508 BEGIN
509 SET @current_date = DATEADD(day, 1, @current_date)
510 END
511 END;
512 FETCH NEXT FROM @my_cursor
513 INTO @start_date, @end_date
514 END;
515
516 CLOSE @my_cursor ;
517 DEALLOCATE @my_cursor;
518 PRINT @current_date
519 PRINT DATENAME(WEEKDAY, @current_date)
520END;
521
522-- 5. displays five least popular rooms
523SELECT TOP 5 room_id AS 'Room number', COUNT(room_id) AS 'Occurrence'
524FROM bookings b1
525GROUP BY room_id
526ORDER BY 'Occurrence' ASC
527
528-- ======================= TASK #5 - stored procedure to confirm booking (input: booking_id) =======================
529IF EXISTS (
530SELECT * FROM INFORMATION_SCHEMA.ROUTINES
531WHERE SPECIFIC_SCHEMA = N'dbo'
532 AND SPECIFIC_NAME = N'usp_confirm_booking'
533 AND ROUTINE_TYPE = N'PROCEDURE'
534)
535DROP PROCEDURE dbo.usp_confirm_booking
536GO
537
538CREATE PROCEDURE dbo.usp_confirm_booking
539 @tmp_id int = 0
540AS
541DECLARE @start_date DATE;
542DECLARE @end_date DATE;
543DECLARE @room INT;
544DECLARE @booking INT;
545DECLARE @booking_status VARCHAR;
546DECLARE @day_time VARCHAR;
547DECLARE @my_cursor CURSOR;
548
549DECLARE @tmp_room INT;
550DECLARE @tmp_start_date DATE;
551DECLARE @tmp_end_date DATE;
552DECLARE @tmp_day_time VARCHAR;
553DECLARE @tmp_booking_status VARCHAR
554
555BEGIN
556 SET @my_cursor = CURSOR FOR
557 SELECT booking_id, room_id, booking_status_desc, reservation_start_date, reservation_end_date, day_time
558 FROM bookings
559
560 OPEN @my_cursor
561 FETCH NEXT FROM @my_cursor
562 INTO @booking, @room, @booking_status, @start_date, @end_date, @day_time
563
564 SELECT @tmp_room = room_id FROM bookings WHERE booking_id = @tmp_id ;
565 SELECT @tmp_start_date = reservation_start_date FROM bookings WHERE booking_id = @tmp_id ;
566 SELECT @tmp_end_date = reservation_end_date FROM bookings WHERE booking_id = @tmp_id ;
567 SELECT @tmp_day_time = day_time FROM bookings WHERE booking_id = @tmp_id ;
568 SELECT @tmp_booking_status = booking_status_desc FROM bookings WHERE booking_id = @tmp_id ;
569
570 WHILE @@FETCH_STATUS = 0
571 BEGIN
572 BEGIN
573 IF (@tmp_room = @room
574 AND @tmp_day_time = @day_time
575 AND @tmp_end_date >= @start_date
576 AND @tmp_start_date <= @end_date
577 AND @booking_status = 'p')
578 BEGIN
579 UPDATE bookings
580 SET booking_status_desc = 'canceled'
581 WHERE @room = room_id
582
583 UPDATE bookings
584 SET booking_status_desc = 'confirmed'
585 WHERE @tmp_id = booking_id;
586
587 PRINT 'booking_id:' + CONVERT(VARCHAR(10), @booking)
588 PRINT 'room_id:' + CONVERT(VARCHAR(10), @room)
589
590 END
591 ELSE
592 -- PRINT 'booking_id:' + CONVERT(VARCHAR(100), @booking)
593 -- PRINT 'tmp_room:' + CONVERT(VARCHAR(100), @tmp_room)
594 -- PRINT 'room:' + CONVERT(VARCHAR(100), @room)
595 -- PRINT 'tmp_day_time:' + CONVERT(VARCHAR(100), @tmp_day_time)
596 -- PRINT 'day_time:' + CONVERT(VARCHAR(100), @day_time)
597 -- PRINT 'tmp_start_date:' + CONVERT(VARCHAR(100), @tmp_start_date)
598 -- PRINT 'start_date:' + CONVERT(VARCHAR(100), @start_date)
599 -- PRINT 'tmp_end_date:' + CONVERT(VARCHAR(100), @tmp_end_date)
600 -- PRINT 'tmp_end_date:' + CONVERT(VARCHAR(100), @end_date)
601 -- PRINT 'booking_status:' + CONVERT(VARCHAR(100), @booking_status)
602 -- PRINT '+++++++++++++++'
603 RAISERROR('Booking is already confirmed', 16, 1);
604 END;
605 FETCH NEXT FROM @my_cursor
606 INTO @booking, @room, @booking_status, @start_date, @end_date, @day_time
607 END;
608
609 CLOSE @my_cursor ;
610 DEALLOCATE @my_cursor;
611END;
612
613-- execute the stored procedure
614EXECUTE dbo.usp_confirm_booking 9
615GO
616
617select * from bookings;