· 7 years ago · Oct 04, 2018, 09:44 PM
1/*Unnið af:
2Alexander Guðmundsson alexanderg17
3DanÃel Már Guðmundsson danielg17
4Páll Guðbrandsson pallg17*/
5/*1. Create a trigger on the Bookingstable thatthrowsadescriptive errorif the given seatnumber does not existin the corresponding venue.*/
6DROP TRIGGER IF EXISTS checkSeats ON Bookings;
7DROP FUNCTION IF EXISTS checkSeats();
8
9CREATE FUNCTION checkSeats()
10RETURNS TRIGGER
11AS
12$$
13BEGIN
14 IF(new.seat_id > ( --if the seat is bigger then what the venue can hold
15 SELECT DISTINCT V.number_of_seats FROM Bookings B
16 INNER JOIN EventSchedules S ON B.schedule_ID = S.id
17 INNER JOIN venues V ON S.venue_id = V.id
18 WHERE new.schedule_ID = B.schedule_ID)) THEN
19
20 RAISE EXCEPTION 'Seat does not exis in this venue' USING ERRCODE = '45000';
21 END IF;
22
23 IF(new.seat_id < 1) THEN --if the seat number is below 1
24 RAISE EXCEPTION 'Seat number must be positive' USING ERRCODE = '45000';
25 END IF;
26
27 RETURN NEW;
28END;
29$$
30LANGUAGE plpgsql;
31CREATE TRIGGER checkSeats
32BEFORE INSERT ON Bookings
33FOR EACH ROW EXECUTE PROCEDURE checkSeats();
34
35/*2. Create a trigger on the EventSchedules table that makes sure that (i) eachvenue can only be booked once a day,and (ii) each event can only be scheduled once a day.
36If those rules are violated the trigger should throwa descriptive error and cancelthe insertion.*/
37DROP TRIGGER IF EXISTS checkDoubleBookings ON EventSchedules;
38DROP FUNCTION IF EXISTS checkDoubleBookings();
39
40CREATE FUNCTION checkDoubleBookings()
41RETURNS TRIGGER
42AS
43$$
44BEGIN
45 IF (0 != ( --If the select query returns a count bigger than 0 then someone has already booked
46 SELECT COUNT(*) FROM EventSchedules --the venue that day
47 WHERE DATE(new.event_time) IN(
48 SELECT DATE(event_time) FROM EventSchedules
49 WHERE venue_id = new.venue_id))) THEN
50 RAISE EXCEPTION 'Venue already booked for chosen day' USING ERRCODE = '45000';
51 END IF;
52
53 IF (0 != ( --If the select query returns a count bigger than 0 the someone has aldreay booked
54 SELECT COUNT(*) FROM EventSchedules
55 WHERE DATE(new.event_time) IN( --that event on that day
56 SELECT DATE(event_time) FROM EventSchedules
57 WHERE event_id = new.event_id))) THEN
58 RAISE EXCEPTION 'Event already scheduled this day' USING ERRCODE = '45000';
59 END IF;
60
61 RETURN NEW;
62END;
63$$
64LANGUAGE plpgsql;
65
66CREATE TRIGGER checkDoubleBookings
67BEFORE INSERT ON EventSchedules
68FOR EACH ROW EXECUTE PROCEDURE checkDoubleBookings();
69
70/*3. Create a function fGetNextSeatAvailable that takes a scheduleIDas input parameter and returnsthe next available seat numberon that event. */
71CREATE OR REPLACE FUNCTION fGetNextSeatAvailable(in NscheduleID int, OUT starting int)
72AS
73$$
74BEGIN
75 starting := 1;
76
77 WHILE (SeatsRemainingting <= (SELECT V.number_of_seats FROM EventSchedules E INNER JOIN venues V ON E.venue_id = V.id WHERE E.id = NscheduleID))
78 LOOP
79 IF EXISTS(SELECT * FROM Bookings WHERE schedule_ID = NscheduleID AND seat_id = starting) THEN --it loops through until it finds an empty seat
80 -- SEAT IS BOOKED
81 ELSE
82 EXIT;
83 END IF;
84
85 starting := starting + 1;
86 END LOOP;
87END;
88$$
89LANGUAGE plpgsql;
90
91/*4. Create a function fGetNumberOfFreeSeats that takes a schedule IDas input parameter and returns the current number of free seats on that scheduled event.*/
92CREATE OR REPLACE FUNCTION fGetNumberOfFreeSeats(in Input int, OUT NoFS int) --NoFS = number of free seats
93AS
94$$
95BEGIN
96 NoFS :=(SELECT v.number_of_seats FROM Venues v JOIN Eventschedules e ON e.venue_id = v.id WHERE e.id = Input) - (SELECT number_of_bookedseats FROM Eventschedules WHERE id = Input);
97END;
98$$
99LANGUAGE plpgsql;
100
101/*5. Create a trigger on the Bookings table that maintains the number_of_bookedSeats counterin the EventSchedulestable.
102This counter shows how many seats have been booked at that given eventat each time.*/
103DROP TRIGGER IF EXISTS maintainBookedSeats ON Bookings;
104DROP FUNCTION IF EXISTS maintainBookedSeats();
105
106CREATE FUNCTION maintainBookedSeats()
107RETURNS TRIGGER
108AS
109$$
110BEGIN
111
112 IF (TG_OP = 'DELETE') THEN --if you delete a booking lower the number by 1
113 UPDATE EventSchedules
114 SET number_of_bookedseats = number_of_bookedseats - 1
115 WHERE id = old.schedule_id;
116
117 RETURN OLD;
118 END IF;
119
120 IF (TG_OP = 'INSERT') THEN --if you insert a booking raise the number by 1
121 UPDATE EventSchedules
122 SET number_of_bookedseats = number_of_bookedseats + 1
123 WHERE id = new.schedule_id;
124
125 RETURN NEW;
126 END IF;
127
128END;
129$$
130LANGUAGE plpgsql;
131
132CREATE TRIGGER maintainBookedSeats
133BEFORE INSERT OR DELETE ON Bookings
134FOR EACH ROW EXECUTE PROCEDURE maintainBookedSeats();
135/*6. Create a function fFindConsecutiveSeatsthat takes as input parameters a scheduled event ID and the
136number of consecutive seats it should find. The function returns the first (lowest) seat number where there
137are sufficiently many free seats in a row. If nosequenceof sufficiently many adjacent free seats exists,
138the function should throw a descriptive error.*/
139DROP FUNCTION IF EXISTS fFindConsecutiveSeats(IN SE_eventID INT, IN numSeats INT, OUT FseatID INT);
140
141CREATE OR REPLACE FUNCTION fFindConsecutiveSeats(IN SE_eventID INT, IN numSeats INT, OUT FseatID INT)
142AS
143$$
144DECLARE
145 i INTEGER := 1;
146 counter INTEGER := 0;
147 totalNumSeats INTEGER := 0;
148BEGIN
149 FseatID := 1;
150 totalNumSeats := (SELECT V.number_of_seats --this variable keeps track of how many seats the venue holds
151 FROM EventSchedules ES
152 INNER JOIN Venues V ON ES.venue_ID = V.ID
153 WHERE ES.ID = SE_eventID);
154
155
156 LOOP
157 EXIT WHEN i = totalNumSeats OR counter = numSeats; --stops the loop when it´s reach the max number the venue holds or found seats
158 IF(0 = (
159 SELECT COUNT(*) FROM Bookings B
160 INNER JOIN EventSchedules ES ON B.schedule_ID = ES.ID
161 WHERE ES.ID = SE_eventID
162 AND B.seat_id = i)) THEN
163 -- Seat is avaliable
164 counter := counter + 1;
165 ELSE
166 counter := 0;
167 END IF;
168 i = i + 1;
169 END LOOP;
170 IF (counter < numSeats) THEN --if it reached the end without finding seats raise exception
171 RAISE EXCEPTION 'Requested number of seast are not avaliable in a row' USING ERRCODE = '45000';
172 ELSIF (counter = numSeats) THEN --if it found seats return the first seat that was found
173 FseatID := (i - numSeats);
174 END IF;
175END
176$$
177LANGUAGE plpgsql;
178/*7. Create a procedure, fBookManySeats that takes 4 input parameters
179(scheduleID, customer ssn, first seat number and how many seats it should book)
180and makes a booking for the given number of seats,
181starting on the first seat number providedand
182booking consecutive seats to the given customer.*/
183CREATE OR REPLACE FUNCTION fBookManySeats(IN SchID INT, IN userSSN VARCHAR, IN firstSeat INT, IN numSeats INT)
184RETURNS void
185AS
186$$
187DECLARE
188-- VARS
189 i INTEGER := 0;
190BEGIN
191
192 LOOP
193 EXIT WHEN i=numSeats;
194 INSERT INTO Bookings(schedule_ID,seat_id,people_ssn)
195 VALUES(SchID,(firstSeat+i), userSSN);
196 i = i + 1;
197 END LOOP;
198END
199$$
200LANGUAGE plpgsql;
201
202/*8. Create a procedure, fFindAndBookSeats that takes number of consecutive seats that should be booked, the schedule IDand customer ssn.
203This procedure books the next available seat row with given amount of seats in a row to the given schedule to the given person.
204If seat row is not found, a descriptive errorshould be throwndescribing the problem.*/
205CREATE OR REPLACE FUNCTION fFindAndBookSeats(IN numSeats INT, IN schID INT, IN userSSN varchar)
206RETURNS void
207AS
208$$
209BEGIN
210
211SELECT fBookManySeats(schID,userSSN,(SELECT fFindConsecutiveSeats(schId,numSeats)), numSeats); --uses 6 and 7 to book many seats
212
213END
214$$
215LANGUAGE plpgsql;
216/*9. Create a viewvGetShowList that returns a list of schedules that have not already passed.
217The list should show IDof schedule, IDof event, time of event, name of event, name of venue, total number of seats and how many seats are still available.*/
218DROP VIEW IF EXISTS viewvGetShowList;
219
220CREATE OR REPLACE VIEW viewvGetShowList (
221 ESID,
222 ESEVENTID,
223 ESEVENT_time,
224 ENAME,
225 VNAME,
226 Vnumber_of_seats,
227 SeatsRemaining)
228AS
229SELECT ES.ID, ES.event_ID, ES.event_time, E.name, V.name, V.number_of_seats, (V.number_of_seats - ES.number_of_bookedseats) as SeatsRemaining
230FROM EventSchedules ES
231INNER JOIN Venues V ON V.ID = ES.venue_ID
232INNER JOIN Events E ON E.ID = ES.event_ID
233WHERE DATE(ES.event_time) > DATE(CURRENT_TIMESTAMP);
234
235/*10. Create a view vListOfVipPeople that shows ssn, name and email of all people that have booked every event that has been scheduled inthe current year. */
236Alex i landsliðið
237
238DROP VIEW IF EXISTS vListOfVipPeople;
239
240CREATE OR REPLACE VIEW vListOfVipPeople (
241 PSSN,
242 Pname,
243 Pemail)
244AS
245SELECT P.SSN,
246 P.name,
247 P.email
248FROM People P
249INNER JOIN Bookings B ON P.SSN = B.people_SSN
250INNER JOIN EventSchedules ES ON B.schedule_id = ES.ID
251GROUP BY P.SSN, P.name, P.email
252HAVING COUNT(DISTINCT ES.event_ID) = (SELECT COUNT(E.ID) FROM Events E);