-- Generated by Oracle SQL Developer Data Modeler 3.1.4.710
--   at:        2013-09-12 15:52:16 CEST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g
set echo on
CREATE OR REPLACE PROCEDURE "ZRUS_ALL_OBJEKTY_SCHEMATU" AS
-- pokud v logu bude uvedeno, ze nektery objekt nebyl zrusen, protoze na nej jiny jeste existujici objekt stavi,
-- spust proceduru opakovane, dokud se nezrusi vse
v_pom PLS_INTEGER;
BEGIN
LOOP
FOR irec IN (SELECT DISTINCT
                    object_type,object_name,
                    'drop '||object_type||' "'||object_name||'"'||
                    CASE object_type WHEN 'TABLE' THEN ' cascade constraints purge'
                                     ELSE ' '
                    END AS prikaz
              FROM user_objects
              WHERE object_name <> 'ZRUS_ALL_OBJEKTY_SCHEMATU'
            )
  LOOP
   BEGIN
   DBMS_OUTPUT.put_line('prikaz '||irec.prikaz);
      EXECUTE IMMEDIATE irec.prikaz;
   EXCEPTION
      WHEN OTHERS THEN DBMS_OUTPUT.put('není ');
   END;
   DBMS_OUTPUT.put_line('zrusen(a) '||irec.object_type||' '||irec.object_name);
  END LOOP;
SELECT COUNT(*) INTO v_pom
FROM user_objects
WHERE object_name <> 'ZRUS_ALL_OBJEKTY_SCHEMATU';
EXIT WHEN v_pom = 0;
END LOOP;
DBMS_OUTPUT.put_line('hotovo!');
END ZRUS_ALL_OBJEKTY_SCHEMATU;
/
execute ZRUS_ALL_OBJEKTY_SCHEMATU
/*
DROP TABLE AUTOBUS CASCADE CONSTRAINTS PURGE
;
DROP TABLE LINKA CASCADE CONSTRAINTS PURGE
;
DROP TABLE LOD CASCADE CONSTRAINTS PURGE
;
DROP TABLE NAMORNIK CASCADE CONSTRAINTS PURGE
;
DROP TABLE PLAVBA_S_PRUVODCEM CASCADE CONSTRAINTS PURGE
;
DROP TABLE POKRYTI CASCADE CONSTRAINTS PURGE
;
DROP TABLE REZERVACE CASCADE CONSTRAINTS PURGE
;
DROP TABLE SPOJ CASCADE CONSTRAINTS PURGE
;
DROP TABLE ZAKAZNIK CASCADE CONSTRAINTS PURGE
;
DROP TABLE ZAZEMI CASCADE CONSTRAINTS PURGE
;
DROP TABLE kalendar_namornika CASCADE CONSTRAINTS PURGE
; */
CREATE TABLE AUTOBUS 
    ( 
     inv_cislo NUMBER (10)  NOT NULL , 
     SPZ VARCHAR2 (10) , 
     pocet_mist NUMBER (3) , 
     datum_uvedeni_do_provozu DATE  NOT NULL , 
     porizovaci_cena NUMBER (9,2) , 
     id_zazemi NUMBER (10)  NOT NULL 
    ) 
;



COMMENT ON TABLE AUTOBUS IS 'evidence autobusu, kterymi spolecnost disponuje'
;


COMMENT ON COLUMN AUTOBUS.SPZ IS 'statni poznavaci znacka autobusu' 
;

COMMENT ON COLUMN AUTOBUS.pocet_mist IS 'pocet mist k sezeni v autobusu' 
;

COMMENT ON COLUMN AUTOBUS.datum_uvedeni_do_provozu IS 'datum uvedeni do provozu autobusu' 
;

COMMENT ON COLUMN AUTOBUS.porizovaci_cena IS 'porizovaci cena autobusu' 
;

COMMENT ON COLUMN AUTOBUS.id_zazemi IS 've kterem depu autobus parkuje' 
;
CREATE UNIQUE INDEX "autobus PKX" ON AUTOBUS 
    ( 
     inv_cislo ASC 
    ) 
;
CREATE UNIQUE INDEX SPZ_UNQX ON AUTOBUS 
    ( 
     SPZ ASC 
    ) 
;
CREATE INDEX ma_depoX ON AUTOBUS 
    ( 
     id_zazemi ASC 
    ) 
;

ALTER TABLE AUTOBUS 
    ADD CONSTRAINT "autobus PK" PRIMARY KEY ( inv_cislo ) ;


