drop table TISSUE;

drop table SPUTUM;

drop table BONE_MARROW;

drop table EDRN_SPECIMEN;
drop table EDRN_FAMILY;
drop table EDRN_PATIENT;


create table EDRN_PATIENT
(
STUDY_PARTICIPANT_ID 				char(8) not null,
STUDY_PROTOCOL_ID 					int not null,
CANCER_HX_ICD9_CODE 				int , 
CANCER_HX_CONFIRMATION_CODE 		int ,
CANCER_AGE_DIAGNOSIS_VALUE 			int ,
CANCER_DIAGNOSIS_YEAR_TEXT 			char(4),
DEMOGRAPHICS_GENDER_CODE 			int  not null,
DEMOGRAPHICS_ETHNIC_CODE 			int ,
DEMOGRAPHICS_RACE_CODE 				int  not null,
DEMOGRAPHICS_BIRTH_YEAR_TEXT 		char(4),
DEMOGRAPHICS_RACE_OTHER_TEXT 		varchar(25),
SMOKE_HX_AVERAGE_DAY_VALUE			int ,
SMOKE_HX_BEGIN_AGE_REGULAR			int ,
SMOKE_HX_QUIT_AGE_VALUE 			int ,
SMOKE_HX_REGULAR_1YEAR_CODE 		int ,
SMOKE_HX_YEARS_TOTAL_VALUE 			int ,
PPT_WEIGHT_DIAGNOSIS    			int ,   
PPT_MED_HX_PERF_STATUS_CODE 		int,
PPT_SMOKE_HX_AVERAGE_PACK 			int ,
FAMILY_CANCER_CONFIRMATION 			int 
);

alter table EDRN_PATIENT
add constraint EDRN_PATIENT_PK primary key
(STUDY_PARTICIPANT_ID);

create table EDRN_FAMILY 
(
FAMILY_ID							int not null,
CANCER_HX_LOCATION_CODE        		int ,
CANCER_LOCATION_OTHER_TEXT  		varchar(25),
STUDY_PARTICIPANT_ID 				char(8)
);

alter table EDRN_FAMILY
add constraint EDRN_FAMILY_PK primary key
(FAMILY_ID);

alter table EDRN_FAMILY
add constraint EDRN_FAMILY_FK foreign key
(STUDY_PARTICIPANT_ID)
references EDRN_PATIENT (STUDY_PARTICIPANT_ID);

create table EDRN_SPECIMEN 
(
SPECIMEN_ID 						int not null,
SPECIMEN_COLLECTED 					int ,   
SPECIMEN_STORED 					int ,
SPECIMEN_AMOUNT_STORED_VALUE		number(10,2) ,
SPECIMEN_AMOUNT_STORED_UNIT			int ,   
SPECIMEN_CONCENTRATION_VALUE		number(10,2) ,
SPECIMEN_AVAILABLE_CODE				int,
SPECIMEN_AMOUNT_REMAINING			number(10,2) ,
SPECIMEN_AMOUNT_REMAINING_UNIT		int ,   
SPECIMEN_DAY_CA_DIAGNOSIS			int ,
SPECIMEN_DAY_COLLECTED				int ,
SPECIMEN_AGE_COLLECTED				int ,
SPECIMEN_FINAL_STORE_CODE			int ,
SPECIMEN_FINAL_STORE				varchar(35), 
STUDY_PARTICIPANT_ID 				char(8)
);

alter table EDRN_SPECIMEN
add constraint EDRN_SPECIMEN_PK primary key
(SPECIMEN_ID);

alter table EDRN_SPECIMEN
add constraint EDRN_SPECIMEN_FK foreign key
(STUDY_PARTICIPANT_ID)
references EDRN_PATIENT (STUDY_PARTICIPANT_ID);

create table BONE_MARROW 
(
SPECIMEN_ID							int not null,
SPECIMEN_BONE_MARROW_PLASMA			number(10,2) ,
SPECIMEN_BONE_MARROW_BLAST			number(10,2) ,
BONE_MARROW_CELL_COUNT				int 
);

alter table BONE_MARROW 
add constraint BONE_MARROW_FK foreign key
(SPECIMEN_ID)
references EDRN_SPECIMEN (SPECIMEN_ID);

create table TISSUE
(
SPECIMEN_ID							int not null,
DEGREE_INVASIVE_CODE				int ,
DEGREE_INVASIVE_TUMOR_CODE			int ,	
ORGAN_SITE_CODE						int ,	
ORGAN_SITE_OTHER_TEXT				varchar(35)
);

alter table TISSUE
add constraint TISSUE_FK foreign key
(SPECIMEN_ID)
references EDRN_SPECIMEN (SPECIMEN_ID);

create table SPUTUM
(
SPECIMEN_ID							int not null,
PRESERVATIVE_CODE					int ,	
PRESERVATIVE_OTHER_TEXT				varchar(35)
);

alter table SPUTUM 
add constraint SPUTUM_FK foreign key
(SPECIMEN_ID)
references EDRN_SPECIMEN (SPECIMEN_ID);