Drop table Host cascade constraints;
Drop table Pokoj cascade constraints;
Drop table Osoba cascade constraints;
Drop table Sestra cascade constraints;
Drop table Mistnost cascade constraints;
Drop table Lecebna cascade constraints;
Drop table Druh_procedury cascade constraints;
Drop table Lekar cascade constraints;
Drop table Konani cascade constraints;
Drop table Provedeni_p cascade constraints;
Drop table Ubytovani cascade constraints;
Drop table Vysetreni cascade constraints;
Drop table Procedura cascade constraints;
Drop table Vybaveni cascade constraints;
Drop table Kvalifikace cascade constraints;
Create table Host (
      Pojistovna Integer Null,
      Pohlavi Integer Not Null,
      Osoba_Cislo Integer Not Null,
      Constraint PK_Host Primary Key (Osoba_Cislo)
);
Create table Pokoj (
      Kapacita Integer Not Null,
      Mistnost_Cislo Integer Not Null,
      Constraint PK_Pokoj Primary Key (Mistnost_Cislo)
);
Create table Osoba (
      Cislo Integer Not Null,
      Narozeni Date Not Null,
      Jmeno VarChar2(64) Not Null,
      Prijmeni VarChar2(63) Not Null,
      Rodne_c Number(11, 0) Null,
      Titul VarChar2(15) Null,
      Constraint PK_Osoba Primary Key (Cislo)
);
Create table Sestra (
      Osoba_Cislo Integer Not Null,
      Constraint PK_Sestra Primary Key (Osoba_Cislo)
);
Create table Mistnost (
      Cislo Integer Not Null,
      Patro Integer Not Null,
      Telefon Number(17, 0) Null,
      Constraint PK_Mistnost Primary Key (Cislo)
);
Create table Lecebna (
      Kapacita Integer Not Null,
      Mistnost_Cislo Integer Not Null,
      Constraint PK_Lecebna Primary Key (Mistnost_Cislo)
);
Create table Druh_procedury (
      Cislo Integer Not Null,
      Popis VarChar2(128) Not Null,
      Constraint PK_Druh_procedury Primary Key (Cislo)
);
Create table Lekar (
      Osoba_Cislo Integer Not Null,
      Constraint PK_Lekar Primary Key (Osoba_Cislo)
);
Create table Konani (
      konana_Cislo Integer Not Null,
      realizuje_Mistnost_Cislo Integer Not Null,
      Constraint UNQ_Konani_1 Unique (konana_Cislo)
);
Create table Provedeni_p (
      provadi_p_Osoba_Cislo Integer Not Null,
      provedena__Cislo Integer Not Null,
      Constraint UNQ_Provedeni_p_1 Unique (provedena__Cislo)
);
Create table Ubytovani (
      Od Date Not Null,
      Do Date Not Null,
      obyvan_Mistnost_Cislo Integer Not Null,
      ubytovan_Osoba_Cislo Integer Not Null,
      Constraint UNQ_Ubytovani_1 Unique (ubytovan_Osoba_Cislo)
);
Create table Vysetreni (
      vysetren_Osoba_Cislo Integer Not Null,
      Diagnoza VarChar2(255) Null,
      Datum Date Not Null,
      Cislo Integer Not Null,
      provadi_v_Osoba_Cislo Integer Not Null,
      Constraint PK_Vysetreni Primary Key (Cislo)
);
Create table Procedura (
      Doba Date Null,
      Cislo Integer Not Null,
      Provedena Integer Not Null,
      Vysledek VarChar2(255) Null,
      predepisuj_Cislo Integer Not Null,
      druh_Cislo Integer Not Null,
      Constraint PK_Procedura Primary Key (Cislo)
);
Create table Vybaveni (
      proveditel_Cislo Integer Not Null,
      vybavena_Mistnost_Cislo Integer Not Null,
      Constraint UNQ_Vybaveni_1 Unique (proveditel_Cislo, vybavena_Mistnost_Cislo)
);
Create table Kvalifikace (
      ovladana_Cislo Integer Not Null,
      ovlada_Osoba_Cislo Integer Not Null,
      Constraint UNQ_Kvalifikace_1 Unique (ovladana_Cislo, ovlada_Osoba_Cislo)
);
Alter table Host add (
      Constraint FK_Host_1 Foreign Key (Osoba_Cislo) References Osoba(Cislo)
);
Alter table Pokoj add (
      Constraint FK_Pokoj_1 Foreign Key (Mistnost_Cislo) References Mistnost(Cislo)
);
Alter table Sestra add (
      Constraint FK_Sestra_1 Foreign Key (Osoba_Cislo) References Osoba(Cislo)
);
Alter table Lecebna add (
      Constraint FK_Lecebna_1 Foreign Key (Mistnost_Cislo) References Mistnost(Cislo)
);
Alter table Lekar add (
      Constraint FK_Lekar_1 Foreign Key (Osoba_Cislo) References Osoba(Cislo)
);
Alter table Konani add (
      Constraint FK_Konani_1 Foreign Key (konana_Cislo) References Procedura(Cislo),
      Constraint FK_Konani_2 Foreign Key (realizuje_Mistnost_Cislo) References Lecebna(Mistnost_Cislo)
);
Alter table Provedeni_p add (
      Constraint FK_Provedeni_p_1 Foreign Key (provadi_p_Osoba_Cislo) References Sestra(Osoba_Cislo),
      Constraint FK_Provedeni_p_2 Foreign Key (provedena__Cislo) References Procedura(Cislo)
);
Alter table Ubytovani add (
      Constraint FK_Ubytovani_1 Foreign Key (obyvan_Mistnost_Cislo) References Pokoj(Mistnost_Cislo),
      Constraint FK_Ubytovani_2 Foreign Key (ubytovan_Osoba_Cislo) References Host(Osoba_Cislo)
);
Alter table Vysetreni add (
      Constraint FK_Vysetreni_1 Foreign Key (vysetren_Osoba_Cislo) References Host(Osoba_Cislo),
      Constraint FK_Vysetreni_2 Foreign Key (provadi_v_Osoba_Cislo) References Lekar(Osoba_Cislo)
);
Alter table Procedura add (
      Constraint FK_Procedura_1 Foreign Key (druh_Cislo) References Druh_procedury(Cislo),
      Constraint FK_Procedura_2 Foreign Key (predepisuj_Cislo) References Vysetreni(Cislo)
);
Alter table Vybaveni add (
      Constraint FK_Vybaveni_1 Foreign Key (proveditel_Cislo) References Druh_procedury(Cislo),
      Constraint FK_Vybaveni_2 Foreign Key (vybavena_Mistnost_Cislo) References Lecebna(Mistnost_Cislo)
);
Alter table Kvalifikace add (
      Constraint FK_Kvalifikace_1 Foreign Key (ovladana_Cislo) References Druh_procedury(Cislo),
      Constraint FK_Kvalifikace_2 Foreign Key (ovlada_Osoba_Cislo) References Sestra(Osoba_Cislo)
);