CREATE DATABASE cyklo_servis

CREATE TABLE oprava
(
    ID_oprava INTEGER NOT NULL AUTO_INCREMENT,
    typ CHAR(255),
    PRIMARY KEY (ID_oprava)

)
;


CREATE TABLE mesto
(
    ID_mesto INTEGER NOT NULL AUTO_INCREMENT,
    nazev_mesta CHAR(255),
    smerovaci_cislo INTEGER,
    PRIMARY KEY (ID_mesto)

)
;


CREATE TABLE ulice
(
    ID_ulice INTEGER NOT NULL AUTO_INCREMENT,
    nazev_ulice CHAR(255),
    cislo_popisne INTEGER,
    ID_mesto INTEGER,
    PRIMARY KEY (ID_ulice),
    KEY (ID_mesto)

)
;


CREATE TABLE zakazka
(
    ID_zakazka INTEGER NOT NULL AUTO_INCREMENT,
    cena_zakazky INTEGER,
    zapoceti_zakazky DATE,
    ID_oprava INTEGER,
    ID_zakaznik INTEGER,
    PRIMARY KEY (ID_zakazka),
    KEY (ID_oprava),
    KEY (ID_zakaznik)

)
;


CREATE TABLE zakaznik
(
    ID_zakaznik INTEGER NOT NULL AUTO_INCREMENT,
    jmeno_zakaznika CHAR(255),
    prijmeni_zakaznika CHAR(255),
    rodne_cislo INTEGER,
    celkovy_pocet_zakazek INTEGER,
    celkova_utrata INTEGER,
    ID_ulice INTEGER,
    PRIMARY KEY (ID_zakaznik),
    KEY (ID_ulice)

)
;


CREATE TABLE zamestnanec
(
    ID_zamestnanec INTEGER NOT NULL AUTO_INCREMENT,
    jmeno_zamestnance CHAR(255),
    prijmeni_zamestnance CHAR(255),
    rodne_cislo INTEGER,
    nastup DATE,
    plat INTEGER,
    ID_ulice INTEGER,
    PRIMARY KEY (ID_zamestnanec),
    KEY (ID_ulice)

)
;