-- M4 tables installation script for PostgreSQL --

-- Version: 0.02 (tested ok)
-- Author: Timm Euler and Martin Scholz
-- Date: 10th June 2003

-- Changes against Oracle version:

-- a) DROP TABLE ... CASCADE CONSTRAINTS;  --> DROP TABLE ... CASCADE;
-- b) VARCHAR2(..) --> VARCHAR
-- c) NUMBER --> NUMERIC
-- d) deleted "USING INDEX" in UNIQUE clauses (postgres creates indexes automatically (?))
-- e) REM --> --REM (keyword REM is unknown to Postgres)
-- f) deleted "COMMIT" and "EXIT"
-- g) ALTER TABLE ... ADD CONSTRAINT: separate commands for each constraint; no round brackets

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

DROP TABLE M4_ACCESS_T       CASCADE;
DROP TABLE HCI_COORD_T       CASCADE;

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

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

DROP TABLE M4TRASH_T	     CASCADE;
DROP TABLE DBTRASH_T         CASCADE;


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

--REM Table CASE_T
CREATE TABLE CASE_T (
  CA_ID          NUMERIC         NOT NULL,
  CA_NAME        VARCHAR NOT NULL,
  CA_MODE        VARCHAR  NOT NULL,
  CA_POPULATION  NUMERIC,
  CA_OUTPUT      NUMERIC,
  CA_VALID       VARCHAR,
  CONSTRAINT CAID_PK
    PRIMARY KEY ( CA_ID ),
  CONSTRAINT CANAME_UQ
    UNIQUE (CA_NAME),
  CONSTRAINT CAMODE_CH
    CHECK (CA_MODE IN ('DESIGN', 'TEST', 'FINAL')),
  CONSTRAINT CAVALID_CH
    CHECK(CA_VALID IN ('YES', 'NO'))
);


--REM Table CON_DATATYPE_T
CREATE TABLE CON_DATATYPE_T (
  CONDT_ID    NUMERIC        NOT NULL,
  CONDT_NAME  VARCHAR  NOT NULL,
  CONSTRAINT CONDTID_PK
    PRIMARY KEY ( CONDT_ID )
);


--REM Table COL_DATATYPE_T
CREATE TABLE COL_DATATYPE_T (
  COLDT_ID    NUMERIC        NOT NULL,
  COLDT_NAME  VARCHAR  NOT NULL,
  CONSTRAINT COLDTID_PK
    PRIMARY KEY ( COLDT_ID )
);


--REM Table OP_TYPE_T
CREATE TABLE OP_TYPE_T (
  OPT_ID    NUMERIC          NOT NULL,
  OPT_TYPE  VARCHAR  NOT NULL,
  CONSTRAINT OPTID_PK
    PRIMARY KEY ( OPT_ID )
);


--REM Table CONCEPT_T
CREATE TABLE CONCEPT_T (
  CON_ID           NUMERIC          NOT NULL,
  CON_CAID         NUMERIC,
  CON_NAME         VARCHAR  NOT NULL,
  CON_TYPE         VARCHAR   NOT NULL,
  CON_SUBCONRESTR  VARCHAR,
  CON_VALID        VARCHAR,
  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')),
  CONSTRAINT CONVALID_CH
    CHECK ( CON_VALID IN ('YES', 'NO'))
);


--REM Table CASE_T
ALTER TABLE CASE_T ADD
    CONSTRAINT CAOUTPUT_FK
    FOREIGN KEY (CA_OUTPUT)
      REFERENCES CONCEPT_T (CON_ID);

ALTER TABLE CASE_T ADD
    CONSTRAINT CAPOPULATION_FK
    FOREIGN KEY (CA_POPULATION)
      REFERENCES CONCEPT_T (CON_ID);


--REM Table COLUMNSET_T
CREATE TABLE COLUMNSET_T (
  CS_ID       NUMERIC          NOT NULL,
  CS_SCHEMA   VARCHAR  NOT NULL,
  CS_NAME     VARCHAR  NOT NULL,
  CS_FILE     VARCHAR,
  CS_USER     VARCHAR,
  CS_CONNECT  VARCHAR,
  CS_TYPE     VARCHAR    NOT NULL,
  CS_SQL      VARCHAR,
  CS_CONID    NUMERIC,
  CS_MSBRANCH VARCHAR,
  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', 'SN', 'MV'))
);


