CinemaDB/Faza2A: DDLCinemaDB.sql

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