nvSuNuU0

1DROP TABLE IF EXISTS addresses CASCADE;
2CREATE TABLE addresses(
3    id SERIAL PRIMARY KEY,
4    name VARCHAR(100) NOT NULL
5);
6
7
8DROP TABLE IF EXISTS categories CASCADE;
9CREATE TABLE categories(
10    id SERIAL PRIMARY KEY,
11    name VARCHAR(10) NOT NULL
12);
13
14
15DROP TABLE IF EXISTS clients CASCADE;
16CREATE TABLE clients(
17    id SERIAL PRIMARY KEY,
18    full_name VARCHAR(50) NOT NULL,
19    phone_number VARCHAR(20) NOT NULL
20);
21
22
23DROP TABLE IF EXISTS drivers CASCADE;
24CREATE TABLE drivers(
25    id SERIAL PRIMARY KEY,
26    first_name VARCHAR(30) NOT NULL,
27    last_name VARCHAR(30) NOT NULL,
28    age INT NOT NULL CHECK (age > 0),
29    rating NUMERIC(2) DEFAULT 5.5
30);
31
32DROP TABLE IF EXISTS cars CASCADE;
33CREATE TABLE cars(
34    id SERIAL PRIMARY KEY,
35    make VARCHAR(20) NOT NULL,
36    model VARCHAR(20),
37    year INT DEFAULT 0 CHECK (year > 0) NOT NULL,
38    mileage INT DEFAULT 0 CHECK (mileage > 0),
39    condition CHAR(1) NOT NULL,
40    category_id INT NOT NULL,
41
42    CONSTRAINT fk_cars_categories
43        FOREIGN KEY (category_id)
44            REFERENCES categories(id)
45            ON DELETE CASCADE
46            ON UPDATE CASCADE
47);
48
49
50DROP TABLE IF EXISTS courses CASCADE;
51CREATE TABLE courses(
52    id SERIAL PRIMARY KEY,
53    from_address_id INT NOT NULL,
54    start TIMESTAMP NOT NULL,
55    bill NUMERIC(10,2) DEFAULT 10 CHECK (bill > 0),
56    car_id INT NOT NULL,
57    client_id INT NOT NULL,
58
59    CONSTRAINT fk_courses_addresses
60        FOREIGN KEY (from_address_id)
61            REFERENCES addresses(id)
62            ON DELETE CASCADE
63            ON UPDATE CASCADE,
64
65    CONSTRAINT fk_courses_cars
66        FOREIGN KEY (car_id)
67            REFERENCES cars(id)
68            ON DELETE CASCADE
69            ON UPDATE CASCADE,
70
71    CONSTRAINT fk_courses_clients
72        FOREIGN KEY (client_id)
73            REFERENCES clients(id)
74            ON DELETE CASCADE
75            ON UPDATE CASCADE 
76
77);
78
79DROP TABLE IF EXISTS cars_drivers CASCADE;
80CREATE TABLE cars_drivers(
81    car_id INT NOT NULL,
82    driver_id INT NOT NULL,
83
84    CONSTRAINT fk_cars_drivers_cars
85        FOREIGN KEY (car_id)
86            REFERENCES cars(id)
87            ON DELETE CASCADE
88            ON UPDATE CASCADE,
89
90    CONSTRAINT fk_cars_drivers_drivers
91        FOREIGN KEY (driver_id)
92            REFERENCES drivers(id)
93            ON DELETE CASCADE
94            ON UPDATE CASCADE
95
96);