NORM

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
CREATE TABLE Proben
(id integer not null autoincrement,
Lnr varchar(50),
Name varchar(50),
Probenahme_zeitpunkt date);

CREATE TABLE Projekte
(id integer not null autoincrement,
R_Proben integer, 
Kennung varchar(50));

CREATE TABLE Teilproben
(id integer not null autoincrement,
R_Proben integer,
R_Bereiche integer,
Messdatum date,
Parameter_2 varchar(50),
Parameter_3 varchar(50));

CREATE TABLE Bereiche
(id integer not null autoincrement,
R_Sektion integer);

CREATE TABLE Ergebnisse
(id integer not null autoincrement,
R_Teilproben integer,
R_PM_Parameter integer,
R_Einheiten integer,
Wert_Text varchar(50),
U95TOT FLOAT);

CREATE TABLE PM_Parameter
(id integer not null autoincrement,
R_Parameter integer);

CREATE TABLE Parameter
(id integer not null autoincrement,
Name varchar(50));

CREATE TABLE Sektion
(id integer not null autoincrement,
Name varchar(50));

CREATE TABLE Einheiten
(id integer not null autoincrement,
Name varchar(50));




CREATE VIEW V_IC_PROBEN AS
SELECT
       p.ID
      ,p.LNR
      ,p.NAME
      ,p.PROBENAHME_ZEITPUNKT datum
      ,j.KENNUNG proj
FROM PROBEN p
      LEFT JOIN PROJEKTE_PROBEN jp 
           ON (p.ID = jp.R_PROBEN)
      LEFT JOIN PROJEKTE j
           ON (jp.R_PROJEKTE = j.ID)

CREATE VIEW V_IC_MESSUNG AS
SELECT
       m.ID
      ,m.R_PROBEN R_pID
      ,s.NAME Sektion
      ,m.MESSDATUM Messdat
      ,m.PARAMETER_2 Det
      ,m.PARAMETER_3 Geom
FROM TEILPROBEN m
      LEFT JOIN BEREICHE b 
           ON (m.R_BEREICHE = b.ID)
      LEFT JOIN SEKTION s 
           ON (b.R_SEKTION = s.ID)

CREATE VIEW V_IC_ERGEBNIS AS
SELECT
       e.R_TEILPROBEN R_mID
      ,PARAMETER.NAME isot
      ,e.WERT_TEXT val
      ,e.U95TOT
      ,u.NAME unit
FROM ERGEBNISSE e
      JOIN PM_PARAMETER pmp 
           ON (e.R_PM_PARAMETER = pmp.ID)
      JOIN PARAMETER 
           ON (pmp.R_PARAMETER = PARAMETER.ID)
      LEFT JOIN EINHEITEN u 
           ON (e.R_EINHEITEN = u.ID)



CREATE VIEW V_IC_URA AS
SELECT
       p.proj
      ,p.LNR
      ,p.NAME
      ,p.date
      ,m.Messdat
      ,m.Det
      ,m.Geom
      ,e.isot
      ,e.val
      ,e.U95TOT
      ,e.unit
FROM V_IC_PROBEN p 
      JOIN V_IC_MESSUNG m
           ON (p.ID = m.R_pID)
      JOIN V_IC_ERGEBNIS e
           ON (m.ID = e.R_mID)
WHERE proj [Bedingung für Projekt] AND date [Bedingung für date]
     
CREATE VIEW V_IC_REF AS
SELECT
       p.LNR
      ,m.Det
      ,e.isot
      ,e.val
      ,e.U95TOT
      ,e.unit
FROM V_IC_PROBEN p 
      JOIN V_IC_MESSUNG m
           ON (p.ID = m.R_pID)
      JOIN V_IC_ERGEBNIS e
           ON (m.ID = e.R_mID)
WHERE proj [Bedingung für Projekt] AND date [Bedingung für date]
      AND Det="REF"

CREATE VIEW V_IC_FINAL AS
SELECT
      u.proj
     ,u.LNR
     ,u.NAME
     ,u.date
     ,u.Messdat
     ,u.Det
     ,u.Geom
     ,u.isot
     ,u.val
     ,u.U95TOT
     ,u.unit
     ,r.val
     ,r.U95TOT
FROM V_IC_URA u 
      JOIN V_IC_REF r
           ON (u.LNR = r.LNR AND u.isot = r.isot)



{{ :trachtenberg.pdf |}}