MAD

 table DEP_MAD_SET  
 table DEP_MAD_EXP    
 table DEP_MAD_RATIO    
 table DEP_MAD_CLUST 
 table DEP_MAD  



create table DEP_MAD_SET  (
             SET_ID               number(38)   not null,
             DMC_DMC_ID           number(10,0) not null,
             DMS1_ID              number(10,0) not null,
             WAVELENGTH           number(8,0)  not null,
             F_DOUBLE_PRIME       number(8,0)      null,
             F_PRIME              number(8,0)      null,
             D_RES_HIGH           number(8,0)      null,
             D_RES_LOW            number(8,0)      null,
             WAVE_DETAILS         varchar2(80)     null
)   ;


create table DEP_MAD_EXP    (
             DDS_DDS_ID               number(10,0) not null,
             DME_ID                   number(10,0) not null,
             R_NORMAL_ANOM_SCAT       number(8,0)      null,
             R_NORMAL_ALL             number(8,0)      null,
             DELTA_PHI                number(8,0)      null,
             DELTA_PHI_SIGMA          number(8,0)      null,
             MEAN_FOM                 number(8,0)      null,
             NUM_CLUST                number(38)       null,
             DELTA_DELTA_PHI          number(8,0)      null
)   ;


create table DEP_MAD_RATIO    (
             DMR_ID               number(10,0) not null,
             ONE_WL               number(8,0)      null,
             ONE_WL_CENTRIC       number(8,0)      null,
             TWO_WL               number(8,0)      null,
             D_RES_HIGH           number(8,0)      null,
             D_RES_LOW            number(8,0)      null,
             DMS1_DMS1_ID         number(10,0)     null,
             DMS1_DMS1_ID_CP591   number(10,0)     null
)   ;


create table DEP_MAD_CLUST (
             CLUST_ID       number(38)   not null,
             DME_DME_ID     number(10,0) not null,
             DMC_ID         number(10,0) not null,
             NUM_SETS       number(38)       null
)   ;


create table DEP_MAD  (
             DDS_DDS_ID       number(10,0)  not null,
             DM1_ID           number(10,0)  not null,
             METHOD           varchar2(80)      null,
             DETAILS          varchar2(1000)    null
)   ;




alter table DEP_MAD
 add (constraint DM1_PK primary key   (DM1_ID))
alter table DEP_MAD 
 add ( constraint DM1_DM11_UK unique   (DDS_DDS_ID))
alter table DEP_MAD 
 add constraint DM1_DDS_FK foreign key   (DDS_DDS_ID) 
     references DEP_DATA_SET  (DDS_ID) on delete cascade

alter table DEP_MAD_SET
 add (constraint DMS1_PK primary key   (DMS1_ID))
alter table DEP_MAD_SET 
 add ( constraint DMS1_DMS11_UK unique   (DMC_DMC_ID  ,SET_ID  ,WAVELENGTH))
alter table DEP_MAD_SET 
 add constraint DMS1_DMC_FK foreign key   (DMC_DMC_ID) 
     references DEP_MAD_CLUST  (DMC_ID) on delete cascade


alter table DEP_MAD_EXP
 add (constraint DME_PK primary key   (DME_ID))
alter table DEP_MAD_EXP 
 add ( constraint DME_DME1_UK unique   (DDS_DDS_ID))
alter table DEP_MAD_EXP 
 add constraint DME_DDS_FK foreign key   (DDS_DDS_ID) 
     references DEP_DATA_SET  (DDS_ID) on delete cascade

alter table DEP_MAD_RATIO
 add (constraint DMR_PK primary key   (DMR_ID))
alter table DEP_MAD_RATIO 
  add constraint DMR_DMS1_CP591_FK foreign key   (DMS1_DMS1_ID_CP591) 
    references DEP_MAD_SET  (DMS1_ID) on delete cascade 
  add constraint DMR_DMS1_FK foreign key   (DMS1_DMS1_ID) 
    references DEP_MAD_SET  (DMS1_ID) on delete cascade

alter table DEP_MAD_CLUST
 add (constraint DMC_PK primary key   (DMC_ID))
alter table DEP_MAD_CLUST 
 add ( constraint DMC_DMC1_UK unique   (DME_DME_ID  ,CLUST_ID))
alter table DEP_MAD_CLUST 
 add constraint DMC_DME_FK foreign key   (DME_DME_ID) 
     references DEP_MAD_EXP  (DME_ID) on delete cascade