forked from UsmanZafar47/Cinexus-Cinema-Management-System
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cinexus.sql
124 lines (107 loc) · 4.22 KB
/
cinexus.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
CREATE DATABASE IF NOT EXISTS Cinexus;
use Cinexus;
DROP TABLE users;
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
cnic VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
role ENUM('Customer', 'Admin', 'Cinema Manager', 'Event Organizer') NOT NULL
);
INSERT into users (username, password, name, cnic, email, role) VALUES
("Usman", "321", "Usman", "12", "[email protected]", "Customer"),
("Rayyan", "123", "Rayyan", "121", "[email protected]", "Cinema Manager");
select * from users;
DROP TABLE movies;
CREATE TABLE movies (
movie_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
duration INT NOT NULL,
rating VARCHAR(50)
);
INSERT INTO movies (title, duration, rating) VALUES ('Avengers', 110, '4');
INSERT INTO movies (title, duration, rating) VALUES ('Avengers EndGame', 100, '3');
INSERT INTO movies (title, duration, rating) VALUES ('Justice League', 90, '1');
select * from movies;
SELECT m.title, c.name AS cinema_name, GROUP_CONCAT(s.showtime SEPARATOR ', ') AS showtimes
FROM movies m
JOIN showtimes s ON m.movie_id = s.movie_id
JOIN cinema c ON s.cinema_id = c.cinema_id ;
SELECT m.title, c.name AS cinema_name, GROUP_CONCAT(s.showtime SEPARATOR ', ') AS showtimes
FROM movies m
JOIN showtimes s ON m.movie_id = s.movie_id
JOIN cinema c ON s.cinema_id = c.cinema_id
GROUP BY m.title, c.name;
DROP TABLE cinema;
CREATE TABLE cinema (
cinema_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL unique,
location VARCHAR(255) NOT NULL,
manager_id int NOT NULL,
seatprice int NOT NULL,
noSeats int NOT NULL,
FOREIGN KEY (manager_id) REFERENCES users(user_id) ON DELETE CASCADE
);
insert into cinema (name, location, manager_id, seatprice, noSeats) values
("Cinepex","Islamabad", 2, 250, 58), ("Megapex","Islamabad", 2, 350, 25);
select * from cinema;
SELECT * FROM cinema WHERE manager_id = 2;
DROP Table showtimes;
CREATE TABLE showtimes (
showtime_id INT AUTO_INCREMENT PRIMARY KEY,
movie_id INT,
cinema_id INT,
showtime DATETIME NOT NULL,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE CASCADE,
FOREIGN KEY (cinema_id) REFERENCES cinema(cinema_id) ON DELETE CASCADE
);
INSERT INTO showtimes (movie_id, cinema_id, showtime) VALUES
(1, 1, '2023-12-10 18:00:00'), (1, 2, '2023-12-10 19:00:00'),
(2, 1, '2023-12-10 20:00:00'), (2, 2, '2023-12-10 21:00:00'),
(3, 1, '2023-12-10 22:00:00'), (3, 2, '2023-12-10 23:00:00');
Select * from showtimes;
DROP Table tickets;
CREATE TABLE tickets (
ticket_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
showtime_id INT,
seatCount INT,
price DECIMAL(10, 2) NOT NULL,
status ENUM('Reserved', 'Booked','Cancelled') NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
FOREIGN KEY (showtime_id) REFERENCES showtimes(showtime_id) ON DELETE CASCADE
);
Select * from tickets;
CREATE TABLE payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE TABLE movie_reviews (
review_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
movie_id INT,
review_text TEXT NOT NULL,
rating INT NOT NULL,
review_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
FOREIGN KEY (movie_id) REFERENCES movies(movie_id) ON DELETE CASCADE
);
ALTER TABLE movie_reviews MODIFY COLUMN rating INT DEFAULT 0;
ALTER TABLE movie_reviews MODIFY COLUMN review_date DATETIME DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE cinema_feedback MODIFY COLUMN feedback_date DATETIME DEFAULT CURRENT_TIMESTAMP;
select* from cinema_feedback
select* from movie_reviews
-- Create the cinema_feedback table
CREATE TABLE cinema_feedback (
feedback_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
cinema_id INT,
feedback_text TEXT NOT NULL,
feedback_date DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
FOREIGN KEY (cinema_id) REFERENCES cinema(cinema_id) ON DELETE CASCADE
);