· 6 years ago · May 18, 2019, 12:42 AM
1DROP SCHEMA CINEMA;
2SET GLOBAL event_scheduler = ON;
3CREATE SCHEMA CINEMA;
4
5USE CINEMA;
6
7#Users Info
8
9CREATE TABLE IF NOT EXISTS USER_ROLE (
10 ID TINYINT NOT NULL AUTO_INCREMENT,
11 ROLE VARCHAR(15) NOT NULL,
12
13 PRIMARY KEY(ID)
14);
15
16
17
18# User table, It uses SHA1 hashing algorithm for the password.
19
20CREATE TABLE IF NOT EXISTS USER (
21 ID INT NOT NULL AUTO_INCREMENT,
22 EMAIL VARCHAR(50) NOT NULL,
23 FIRST_NAME VARCHAR(50) NOT NULL,
24 LAST_NAME VARCHAR(50) NOT NULL,
25 PASSWORD_SHA1 VARCHAR(50) NOT NULL,
26 PHONE_NUMBER VARCHAR(20) NOT NULL,
27 ADDRESS VARCHAR(100) NOT NULL,
28 ROLE_ID TINYINT NOT NULL,
29
30 PRIMARY KEY(ID),
31 UNIQUE(EMAIL),
32 FOREIGN KEY(ROLE_ID) REFERENCES USER_ROLE(ID) on DELETE CASCADE ON UPDATE CASCADE
33
34);
35
36###########################################################
37
38CREATE TABLE IF NOT EXISTS CATEGORY (
39 ID TINYINT NOT NULL AUTO_INCREMENT,
40 NAME VARCHAR(20) NOT NULL,
41
42 PRIMARY KEY(ID)
43);
44
45
46
47CREATE TABLE IF NOT EXISTS MOVIE (
48 ID TINYINT NOT NULL AUTO_INCREMENT,
49 NAME VARCHAR(100) NOT NULL,
50 DURATION TIME NOT NULL,
51 ENTRY_DATE DATE NOT NULL,
52 EXIT_DATE DATE NOT NULL,
53 PRIMARY KEY(ID),
54 UNIQUE (NAME)
55);
56
57delimiter $$
58create trigger CORRECT_DATA_INSERT before insert on MOVIE
59for each row
60begin
61 if new.EXIT_DATE <= new.ENTRY_DATE OR new.DURATION <= 0 then
62 signal sqlstate '45000';
63 end if;
64end;$$
65delimiter ;
66
67
68
69delimiter $$
70create trigger CORRECT_DATA_UPDATE before update on MOVIE
71for each row
72begin
73 if new.EXIT_DATE <= new.ENTRY_DATE OR new.DURATION <= 0 then
74 signal sqlstate '45000';
75 end if;
76end;$$
77delimiter ;
78
79
80delimiter $$
81CREATE TRIGGER DELETE_CATEGORIES_FOR_MOVIE AFTER DELETE ON MOVIE
82for each row
83begin
84 DECLARE X TINYINT;
85 SET X = (SELECT ID FROM MOVIE WHERE NAME = old.NAME);
86 DELETE FROM CATEGORIES_OF_MOVIES WHERE MOVIE_ID = X;
87end;$$
88delimiter ;
89
90
91CREATE TABLE IF NOT EXISTS CATEGORIES_OF_MOVIES (
92 MOVIE_ID TINYINT NOT NULL,
93 CATEGORY_ID TINYINT NOT NULL,
94
95 PRIMARY KEY(MOVIE_ID, CATEGORY_ID),
96 FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
97 FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(ID) on DELETE CASCADE ON UPDATE CASCADE
98
99);
100
101
102
103
104CREATE INDEX INDEX_NAME
105ON MOVIE (NAME);
106
107# Event used to delete all the movies where exit date has reached
108
109CREATE EVENT remove_movies
110ON SCHEDULE EVERY 1 DAY
111DO
112 DELETE FROM MOVIE WHERE EXIT_DATE = (SELECT DATE(NOW()));
113################################################################
114CREATE TABLE IF NOT EXISTS ROOM_TYPE(
115 ID TINYINT NOT NULL AUTO_INCREMENT,
116 NAME VARCHAR(15) NOT NULL,
117
118 PRIMARY KEY(ID)
119);
120
121
122
123CREATE TABLE IF NOT EXISTS ROOM(
124 ID TINYINT NOT NULL AUTO_INCREMENT,
125 CAPACITY SMALLINT NOT NULL,
126 TYPE_ID TINYINT NOT NULL,
127
128 PRIMARY KEY(ID),
129 FOREIGN KEY(TYPE_ID) REFERENCES ROOM_TYPE(ID) on DELETE CASCADE ON UPDATE CASCADE
130);
131
132#################################################################
133CREATE TABLE IF NOT EXISTS MOVIE_ROOM_ASSIGNMENT(
134 ID SMALLINT NOT NULL AUTO_INCREMENT,
135 MOVIE_ID TINYINT NOT NULL,
136 ROOM_ID TINYINT NOT NULL,
137 SHOW_TIME TIME NOT NULL,
138 TICKET_PRICE DECIMAL(8, 2) NOT NULL,
139
140 PRIMARY KEY(ID,MOVIE_ID, ROOM_ID, SHOW_TIME),
141 FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
142 FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ID) on DELETE CASCADE ON UPDATE CASCADE
143);
144
145###Trigger for checking that there no overlapping in room assignment in the same day
146
147delimiter $$
148create trigger CORRECT_ASSIGNMENT_INSERT before insert on MOVIE_ROOM_ASSIGNMENT
149for each row
150begin
151 DECLARE X TIME;
152 DECLARE OVERLAPPED_COUNT INT;
153 SET X = (SELECT DURATION FROM MOVIE WHERE ID = new.MOVIE_ID);
154 SET OVERLAPPED_COUNT = (select count(MOVIE_ID) FROM MOVIE_ROOM_ASSIGNMENT WHERE ROOM_ID = new.ROOM_ID AND
155 SHOW_TIME BETWEEN new.SHOW_TIME AND (new.SHOW_TIME + X));
156 if OVERLAPPED_COUNT > 0 then
157 signal sqlstate '45000';
158 end if;
159end;$$
160delimiter ;
161
162
163
164CREATE TABLE IF NOT EXISTS RESERVATIONS(
165 MOVIE_ROOM_ASSIGNMENT_ID SMALLINT NOT NULL,
166 SEAT_NO TINYINT NOT NULL,
167 RESERVATION_TIME TIMESTAMP NOT NULL,
168
169 PRIMARY KEY(MOVIE_ROOM_ASSIGNMENT_ID, SEAT_NO),
170 FOREIGN KEY(MOVIE_ROOM_ASSIGNMENT_ID) REFERENCES MOVIE_ROOM_ASSIGNMENT(ID) on DELETE CASCADE ON UPDATE CASCADE
171
172);
173
174
175
176CREATE TABLE IF NOT EXISTS HISTORY(
177 MOVIE_NAME VARCHAR(50) NOT NULL,
178 SHOW_TIME TIME NOT NULL,
179 TICKET_PRICE DECIMAL (8,2) NOT NULL,
180 SHOW_DAY DATE NOT NULL,
181 TOTAL_VIEWS SMALLINT NOT NULL,
182 PRIMARY KEY (MOVIE_NAME, SHOW_TIME, SHOW_DAY)
183
184);
185
186
187delimiter $$
188create trigger ADD_TO_HISTORY before delete on RESERVATIONS
189for each row
190begin
191 DECLARE DMOVIE_NAME varchar(50);
192 DECLARE DSHOW_TIME TIME;
193 DECLARE RESERVATION_DAY DATE;
194 DECLARE VIEW_COUNT INT;
195 DECLARE DTICKET_PRICE DECIMAL (8,2);
196 SET RESERVATION_DAY = (select Date(old.RESERVATION_TIME));
197 SET DMOVIE_NAME = (Select M1.NAME from (Select R1.MOVIE_ROOM_ASSIGNMENT_ID, S1.MOVIE_ID FROM RESERVATIONS as R1 JOIN MOVIE_ROOM_ASSIGNMENT as S1 ON R1.MOVIE_ROOM_ASSIGNMENT_ID = S1.ID) as T12 JOIN MOVIE AS M1 ON T12.MOVIE_ID = M1.ID WHERE MOVIE_ROOM_ASSIGNMENT_ID = old.MOVIE_ROOM_ASSIGNMENT_ID);
198 SET DSHOW_TIME = (Select S1.SHOW_TIME FROM RESERVATIONS as R1 JOIN MOVIE_ROOM_ASSIGNMENT as S1 ON R1.MOVIE_ROOM_ASSIGNMENT_ID = S1.ID WHERE MOVIE_ROOM_ASSIGNMENT_ID = old.MOVIE_ROOM_ASSIGNMENT_ID);
199 SET DTICKET_PRICE = (Select S1.TICKET_PRICE FROM RESERVATIONS as R1 JOIN MOVIE_ROOM_ASSIGNMENT as S1 ON R1.MOVIE_ROOM_ASSIGNMENT_ID = S1.ID WHERE MOVIE_ROOM_ASSIGNMENT_ID = old.MOVIE_ROOM_ASSIGNMENT_ID);
200 SET VIEW_COUNT = (SELECT count(*) FROM HISTORY WHERE MOVIE_NAME = DMOVIE_NAME AND SHOW_TIME = DSHOW_TIME AND SHOW_DAY = RESERVATION_DAY);
201 IF VIEW_COUNT = 0 THEN
202 INSERT INTO HISTORY VALUES (DMOVIE_NAME, DSHOW_TIME, DTICKET_PRICE, RESERVATION_DAY, 1);
203 ELSE
204
205 SET VIEW_COUNT = VIEW_COUNT + 1;
206 UPDATE HISTORY SET TOTAL_VIEWS = VIEW_COUNT WHERE MOVIE_NAME = DMOVIE_NAME AND SHOW_TIME = DSHOW_TIME AND SHOW_DAY = RESERVATION_DAY;
207 END IF;
208end;$$
209delimiter ;
210
211
212CREATE EVENT unbook_seat_automatically
213 ON SCHEDULE
214 EVERY 3 MINUTE
215 COMMENT 'removes reservation entry from its table'
216 DO
217 DELETE from RESERVATIONS WHERE MOVIE_ROOM_ASSIGNMENT_ID IN (Select DISTINCT T12.MOVIE_ROOM_ASSIGNMENT_ID from (Select R1.MOVIE_ROOM_ASSIGNMENT_ID, R1.RESERVATION_TIME, S1.MOVIE_ID, S1.SHOW_TIME FROM RESERVATIONS as R1 JOIN MOVIE_ROOM_ASSIGNMENT as S1 ON R1.MOVIE_ROOM_ASSIGNMENT_ID = S1.ID) as T12 JOIN MOVIE AS M1 ON T12.MOVIE_ID = M1.ID WHERE (TIME(ADDTIME(TIMESTAMP(CURRENT_DATE(), T12.SHOW_TIME), M1.DURATION)) < CURRENT_TIME() AND DATE(ADDTIME(TIMESTAMP(CURRENT_DATE(), T12.SHOW_TIME), M1.DURATION)) <= DATE(T12.RESERVATION_TIME))OR DATE(T12.RESERVATION_TIME) < CURRENT_DATE());
218
219insert into CATEGORY(NAME) VALUES ("Drama");
220insert into CATEGORY(NAME) VALUES ("Action");
221insert into CATEGORY(NAME) VALUES ("Comedy");
222insert into CATEGORY(NAME) VALUES ("Art");
223
224INSERT INTO USER_ROLE(ROLE) VALUES("Staff");
225INSERT INTO USER_ROLE(ROLE) VALUES("Admin");
226
227INSERT INTO ROOM_TYPE(NAME) VALUES("2D");
228INSERT INTO ROOM_TYPE(NAME) VALUES("3D");
229
230insert into USER VALUES(1, "abcd@gmail.com","BOSS", "HOSS",SHA1("1234"),"01115660036", "ELrasafa",2);
231insert into MOVIE (NAME, DURATION, ENTRY_DATE, EXIT_DATE) values ("Inception", '03:00:00', '2019-07-01', '2019-08-01');
232insert into MOVIE (NAME, DURATION, ENTRY_DATE, EXIT_DATE) values ("12 years of Slave", '03:00:00', '2019-07-01', '2019-08-01');
233insert into MOVIE (NAME, DURATION, ENTRY_DATE, EXIT_DATE) values ("coco", '00:01:00', '2019-07-01', '2019-08-01');
234
235insert into CATEGORIES_OF_MOVIES values(1, 1);
236insert into CATEGORIES_OF_MOVIES values(2, 3);
237insert into CATEGORIES_OF_MOVIES values(2, 1);
238
239insert into ROOM values (1, 10, 1);
240insert into ROOM values (2, 10, 1);
241
242insert into MOVIE_ROOM_ASSIGNMENT values (1, 3, 1, "16:00:00", 40);
243
244insert into RESERVATIONS values (1, 1, '2019-05-17 14:00:00');
245insert into RESERVATIONS values(1,23,'2019-05-17 01:00:00');
246
247
248insert into USER (EMAIL, FIRST_NAME, LAST_NAME, PASSWORD_SHA1,PHONE_NUMBER,ADDRESS,ROLE_ID) values ("uone@gmail.com","userDFirstName", "userDLastName",SHA1("1234"),"011111112","addressD",1);
249insert into USER (EMAIL, FIRST_NAME, LAST_NAME, PASSWORD_SHA1,PHONE_NUMBER,ADDRESS,ROLE_ID) values ("utwo@gmail.com","userDFirstName", "userDLastName",SHA1("1234"),"011111112","addressD",2);