--
-- Create Schema Script 
--   Database Version   : 10.2.0.1.0 
--   TOAD Version       : 9.7.2.5 
--   DB Connect String  : VMWARE_XE 
--   Schema             : I2B2DEMODATA 
--   Script Created by  : I2B2DEMODATA 
--   Script Created at  : 6/30/2009 4:24:34 PM 
--   Physical Location  :  
--   Notes              :  
--

-- Object Counts: 
--   Indexes: 74        Columns: 166        
--   Tables: 47         Columns: 382        Constraints: 83     


CREATE TABLE I2B2DEMODATA.SET_UPLOAD_STATUS
(
  UPLOAD_ID        NUMBER,
  SET_TYPE_ID      INTEGER,
  SOURCE_CD        VARCHAR2(50 BYTE)            NOT NULL,
  NO_OF_RECORD     NUMBER,
  LOADED_RECORD    NUMBER,
  DELETED_RECORD   NUMBER,
  LOAD_DATE        DATE                         NOT NULL,
  END_DATE         DATE,
  LOAD_STATUS      VARCHAR2(100 BYTE),
  MESSAGE          VARCHAR2(4000 BYTE),
  INPUT_FILE_NAME  VARCHAR2(500 BYTE),
  LOG_FILE_NAME    VARCHAR2(500 BYTE),
  TRANSFORM_NAME   VARCHAR2(500 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.SOURCE_MASTER
(
  SOURCE_CD    VARCHAR2(50 BYTE)                NOT NULL,
  DESCRIPTION  VARCHAR2(300 BYTE),
  CREATE_DATE  DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.SET_TYPE
(
  ID           INTEGER,
  NAME         VARCHAR2(500 BYTE),
  CREATE_DATE  DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.UPLOAD_STATUS
(
  UPLOAD_ID        NUMBER(38),
  UPLOAD_LABEL     VARCHAR2(500 BYTE)           NOT NULL,
  USER_ID          VARCHAR2(100 BYTE)           NOT NULL,
  SOURCE_CD        VARCHAR2(50 BYTE)            NOT NULL,
  NO_OF_RECORD     NUMBER,
  LOADED_RECORD    NUMBER,
  DELETED_RECORD   NUMBER,
  LOAD_DATE        DATE                         NOT NULL,
  END_DATE         DATE,
  LOAD_STATUS      VARCHAR2(100 BYTE),
  MESSAGE          VARCHAR2(4000 BYTE),
  INPUT_FILE_NAME  VARCHAR2(500 BYTE),
  LOG_FILE_NAME    VARCHAR2(500 BYTE),
  TRANSFORM_NAME   VARCHAR2(500 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.DATAMART_REPORT
(
  TOTAL_PATIENT          NUMBER(38),
  TOTAL_OBSERVATIONFACT  NUMBER(38),
  TOTAL_EVENT            NUMBER(38),
  REPORT_DATE            DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.ARCHIVE_OBSERVATION_FACT
(
  ENCOUNTER_NUM      NUMBER(38)                 NOT NULL,
  PATIENT_NUM        NUMBER(38)                 NOT NULL,
  CONCEPT_CD         VARCHAR2(50 BYTE)          NOT NULL,
  PROVIDER_ID        VARCHAR2(50 BYTE)          NOT NULL,
  START_DATE         DATE                       NOT NULL,
  MODIFIER_CD        VARCHAR2(100 BYTE)         NOT NULL,
  VALTYPE_CD         VARCHAR2(50 BYTE),
  TVAL_CHAR          VARCHAR2(255 BYTE),
  NVAL_NUM           NUMBER(18,5),
  VALUEFLAG_CD       VARCHAR2(50 BYTE),
  QUANTITY_NUM       NUMBER(18,5),
  UNITS_CD           VARCHAR2(50 BYTE),
  END_DATE           DATE,
  LOCATION_CD        VARCHAR2(50 BYTE),
  CONFIDENCE_NUM     NUMBER(18,5),
  OBSERVATION_BLOB   CLOB,
  UPDATE_DATE        DATE,
  DOWNLOAD_DATE      DATE,
  IMPORT_DATE        DATE,
  SOURCESYSTEM_CD    VARCHAR2(50 BYTE),
  UPLOAD_ID          NUMBER(38),
  ARCHIVE_UPLOAD_ID  NUMBER(22)
)
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE GLOBAL TEMPORARY TABLE I2B2DEMODATA.QUERY_GLOBAL_TEMP
(
  ENCOUNTER_NUM  NUMBER(22),
  PATIENT_NUM    NUMBER(22),
  PANEL_COUNT    NUMBER(5),
  FACT_COUNT     NUMBER(22),
  FACT_PANELS    NUMBER(5)
)
ON COMMIT PRESERVE ROWS
NOCACHE;


CREATE GLOBAL TEMPORARY TABLE I2B2DEMODATA.DX
(
  ENCOUNTER_NUM  NUMBER(22),
  PATIENT_NUM    NUMBER(22)
)
ON COMMIT PRESERVE ROWS
NOCACHE;


CREATE TABLE I2B2DEMODATA.VISIT_DIMENSION
(
  ENCOUNTER_NUM     NUMBER(38)                  NOT NULL,
  PATIENT_NUM       NUMBER(38)                  NOT NULL,
  ACTIVE_STATUS_CD  VARCHAR2(50 BYTE),
  START_DATE        DATE,
  END_DATE          DATE,
  INOUT_CD          VARCHAR2(50 BYTE),
  LOCATION_CD       VARCHAR2(50 BYTE),
  LOCATION_PATH     VARCHAR2(900 BYTE),
  VISIT_BLOB        CLOB,
  UPDATE_DATE       DATE,
  DOWNLOAD_DATE     DATE,
  IMPORT_DATE       DATE,
  SOURCESYSTEM_CD   VARCHAR2(50 BYTE),
  UPLOAD_ID         NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.PROVIDER_DIMENSION
(
  PROVIDER_ID      VARCHAR2(50 BYTE)            NOT NULL,
  PROVIDER_PATH    VARCHAR2(700 BYTE)           NOT NULL,
  NAME_CHAR        VARCHAR2(850 BYTE),
  PROVIDER_BLOB    CLOB,
  UPDATE_DATE      DATE,
  DOWNLOAD_DATE    DATE,
  IMPORT_DATE      DATE,
  SOURCESYSTEM_CD  VARCHAR2(50 BYTE),
  UPLOAD_ID        NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.PATIENT_DIMENSION
(
  PATIENT_NUM        NUMBER(38)                 NOT NULL,
  VITAL_STATUS_CD    VARCHAR2(50 BYTE),
  BIRTH_DATE         DATE,
  DEATH_DATE         DATE,
  SEX_CD             VARCHAR2(50 BYTE),
  AGE_IN_YEARS_NUM   NUMBER(38),
  LANGUAGE_CD        VARCHAR2(50 BYTE),
  RACE_CD            VARCHAR2(50 BYTE),
  MARITAL_STATUS_CD  VARCHAR2(50 BYTE),
  RELIGION_CD        VARCHAR2(50 BYTE),
  ZIP_CD             VARCHAR2(50 BYTE),
  STATECITYZIP_PATH  VARCHAR2(700 BYTE),
  PATIENT_BLOB       CLOB,
  UPDATE_DATE        DATE,
  DOWNLOAD_DATE      DATE,
  IMPORT_DATE        DATE,
  SOURCESYSTEM_CD    VARCHAR2(50 BYTE),
  UPLOAD_ID          NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.OBSERVATION_FACT
(
  ENCOUNTER_NUM        NUMBER(38)               NOT NULL,
  PATIENT_NUM          NUMBER(38)               NOT NULL,
  CONCEPT_CD           VARCHAR2(50 BYTE)        NOT NULL,
  PROVIDER_ID          VARCHAR2(50 BYTE)        NOT NULL,
  START_DATE           DATE                     NOT NULL,
  MODIFIER_CD          VARCHAR2(100 BYTE)       NOT NULL,
  VALTYPE_CD           VARCHAR2(50 BYTE),
  TVAL_CHAR            VARCHAR2(255 BYTE),
  NVAL_NUM             NUMBER(18,5),
  VALUEFLAG_CD         VARCHAR2(50 BYTE),
  QUANTITY_NUM         NUMBER(18,5),
  UNITS_CD             VARCHAR2(50 BYTE),
  END_DATE             DATE,
  LOCATION_CD          VARCHAR2(50 BYTE),
  CONFIDENCE_NUM       NUMBER(18,5),
  OBSERVATION_BLOB     CLOB,
  UPDATE_DATE          DATE,
  DOWNLOAD_DATE        DATE,
  IMPORT_DATE          DATE,
  SOURCESYSTEM_CD      VARCHAR2(50 BYTE),
  UPLOAD_ID            NUMBER(38),
  CONCEPT_PATH         VARCHAR2(700 BYTE),
  OBSERVATION_FACT_ID  NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.CONCEPT_DIMENSION
(
  CONCEPT_CD       VARCHAR2(50 BYTE)            NOT NULL,
  CONCEPT_PATH     VARCHAR2(700 BYTE)           NOT NULL,
  NAME_CHAR        VARCHAR2(2000 BYTE),
  CONCEPT_BLOB     CLOB,
  UPDATE_DATE      DATE,
  DOWNLOAD_DATE    DATE,
  IMPORT_DATE      DATE,
  SOURCESYSTEM_CD  VARCHAR2(50 BYTE),
  UPLOAD_ID        NUMBER(38),
  ENCODING_CD      VARCHAR2(25 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.CODE_LOOKUP
(
  TABLE_CD         VARCHAR2(100 BYTE)           NOT NULL,
  COLUMN_CD        VARCHAR2(100 BYTE)           NOT NULL,
  CODE_CD          VARCHAR2(50 BYTE)            NOT NULL,
  NAME_CHAR        VARCHAR2(650 BYTE),
  LOOKUP_BLOB      CLOB,
  UPLOAD_DATE      DATE,
  UPDATE_DATE      DATE,
  DOWNLOAD_DATE    DATE,
  IMPORT_DATE      DATE,
  SOURCESYSTEM_CD  VARCHAR2(50 BYTE),
  UPLOAD_ID        NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.PATIENT_MAPPING
(
  PATIENT_IDE         VARCHAR2(200 BYTE)        NOT NULL,
  PATIENT_IDE_SOURCE  VARCHAR2(50 BYTE)         NOT NULL,
  PATIENT_NUM         NUMBER(38)                NOT NULL,
  PATIENT_IDE_STATUS  VARCHAR2(50 BYTE),
  UPLOAD_DATE         DATE,
  UPDATE_DATE         DATE,
  DOWNLOAD_DATE       DATE,
  IMPORT_DATE         DATE,
  SOURCESYSTEM_CD     VARCHAR2(50 BYTE),
  UPLOAD_ID           NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.ENCOUNTER_MAPPING
(
  ENCOUNTER_IDE         VARCHAR2(200 BYTE)      NOT NULL,
  ENCOUNTER_IDE_SOURCE  VARCHAR2(50 BYTE)       NOT NULL,
  ENCOUNTER_NUM         NUMBER(38)              NOT NULL,
  PATIENT_IDE           VARCHAR2(200 BYTE),
  PATIENT_IDE_SOURCE    VARCHAR2(50 BYTE),
  ENCOUNTER_IDE_STATUS  VARCHAR2(50 BYTE),
  UPDATE_DATE           DATE,
  UPLOAD_DATE           DATE,
  DOWNLOAD_DATE         DATE,
  IMPORT_DATE           DATE,
  SOURCESYSTEM_CD       VARCHAR2(50 BYTE),
  UPLOAD_ID             NUMBER(38)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.JMS_SUBSCRIPTIONS
(
  CLIENTID  VARCHAR2(128 BYTE)                  NOT NULL,
  SUBNAME   VARCHAR2(128 BYTE)                  NOT NULL,
  TOPIC     VARCHAR2(255 BYTE)                  NOT NULL,
  SELECTOR  VARCHAR2(255 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.JMS_ROLES
(
  ROLEID  VARCHAR2(32 BYTE)                     NOT NULL,
  USERID  VARCHAR2(32 BYTE)                     NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.JMS_USERS
(
  USERID    VARCHAR2(32 BYTE)                   NOT NULL,
  PASSWD    VARCHAR2(32 BYTE)                   NOT NULL,
  CLIENTID  VARCHAR2(128 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.JMS_TRANSACTIONS
(
  TXID  INTEGER
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.JMS_MESSAGES
(
  MESSAGEID    INTEGER                          NOT NULL,
  DESTINATION  VARCHAR2(255 BYTE)               NOT NULL,
  TXID         INTEGER,
  TXOP         CHAR(1 BYTE),
  MESSAGEBLOB  BLOB
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.HILOSEQUENCES
(
  SEQUENCENAME  VARCHAR2(50 BYTE)               NOT NULL,
  HIGHVALUES    INTEGER                         NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.TIMERS
(
  TIMERID        VARCHAR2(80 BYTE)              NOT NULL,
  TARGETID       VARCHAR2(250 BYTE)             NOT NULL,
  INITIALDATE    TIMESTAMP(9)                   NOT NULL,
  TIMERINTERVAL  NUMBER(19),
  INSTANCEPK     BLOB,
  INFO           BLOB
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_XML_RESULT
(
  XML_RESULT_ID       NUMBER(5),
  RESULT_INSTANCE_ID  NUMBER(5),
  XML_VALUE           VARCHAR2(4000 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_PATIENT_ENC_COLLECTION
(
  PATIENT_ENC_COLL_ID  NUMBER(10),
  RESULT_INSTANCE_ID   NUMBER(5),
  SET_INDEX            NUMBER(10),
  PATIENT_NUM          NUMBER(10),
  ENCOUNTER_NUM        NUMBER(10)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_PATIENT_SET_COLLECTION
(
  PATIENT_SET_COLL_ID  NUMBER(10),
  RESULT_INSTANCE_ID   NUMBER(5),
  SET_INDEX            NUMBER(10),
  PATIENT_NUM          NUMBER(10)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_QUERY_RESULT_INSTANCE
(
  RESULT_INSTANCE_ID  NUMBER(5),
  QUERY_INSTANCE_ID   NUMBER(5),
  RESULT_TYPE_ID      NUMBER(3)                 NOT NULL,
  SET_SIZE            NUMBER(10),
  START_DATE          DATE                      NOT NULL,
  END_DATE            DATE,
  DELETE_FLAG         VARCHAR2(3 BYTE),
  STATUS_TYPE_ID      NUMBER(3)                 NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_QUERY_INSTANCE
(
  QUERY_INSTANCE_ID  NUMBER(5),
  QUERY_MASTER_ID    NUMBER(5),
  USER_ID            VARCHAR2(50 BYTE)          NOT NULL,
  GROUP_ID           VARCHAR2(50 BYTE)          NOT NULL,
  BATCH_MODE         VARCHAR2(50 BYTE),
  START_DATE         DATE                       NOT NULL,
  END_DATE           DATE,
  DELETE_FLAG        VARCHAR2(3 BYTE),
  STATUS_TYPE_ID     NUMBER(3)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_QUERY_STATUS_TYPE
(
  STATUS_TYPE_ID  NUMBER(3),
  NAME            VARCHAR2(100 BYTE),
  DESCRIPTION     VARCHAR2(200 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_QUERY_RESULT_TYPE
(
  RESULT_TYPE_ID  NUMBER(3),
  NAME            VARCHAR2(100 BYTE),
  DESCRIPTION     VARCHAR2(200 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.QT_QUERY_MASTER
(
  QUERY_MASTER_ID   NUMBER(5),
  NAME              VARCHAR2(250 BYTE)          NOT NULL,
  USER_ID           VARCHAR2(50 BYTE)           NOT NULL,
  GROUP_ID          VARCHAR2(50 BYTE)           NOT NULL,
  CREATE_DATE       DATE                        NOT NULL,
  DELETE_DATE       DATE,
  DELETE_FLAG       VARCHAR2(3 BYTE),
  GENERATED_SQL     CLOB,
  REQUEST_XML       CLOB,
  I2B2_REQUEST_XML  CLOB
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.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 TABLE I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.MAP_DIMENSION
(
  MAP_ID           NUMBER(38)                   NOT NULL,
  MAP_NAME         VARCHAR2(25 BYTE)            NOT NULL,
  CONCEPT_PATH     VARCHAR2(700 BYTE)           NOT NULL,
  TARGET_ENCODING  VARCHAR2(25 BYTE)            NOT NULL,
  MAP_PATH         VARCHAR2(200 BYTE)           NOT NULL,
  MAP_DESC         VARCHAR2(200 BYTE),
  IMPORT_DATE      DATE                         NOT NULL,
  UPDATE_DATE      DATE,
  UPLOAD_USER      VARCHAR2(50 BYTE),
  UPDATE_USER      VARCHAR2(50 BYTE),
  LAST_RUN_DATE    DATE
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.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(255 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 I2B2DEMODATA.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
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE TABLE I2B2DEMODATA.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 I2B2DEMODATA.ENCODING_SERVICE
(
  ENCODING_SERVICE_ID  NUMBER(10)               NOT NULL,
  SERVICE_URL          VARCHAR2(200 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


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


CREATE TABLE I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 UNIQUE INDEX I2B2DEMODATA.PK_DATA_REQUEST ON I2B2DEMODATA.DATA_REQUEST
(DATA_REQUEST_ID)
LOGGING
NOPARALLEL;


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


--CREATE UNIQUE INDEX I2B2DEMODATA.UQ_ENCODING_DIME_ENCODING_NAM ON I2B2DEMODATA.ENCODING_DIMENSION
--(ENCODING_NAME)
--LOGGING
--NOPARALLEL;


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


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


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


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


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


CREATE INDEX I2B2DEMODATA.QT_IDX_QPSC_RIID ON I2B2DEMODATA.QT_PATIENT_SET_COLLECTION
(RESULT_INSTANCE_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.TIMERS_PK ON I2B2DEMODATA.TIMERS
(TIMERID, TARGETID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.HILO_PK ON I2B2DEMODATA.HILOSEQUENCES
(SEQUENCENAME)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.JMS_MESSAGES_DESTINATION ON I2B2DEMODATA.JMS_MESSAGES
(DESTINATION)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.ENCOUNTER_MAPPING_PK ON I2B2DEMODATA.ENCOUNTER_MAPPING
(ENCOUNTER_IDE, ENCOUNTER_IDE_SOURCE)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.EM_UPLOADID_IDX ON I2B2DEMODATA.ENCOUNTER_MAPPING
(UPLOAD_ID)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.EM_ENCNUM_IDX ON I2B2DEMODATA.ENCOUNTER_MAPPING
(ENCOUNTER_NUM)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PATIENT_MAPPING_PK ON I2B2DEMODATA.PATIENT_MAPPING
(PATIENT_IDE, PATIENT_IDE_SOURCE)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.PM_UPLOADID_IDX ON I2B2DEMODATA.PATIENT_MAPPING
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.PM_PATNUM_IDX ON I2B2DEMODATA.PATIENT_MAPPING
(PATIENT_NUM)
LOGGING
NOPARALLEL;


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


CREATE UNIQUE INDEX I2B2DEMODATA.CODE_LOOKUP_PK ON I2B2DEMODATA.CODE_LOOKUP
(TABLE_CD, COLUMN_CD, CODE_CD)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.CL_IDX_NAME_CHAR ON I2B2DEMODATA.CODE_LOOKUP
(NAME_CHAR)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.CL_IDX_UPLOADID ON I2B2DEMODATA.CODE_LOOKUP
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.CONCEPT_DIMENSION_PK ON I2B2DEMODATA.CONCEPT_DIMENSION
(CONCEPT_PATH)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.CD_UPLOADID_IDX ON I2B2DEMODATA.CONCEPT_DIMENSION
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.OBSERVATION_FACT_PK ON I2B2DEMODATA.OBSERVATION_FACT
(ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.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)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.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)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PATIENT_DIMENSION_PK ON I2B2DEMODATA.PATIENT_DIMENSION
(PATIENT_NUM)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.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)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.PATD_UPLOADID_IDX ON I2B2DEMODATA.PATIENT_DIMENSION
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PROVIDER_DIMENSION_PK ON I2B2DEMODATA.PROVIDER_DIMENSION
(PROVIDER_PATH, PROVIDER_ID)
LOGGING
NOPARALLEL;


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


CREATE INDEX I2B2DEMODATA.PROD_UPLOADID_IDX ON I2B2DEMODATA.PROVIDER_DIMENSION
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.VISIT_DIMENSION_PK ON I2B2DEMODATA.VISIT_DIMENSION
(ENCOUNTER_NUM, PATIENT_NUM)
LOGGING
NOPARALLEL;


CREATE INDEX I2B2DEMODATA.VD_UPLOADID_IDX ON I2B2DEMODATA.VISIT_DIMENSION
(UPLOAD_ID)
LOGGING
NOPARALLEL;


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


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


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


CREATE UNIQUE INDEX I2B2DEMODATA.PK_UP_UPSTATUS_UPLOADID ON I2B2DEMODATA.UPLOAD_STATUS
(UPLOAD_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PK_ST_ID ON I2B2DEMODATA.SET_TYPE
(ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PK_SOURCEMASTER_SOURCECD ON I2B2DEMODATA.SOURCE_MASTER
(SOURCE_CD)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX I2B2DEMODATA.PK_UP_UPSTATUS_IDSETTYPEID ON I2B2DEMODATA.SET_UPLOAD_STATUS
(UPLOAD_ID, SET_TYPE_ID)
LOGGING
NOPARALLEL;


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),
  CONSTRAINT UQ_OBS_F_OBSERVATION_FACT_ID
 UNIQUE (OBSERVATION_FACT_ID));

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),
  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));
--  CONSTRAINT UQ_ENCODING_DIME_ENCODING_NAM
-- UNIQUE (ENCODING_NAME));

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),
  CONSTRAINT FK_OBS_F_CONCEPT_DIMENSION 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH));

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),
  CONSTRAINT QT_FK_QRI_RTID 
 FOREIGN KEY (RESULT_TYPE_ID) 
 REFERENCES I2B2DEMODATA.QT_QUERY_RESULT_TYPE (RESULT_TYPE_ID),
  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),
  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_CRITERIA_DATA_REQUEST 
 FOREIGN KEY (DATA_REQUEST_ID) 
 REFERENCES I2B2DEMODATA.DATA_REQUEST (DATA_REQUEST_ID),
  CONSTRAINT FK_STUDY_CRITERI_ENCODING_DIME 
 FOREIGN KEY (ENCODING_REQD) 
 REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD),
  CONSTRAINT FK_STUDY_CRITERI_CONCEPT_DIMEN 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH));

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_QUERY_DIMENSI 
 FOREIGN KEY (QUERY_ID) 
 REFERENCES I2B2DEMODATA.QUERY_DIMENSION (QUERY_ID),
  CONSTRAINT FK_QUERY_CRITERI_CONCEPT_DIMEN 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH));

ALTER TABLE I2B2DEMODATA.MAP_REQUEST ADD (
  CONSTRAINT FK_MAP_REQUEST_ENCODING_DIMEN2 
 FOREIGN KEY (TARGET_ENCODING) 
 REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD),
  CONSTRAINT FK_MAP_REQUEST_USER_INFO 
 FOREIGN KEY (USER_ID) 
 REFERENCES I2B2DEMODATA.USER_INFO (USER_ID),
  CONSTRAINT FK_MAP_REQUEST_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES I2B2DEMODATA.MAP_DIMENSION (MAP_ID),
  CONSTRAINT FK_MAP_REQUEST_CONCEPT_DIMENS 
 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),
  CONSTRAINT FK_MAP_DIMENSION_CONCEPT_DIME 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH));

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),
  CONSTRAINT FK_MAP_DATA_FACT_PATIENT_DIME 
 FOREIGN KEY (PATIENT_NUM) 
 REFERENCES I2B2DEMODATA.PATIENT_DIMENSION (PATIENT_NUM),
  CONSTRAINT FK_MAP_DATA_FACT_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES I2B2DEMODATA.MAP_DIMENSION (MAP_ID),
  CONSTRAINT FK_MAP_DATA_FACT_CONCEPT_DIME 
 FOREIGN KEY (CONCEPT_PATH) 
 REFERENCES I2B2DEMODATA.CONCEPT_DIMENSION (CONCEPT_PATH));

ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD (
  CONSTRAINT FK_MAP_AGGR_FACT_MAP_DIMENSION 
 FOREIGN KEY (MAP_ID) 
 REFERENCES I2B2DEMODATA.MAP_DIMENSION (MAP_ID),
  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_PROJ 
 FOREIGN KEY (ENCODING_PROJECT_ID) 
 REFERENCES I2B2DEMODATA.ENCODING_PROJECT (ENCODING_PROJECT_ID),
  CONSTRAINT FK_ENCODING_PROJ_ENCODING_DIME 
 FOREIGN KEY (ENCODING_CD) 
 REFERENCES I2B2DEMODATA.ENCODING_DIMENSION (ENCODING_CD));

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_USER_INFO 
 FOREIGN KEY (USER_ID) 
 REFERENCES I2B2DEMODATA.USER_INFO (USER_ID),
  CONSTRAINT FK_DATA_REQUEST_RESEARCH_TYPE 
 FOREIGN KEY (RESEARCH_TYPE_CD) 
 REFERENCES I2B2DEMODATA.RESEARCH_TYPE (RESEARCH_TYPE_CD),
  CONSTRAINT FK_DATA_REQUEST_REQUEST_STATUS 
 FOREIGN KEY (REQUEST_STATUS_CD) 
 REFERENCES I2B2DEMODATA.REQUEST_STATUS (REQUEST_STATUS_CD),
  CONSTRAINT FK_DATA_REQUEST_IRB_APPROVAL_ 
 FOREIGN KEY (IRB_APPROVAL_TYPE_CD) 
 REFERENCES I2B2DEMODATA.IRB_APPROVAL_TYPE (IRB_APPROVAL_TYPE_CD));

