CinemaDB/Faza2A: DDL_CinemaDB.sql

File DDL_CinemaDB.sql, 9.4 KB (added by 233280, 5 days ago)
Line 
1CREATE TYPE statusReservation AS ENUM ('PENDING','CONFIRMED','CANCELLED');
2CREATE TYPE statusOrder AS ENUM ('PREPARING','COMPLETED');
3
4CREATE TABLE Genre (
5 genre_id SERIAL PRIMARY KEY,
6 name VARCHAR(255) NOT NULL,
7 description VARCHAR(255)
8);
9
10CREATE TABLE Movie (
11 movie_id SERIAL PRIMARY KEY,
12 title VARCHAR(255) NOT NULL,
13 release_year INT NOT NULL,
14 duration INT NOT NULL,
15 language VARCHAR(255) NOT NULL,
16 description VARCHAR(255)
17);
18
19CREATE TABLE Cinema (
20 cinema_id SERIAL PRIMARY KEY,
21 name VARCHAR(255) NOT NULL,
22 city VARCHAR(255) NOT NULL,
23 address VARCHAR(255) NOT NULL,
24 phone VARCHAR(255) NOT NULL default '123456789',
25 email VARCHAR(255) unique default 'unknown@example.com',
26
27 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
28 CHECK (phone ~ '^\+?[0-9]{7,15}$')
29);
30
31CREATE TABLE Hall (
32 hall_id SERIAL PRIMARY KEY,
33 cinema_id INT NOT NULL,
34 name VARCHAR(255) NOT NULL,
35 capacity INT NOT NULL,
36 FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) on delete restrict on update cascade
37);
38
39
40CREATE TABLE Seat_Type (
41 seat_type_id SERIAL PRIMARY KEY,
42 type VARCHAR(255) NOT NULL DEFAULT 'Standard',
43 price INT NOT NULL
44);
45
46CREATE TABLE Seat (
47 seat_id SERIAL PRIMARY KEY,
48 hall_id INT NOT NULL,
49 seat_type_id INT NOT NULL DEFAULT 1,
50 seat_row INT NOT NULL,
51 seat_number INT NOT NULL,
52 FOREIGN KEY (hall_id) REFERENCES Hall(hall_id) ON DELETE RESTRICT ON UPDATE CASCADE,
53 FOREIGN KEY (seat_type_id) REFERENCES Seat_Type(seat_type_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
54);
55
56CREATE TABLE CinemaUser (
57 user_id SERIAL PRIMARY KEY,
58 first_name VARCHAR(255) default 'Unknown',
59 last_name VARCHAR(255) default 'Unknown',
60 email VARCHAR(255) unique default 'unknown@example.com',
61 phone VARCHAR(255) NOT NULL default '123456789',
62 registration_date DATE DEFAULT CURRENT_DATE,
63
64 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
65 CHECK (phone ~ '^\+?[0-9]{7,15}$')
66);
67
68CREATE TABLE Employee (
69 employee_id SERIAL PRIMARY KEY,
70 cinema_id INT NOT NULL,
71 first_name VARCHAR(255) default 'Unknown',
72 last_name VARCHAR(255) default 'Unknown',
73 phone VARCHAR(255) default '123456789',
74 email VARCHAR(255) unique default 'unknown@example.com',
75 hire_date DATE DEFAULT CURRENT_DATE,
76 FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) on delete restrict on update cascade ,
77
78 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
79 CHECK (phone ~ '^\+?[0-9]{7,15}$')
80);
81
82CREATE TABLE Showtime (
83 showtime_id SERIAL PRIMARY KEY,
84 movie_id INT NOT NULL,
85 hall_id INT NOT NULL,
86 start_time TIMESTAMP(0) NOT NULL,
87 end_time TIMESTAMP(0) NOT NULL,
88 base_price INT NOT NULL,
89 FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) on delete restrict on update cascade ,
90 FOREIGN KEY (hall_id) REFERENCES Hall(hall_id) on delete restrict on update cascade
91);
92
93CREATE TABLE Reservation (
94 reservation_id SERIAL PRIMARY KEY,
95 user_id INT NOT NULL DEFAULT 111111,
96 showtime_id INT NOT NULL,
97 employee_id INT NOT NULL DEFAULT 1,
98 reservation_date DATE DEFAULT CURRENT_DATE,
99 status statusReservation DEFAULT 'PENDING',
100 FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
101 FOREIGN KEY (showtime_id) REFERENCES Showtime(showtime_id) ON DELETE RESTRICT ON UPDATE CASCADE,
102 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
103);
104
105CREATE TABLE Ticket (
106 ticket_id SERIAL PRIMARY KEY,
107 showtime_id INT NOT NULL,
108 seat_id INT NOT NULL,
109 reservation_id INT NOT NULL,
110 price INT NOT NULL,
111 purchase_date DATE DEFAULT CURRENT_DATE,
112 FOREIGN KEY (showtime_id) REFERENCES Showtime(showtime_id) ON DELETE RESTRICT ON UPDATE CASCADE,
113 FOREIGN KEY (seat_id) REFERENCES Seat(seat_id) ON DELETE RESTRICT ON UPDATE CASCADE,
114 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id) ON DELETE RESTRICT ON UPDATE CASCADE
115);
116
117CREATE TABLE ReservationPayment (
118 payment_id SERIAL PRIMARY KEY,
119 user_id INT NOT NULL DEFAULT 111111,
120 reservation_id INT NOT NULL,
121 employee_id INT NOT NULL DEFAULT 1,
122 amount INT NOT NULL,
123 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
124 payment_method VARCHAR(255) NOT NULL,
125 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id) ON DELETE RESTRICT ON UPDATE CASCADE,
126 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
127 FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
128);
129
130CREATE TABLE Review (
131 review_id SERIAL PRIMARY KEY,
132 user_id INT NOT NULL DEFAULT 111111,
133 movie_id INT NOT NULL,
134 rating INT NOT NULL,
135 comment VARCHAR(255),
136 review_date DATE NOT NULL DEFAULT CURRENT_DATE,
137
138 FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
139 FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE RESTRICT ON UPDATE CASCADE
140);
141
142CREATE TABLE Role (
143 role_id SERIAL PRIMARY KEY,
144 role_name VARCHAR(255) NOT NULL,
145 description VARCHAR(255),
146 base_salary INT NOT NULL
147);
148
149CREATE TABLE Employee_Role (
150 employee_id INT NOT NULL,
151 role_id INT NOT NULL,
152 PRIMARY KEY (employee_id, role_id),
153 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE CASCADE ON UPDATE CASCADE,
154 FOREIGN KEY (role_id) REFERENCES Role(role_id) ON DELETE CASCADE ON UPDATE CASCADE
155);
156
157CREATE TABLE Salary (
158 salary_id SERIAL PRIMARY KEY,
159 employee_id INT NOT NULL DEFAULT 1,
160 amount INT NOT NULL,
161 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
162 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
163);
164
165CREATE TABLE Expense (
166 expense_id SERIAL PRIMARY KEY,
167 cinema_id INT NOT NULL,
168 description VARCHAR(255),
169 amount INT NOT NULL,
170 expense_date DATE NOT NULL DEFAULT CURRENT_DATE,
171 FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) ON DELETE RESTRICT ON UPDATE CASCADE
172);
173
174CREATE TABLE CinemaOrder (
175 order_id SERIAL PRIMARY KEY,
176 user_id INT NOT NULL DEFAULT 111111,
177 employee_id INT NOT NULL DEFAULT 1,
178 order_date DATE NOT NULL DEFAULT CURRENT_DATE,
179 total_price INT NOT NULL,
180 status statusOrder NOT NULL DEFAULT 'PREPARING',
181
182 FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
183 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
184);
185
186CREATE TABLE Product (
187 product_id SERIAL PRIMARY KEY,
188 name VARCHAR(255) NOT NULL,
189 price INT NOT NULL,
190 stock_quantity INT NOT NULL
191);
192
193CREATE TABLE Order_Product (
194 order_product_id SERIAL PRIMARY KEY,
195 order_id INT NOT NULL,
196 product_id INT NOT NULL,
197 quantity INT NOT NULL,
198 price_at_order INT NOT NULL,
199 FOREIGN KEY (order_id) REFERENCES CinemaOrder(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
200 FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
201);
202
203CREATE TABLE Supplier (
204 supplier_id SERIAL PRIMARY KEY,
205 name VARCHAR(255) NOT NULL,
206 phone VARCHAR(255) NOT NULL,
207 email VARCHAR(255) NOT NULL,
208 address VARCHAR(255) NOT NULL
209);
210
211CREATE TABLE Supply (
212 supply_id SERIAL PRIMARY KEY,
213 product_id INT NOT NULL,
214 supplier_id INT NOT NULL,
215 expense_id INT NOT NULL,
216 quantity INT NOT NULL,
217 supply_date DATE NOT NULL DEFAULT CURRENT_DATE,
218 price_per_unit INT NOT NULL,
219
220 FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE,
221 FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id) ON DELETE RESTRICT ON UPDATE CASCADE,
222 FOREIGN KEY (expense_id) REFERENCES Expense(expense_id) ON DELETE RESTRICT ON UPDATE CASCADE
223);
224
225CREATE TABLE OrderPayment (
226 orderPayment_id SERIAL PRIMARY KEY,
227 employee_id INT NOT NULL DEFAULT 1,
228 order_id INT NOT NULL,
229 user_id INT NOT NULL DEFAULT 111111,
230 amount INT NOT NULL,
231 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
232 payment_method VARCHAR(255) NOT NULL,
233
234 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
235 FOREIGN KEY (order_id) REFERENCES CinemaOrder(order_id) ON DELETE RESTRICT ON UPDATE CASCADE,
236 FOREIGN KEY (user_id) REFERENCES CinemaUser(user_id) ON DELETE SET DEFAULT ON UPDATE CASCADE
237);
238
239CREATE TABLE PresentationRights (
240 presentationRights_id SERIAL PRIMARY KEY,
241 movie_id INT NOT NULL,
242 cinema_id INT NOT NULL,
243 start_date DATE NOT NULL,
244 end_date DATE NOT NULL,
245 FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE RESTRICT ON UPDATE CASCADE,
246 FOREIGN KEY (cinema_id) REFERENCES Cinema(cinema_id) ON DELETE RESTRICT ON UPDATE CASCADE
247);
248
249CREATE TABLE Genre_Movie (
250 genre_id INT NOT NULL,
251 movie_id INT NOT NULL,
252 PRIMARY KEY (genre_id, movie_id),
253 FOREIGN KEY (genre_id) REFERENCES Genre(genre_id) ON DELETE CASCADE ON UPDATE CASCADE,
254 FOREIGN KEY (movie_id) REFERENCES Movie(movie_id) ON DELETE CASCADE ON UPDATE CASCADE
255);