Z8ahpnnf

1create database if not exists car_rental_system;
2
3use car_rental_system;
4
5create table city
6(
7    id        int(11)      not null auto_increment,
8    city_name varchar(100) not null,
9    primary key (id)
10);
11
12create table location
13(
14    id            int(11)      not null auto_increment,
15    location_name varchar(100) not null,
16    city_id       int(11),
17    primary key (id),
18    key city_locations (city_id),
19    constraint city_locations foreign key (city_id) references city (id)
20);
21
22create table category
23(
24    id            int(11) auto_increment,
25    category_name varchar(100) not null,
26    primary key (id)
27);
28
29create table car
30(
31    id              int(11) auto_increment,
32    brand           varchar(100) not null,
33    model           varchar(100) not null,
34    production_year int(19)      null,
35    mileage         int(19)      null,
36    color           varchar(100) null,
37    category_id     int(11)      null,
38    primary key (id),
39    key categories_cars (category_id),
40    constraint categories_cars foreign key (category_id) references category (id)
41);
42
43create table customer
44(
45    id        int(11) auto_increment,
46    name      varchar(100) not null,
47    age       int(19)      not null,
48    license   varchar(100) not null,
49    insurance varchar(100),
50    primary key (id)
51);
52
53create table insurance
54(
55    id          int(11) auto_increment,
56    primary key (id),
57    name        varchar(100) not null,
58    customer_id int(11)      not null,
59    key customers_insurances (customer_id),
60    constraint customers_insurances foreign key (customer_id) references customer (id)
61);
62
63create table reservation
64(
65    id                   int(11) auto_increment,
66    primary key (id),
67    pick_up_location_id  int(11) not null,
68    drop_off_location_id int(11) not null,
69    category_id          int(11) not null,
70    customer_id          int(11) not null,
71    key pick_up_location (pick_up_location_id),
72    key drop_off_location (drop_off_location_id),
73    key categories_reservations (category_id),
74    key customers_reservations (customer_id),
75    constraint pick_up_location foreign key (pick_up_location_id) references location (id),
76    constraint drop_off_location foreign key (drop_off_location_id) references location (id),
77    constraint categories_reservations foreign key (category_id) references category (id),
78    constraint customers_reservations foreign key (customer_id) references customer (id)
79);
80
81create table rental
82(
83    id                   int(11) auto_increment,
84    primary key (id),
85    pick_up_location_id  int(11) not null,
86    drop_off_location_id int(11) not null,
87    car_id               int(11) not null,
88    customer_id          int(11) not null,
89    key pick_up (pick_up_location_id),
90    key drop_off (drop_off_location_id),
91    key cars_rentals (car_id),
92    key customers_rentals (customer_id),
93    constraint pick_up foreign key (pick_up_location_id) references location (id),
94    constraint drop_off foreign key (drop_off_location_id) references location (id),
95    constraint cars_rentals foreign key (car_id) references car (id),
96    constraint customers_rentals foreign key (customer_id) references customer (id)
97);
98
99