drop view seznam_pokoju;
drop view hoste_na_pokoji;
drop view seznam_hostu;
drop view vysetreni_hostu;

create view seznam_pokoju as select cislo,patro,telefon from pokoj,mistnost
 where pokoj.mistnost_cislo=mistnost.cislo;

create view hoste_na_pokoji as select obyvan_mistnost_cislo,jmeno,prijmeni,od,do from host,osoba,ubytovani
 where host.osoba_cislo=osoba.cislo and osoba.cislo=ubytovani.ubytovan_osoba_cislo;

create view seznam_hostu as select cislo,jmeno,prijmeni from host,osoba
 where host.osoba_cislo=osoba.cislo;

create view vysetreni_hostu as select h.cislo,datum,h.jmeno as host_jmeno,h.prijmeni as host_prijmeni,
  l.prijmeni as lekar_prijmeni,diagnoza,count(*) as procedur from host,osoba h,vysetreni,osoba l,procedura
 where host.osoba_cislo=h.cislo and h.cislo=vysetreni.vysetren_osoba_cislo and vysetreni.provadi_v_osoba_cislo=l.cislo
  and vysetreni.cislo=procedura.predepisuj_cislo group by h.cislo,datum,h.jmeno,h.prijmeni,l.prijmeni,diagnoza;

drop sequence cis_os;
create sequence cis_os start with 100 increment by 1;

drop sequence cis_vy;
create sequence cis_vy start with 100 increment by 1;

drop sequence cis_pr;
create sequence cis_pr start with 100 increment by 1;