ALTER TABLE AUTOBUS 
    ADD CONSTRAINT SPZ_UNQ UNIQUE ( SPZ ) ;



CREATE TABLE LINKA 
    ( 
     LID NUMBER (10)  NOT NULL , 
     STRT VARCHAR2 (200) , 
     CIL VARCHAR2 (200) 
    ) 
;



COMMENT ON TABLE LINKA IS 'Pravidelna autobusova, nebo lodni linka, kterou spolecnost poskytuje. '
;


COMMENT ON COLUMN LINKA.LID IS 'evidencni cislo linky' 
;

COMMENT ON COLUMN LINKA.STRT IS 'stanice, ve ktere linka startuje' 
;

COMMENT ON COLUMN LINKA.CIL IS 'stanice, ve ktered linka konci' 
;
CREATE UNIQUE INDEX LINKA_PKX ON LINKA 
    ( 
     LID ASC 
    ) 
;

ALTER TABLE LINKA 
    ADD CONSTRAINT LINKA_PK PRIMARY KEY ( LID ) ;



CREATE TABLE LOD 
    ( 
     LODID NUMBER (10)  NOT NULL , 
     JMENOL VARCHAR2 (20)  NOT NULL , 
     LTYP VARCHAR2 (20) , 
     BARVA VARCHAR2 (20) , 
     POCET_MIST NUMBER , 
     id_zazemi NUMBER (10) 
    ) 
;



COMMENT ON TABLE LOD IS 'registrovana Lod. Muze byt rezervovana zakaznikovi na cely den, muze nasazena na plavbu s pruvodcem a muze byt nasazena na previdelny spoj, ktery spolecnost provozuje'
;


COMMENT ON COLUMN LOD.LODID IS 'identifikator lod, pod ktery je lod evidovana' 
;

COMMENT ON COLUMN LOD.JMENOL IS 'jmeno lodi' 
;

COMMENT ON COLUMN LOD.BARVA IS 'barva lodi' 
;

COMMENT ON COLUMN LOD.POCET_MIST IS 'kapacita lodi' 
;

COMMENT ON COLUMN LOD.id_zazemi IS 've kterem pristavisti lod kotvi' 
;
CREATE UNIQUE INDEX LOD_PKX ON LOD 
    ( 
     LODID ASC 
    ) 
;
CREATE UNIQUE INDEX LOD_UKX ON LOD 
    ( 
     JMENOL ASC 
    ) 
;
CREATE INDEX kotvi_kdeX ON LOD 
    ( 
     id_zazemi ASC 
    ) 
;

ALTER TABLE LOD 
    ADD CONSTRAINT LOD_PK PRIMARY KEY ( LODID ) ;


ALTER TABLE LOD 
    ADD CONSTRAINT LOD_UK UNIQUE ( JMENOL ) ;



CREATE TABLE NAMORNIK 
    ( 
     NID NUMBER (10)  NOT NULL , 
     JMENON VARCHAR2 (20)  NOT NULL , 
     VEK NUMBER , 
     HODNOST VARCHAR2 (20) , 
     PLAT NUMBER (6) 
    ) 
;



COMMENT ON TABLE NAMORNIK IS 'registr namorniku'
;


COMMENT ON COLUMN NAMORNIK.NID IS 'evidencni cislo namornika' 
;

COMMENT ON COLUMN NAMORNIK.JMENON IS 'jmeno a prijmeni zakaznika' 
;

COMMENT ON COLUMN NAMORNIK.VEK IS 'vek zakaznika v poctu roku, udaj se musi kazdy rok aktualizovat' 
;

COMMENT ON COLUMN NAMORNIK.HODNOST IS 'hodnost namornika, zadava se volnym textem' 
;

COMMENT ON COLUMN NAMORNIK.PLAT IS 'mesicni plat namornika, udava se v celych cislech' 
;
CREATE UNIQUE INDEX NAMORNIK_PKX ON NAMORNIK 
    ( 
     NID ASC 
    ) 
;
CREATE UNIQUE INDEX NAMORNIK_UKX ON NAMORNIK 
    ( 
     JMENON ASC 
    ) 
;

ALTER TABLE NAMORNIK 
    ADD CONSTRAINT NAMORNIK_PK PRIMARY KEY ( NID ) ;


ALTER TABLE NAMORNIK 
    ADD CONSTRAINT NAMORNIK_UK UNIQUE ( JMENON ) ;



CREATE TABLE PLAVBA_S_PRUVODCEM 
    ( 
     NID NUMBER (10)  NOT NULL , 
     Od DATE  NOT NULL , 
     do DATE  NOT NULL , 
     ID_LOD NUMBER (10)  NOT NULL , 
     ZID NUMBER (10)  NOT NULL 
    ) 
