Drop table tblUrad cascade constraints;
Drop table tblObcan cascade constraints;
Drop table allUkony cascade constraints;
Drop table allUrychleni cascade constraints;
Drop table allUrednikFunkce cascade constraints;
Drop table tblUkony cascade constraints;
Drop table tblZamestnanci cascade constraints;
Drop table tblUrednik cascade constraints;
Drop table tblFunkceSMistem cascade constraints;
Create table tblUrad (
      NazevUradu VarChar2(255) Not Null,
      KodUradu Integer Not Null,
      PSC Number(5, 0) Null,
      Mesto VarChar2(255) Null,
      Ulice VarChar2(255) Null,
      Constraint PK_tblUrad Primary Key (KodUradu)
);
Create table tblObcan (
      RodneCislo Number(10, 0) Not Null,
      Jmeno VarChar2(50) Null,
      Prijmeni VarChar2(80) Not Null,
      Constraint PK_tblObcan Primary Key (RodneCislo)
);
Create table allUkony (
      NazevUkonu VarChar2(255) Not Null,
      StandardniDoba Integer Not Null,
      ID Integer Not Null,
      tblUrad_KodUradu Integer Not Null,
      Constraint PK_allUkony Primary Key (ID, tblUrad_KodUradu)
);
Create table allUrychleni (
      MinCastka Number(38, 2) Not Null,
      ZkraceniDoby Integer Not Null,
      allUkony_ID Integer Not Null,
      allUkony_tblUrad_KodUradu Integer Not Null,
      Constraint PK_allUrychleni Primary Key (allUkony_ID, allUkony_tblUrad_KodUradu)
);
Create table allUrednikFunkce (
      ID Integer Not Null,
      Nazev VarChar2(255) Not Null,
      Constraint PK_allUrednikFunkce Primary Key (ID)
);
Create table tblUkony (
      Porizeno Date Not Null,
      Uplatek Integer Null,
      Pozadu_RodneCislo Number(10, 0) Not Null,
      Resi_u_tblObcan_RodneCislo Number(10, 0) Not Null,
      Pozado_ID Integer Not Null,
      Pozado_tblUrad_KodUradu Integer Not Null,
      Constraint UNQ_tblUkony_1 Unique (Pozadu_RodneCislo, Resi_u_tblObcan_RodneCislo, Pozado_ID, Pozado_tblUrad_KodUradu)
);
Create table tblZamestnanci (
      Ma_zam_KodUradu Integer Not Null,
      Je_zam_tblObcan_RodneCislo Number(10, 0) Not Null,
      Constraint UNQ_tblZamestnanci_1 Unique (Ma_zam_KodUradu, Je_zam_tblObcan_RodneCislo)
);
Create table tblUrednik (
      Je_obs_ID Integer Not Null,
      CisloUrednika Integer Not Null,
      PlatovaTrida Integer Null,
      tblObcan_RodneCislo Number(10, 0) Not Null,
      Constraint UNQ_tblUrednik_1 Unique (CisloUrednika),
      Constraint PK_tblUrednik Primary Key (tblObcan_RodneCislo)
);
Create table tblFunkceSMistem (
      Funkce_ID Integer Not Null,
      Ma_mis_KodUradu Integer Not Null,
      Constraint UNQ_tblFunkceSMistem_1 Unique (Funkce_ID, Ma_mis_KodUradu)
);
Alter table allUkony add (
      Constraint FK_allUkony_1 Foreign Key (tblUrad_KodUradu) References tblUrad(KodUradu)
);
Alter table allUrychleni add (
      Constraint FK_allUrychleni_1 Foreign Key (allUkony_ID, allUkony_tblUrad_KodUradu) References allUkony(ID, tblUrad_KodUradu)
);
Alter table tblUkony add (
      Constraint FK_tblUkony_1 Foreign Key (Pozadu_RodneCislo) References tblObcan(RodneCislo),
      Constraint FK_tblUkony_2 Foreign Key (Resi_u_tblObcan_RodneCislo) References tblUrednik(tblObcan_RodneCislo),
      Constraint FK_tblUkony_3 Foreign Key (Pozado_ID, Pozado_tblUrad_KodUradu) References allUkony(ID, tblUrad_KodUradu)
);
Alter table tblZamestnanci add (
      Constraint FK_tblZamestnanci_1 Foreign Key (Ma_zam_KodUradu) References tblUrad(KodUradu),
      Constraint FK_tblZamestnanci_2 Foreign Key (Je_zam_tblObcan_RodneCislo) References tblUrednik(tblObcan_RodneCislo)
);
Alter table tblUrednik add (
      Constraint FK_tblUrednik_1 Foreign Key (tblObcan_RodneCislo) References tblObcan(RodneCislo),
      Constraint FK_tblUrednik_2 Foreign Key (Je_obs_ID) References allUrednikFunkce(ID)
);
Alter table tblFunkceSMistem add (
      Constraint FK_tblFunkceSMistem_1 Foreign Key (Funkce_ID) References allUrednikFunkce(ID),
      Constraint FK_tblFunkceSMistem_2 Foreign Key (Ma_mis_KodUradu) References tblUrad(KodUradu)
);