UE5Mn0Yd

1DROP SCHEMA   CINEMA;
2
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    STREET            VARCHAR(50) NOT NULL,
29    CITY              VARCHAR(50) NOT NULL,
30    COUNTRY           VARCHAR(90) NOT NULL,
31    ROLE_ID           TINYINT NOT NULL,
32
33    PRIMARY KEY(ID),
34    UNIQUE(EMAIL),
35    FOREIGN KEY(ROLE_ID) REFERENCES USER_ROLE(ID) on DELETE CASCADE ON UPDATE CASCADE
36
37);
38
39###########################################################
40
41CREATE TABLE IF NOT EXISTS MOVIE_CATEGORY (
42    ID          TINYINT NOT NULL AUTO_INCREMENT,
43    CATEGORY    VARCHAR(15) NOT NULL,
44
45    PRIMARY KEY(ID)
46);
47
48
49
50CREATE TABLE IF NOT EXISTS MOVIE (
51   ID        	TINYINT NOT NULL AUTO_INCREMENT,
52   NAME      	VARCHAR(50) NOT NULL,
53   DURATION  	TIME NOT NULL,
54   ENTRY_DATE 	DATE NOT NULL,
55   EXIT_DATE    DATE NOT NULL,
56   PRIMARY KEY(ID)
57)
58
59# Event used to delete all the old data in the shopping order table.
60
61CREATE EVENT remove_movies
62ON SCHEDULE EVERY 1 DAY
63DO
64	DELETE FROM MOVIE WHERE EXIT_DATE = (SELECT DATE(NOW()));