--REM Table CONCEPTISA_T
CREATE TABLE CONCEPTISA_T (
  CISA_ID          NUMERIC        NOT NULL,
  CISA_SUPERCONID  NUMERIC        NOT NULL,
  CISA_SUBCONID    NUMERIC        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         NUMERIC        NOT NULL,
  PRO_FROMCONID  NUMERIC        NOT NULL,
  PRO_TOCONID    NUMERIC        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     NUMERIC        NOT NULL,
  MCF_NAME   VARCHAR  NOT NULL,
  MCF_CONID  NUMERIC,
  MCF_VALID  VARCHAR,
  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),
  CONSTRAINT MCFVALID_CH
    CHECK ( MCF_VALID IN ('YES', 'NO'))
);


--REM Table COLUMN_T
CREATE TABLE COLUMN_T (
  COL_ID       NUMERIC         NOT NULL,
  COL_NAME     VARCHAR NOT NULL,
  COL_CSID     NUMERIC,
  COL_COLDTID  NUMERIC         NOT NULL,
  COL_SQL      VARCHAR,
  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      NUMERIC        NOT NULL,
  KH_NAME    VARCHAR  NOT NULL,
  KH_PKCSID  NUMERIC,
  KH_FKCSID  NUMERIC,
  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       NUMERIC        NOT NULL,
  KM_KHID     NUMERIC        NOT NULL,
  KM_PKCOLID  NUMERIC,
  KM_FKCOLID  NUMERIC,
  KM_POS      NUMERIC (5)    NOT NULL,
  KM_FKTYPE   VARCHAR,
  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           NUMERIC        NOT NULL,
  REL_NAME         VARCHAR  NOT NULL,
  REL_FROMCONID    NUMERIC,
  REL_TOCONID      NUMERIC,
  REL_FROMKID      NUMERIC,
  REL_TOKID        NUMERIC,
  REL_CSID         NUMERIC,
  REL_SUBRELRESTR  VARCHAR,
  REL_VALID        VARCHAR,
  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),
  CONSTRAINT RELVALID_CH
    CHECK(REL_VALID IN ('YES', 'NO'))
);


--REM Table RELATIONISA_T
CREATE TABLE RELATIONISA_T (
  RISA_ID          NUMERIC        NOT NULL,
  RISA_SUPERRELID  NUMERIC        NOT NULL,
  RISA_SUBRELID    NUMERIC        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          NUMERIC        NOT NULL,
  BA_NAME        VARCHAR  NOT NULL,
  BA_CONDTID     NUMERIC        NOT NULL,
  BA_RELEVANCE   VARCHAR,
  BA_ATTRIBTYPE  VARCHAR  NOT NULL,
  BA_MCFID       NUMERIC,
  BA_HIDE	 VARCHAR,
  BA_VALID       VARCHAR,
  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')),
  CONSTRAINT BAHIDE_CH
    CHECK (BA_HIDE IN ('YES', 'NO')),
  CONSTRAINT BAVALID_CH
    CHECK(BA_VALID IN ('YES', 'NO'))
);


