CREATE OR REPLACE VIEW view_results_sampling AS 
 SELECT DISTINCT results.rhl_id_sample AS muestrica, laboratory.lay_name, oro.rhl_answer AS orito, plata.rhl_answer AS platica, cobre.rhl_answer AS cobrecito
   FROM results, ( SELECT results.rhl_id_sample, results.rhl_ane_id_elements_unit, results.rhl_orig_lab, results.rhl_answer
           FROM results
          WHERE results.rhl_ane_id_elements_unit = 59::numeric AND results.rhl_lay_id_lab = 7::numeric AND results.rhl_stp_id = 1::numeric) oro, ( SELECT results.rhl_id_sample, results.rhl_ane_id_elements_unit, results.rhl_answer
           FROM results
          WHERE results.rhl_ane_id_elements_unit = 60::numeric AND results.rhl_lay_id_lab = 7::numeric AND results.rhl_stp_id = 1::numeric) plata, ( SELECT results.rhl_id_sample, results.rhl_ane_id_elements_unit, results.rhl_answer
           FROM results
          WHERE results.rhl_ane_id_elements_unit = 61::numeric AND results.rhl_lay_id_lab = 7::numeric AND results.rhl_stp_id = 1::numeric) cobre, laboratory
  WHERE results.rhl_id_sample::text = oro.rhl_id_sample::text AND results.rhl_id_sample::text = plata.rhl_id_sample::text AND results.rhl_id_sample::text = cobre.rhl_id_sample::text AND (results.rhl_ane_id_elements_unit = oro.rhl_ane_id_elements_unit OR results.rhl_ane_id_elements_unit = plata.rhl_ane_id_elements_unit OR results.rhl_ane_id_elements_unit = cobre.rhl_ane_id_elements_unit) AND oro.rhl_orig_lab = laboratory.lay_id_lab AND results.rhl_lay_id_lab = 7::numeric AND results.rhl_id_sample IS NOT NULL
  ORDER BY results.rhl_id_sample, laboratory.lay_name, oro.rhl_answer, plata.rhl_answer, cobre.rhl_answer;


CREATE TABLE laboratory
(
  lay_id_lab numeric(4) NOT NULL,
  lay_name varchar(80) NOT NULL,
  lay_contact numeric(4),
  lay_telephone varchar(15),
  lay_country numeric(3) NOT NULL,
  lay_city numeric(3) NOT NULL,
  lay_activo varchar(1) NOT NULL,
  CONSTRAINT lay_pk PRIMARY KEY (lay_id_lab),
  CONSTRAINT lay_ciy_fk FOREIGN KEY (lay_city)
      REFERENCES city (ciy_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT lay_con_fk FOREIGN KEY (lay_contact)
      REFERENCES contacts (con_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT lay_cty_fk FOREIGN KEY (lay_country)
      REFERENCES country (cty_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT lay_uk UNIQUE (lay_name),
  CONSTRAINT lay_ck CHECK (lay_activo::text = 'S'::character varying::text OR lay_activo::text = 'N'::character varying::text)
) 

CREATE TABLE hole_samples
(
  hse_hole_name varchar(10) NOT NULL,
  hse_id_sample varchar(8) NOT NULL,
  hse_ss varchar(2),
  hse_from numeric(5,2) NOT NULL,
  hse_to numeric(5,2) NOT NULL,
  hse_length numeric(5,2) NOT NULL,
  hse_total_length numeric(5,2) NOT NULL,
  hse_loss numeric(5,2) NOT NULL,
  hse_perc_loss numeric(5,2) NOT NULL,
  hse_vein varchar(12),
  hse_old_vein varchar(12),
  hse_shoot numeric(3),
  CONSTRAINT hse_pk PRIMARY KEY (hse_id_sample),
  CONSTRAINT hse_gie_fk FOREIGN KEY (hse_hole_name)
      REFERENCES general_info_hole (gie_name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT hse_uk UNIQUE (hse_id_sample)
)

CREATE TABLE major
(
  maj_hole_name varchar(10) NOT NULL,
  maj_number numeric(3) NOT NULL,
  maj_from numeric(5,2) NOT NULL,
  maj_to numeric(5,2) NOT NULL,
  maj_appear varchar(3),
  maj_rock_type varchar(4) NOT NULL,
  maj_alt_int1 varchar(4),
  maj_alt_type1 varchar(4),
  maj_alt_style1 varchar(4),
  maj_alt_int2 varchar(4),
  maj_alt_type2 varchar(4),
  maj_alt_style2 varchar(4),
  maj_contact varchar(10),
  maj_color varchar(50),
  maj_size_from varchar(3),
  maj_size_to varchar(3),
  maj_comments varchar(256),
  maj_user varchar(30) NOT NULL,
  CONSTRAINT maj_pk PRIMARY KEY (maj_hole_name, maj_number),
  CONSTRAINT maj_aiy_fk1 FOREIGN KEY (maj_alt_int1)
      REFERENCES alteration_intensity (aiy_intensity) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_aiy_fk2 FOREIGN KEY (maj_alt_int2)
      REFERENCES alteration_intensity (aiy_intensity) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_ase_fk1 FOREIGN KEY (maj_alt_style1)
      REFERENCES alteration_style (ase_style) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_ase_fk2 FOREIGN KEY (maj_alt_style2)
      REFERENCES alteration_style (ase_style) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_aty_fk1 FOREIGN KEY (maj_alt_type1)
      REFERENCES alteration_type (aty_type) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_aty_fk2 FOREIGN KEY (maj_alt_type2)
      REFERENCES alteration_type (aty_type) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_cot_fk FOREIGN KEY (maj_contact)
      REFERENCES contact (cot_contact) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_gie_fk FOREIGN KEY (maj_hole_name)
      REFERENCES general_info_hole (gie_name) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_rte_fk FOREIGN KEY (maj_rock_type)
      REFERENCES rock_type (rte_type) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT maj_usr_fk FOREIGN KEY (maj_user)
      REFERENCES users (usr_alias) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)