DatabaseCreation: DDL.sql

File DDL.sql, 15.4 KB (added by 231004, 3 weeks 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
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
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'))
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
103-- ══════════════════════════════════════════════════
104-- LEVEL 2
105-- ══════════════════════════════════════════════════
106
107CREATE TABLE Model
108(
109 id SERIAL PRIMARY KEY,
110 model VARCHAR(255) NOT NULL,
111 brand_id INT NOT NULL,
112 year year_limits NOT NULL,
113
114 CONSTRAINT fk_model_brand FOREIGN KEY (brand_id)
115 REFERENCES Brand (id)
116 ON DELETE RESTRICT
117 ON UPDATE CASCADE
118);
119
120CREATE TABLE EquipmentPackage
121(
122 id SERIAL PRIMARY KEY,
123 name VARCHAR(255) NOT NULL UNIQUE,
124 price price_range NOT NULL,
125 description VARCHAR(255) NOT NULL,
126 is_default BOOLEAN NOT NULL DEFAULT FALSE,
127 equipment_type_id INT NOT NULL,
128
129 CONSTRAINT fk_equipmentPackage_equipmentType FOREIGN KEY (equipment_type_id)
130 REFERENCES EquipmentType (id)
131 ON DELETE RESTRICT
132 ON UPDATE CASCADE
133);
134
135-- ══════════════════════════════════════════════════
136-- LEVEL 3
137-- ══════════════════════════════════════════════════
138
139CREATE TABLE Address
140(
141 id SERIAL PRIMARY KEY,
142 country VARCHAR(255) NOT NULL,
143 city VARCHAR(255) NOT NULL,
144 postal_code INT NOT NULL,
145 street VARCHAR(255) NOT NULL,
146 building_number INT NOT NULL,
147 entry_number INT,
148 apartment_number INT,
149 customer_id INT,
150 factory_id INT,
151
152 CONSTRAINT fk_address_customer FOREIGN KEY (customer_id)
153 REFERENCES Customer (id)
154 ON DELETE RESTRICT
155 ON UPDATE CASCADE,
156
157 CONSTRAINT fk_address_factory FOREIGN KEY (factory_id)
158 REFERENCES Factory (id)
159 ON DELETE RESTRICT
160 ON UPDATE CASCADE,
161
162 CONSTRAINT chk_address_owner
163 CHECK ((customer_id IS NOT NULL AND factory_id IS NULL) or (customer_id IS NULL AND factory_id IS NOT NULL))
164);
165
166CREATE TABLE Vehicle
167(
168 vin VARCHAR(17) PRIMARY KEY,
169 model_id INT NOT NULL,
170 status_id INT NOT NULL,
171 vehicle_type_id INT NOT NULL,
172 color VARCHAR(255) DEFAULT 'Silver',
173 price price_range NOT NULL,
174 production_year year_limits NOT NULL,
175 engine_number VARCHAR(20) NOT NULL UNIQUE,
176
177 CONSTRAINT fk_vehicle_model FOREIGN KEY (model_id)
178 REFERENCES Model (id)
179 ON DELETE RESTRICT
180 ON UPDATE CASCADE,
181 CONSTRAINT fk_vehicle_status FOREIGN KEY (status_id)
182 REFERENCES Status (id)
183 ON DELETE RESTRICT
184 ON UPDATE CASCADE,
185 CONSTRAINT fk_vehicle_vehicleType FOREIGN KEY (vehicle_type_id)
186 REFERENCES VehicleType (id)
187 ON DELETE RESTRICT
188 ON UPDATE CASCADE,
189 CONSTRAINT fk_vehicle_engine FOREIGN KEY (engine_number)
190 REFERENCES ENGINE (engineNumber)
191 ON DELETE RESTRICT
192 ON UPDATE RESTRICT
193);
194
195CREATE TABLE PackageElement
196(
197 id SERIAL PRIMARY KEY,
198 name VARCHAR(255) NOT NULL,
199 price price_range NOT NULL,
200 package_id INT NOT NULL,
201
202 CONSTRAINT fk_packageElement_equipmentPackage FOREIGN KEY (package_id)
203 REFERENCES EquipmentPackage (id)
204 ON DELETE CASCADE
205 ON UPDATE CASCADE
206);
207
208-- ══════════════════════════════════════════════════
209-- LEVEL 4
210-- ══════════════════════════════════════════════════
211
212CREATE TABLE Production
213(
214 id SERIAL PRIMARY KEY,
215 factory_id INT NOT NULL,
216 vin VARCHAR(17) NOT NULL UNIQUE,
217 status VARCHAR(255) NOT NULL DEFAULT 'Scheduled' CHECK (status IN
218 ('Scheduled', 'In Progress', 'Quality Check',
219 'Completed')),
220
221 CONSTRAINT fk_production_factory FOREIGN KEY (factory_id)
222 REFERENCES Factory (id)
223 ON DELETE RESTRICT
224 ON UPDATE CASCADE,
225 CONSTRAINT fk_production_vehicle FOREIGN KEY (vin)
226 REFERENCES Vehicle (vin)
227 ON DELETE RESTRICT
228 ON UPDATE RESTRICT
229);
230
231CREATE TABLE Configuration
232(
233 id SERIAL PRIMARY KEY,
234 vin VARCHAR(17) NOT NULL,
235 description VARCHAR(255) NOT NULL,
236 total_price price_range NOT NULL,
237 created_at DATE NOT NULL DEFAULT CURRENT_DATE,
238 customer_id INT DEFAULT NULL,
239
240 CONSTRAINT fk_configuration_vehicle FOREIGN KEY (vin)
241 REFERENCES Vehicle (vin)
242 ON DELETE RESTRICT
243 ON UPDATE RESTRICT,
244 CONSTRAINT fk_configuration_customer FOREIGN KEY (customer_id)
245 REFERENCES Customer (id)
246 ON DELETE SET NULL
247 ON UPDATE CASCADE
248);
249
250CREATE TABLE TestDrive
251(
252 id SERIAL PRIMARY KEY,
253 date DATE NOT NULL DEFAULT CURRENT_DATE,
254 time_start TIMESTAMP NOT NULL,
255 time_end TIMESTAMP NOT NULL,
256 customer_id INT NOT NULL,
257 vin VARCHAR(17) NOT NULL,
258 result VARCHAR(255) CHECK (result IN ('Interested', 'Not Interested', 'Follow-Up')),
259
260 CONSTRAINT fk_testDrive_customer FOREIGN KEY (customer_id)
261 REFERENCES Customer (id)
262 ON DELETE RESTRICT
263 ON UPDATE CASCADE,
264 CONSTRAINT fk_testDrive_vehicle FOREIGN KEY (vin)
265 REFERENCES Vehicle (vin)
266 ON DELETE RESTRICT
267 ON UPDATE RESTRICT,
268 CONSTRAINT chk_testDrive_times CHECK (time_end > time_start)
269);
270
271
272
273-- ══════════════════════════════════════════════════
274-- LEVEL 5 — DEPEND ON LEVEL 4
275-- ══════════════════════════════════════════════════
276
277CREATE TABLE ConfigurationPackage
278(
279 id SERIAL PRIMARY KEY,
280 configuration_id INT NOT NULL,
281 package_id INT NOT NULL,
282
283 CONSTRAINT fk_confPack_conf FOREIGN KEY (configuration_id)
284 REFERENCES Configuration (id)
285 ON DELETE RESTRICT
286 ON UPDATE CASCADE,
287 CONSTRAINT fk_confPack_pack FOREIGN KEY (package_id)
288 REFERENCES EquipmentPackage (id)
289 ON DELETE RESTRICT
290 ON UPDATE CASCADE,
291 CONSTRAINT unique_confPack UNIQUE (configuration_id, package_id)
292);
293
294CREATE TABLE "Order"
295(
296 id SERIAL PRIMARY KEY,
297 customer_id INT NOT NULL,
298 configuration_id INT NOT NULL,
299 employee_id INT NOT NULL,
300 date DATE NOT NULL DEFAULT CURRENT_DATE,
301 status VARCHAR(255) NOT NULL DEFAULT 'Pending' CHECK
302 (status IN ('Pending', 'Confirmed', 'Canceled', 'Completed')),
303
304 CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
305 REFERENCES Customer (id)
306 ON DELETE RESTRICT
307 ON UPDATE CASCADE,
308 CONSTRAINT fk_order_configuration FOREIGN KEY (configuration_id)
309 REFERENCES Configuration (id)
310 ON DELETE RESTRICT
311 ON UPDATE CASCADE,
312 CONSTRAINT fk_order_employee FOREIGN KEY (employee_id)
313 REFERENCES Employee (id)
314 ON DELETE RESTRICT
315 ON UPDATE CASCADE
316);
317
318-- ══════════════════════════════════════════════════
319-- LEVEL 6 — DEPEND ON LEVEL 5
320-- ══════════════════════════════════════════════════
321
322CREATE TABLE ConfigurationElement
323(
324 id SERIAL PRIMARY KEY,
325 package_element_id INT NOT NULL,
326 configuration_package_id INT NOT NULL,
327
328 CONSTRAINT fk_confEl_packEl FOREIGN KEY (package_element_id)
329 REFERENCES PackageElement (id)
330 ON DELETE RESTRICT
331 ON UPDATE CASCADE,
332 CONSTRAINT fk_confEl_confPack FOREIGN KEY (configuration_package_id)
333 REFERENCES ConfigurationPackage (id)
334 ON DELETE CASCADE
335 ON UPDATE CASCADE,
336 CONSTRAINT unique_confEl UNIQUE (package_element_id, configuration_package_id)
337);
338
339CREATE TABLE Contract
340(
341 id SERIAL PRIMARY KEY,
342 employee_id INT NOT NULL,
343 notes VARCHAR(255),
344 date DATE NOT NULL DEFAULT CURRENT_DATE,
345 type VARCHAR(255) NOT NULL CHECK (type IN ('Standard', 'Finance', 'Fleet')),
346 order_id INT NOT NULL UNIQUE,
347 customer_id INT NOT NULL,
348 vin VARCHAR(17) NOT NULL UNIQUE,
349
350 CONSTRAINT fk_contract_employee FOREIGN KEY (employee_id)
351 REFERENCES Employee (id)
352 ON DELETE RESTRICT
353 ON UPDATE CASCADE,
354 CONSTRAINT fk_contract_order FOREIGN KEY (order_id)
355 REFERENCES "Order" (id)
356 ON DELETE RESTRICT
357 ON UPDATE CASCADE,
358 CONSTRAINT fk_contract_customer FOREIGN KEY (customer_id)
359 REFERENCES Customer (id)
360 ON DELETE RESTRICT
361 ON UPDATE CASCADE,
362 CONSTRAINT fk_contract_vehicle FOREIGN KEY (vin)
363 REFERENCES Vehicle (vin)
364 ON DELETE RESTRICT
365 ON UPDATE RESTRICT
366);
367
368-- ══════════════════════════════════════════════════
369-- LEVEL 7 — DEPEND ON LEVEL 6
370-- ══════════════════════════════════════════════════
371
372CREATE TABLE Sale
373(
374 id SERIAL PRIMARY KEY,
375 date DATE NOT NULL DEFAULT CURRENT_DATE,
376 employee_id INT NOT NULL,
377 contract_id INT NOT NULL UNIQUE,
378 customer_id INT NOT NULL,
379
380 CONSTRAINT fk_sale_employee FOREIGN KEY (employee_id)
381 REFERENCES Employee (id)
382 ON DELETE RESTRICT
383 ON UPDATE CASCADE,
384 CONSTRAINT fk_sale_contract FOREIGN KEY (contract_id)
385 REFERENCES Contract (id)
386 ON DELETE RESTRICT
387 ON UPDATE CASCADE,
388 CONSTRAINT fk_sale_customer FOREIGN KEY (customer_id)
389 REFERENCES Customer (id)
390 ON DELETE RESTRICT
391 ON UPDATE CASCADE
392);
393
394-- ══════════════════════════════════════════════════
395-- LEVEL 8 — DEPEND ON LEVEL 7
396-- ══════════════════════════════════════════════════
397
398CREATE TABLE Payment
399(
400 id SERIAL PRIMARY KEY,
401 type VARCHAR(255) NOT NULL CHECK (type IN ('Cash', 'Installment', 'Leasing')),
402 amount price_range NOT NULL,
403 sale_id INT NOT NULL UNIQUE,
404
405 CONSTRAINT fk_payment_sale FOREIGN KEY (sale_id)
406 REFERENCES Sale (id)
407 ON DELETE RESTRICT
408 ON UPDATE CASCADE
409
410);
411
412-- ══════════════════════════════════════════════════
413-- LEVEL 9 — DEPEND ON LEVEL 8
414-- ══════════════════════════════════════════════════
415
416CREATE TABLE Discount
417(
418 id SERIAL PRIMARY KEY,
419 percentage SMALLINT NOT NULL CHECK (percentage BETWEEN 1 AND 100),
420 payment_id INT NOT NULL,
421 employee_id INT NOT NULL,
422
423 CONSTRAINT fk_discount_payment FOREIGN KEY (payment_id)
424 REFERENCES Payment (id)
425 ON DELETE CASCADE
426 ON UPDATE CASCADE,
427 CONSTRAINT fk_discount_employee FOREIGN KEY (employee_id)
428 REFERENCES Employee (id)
429 ON DELETE RESTRICT
430 ON UPDATE CASCADE
431);