VnbXQZZc

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(50) 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(15) 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(50) 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
58CREATE TABLE IF NOT EXISTS CATEGORIES_OF_MOVIES (
59   MOVIE_ID        	TINYINT NOT NULL,
60   CATEGORY_ID          TINYINT NOT NULL,
61  
62   PRIMARY KEY(MOVIE_ID, CATEGORY_ID),
63   FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
64   FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(ID) on DELETE CASCADE ON UPDATE CASCADE
65  
66);
67
68
69CREATE INDEX INDEX_NAME
70ON MOVIE (NAME);
71
72# Event used to delete all the old data in the shopping order table.
73
74CREATE EVENT remove_movies
75ON SCHEDULE EVERY 1 DAY
76DO
77	DELETE FROM MOVIE WHERE EXIT_DATE = (SELECT DATE(NOW()));
78################################################################
79CREATE TABLE IF NOT EXISTS ROOM_TYPE(
80    ID           TINYINT NOT NULL AUTO_INCREMENT,
81    NAME         VARCHAR(15) NOT NULL,
82
83    PRIMARY KEY(ID)
84);
85
86INSERT INTO ROOM_TYPE(NAME) VALUES("2D");
87INSERT INTO ROOM_TYPE(NAME) VALUES("3D");
88
89CREATE TABLE IF NOT EXISTS ROOM(
90     ID   	TINYINT NOT NULL AUTO_INCREMENT,
91     CAPACITY   SMALLINT NOT NULL,
92     TYPE_ID    TINYINT NOT NULL,
93
94     PRIMARY KEY(ID),
95     FOREIGN KEY(TYPE_ID) REFERENCES ROOM_TYPE(ID) on DELETE CASCADE ON UPDATE CASCADE
96);
97
98#################################################################
99CREATE TABLE IF NOT EXISTS MOVIE_ROOM_ASSIGNMENT(
100     ID        		SMALLINT NOT NULL AUTO_INCREMENT,
101     MOVIE_ID   	TINYINT NOT NULL,
102     ROOM_ID    	TINYINT NOT NULL,
103     SHOW_TIME          TIME NOT NULL,
104     TICKET_PRICE       DECIMAL(8, 2) NOT NULL,
105
106     PRIMARY KEY(ID,MOVIE_ID, ROOM_ID, SHOW_TIME),
107     FOREIGN KEY(MOVIE_ID) REFERENCES MOVIE(ID) on DELETE CASCADE ON UPDATE CASCADE,
108     FOREIGN KEY(ROOM_ID) REFERENCES ROOM(ID) on DELETE CASCADE ON UPDATE CASCADE
109);
110
111
112CREATE TABLE IF NOT EXISTS RESERVATIONS(
113     MOVIE_ROOM_ASSIGNMENT_ID  	SMALLINT NOT NULL,
114     SEAT_NO 		        TINYINT NOT NULL,	
115     SHOW_TIME                  TIME NOT NULL,
116     TICKET_PRICE               DECIMAL(8, 2) NOT NULL,
117
118     PRIMARY KEY(MOVIE_ROOM_ASSIGNMENT_ID, SEAT_NO),
119     FOREIGN KEY(MOVIE_ROOM_ASSIGNMENT_ID) REFERENCES MOVIE_ROOM_ASSIGNMENT(ID) on DELETE CASCADE ON UPDATE CASCADE
120     
121);
122
123CREATE TABLE IF NOT EXISTS STATISTICS(
124	MOVIE  VARCHAR(50) NOT NULL,
125	NO_OF_VIEWERS INT NOT NULL,
126	TOTAL_REVENUE DECIMAL (8,2) NOT NULL,
127        
128       PRIMARY KEY (MOVIE)
129
130);