ALTER TABLE CONCEPT_DIMENSION ADD
(
  ENCODING_CD      VARCHAR2(25 BYTE)
);



CREATE TABLE DATA_REQUEST
(
  DATA_REQUEST_ID       NUMBER(38)              NOT NULL,
  PROJECT_TITLE         VARCHAR2(100 BYTE),
  IS_RESEARCH_STUDY     CHAR(1 BYTE),
  RESEARCH_TYPE_CD      VARCHAR2(3 BYTE),
  IS_IRB_APPROVED       CHAR(1 BYTE),
  IRB_APPROVAL_NO       VARCHAR2(25 BYTE),
  IRB_APPROVAL_TYPE_CD  VARCHAR2(3 BYTE),
  IRB_APPROVAL_DOC      VARCHAR2(200 BYTE),
  IRB_APPROVAL_DATE     DATE,
  IACUC_APPROVAL_DOC    VARCHAR2(200 BYTE),
  IACUC_APPROVAL_NO     VARCHAR2(25 BYTE),
  IACUC_APPROVAL_DATE   DATE,
  DATA_REQUEST_REASON   VARCHAR2(4000 BYTE),
  USER_ID               VARCHAR2(20 BYTE)       NOT NULL,
  REQUEST_STATUS_CD     CHAR(1 BYTE),
  REQUEST_DATE          DATE,
  REQUESTER_NAME        VARCHAR2(50 BYTE),
  REQUESTER_ADDRESS     VARCHAR2(100 BYTE),
  REQUESTER_PHONE       NUMBER(15),
  REQUESTER_EMAIL       VARCHAR2(50 BYTE),
  REQ_EXISTS            CHAR(1 BYTE),
  RA_PROCESS_NOTES      VARCHAR2(250 BYTE),
  RA_DATA_NOTES         VARCHAR2(250 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE ENCODING_DIMENSION
(
  ENCODING_CD    VARCHAR2(25 BYTE)              NOT NULL,
  ENCODING_NAME  VARCHAR2(25 BYTE)              NOT NULL,
  ENCODING_DESC  VARCHAR2(200 BYTE),
  CREATE_USER    VARCHAR2(50 BYTE),
  UPDATE_USER    VARCHAR2(50 BYTE),
  CREATE_DATE    DATE,
  UPDATE_DATE    DATE,
  CDE_PUBLIC_ID  NUMBER(10),
  CDE_VERSION    VARCHAR2(10 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE ENCODING_PROJECT
(
  ENCODING_PROJECT_ID  NUMBER(10)               NOT NULL,
  PROJECT_NAME         VARCHAR2(50 BYTE),
  PROJECT_VERSION      VARCHAR2(10 BYTE),
  PROJECT_ID           NUMBER(10),
  ENCODING_SERVICE_ID  NUMBER(10)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE ENCODING_PROJECT_LINK
(
  ENCODING_PROJECT_ID  NUMBER(10)               NOT NULL,
  ENCODING_CD          VARCHAR2(25 BYTE)        NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE ENCODING_SERVICE
(
  ENCODING_SERVICE_ID  NUMBER(10)               NOT NULL,
  SERVICE_URL          VARCHAR2(200 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE IRB_APPROVAL_TYPE
(
  IRB_APPROVAL_TYPE_CD    VARCHAR2(3 BYTE)      NOT NULL,
  IRB_APPROVAL_TYPE_DESC  VARCHAR2(50 BYTE)     NOT NULL,
  CREATE_USER             VARCHAR2(50 BYTE),
  UPDATE_USER             VARCHAR2(50 BYTE),
  CREATE_DATE             DATE,
  UPDATE_DATE             DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_AGGR_FACT
(
  CONCEPT_PATH    VARCHAR2(700 BYTE)            NOT NULL,
  MAP_ID          NUMBER(38)                    NOT NULL,
  VALTYPE_CD      VARCHAR2(3 BYTE),
  TVAL_CHAR       VARCHAR2(255 BYTE),
  NVAL_NUM        NUMBER(18,5),
  QUANTITY_NUM    NUMBER(18,5),
  UNITS_CD        VARCHAR2(100 BYTE),
  CONFIDENCE_NUM  NUMBER(18),
  MAP_DATE        DATE,
  PATIENT_NUM     NUMBER(38)                    NOT NULL,
  CONCEPT_CD      VARCHAR2(50 BYTE)             NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_DATA_FACT
(
  ENCOUNTER_NUM        NUMBER(38)               NOT NULL,
  PATIENT_NUM          NUMBER(38)               NOT NULL,
  CONCEPT_CD           VARCHAR2(50 BYTE)        NOT NULL,
  PROVIDER_ID          VARCHAR2(20 BYTE)        NOT NULL,
  START_DATE           DATE                     NOT NULL,
  MODIFIER_CD          VARCHAR2(100 BYTE)       NOT NULL,
  CONCEPT_PATH         VARCHAR2(700 BYTE)       NOT NULL,
  MAP_ID               NUMBER(38)               NOT NULL,
  VALTYPE_CD           VARCHAR2(3 BYTE),
  TVAL_CHAR            VARCHAR2(500 BYTE),
  NVAL_NUM             NUMBER(18,5),
  VALUEFLAG_CD         CHAR(1 BYTE),
  QUANTITY_NUM         NUMBER(18,5),
  UNITS_CD             VARCHAR2(100 BYTE),
  END_DATE             DATE,
  LOCATION_CD          VARCHAR2(50 BYTE),
  CONFIDENCE_NUM       NUMBER(18),
  OBSERVATION_BLOB     CLOB,
  UPDATE_DATE          DATE,
  DOWNLOAD_DATE        DATE,
  IMPORT_DATE          DATE,
  SOURCESYSTEM_CD      VARCHAR2(50 BYTE),
  UPLOAD_ID            NUMBER(38),
  OBSERVATION_FACT_ID  NUMBER(38)               NOT NULL,
  MAP_DATE             DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_DIMENSION
(
  MAP_ID                 NUMBER(38)             NOT NULL,
  MAP_NAME               VARCHAR2(25 BYTE)      NOT NULL,
  MAP_PATH               VARCHAR2(200 BYTE)     NOT NULL,
  IMPORT_DATE            DATE                   NOT NULL,
  UPDATE_DATE            DATE,
  UPLOAD_USER            VARCHAR2(50 BYTE),
  UPDATE_USER            VARCHAR2(50 BYTE),
  LAST_RUN_DATE          DATE,
  MAP_TYPE_CD            CHAR(2 BYTE)           NOT NULL,
  TARGET_CONCEPT_PATH    VARCHAR2(700 BYTE),
  AUTHOR                 VARCHAR2(25 BYTE),
  CREATOR                VARCHAR2(25 BYTE),
  CURATOR                VARCHAR2(25 BYTE),
  CONTRIBUTOR            VARCHAR2(25 BYTE),
  CREATE_DATE            DATE,
  ID                     VARCHAR2(25 BYTE),
  LICENSE                VARCHAR2(50 BYTE),
  LICENSE_TYPE           VARCHAR2(25 BYTE),
  IS_LICENSE_RESTRICTED  CHAR(1 BYTE),
  RELEASE_DATE           DATE,
  RELEASE_REPO_NAME      VARCHAR2(50 BYTE),
  RELEASE_URI            VARCHAR2(50 BYTE),
  RELEASE_VERSION        VARCHAR2(10 BYTE),
  RESTRICTION_TYPE       VARCHAR2(25 BYTE),
  RIGHTS_HOLDER          VARCHAR2(25 BYTE),
  PURPOSE                VARCHAR2(50 BYTE),
  ORG_NAME               VARCHAR2(50 BYTE),
  ORG_TYPE               VARCHAR2(25 BYTE),
  SOURCE_SOFTWARE_ENV    VARCHAR2(50 BYTE),
  ETL_SCRIPT_URL         VARCHAR2(50 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_REQUEST
(
  MAP_REQUEST_ID   NUMBER(38)                   NOT NULL,
  CONCEPT_PATH     VARCHAR2(700 BYTE)           NOT NULL,
  TARGET_ENCODING  VARCHAR2(25 BYTE)            NOT NULL,
  USER_ID          VARCHAR2(20 BYTE)            NOT NULL,
  REQUEST_DESC     VARCHAR2(200 BYTE),
  REQUEST_DATE     DATE,
  MAP_ID           NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_SRC_CONCEPT_SET
(
  MAP_SRC_CONCEPT_ID   NUMBER(38),
  MAP_ID               NUMBER(38)               NOT NULL,
  SOURCE_CONCEPT_PATH  VARCHAR2(700 BYTE),
  SOURCE_CONCEPT_CD    VARCHAR2(50 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE MAP_TYPE
(
  MAP_TYPE_CD    CHAR(2 BYTE),
  MAP_TYPE_DESC  VARCHAR2(25 BYTE)              NOT NULL,
  CREATE_USER    VARCHAR2(50 BYTE),
  UPDATE_USER    VARCHAR2(50 BYTE),
  CREATE_DATE    DATE,
  UPDATE_DATE    DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



ALTER TABLE OBSERVATION_FACT ADD
(
  CONCEPT_PATH         VARCHAR2(700 BYTE),
  OBSERVATION_FACT_ID  NUMBER(38)
);



CREATE TABLE QUERY_CRITERIA
(
  QUERY_ID      NUMBER(38)                      NOT NULL,
  CONCEPT_PATH  VARCHAR2(700 BYTE)              NOT NULL,
  IS_VALID      CHAR(1 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE QUERY_DIMENSION
(
  QUERY_ID     NUMBER(38)                       NOT NULL,
  QUERY_NAME   VARCHAR2(200 BYTE)               NOT NULL,
  USER_ID      VARCHAR2(20 BYTE)                NOT NULL,
  CREATE_DATE  DATE,
  UPDATE_DATE  DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE REQUEST_STATUS
(
  REQUEST_STATUS_CD    CHAR(1 BYTE)             NOT NULL,
  REQUEST_STATUS_DESC  VARCHAR2(50 BYTE)        NOT NULL,
  CREATE_USER          VARCHAR2(50 BYTE),
  UPDATE_USER          VARCHAR2(50 BYTE),
  CREATE_DATE          DATE,
  UPDATE_DATE          DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE RESEARCH_TYPE
(
  RESEARCH_TYPE_CD    VARCHAR2(3 BYTE)          NOT NULL,
  RESEARCH_TYPE_DESC  VARCHAR2(50 BYTE)         NOT NULL,
  CREATE_USER         VARCHAR2(50 BYTE),
  UPDATE_USER         VARCHAR2(50 BYTE),
  CREATE_DATE         DATE,
  UPDATE_DATE         DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE STUDY_CRITERIA
(
  DATA_REQUEST_ID  NUMBER(38)                   NOT NULL,
  CONCEPT_PATH     VARCHAR2(700 BYTE)           NOT NULL,
  ENCODING_REQD    VARCHAR2(25 BYTE),
  PARAMETERS       VARCHAR2(200 BYTE),
  IS_VALID         CHAR(1 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE TABLE USER_INFO
(
  USER_ID               VARCHAR2(20 BYTE)       NOT NULL,
  CREATE_USER           VARCHAR2(50 BYTE),
  UPDATE_USER           VARCHAR2(50 BYTE),
  CREATE_DATE           DATE,
  UPDATE_DATE           DATE,
  FIRST_NAME            VARCHAR2(25 BYTE),
  LAST_NAME             VARCHAR2(25 BYTE),
  MIDDLE_INITIAL        CHAR(1 BYTE),
  PASSWORD              VARCHAR2(25 BYTE),
  DEGREE                VARCHAR2(50 BYTE),
  UNIVERSITY_TITLE      VARCHAR2(50 BYTE),
  RESEARCH_AREA         VARCHAR2(200 BYTE),
  ACADEMIC_INSTITUTION  VARCHAR2(50 BYTE),
  ADDRESS               VARCHAR2(100 BYTE),
  EMAIL                 VARCHAR2(50 BYTE),
  PHONE                 NUMBER(15),
  PI_DEPT_AFFILIATION   VARCHAR2(200 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE UNIQUE INDEX PK_DATA_REQUEST ON DATA_REQUEST
(DATA_REQUEST_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_ENCODING_DIMENSION ON ENCODING_DIMENSION
(ENCODING_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_ENCODING_PROJECT ON ENCODING_PROJECT
(ENCODING_PROJECT_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_ENCODING_PROJECT_LINK ON ENCODING_PROJECT_LINK
(ENCODING_PROJECT_ID, ENCODING_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_ENCODING_SERVICE ON ENCODING_SERVICE
(ENCODING_SERVICE_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_IRB_APPROVAL_TYPE ON IRB_APPROVAL_TYPE
(IRB_APPROVAL_TYPE_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_MAP_AGGR_FACT ON MAP_AGGR_FACT
(CONCEPT_PATH, MAP_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_MAP_DATA_FACT ON MAP_DATA_FACT
(ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, 
MODIFIER_CD, CONCEPT_PATH, MAP_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_MAP_DIMENSION ON MAP_DIMENSION
(MAP_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_MAP_REQUEST ON MAP_REQUEST
(MAP_REQUEST_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_QUERY_CRITERIA ON QUERY_CRITERIA
(QUERY_ID, CONCEPT_PATH)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_QUERY_DIMENSION ON QUERY_DIMENSION
(QUERY_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_REQUEST_STATUS ON REQUEST_STATUS
(REQUEST_STATUS_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_RESEARCH_TYPE ON RESEARCH_TYPE
(RESEARCH_TYPE_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_STUDY_CRITERIA ON STUDY_CRITERIA
(DATA_REQUEST_ID, CONCEPT_PATH)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX PK_USER_INFO ON USER_INFO
(USER_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX UQ_MAP_DIMENSION_MAP_NAME ON MAP_DIMENSION
(MAP_NAME)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX UQ_OBS_F_OBSERVATION_FACT_ID ON OBSERVATION_FACT
(OBSERVATION_FACT_ID)
LOGGING
NOPARALLEL;


CREATE VIEW OBS_FACT_VIEW (ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID) AS
(
SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID
FROM OBSERVATION_FACT
UNION ALL
SELECT ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, 
LOCATION_CD, CONFIDENCE_NUM, UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID, OBSERVATION_FACT_ID
FROM MAP_DATA_FACT
UNION ALL
SELECT NULL, PATIENT_NUM, CONCEPT_CD, NULL, NULL, NULL, CONCEPT_PATH,VALTYPE_CD, TVAL_CHAR, NVAL_NUM, NULL, QUANTITY_NUM, UNITS_CD, NULL, NULL, CONFIDENCE_NUM, NULL, NULL, NULL, NULL, NULL, NULL
FROM MAP_AGGR_FACT
);


ALTER TABLE DATA_REQUEST ADD (
  CONSTRAINT PK_DATA_REQUEST
 PRIMARY KEY
 (DATA_REQUEST_ID));


ALTER TABLE ENCODING_DIMENSION ADD (
  CONSTRAINT PK_ENCODING_DIMENSION
 PRIMARY KEY
 (ENCODING_CD));


ALTER TABLE ENCODING_PROJECT ADD (
  CONSTRAINT PK_ENCODING_PROJECT
 PRIMARY KEY
 (ENCODING_PROJECT_ID));


ALTER TABLE ENCODING_PROJECT_LINK ADD (
  CONSTRAINT PK_ENCODING_PROJECT_LINK
 PRIMARY KEY
 (ENCODING_PROJECT_ID, ENCODING_CD));


ALTER TABLE ENCODING_SERVICE ADD (
  CONSTRAINT PK_ENCODING_SERVICE
 PRIMARY KEY
 (ENCODING_SERVICE_ID));


ALTER TABLE IRB_APPROVAL_TYPE ADD (
  CONSTRAINT PK_IRB_APPROVAL_TYPE
 PRIMARY KEY
 (IRB_APPROVAL_TYPE_CD));


ALTER TABLE MAP_AGGR_FACT ADD (
  CONSTRAINT PK_MAP_AGGR_FACT
 PRIMARY KEY
 (CONCEPT_PATH, MAP_ID));


ALTER TABLE MAP_DATA_FACT ADD (
  CONSTRAINT PK_MAP_DATA_FACT
 PRIMARY KEY
 (ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, CONCEPT_PATH, MAP_ID));


ALTER TABLE MAP_DIMENSION ADD (
  CONSTRAINT PK_MAP_DIMENSION
 PRIMARY KEY
 (MAP_ID));


ALTER TABLE MAP_REQUEST ADD (
  CONSTRAINT PK_MAP_REQUEST
 PRIMARY KEY
 (MAP_REQUEST_ID));


ALTER TABLE MAP_SRC_CONCEPT_SET ADD (
  PRIMARY KEY
 (MAP_SRC_CONCEPT_ID));


ALTER TABLE MAP_TYPE ADD (
  PRIMARY KEY
 (MAP_TYPE_CD));


ALTER TABLE QUERY_CRITERIA ADD (
  CONSTRAINT PK_QUERY_CRITERIA
 PRIMARY KEY
 (QUERY_ID, CONCEPT_PATH));


ALTER TABLE QUERY_DIMENSION ADD (
  CONSTRAINT PK_QUERY_DIMENSION
 PRIMARY KEY
 (QUERY_ID));


ALTER TABLE REQUEST_STATUS ADD (
  CONSTRAINT PK_REQUEST_STATUS
 PRIMARY KEY
 (REQUEST_STATUS_CD));


ALTER TABLE RESEARCH_TYPE ADD (
  CONSTRAINT PK_RESEARCH_TYPE
 PRIMARY KEY
 (RESEARCH_TYPE_CD));


ALTER TABLE STUDY_CRITERIA ADD (
  CONSTRAINT PK_STUDY_CRITERIA
 PRIMARY KEY
 (DATA_REQUEST_ID, CONCEPT_PATH));


ALTER TABLE USER_INFO ADD (
  CONSTRAINT PK_USER_INFO
 PRIMARY KEY
 (USER_ID));


ALTER TABLE CONCEPT_DIMENSION ADD (
  CONSTRAINT FK_CONCEPT_DIMEN_ENCODING_DIME 
 FOREIGN KEY (ENCODING_CD) 
 REFERENCES ENCODING_DIMENSION (ENCODING_CD));


ALTER TABLE DATA_REQUEST ADD (
  CONSTRAINT FK_DATA_REQUEST_IRB_APPROVAL_ 
 FOREIGN KEY (IRB_APPROVAL_TYPE_CD) 
 REFERENCES IRB_APPROVAL_TYPE (IRB_APPROVAL_TYPE_CD),
  CONSTRAINT FK_DATA_REQUEST_REQUEST_STATUS 
 FOREIGN KEY (REQUEST_STATUS_CD) 
 REFERENCES REQUEST_STATUS (REQUEST_STATUS_CD),
  CONSTRAINT FK_DATA_REQUEST_RESEARCH_TYPE 
 FOREIGN KEY (RESEARCH_TYPE_CD) 
 REFERENCES RESEARCH_TYPE (RESEARCH_TYPE_CD),
  CONSTRAINT FK_DATA_REQUEST_USER_INFO 
 FOREIGN KEY (USER_ID) 
 REFERENCES USER_INFO (USER_ID));


ALTER TABLE ENCODING_PROJECT ADD (
  CONSTRAINT FK_ENCODING_PROJ_ENCODING_SERV 
 FOREIGN KEY (ENCODING_SERVICE_ID) 
 REFERENCES ENCODING_SERVICE (ENCODING_SERVICE_ID));


ALTER TABLE ENCODING_PROJECT_LINK ADD (
  CONSTRAINT FK_ENCODING_PROJ_ENCODING_DIME 
 FOREIGN KEY (ENCODING_CD) 
 REFERENCES ENCODING_DIMENSION (ENCODING_CD),
  CONSTRAINT FK_ENCODING_PROJ_ENCODING_PROJ 
 FOREIGN KEY (ENCODING_PROJECT_ID) 
 REFERENCES ENCODING_PROJECT (ENCODING_PROJECT_ID));


ALTER TABLE MAP_AGGR_FACT ADD (
  CONSTRAINT FK_MAP_AGGR_FACT_CONCEPT_DIME 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH),
  CONSTRAINT FK_MAP_AGGR_FACT_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES MAP_DIMENSION (MAP_ID));


ALTER TABLE MAP_DATA_FACT ADD (
  CONSTRAINT FK_MAP_DATA_FACT_CONCEPT_DIME 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH),
  CONSTRAINT FK_MAP_DATA_FACT_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES MAP_DIMENSION (MAP_ID));


ALTER TABLE MAP_DIMENSION ADD (
  FOREIGN KEY (MAP_TYPE_CD) 
 REFERENCES MAP_TYPE (MAP_TYPE_CD),
  FOREIGN KEY (TARGET_CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH));


ALTER TABLE MAP_REQUEST ADD (
  CONSTRAINT FK_MAP_REQUEST_CONCEPT_DIMENS 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH),
  CONSTRAINT FK_MAP_REQUEST_ENCODING_DIMEN2 
 FOREIGN KEY (TARGET_ENCODING) 
 REFERENCES ENCODING_DIMENSION (ENCODING_CD),
  CONSTRAINT FK_MAP_REQUEST_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES MAP_DIMENSION (MAP_ID),
  CONSTRAINT FK_MAP_REQUEST_USER_INFO 
 FOREIGN KEY (USER_ID) 
 REFERENCES USER_INFO (USER_ID));


ALTER TABLE MAP_SRC_CONCEPT_SET ADD (
  FOREIGN KEY (MAP_ID) 
 REFERENCES MAP_DIMENSION (MAP_ID));


ALTER TABLE OBSERVATION_FACT ADD (
  CONSTRAINT FK_OBS_F_CONCEPT_DIMENSION 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH));


ALTER TABLE QUERY_CRITERIA ADD (
  CONSTRAINT FK_QUERY_CRITERI_CONCEPT_DIMEN 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH),
  CONSTRAINT FK_QUERY_CRITERI_QUERY_DIMENSI 
 FOREIGN KEY (QUERY_ID) 
 REFERENCES QUERY_DIMENSION (QUERY_ID));


ALTER TABLE QUERY_DIMENSION ADD (
  CONSTRAINT FK_QUERY_DIMENSION_USER_INFO 
 FOREIGN KEY (USER_ID) 
 REFERENCES USER_INFO (USER_ID));


ALTER TABLE STUDY_CRITERIA ADD (
  CONSTRAINT FK_STUDY_CRITERIA_DATA_REQUEST 
 FOREIGN KEY (DATA_REQUEST_ID) 
 REFERENCES DATA_REQUEST (DATA_REQUEST_ID),
  CONSTRAINT FK_STUDY_CRITERI_CONCEPT_DIMEN 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES CONCEPT_DIMENSION (CONCEPT_PATH),
  CONSTRAINT FK_STUDY_CRITERI_ENCODING_DIME 
 FOREIGN KEY (ENCODING_REQD) 
 REFERENCES ENCODING_DIMENSION (ENCODING_CD));