CREATE TABLE I2B2DEMODATA.MAP_TYPE (
  MAP_TYPE_CD CHAR(2) PRIMARY KEY,
  MAP_TYPE_DESC VARCHAR(25) NOT NULL,
  CREATE_USER         VARCHAR(50),
  UPDATE_USER         VARCHAR(50),
  CREATE_DATE         DATETIME,
  UPDATE_DATE         DATETIME
);


ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP MAP_DESC;
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP CONSTRAINT "FK_MAP_DIMENSION_ENCODING_DIME";
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP TARGET_ENCODING;
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP CONSTRAINT "FK_MAP_DIMENSION_CONCEPT_DIME";
ALTER TABLE I2B2DEMODATA.MAP_DIMENSION DROP CONCEPT_PATH;

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION ADD (
MAP_TYPE_CD 		CHAR(2) 	NOT NULL,
TARGET_CONCEPT_PATH 	VARCHAR(255) 	NULL,
AUTHOR 			VARCHAR(25) 	NULL,
CREATOR 		VARCHAR(25) 	NULL,
CURATOR 		VARCHAR(25) 	NULL,
CONTRIBUTOR 		VARCHAR(25) 	NULL,
CREATE_DATE 		DATETIME 		NULL,
ID 			VARCHAR(25) 	NULL,
LICENSE 		VARCHAR(50) 	NULL,
LICENSE_TYPE 		VARCHAR(25) 	NULL,
IS_LICENSE_RESTRICTED 	CHAR(1) 	NULL,
RELEASE_DATE 		DATETIME 		NULL,
RELEASE_REPO_NAME 	VARCHAR(50) 	NULL,
RELEASE_URI 		VARCHAR(50) 	NULL,
RELEASE_VERSION 	VARCHAR(10) 	NULL,
RESTRICTION_TYPE 	VARCHAR(25) 	NULL,
RIGHTS_HOLDER 		VARCHAR(25) 	NULL,
PURPOSE 		VARCHAR(50) 	NULL,
ORG_NAME 		VARCHAR(50) 	NULL,
ORG_TYPE 		VARCHAR(25) 	NULL,
SOURCE_SOFTWARE_ENV 	VARCHAR(50) 	NULL,
ETL_SCRIPT_URL 		VARCHAR(50) 	NULL
);

ALTER TABLE I2B2DEMODATA.MAP_DIMENSION ADD CONSTRAINT FK_MAP_DIMENSION_MAP_TYPE_CD
FOREIGN KEY (MAP_TYPE_CD)
REFERENCES I2B2DEMODATA.MAP_TYPE(MAP_TYPE_CD);

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


CREATE TABLE I2B2DEMODATA.MAP_SRC_CONCEPT_SET (
MAP_SRC_CONCEPT_ID NUMERIC(38) PRIMARY KEY,
MAP_ID NUMERIC(38) NOT NULL,
SOURCE_CONCEPT_PATH VARCHAR(255) NULL,
SOURCE_CONCEPT_CD VARCHAR(50) NULL
);

ALTER TABLE I2B2DEMODATA.MAP_SRC_CONCEPT_SET ADD CONSTRAINT FK_MAP_SRC_CONCEPT_SET_MAP_ID
FOREIGN KEY (MAP_ID) 
REFERENCES I2B2DEMODATA.MAP_DIMENSION(MAP_ID);


ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD PATIENT_NUM NUMERIC(38) NOT NULL;

ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD CONSTRAINT FK_MAP_AGGR_FACT_PATIENT_NUM
FOREIGN KEY (PATIENT_NUM) 
REFERENCES I2B2DEMODATA.PATIENT_DIMENSION(PATIENT_NUM);

ALTER TABLE I2B2DEMODATA.MAP_AGGR_FACT ADD CONCEPT_CD VARCHAR(50) NOT NULL;


CREATE VIEW I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.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 I2B2DEMODATA.MAP_AGGR_FACT
);

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT DROP TVAL_CHAR;

ALTER TABLE I2B2DEMODATA.MAP_DATA_FACT ADD TVAL_CHAR VARCHAR(500) NULL;



