text: -- Generated by Oracle SQL Developer Data Modeler 20.3.0.283.0710
  --   at:        2021-01-20 13:42:56 CET
  --   site:      Oracle Database 12c
  --   type:      Oracle Database 12c
  
  
  
  -- predefined type, no DDL - MDSYS.SDO_GEOMETRY
  
  -- predefined type, no DDL - XMLTYPE
  
  CREATE TABLE distributer (
      iddist     INTEGER NOT NULL,
      nazivdist  VARCHAR2(32 CHAR) NOT NULL
  );
  
  ALTER TABLE distributer ADD CONSTRAINT distributer_pk PRIMARY KEY ( iddist );
  
  CREATE TABLE isporucuje (
      potpisanpud_distributer_iddist  INTEGER NOT NULL,
      potpisanpud_produgovor_idugo    INTEGER NOT NULL,
      obuhvataproiz_idugo             INTEGER NOT NULL,
      obuhvataproiz_idproiz           INTEGER NOT NULL,
      prodavnica_idprod               INTEGER NOT NULL
  );
  
  ALTER TABLE isporucuje
      ADD CONSTRAINT isporucuje_pk PRIMARY KEY ( potpisanpud_distributer_iddist,
                                                 potpisanpud_produgovor_idugo,
                                                 obuhvataproiz_idugo,
                                                 obuhvataproiz_idproiz );
  
  CREATE TABLE menadzer (
      idzap INTEGER NOT NULL
  );
  
  ALTER TABLE menadzer ADD CONSTRAINT menadzer_pk PRIMARY KEY ( idzap );
  
  CREATE TABLE nabugovor (
      idugo               INTEGER NOT NULL,
      distributer_iddist  INTEGER NOT NULL
  );
  
  ALTER TABLE nabugovor ADD CONSTRAINT nabugovor_pk PRIMARY KEY ( idugo );
  
  CREATE TABLE obuhvataproiz (
      kolicina          INTEGER,
      produgovor_idugo  INTEGER NOT NULL,
      proizvod_idproiz  INTEGER NOT NULL
  );
  
  ALTER TABLE obuhvataproiz ADD CONSTRAINT obuhvataproiz_pk PRIMARY KEY ( produgovor_idugo,
                                                                          proizvod_idproiz );
  
  CREATE TABLE obuhvatasir (
      nabugovor_idugo  INTEGER NOT NULL,
      sirovina_idsir   INTEGER NOT NULL,
      kolicina         FLOAT,
      jedinica         VARCHAR2(8 CHAR)
  );
  
  ALTER TABLE obuhvatasir ADD CONSTRAINT obuhvatasir_pk PRIMARY KEY ( nabugovor_idugo,
                                                                      sirovina_idsir );
  
  CREATE TABLE pekar (
      idzap INTEGER NOT NULL
  );
  
  ALTER TABLE pekar ADD CONSTRAINT pekar_pk PRIMARY KEY ( idzap );
  
  CREATE TABLE potpisanpud (
      distributer_iddist  INTEGER NOT NULL,
      produgovor_idugo    INTEGER NOT NULL
  );
  
  ALTER TABLE potpisanpud ADD CONSTRAINT potpisanpud_pk PRIMARY KEY ( distributer_iddist,
                                                                      produgovor_idugo );
  
  CREATE TABLE pravi (
      pekar_idzap       INTEGER NOT NULL,
      proizvod_idproiz  INTEGER NOT NULL
  );
  
  ALTER TABLE pravi ADD CONSTRAINT pravi_pk PRIMARY KEY ( pekar_idzap,
                                                          proizvod_idproiz );
  
  CREATE TABLE prodavnica (
      idprod      INTEGER NOT NULL,
      nazivprod   VARCHAR2(32 CHAR) NOT NULL,
      adresaprod  VARCHAR2(128 CHAR)
  );
  
  ALTER TABLE prodavnica ADD CONSTRAINT prodavnica_pk PRIMARY KEY ( idprod );
  
  CREATE TABLE produgovor (
      idugo              INTEGER NOT NULL,
      prodavnica_idprod  INTEGER NOT NULL
  );
  
  ALTER TABLE produgovor ADD CONSTRAINT produgovor_pk PRIMARY KEY ( idugo );
  
  CREATE TABLE proizvod (
      idproiz     INTEGER NOT NULL,
      masaproiz   FLOAT,
      nazivproiz  VARCHAR2(32 CHAR) NOT NULL
  );
  
  ALTER TABLE proizvod ADD CONSTRAINT proizvod_pk PRIMARY KEY ( idproiz );
  
  CREATE TABLE sastav (
      proizvod_idproiz  INTEGER NOT NULL,
      sirovina_idsir    INTEGER NOT NULL
  );
  
  ALTER TABLE sastav ADD CONSTRAINT sastav_pk PRIMARY KEY ( proizvod_idproiz,
                                                            sirovina_idsir );
  
  CREATE TABLE sirovina (
      idsir     INTEGER NOT NULL,
      nazivsir  VARCHAR2(32 CHAR) NOT NULL
  );
  
  ALTER TABLE sirovina ADD CONSTRAINT sirovina_pk PRIMARY KEY ( idsir );
  
  CREATE TABLE ugovor (
      idugo           INTEGER NOT NULL,
      tipugo          VARCHAR2(16 CHAR) NOT NULL,
      menadzer_idzap  INTEGER NOT NULL
  );
  
  ALTER TABLE ugovor
      ADD CONSTRAINT ch_inh_ugovor CHECK ( tipugo IN ( 'NabUgovor', 'ProdUgovor', 'Ugovor' ) );
  
  ALTER TABLE ugovor ADD CONSTRAINT ugovor_pk PRIMARY KEY ( idugo );
  
  CREATE TABLE zaposleni (
      idzap   INTEGER NOT NULL,
      imezap  VARCHAR2(32 CHAR) NOT NULL,
      tipzap  VARCHAR2(16 CHAR) NOT NULL
  );
  
  ALTER TABLE zaposleni
      ADD CONSTRAINT ch_inh_zaposleni CHECK ( tipzap IN ( 'Menadzer', 'Pekar', 'Zaposleni' ) );
  
  ALTER TABLE zaposleni ADD CONSTRAINT zaposleni_pk PRIMARY KEY ( idzap );
  
  ALTER TABLE isporucuje
      ADD CONSTRAINT isporucuje_obuhvataproiz_fk FOREIGN KEY ( obuhvataproiz_idugo,
                                                               obuhvataproiz_idproiz )
          REFERENCES obuhvataproiz ( produgovor_idugo,
                                     proizvod_idproiz );
  
  ALTER TABLE isporucuje
      ADD CONSTRAINT isporucuje_potpisanpud_fk FOREIGN KEY ( potpisanpud_distributer_iddist,
                                                             potpisanpud_produgovor_idugo )
          REFERENCES potpisanpud ( distributer_iddist,
                                   produgovor_idugo );
  
  ALTER TABLE isporucuje
      ADD CONSTRAINT isporucuje_prodavnica_fk FOREIGN KEY ( prodavnica_idprod )
          REFERENCES prodavnica ( idprod );
  
  ALTER TABLE menadzer
      ADD CONSTRAINT menadzer_zaposleni_fk FOREIGN KEY ( idzap )
          REFERENCES zaposleni ( idzap );
  
  ALTER TABLE nabugovor
      ADD CONSTRAINT nabugovor_distributer_fk FOREIGN KEY ( distributer_iddist )
          REFERENCES distributer ( iddist );
  
  ALTER TABLE nabugovor
      ADD CONSTRAINT nabugovor_ugovor_fk FOREIGN KEY ( idugo )
          REFERENCES ugovor ( idugo );
  
  ALTER TABLE obuhvataproiz
      ADD CONSTRAINT obuhvataproiz_produgovor_fk FOREIGN KEY ( produgovor_idugo )
          REFERENCES produgovor ( idugo );
  
  ALTER TABLE obuhvataproiz
      ADD CONSTRAINT obuhvataproiz_proizvod_fk FOREIGN KEY ( proizvod_idproiz )
          REFERENCES proizvod ( idproiz );
  
  ALTER TABLE obuhvatasir
      ADD CONSTRAINT obuhvatasir_nabugovor_fk FOREIGN KEY ( nabugovor_idugo )
          REFERENCES nabugovor ( idugo );
  
  ALTER TABLE obuhvatasir
      ADD CONSTRAINT obuhvatasir_sirovina_fk FOREIGN KEY ( sirovina_idsir )
          REFERENCES sirovina ( idsir );
  
  ALTER TABLE pekar
      ADD CONSTRAINT pekar_zaposleni_fk FOREIGN KEY ( idzap )
          REFERENCES zaposleni ( idzap );
  
  ALTER TABLE potpisanpud
      ADD CONSTRAINT potpisanpud_distributer_fk FOREIGN KEY ( distributer_iddist )
          REFERENCES distributer ( iddist );
  
  ALTER TABLE potpisanpud
      ADD CONSTRAINT potpisanpud_produgovor_fk FOREIGN KEY ( produgovor_idugo )
          REFERENCES produgovor ( idugo );
  
  ALTER TABLE pravi
      ADD CONSTRAINT pravi_pekar_fk FOREIGN KEY ( pekar_idzap )
          REFERENCES pekar ( idzap );
  
  ALTER TABLE pravi
      ADD CONSTRAINT pravi_proizvod_fk FOREIGN KEY ( proizvod_idproiz )
          REFERENCES proizvod ( idproiz );
  
  ALTER TABLE produgovor
      ADD CONSTRAINT produgovor_prodavnica_fk FOREIGN KEY ( prodavnica_idprod )
          REFERENCES prodavnica ( idprod );
  
  ALTER TABLE produgovor
      ADD CONSTRAINT produgovor_ugovor_fk FOREIGN KEY ( idugo )
          REFERENCES ugovor ( idugo );
  
  ALTER TABLE sastav
      ADD CONSTRAINT sastav_proizvod_fk FOREIGN KEY ( proizvod_idproiz )
          REFERENCES proizvod ( idproiz );
  
  ALTER TABLE sastav
      ADD CONSTRAINT sastav_sirovina_fk FOREIGN KEY ( sirovina_idsir )
          REFERENCES sirovina ( idsir );
  
  ALTER TABLE ugovor
      ADD CONSTRAINT ugovor_menadzer_fk FOREIGN KEY ( menadzer_idzap )
          REFERENCES menadzer ( idzap );
  
  CREATE OR REPLACE TRIGGER arc_fkarc_1_nabugovor BEFORE
      INSERT OR UPDATE OF idugo ON nabugovor
      FOR EACH ROW
  DECLARE
      d VARCHAR2(16 CHAR);
  BEGIN
      SELECT
          a.tipugo
      INTO d
      FROM
          ugovor a
      WHERE
          a.idugo = :new.idugo;
  
      IF ( d IS NULL OR d <> 'NabUgovor' ) THEN
          raise_application_error(-20223,
                                 'FK NabUgovor_Ugovor_FK in Table NabUgovor violates Arc constraint on Table Ugovor - discriminator column tipUgo doesn''t have value ''NabUgovor''');
      END IF;
  
  EXCEPTION
      WHEN no_data_found THEN
          NULL;
      WHEN OTHERS THEN
          RAISE;
  END;
  /
  
  CREATE OR REPLACE TRIGGER arc_fkarc_1_produgovor BEFORE
      INSERT OR UPDATE OF idugo ON produgovor
      FOR EACH ROW
  DECLARE
      d VARCHAR2(16 CHAR);
  BEGIN
      SELECT
          a.tipugo
      INTO d
      FROM
          ugovor a
      WHERE
          a.idugo = :new.idugo;
  
      IF ( d IS NULL OR d <> 'ProdUgovor' ) THEN
          raise_application_error(-20223,
                                 'FK ProdUgovor_Ugovor_FK in Table ProdUgovor violates Arc constraint on Table Ugovor - discriminator column tipUgo doesn''t have value ''ProdUgovor''');
      END IF;
  
  EXCEPTION
      WHEN no_data_found THEN
          NULL;
      WHEN OTHERS THEN
          RAISE;
  END;
  /
  
  CREATE OR REPLACE TRIGGER arc_fkarc_2_pekar BEFORE
      INSERT OR UPDATE OF idzap ON pekar
      FOR EACH ROW
  DECLARE
      d VARCHAR2(16 CHAR);
  BEGIN
      SELECT
          a.tipzap
      INTO d
      FROM
          zaposleni a
      WHERE
          a.idzap = :new.idzap;
  
      IF ( d IS NULL OR d <> 'Pekar' ) THEN
          raise_application_error(-20223,
                                 'FK Pekar_Zaposleni_FK in Table Pekar violates Arc constraint on Table Zaposleni - discriminator column tipZap doesn''t have value ''Pekar''');
      END IF;
  
  EXCEPTION
      WHEN no_data_found THEN
          NULL;
      WHEN OTHERS THEN
          RAISE;
  END;
  /
  
  CREATE OR REPLACE TRIGGER arc_fkarc_2_menadzer BEFORE
      INSERT OR UPDATE OF idzap ON menadzer
      FOR EACH ROW
  DECLARE
      d VARCHAR2(16 CHAR);
  BEGIN
      SELECT
          a.tipzap
      INTO d
      FROM
          zaposleni a
      WHERE
          a.idzap = :new.idzap;
  
      IF ( d IS NULL OR d <> 'Menadzer' ) THEN
          raise_application_error(-20223,
                                 'FK Menadzer_Zaposleni_FK in Table Menadzer violates Arc constraint on Table Zaposleni - discriminator column tipZap doesn''t have value ''Menadzer''');
      END IF;
  
  EXCEPTION
      WHEN no_data_found THEN
          NULL;
      WHEN OTHERS THEN
          RAISE;
  END;
  /
  
  
  
  -- Oracle SQL Developer Data Modeler Summary Report: 
  -- 
  -- CREATE TABLE                            16
  -- CREATE INDEX                             0
  -- ALTER TABLE                             38
  -- CREATE VIEW                              0
  -- ALTER VIEW                               0
  -- CREATE PACKAGE                           0
  -- CREATE PACKAGE BODY                      0
  -- CREATE PROCEDURE                         0
  -- CREATE FUNCTION                          0
  -- CREATE TRIGGER                           4
  -- ALTER TRIGGER                            0
  -- CREATE COLLECTION TYPE                   0
  -- CREATE STRUCTURED TYPE                   0
  -- CREATE STRUCTURED TYPE BODY              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 MATERIALIZED VIEW LOG             0
  -- CREATE SYNONYM                           0
  -- CREATE TABLESPACE                        0
  -- CREATE USER                              0
  -- 
  -- DROP TABLESPACE                          0
  -- DROP DATABASE                            0
  -- 
  -- REDACTION POLICY                         0
  -- TSDP POLICY                              0
  -- 
  -- ORDS DROP SCHEMA                         0
  -- ORDS ENABLE SCHEMA                       0
  -- ORDS ENABLE OBJECT                       0
  -- 
  -- ERRORS                                   0
  -- WARNINGS                                 0
  