DatabaseCreation: schema.sql

File schema.sql, 12.6 KB (added by 231123, 11 days ago)

DDL

Line 
1CREATE TABLE Species (
2 species_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
3 name VARCHAR(100) NOT NULL,
4 description VARCHAR(255),
5 PRIMARY KEY (species_id),
6 CONSTRAINT uq_species_name UNIQUE (name)
7);
8
9CREATE TABLE Breed (
10 breed_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
11 name VARCHAR(100) NOT NULL,
12 description VARCHAR(255),
13 species_id INTEGER NOT NULL,
14 PRIMARY KEY (breed_id),
15 CONSTRAINT fk_breed_species
16 FOREIGN KEY (species_id) REFERENCES Species(species_id)
17 ON DELETE RESTRICT ON UPDATE CASCADE
18);
19
20CREATE TABLE Category (
21 category_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
22 name VARCHAR(100) NOT NULL,
23 description VARCHAR(255),
24 PRIMARY KEY (category_id)
25);
26
27CREATE TABLE Room_Type (
28 room_type_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
29 name VARCHAR(100) NOT NULL,
30 description VARCHAR(255),
31 price_per_night NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
32 PRIMARY KEY (room_type_id),
33 CONSTRAINT uq_room_type_name UNIQUE (name)
34);
35
36CREATE TABLE Service (
37 service_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
38 name VARCHAR(100) NOT NULL,
39 description VARCHAR(255),
40 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
41 duration_minutes INTEGER,
42 PRIMARY KEY (service_id),
43 CONSTRAINT uq_service_name UNIQUE (name)
44);
45
46CREATE TABLE Hotel (
47 hotel_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
48 name VARCHAR(100),
49 location VARCHAR(100),
50 PRIMARY KEY (hotel_id)
51);
52
53CREATE TABLE Employee (
54 employee_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
55 first_name VARCHAR(50) NOT NULL,
56 last_name VARCHAR(50) NOT NULL,
57 role VARCHAR(50),
58 phone VARCHAR(20),
59 email VARCHAR(100),
60 hire_date DATE,
61 hotel_id INTEGER NOT NULL DEFAULT -1,
62 PRIMARY KEY (employee_id),
63 CONSTRAINT fk_employee_hotel
64 FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
65 ON DELETE RESTRICT ON UPDATE CASCADE,
66 CONSTRAINT chk_employee_email
67 CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
68 CONSTRAINT chk_employee_phone
69 CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
70);
71
72CREATE TABLE Room (
73 room_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
74 room_number VARCHAR(20) NOT NULL,
75 capacity INTEGER,
76 room_type_id INTEGER NOT NULL,
77 hotel_id INTEGER NOT NULL DEFAULT -1,
78 PRIMARY KEY (room_id),
79 CONSTRAINT fk_room_room_type
80 FOREIGN KEY (room_type_id) REFERENCES Room_Type(room_type_id)
81 ON DELETE RESTRICT ON UPDATE CASCADE,
82 CONSTRAINT fk_room_hotel
83 FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
84 ON DELETE SET DEFAULT ON UPDATE CASCADE,
85 CONSTRAINT uq_room_number UNIQUE (room_number)
86);
87
88CREATE TABLE Date (
89 date DATE NOT NULL,
90 room_id INTEGER NOT NULL,
91 status VARCHAR(20),
92 PRIMARY KEY (date, room_id),
93 CONSTRAINT fk_dates_room
94 FOREIGN KEY (room_id) REFERENCES Room(room_id)
95 ON DELETE RESTRICT ON UPDATE CASCADE
96);
97
98CREATE TABLE Supplier (
99 supplier_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
100 name VARCHAR(100) NOT NULL,
101 contact_name VARCHAR(100),
102 phone VARCHAR(20),
103 email VARCHAR(100),
104 address VARCHAR(255),
105 PRIMARY KEY (supplier_id),
106 CONSTRAINT chk_supplier_email
107 CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
108 CONSTRAINT chk_supplier_phone
109 CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
110);
111
112CREATE TABLE Customer (
113 customer_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
114 first_name VARCHAR(50) NOT NULL,
115 last_name VARCHAR(50) NOT NULL,
116 email VARCHAR(100) NOT NULL,
117 phone VARCHAR(20),
118 address VARCHAR(255),
119 registration_date DATE,
120 PRIMARY KEY (customer_id),
121 CONSTRAINT uq_customer_email UNIQUE (email),
122 CONSTRAINT chk_customer_email
123 CHECK (email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
124 CONSTRAINT chk_customer_phone
125 CHECK (phone IS NULL OR phone ~ '^\+?[0-9][0-9\s\-().]{6,18}[0-9]$')
126);
127
128CREATE TABLE Pet (
129 pet_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
130 name VARCHAR(100) NOT NULL,
131 date_of_birth DATE,
132 gender VARCHAR(10),
133 customer_id INTEGER NOT NULL,
134 species_id INTEGER NOT NULL,
135 breed_id INTEGER,
136 PRIMARY KEY (pet_id),
137 CONSTRAINT fk_pet_customer
138 FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
139 ON DELETE CASCADE ON UPDATE CASCADE,
140 CONSTRAINT fk_pet_species
141 FOREIGN KEY (species_id) REFERENCES Species(species_id)
142 ON DELETE RESTRICT ON UPDATE CASCADE,
143 CONSTRAINT fk_pet_breed
144 FOREIGN KEY (breed_id) REFERENCES Breed(breed_id)
145 ON DELETE SET NULL ON UPDATE CASCADE
146);
147
148CREATE TABLE Product (
149 product_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
150 name VARCHAR(100) NOT NULL,
151 description VARCHAR(255),
152 price NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
153 category_id INTEGER NOT NULL,
154 supplier_id INTEGER,
155 PRIMARY KEY (product_id),
156 CONSTRAINT fk_product_category
157 FOREIGN KEY (category_id) REFERENCES Category(category_id)
158 ON DELETE RESTRICT ON UPDATE CASCADE,
159 CONSTRAINT fk_product_supplier
160 FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id)
161 ON DELETE SET NULL ON UPDATE CASCADE
162);
163
164CREATE TABLE "Order" (
165 order_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
166 order_date DATE NOT NULL DEFAULT CURRENT_DATE,
167 status VARCHAR(50),
168 total_amount NUMERIC(10, 2) DEFAULT 0.00,
169 customer_id INTEGER NOT NULL,
170 hotel_id INTEGER NOT NULL,
171 PRIMARY KEY (order_id),
172 CONSTRAINT fk_order_customer
173 FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
174 ON DELETE RESTRICT ON UPDATE CASCADE,
175 CONSTRAINT fk_order_hotel
176 FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
177 ON DELETE SET NULL ON UPDATE CASCADE
178);
179
180CREATE TABLE OrderProduct (
181 orderproduct_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
182 quantity INTEGER NOT NULL DEFAULT 1,
183 unit_price NUMERIC(10, 2) NOT NULL,
184 order_id INTEGER NOT NULL,
185 product_id INTEGER NOT NULL,
186 PRIMARY KEY (orderproduct_id),
187 CONSTRAINT uq_orderproduct_order_product UNIQUE (order_id, product_id),
188 CONSTRAINT fk_orderproduct_order
189 FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
190 ON DELETE CASCADE ON UPDATE CASCADE,
191 CONSTRAINT fk_orderproduct_product
192 FOREIGN KEY (product_id) REFERENCES Product(product_id)
193 ON DELETE RESTRICT ON UPDATE CASCADE
194);
195
196CREATE TABLE Delivery (
197 delivery_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
198 delivered_at VARCHAR(50),
199 status VARCHAR(50),
200 hotel_id INTEGER NOT NULL,
201 purchase_id INTEGER NOT NULL,
202 PRIMARY KEY (delivery_id),
203 CONSTRAINT fk_delivery_hotel
204 FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
205 ON DELETE RESTRICT ON UPDATE CASCADE,
206 CONSTRAINT fk_delivery_orderproduct
207 FOREIGN KEY (purchase_id) REFERENCES OrderProduct(orderproduct_id)
208 ON DELETE RESTRICT ON UPDATE CASCADE
209);
210
211CREATE TABLE Reservation (
212 reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
213 reservation_date DATE NOT NULL DEFAULT CURRENT_DATE,
214 status VARCHAR(50),
215 notes VARCHAR(255),
216 total_cost INTEGER,
217 pet_id INTEGER NOT NULL,
218 employee_id INTEGER,
219 PRIMARY KEY (reservation_id),
220 CONSTRAINT fk_reservation_pet
221 FOREIGN KEY (pet_id) REFERENCES Pet(pet_id)
222 ON DELETE RESTRICT ON UPDATE CASCADE,
223 CONSTRAINT fk_reservation_employee
224 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
225 ON DELETE SET NULL ON UPDATE CASCADE
226);
227
228CREATE TABLE RoomReservation (
229 room_reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
230 check_in_date DATE NOT NULL,
231 check_out_date DATE NOT NULL,
232 reservation_id INTEGER NOT NULL,
233 room_id INTEGER NOT NULL,
234 PRIMARY KEY (room_reservation_id),
235 CONSTRAINT chk_roomres_dates
236 CHECK (check_out_date > check_in_date),
237 CONSTRAINT fk_roomres_room
238 FOREIGN KEY (room_id) REFERENCES Room(room_id)
239 ON DELETE RESTRICT ON UPDATE CASCADE,
240 CONSTRAINT fk_roomres_reservation
241 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
242 ON DELETE CASCADE ON UPDATE CASCADE
243);
244
245CREATE TABLE ServiceReservation (
246 service_reservation_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
247 scheduled_date DATE NOT NULL,
248 scheduled_time TIME,
249 status VARCHAR(50),
250 reservation_id INTEGER NOT NULL,
251 service_id INTEGER NOT NULL,
252 PRIMARY KEY (service_reservation_id),
253 CONSTRAINT fk_svcres_reservation
254 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
255 ON DELETE CASCADE ON UPDATE CASCADE,
256 CONSTRAINT fk_svcres_service
257 FOREIGN KEY (service_id) REFERENCES Service(service_id)
258 ON DELETE RESTRICT ON UPDATE CASCADE
259);
260
261CREATE TABLE Employee_Service (
262 employee_id INTEGER NOT NULL,
263 service_id INTEGER NOT NULL,
264 PRIMARY KEY (employee_id, service_id),
265 CONSTRAINT fk_empservice_employee
266 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
267 ON DELETE SET DEFAULT ON UPDATE CASCADE,
268 CONSTRAINT fk_empservice_service
269 FOREIGN KEY (service_id) REFERENCES Service(service_id)
270 ON DELETE SET DEFAULT ON UPDATE CASCADE
271);
272
273CREATE TABLE Product_Service (
274 product_id INTEGER NOT NULL,
275 service_id INTEGER NOT NULL,
276 PRIMARY KEY (product_id, service_id),
277 CONSTRAINT fk_prodservice_product
278 FOREIGN KEY (product_id) REFERENCES Product(product_id)
279 ON DELETE RESTRICT ON UPDATE CASCADE,
280 CONSTRAINT fk_prodservice_service
281 FOREIGN KEY (service_id) REFERENCES Service(service_id)
282 ON DELETE RESTRICT ON UPDATE CASCADE
283);
284
285CREATE TABLE Payment (
286 payment_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
287 payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
288 amount NUMERIC(10, 2) NOT NULL,
289 payment_method VARCHAR(50),
290 status VARCHAR(50),
291 reservation_id INTEGER,
292 PRIMARY KEY (payment_id),
293 CONSTRAINT chk_payment_source
294 CHECK (reservation_id IS NOT NULL),
295 CONSTRAINT fk_payment_reservation
296 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
297 ON DELETE SET NULL ON UPDATE CASCADE
298);
299
300CREATE TABLE Review (
301 review_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
302 review_date DATE,
303 rating INTEGER CHECK (rating BETWEEN 1 AND 9),
304 comment VARCHAR(255),
305 customer_id INTEGER NOT NULL,
306 hotel_id INTEGER NOT NULL,
307 PRIMARY KEY (review_id),
308 CONSTRAINT fk_review_customer
309 FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
310 ON DELETE SET DEFAULT ON UPDATE CASCADE,
311 CONSTRAINT fk_review_hotel
312 FOREIGN KEY (hotel_id) REFERENCES Hotel(hotel_id)
313 ON DELETE CASCADE ON UPDATE CASCADE
314);
315
316CREATE TABLE MedicalRecord (
317 record_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
318 record_date DATE,
319 status VARCHAR(11),
320 vet_name VARCHAR(50),
321 alergies VARCHAR(255),
322 medications VARCHAR(255),
323 note VARCHAR(255),
324 diagnosis VARCHAR(255),
325 pet_id INTEGER NOT NULL,
326 PRIMARY KEY (record_id),
327 CONSTRAINT fk_medrecord_pet
328 FOREIGN KEY (pet_id) REFERENCES Pet(pet_id)
329 ON DELETE CASCADE ON UPDATE CASCADE
330);
331
332CREATE TABLE PetDelivery (
333 pet_delivery_id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
334 delivery_time TIMESTAMP,
335 status VARCHAR(11),
336 destination VARCHAR(11),
337 reservation_id INTEGER NOT NULL,
338 employee_id INTEGER,
339 PRIMARY KEY (pet_delivery_id),
340 CONSTRAINT fk_petdelivery_reservation
341 FOREIGN KEY (reservation_id) REFERENCES Reservation(reservation_id)
342 ON DELETE RESTRICT ON UPDATE CASCADE,
343 CONSTRAINT fk_petdelivery_employee
344 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
345 ON DELETE SET NULL ON UPDATE CASCADE
346);