--REM Table BA_CONCEPT_T
CREATE TABLE BA_CONCEPT_T (
  BC_ID		NUMERIC	NOT NULL,
  BC_BAID	NUMERIC	NOT NULL,
  BC_CONID	NUMERIC	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	NUMERIC	NOT NULL,
  BAC_BAID	NUMERIC	NOT NULL,
  BAC_COLID	NUMERIC	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    NUMERIC        NOT NULL,
  CSST_CSID  NUMERIC        NOT NULL,
  CSST_ALL   NUMERIC (20),
  CSST_ORD   NUMERIC (5),
  CSST_NOM   NUMERIC (5),
  CSST_TIME  NUMERIC (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        NUMERIC        NOT NULL,
  COLST1_COLID     NUMERIC        NOT NULL,
  COLST1_UNIQUE    NUMERIC (20),
  COLST1_MISSING   NUMERIC (20),
  COLST1_MIN       VARCHAR,
  COLST1_MAX       VARCHAR,
  COLST1_AVG       NUMERIC (20,5),
  COLST1_STDDEV    NUMERIC (20,5),
  COLST1_VARIANCE  NUMERIC (38,5),
  COLST1_MEDIAN    VARCHAR,
  COLST1_MODAL     VARCHAR,
  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         NUMERIC        NOT NULL,
  COLST2_COLID      NUMERIC        NOT NULL,
  COLST2_DISTVALUE  VARCHAR  NOT NULL,
  COLST2_DISTCOUNT  NUMERIC (20)   NOT NULL,
  COLST2_DISTMIN    NUMERIC (20,5),
  COLST2_DISTMAX    NUMERIC (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       NUMERIC        NOT NULL,
  V_CONDTID  NUMERIC        NOT NULL,
  V_NAME     VARCHAR,
  V_VALUE    VARCHAR 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         NUMERIC        NOT NULL,
  RR_NAME       VARCHAR  NOT NULL,
  RR_RELID      NUMERIC,
  RR_FROMCONID  NUMERIC,
  RR_TOCONID    NUMERIC,
  RR_MIN        NUMERIC,
  RR_MAX        NUMERIC,
  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 CASEINPUT_T
CREATE TABLE CASEINPUT_T (
  CAI_ID       NUMERIC        NOT NULL,
  CAI_CAID     NUMERIC        NOT NULL,
  CAI_OBJID    NUMERIC,
  CAI_OBJTYPE  VARCHAR NOT NULL,
  CONSTRAINT CAIID_PK
    PRIMARY KEY ( CAI_ID ),
  CONSTRAINT CAICAID_FK
    FOREIGN KEY (CAI_CAID)
      REFERENCES CASE_T (CA_ID),
  CONSTRAINT CAI_OBJTYPE_CH
    CHECK (CAI_OBJTYPE IN ('CON', 'REL', 'BA', 'V', 'MCF'))
);


--REM Table CASEATTRIB_T
CREATE TABLE CASEATTRIB_T (
  CAA_ID       NUMERIC         NOT NULL,
  CAA_CAID     NUMERIC         NOT NULL,
  CAA_OBJID    NUMERIC         NOT NULL,
  CAA_OBJTYPE  VARCHAR  NOT NULL,
  CONSTRAINT CAAID_PK
    PRIMARY KEY ( CAA_ID ),
  CONSTRAINT CAACAID_FK
    FOREIGN KEY (CAA_CAID)
      REFERENCES CASE_T (CA_ID)
);


--REM Table OPERATOR_T
CREATE TABLE OPERATOR_T (
  OP_ID       NUMERIC           NOT NULL,
  OP_NAME     VARCHAR  NOT NULL,
  OP_LOOP     VARCHAR,
  OP_MULTI    VARCHAR,
  OP_MANUAL   VARCHAR,
  OP_REALIZE  VARCHAR,
  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     NUMERIC           NOT NULL,
  OP_ID	       NUMERIC           NOT NULL,
  MINARG       NUMERIC		NOT NULL,
  MAXARG       NUMERIC,
  NAME	       VARCHAR(100)	NOT NULL,
  IO	       VARCHAR(5)	NOT NULL,
  TYPE	       VARCHAR(5)	NOT NULL,
  DOCU	       VARCHAR,
  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    NUMERIC           NOT NULL,
  CONSTR_OPID  NUMERIC           NOT NULL,
  CONSTR_TYPE  VARCHAR     NOT NULL,
  CONSTR_OBJ1  VARCHAR	NOT NULL,
  CONSTR_OBJ2  VARCHAR,
  CONSTR_DOCU  VARCHAR,
  CONSTR_SQL   VARCHAR,
  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      NUMERIC           NOT NULL,
  COND_OPID    NUMERIC           NOT NULL,
  COND_TYPE    VARCHAR     NOT NULL,
  COND_OBJ1    VARCHAR	NOT NULL,
  COND_OBJ2    VARCHAR,
  COND_DOCU    VARCHAR,
  COND_SQL     VARCHAR,
  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    NUMERIC           NOT NULL,
  ASSERT_OPID  NUMERIC           NOT NULL,
  ASSERT_TYPE  VARCHAR     NOT NULL,
  ASSERT_OBJ1  VARCHAR	NOT NULL,
  ASSERT_OBJ2  VARCHAR,
  ASSERT_DOCU  VARCHAR,
  ASSERT_SQL   VARCHAR,
  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        NUMERIC           NOT NULL,
  CH_CASEID    NUMERIC           NOT NULL,
  CH_NAME      VARCHAR	NOT NULL,
  CH_PARENT    NUMERIC,
  CH_DESCRIPT  VARCHAR,
  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)
);


--REM Table STEP_T
CREATE TABLE STEP_T (
  ST_ID             NUMERIC        NOT NULL,
  ST_NAME	    VARCHAR NOT NULL,
  ST_CAID           NUMERIC,
  ST_NR             NUMERIC (5),
  ST_OPID           NUMERIC,
  ST_LOOPNR         NUMERIC (5),
  ST_MULTISTEPCOND  VARCHAR,
  ST_CHID	    NUMERIC,
  ST_VALID          VARCHAR,
  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),
  CONSTRAINT STVALID_CH
    CHECK ( ST_VALID IN ('YES', 'NO'))
);


--REM Table STEPSEQUENCE_T
CREATE TABLE STEPSEQUENCE_T (
  STS_ID               NUMERIC        NOT NULL,
  STS_STID             NUMERIC        NOT NULL,
  STS_SUCCESSORSTID    NUMERIC,
  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        NUMERIC         NOT NULL,
  PAR_NAME      VARCHAR NOT NULL,
  PAR_OBJID     NUMERIC,
  PAR_OBJTYPE   VARCHAR  NOT NULL,
  PAR_OPID      NUMERIC         NOT NULL,
  PAR_TYPE      VARCHAR  NOT NULL,
  PAR_NR        NUMERIC (3)     NOT NULL,
  PAR_STID      NUMERIC         NOT NULL,
  PAR_STLOOPNR  NUMERIC (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),
  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       NUMERIC        NOT NULL,
  DOC_OBJID    NUMERIC        NOT NULL,
  DOC_OBJTYPE  VARCHAR  NOT NULL,
  DOC_TEXT     VARCHAR  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 --------------------------------------------------
--REM --- TABLES REQUIRED FOR THE GARBAGE COLLECTION ---
--REM --------------------------------------------------

CREATE TABLE M4TRASH_T (
  M4ID		NUMERIC       NOT NULL, 
  M4TABLE       VARCHAR NOT NULL,
  STEPID	NUMERIC	     NOT NULL,
  CONSTRAINT M4TRASH_FK
    FOREIGN KEY ( STEPID )
      REFERENCES STEP_T (ST_ID)
);


CREATE TABLE DBTRASH_T (
  OBJTYPE	VARCHAR   NOT NULL,
  OBJNAME	VARCHAR NOT NULL,
  STEPID	NUMERIC        NOT NULL,
  SCHEMANAME	VARCHAR,
  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     VARCHAR NOT NULL,
  OBJECT_TYPE   VARCHAR NOT NULL,
  CLIENT_NAME   VARCHAR NOT NULL,
  ACCESS_TYPE   VARCHAR 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        NUMERIC          NOT NULL
, OBJ_NAME      VARCHAR   NOT NULL
, OBJ_TYPE      VARCHAR    NOT NULL
, CONTEXT_ID    NUMERIC          NULL
, CONTEXT_NAME  VARCHAR   NULL
, CONTEXT_TYPE  VARCHAR    NULL
, X             NUMERIC          NOT NULL
, Y             NUMERIC          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     NUMERIC    NOT NULL,
  OPG_PARENT NUMERIC,
  OPG_OPID   NUMERIC,
  OPG_NAME   VARCHAR    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 --------------------------------------------------
