REM ----------------------------------------------
REM ------------    Delete Tables    -------------
REM ----------------------------------------------

DROP TABLE M4_ACCESS_T       CASCADE CONSTRAINTS ;
DROP TABLE HCI_COORD_T       CASCADE CONSTRAINTS ;

DROP TABLE CON_DATATYPE_T    CASCADE CONSTRAINTS ;
DROP TABLE COL_DATATYPE_T    CASCADE CONSTRAINTS ;
DROP TABLE OP_GROUP_T        CASCADE CONSTRAINTS ;
DROP TABLE CONCEPT_T         CASCADE CONSTRAINTS ;
DROP TABLE CONCEPTISA_T      CASCADE CONSTRAINTS ;
DROP TABLE PROJECTION_T      CASCADE CONSTRAINTS ;
DROP TABLE MCFEATURE_T       CASCADE CONSTRAINTS ;
DROP TABLE RELATION_T        CASCADE CONSTRAINTS ;
DROP TABLE RELATIONISA_T     CASCADE CONSTRAINTS ;
DROP TABLE COLUMNSET_T       CASCADE CONSTRAINTS ;
DROP TABLE BASEATTRIB_T      CASCADE CONSTRAINTS ;
DROP TABLE COLUMN_T          CASCADE CONSTRAINTS ;
DROP TABLE KEYHEAD_T         CASCADE CONSTRAINTS ;
DROP TABLE KEYMEMBER_T       CASCADE CONSTRAINTS ;
DROP TABLE CSSTATIST_T       CASCADE CONSTRAINTS ;
DROP TABLE COLSTATIST1_T     CASCADE CONSTRAINTS ;
DROP TABLE COLSTATIST2_T     CASCADE CONSTRAINTS ;
DROP TABLE VALUE_T           CASCADE CONSTRAINTS ;
DROP TABLE ROLERESTRICTION_T CASCADE CONSTRAINTS ;
DROP TABLE CASE_T            CASCADE CONSTRAINTS ;
DROP TABLE OPERATOR_T        CASCADE CONSTRAINTS ;
DROP TABLE PARAMETER_T       CASCADE CONSTRAINTS ;
DROP TABLE STEP_T            CASCADE CONSTRAINTS ;
DROP TABLE STEPSEQUENCE_T    CASCADE CONSTRAINTS ;
DROP TABLE DOCU_T            CASCADE CONSTRAINTS ;

DROP TABLE OP_PARAMS_T       CASCADE CONSTRAINTS ;
DROP TABLE OP_CONSTR_T       CASCADE CONSTRAINTS ;
DROP TABLE OP_COND_T         CASCADE CONSTRAINTS ;
DROP TABLE OP_ASSERT_T       CASCADE CONSTRAINTS ;
DROP TABLE CHAIN_T           CASCADE CONSTRAINTS ;
DROP TABLE BA_CONCEPT_T      CASCADE CONSTRAINTS ;
DROP TABLE BA_COLUMN_T       CASCADE CONSTRAINTS ;

DROP TABLE M4TRASH_T	     CASCADE CONSTRAINTS ;
DROP TABLE DBTRASH_T         CASCADE CONSTRAINTS ;


REM ----------------------------------------------
REM ------------    Create Tables    -------------
REM ----------------------------------------------

REM Table CASE_T
CREATE TABLE CASE_T (
  CA_ID          NUMBER         NOT NULL,
  CA_NAME        VARCHAR2 (100) NOT NULL,
  CA_MODE        VARCHAR2 (15)  NOT NULL,
  CONSTRAINT CAID_PK
    PRIMARY KEY ( CA_ID ),
  CONSTRAINT CANAME_UQ
    UNIQUE (CA_NAME),
  CONSTRAINT CAMODE_CH
    CHECK (CA_MODE IN ('DESIGN', 'TEST', 'FINAL'))
);


REM Table CON_DATATYPE_T
CREATE TABLE CON_DATATYPE_T (
  CONDT_ID    NUMBER        NOT NULL,
  CONDT_NAME  VARCHAR2 (100)  NOT NULL,
  CONSTRAINT CONDTID_PK
    PRIMARY KEY ( CONDT_ID )
);


