Drop table Pohon cascade constraints;
Drop table Lokace cascade constraints;
Drop table Smena cascade constraints;
Drop table Pracovnik cascade constraints;
Drop table Strojvudce cascade constraints;
Drop table Posunovac cascade constraints;
Drop table Pokladni cascade constraints;
Drop table Zamestnani cascade constraints;
Drop table Skupina_pracovist cascade constraints;
Drop table Razeni cascade constraints;
Drop table Zastavka cascade constraints;
Drop table Typ_vuz cascade constraints;
Drop table Typ_vagon cascade constraints;
Drop table Typ_lokomotiva cascade constraints;
Drop table Obecny cascade constraints;
Drop table Lokomotiva cascade constraints;
Drop table Vagon cascade constraints;
Drop table Stanice cascade constraints;
Drop table Vuz cascade constraints;
Drop table Trasa cascade constraints;
Drop table Skupina_pracovniku cascade constraints;
Drop table Opravneni cascade constraints;
Drop table Spoj cascade constraints;
Drop table Pracoviste cascade constraints;
Drop table Jizdenka cascade constraints;
Drop table Vyhybka cascade constraints;
Create table Pohon (
      nasobnost Integer Null,
      Lokomotiva_Vuz_id_vuz Integer Not Null,
      Spoj_id_spoj Integer Not Null,
      Constraint PK_Pohon Primary Key (Lokomotiva_Vuz_id_vuz, Spoj_id_spoj)
);
Create table Lokace (
      osada VarChar2(255) Null,
      souradnice_z Float Not Null,
      souradnice_x Float Not Null,
      souradnice_y Float Not Null,
      id_lokace Integer Not Null,
      Constraint PK_Lokace Primary Key (id_lokace)
);
Create table Smena (
      cislo_smeny Integer Not Null,
      od Date Not Null,
      do Date Not Null,
      Constraint PK_Smena Primary Key (cislo_smeny)
);
Create table Pracovnik (
      jmeno VarChar2(255) Not Null,
      RC Integer Not Null,
      Constraint PK_Pracovnik Primary Key (RC)
);
Create table Strojvudce (
      Pracovnik_RC Integer Not Null,
      Constraint PK_Strojvudce Primary Key (Pracovnik_RC)
);
Create table Posunovac (
      Pracovnik_RC Integer Not Null,
      Constraint PK_Posunovac Primary Key (Pracovnik_RC)
);
Create table Pokladni (
      Pracovnik_RC Integer Not Null,
      Constraint PK_Pokladni Primary Key (Pracovnik_RC)
);
Create table Zamestnani (
      Pracovnik_RC Integer Not Null,
      Smena_cislo_smeny Integer Not Null,
      Pracoviste_id_pracoviste Integer Not Null,
      Constraint PK_Zamestnani Primary Key (Pracovnik_RC, Smena_cislo_smeny, Pracoviste_id_pracoviste)
);
Create table Skupina_pracovist (
      popis VarChar2(255) Not Null,
      Constraint PK_Skupina_pracovist Primary Key (popis)
);
Create table Razeni (
      poradi Integer Null,
      kuracky Integer Null,
      Spoj_id_spoj Integer Not Null,
      Vuz_id_vuz Integer Not Null,
      Constraint PK_Razeni Primary Key (Spoj_id_spoj, Vuz_id_vuz)
);
Create table Zastavka (
      nastupiste Integer Null,
      prijezd Date Not Null,
      kolej Integer Not Null,
      odjezd Date Not Null,
      Spoj_id_spoj Integer Not Null,
      Stanice_id_stanice Integer Not Null,
      Constraint PK_Zastavka Primary Key (Spoj_id_spoj, Stanice_id_stanice)
);
Create table Typ_vuz (
      hmotnost Integer Not Null,
      pocet_kol Integer Not Null,
      id_typ_vuz Integer Not Null,
      Constraint PK_Typ_vuz Primary Key (id_typ_vuz)
);
Create table Typ_vagon (
      kapacita Integer Not Null,
      druh Integer Not Null,
      Typ_vuz_id_typ_vuz Integer Not Null,
      Constraint PK_Typ_vagon Primary Key (Typ_vuz_id_typ_vuz)
);
Create table Typ_lokomotiva (
      vykon Integer Not Null,
      max_tazena_hmotnost Integer Not Null,
      typ_pohon VarChar2(255) Not Null,
      Typ_vuz_id_typ_vuz Integer Not Null,
      Constraint PK_Typ_lokomotiva Primary Key (Typ_vuz_id_typ_vuz)
);
Create table Obecny (
      skupina_Z_popis VarChar2(255) Not Null,
      Pracovnik_RC Integer Not Null,
      Constraint PK_Obecny Primary Key (Pracovnik_RC)
);
Create table Lokomotiva (
      typ_L_Typ_vuz_id_typ_vuz Integer Not Null,
      Vuz_id_vuz Integer Not Null,
      Constraint PK_Lokomotiva Primary Key (Vuz_id_vuz)
);
Create table Vagon (
      typ_V_Typ_vuz_id_typ_vuz Integer Not Null,
      Vuz_id_vuz Integer Not Null,
      Constraint PK_Vagon Primary Key (Vuz_id_vuz)
);
Create table Stanice (
      nazev VarChar2(255) Not Null,
      mesto VarChar2(255) Null,
      pocet_koleji Integer Not Null,
      elektrifikace Integer Null,
      hala Integer Null,
      id_stanice Integer Not Null,
      umisteni_S_id_lokace Integer Not Null,
      Constraint UNQ_Stanice_1 Unique (umisteni_S_id_lokace),
      Constraint PK_Stanice Primary Key (id_stanice)
);
Create table Vuz (
      doma_lokalni_servis Integer Null,
      doma_od Date Not Null,
      id_vuz Integer Not Null,
      posledni_servis Date Not Null,
      datum_vyroby Date Not Null,
      domov_id_stanice Integer Not Null,
      Constraint PK_Vuz Primary Key (id_vuz)
);
Create table Trasa (
      kilometry Integer Not Null,
      svetlost Integer Null,
      pocet_tunelu Integer Null,
      max_prevyseni Integer Null,
      id_trasa Integer Not Null,
      konec_id_lokace Integer Not Null,
      zacatek_id_lokace Integer Not Null,
      Constraint PK_Trasa Primary Key (id_trasa)
);
Create table Skupina_pracovniku (
      od_pracovist_popis VarChar2(255) Not Null,
      popis VarChar2(255) Not Null,
      Constraint PK_Skupina_pracovniku Primary Key (popis)
);
Create table Opravneni (
      k_rizeni_Typ_vuz_id_typ_vuz Integer Not Null,
      opravnen_Pracovnik_RC Integer Not Null,
      Constraint UNQ_Opravneni_1 Unique (k_rizeni_Typ_vuz_id_typ_vuz, opravnen_Pracovnik_RC)
);
Create table Spoj (
      zrychleny Integer Null,
      osobni_nakladni Integer Not Null,
      id_spoj Integer Not Null,
      jede_sobota Integer Not Null,
      jede_nedele Integer Not Null,
      jede_svatek Integer Not Null,
      prijezd_spoj Date Not Null,
      odjezd_spoj Date Not Null,
      posun_Pracovnik_RC Integer Not Null,
      rizeni_Pracovnik_RC Integer Not Null,
      Constraint PK_Spoj Primary Key (id_spoj)
);
Create table Pracoviste (
      skupina_P_popis VarChar2(255) Not Null,
      umisteni_P_id_stanice Integer Not Null,
      smerovka VarChar2(255) Null,
      id_pracoviste Integer Not Null,
      pocet_mist Integer Not Null,
      Constraint PK_Pracoviste Primary Key (id_pracoviste)
);
Create table Jizdenka (
      vydej_kde_id_stanice Integer Not Null,
      detska Integer Not Null,
      osoby Integer Not Null,
      zpatecni Integer Not Null,
      cena Float Not Null,
      vydej_kdy Date Not Null,
      id_jizdenka Integer Not Null,
      cil_id_stanice Integer Not Null,
      start_id_stanice Integer Not Null,
      vydej_kym_Pracovnik_RC Integer Not Null,
      Constraint PK_Jizdenka Primary Key (id_jizdenka)
);
Create table Vyhybka (
      smer_1_id_stanice Integer Not Null,
      smer_2_id_stanice Integer Not Null,
      mechanicka Integer Null,
      id_vyhybka Integer Not Null,
      umisteni_V_id_pracoviste Integer Not Null,
      Constraint PK_Vyhybka Primary Key (id_vyhybka)
);
Alter table Pohon add (
      Constraint FK_Pohon_1 Foreign Key (Lokomotiva_Vuz_id_vuz) References Lokomotiva(Vuz_id_vuz),
      Constraint FK_Pohon_2 Foreign Key (Spoj_id_spoj) References Spoj(id_spoj)
);
Alter table Strojvudce add (
      Constraint FK_Strojvudce_1 Foreign Key (Pracovnik_RC) References Pracovnik(RC)
);
Alter table Posunovac add (
      Constraint FK_Posunovac_1 Foreign Key (Pracovnik_RC) References Pracovnik(RC)
);
Alter table Pokladni add (
      Constraint FK_Pokladni_1 Foreign Key (Pracovnik_RC) References Pracovnik(RC)
);
Alter table Zamestnani add (
      Constraint FK_Zamestnani_1 Foreign Key (Pracovnik_RC) References Pracovnik(RC),
      Constraint FK_Zamestnani_2 Foreign Key (Smena_cislo_smeny) References Smena(cislo_smeny),
      Constraint FK_Zamestnani_3 Foreign Key (Pracoviste_id_pracoviste) References Pracoviste(id_pracoviste)
);
Alter table Razeni add (
      Constraint FK_Razeni_1 Foreign Key (Spoj_id_spoj) References Spoj(id_spoj),
      Constraint FK_Razeni_2 Foreign Key (Vuz_id_vuz) References Vuz(id_vuz)
);
Alter table Zastavka add (
      Constraint FK_Zastavka_1 Foreign Key (Spoj_id_spoj) References Spoj(id_spoj),
      Constraint FK_Zastavka_2 Foreign Key (Stanice_id_stanice) References Stanice(id_stanice)
);
Alter table Typ_vagon add (
      Constraint FK_Typ_vagon_1 Foreign Key (Typ_vuz_id_typ_vuz) References Typ_vuz(id_typ_vuz)
);
Alter table Typ_lokomotiva add (
      Constraint FK_Typ_lokomotiva_1 Foreign Key (Typ_vuz_id_typ_vuz) References Typ_vuz(id_typ_vuz)
);
Alter table Obecny add (
      Constraint FK_Obecny_1 Foreign Key (Pracovnik_RC) References Pracovnik(RC),
      Constraint FK_Obecny_2 Foreign Key (skupina_Z_popis) References Skupina_pracovniku(popis)
);
Alter table Lokomotiva add (
      Constraint FK_Lokomotiva_1 Foreign Key (Vuz_id_vuz) References Vuz(id_vuz),
      Constraint FK_Lokomotiva_2 Foreign Key (typ_L_Typ_vuz_id_typ_vuz) References Typ_lokomotiva(Typ_vuz_id_typ_vuz)
);
Alter table Vagon add (
      Constraint FK_Vagon_1 Foreign Key (Vuz_id_vuz) References Vuz(id_vuz),
      Constraint FK_Vagon_2 Foreign Key (typ_V_Typ_vuz_id_typ_vuz) References Typ_vagon(Typ_vuz_id_typ_vuz)
);
Alter table Stanice add (
      Constraint FK_Stanice_1 Foreign Key (umisteni_S_id_lokace) References Lokace(id_lokace)
);
Alter table Vuz add (
      Constraint FK_Vuz_1 Foreign Key (domov_id_stanice) References Stanice(id_stanice)
);
Alter table Trasa add (
      Constraint FK_Trasa_1 Foreign Key (zacatek_id_lokace) References Lokace(id_lokace),
      Constraint FK_Trasa_2 Foreign Key (konec_id_lokace) References Lokace(id_lokace)
);
Alter table Skupina_pracovniku add (
      Constraint FK_Skupina_pracovniku_1 Foreign Key (od_pracovist_popis) References Skupina_pracovist(popis)
);
Alter table Opravneni add (
      Constraint FK_Opravneni_1 Foreign Key (k_rizeni_Typ_vuz_id_typ_vuz) References Typ_lokomotiva(Typ_vuz_id_typ_vuz),
      Constraint FK_Opravneni_2 Foreign Key (opravnen_Pracovnik_RC) References Strojvudce(Pracovnik_RC)
);
Alter table Spoj add (
      Constraint FK_Spoj_1 Foreign Key (rizeni_Pracovnik_RC) References Strojvudce(Pracovnik_RC),
      Constraint FK_Spoj_2 Foreign Key (posun_Pracovnik_RC) References Posunovac(Pracovnik_RC)
);
Alter table Pracoviste add (
      Constraint FK_Pracoviste_1 Foreign Key (skupina_P_popis) References Skupina_pracovist(popis),
      Constraint FK_Pracoviste_2 Foreign Key (umisteni_P_id_stanice) References Stanice(id_stanice)
);
Alter table Jizdenka add (
      Constraint FK_Jizdenka_1 Foreign Key (vydej_kym_Pracovnik_RC) References Pokladni(Pracovnik_RC),
      Constraint FK_Jizdenka_2 Foreign Key (vydej_kde_id_stanice) References Stanice(id_stanice),
      Constraint FK_Jizdenka_3 Foreign Key (start_id_stanice) References Stanice(id_stanice),
      Constraint FK_Jizdenka_4 Foreign Key (cil_id_stanice) References Stanice(id_stanice)
);
Alter table Vyhybka add (
      Constraint FK_Vyhybka_1 Foreign Key (smer_1_id_stanice) References Stanice(id_stanice),
      Constraint FK_Vyhybka_2 Foreign Key (smer_2_id_stanice) References Stanice(id_stanice),
      Constraint FK_Vyhybka_3 Foreign Key (umisteni_V_id_pracoviste) References Pracoviste(id_pracoviste)
);
