--
-- Create Schema Script 
--   Schema             : I2B2DEMODATA 
--   Script Created by  : I2B2DEMODATA 
--   Script Created at  : 6/30/2009 4:24:34 PM 


CREATE TABLE I2B2DEMODATA.SET_UPLOAD_STATUS
(
  UPLOAD_ID        NUMERIC 		NOT NULL,
  SET_TYPE_ID      INT			NOT NULL,
  SOURCE_CD        VARCHAR(50)            NOT NULL,
  NO_OF_RECORD     NUMERIC,
  LOADED_RECORD    NUMERIC,
  DELETED_RECORD   NUMERIC,
  LOAD_DATE        DATETIME                         NOT NULL,
  END_DATE         DATETIME,
  LOAD_STATUS      VARCHAR(100),
  "MESSAGE"          VARCHAR(4000),
  INPUT_FILE_NAME  VARCHAR(500),
  LOG_FILE_NAME    VARCHAR(500),
  TRANSFORM_NAME   VARCHAR(500)
)
;


CREATE TABLE I2B2DEMODATA.SOURCE_MASTER
(
  SOURCE_CD    VARCHAR(50)                NOT NULL,
  DESCRIPTION  VARCHAR(300),
  CREATE_DATE  DATETIME
)
;


CREATE TABLE I2B2DEMODATA.SET_TYPE
(
  ID           INT 			NOT NULL,
  NAME         VARCHAR(500),
  CREATE_DATE  DATETIME
)
;


CREATE TABLE I2B2DEMODATA.UPLOAD_STATUS
(
  UPLOAD_ID        NUMERIC(38)		  NOT NULL,
  UPLOAD_LABEL     VARCHAR(500)           NOT NULL,
  USER_ID          VARCHAR(100)           NOT NULL,
  SOURCE_CD        VARCHAR(50)            NOT NULL,
  NO_OF_RECORD     NUMERIC,
  LOADED_RECORD    NUMERIC,
  DELETED_RECORD   NUMERIC,
  LOAD_DATE        DATETIME                         NOT NULL,
  END_DATE         DATETIME,
  LOAD_STATUS      VARCHAR(100),
  "MESSAGE"          VARCHAR(4000),
  INPUT_FILE_NAME  VARCHAR(500),
  LOG_FILE_NAME    VARCHAR(500),
  TRANSFORM_NAME   VARCHAR(500)
)
;


CREATE TABLE I2B2DEMODATA.DATAMART_REPORT
(
  TOTAL_PATIENT          NUMERIC(38),
  TOTAL_OBSERVATIONFACT  NUMERIC(38),
  TOTAL_EVENT            NUMERIC(38),
  REPORT_DATE            DATETIME
)
;


CREATE TABLE I2B2DEMODATA.ARCHIVE_OBSERVATION_FACT
(
  ENCOUNTER_NUM      NUMERIC(38)                 NOT NULL,
  PATIENT_NUM        NUMERIC(38)                 NOT NULL,
  CONCEPT_CD         VARCHAR(50)          NOT NULL,
  PROVIDER_ID        VARCHAR(50)          NOT NULL,
  START_DATE         DATETIME                       NOT NULL,
  MODIFIER_CD        VARCHAR(100)         NOT NULL,
  VALTYPE_CD         VARCHAR(50),
  TVAL_CHAR          VARCHAR(255),
  NVAL_NUM           NUMERIC(18,5),
  VALUEFLAG_CD       VARCHAR(50),
  QUANTITY_NUM       NUMERIC(18,5),
  UNITS_CD           VARCHAR(50),
  END_DATE           DATETIME,
  LOCATION_CD        VARCHAR(50),
  CONFIDENCE_NUM     NUMERIC(18,5),
  OBSERVATION_BLOB   VARCHAR(8192),
  UPDATE_DATE        DATETIME,
  DOWNLOAD_DATE      DATETIME,
  IMPORT_DATE        DATETIME,
  SOURCESYSTEM_CD    VARCHAR(50),
  UPLOAD_ID          NUMERIC(38),
  ARCHIVE_UPLOAD_ID  NUMERIC(22)
)
;


