· 7 years ago · Sep 26, 2018, 10:42 PM
1--Dæmi 1
2/*Create a trigger on the Bookings table that throws a
3descriptive error if the given seat number does
4not exist in the corresponding venue.
5*/
6select '1. Seat exist Trigger' as now_checking;
7
8begin transaction;
9
10select 'This should give a "Seat does not exist"
11 error' as wrong_seating;
12
13insert into Bookings (schedule_id, seat_id, people_ssn)
14values (1, 25, "1207169649");
15
16rollback;
17
18select 'This should work' as right_seating;
19
20insert into Bookings (schedule_id, seat_id, people_ssn)
21values (2, 105, "2211452089");
22
23rollback;
24
25--Dæmi 2
26/*
27Create a trigger on the EventSchedules table that
28makes sure that (i) each venue can only
29be booked once a day, and (ii) each
30event can only be scheduled once a day.
31If those rules
32are violated the trigger should throw a
33descriptive error and cancel the insertion.
34*/
35select '2. Two events on the same day' as now_checking;
36
37begin transaction;
38
39select 'This should give an error because a venue is booked
40 twice the same day' as wrongly_booked;
41
42insert into EventSchedules (id, event_id, venue_id, event_time
43 event_price, number_of_bookedSeats);
44values(5, 3, 1, 2017-11-18 20:00:00, 9000, 0);
45
46select 'This should give an error because 2 events are booked
47 the same day' as wrongly_booked;
48
49insert into EventSchedules (id, event_id, venue_id, event_time
50 event_price, number_of_bookedSeats);
51values(6, 1, 2, 2017-11-19 20:00:00, 10000, 10);
52
53rollback;
54
55--Dæmi 3
56/*
57Create a function fGetNextSeatAvailable that takes
58a schedule ID as input parameter and
59returns the next available seat number on that
60event.
61*/
62select '3. Next seat available' as now_checking;
63
64begin transaction;
65
66select 'This should return the next available seat at a
67 specific event' as next_seat;
68
69insert into EventSchedules (id, event_id, venue_id, event_time
70 event_price, number_of_bookedSeats);
71values (6, 3, 3, CURRENT_DATE, 8000, 0);
72
73select fGetNextSeatAvailable(6);
74
75rollback;
76
77--Dæmi 4
78/*
79Create a function fGetNumberOfFreeSeats that
80takes a schedule ID as input parameter
81and returns the current number of free seats on
82that scheduled event.
83*/
84select '4. Getting number of seats' as now_checking;
85
86begin transaction;
87
88select 'This should return the amount of avalable seats
89left in a specific Schedule event' as seats_left;
90
91insert into EventSchedules (id, event_id, venue_id, event_time, event_price, number_of_bookedSeats);
92values (5, 1 , CURRENT_DATE, 8000, 0);
93
94select fGetNumberOfFreeSeats(6);
95
96rollback;
97
98--Dæmi 5
99/*
100Create a trigger on the Bookings table
101that maintains the number_of_bookedSeats
102counter in the EventSchedules table. This
103counter shows how many seats have been
104booked at that given event at each time.
105*/
106select '5. Maintaining the number of booked seats' as now_checking;
107
108begin transaction;
109
110select 'This should return 5 since the new event has 5 booked
111 seats' as booked_seats;
112
113INSERT INTO EventSchedules(id, event_id, venue_id, event_time, event_price, number_of_bookedSeats)
114VALUES(9, 1, 1, CURRENT_DATE, 10000, 5);
115
116rollback;
117
118--Dæmi 6
119/*
120Create a function fFindConsecutiveSeats that takes
121as input parameters a scheduled event
122ID and the number of consecutive seats it
123should find. The function returns the first
124(lowest) seat number where there are sufficiently
125many free seats in a row. If no sequence
126of sufficiently many adjacent free seats
127exists, the function should throw a descriptive
128error.
129*/
130select '6. Find consecutive seats' as now_checking;
131
132begin transaction;
133
134select 'This should return an error because the venue can only
135 have 20 consecutive seats because it only has that many'
136 as consecutive_seats;
137
138insert into EventSchedules (id, event_id, venue_id, event_time,
139 event_price, number_of_bookedSeats);
140values(12, 1, 6, CURRENT_DATE, 14000, 0);
141
142select fFindConsecutiveSeats(12, 21);
143
144select 'This should work because the venue can take 20 people
145 and it has no seats booked' as consecutive_seats;
146
147select fFindConsecutiveSeats(12, 19);
148
149rollback;
150
151--Dæmi 7
152
153--Dæmi 8
154/*
155Create a procedure, fFindAndBookSeats that takes
156number of consecutive seats that
157should be booked, the schedule ID and
158customer ssn. This procedure books the next
159available seat row with given amount of seats
160in a row to the given schedule to the given
161person. If seat row is not found, a
162descriptive error should be thrown describing the problem.
163*/
164select 'finding and booking seats' as now_checking;
165
166begin transaction;
167
168select 'This should display an error due to not enough
169 consecutive seats to book' as no_consecutive_seats;
170
171INSERT INTO EventSchedules(id, event_id, venue_id, event_time, event_price, number_of_bookedSeats)
172VALUES(16, 1, 6, CURRENT_DATE, 2500, 0);
173
174INSERT INTO Bookings(schedule_id, seat_id, people_ssn)
175VALUES(16, 12, '2507489819');
176
177SELECT fFindAndBookSeats(15, 16, '2507489819');
178
179rollback;