;



COMMENT ON TABLE PLAVBA_S_PRUVODCEM IS 'kniha plaveb s pruvodcem pro daneho zakaznika. Plavna zacina v dany den a cas a konci v dany den a cas'
;

CREATE UNIQUE INDEX PLAVBA_S_PRUVODCEM__IDX ON PLAVBA_S_PRUVODCEM 
    ( 
     NID ASC , 
     Od ASC , 
     do ASC 
    ) 
;
CREATE UNIQUE INDEX PLAVBA_VEDENA_PKX ON PLAVBA_S_PRUVODCEM 
    ( 
     NID ASC , 
     Od ASC , 
     do ASC , 
     ID_LOD ASC , 
     ZID ASC 
    ) 
;
CREATE INDEX plavi_se_kdoX ON PLAVBA_S_PRUVODCEM 
    ( 
     ZID ASC 
    ) 
;
CREATE INDEX plavi_se_na_cemX ON PLAVBA_S_PRUVODCEM 
    ( 
     ID_LOD ASC 
    ) 
;

ALTER TABLE PLAVBA_S_PRUVODCEM 
    ADD CONSTRAINT PLAVBA_VEDENA_PK PRIMARY KEY ( NID, Od, do, ID_LOD, ZID ) ;



CREATE TABLE POKRYTI 
    ( 
     datum_pokryti DATE  NOT NULL , 
     LODID NUMBER (10) , 
     inv_cislo NUMBER (10) , 
     NID NUMBER (10) , 
     Od DATE , 
     do DATE , 
     LID NUMBER (10)  NOT NULL , 
     SPID NUMBER (10)  NOT NULL 
    ) 
;

ALTER TABLE POKRYTI 
    ADD CONSTRAINT FKArc_1 CHECK ( 
        (  (inv_cislo IS NOT NULL) AND 
         (LODID IS NULL) ) OR 
        (  (LODID IS NOT NULL) AND 
         (inv_cislo IS NULL) ) ) 
;


COMMENT ON TABLE POKRYTI IS 'vztahovy typ entity, ktery urcuje, ktery dopravni prostredek a ktery namornik jsou nasazeni na pravidelny spoj. Dopravnim prostredkem je lod, nebo autobus.'
;


COMMENT ON COLUMN POKRYTI.datum_pokryti IS 'datum, kdy dochazi k pokryti pravidelneho spoje' 
;

COMMENT ON COLUMN POKRYTI.LODID IS 'spoj je pokryt lodi' 
;

COMMENT ON COLUMN POKRYTI.inv_cislo IS 'spoj je pokryt busem' 
;
CREATE UNIQUE INDEX POKRYTO_PKX ON POKRYTI 
    ( 
     datum_pokryti ASC , 
     LID ASC , 
     SPID ASC 
    ) 
;
CREATE INDEX je_pokryt_busemX ON POKRYTI 
    ( 
     inv_cislo ASC 
    ) 
;
CREATE INDEX je_pokryt_lodiX ON POKRYTI 
    ( 
     LODID ASC 
    ) 
;
CREATE INDEX je_pokryt_spojX ON POKRYTI 
    ( 
     LID ASC , 
     SPID ASC 
    ) 
;

ALTER TABLE POKRYTI 
    ADD CONSTRAINT POKRYTO_PK PRIMARY KEY ( datum_pokryti, LID, SPID ) ;



CREATE TABLE REZERVACE 
    ( 
     DATUM_RES DATE  NOT NULL , 
     LODID NUMBER (10)  NOT NULL , 
     ZID NUMBER (10)  NOT NULL 
    ) 
;



COMMENT ON TABLE REZERVACE IS 'Kniha rezervaci lodi zakaznikum'
;


COMMENT ON COLUMN REZERVACE.DATUM_RES IS 'datum, na kdy je lod rezervovana. Lod se vyzvedava rano v 7 hod. a odevzdava nejpozdeji ve 22 hod.' 
;

COMMENT ON COLUMN REZERVACE.LODID IS 'odkaz na lod, ktera je rezervovana' 
;
CREATE UNIQUE INDEX REZERVACE2_PKX ON REZERVACE 
    ( 
     LODID ASC , 
     DATUM_RES ASC , 
     ZID ASC 
    ) 
;
CREATE INDEX rezervuje_se_coX ON REZERVACE 
    ( 
     LODID ASC 
    ) 
;
CREATE INDEX rezervuje_si_kdoX ON REZERVACE 
    ( 
     ZID ASC 
    ) 
