DMLiDDL: DDL2.sql

File DDL2.sql, 15.7 KB (added by 231004, 4 days ago)
Line 
1DROP SCHEMA car_dealership CASCADE;
2CREATE SCHEMA car_dealership;
3
4
5-- ══════════════════════════════════════════════════
6-- LEVEL 0 — DOMAINS
7-- ══════════════════════════════════════════════════
8SET search_path TO car_dealership;
9
10CREATE DOMAIN email_type AS VARCHAR(255)
11 CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
12
13CREATE DOMAIN phone_type AS VARCHAR(20)
14 CHECK (VALUE ~ '^\+?[0-9]{7,15}$');
15
16CREATE DOMAIN year_limits as INT
17 CHECK (VALUE BETWEEN 1995 AND EXTRACT(YEAR FROM CURRENT_DATE));
18
19CREATE DOMAIN price_range AS NUMERIC(10, 2)
20 CHECK (VALUE >= 0);
21
22-- ══════════════════════════════════════════════════
23-- LEVEL 1 — NO DEPENDENCIES
24-- ══════════════════════════════════════════════════
25CREATE TABLE Brand
26(
27 id SERIAL PRIMARY KEY,
28 brand VARCHAR(255) NOT NULL UNIQUE
29);
30
31CREATE TABLE EngineType
32(
33 id SERIAL PRIMARY KEY,
34 type VARCHAR(255) NOT NULL DEFAULT 'Petrol'
35 CHECK (type IN ('Petrol', 'Diesel', 'Electric', 'Hybrid'))
36);
37
38CREATE TABLE Engine
39(
40 engineNumber VARCHAR(20) PRIMARY KEY,
41 horsepower INT NOT NULL CHECK (horsepower BETWEEN 50 AND 730),
42 engineTypeId INT NOT NULL,
43
44 CONSTRAINT fk_engine_engineType FOREIGN KEY (engineTypeId)
45 REFERENCES EngineType (id)
46 ON DELETE RESTRICT
47 ON UPDATE CASCADE
48);
49
50CREATE TABLE VehicleType
51(
52 id SERIAL PRIMARY KEY,
53 type VARCHAR(255) NOT NULL UNIQUE
54);
55
56CREATE TABLE Status
57(
58 id SERIAL PRIMARY KEY,
59 status VARCHAR(255) NOT NULL CHECK (status IN
60 ('In Stock', 'Ordered', 'In Production', 'Reserved', 'Sold',
61 'In Transit')) UNIQUE
62);
63
64CREATE TABLE Factory
65(
66 id SERIAL PRIMARY KEY,
67 name VARCHAR(255) NOT NULL UNIQUE
68);
69
70CREATE TABLE EquipmentType
71(
72 id SERIAL PRIMARY KEY,
73 name VARCHAR(255) NOT NULL UNIQUE
74);
75
76CREATE TABLE Customer
77(
78 id SERIAL PRIMARY KEY,
79 first_name VARCHAR(255) NOT NULL,
80 last_name VARCHAR(255) NOT NULL,
81 email email_type NOT NULL UNIQUE,
82 phone phone_type NOT NULL UNIQUE,
83 created_at DATE NOT NULL DEFAULT CURRENT_DATE
84);
85
86CREATE TABLE Employee
87(
88 id SERIAL PRIMARY KEY,
89 position VARCHAR(255) NOT NULL CHECK (position IN ('Salesperson', 'Manager', 'Finance', 'Admin')),
90 first_name VARCHAR(255) NOT NULL,
91 last_name VARCHAR(255) NOT NULL,
92 email car_dealership.email_type NOT NULL UNIQUE,
93 phone car_dealership.phone_type NOT NULL UNIQUE,
94 hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
95 manager_id INT DEFAULT NULL,
96
97 CONSTRAINT fk_employee_manager FOREIGN KEY (manager_id)
98 REFERENCES Employee (id)
99 ON DELETE SET NULL
100 ON UPDATE CASCADE,
101
102 CONSTRAINT chk_employee_not_own_manager CHECK (manager_id IS NULL OR manager_id <> id)
103);
104
105-- ══════════════════════════════════════════════════
106-- LEVEL 2
107-- ══════════════════════════════════════════════════
108
109CREATE TABLE Model
110(
111 id SERIAL PRIMARY KEY,
112 model VARCHAR(255) NOT NULL,
113 brand_id INT NOT NULL,
114 year year_limits NOT NULL,
115
116 CONSTRAINT fk_model_brand FOREIGN KEY (brand_id)
117 REFERENCES Brand (id)
118 ON DELETE RESTRICT
119 ON UPDATE CASCADE,
120
121 CONSTRAINT uq_model_brand_year UNIQUE (brand_id, model, year)
122);
123
124CREATE TABLE EquipmentPackage
125(
126 id SERIAL PRIMARY KEY,
127 name VARCHAR(255) NOT NULL UNIQUE,
128 price price_range NOT NULL,
129 description VARCHAR(255) NOT NULL,
130 is_default BOOLEAN NOT NULL DEFAULT FALSE,
131 equipment_type_id INT NOT NULL,
132
133 CONSTRAINT fk_equipmentPackage_equipmentType FOREIGN KEY (equipment_type_id)
134 REFERENCES EquipmentType (id)
135 ON DELETE RESTRICT
136 ON UPDATE CASCADE
137);
138
139-- ══════════════════════════════════════════════════
140-- LEVEL 3
141-- ══════════════════════════════════════════════════
142
143CREATE TABLE Address
144(
145 id SERIAL PRIMARY KEY,
146 country VARCHAR(255) NOT NULL,
147 city VARCHAR(255) NOT NULL,
148 postal_code INT NOT NULL,
149 street VARCHAR(255) NOT NULL,
150 building_number INT NOT NULL,
151 entry_number INT,
152 apartment_number INT,
153 customer_id INT,
154 factory_id INT,
155
156 CONSTRAINT fk_address_customer FOREIGN KEY (customer_id)
157 REFERENCES Customer (id)
158 ON DELETE RESTRICT
159 ON UPDATE CASCADE,
160
161 CONSTRAINT fk_address_factory FOREIGN KEY (factory_id)
162 REFERENCES Factory (id)
163 ON DELETE RESTRICT
164 ON UPDATE CASCADE,
165
166 CONSTRAINT chk_address_owner
167 CHECK ((customer_id IS NOT NULL AND factory_id IS NULL) or (customer_id IS NULL AND factory_id IS NOT NULL))
168);
169
170CREATE TABLE Vehicle
171(
172 vin VARCHAR(17) PRIMARY KEY,
173 model_id INT NOT NULL,
174 status_id INT NOT NULL,
175 vehicle_type_id INT NOT NULL,
176 color VARCHAR(255) DEFAULT 'Silver',
177 price price_range NOT NULL,
178 production_year year_limits NOT NULL,
179 engine_number VARCHAR(20) NOT NULL UNIQUE,
180
181 CONSTRAINT fk_vehicle_model FOREIGN KEY (model_id)
182 REFERENCES Model (id)
183 ON DELETE RESTRICT
184 ON UPDATE CASCADE,
185 CONSTRAINT fk_vehicle_status FOREIGN KEY (status_id)
186 REFERENCES Status (id)
187 ON DELETE RESTRICT
188 ON UPDATE CASCADE,
189 CONSTRAINT fk_vehicle_vehicleType FOREIGN KEY (vehicle_type_id)
190 REFERENCES VehicleType (id)
191 ON DELETE RESTRICT
192 ON UPDATE CASCADE,
193 CONSTRAINT fk_vehicle_engine FOREIGN KEY (engine_number)
194 REFERENCES ENGINE (engineNumber)
195 ON DELETE RESTRICT
196 ON UPDATE RESTRICT
197);
198
199CREATE TABLE PackageElement
200(
201 id SERIAL PRIMARY KEY,
202 name VARCHAR(255) NOT NULL,
203 price price_range NOT NULL,
204 package_id INT NOT NULL,
205
206 CONSTRAINT fk_packageElement_equipmentPackage FOREIGN KEY (package_id)
207 REFERENCES EquipmentPackage (id)
208 ON DELETE CASCADE
209 ON UPDATE CASCADE
210);
211
212-- ══════════════════════════════════════════════════
213-- LEVEL 4
214-- ══════════════════════════════════════════════════
215
216CREATE TABLE Production
217(
218 id SERIAL PRIMARY KEY,
219 factory_id INT NOT NULL,
220 vin VARCHAR(17) NOT NULL UNIQUE,
221 status VARCHAR(255) NOT NULL DEFAULT 'Scheduled' CHECK (status IN
222 ('Scheduled', 'In Progress', 'Quality Check',
223 'Completed')),
224
225 CONSTRAINT fk_production_factory FOREIGN KEY (factory_id)
226 REFERENCES Factory (id)
227 ON DELETE RESTRICT
228 ON UPDATE CASCADE,
229 CONSTRAINT fk_production_vehicle FOREIGN KEY (vin)
230 REFERENCES Vehicle (vin)
231 ON DELETE RESTRICT
232 ON UPDATE RESTRICT
233);
234
235CREATE TABLE Configuration
236(
237 id SERIAL PRIMARY KEY,
238 vin VARCHAR(17) NOT NULL,
239 description VARCHAR(255) NOT NULL,
240 total_price price_range NOT NULL,
241 created_at DATE NOT NULL DEFAULT CURRENT_DATE,
242 customer_id INT DEFAULT NULL,
243
244 CONSTRAINT fk_configuration_vehicle FOREIGN KEY (vin)
245 REFERENCES Vehicle (vin)
246 ON DELETE RESTRICT
247 ON UPDATE RESTRICT,
248 CONSTRAINT fk_configuration_customer FOREIGN KEY (customer_id)
249 REFERENCES Customer (id)
250 ON DELETE SET NULL
251 ON UPDATE CASCADE
252);
253
254CREATE TABLE TestDrive
255(
256 id SERIAL PRIMARY KEY,
257 date DATE NOT NULL DEFAULT CURRENT_DATE,
258 time_start TIMESTAMP NOT NULL,
259 time_end TIMESTAMP NOT NULL,
260 customer_id INT NOT NULL,
261 vin VARCHAR(17) NOT NULL,
262 result VARCHAR(255) CHECK (result IN ('Interested', 'Not Interested', 'Follow-Up')),
263
264 CONSTRAINT fk_testDrive_customer FOREIGN KEY (customer_id)
265 REFERENCES Customer (id)
266 ON DELETE RESTRICT
267 ON UPDATE CASCADE,
268 CONSTRAINT fk_testDrive_vehicle FOREIGN KEY (vin)
269 REFERENCES Vehicle (vin)
270 ON DELETE RESTRICT
271 ON UPDATE RESTRICT,
272 CONSTRAINT chk_testDrive_times CHECK (time_end > time_start)
273);
274
275
276
277-- ══════════════════════════════════════════════════
278-- LEVEL 5 — DEPEND ON LEVEL 4
279-- ══════════════════════════════════════════════════
280
281CREATE TABLE ConfigurationPackage
282(
283 id SERIAL PRIMARY KEY,
284 configuration_id INT NOT NULL,
285 package_id INT NOT NULL,
286
287 CONSTRAINT fk_confPack_conf FOREIGN KEY (configuration_id)
288 REFERENCES Configuration (id)
289 ON DELETE RESTRICT
290 ON UPDATE CASCADE,
291 CONSTRAINT fk_confPack_pack FOREIGN KEY (package_id)
292 REFERENCES EquipmentPackage (id)
293 ON DELETE RESTRICT
294 ON UPDATE CASCADE,
295 CONSTRAINT unique_confPack UNIQUE (configuration_id, package_id)
296);
297
298CREATE TABLE "Order"
299(
300 id SERIAL PRIMARY KEY,
301 customer_id INT NOT NULL,
302 configuration_id INT NOT NULL,
303 employee_id INT NOT NULL,
304 date DATE NOT NULL DEFAULT CURRENT_DATE,
305 status VARCHAR(255) NOT NULL DEFAULT 'Pending' CHECK
306 (status IN ('Pending', 'Confirmed', 'Canceled', 'Completed')),
307
308 CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
309 REFERENCES Customer (id)
310 ON DELETE RESTRICT
311 ON UPDATE CASCADE,
312 CONSTRAINT fk_order_configuration FOREIGN KEY (configuration_id)
313 REFERENCES Configuration (id)
314 ON DELETE RESTRICT
315 ON UPDATE CASCADE,
316 CONSTRAINT fk_order_employee FOREIGN KEY (employee_id)
317 REFERENCES Employee (id)
318 ON DELETE RESTRICT
319 ON UPDATE CASCADE
320);
321
322-- ══════════════════════════════════════════════════
323-- LEVEL 6 — DEPEND ON LEVEL 5
324-- ══════════════════════════════════════════════════
325
326CREATE TABLE ConfigurationElement
327(
328 id SERIAL PRIMARY KEY,
329 package_element_id INT NOT NULL,
330 configuration_package_id INT NOT NULL,
331
332 CONSTRAINT fk_confEl_packEl FOREIGN KEY (package_element_id)
333 REFERENCES PackageElement (id)
334 ON DELETE RESTRICT
335 ON UPDATE CASCADE,
336 CONSTRAINT fk_confEl_confPack FOREIGN KEY (configuration_package_id)
337 REFERENCES ConfigurationPackage (id)
338 ON DELETE CASCADE
339 ON UPDATE CASCADE,
340 CONSTRAINT unique_confEl UNIQUE (package_element_id, configuration_package_id)
341);
342
343CREATE TABLE Contract
344(
345 id SERIAL PRIMARY KEY,
346 employee_id INT NOT NULL,
347 notes VARCHAR(255),
348 date DATE NOT NULL DEFAULT CURRENT_DATE,
349 type VARCHAR(255) NOT NULL CHECK (type IN ('Standard', 'Finance', 'Fleet')),
350 order_id INT NOT NULL UNIQUE,
351 customer_id INT NOT NULL,
352 vin VARCHAR(17) NOT NULL UNIQUE,
353
354 CONSTRAINT fk_contract_employee FOREIGN KEY (employee_id)
355 REFERENCES Employee (id)
356 ON DELETE RESTRICT
357 ON UPDATE CASCADE,
358 CONSTRAINT fk_contract_order FOREIGN KEY (order_id)
359 REFERENCES "Order" (id)
360 ON DELETE RESTRICT
361 ON UPDATE CASCADE,
362 CONSTRAINT fk_contract_customer FOREIGN KEY (customer_id)
363 REFERENCES Customer (id)
364 ON DELETE RESTRICT
365 ON UPDATE CASCADE,
366 CONSTRAINT fk_contract_vehicle FOREIGN KEY (vin)
367 REFERENCES Vehicle (vin)
368 ON DELETE RESTRICT
369 ON UPDATE RESTRICT
370);
371
372-- ══════════════════════════════════════════════════
373-- LEVEL 7 — DEPEND ON LEVEL 6
374-- ══════════════════════════════════════════════════
375
376CREATE TABLE Sale
377(
378 id SERIAL PRIMARY KEY,
379 date DATE NOT NULL DEFAULT CURRENT_DATE,
380 employee_id INT NOT NULL,
381 contract_id INT NOT NULL UNIQUE,
382 customer_id INT NOT NULL,
383
384 CONSTRAINT fk_sale_employee FOREIGN KEY (employee_id)
385 REFERENCES Employee (id)
386 ON DELETE RESTRICT
387 ON UPDATE CASCADE,
388 CONSTRAINT fk_sale_contract FOREIGN KEY (contract_id)
389 REFERENCES Contract (id)
390 ON DELETE RESTRICT
391 ON UPDATE CASCADE,
392 CONSTRAINT fk_sale_customer FOREIGN KEY (customer_id)
393 REFERENCES Customer (id)
394 ON DELETE RESTRICT
395 ON UPDATE CASCADE
396);
397
398-- ══════════════════════════════════════════════════
399-- LEVEL 8 — DEPEND ON LEVEL 7
400-- ══════════════════════════════════════════════════
401
402CREATE TABLE Payment
403(
404 id SERIAL PRIMARY KEY,
405 type VARCHAR(255) NOT NULL CHECK (type IN ('Cash', 'Installment', 'Leasing')),
406 amount price_range NOT NULL,
407 sale_id INT NOT NULL UNIQUE,
408
409 CONSTRAINT fk_payment_sale FOREIGN KEY (sale_id)
410 REFERENCES Sale (id)
411 ON DELETE RESTRICT
412 ON UPDATE CASCADE
413
414);
415
416-- ══════════════════════════════════════════════════
417-- LEVEL 9 — DEPEND ON LEVEL 8
418-- ══════════════════════════════════════════════════
419
420CREATE TABLE Discount
421(
422 id SERIAL PRIMARY KEY,
423 percentage SMALLINT NOT NULL CHECK (percentage BETWEEN 1 AND 100),
424 payment_id INT NOT NULL,
425 employee_id INT NOT NULL,
426
427 CONSTRAINT fk_discount_payment FOREIGN KEY (payment_id)
428 REFERENCES Payment (id)
429 ON DELETE CASCADE
430 ON UPDATE CASCADE,
431 CONSTRAINT fk_discount_employee FOREIGN KEY (employee_id)
432 REFERENCES Employee (id)
433 ON DELETE RESTRICT
434 ON UPDATE CASCADE,
435
436 CONSTRAINT uq_discount_payment UNIQUE (payment_id)
437);