· 6 years ago · Apr 30, 2019, 05:03 PM
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
121INSERT INTO ROOM_TYPE(NAME) VALUES("2D");
122INSERT INTO ROOM_TYPE(NAME) VALUES("3D");
123
124CREATE TABLE IF NOT EXISTS ROOM(
125 ID TINYINT NOT NULL AUTO_INCREMENT,
126 CAPACITY SMALLINT NOT NULL,
127 TYPE_ID TINYINT NOT NULL,
128
129 PRIMARY KEY(ID),
130 FOREIGN KEY(TYPE_ID) REFERENCES ROOM_TYPE(ID) on DELETE CASCADE ON UPDATE CASCADE
131);
132
133#################################################################
134CREATE TABLE IF NOT EXISTS MOVIE_ROOM_ASSIGNMENT(
135 ID SMALLINT NOT NULL AUTO_INCREMENT,
136 MOVIE_ID TINYINT NOT NULL,
137 ROOM_ID TINYINT NOT NULL,
138 SHOW_TIME TIME NOT NULL,
139 TICKET_PRICE DECIMAL(8, 2) NOT NULL,
140
141 PRIMARY KEY(ID,MOVIE_ID, ROOM_ID, SHOW_TIME),
142 FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
143 FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ID) on DELETE CASCADE ON UPDATE CASCADE
144);
145
146###Trigger for checking that there no overlapping in room assignment in the same day
147
148delimiter $$
149create trigger CORRECT_ASSIGNMENT_INSERT before insert on MOVIE_ROOM_ASSIGNMENT
150for each row
151begin
152 DECLARE X TIME;
153 DECLARE OVERLAPPED_COUNT INT;
154 SET X = (SELECT DURATION FROM MOVIE WHERE ID = new.MOVIE_ID);
155 SET OVERLAPPED_COUNT = (select count(MOVIE_ID) FROM MOVIE_ROOM_ASSIGNMENT WHERE ROOM_ID = new.ROOM_ID AND
156 SHOW_TIME BETWEEN new.SHOW_TIME AND (new.SHOW_TIME + X));
157 if OVERLAPPED_COUNT > 0 then
158 signal sqlstate '45000';
159 end if;
160end;$$
161delimiter ;
162
163
164
165CREATE TABLE IF NOT EXISTS RESERVATIONS(
166 MOVIE_ROOM_ASSIGNMENT_ID SMALLINT NOT NULL,
167 SEAT_NO TINYINT NOT NULL,
168 RESERVATION_TIME TIMESTAMP NOT NULL,
169
170 PRIMARY KEY(MOVIE_ROOM_ASSIGNMENT_ID, SEAT_NO),
171 FOREIGN KEY(MOVIE_ROOM_ASSIGNMENT_ID) REFERENCES MOVIE_ROOM_ASSIGNMENT(ID) on DELETE CASCADE ON UPDATE CASCADE
172
173);
174
175CREATE TABLE IF NOT EXISTS STATISTICS(
176 MOVIE VARCHAR(50) NOT NULL,
177 NO_OF_VIEWERS INT NOT NULL,
178 TOTAL_REVENUE DECIMAL (8,2) NOT NULL,
179 PRIMARY KEY (MOVIE)
180
181);
182
183insert into CATEGORY(NAME) VALUES ("Drama");
184insert into CATEGORY(NAME) VALUES ("Action");
185insert into CATEGORY(NAME) VALUES ("Comedy");
186insert into CATEGORY(NAME) VALUES ("Art");
187
188INSERT INTO USER_ROLE(ROLE) VALUES("Staff");
189INSERT INTO USER_ROLE(ROLE) VALUES("Admin");
190
191insert into USER VALUES(1, "abcd@gmail.com","BOSS", "HOSS",SHA1("1234"),"01115660036", "ELrasafa",2);
192insert into MOVIE (NAME, DURATION, ENTRY_DATE, EXIT_DATE) values ("Inception", '03:00:00', '2019-07-01', '2019-08-01');
193insert into MOVIE (NAME, DURATION, ENTRY_DATE, EXIT_DATE) values ("12 years of Slave", '03:00:00', '2019-07-01', '2019-08-01');
194insert into CATEGORIES_OF_MOVIES values(1, 1);
195insert into CATEGORIES_OF_MOVIES values(2, 3);
196
197insert into ROOM values (1, 10, 1);
198insert into MOVIE_ROOM_ASSIGNMENT values (1,1,1,'08:00:00',40.0);