nNpHcjnF

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
16INSERT INTO USER_ROLE(ROLE) VALUES("Staff");
17INSERT INTO USER_ROLE(ROLE) VALUES("Admin");
18
19# User table, It uses SHA1 hashing algorithm for the password.
20
21CREATE TABLE IF NOT EXISTS USER (
22    ID                INT NOT NULL AUTO_INCREMENT,
23    EMAIL             VARCHAR(50) NOT NULL,
24    FIRST_NAME        VARCHAR(50) NOT NULL,
25    LAST_NAME         VARCHAR(50) NOT NULL,
26    PASSWORD_SHA1     VARCHAR(50) NOT NULL,
27    PHONE_NUMBER      VARCHAR(20) NOT NULL,
28    ADDRESS           VARCHAR(100) NOT NULL,
29    ROLE_ID           TINYINT NOT NULL,
30
31    PRIMARY KEY(ID),
32    UNIQUE(EMAIL),
33    FOREIGN KEY(ROLE_ID) REFERENCES USER_ROLE(ID) on DELETE CASCADE ON UPDATE CASCADE
34
35);
36
37###########################################################
38
39CREATE TABLE IF NOT EXISTS CATEGORY (
40    ID          TINYINT NOT NULL AUTO_INCREMENT,
41    NAME    VARCHAR(20) NOT NULL,
42
43    PRIMARY KEY(ID)
44);
45
46
47
48CREATE TABLE IF NOT EXISTS MOVIE (
49   ID        	TINYINT NOT NULL AUTO_INCREMENT,
50   NAME      	VARCHAR(100) NOT NULL,
51   DURATION  	TIME NOT NULL,
52   ENTRY_DATE 	DATE NOT NULL,
53   EXIT_DATE    DATE NOT NULL,
54   PRIMARY KEY(ID),
55   UNIQUE (NAME)
56);
57
58delimiter $$
59create trigger CORRECT_DATA_INSERT before insert on MOVIE
60for each row
61begin
62  if new.EXIT_DATE <= new.ENTRY_DATE OR new.DURATION < 0 then
63    signal sqlstate '45000';
64  end if;
65end;$$
66delimiter ;
67
68
69
70delimiter $$
71create trigger CORRECT_DATA_UPDATE before update on MOVIE
72for each row
73begin
74  if new.EXIT_DATE <= new.ENTRY_DATE OR new.DURATION < 0 then
75    signal sqlstate '45000';
76  end if;
77end;$$
78delimiter ;
79
80CREATE TABLE IF NOT EXISTS CATEGORIES_OF_MOVIES (
81   MOVIE_ID        	TINYINT NOT NULL,
82   CATEGORY_ID          TINYINT NOT NULL,
83  
84   PRIMARY KEY(MOVIE_ID, CATEGORY_ID),
85   FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
86   FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(ID) on DELETE CASCADE ON UPDATE CASCADE
87  
88);
89
90
91CREATE INDEX INDEX_NAME
92ON MOVIE (NAME);
93
94# Event used to delete all the old data in the shopping order table.
95
96CREATE EVENT remove_movies
97ON SCHEDULE EVERY 1 DAY
98DO
99	DELETE FROM MOVIE WHERE EXIT_DATE = (SELECT DATE(NOW()));
100################################################################
101CREATE TABLE IF NOT EXISTS ROOM_TYPE(
102    ID           TINYINT NOT NULL AUTO_INCREMENT,
103    NAME         VARCHAR(15) NOT NULL,
104
105    PRIMARY KEY(ID)
106);
107
108INSERT INTO ROOM_TYPE(NAME) VALUES("2D");
109INSERT INTO ROOM_TYPE(NAME) VALUES("3D");
110
111CREATE TABLE IF NOT EXISTS ROOM(
112     ID   	TINYINT NOT NULL AUTO_INCREMENT,
113     CAPACITY   SMALLINT NOT NULL,
114     TYPE_ID    TINYINT NOT NULL,
115
116     PRIMARY KEY(ID),
117     FOREIGN KEY(TYPE_ID) REFERENCES ROOM_TYPE(ID) on DELETE CASCADE ON UPDATE CASCADE
118);
119
120#################################################################
121CREATE TABLE IF NOT EXISTS MOVIE_ROOM_ASSIGNMENT(
122     ID        		SMALLINT NOT NULL AUTO_INCREMENT,
123     MOVIE_ID   	TINYINT NOT NULL,
124     ROOM_ID    	TINYINT NOT NULL,
125     SHOW_TIME          TIME NOT NULL,
126     TICKET_PRICE       DECIMAL(8, 2) NOT NULL,
127
128     PRIMARY KEY(ID,MOVIE_ID, ROOM_ID, SHOW_TIME),
129     FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
130     FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ID) on DELETE CASCADE ON UPDATE CASCADE
131);
132
133###Trigger for checking that there no overlapping in room assignment in the same day
134
135delimiter $$
136create trigger CORRECT_ASSIGNMENT_INSERT before insert on MOVIE_ROOM_ASSIGNMENT
137for each row
138begin
139   DECLARE X TIME;
140   DECLARE OVERLAPPED_COUNT INT;
141   SET X = (SELECT DURATION FROM MOVIE WHERE ID = new.MOVIE_ID);
142   SET OVERLAPPED_COUNT = (select count(MOVIE.ID) FROM MOVIE_ROOM_ASSIGNMENT WHERE ROOM_ID = new.ROOM_ID AND 
143				SHOW_TIME BETWEEN new.SHOW_TIME  AND (new.SHOW_TIME + X));
144   if OVERLAPPED_COUNT > 0 then
145      signal sqlstate '45000';
146   end if;
147end;$$
148delimiter ;
149
150
151
152CREATE TABLE IF NOT EXISTS RESERVATIONS(
153     MOVIE_ROOM_ASSIGNMENT_ID  	SMALLINT NOT NULL,
154     SEAT_NO 		        TINYINT NOT NULL,	
155     SHOW_TIME                  TIME NOT NULL,
156     TICKET_PRICE               DECIMAL(8, 2) NOT NULL,
157
158     PRIMARY KEY(MOVIE_ROOM_ASSIGNMENT_ID, SEAT_NO),
159     FOREIGN KEY(MOVIE_ROOM_ASSIGNMENT_ID) REFERENCES MOVIE_ROOM_ASSIGNMENT(ID) on DELETE CASCADE ON UPDATE CASCADE
160     
161);
162
163CREATE TABLE IF NOT EXISTS STATISTICS(
164	MOVIE  VARCHAR(50) NOT NULL,
165	NO_OF_VIEWERS INT NOT NULL,
166	TOTAL_REVENUE DECIMAL (8,2) NOT NULL,
167        
168       PRIMARY KEY (MOVIE)
169
170);