CREATE TABLE I2B2DEMODATA.VISIT_DIMENSION
(
  ENCOUNTER_NUM     NUMERIC(38)                  NOT NULL,
  PATIENT_NUM       NUMERIC(38)                  NOT NULL,
  ACTIVE_STATUS_CD  VARCHAR(50),
  START_DATE        DATETIME,
  END_DATE          DATETIME,
  INOUT_CD          VARCHAR(50),
  LOCATION_CD       VARCHAR(50),
  LOCATION_PATH     VARCHAR(255),
  VISIT_BLOB        VARCHAR(8192),
  UPDATE_DATE       DATETIME,
  DOWNLOAD_DATE     DATETIME,
  IMPORT_DATE       DATETIME,
  SOURCESYSTEM_CD   VARCHAR(50),
  UPLOAD_ID         NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.PROVIDER_DIMENSION
(
  PROVIDER_ID      VARCHAR(50)            NOT NULL,
  PROVIDER_PATH    VARCHAR(255)           NOT NULL,
  NAME_CHAR        VARCHAR(850),
  PROVIDER_BLOB    VARCHAR(8192),
  UPDATE_DATE      DATETIME,
  DOWNLOAD_DATE    DATETIME,
  IMPORT_DATE      DATETIME,
  SOURCESYSTEM_CD  VARCHAR(50),
  UPLOAD_ID        NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.PATIENT_DIMENSION
(
  PATIENT_NUM        NUMERIC(38)                 NOT NULL,
  VITAL_STATUS_CD    VARCHAR(50),
  BIRTH_DATE         DATETIME,
  DEATH_DATE         DATETIME,
  SEX_CD             VARCHAR(50),
  AGE_IN_YEARS_NUM   NUMERIC(38),
  LANGUAGE_CD        VARCHAR(50),
  RACE_CD            VARCHAR(50),
  MARITAL_STATUS_CD  VARCHAR(50),
  RELIGION_CD        VARCHAR(50),
  ZIP_CD             VARCHAR(50),
  STATECITYZIP_PATH  VARCHAR(255),
  PATIENT_BLOB       VARCHAR(8192),
  UPDATE_DATE        DATETIME,
  DOWNLOAD_DATE      DATETIME,
  IMPORT_DATE        DATETIME,
  SOURCESYSTEM_CD    VARCHAR(50),
  UPLOAD_ID          NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.OBSERVATION_FACT
(
  ENCOUNTER_NUM        NUMERIC(38)               NOT NULL,
  PATIENT_NUM          NUMERIC(38)               NOT NULL,
  CONCEPT_CD           VARCHAR(50)        NOT NULL,
  PROVIDER_ID          VARCHAR(50)        NOT NULL,
  START_DATE           DATETIME                     NOT NULL,
  MODIFIER_CD          VARCHAR(100)       NOT NULL,
  VALTYPE_CD           VARCHAR(50),
  TVAL_CHAR            VARCHAR(255),
  NVAL_NUM             NUMERIC(18,5),
  VALUEFLAG_CD         VARCHAR(50),
  QUANTITY_NUM         NUMERIC(18,5),
  UNITS_CD             VARCHAR(50),
  END_DATE             DATETIME,
  LOCATION_CD          VARCHAR(50),
  CONFIDENCE_NUM       NUMERIC(18,5),
  OBSERVATION_BLOB     VARCHAR(8192),
  UPDATE_DATE          DATETIME,
  DOWNLOAD_DATE        DATETIME,
  IMPORT_DATE          DATETIME,
  SOURCESYSTEM_CD      VARCHAR(50),
  UPLOAD_ID            NUMERIC(38),
  CONCEPT_PATH         VARCHAR(255),
  OBSERVATION_FACT_ID  NUMERIC(38)	NOT NULL DEFAULT AUTOINCREMENT
)
;


CREATE TABLE I2B2DEMODATA.CONCEPT_DIMENSION
(
  CONCEPT_CD       VARCHAR(50)            NOT NULL,
  CONCEPT_PATH     VARCHAR(255)           NOT NULL,
  NAME_CHAR        VARCHAR(2000),
  CONCEPT_BLOB     VARCHAR(8192),
  UPDATE_DATE      DATETIME,
  DOWNLOAD_DATE    DATETIME,
  IMPORT_DATE      DATETIME,
  SOURCESYSTEM_CD  VARCHAR(50),
  UPLOAD_ID        NUMERIC(38),
  ENCODING_CD      VARCHAR(25)
)
;


CREATE TABLE I2B2DEMODATA.CODE_LOOKUP
(
  TABLE_CD         VARCHAR(100)           NOT NULL,
  COLUMN_CD        VARCHAR(100)           NOT NULL,
  CODE_CD          VARCHAR(50)            NOT NULL,
  NAME_CHAR        VARCHAR(255),
  LOOKUP_BLOB      VARCHAR(8192),
  UPLOAD_DATE      DATETIME,
  UPDATE_DATE      DATETIME,
  DOWNLOAD_DATE    DATETIME,
  IMPORT_DATE      DATETIME,
  SOURCESYSTEM_CD  VARCHAR(50),
  UPLOAD_ID        NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.PATIENT_MAPPING
(
  PATIENT_IDE         VARCHAR(200)        NOT NULL,
  PATIENT_IDE_SOURCE  VARCHAR(50)         NOT NULL,
  PATIENT_NUM         NUMERIC(38)                NOT NULL,
  PATIENT_IDE_STATUS  VARCHAR(50),
  UPLOAD_DATE         DATETIME,
  UPDATE_DATE         DATETIME,
  DOWNLOAD_DATE       DATETIME,
  IMPORT_DATE         DATETIME,
  SOURCESYSTEM_CD     VARCHAR(50),
  UPLOAD_ID           NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.ENCOUNTER_MAPPING
(
  ENCOUNTER_IDE         VARCHAR(200)      NOT NULL,
  ENCOUNTER_IDE_SOURCE  VARCHAR(50)       NOT NULL,
  ENCOUNTER_NUM         NUMERIC(38)              NOT NULL,
  PATIENT_IDE           VARCHAR(200),
  PATIENT_IDE_SOURCE    VARCHAR(50),
  ENCOUNTER_IDE_STATUS  VARCHAR(50),
  UPDATE_DATE           DATETIME,
  UPLOAD_DATE           DATETIME,
  DOWNLOAD_DATE         DATETIME,
  IMPORT_DATE           DATETIME,
  SOURCESYSTEM_CD       VARCHAR(50),
  UPLOAD_ID             NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.JMS_SUBSCRIPTIONS
(
  CLIENTID  VARCHAR(128)                  NOT NULL,
  SUBNAME   VARCHAR(128)                  NOT NULL,
  TOPIC     VARCHAR(255)                  NOT NULL,
  SELECTOR  VARCHAR(255)
)
;


CREATE TABLE I2B2DEMODATA.JMS_ROLES
(
  ROLEID  VARCHAR(32)                     NOT NULL,
  USERID  VARCHAR(32)                     NOT NULL
)
;


CREATE TABLE I2B2DEMODATA.JMS_USERS
(
  USERID    VARCHAR(32)                   NOT NULL,
  PASSWD    VARCHAR(32)                   NOT NULL,
  CLIENTID  VARCHAR(128)
)
;


CREATE TABLE I2B2DEMODATA.JMS_TRANSACTIONS
(
  TXID  INT		NOT NULL
)
;


CREATE TABLE I2B2DEMODATA.JMS_MESSAGES
(
  MESSAGEID    INT                          NOT NULL,
  DESTINATION  VARCHAR(255)               NOT NULL,
  TXID         INT,
  TXOP         CHAR(1),
  MESSAGEBLOB  VARBINARY(8192)
)
;


CREATE TABLE I2B2DEMODATA.HILOSEQUENCES
(
  SEQUENCENAME  VARCHAR(50)               NOT NULL,
  HIGHVALUES    INT                         NOT NULL
)
;


CREATE TABLE I2B2DEMODATA.TIMERS
(
  TIMERID        VARCHAR(80)              NOT NULL,
  TARGETID       VARCHAR(250)             NOT NULL,
  INITIALDATE    TIMESTAMP                   NOT NULL,
  TIMERINTERVAL  NUMERIC(19),
  INSTANCEPK     VARBINARY(8192),
  INFO           VARBINARY(8192)
)
;


CREATE TABLE I2B2DEMODATA.QT_XML_RESULT
(
  XML_RESULT_ID       NUMERIC(5) 	NOT NULL DEFAULT AUTOINCREMENT,
  RESULT_INSTANCE_ID  NUMERIC(5),
  XML_VALUE           VARCHAR(4000)
)
;


CREATE TABLE I2B2DEMODATA.QT_PATIENT_ENC_COLLECTION
(
  PATIENT_ENC_COLL_ID  NUMERIC(10)	NOT NULL DEFAULT AUTOINCREMENT,
  RESULT_INSTANCE_ID   NUMERIC(5),
  SET_INDEX            NUMERIC(10),
  PATIENT_NUM          NUMERIC(10),
  ENCOUNTER_NUM        NUMERIC(10)
)
;


CREATE TABLE I2B2DEMODATA.QT_PATIENT_SET_COLLECTION
(
  PATIENT_SET_COLL_ID  NUMERIC(10)	NOT NULL DEFAULT AUTOINCREMENT,
  RESULT_INSTANCE_ID   NUMERIC(5),
  SET_INDEX            NUMERIC(10),
  PATIENT_NUM          NUMERIC(10)
)
;


CREATE TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE
(
  RESULT_INSTANCE_ID  NUMERIC(5) 		NOT NULL DEFAULT AUTOINCREMENT,
  QUERY_INSTANCE_ID   NUMERIC(5),
  RESULT_TYPE_ID      NUMERIC(3)                 NOT NULL,
  SET_SIZE            NUMERIC(10),
  START_DATE          DATETIME                      NOT NULL,
  END_DATE            DATETIME,
  DELETE_FLAG         VARCHAR(3),
  STATUS_TYPE_ID      NUMERIC(3)                 NOT NULL
)
;


CREATE TABLE I2B2DEMODATA.QT_QUERY_INSTANCE
(
  QUERY_INSTANCE_ID  NUMERIC(5)		NOT NULL DEFAULT AUTOINCREMENT,
  QUERY_MASTER_ID    NUMERIC(5),
  USER_ID            VARCHAR(50)          NOT NULL,
  GROUP_ID           VARCHAR(50)          NOT NULL,
  BATCH_MODE         VARCHAR(50),
  START_DATE         DATETIME                       NOT NULL,
  END_DATE           DATETIME,
  DELETE_FLAG        VARCHAR(3),
  STATUS_TYPE_ID     NUMERIC(3)
)
;


CREATE TABLE I2B2DEMODATA.QT_QUERY_STATUS_TYPE
(
  STATUS_TYPE_ID  NUMERIC(3) 		NOT NULL,
  NAME            VARCHAR(100),
  DESCRIPTION     VARCHAR(200)
)
;


CREATE TABLE I2B2DEMODATA.QT_QUERY_RESULT_TYPE
(
  RESULT_TYPE_ID  NUMERIC(3) 		NOT NULL,
  NAME            VARCHAR(100),
  DESCRIPTION     VARCHAR(200)
)
;


CREATE TABLE I2B2DEMODATA.QT_QUERY_MASTER
(
  QUERY_MASTER_ID   NUMERIC(5) 		  NOT NULL DEFAULT AUTOINCREMENT,
  NAME              VARCHAR(250)          NOT NULL,
  USER_ID           VARCHAR(50)           NOT NULL,
  GROUP_ID          VARCHAR(50)           NOT NULL,
  CREATE_DATE       DATETIME                        NOT NULL,
  DELETE_DATE       DATETIME,
  DELETE_FLAG       VARCHAR(3),
  GENERATED_SQL     VARCHAR(8192),
  REQUEST_XML       VARCHAR(8192),
  I2B2_REQUEST_XML  VARCHAR(8192)
)
;


CREATE TABLE I2B2DEMODATA.USER_INFO
(
  USER_ID               VARCHAR(20)       NOT NULL,
  CREATE_USER           VARCHAR(50),
  UPDATE_USER           VARCHAR(50),
  CREATE_DATE           DATETIME,
  UPDATE_DATE           DATETIME,
  FIRST_NAME            VARCHAR(25),
  LAST_NAME             VARCHAR(25),
  MIDDLE_INITIAL        CHAR(1),
  PASSWORD              VARCHAR(25),
  DEGREE                VARCHAR(50),
  UNIVERSITY_TITLE      VARCHAR(50),
  RESEARCH_AREA         VARCHAR(200),
  ACADEMIC_INSTITUTION  VARCHAR(50),
  ADDRESS               VARCHAR(100),
  EMAIL                 VARCHAR(50),
  PHONE                 NUMERIC(15),
  PI_DEPT_AFFILIATION   VARCHAR(200)
)
;


CREATE TABLE I2B2DEMODATA.STUDY_CRITERIA
(
  DATA_REQUEST_ID  NUMERIC(38)                   NOT NULL,
  CONCEPT_PATH     VARCHAR(255)           NOT NULL,
  ENCODING_REQD    VARCHAR(25),
  PARAMETERS       VARCHAR(200),
  IS_VALID         CHAR(1)
)
;


CREATE TABLE I2B2DEMODATA.RESEARCH_TYPE
(
  RESEARCH_TYPE_CD    VARCHAR(3)          NOT NULL,
  RESEARCH_TYPE_DESC  VARCHAR(50)         NOT NULL,
  CREATE_USER         VARCHAR(50),
  UPDATE_USER         VARCHAR(50),
  CREATE_DATE         DATETIME,
  UPDATE_DATE         DATETIME
)
;


CREATE TABLE I2B2DEMODATA.REQUEST_STATUS
(
  REQUEST_STATUS_CD    CHAR(1)             NOT NULL,
  REQUEST_STATUS_DESC  VARCHAR(50)        NOT NULL,
  CREATE_USER          VARCHAR(50),
  UPDATE_USER          VARCHAR(50),
  CREATE_DATE          DATETIME,
  UPDATE_DATE          DATETIME
)
;


CREATE TABLE I2B2DEMODATA.QUERY_DIMENSION
(
  QUERY_ID     NUMERIC(38)                       NOT NULL,
  QUERY_NAME   VARCHAR(200)               NOT NULL,
  USER_ID      VARCHAR(20)                NOT NULL,
  CREATE_DATE  DATETIME,
  UPDATE_DATE  DATETIME
)
;


CREATE TABLE I2B2DEMODATA.QUERY_CRITERIA
(
  QUERY_ID      NUMERIC(38)                      NOT NULL,
  CONCEPT_PATH  VARCHAR(255)              NOT NULL,
  IS_VALID      CHAR(1)
)
;


CREATE TABLE I2B2DEMODATA.MAP_REQUEST
(
  MAP_REQUEST_ID   NUMERIC(38)                   NOT NULL,
  CONCEPT_PATH     VARCHAR(255)           NOT NULL,
  TARGET_ENCODING  VARCHAR(25)            NOT NULL,
  USER_ID          VARCHAR(20)            NOT NULL,
  REQUEST_DESC     VARCHAR(200),
  REQUEST_DATE     DATETIME,
  MAP_ID           NUMERIC(38)
)
;


CREATE TABLE I2B2DEMODATA.MAP_DIMENSION
(
  MAP_ID           NUMERIC(38)                   NOT NULL,
  MAP_NAME         VARCHAR(25)            NOT NULL,
  CONCEPT_PATH     VARCHAR(255)           NOT NULL,
  TARGET_ENCODING  VARCHAR(25)            NOT NULL,
  MAP_PATH         VARCHAR(200)           NOT NULL,
  MAP_DESC         VARCHAR(200),
  IMPORT_DATE      DATETIME                         NOT NULL,
  UPDATE_DATE      DATETIME,
  UPLOAD_USER      VARCHAR(50),
  UPDATE_USER      VARCHAR(50),
  LAST_RUN_DATE    DATETIME
)
;


CREATE TABLE I2B2DEMODATA.MAP_DATA_FACT
(
  ENCOUNTER_NUM        NUMERIC(38)               NOT NULL,
  PATIENT_NUM          NUMERIC(38)               NOT NULL,
  CONCEPT_CD           VARCHAR(50)        NOT NULL,
  PROVIDER_ID          VARCHAR(20)        NOT NULL,
  START_DATE           DATETIME                     NOT NULL,
  MODIFIER_CD          VARCHAR(100)       NOT NULL,
  CONCEPT_PATH         VARCHAR(255)       NOT NULL,
  MAP_ID               NUMERIC(38)               NOT NULL,
  VALTYPE_CD           VARCHAR(3),
  TVAL_CHAR            VARCHAR(255),
  NVAL_NUM             NUMERIC(18,5),
  VALUEFLAG_CD         CHAR(1),
  QUANTITY_NUM         NUMERIC(18,5),
  UNITS_CD             VARCHAR(100),
  END_DATE             DATETIME,
  LOCATION_CD          VARCHAR(50),
  CONFIDENCE_NUM       NUMERIC(18),
  OBSERVATION_BLOB     VARCHAR(8192),
  UPDATE_DATE          DATETIME,
  DOWNLOAD_DATE        DATETIME,
  IMPORT_DATE          DATETIME,
  SOURCESYSTEM_CD      VARCHAR(50),
  UPLOAD_ID            NUMERIC(38),
  OBSERVATION_FACT_ID  NUMERIC(38)               NOT NULL,
  MAP_DATE             DATETIME
)
;


CREATE TABLE I2B2DEMODATA.MAP_AGGR_FACT
(
  CONCEPT_PATH    VARCHAR(255)            NOT NULL,
  MAP_ID          NUMERIC(38)                    NOT NULL,
  VALTYPE_CD      VARCHAR(3),
  TVAL_CHAR       VARCHAR(255),
  NVAL_NUM        NUMERIC(18,5),
  QUANTITY_NUM    NUMERIC(18,5),
  UNITS_CD        VARCHAR(100),
  CONFIDENCE_NUM  NUMERIC(18),
  MAP_DATE        DATETIME
)
;


CREATE TABLE I2B2DEMODATA.IRB_APPROVAL_TYPE
(
  IRB_APPROVAL_TYPE_CD    VARCHAR(3)      NOT NULL,
  IRB_APPROVAL_TYPE_DESC  VARCHAR(50)     NOT NULL,
  CREATE_USER             VARCHAR(50),
  UPDATE_USER             VARCHAR(50),
  CREATE_DATE             DATETIME,
  UPDATE_DATE             DATETIME
)
;


CREATE TABLE I2B2DEMODATA.ENCODING_SERVICE
(
  ENCODING_SERVICE_ID  NUMERIC(10)               NOT NULL,
  SERVICE_URL          VARCHAR(200)
)
;


CREATE TABLE I2B2DEMODATA.ENCODING_PROJECT_LINK
(
  ENCODING_PROJECT_ID  NUMERIC(10)               NOT NULL,
  ENCODING_CD          VARCHAR(25)        NOT NULL
)
;


CREATE TABLE I2B2DEMODATA.ENCODING_PROJECT
(
  ENCODING_PROJECT_ID  NUMERIC(10)               NOT NULL,
  PROJECT_NAME         VARCHAR(50),
  PROJECT_VERSION      VARCHAR(10),
  PROJECT_ID           NUMERIC(10),
  ENCODING_SERVICE_ID  NUMERIC(10)
)
;


CREATE TABLE I2B2DEMODATA.ENCODING_DIMENSION
(
  ENCODING_CD    VARCHAR(25)              NOT NULL,
  ENCODING_NAME  VARCHAR(25)              NOT NULL,
  ENCODING_DESC  VARCHAR(200),
  CREATE_USER    VARCHAR(50),
  UPDATE_USER    VARCHAR(50),
  CREATE_DATE    DATETIME,
  UPDATE_DATE    DATETIME,
  CDE_PUBLIC_ID  NUMERIC(10),
  CDE_VERSION    VARCHAR(10)
)
;


CREATE TABLE I2B2DEMODATA.DATA_REQUEST
(
  DATA_REQUEST_ID       NUMERIC(38)              NOT NULL,
  PROJECT_TITLE         VARCHAR(100),
  IS_RESEARCH_STUDY     CHAR(1),
  RESEARCH_TYPE_CD      VARCHAR(3),
  IS_IRB_APPROVED       CHAR(1),
  IRB_APPROVAL_NO       VARCHAR(25),
  IRB_APPROVAL_TYPE_CD  VARCHAR(3),
  IRB_APPROVAL_DOC      VARCHAR(200),
  IRB_APPROVAL_DATE     DATETIME,
  IACUC_APPROVAL_DOC    VARCHAR(200),
  IACUC_APPROVAL_NO     VARCHAR(25),
  IACUC_APPROVAL_DATE   DATETIME,
  DATA_REQUEST_REASON   VARCHAR(4000),
  USER_ID               VARCHAR(20)       NOT NULL,
  REQUEST_STATUS_CD     CHAR(1),
  REQUEST_DATE          DATETIME,
  REQUESTER_NAME        VARCHAR(50),
  REQUESTER_ADDRESS     VARCHAR(100),
  REQUESTER_PHONE       NUMERIC(15),
  REQUESTER_EMAIL       VARCHAR(50),
  REQ_EXISTS            CHAR(1),
  RA_PROCESS_NOTES      VARCHAR(250),
  RA_DATA_NOTES         VARCHAR(250)
)
;


CREATE INDEX QT_IDX_QM_UGID ON I2B2DEMODATA.QT_QUERY_MASTER
(USER_ID, GROUP_ID, NAME)
;


CREATE INDEX QT_IDX_QI_UGID ON I2B2DEMODATA.QT_QUERY_INSTANCE
(USER_ID, GROUP_ID)
;


CREATE INDEX QT_IDX_QI_MSTARTID ON I2B2DEMODATA.QT_QUERY_INSTANCE
(QUERY_MASTER_ID, START_DATE)
;


CREATE INDEX QT_IDX_QPSC_RIID ON I2B2DEMODATA.QT_PATIENT_SET_COLLECTION
(RESULT_INSTANCE_ID)
;


CREATE INDEX JMS_MESSAGES_TXOP_TXID ON I2B2DEMODATA.JMS_MESSAGES
(TXOP, TXID)
;


CREATE INDEX JMS_MESSAGES_DESTINATION ON I2B2DEMODATA.JMS_MESSAGES
(DESTINATION)
;


CREATE INDEX EM_UPLOADID_IDX ON I2B2DEMODATA.ENCOUNTER_MAPPING
(UPLOAD_ID)
;


CREATE INDEX EM_IDX_ENCPATH ON I2B2DEMODATA.ENCOUNTER_MAPPING
(ENCOUNTER_IDE, ENCOUNTER_IDE_SOURCE, PATIENT_IDE, PATIENT_IDE_SOURCE, ENCOUNTER_NUM)
;


CREATE INDEX EM_ENCNUM_IDX ON I2B2DEMODATA.ENCOUNTER_MAPPING
(ENCOUNTER_NUM)
;


CREATE INDEX PM_UPLOADID_IDX ON I2B2DEMODATA.PATIENT_MAPPING
(UPLOAD_ID)
;


CREATE INDEX PM_PATNUM_IDX ON I2B2DEMODATA.PATIENT_MAPPING
(PATIENT_NUM)
;


CREATE INDEX PM_ENCPNUM_IDX ON I2B2DEMODATA.PATIENT_MAPPING
(PATIENT_IDE, PATIENT_IDE_SOURCE, PATIENT_NUM)
;


CREATE INDEX CL_IDX_NAME_CHAR ON I2B2DEMODATA.CODE_LOOKUP
(NAME_CHAR)
;


CREATE INDEX CL_IDX_UPLOADID ON I2B2DEMODATA.CODE_LOOKUP
(UPLOAD_ID)
;


CREATE INDEX CD_UPLOADID_IDX ON I2B2DEMODATA.CONCEPT_DIMENSION
(UPLOAD_ID)
;


CREATE INDEX FACT_NOLOB ON I2B2DEMODATA.OBSERVATION_FACT
(PATIENT_NUM, START_DATE, CONCEPT_CD, ENCOUNTER_NUM, NVAL_NUM, 
TVAL_CHAR, VALTYPE_CD, MODIFIER_CD, VALUEFLAG_CD, PROVIDER_ID, 
QUANTITY_NUM, UNITS_CD, END_DATE, LOCATION_CD, CONFIDENCE_NUM, 
UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, UPLOAD_ID)
;


CREATE INDEX FACT_CNPT_PAT_ENCT_IDX ON I2B2DEMODATA.OBSERVATION_FACT
(CONCEPT_CD, PATIENT_NUM, ENCOUNTER_NUM)
;


CREATE INDEX FACT_PATCON_DATE_PRVD_IDX ON I2B2DEMODATA.OBSERVATION_FACT
(PATIENT_NUM, CONCEPT_CD, START_DATE, END_DATE, ENCOUNTER_NUM, 
PROVIDER_ID, NVAL_NUM, VALTYPE_CD)
;


CREATE INDEX PD_IDX_DATES ON I2B2DEMODATA.PATIENT_DIMENSION
(PATIENT_NUM, VITAL_STATUS_CD, BIRTH_DATE, DEATH_DATE)
;


CREATE INDEX PD_IDX_ALLPATIENTDIM ON I2B2DEMODATA.PATIENT_DIMENSION
(PATIENT_NUM, VITAL_STATUS_CD, BIRTH_DATE, DEATH_DATE, SEX_CD, 
AGE_IN_YEARS_NUM, LANGUAGE_CD, RACE_CD, MARITAL_STATUS_CD, RELIGION_CD, 
ZIP_CD)
;


CREATE INDEX PD_IDX_STATECITYZIP ON I2B2DEMODATA.PATIENT_DIMENSION
(STATECITYZIP_PATH, PATIENT_NUM)
;


CREATE INDEX PATD_UPLOADID_IDX ON I2B2DEMODATA.PATIENT_DIMENSION
(UPLOAD_ID)
;


CREATE INDEX PD_IDX_NAME_CHAR ON I2B2DEMODATA.PROVIDER_DIMENSION
(PROVIDER_ID, NAME_CHAR)
;


CREATE INDEX PROD_UPLOADID_IDX ON I2B2DEMODATA.PROVIDER_DIMENSION
(UPLOAD_ID)
;


CREATE INDEX VD_UPLOADID_IDX ON I2B2DEMODATA.VISIT_DIMENSION
(UPLOAD_ID)
;


CREATE INDEX VISITDIM_EN_PN_LP_IO_SD_IDX ON I2B2DEMODATA.VISIT_DIMENSION
(ENCOUNTER_NUM, PATIENT_NUM, LOCATION_PATH, INOUT_CD, START_DATE, 
END_DATE)
;


CREATE INDEX VISITDIM_STD_EDD_IDX ON I2B2DEMODATA.VISIT_DIMENSION
(START_DATE, END_DATE)
;


CREATE INDEX PK_ARCHIVE_OBSFACT ON I2B2DEMODATA.ARCHIVE_OBSERVATION_FACT
(ENCOUNTER_NUM, PATIENT_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, 
MODIFIER_CD, ARCHIVE_UPLOAD_ID)
;


ALTER TABLE I2B2DEMODATA.SET_UPLOAD_STATUS ADD (
  CONSTRAINT PK_UP_UPSTATUS_IDSETTYPEID
 PRIMARY KEY
 (UPLOAD_ID, SET_TYPE_ID));

ALTER TABLE I2B2DEMODATA.SOURCE_MASTER ADD (
  CONSTRAINT PK_SOURCEMASTER_SOURCECD
 PRIMARY KEY
 (SOURCE_CD));

ALTER TABLE I2B2DEMODATA.SET_TYPE ADD (
  CONSTRAINT PK_ST_ID
 PRIMARY KEY
 (ID));

ALTER TABLE I2B2DEMODATA.UPLOAD_STATUS ADD (
  CONSTRAINT PK_UP_UPSTATUS_UPLOADID
 PRIMARY KEY
 (UPLOAD_ID));

ALTER TABLE I2B2DEMODATA.VISIT_DIMENSION ADD (
  CONSTRAINT VISIT_DIMENSION_PK
 PRIMARY KEY
 (ENCOUNTER_NUM, PATIENT_NUM));

ALTER TABLE I2B2DEMODATA.PROVIDER_DIMENSION ADD (
  CONSTRAINT PROVIDER_DIMENSION_PK
 PRIMARY KEY
 (PROVIDER_PATH, PROVIDER_ID));

ALTER TABLE I2B2DEMODATA.PATIENT_DIMENSION ADD (
  CONSTRAINT PATIENT_DIMENSION_PK
 PRIMARY KEY
 (PATIENT_NUM));

ALTER TABLE I2B2DEMODATA.OBSERVATION_FACT ADD (
  CONSTRAINT OBSERVATION_FACT_PK
 PRIMARY KEY
 (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD));
 
ALTER TABLE I2B2DEMODATA.CONCEPT_DIMENSION ADD (
  CONSTRAINT CONCEPT_DIMENSION_PK
 PRIMARY KEY
 (CONCEPT_PATH));

ALTER TABLE I2B2DEMODATA.CODE_LOOKUP ADD (
  CONSTRAINT CODE_LOOKUP_PK
 PRIMARY KEY
 (TABLE_CD, COLUMN_CD, CODE_CD));

ALTER TABLE I2B2DEMODATA.PATIENT_MAPPING ADD (
  CONSTRAINT PATIENT_MAPPING_PK
 PRIMARY KEY
 (PATIENT_IDE, PATIENT_IDE_SOURCE));

ALTER TABLE I2B2DEMODATA.ENCOUNTER_MAPPING ADD (
  CONSTRAINT ENCOUNTER_MAPPING_PK
 PRIMARY KEY
 (ENCOUNTER_IDE, ENCOUNTER_IDE_SOURCE));

ALTER TABLE I2B2DEMODATA.JMS_SUBSCRIPTIONS ADD (
  PRIMARY KEY
 (CLIENTID, SUBNAME));

ALTER TABLE I2B2DEMODATA.JMS_ROLES ADD (
  PRIMARY KEY
 (USERID, ROLEID));

ALTER TABLE I2B2DEMODATA.JMS_USERS ADD (
  PRIMARY KEY
 (USERID));

ALTER TABLE I2B2DEMODATA.JMS_TRANSACTIONS ADD (
  PRIMARY KEY
 (TXID));

ALTER TABLE I2B2DEMODATA.JMS_MESSAGES ADD (
  PRIMARY KEY
 (MESSAGEID, DESTINATION));

ALTER TABLE I2B2DEMODATA.HILOSEQUENCES ADD (
  CONSTRAINT HILO_PK
 PRIMARY KEY
 (SEQUENCENAME));

ALTER TABLE I2B2DEMODATA.TIMERS ADD (
  CONSTRAINT TIMERS_PK
 PRIMARY KEY
 (TIMERID, TARGETID));

ALTER TABLE I2B2DEMODATA.QT_XML_RESULT ADD (
  PRIMARY KEY
 (XML_RESULT_ID));

ALTER TABLE I2B2DEMODATA.QT_PATIENT_ENC_COLLECTION ADD (
  PRIMARY KEY
 (PATIENT_ENC_COLL_ID));

ALTER TABLE I2B2DEMODATA.QT_PATIENT_SET_COLLECTION ADD (
  PRIMARY KEY
 (PATIENT_SET_COLL_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE ADD (
  PRIMARY KEY
 (RESULT_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_INSTANCE ADD (
  PRIMARY KEY
 (QUERY_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_STATUS_TYPE ADD (
  PRIMARY KEY
 (STATUS_TYPE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_RESULT_TYPE ADD (
  PRIMARY KEY
 (RESULT_TYPE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_MASTER ADD (
  PRIMARY KEY
 (QUERY_MASTER_ID));

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

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

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

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

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

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

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

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

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION
	ADD CONSTRAINT UQ_MAP_DIMENSION_MAP_NAME UNIQUE (MAP_NAME);


ALTER TABLE I2B2DEMODATA.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 I2B2DEMODATA.MAP_AGGR_FACT ADD (
  CONSTRAINT PK_MAP_AGGR_FACT
 PRIMARY KEY
 (CONCEPT_PATH, MAP_ID));

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

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

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

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

ALTER TABLE I2B2DEMODATA.ENCODING_DIMENSION ADD (
  CONSTRAINT PK_ENCODING_DIMENSION
 PRIMARY KEY
 (ENCODING_CD));
 
ALTER TABLE I2B2DEMODATA.DATA_REQUEST ADD (
  CONSTRAINT PK_DATA_REQUEST
 PRIMARY KEY
 (DATA_REQUEST_ID));

ALTER TABLE I2B2DEMODATA.SET_UPLOAD_STATUS ADD (
  CONSTRAINT FK_UP_SET_TYPE_ID 
 FOREIGN KEY (SET_TYPE_ID) 
 REFERENCES I2B2DEMODATA.SET_TYPE (ID));

ALTER TABLE I2B2DEMODATA.OBSERVATION_FACT ADD (
  CONSTRAINT FK_OBS_F_PATIENT_DIMENSION 
 FOREIGN KEY (PATIENT_NUM) 
 REFERENCES I2B2DEMODATA.PATIENT_DIMENSION (PATIENT_NUM));

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

ALTER TABLE I2B2DEMODATA.OBSERVATION_FACT ADD CONSTRAINT FK_OBS__F_VISIT_DIMENSION 
	FOREIGN KEY (ENCOUNTER_NUM, PATIENT_NUM) REFERENCES I2B2DEMODATA.VISIT_DIMENSION (ENCOUNTER_NUM, PATIENT_NUM);

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

ALTER TABLE I2B2DEMODATA.QT_XML_RESULT ADD (
  CONSTRAINT QT_FK_XMLR_RIID 
 FOREIGN KEY (RESULT_INSTANCE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE (RESULT_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_PATIENT_ENC_COLLECTION ADD (
  CONSTRAINT QT_FK_PESC_RI 
 FOREIGN KEY (RESULT_INSTANCE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE (RESULT_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_PATIENT_SET_COLLECTION ADD (
  CONSTRAINT QT_FK_PSC_RI 
 FOREIGN KEY (RESULT_INSTANCE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE (RESULT_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE ADD (
  CONSTRAINT QT_FK_QRI_RID 
 FOREIGN KEY (QUERY_INSTANCE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_INSTANCE (QUERY_INSTANCE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE ADD (
  CONSTRAINT QT_FK_QRI_RTID 
   FOREIGN KEY (RESULT_TYPE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_RESULT_TYPE (RESULT_TYPE_ID));
 
ALTER TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE ADD (
  CONSTRAINT QT_FK_QRI_STID 
  FOREIGN KEY (STATUS_TYPE_ID) 
  REFERENCES I2B2DEMODATA.QT_QUERY_STATUS_TYPE (STATUS_TYPE_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_INSTANCE ADD (
  CONSTRAINT QT_FK_QI_MID 
 FOREIGN KEY (QUERY_MASTER_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_MASTER (QUERY_MASTER_ID));

ALTER TABLE I2B2DEMODATA.QT_QUERY_INSTANCE ADD (
  CONSTRAINT QT_FK_QI_STID 
 FOREIGN KEY (STATUS_TYPE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_STATUS_TYPE (STATUS_TYPE_ID));

ALTER TABLE I2B2DEMODATA.STUDY_CRITERIA ADD CONSTRAINT FK_STUDY_CRITERI_CONCEPT_DIMEN 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);

ALTER TABLE I2B2DEMODATA.STUDY_CRITERIA ADD CONSTRAINT FK_STUDY_CRITERI_ENCODING_DIME 
	FOREIGN KEY (ENCODING_REQD) REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD);

ALTER TABLE I2B2DEMODATA.STUDY_CRITERIA ADD CONSTRAINT FK_STUDY_CRITERIA_DATA_REQUEST 
	FOREIGN KEY (DATA_REQUEST_ID) REFERENCES I2B2DEMODATA.DATA_REQUEST (DATA_REQUEST_ID);

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

ALTER TABLE I2B2DEMODATA.QUERY_CRITERIA ADD CONSTRAINT FK_QUERY_CRITERI_CONCEPT_DIMEN 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);

ALTER TABLE I2B2DEMODATA.QUERY_CRITERIA ADD CONSTRAINT FK_QUERY_CRITERI_QUERY_DIMENSI 
	FOREIGN KEY (QUERY_ID) REFERENCES I2B2DEMODATA.QUERY_DIMENSION (QUERY_ID);

ALTER TABLE I2B2DEMODATA.MAP_REQUEST ADD CONSTRAINT FK_MAP_REQUEST_CONCEPT_DIMENS 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);

ALTER TABLE I2B2DEMODATA.MAP_REQUEST ADD CONSTRAINT FK_MAP_REQUEST_MAP_DIMENSION 
	FOREIGN KEY (MAP_ID) REFERENCES I2B2DEMODATA.MAP_DIMENSION (MAP_ID);

ALTER TABLE I2B2DEMODATA.MAP_REQUEST ADD CONSTRAINT FK_MAP_REQUEST_USER_INFO 
	FOREIGN KEY (USER_ID) REFERENCES I2B2DEMODATA.USER_INFO (USER_ID);

ALTER TABLE I2B2DEMODATA.MAP_REQUEST ADD CONSTRAINT FK_MAP_REQUEST_ENCODING_DIMEN2 
	FOREIGN KEY (TARGET_ENCODING) REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD);

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION ADD CONSTRAINT FK_MAP_DIMENSION_CONCEPT_DIME 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION ADD CONSTRAINT FK_MAP_DIMENSION_ENCODING_DIME 
	FOREIGN KEY (TARGET_ENCODING) REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD);

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT ADD CONSTRAINT FK_MAP_DATA_FACT_CONCEPT_DIME 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT ADD CONSTRAINT FK_MAP_DATA_FACT_MAP_DIMENSION 
	FOREIGN KEY (MAP_ID) REFERENCES I2B2DEMODATA.MAP_DIMENSION (MAP_ID);

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT ADD CONSTRAINT FK_MAP_DATA_FACT_PATIENT_DIME 
	FOREIGN KEY (PATIENT_NUM) REFERENCES I2B2DEMODATA.PATIENT_DIMENSION (PATIENT_NUM);

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT ADD CONSTRAINT FK_MAP_DATA_FACT_VISIT_DIMENS 
	FOREIGN KEY (ENCOUNTER_NUM, PATIENT_NUM) REFERENCES I2B2DEMODATA.VISIT_DIMENSION (ENCOUNTER_NUM, PATIENT_NUM);

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

ALTER TABLE I2B2DEMODATA.ENCODING_PROJECT_LINK ADD CONSTRAINT FK_ENCODING_PROJ_ENCODING_DIME 
	FOREIGN KEY (ENCODING_CD) REFERENCES ENCODING_DIMENSION (ENCODING_CD);

ALTER TABLE I2B2DEMODATA.ENCODING_PROJECT_LINK ADD CONSTRAINT FK_ENCODING_PROJ_ENCODING_PROJ 
	FOREIGN KEY (ENCODING_PROJECT_ID) REFERENCES ENCODING_PROJECT (ENCODING_PROJECT_ID);

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

ALTER TABLE I2B2DEMODATA.DATA_REQUEST ADD CONSTRAINT FK_DATA_REQUEST_IRB_APPROVAL_ 
	FOREIGN KEY (IRB_APPROVAL_TYPE_CD) REFERENCES I2B2DEMODATA.IRB_APPROVAL_TYPE (IRB_APPROVAL_TYPE_CD);

ALTER TABLE I2B2DEMODATA.DATA_REQUEST ADD CONSTRAINT FK_DATA_REQUEST_REQUEST_STATUS 
	FOREIGN KEY (REQUEST_STATUS_CD) REFERENCES I2B2DEMODATA.REQUEST_STATUS (REQUEST_STATUS_CD);

ALTER TABLE I2B2DEMODATA.DATA_REQUEST ADD CONSTRAINT FK_DATA_REQUEST_RESEARCH_TYPE 
	FOREIGN KEY (RESEARCH_TYPE_CD) REFERENCES I2B2DEMODATA.RESEARCH_TYPE (RESEARCH_TYPE_CD);

ALTER TABLE I2B2DEMODATA.DATA_REQUEST ADD CONSTRAINT FK_DATA_REQUEST_USER_INFO 
	FOREIGN KEY (USER_ID) REFERENCES I2B2DEMODATA.USER_INFO (USER_ID);

--This creates a foreign key relationship between I2B2(metadata schema) and CONCEPT_DIMENSION(demodata schema)
ALTER TABLE I2B2METADATA.I2B2 ADD CONSTRAINT FK_I2B2_CONCEPT_DIMENSION 
	FOREIGN KEY (CONCEPT_PATH) REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH);