;

ALTER TABLE REZERVACE 
    ADD CONSTRAINT REZERVACE2_PK PRIMARY KEY ( LODID, DATUM_RES, ZID ) ;



CREATE TABLE SPOJ 
    ( 
     LID NUMBER (10)  NOT NULL , 
     SPID NUMBER (10)  NOT NULL , 
     SCAS INTERVAL DAY (0) TO SECOND (0) 
    ) 
;



COMMENT ON TABLE SPOJ IS 'na kazde lince je v dany den nekolik spoju, ktere vyjizdeji v dany cas'
;


COMMENT ON COLUMN SPOJ.LID IS 'na kterou linku spoj patri' 
;

COMMENT ON COLUMN SPOJ.SPID IS 'cislo spoje' 
;

COMMENT ON COLUMN SPOJ.SCAS IS 'cas v danem dni, kdy spoj vyjizdi na danou linku' 
;
CREATE UNIQUE INDEX SPOJ_LID_SPID_PKX ON SPOJ 
    ( 
     LID ASC , 
     SPID ASC 
    ) 
;
CREATE INDEX je_na_linceX ON SPOJ 
    ( 
     LID ASC 
    ) 
;

ALTER TABLE SPOJ 
    ADD CONSTRAINT SPOJ_LID_SPID_PK PRIMARY KEY ( LID, SPID ) ;



CREATE TABLE ZAKAZNIK 
    ( 
     ZID NUMBER (10)  NOT NULL , 
     PRIJMENI VARCHAR2 (20) , 
     JMENOZ VARCHAR2 (20) , 
     ADRESA VARCHAR2 (20) , 
     CREDITLIMIT NUMBER 
    ) 
;



COMMENT ON TABLE ZAKAZNIK IS 'Registrovani zakaznici si mohou rezervovat na cely den lod a objednat si plavbu s pruvodcem na interval v danem dnu. '
;

CREATE UNIQUE INDEX ZAKAZNIK_PKX ON ZAKAZNIK 
    ( 
     ZID ASC 
    ) 
;

ALTER TABLE ZAKAZNIK 
    ADD CONSTRAINT ZAKAZNIK_PK PRIMARY KEY ( ZID ) ;



CREATE TABLE ZAZEMI 
    ( 
     id_zazemi NUMBER (10)  NOT NULL , 
     adresa VARCHAR2 (50)  NOT NULL , 
     pocet_stani INTEGER , 
     uroven_moznych_oprav INTEGER CONSTRAINT mozne_typy_oprav CHECK ( uroven_moznych_oprav IN (1, 2, 3, 4, 5, 6)) , 
     pocet_opravnych_doku INTEGER , 
     pocet_kotvist INTEGER 
    ) 
;



COMMENT ON TABLE ZAZEMI IS 'zazemi pro autobusy nebo lode. Zazemi se specializuje na depo a pristaviste.'
;


COMMENT ON COLUMN ZAZEMI.id_zazemi IS 'identifikator zazemi (depa, nebo pristaviste)' 
;

COMMENT ON COLUMN ZAZEMI.adresa IS 'adresa depa nebo pristaviste' 
;

COMMENT ON COLUMN ZAZEMI.pocet_stani IS 'kapacita depa v poctu stani' 
;

COMMENT ON COLUMN ZAZEMI.uroven_moznych_oprav IS 'maximalni uroven moznych oprav, ktere se v depu mohou provadet. Mozne hodnoty jsou 1 (cisteni), 2 (tankovani)), 3(bezna udrzba), 4 (vymena dilu vcetne vymeny oleje), 5 (stredni oprava), 6(generalka).
Vyznam - dany typ a vsechny nizsi' 
;

COMMENT ON COLUMN ZAZEMI.pocet_opravnych_doku IS 'pocet doku, ve ktery lze provadet opravy lodi' 
;

COMMENT ON COLUMN ZAZEMI.pocet_kotvist IS 'kapacita pristaviste, kolik ma kotvist' 
;
CREATE UNIQUE INDEX "zazemi PKX" ON ZAZEMI 
    ( 
     id_zazemi ASC 
    ) 
;

ALTER TABLE ZAZEMI 
    ADD CONSTRAINT "zazemi PK" PRIMARY KEY ( id_zazemi ) ;



CREATE TABLE kalendar_namornika 
    ( 
     NID NUMBER (10)  NOT NULL , 
     Od DATE  NOT NULL , 
     do DATE  NOT NULL 
    ) 
;



