CinemaDB/Faza2A: DDL_2A.txt

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