REM Table COL_DATATYPE_T
CREATE TABLE COL_DATATYPE_T (
  COLDT_ID    NUMBER        NOT NULL,
  COLDT_NAME  VARCHAR2 (100)  NOT NULL,
  CONSTRAINT COLDTID_PK
    PRIMARY KEY ( COLDT_ID )
);


REM Table CONCEPT_T
CREATE TABLE CONCEPT_T (
  CON_ID           NUMBER          NOT NULL,
  CON_CAID         NUMBER,
  CON_NAME         VARCHAR2 (100)  NOT NULL,
  CON_TYPE         VARCHAR2 (10)   NOT NULL,
  CONSTRAINT CONID_PK
    PRIMARY KEY ( CON_ID ),
  CONSTRAINT CONCASE_CASE_FK
    FOREIGN KEY ( CON_CAID )
      REFERENCES CASE_T (CA_ID),
  CONSTRAINT CON_CAID_NAME_UQ
    UNIQUE (CON_CAID, CON_NAME),
  CONSTRAINT CONTYPE_CH
    CHECK (CON_TYPE IN ('BASE', 'DB', 'MINING'))
);


REM Table COLUMNSET_T
CREATE TABLE COLUMNSET_T (
  CS_ID       NUMBER          NOT NULL,
  CS_SCHEMA   VARCHAR2 (100)  NOT NULL,
  CS_NAME     VARCHAR2 (100)  NOT NULL,
  CS_TYPE     VARCHAR2 (5)    NOT NULL,
  CS_SQL      VARCHAR2 (4000),
  CS_CONID    NUMBER,
  CS_MSBRANCH VARCHAR2 (1000),
  CONSTRAINT CSID_PK
    PRIMARY KEY ( CS_ID ),
  CONSTRAINT CSCONCEPT_FK
    FOREIGN KEY (CS_CONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT CSTYPE_CH
    CHECK (CS_TYPE IN ('T', 'V'))
);


REM Table CONCEPTISA_T
CREATE TABLE CONCEPTISA_T (
  CISA_ID          NUMBER        NOT NULL,
  CISA_SUPERCONID  NUMBER        NOT NULL,
  CISA_SUBCONID    NUMBER        NOT NULL,
  CONSTRAINT CISAID_PK
    PRIMARY KEY ( CISA_ID ),
  CONSTRAINT CISASUBCONID_FK
    FOREIGN KEY (CISA_SUBCONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT CISASUPERCONID_FK
    FOREIGN KEY (CISA_SUPERCONID)
      REFERENCES CONCEPT_T (CON_ID)
);


REM Table PROJECTION_T
CREATE TABLE PROJECTION_T (
  PRO_ID         NUMBER        NOT NULL,
  PRO_FROMCONID  NUMBER        NOT NULL,
  PRO_TOCONID    NUMBER        NOT NULL,
  CONSTRAINT PROID_PK
    PRIMARY KEY ( PRO_ID ),
  CONSTRAINT PROFROMCONID_FK
    FOREIGN KEY (PRO_FROMCONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT PROTOCONID_FK
    FOREIGN KEY (PRO_TOCONID)
      REFERENCES CONCEPT_T (CON_ID)
);


REM Table MCFEATURE_T
CREATE TABLE MCFEATURE_T (
  MCF_ID     NUMBER        NOT NULL,
  MCF_NAME   VARCHAR2 (100)  NOT NULL,
  MCF_CONID  NUMBER,
  MCF_VALID  VARCHAR2(5),
  CONSTRAINT MCFID_PK
    PRIMARY KEY ( MCF_ID ),
  CONSTRAINT MCFCONID_FK
    FOREIGN KEY (MCF_CONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT MCFCONNAME_UQ
    UNIQUE (MCF_CONID, MCF_NAME) USING INDEX
);


REM Table COLUMN_T
CREATE TABLE COLUMN_T (
  COL_ID       NUMBER         NOT NULL,
  COL_NAME     VARCHAR2 (100) NOT NULL,
  COL_CSID     NUMBER,
  COL_COLDTID  NUMBER         NOT NULL,
  COL_SQL      VARCHAR2 (4000),
  CONSTRAINT COLID_PK
    PRIMARY KEY ( COL_ID ),
  CONSTRAINT COLCOLDTID_FK
    FOREIGN KEY (COL_COLDTID)
      REFERENCES COL_DATATYPE_T (COLDT_ID),
  CONSTRAINT COLCSID_FK
    FOREIGN KEY (COL_CSID)
      REFERENCES COLUMNSET_T (CS_ID)
);


REM Table KEYHEAD_T
CREATE TABLE KEYHEAD_T (
  KH_ID      NUMBER        NOT NULL,
  KH_NAME    VARCHAR2 (100)  NOT NULL,
  KH_PKCSID  NUMBER,
  KH_FKCSID  NUMBER,
  CONSTRAINT KHID_PK
    PRIMARY KEY ( KH_ID ),
  CONSTRAINT KHFKCSID_FK
    FOREIGN KEY (KH_FKCSID)
      REFERENCES COLUMNSET_T (CS_ID),
  CONSTRAINT KHPKCSID_FK
    FOREIGN KEY (KH_PKCSID)
      REFERENCES COLUMNSET_T (CS_ID)
);


REM Table KEYMEMBER_T
CREATE TABLE KEYMEMBER_T (
  KM_ID       NUMBER        NOT NULL,
  KM_KHID     NUMBER        NOT NULL,
  KM_PKCOLID  NUMBER,
  KM_FKCOLID  NUMBER,
  KM_POS      NUMBER (5)    NOT NULL,
  KM_FKTYPE   VARCHAR2 (10),
  CONSTRAINT KMID_PK
    PRIMARY KEY ( KM_ID ),
  CONSTRAINT KMFKCOLID_FK
    FOREIGN KEY (KM_FKCOLID)
      REFERENCES COLUMN_T (COL_ID),
  CONSTRAINT KMKHID_FK
    FOREIGN KEY (KM_KHID)
      REFERENCES KEYHEAD_T (KH_ID),
  CONSTRAINT KMPKCOLID_FK
    FOREIGN KEY (KM_PKCOLID)
      REFERENCES COLUMN_T (COL_ID)
);


REM Table RELATION_T
CREATE TABLE RELATION_T (
  REL_ID           NUMBER         NOT NULL,
  REL_NAME         VARCHAR2 (100) NOT NULL,
  REL_FROMCONID    NUMBER,
  REL_TOCONID      NUMBER,
  REL_FROMKID      NUMBER,
  REL_TOKID        NUMBER,
  REL_CSID         NUMBER,
  CONSTRAINT RELID_PK
    PRIMARY KEY ( REL_ID ),
  CONSTRAINT RELCSID_FK
    FOREIGN KEY (REL_CSID)
      REFERENCES COLUMNSET_T (CS_ID),
  CONSTRAINT RELFROMCONID_FK
    FOREIGN KEY (REL_FROMCONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT RELFROMKID_FK
    FOREIGN KEY (REL_FROMKID)
      REFERENCES KEYHEAD_T (KH_ID),
  CONSTRAINT RELTOCONID_FK
    FOREIGN KEY (REL_TOCONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT RELTOKID_FK
    FOREIGN KEY (REL_TOKID)
      REFERENCES KEYHEAD_T (KH_ID)
);


REM Table RELATIONISA_T
CREATE TABLE RELATIONISA_T (
  RISA_ID          NUMBER        NOT NULL,
  RISA_SUPERRELID  NUMBER        NOT NULL,
  RISA_SUBRELID    NUMBER        NOT NULL,
  CONSTRAINT RISAID_PK
    PRIMARY KEY ( RISA_ID ),
  CONSTRAINT RISASUBRELID_FK
    FOREIGN KEY (RISA_SUBRELID)
      REFERENCES RELATION_T (REL_ID),
  CONSTRAINT RISASUPERRELID_FK
    FOREIGN KEY (RISA_SUPERRELID)
      REFERENCES RELATION_T (REL_ID)
);


REM Table BASEATTRIB_T
CREATE TABLE BASEATTRIB_T (
  BA_ID          NUMBER        NOT NULL,
  BA_NAME        VARCHAR2 (100)  NOT NULL,
  BA_CONDTID     NUMBER        NOT NULL,
  BA_RELEVANCE   VARCHAR2 (5),
  BA_ATTRIBTYPE  VARCHAR2 (15)  NOT NULL,
  BA_MCFID       NUMBER,
  CONSTRAINT BAID_PK
    PRIMARY KEY ( BA_ID ),
  CONSTRAINT BACONDTID_FK
    FOREIGN KEY (BA_CONDTID)
      REFERENCES CON_DATATYPE_T (CONDT_ID),
  CONSTRAINT BAMCFID_FK
    FOREIGN KEY (BA_MCFID)
      REFERENCES MCFEATURE_T (MCF_ID),
  CONSTRAINT BARELEVANCE_CH
    CHECK (BA_RELEVANCE IN ('YES', 'NO')) ,
  CONSTRAINT BAATTRIBTYPE_CH
    CHECK (BA_ATTRIBTYPE IN ('BASE', 'DB', 'MINING'))
);


REM Table BA_CONCEPT_T
CREATE TABLE BA_CONCEPT_T (
  BC_ID		NUMBER	NOT NULL,
  BC_BAID	NUMBER	NOT NULL,
  BC_CONID	NUMBER	NOT NULL,
  CONSTRAINT BACON_ID
    PRIMARY KEY ( BC_ID ),
  CONSTRAINT BACON_BA_FK
    FOREIGN KEY ( BC_BAID )
      REFERENCES BASEATTRIB_T (BA_ID),
  CONSTRAINT BACON_CON_FK
    FOREIGN KEY ( BC_CONID )
      REFERENCES CONCEPT_T (CON_ID)
);

CREATE INDEX BACON_BA_IDX  ON BA_CONCEPT_T (BC_BAID);
CREATE INDEX BACON_CON_IDX ON BA_CONCEPT_T (BC_CONID);

REM Table BA_COLUMN_T
CREATE TABLE BA_COLUMN_T (
  BAC_ID	NUMBER	NOT NULL,
  BAC_BAID	NUMBER	NOT NULL,
  BAC_COLID	NUMBER	NOT NULL,
  CONSTRAINT BACID_PK
    PRIMARY KEY ( BAC_ID ),
  CONSTRAINT BACBA_FK
    FOREIGN KEY ( BAC_BAID )
      REFERENCES BASEATTRIB_T (BA_ID),
  CONSTRAINT BACCOL_FK
    FOREIGN KEY ( BAC_COLID )
      REFERENCES COLUMN_T (COL_ID)
);

CREATE INDEX BAC_BA_IDX  ON BA_COLUMN_T (BAC_BAID);
CREATE INDEX BAC_COL_IDX ON BA_COLUMN_T (BAC_COLID);

REM Table CSSTATIST_T
CREATE TABLE CSSTATIST_T (
  CSST_ID    NUMBER        NOT NULL,
  CSST_CSID  NUMBER        NOT NULL,
  CSST_ALL   NUMBER (20),
  CSST_ORD   NUMBER (5),
  CSST_NOM   NUMBER (5),
  CSST_TIME  NUMBER (5),
  CONSTRAINT CSSTID_PK
    PRIMARY KEY ( CSST_ID ),
  CONSTRAINT CSSTCSID_FK
    FOREIGN KEY (CSST_CSID)
      REFERENCES COLUMNSET_T (CS_ID)
);


REM Table COLSTATIST1_T
CREATE TABLE COLSTATIST1_T (
  COLST1_ID        NUMBER        NOT NULL,
  COLST1_COLID     NUMBER        NOT NULL,
  COLST1_UNIQUE    NUMBER (20),
  COLST1_MISSING   NUMBER (20),
  COLST1_MIN       VARCHAR2 (100),
  COLST1_MAX       VARCHAR2 (100),
  COLST1_AVG       NUMBER (20,5),
  COLST1_STDDEV    NUMBER (20,5),
  COLST1_VARIANCE  NUMBER (38,5),
  COLST1_MEDIAN    VARCHAR2 (100),
  COLST1_MODAL     VARCHAR2 (100),
  CONSTRAINT COLST1ID_PK
    PRIMARY KEY ( COLST1_ID ),
  CONSTRAINT COLST1COLID_FK
    FOREIGN KEY (COLST1_COLID)
      REFERENCES COLUMN_T (COL_ID)
);


REM Table COLSTATIST2_T
CREATE TABLE COLSTATIST2_T (
  COLST2_ID         NUMBER        NOT NULL,
  COLST2_COLID      NUMBER        NOT NULL,
  COLST2_DISTVALUE  VARCHAR2 (100)  NOT NULL,
  COLST2_DISTCOUNT  NUMBER (20)   NOT NULL,
  COLST2_DISTMIN    NUMBER (20,5),
  COLST2_DISTMAX    NUMBER (20,5),
  CONSTRAINT COLST2ID_PK
    PRIMARY KEY ( COLST2_ID ),
  CONSTRAINT COLST2COLID_FK
    FOREIGN KEY (COLST2_COLID)
      REFERENCES COLUMN_T (COL_ID)
);


REM Table VALUE_T
CREATE TABLE VALUE_T (
  V_ID       NUMBER        NOT NULL,
  V_CONDTID  NUMBER        NOT NULL,
  V_NAME     VARCHAR2 (100),
  V_VALUE    VARCHAR2 (4000) NOT NULL,
  CONSTRAINT VID_PK
    PRIMARY KEY ( V_ID ),
  CONSTRAINT VCONDTID_FK
    FOREIGN KEY (V_CONDTID)
      REFERENCES CON_DATATYPE_T (CONDT_ID)
);

REM Table ROLERESTRICTION_T
CREATE TABLE ROLERESTRICTION_T (
  RR_ID         NUMBER        NOT NULL,
  RR_NAME       VARCHAR2 (100)  NOT NULL,
  RR_RELID      NUMBER,
  RR_FROMCONID  NUMBER,
  RR_TOCONID    NUMBER,
  RR_MIN        NUMBER,
  RR_MAX        NUMBER,
  CONSTRAINT RRID_PK
    PRIMARY KEY ( RR_ID ),
  CONSTRAINT RRFROMCONID_FK
    FOREIGN KEY (RR_FROMCONID)
      REFERENCES CONCEPT_T (CON_ID),
  CONSTRAINT RRRELID_FK
    FOREIGN KEY (RR_RELID)
      REFERENCES RELATION_T (REL_ID),
  CONSTRAINT RRTOCONID_FK
    FOREIGN KEY (RR_TOCONID)
      REFERENCES CONCEPT_T (CON_ID)
);


REM Table OPERATOR_T
CREATE TABLE OPERATOR_T (
  OP_ID       NUMBER           NOT NULL,
  OP_NAME     VARCHAR2 (1000)  NOT NULL,
  OP_LOOP     VARCHAR2 (5),
  OP_MULTI    VARCHAR2 (5),
  OP_MANUAL   VARCHAR2 (5),
  OP_REALIZE  VARCHAR2 (100),
  CONSTRAINT OPID_PK
    PRIMARY KEY ( OP_ID ),
  CONSTRAINT OPMANUAL_CH
    CHECK ( OP_MANUAL IN ('YES', 'NO')) ,
  CONSTRAINT OPLOOP_CH
    CHECK ( OP_LOOP IN ('YES', 'NO')) ,
  CONSTRAINT OPMULTI_CH
    CHECK ( OP_MULTI IN ('YES', 'NO'))
);


REM Table OP_PARAMS_T
CREATE TABLE OP_PARAMS_T (
  PARAM_ID     NUMBER           NOT NULL,
  OP_ID	       NUMBER           NOT NULL,
  MINARG       NUMBER		NOT NULL,
  MAXARG       NUMBER,
  NAME	       VARCHAR(100)	NOT NULL,
  IO	       VARCHAR(5)	NOT NULL,
  TYPE	       VARCHAR(5)	NOT NULL,
  DOCU	       VARCHAR2(400),
  CONSTRAINT OPPARAMID_PK
    PRIMARY KEY ( PARAM_ID ),
  CONSTRAINT OPPARAMOPID_FK
    FOREIGN KEY (OP_ID)
      REFERENCES OPERATOR_T (OP_ID)
      ON DELETE CASCADE
);


REM Table OP_CONSTR_T
CREATE TABLE OP_CONSTR_T (
  CONSTR_ID    NUMBER           NOT NULL,
  CONSTR_OPID  NUMBER           NOT NULL,
  CONSTR_TYPE  VARCHAR2(10)     NOT NULL,
  CONSTR_OBJ1  VARCHAR2(100)	NOT NULL,
  CONSTR_OBJ2  VARCHAR2(100),
  CONSTR_DOCU  VARCHAR2(400),
  CONSTR_SQL   VARCHAR2(1000),
  CONSTRAINT OPCONSTRID_PK
    PRIMARY KEY ( CONSTR_ID ),
  CONSTRAINT OPCONSTRID_FK
    FOREIGN KEY (CONSTR_OPID)
      REFERENCES OPERATOR_T (OP_ID)
      ON DELETE CASCADE
);


REM Table OP_COND_T
CREATE TABLE OP_COND_T (
  COND_ID      NUMBER           NOT NULL,
  COND_OPID    NUMBER           NOT NULL,
  COND_TYPE    VARCHAR2(10)     NOT NULL,
  COND_OBJ1    VARCHAR2(100)	NOT NULL,
  COND_OBJ2    VARCHAR2(100),
  COND_DOCU    VARCHAR2(400),
  COND_SQL     VARCHAR2(1000),
  CONSTRAINT OPCONDID_PK
    PRIMARY KEY ( COND_ID ),
  CONSTRAINT OPCONDID_FK
    FOREIGN KEY (COND_OPID)
      REFERENCES OPERATOR_T (OP_ID)
      ON DELETE CASCADE
);


REM Table OP_ASSERT_T
CREATE TABLE OP_ASSERT_T (
  ASSERT_ID    NUMBER           NOT NULL,
  ASSERT_OPID  NUMBER           NOT NULL,
  ASSERT_TYPE  VARCHAR2(10)     NOT NULL,
  ASSERT_OBJ1  VARCHAR2(100)	NOT NULL,
  ASSERT_OBJ2  VARCHAR2(100),
  ASSERT_DOCU  VARCHAR2(400),
  ASSERT_SQL   VARCHAR2(1000),
  CONSTRAINT OPASSERTID_PK
    PRIMARY KEY ( ASSERT_ID ),
  CONSTRAINT OPASSERTID_FK
    FOREIGN KEY ( ASSERT_OPID )
      REFERENCES OPERATOR_T (OP_ID)
      ON DELETE CASCADE
);

REM Table CHAIN_T
CREATE TABLE CHAIN_T (
  CH_ID        NUMBER           NOT NULL,
  CH_CASEID    NUMBER           NOT NULL,
  CH_NAME      VARCHAR2(100)	NOT NULL,
  CH_PARENT    NUMBER,
  CH_DESCRIPT  VARCHAR2(400),
  CONSTRAINT CHAINID_PK
    PRIMARY KEY ( CH_ID ),
  CONSTRAINT CHCA_FK
    FOREIGN KEY (CH_CASEID)
      REFERENCES CASE_T (CA_ID),
  CONSTRAINT CHCH_FK
    FOREIGN KEY (CH_PARENT)
      REFERENCES CHAIN_T (CH_ID),
  CONSTRAINT CHCASENAME_UQ
    UNIQUE (CH_CASEID, CH_NAME) USING INDEX
);


REM Table STEP_T
CREATE TABLE STEP_T (
  ST_ID             NUMBER        NOT NULL,
  ST_NAME	    VARCHAR2 (100) NOT NULL,
  ST_CAID           NUMBER,
  ST_NR             NUMBER (5),
  ST_OPID           NUMBER,
  ST_LOOPNR         NUMBER (5),
  ST_MULTISTEPCOND  VARCHAR2 (1000),
  ST_CHID	    NUMBER,
  CONSTRAINT STID_PK
    PRIMARY KEY ( ST_ID ),
  CONSTRAINT STCAID_FK
    FOREIGN KEY (ST_CAID)
      REFERENCES CASE_T (CA_ID),
  CONSTRAINT STOPID_FK
    FOREIGN KEY (ST_OPID)
      REFERENCES OPERATOR_T (OP_ID),
  CONSTRAINT STCHID_FK
    FOREIGN KEY (ST_CHID)
      REFERENCES CHAIN_T (CH_ID),
  CONSTRAINT STCASENAME_UQ
    UNIQUE (ST_CAID, ST_NAME) USING INDEX
);


REM Table STEPSEQUENCE_T
CREATE TABLE STEPSEQUENCE_T (
  STS_ID               NUMBER        NOT NULL,
  STS_STID             NUMBER        NOT NULL,
  STS_SUCCESSORSTID    NUMBER,
  CONSTRAINT STSID_PK
    PRIMARY KEY ( STS_ID ),
  CONSTRAINT STSSTID_FK
    FOREIGN KEY (STS_STID)
      REFERENCES STEP_T (ST_ID),
  CONSTRAINT STSSUCCESSORSTID_FK
    FOREIGN KEY (STS_SUCCESSORSTID)
      REFERENCES STEP_T (ST_ID)
);


REM Table PARAMETER_T
CREATE TABLE PARAMETER_T (
  PAR_ID        NUMBER         NOT NULL,
  PAR_NAME      VARCHAR2 (100) NOT NULL,
  PAR_OBJID     NUMBER,
  PAR_OBJTYPE   VARCHAR2 (20)  NOT NULL,
  PAR_OPID      NUMBER         NOT NULL,
  PAR_TYPE      VARCHAR2 (10)  NOT NULL,
  PAR_NR        NUMBER (3)     NOT NULL,
  PAR_STID      NUMBER         NOT NULL,
  PAR_STLOOPNR  NUMBER (5),
  CONSTRAINT PARID_PK
    PRIMARY KEY ( PAR_ID ),
  CONSTRAINT PAROPID_FK
    FOREIGN KEY (PAR_OPID)
      REFERENCES OPERATOR_T (OP_ID),
  CONSTRAINT PARSTID_FK
    FOREIGN KEY (PAR_STID)
      REFERENCES STEP_T (ST_ID),
  CONSTRAINT PARSTEPNAME_UQ
    UNIQUE (PAR_STID, PAR_NAME) USING INDEX,
  CONSTRAINT PAROBJTYPE_CH
    CHECK ( PAR_OBJTYPE IN ('CON', 'REL', 'BA', 'V', 'MCF', 'FUNC')) ,
  CONSTRAINT PARTYPE_CH
    CHECK ( PAR_TYPE IN ('IN', 'OUT'))
);


REM Table DOCU_T
CREATE TABLE DOCU_T (
  DOC_ID       NUMBER        NOT NULL,
  DOC_OBJID    NUMBER        NOT NULL,
  DOC_OBJTYPE  VARCHAR2 (20)  NOT NULL,
  DOC_TEXT     VARCHAR2 (4000)  NOT NULL,
  CONSTRAINT DOCID_PK
    PRIMARY KEY ( DOC_ID )
);


REM ----------------------------------------------
REM --------------   System Tables   -------------
REM ----------------------------------------------

REM Insert into CON_DATATYPE
INSERT INTO CON_DATATYPE_T VALUES (1,'NOMINAL');
INSERT INTO CON_DATATYPE_T VALUES (2,'CATEGORIAL');
INSERT INTO CON_DATATYPE_T VALUES (3,'KEYATTRIB');
INSERT INTO CON_DATATYPE_T VALUES (4,'CONSTANT');
INSERT INTO CON_DATATYPE_T VALUES (5,'TIMEGROUP');
INSERT INTO CON_DATATYPE_T VALUES (6,'SPATIAL');
INSERT INTO CON_DATATYPE_T VALUES (7,'NUMERIC');
INSERT INTO CON_DATATYPE_T VALUES (8,'ORDINAL');
INSERT INTO CON_DATATYPE_T VALUES (9,'SCALAR');
INSERT INTO CON_DATATYPE_T VALUES (10,'TIME');
INSERT INTO CON_DATATYPE_T VALUES (11,'BINARY');

REM Insert into COL_DATATYPE
INSERT INTO COL_DATATYPE_T VALUES (12,'NUMBER');
INSERT INTO COL_DATATYPE_T VALUES (13,'STRING');
INSERT INTO COL_DATATYPE_T VALUES (14,'DATE');
INSERT INTO COL_DATATYPE_T VALUES (15,'KEY');


REM --------------------------------------------------

CREATE TABLE M4TRASH_T (
  M4ID		NUMBER       NOT NULL, 
  M4TABLE       VARCHAR2(50) NOT NULL,
  STEPID	NUMBER	     NOT NULL,
  CONSTRAINT M4TRASH_FK
    FOREIGN KEY ( STEPID )
      REFERENCES STEP_T (ST_ID)
);

CREATE TABLE DBTRASH_T (
  OBJTYPE	VARCHAR2(5)   NOT NULL,
  OBJNAME	VARCHAR2(100) NOT NULL,
  STEPID	NUMBER        NOT NULL,
  SCHEMANAME	VARCHAR2(200),
  CONSTRAINT DBTRASH_OBJTYPE_CH
    CHECK(OBJTYPE IN ('T', 'V', 'F')),
  CONSTRAINT DBTRASH_FK
    FOREIGN KEY ( STEPID )
      REFERENCES STEP_T (ST_ID)
);

CREATE TABLE M4_ACCESS_T
( OBJECT_ID     VARCHAR2(100) NOT NULL,
  OBJECT_TYPE   VARCHAR2(25) NOT NULL,
  CLIENT_NAME   VARCHAR2(50) NOT NULL,
  ACCESS_TYPE   VARCHAR2(10) NOT NULL,
  CONSTRAINT M4_ACC_PK
    PRIMARY KEY ( OBJECT_ID, OBJECT_TYPE, CLIENT_NAME ),
  CONSTRAINT ACCTYPE_CH
    CHECK (ACCESS_TYPE IN ('READ', 'WRITE'))
);

CREATE TABLE HCI_COORD_T
( OBJ_ID        NUMBER          NOT NULL
, OBJ_NAME      VARCHAR2(100)   NOT NULL
, OBJ_TYPE      VARCHAR2(25)    NOT NULL
, CONTEXT_ID    NUMBER          NULL
, CONTEXT_NAME  VARCHAR2(100)   NULL
, CONTEXT_TYPE  VARCHAR2(25)    NULL
, X             NUMBER          NOT NULL
, Y             NUMBER          NOT NULL
, CONSTRAINT HCI_C_UQ
  UNIQUE ( OBJ_ID, OBJ_NAME, OBJ_TYPE
              , CONTEXT_ID, CONTEXT_NAME, CONTEXT_TYPE)
, CONSTRAINT OBJ_TYPE_CH
    CHECK ( OBJ_TYPE IN ('CON','ST', 'REL', 'CH'))
);

CREATE TABLE OP_GROUP_T (
  OPG_ID     NUMBER        NOT NULL,
  OPG_PARENT NUMBER,
  OPG_OPID   NUMBER,
  OPG_NAME   VARCHAR2(100) NOT NULL,
  CONSTRAINT OPG_PK
    PRIMARY KEY (OPG_ID),
  CONSTRAINT OPGPAR_FK
    FOREIGN KEY (OPG_PARENT)
      REFERENCES OP_GROUP_T (OPG_ID),
  CONSTRAINT OPGOP_FK
    FOREIGN KEY (OPG_OPID)
      REFERENCES OPERATOR_T (OP_ID)  
);

REM --------------------------------------------------

COMMIT;
exit