COMMENT ON TABLE kalendar_namornika IS 'kalendar namornika urcuje, kdy je namornik nasazen bud na pravidelny spoj, nebo na plavbu s pruvodce.'
;


COMMENT ON COLUMN kalendar_namornika.NID IS 'kteremu namornikovi kalendar patri' 
;

COMMENT ON COLUMN kalendar_namornika.Od IS 'den a cas, kdy nasazeni namornika zacina' 
;

COMMENT ON COLUMN kalendar_namornika.do IS 'den a cas, kdy nasazeni namornika konci' 
;
CREATE UNIQUE INDEX "kalendar_namornika PKX" ON kalendar_namornika 
    ( 
     NID ASC , 
     Od ASC , 
     do ASC 
    ) 
;
CREATE INDEX patri_namornikoviX ON kalendar_namornika 
    ( 
     NID ASC 
    ) 
;

ALTER TABLE kalendar_namornika 
    ADD CONSTRAINT "kalendar_namornika PK" PRIMARY KEY ( NID, Od, do ) ;




ALTER TABLE SPOJ 
    ADD CONSTRAINT je_na_lince FOREIGN KEY 
    ( 
     LID
    ) 
    REFERENCES LINKA 
    ( 
     LID
    ) 
;


ALTER TABLE POKRYTI 
    ADD CONSTRAINT je_pokryt_busem FOREIGN KEY 
    ( 
     inv_cislo
    ) 
    REFERENCES AUTOBUS 
    ( 
     inv_cislo
    ) 
;


ALTER TABLE POKRYTI 
    ADD CONSTRAINT je_pokryt_kym FOREIGN KEY 
    ( 
     NID,
     Od,
     do
    ) 
    REFERENCES kalendar_namornika 
    ( 
     NID,
     Od,
     do
    ) 
;


ALTER TABLE POKRYTI 
    ADD CONSTRAINT je_pokryt_lodi FOREIGN KEY 
    ( 
     LODID
    ) 
    REFERENCES LOD 
    ( 
     LODID
    ) 
;


ALTER TABLE POKRYTI 
    ADD CONSTRAINT je_pokryt_spoj FOREIGN KEY 
    ( 
     LID,
     SPID
    ) 
    REFERENCES SPOJ 
    ( 
     LID,
     SPID
    ) 
;


ALTER TABLE LOD 
    ADD CONSTRAINT kotvi_kde FOREIGN KEY 
    ( 
     id_zazemi
    ) 
    REFERENCES ZAZEMI 
    ( 
     id_zazemi
    ) 
;


ALTER TABLE AUTOBUS 
    ADD CONSTRAINT ma_depo FOREIGN KEY 
    ( 
     id_zazemi
    ) 
    REFERENCES ZAZEMI 
    ( 
     id_zazemi
    ) 
;


ALTER TABLE kalendar_namornika 
    ADD CONSTRAINT patri_namornikovi FOREIGN KEY 
    ( 
     NID
    ) 
    REFERENCES NAMORNIK 
    ( 
     NID
    ) 
;


ALTER TABLE PLAVBA_S_PRUVODCEM 
    ADD CONSTRAINT plavi_se_kdo FOREIGN KEY 
    ( 
     ZID
    ) 
    REFERENCES ZAKAZNIK 
    ( 
     ZID
    ) 
;


ALTER TABLE PLAVBA_S_PRUVODCEM 
    ADD CONSTRAINT plavi_se_na_cem FOREIGN KEY 
    ( 
     ID_LOD
    ) 
    REFERENCES LOD 
    ( 
     LODID
    ) 
;


ALTER TABLE PLAVBA_S_PRUVODCEM 
    ADD CONSTRAINT plavi_se_s_kym FOREIGN KEY 
    ( 
     NID,
     Od,
     do
    ) 
    REFERENCES kalendar_namornika 
    ( 
     NID,
     Od,
     do
    ) 
;


ALTER TABLE REZERVACE 
    ADD CONSTRAINT rezervuje_se_co FOREIGN KEY 
    ( 
     LODID
    ) 
    REFERENCES LOD 
    ( 
     LODID
    ) 
;


ALTER TABLE REZERVACE 
    ADD CONSTRAINT rezervuje_si_kdo FOREIGN KEY 
    ( 
     ZID
    ) 
    REFERENCES ZAKAZNIK 
    ( 
     ZID
    ) 
;



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            11
-- CREATE INDEX                            27
-- ALTER TABLE                             27
-- CREATE VIEW                              0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE STRUCTURED TYPE                   0
-- CREATE COLLECTION TYPE                   0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0