MZcisFuM

1create schema if not exists softuni_car_service;
2use softuni_car_service;
3
4CREATE TABLE countries
5(
6    id   INT(11) PRIMARY KEY AUTO_INCREMENT,
7    name VARCHAR(20) NOT NULL UNIQUE
8);
9
10CREATE TABLE towns
11(
12    id      INT(11) PRIMARY KEY AUTO_INCREMENT,
13    name    VARCHAR(50) NOT NULL UNIQUE,
14    country_id INT(11)     NOT NULL,
15    CONSTRAINT FOREIGN KEY (country_id) REFERENCES countries (id)
16);
17
18CREATE TABLE services
19(
20    id          INT(11) PRIMARY KEY AUTO_INCREMENT,
21    name        VARCHAR(20) NOT NULL UNIQUE,
22    rating      FLOAT       NOT NULL,
23    is_nonstop TINYINT(1) DEFAULT FALSE,
24    town_id  INT(11)     NOT NULL,
25    CONSTRAINT FOREIGN KEY (town_id) REFERENCES towns (id)
26);
27
28CREATE TABLE mechanics
29(
30    id          INT(11) PRIMARY KEY AUTO_INCREMENT,
31    first_name  VARCHAR(15) NOT NULL,
32    middle_name CHAR(1),
33    last_name   VARCHAR(20) NOT NULL,
34    job_title VARCHAR(20) NOT NULL,
35    salary      DECIMAL(19, 2) DEFAULT 0,
36    hire_date   DATE        not null,
37    manager_id  INT(11),
38    service_id    INT(11)     NOT NULL,
39    CONSTRAINT FOREIGN KEY (service_id) REFERENCES services (id),
40    CONSTRAINT FOREIGN KEY (manager_id) REFERENCES mechanics (id)
41);
42
43CREATE TABLE service_notes
44(
45    id       INT(11) PRIMARY KEY AUTO_INCREMENT,
46    car_picture_url      VARCHAR(100) NOT NULL,
47    last_repair DATETIME     NOT NULL,
48    description TEXT NOT NULL
49);
50
51CREATE TABLE vehicle_types
52(
53    id   INT(11) PRIMARY KEY AUTO_INCREMENT,
54    name VARCHAR(10) NOT NULL UNIQUE
55);
56
57CREATE TABLE vehicles
58(
59    id          INT(11) PRIMARY KEY AUTO_INCREMENT,
60    manufacturer        VARCHAR(40)    NOT NULL,
61    model VARCHAR(20) NOT NULL ,
62    created DATE NOT NULL ,
63    price       DECIMAL(19, 2) NOT NULL,
64    doors INT NOT NULL ,
65    engine VARCHAR(10) NOT NULL ,
66    color VARCHAR(10) NOT NULL ,
67    vehicle_type_id INT(11)        NOT NULL,
68    service_note_id  INT(11),
69    CONSTRAINT FOREIGN KEY  (vehicle_type_id) REFERENCES vehicle_types(id),
70    CONSTRAINT FOREIGN KEY  (service_note_id) REFERENCES service_notes(id)
71);
72
73CREATE TABLE services_vehicles
74(
75    service_id INT(11) NOT NULL,
76    vehicle_id   INT(11) NOT NULL,
77    PRIMARY KEY (service_id, vehicle_id),
78    CONSTRAINT FOREIGN KEY  (service_id) REFERENCES services(id),
79    CONSTRAINT FOREIGN KEY  (vehicle_id) REFERENCES vehicles(id)
80);