---

create database car_service;
use car_service;
-- drop database car_service;

create table mechanics(
 id int primary key auto_increment,
    passport varchar(10),
    surname varchar(30),
    name varchar(30),
    midname varchar(30),
    address varchar(60),
    phone varchar(15));
    
create table dogovor(
 id int primary key auto_increment,
    date_zac date,
    term int,
    is_ras varchar(3),
    date_ras date,
    id_mec int,
    foreign key (id_mec) references mechanics(id));

create table works(
 id int primary key auto_increment,
    title varchar(50),
    std_hours decimal(10,2),
    cost_price decimal(10,2),
    price decimal(10,2),
    fond decimal(10,2));

create table clients(
 id int primary key auto_increment,
    surname varchar(30),
    name varchar(30),
    midname varchar(30),
    address varchar(60),
    phone varchar(15),
    state_number varchar(10));
    
create table orders(
 id int primary key auto_increment,
    date_in date,
    date_complete date,
    count int,
    id_cl int,
    foreign key (id_cl) references clients(id));
    
create table ord_work(
 id_ord int,
    id_work int,
    primary key (id_ord, id_work),
    foreign key (id_ord) references orders(id),
 foreign key (id_work) references works(id));
    
create table ord_mec(
 id_ord int,
    id_mec int,
    primary key (id_ord, id_mec),
    foreign key (id_ord) references orders(id),
 foreign key (id_mec) references mechanics(id));