-- DDL to create the tables

create table d_study (study character varying(10) not null primary key);


create table d_studyelement (study character varying (10) not null,
  element character varying(10) not null,
  constraint pk_element primary key (study, element),
  constraint fk_elementstudy foreign key (study) references d_study (study)
    on update cascade on delete cascade);


create table l_lunit (length_unit character varying(10) not null primary key);


create table d_sample (study character varying (10) not null,
  sample character varying (10) not null,
  element character varying (10) null,
  surface_elevation double precision,
  elevation_units character varying (10),
  sample_depth double precision,
  depth_units character varying (10),
  constraint pk_sample primary key (study, sample),
  constraint fk_samplestudy foreign key (study) references d_study (study)
    on update cascade on delete cascade,
  constraint fk_sampleelem foreign key (study, element) references d_studyelement
    (study, element) on update cascade on delete cascade,
  constraint fk_sampleelev foreign key (elevation_units) references l_lunit (length_unit)
    on update cascade on delete cascade,
  constraint fk_sampledepth foreign key (depth_units) references l_lunit (length_unit)
    on update cascade on delete cascade);

-- DML to initialize the tables

insert into d_study (study) values ('StudyA');
insert into d_study (study) values ('StudyB');


insert into d_studyelement (study, element) values ('StudyA', 'Elem1');
insert into d_studyelement (study, element) values ('StudyA', 'Elem2');


insert into l_lunit (length_unit) values ('meters');
insert into l_lunit (length_unit) values ('cm');
insert into l_lunit (length_unit) values ('ft');


insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample1', 'Elem1', 1234, 'meters', 2, 'cm');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample2', 'Elem1', 1234, 'meters', 50, 'cm'); 
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample4', 'Elem1', 1234, 'meters', 1.2, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample5', 'Elem2', 602, 'ft', 2, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample6', 'Elem2', 602, 'ft', 4, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyB', 'Sample1', null, 900, 'meters', 2, 'cm');

-- Cascade an update through multiple paths

update d_study set study='StudyZ' where study='StudyA';