-- Generated by Oracle SQL Developer Data Modeler 4.1.3.901
--   at:        2019-10-10 15:52:38 CEST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g




CREATE TABLE Company
  (
    cus_Custid        INTEGER NOT NULL ,
    Com_Num_Employees INTEGER
  ) ;
ALTER TABLE Company ADD CONSTRAINT Company_PK PRIMARY KEY ( cus_Custid ) ;


CREATE TABLE ContactPerson
  (
    Company_cus_Custid INTEGER NOT NULL ,
    PName              VARCHAR2 (10) NOT NULL ,
    Email              VARCHAR2 (10) NOT NULL ,
    Tel                VARCHAR2 (10) NOT NULL
  ) ;
ALTER TABLE ContactPerson ADD CONSTRAINT ContactPerson_PK PRIMARY KEY ( PName ) ;


CREATE TABLE Customer
  (
    cus_Custid    INTEGER NOT NULL ,
    cus_Cname     VARCHAR2 (20) NOT NULL ,
    cus_Telephone VARCHAR2 (10) ,
    CusType       CHAR (1) NOT NULL
  ) ;
ALTER TABLE Customer ADD CONSTRAINT Customer_PK PRIMARY KEY ( cus_Custid ) ;


CREATE TABLE Item
  (
    Quantity       INTEGER NOT NULL ,
    Price          NUMBER NOT NULL ,
    Order_Ordid    INTEGER NOT NULL ,
    Product_Prodid VARCHAR2 (10) NOT NULL
  ) ;
ALTER TABLE Item ADD CONSTRAINT Item_PK PRIMARY KEY ( Order_Ordid, Product_Prodid ) ;


CREATE TABLE "Order"
  (
    Ordid               INTEGER NOT NULL ,
    OrdDate             DATE NOT NULL ,
    Customer_cus_Custid INTEGER NOT NULL
  ) ;
ALTER TABLE "Order" ADD CONSTRAINT Order_PK PRIMARY KEY ( Ordid ) ;


CREATE TABLE Person
  (
    cus_Custid INTEGER NOT NULL ,
    Per_Gender VARCHAR2 (1) NOT NULL
  ) ;
ALTER TABLE Person ADD CONSTRAINT Person_PK PRIMARY KEY ( cus_Custid ) ;


CREATE TABLE Product
  (
    Prodid   VARCHAR2 (10) NOT NULL ,
    ProdName VARCHAR2 (10) NOT NULL ,
    LongName VARCHAR2 (50)
  ) ;
ALTER TABLE Product ADD CONSTRAINT Product_PK PRIMARY KEY ( Prodid ) ;
ALTER TABLE Product ADD CONSTRAINT Product__UN UNIQUE ( ProdName ) ;


ALTER TABLE ContactPerson ADD CONSTRAINT ContactPerson_Company_FK FOREIGN KEY ( Company_cus_Custid ) REFERENCES Company ( cus_Custid ) ;

ALTER TABLE Person ADD CONSTRAINT FK_ASS_1 FOREIGN KEY ( cus_Custid ) REFERENCES Customer ( cus_Custid ) ;

ALTER TABLE Company ADD CONSTRAINT FK_ASS_2 FOREIGN KEY ( cus_Custid ) REFERENCES Customer ( cus_Custid ) ;

ALTER TABLE Item ADD CONSTRAINT Item_Order_FK FOREIGN KEY ( Order_Ordid ) REFERENCES "Order" ( Ordid ) ;

ALTER TABLE Item ADD CONSTRAINT Item_Product_FK FOREIGN KEY ( Product_Prodid ) REFERENCES Product ( Prodid ) ;

ALTER TABLE "Order" ADD CONSTRAINT Order_Customer_FK FOREIGN KEY ( Customer_cus_Custid ) REFERENCES Customer ( cus_Custid ) ;

CREATE OR REPLACE TRIGGER FKNTM_Order BEFORE
  UPDATE OF Customer_cus_Custid ON "Order" BEGIN raise_application_error(-20225,'Non Transferable FK constraint  on table "Order" is violated');
END;
/

CREATE OR REPLACE TRIGGER ARC_FKArc_1_Person BEFORE
  INSERT OR
  UPDATE OF cus_Custid ON Person FOR EACH ROW DECLARE d CHAR (1);
  BEGIN
    SELECT A.CusType INTO d FROM Customer A WHERE A.cus_Custid = :new.cus_Custid;
    IF (d IS NULL OR d <> 'P') THEN
      raise_application_error(-20223,'FK FK_ASS_1 in Table Person violates Arc constraint on Table Customer - discriminator column CusType doesn''t have value ''P''');
    END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN OTHERS THEN
    RAISE;
  END;
  /
CREATE OR REPLACE TRIGGER ARC_FKArc_1_Company BEFORE
  INSERT OR
  UPDATE OF cus_Custid ON Company FOR EACH ROW DECLARE d CHAR (1);
  BEGIN
    SELECT A.CusType INTO d FROM Customer A WHERE A.cus_Custid = :new.cus_Custid;
    IF (d IS NULL OR d <> 'C') THEN
      raise_application_error(-20223,'FK FK_ASS_2 in Table Company violates Arc constraint on Table Customer - discriminator column CusType doesn''t have value ''C''');
    END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  WHEN OTHERS THEN
    RAISE;
  END;
  /


-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             7
-- CREATE INDEX                             0
-- ALTER TABLE                             14
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           3
-- 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 SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0