-- -------------------------
-- Tâches initiales
-- -------------------------
SET search_path TO aras_vbo ;
DROP VIEW IF EXISTS SPECTRE_VUE ;
DROP TABLE IF EXISTS BSS_ESRP ;
DROP TABLE IF EXISTS BSS_BINN ;
DROP TABLE IF EXISTS BSS_ORD ;
DROP TABLE IF EXISTS SPECTRE_ECHELLE ;
DROP TABLE IF EXISTS SPECTRE ;
DROP TABLE IF EXISTS ASTRE ;
-- -------------------------
-- Déclaration des tables
-- -------------------------
CREATE TABLE ASTRE
(
id_astre SERIAL NOT NULL
, affichage_astre BOOLEAN NOT NULL
, ad_fk5_astre FLOAT NOT NULL
, dec_fk5_astre FLOAT NOT NULL
, CONSTRAINT ASTRE_PK PRIMARY KEY (id_astre)
) ;
INSERT INTO ASTRE (affichage_astre, ad_fk5_astre, dec_fk5_astre) VALUES (true, 45, 180) ;
SELECT * FROM ASTRE ;
CREATE TABLE SPECTRE
(
id_astre INTEGER NOT NULL
, id_spectre SERIAL NOT NULL
, crval1 DOUBLE PRECISION NOT NULL
, cdelt1 DOUBLE PRECISION NOT NULL
, crpix1 FLOAT NOT NULL
, cunit1 VARCHAR(32) NOT NULL
, ctype1 VARCHAR(32) NOT NULL
, bss_vhel FLOAT NOT NULL
, bss_tell VARCHAR(32) NOT NULL
, bss_cosm VARCHAR(32) NOT NULL
, bss_norm VARCHAR(32) NOT NULL
, CONSTRAINT SPECTRE_PK PRIMARY KEY (id_astre, id_spectre)
, CONSTRAINT SPECTRE_ASTRE_FK FOREIGN KEY (id_astre) REFERENCES ASTRE (id_astre)
) ;
-- --------------------------------
-- Contraintes de la table SPECTRE
-- --------------------------------
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_CRVAL1 CHECK (crval1 BETWEEN 300 AND 10000) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_CDELT1 CHECK (cdelt1 BETWEEN 0.0001 AND 30) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_CUNIT1 CHECK (cunit1 IN ('angstrom', 'nm')) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_CTYPE1 CHECK (ctype1 IN ('wavelength')) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_BSS_VHEL CHECK (bss_vhel BETWEEN -200 AND +200) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_BSS_TELL CHECK (bss_tell IN ('none', 'removed', 'method xx')) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_BSS_COSM CHECK (bss_cosm IN ('none', 'removed', 'method xx')) ;
ALTER TABLE SPECTRE
ADD CONSTRAINT SPECTRE_BSS_NORM CHECK (bss_norm IN ('none', 'removed', 'method xx')) ;
-- ------------------------------
-- INSERTS
-- ------------------------------
INSERT INTO SPECTRE (id_astre, crval1, cdelt1, crpix1, cunit1, ctype1, bss_vhel, bss_tell, bss_cosm, bss_norm)
VALUES (1, 6233.45, 0.116, 1.0, 'nm', 'wavelength', -9.45, 'none', 'removed', 'none') ;
INSERT INTO SPECTRE (id_astre, crval1, cdelt1, crpix1, cunit1, ctype1, bss_vhel, bss_tell, bss_cosm, bss_norm)
VALUES (1, 6233.45, 0.116, 1.0, 'nm', 'wavelength', -9.45, 'none', 'removed', 'none') ;
-- ----------------------------------
-- Tables satellites de SPECTRE
-- ----------------------------------
CREATE TABLE SPECTRE_ECHELLE
(
id_astre INTEGER NOT NULL
, id_spectre INTEGER NOT NULL
, bss_ord VARCHAR(254) NOT NULL
, CONSTRAINT SPECTRE_ECHELLE_PK PRIMARY KEY (id_astre, id_spectre)
, CONSTRAINT SPECTRE_ECHELLE_FK FOREIGN KEY (id_astre, id_spectre) REFERENCES SPECTRE (id_astre, id_spectre)
) ;
CREATE TABLE BSS_ESRP
(
id_astre INTEGER NOT NULL
, id_spectre INTEGER NOT NULL
, bss_esrp DOUBLE PRECISION NOT NULL
, CONSTRAINT BSS_ESRP_PK PRIMARY KEY (id_astre, id_spectre)
, CONSTRAINT BSS_ESRP_FK FOREIGN KEY (id_astre, id_spectre) REFERENCES SPECTRE (id_astre, id_spectre)
, CONSTRAINT BSS_ESRP_CHK CHECK (bss_esrp >= 0)
) ;
INSERT INTO BSS_ESRP (id_astre, id_spectre, bss_esrp)
VALUES (1, 2, 17.0005) ;
CREATE TABLE BSS_BINN
(
id_astre INTEGER NOT NULL
, id_spectre INTEGER NOT NULL
, bss_binn VARCHAR(254) NOT NULL
, CONSTRAINT BSS_BINN_PK PRIMARY KEY (id_astre, id_spectre)
, CONSTRAINT BSS_BINN_FK FOREIGN KEY (id_astre, id_spectre) REFERENCES SPECTRE (id_astre, id_spectre)
) ;
INSERT INTO BSS_BINN (id_astre, id_spectre, bss_binn)
VALUES (1, 2, 'tout baigne') ;
-- -------------------------------------------------------
-- Vue pour encapsuler les jointures et voir en une fois
-- l'ensemble des propriétés des spectres.
-- -------------------------------------------------------
CREATE VIEW SPECTRE_VUE (id_astre, id_spectre, crval1, cdelt1, crpix1, cunit1, ctype1, bss_vhel, bss_tell, bss_cosm, bss_norm, bss_binn, bss_esrp)
AS
SELECT x.id_astre, x.id_spectre, crval1, cdelt1, crpix1, cunit1, ctype1, bss_vhel, bss_tell, bss_cosm, bss_norm, COALESCE(bss_binn, 'nib !')
, COALESCE(NULLIF(CAST(bss_esrp AS VARCHAR), ''), 'néant !')
FROM SPECTRE AS x LEFT JOIN BSS_ESRP AS y ON x.id_astre = y.id_astre AND x.id_spectre = y.id_spectre
LEFT JOIN BSS_BINN AS z ON x.id_astre = z.id_astre AND x.id_spectre = z.id_spectre
;
-- -------------------------------------------------------------------------------
-- Pour voir l'ensemble des propriétés obligatoires et optionnelles des spectres
-- -------------------------------------------------------------------------------
SELECT * FROM SPECTRE_VUE ;
Modélisation de propriétés variables en nombre et en type
Répondre avec citation
Envoyé par aras-vbo
)
JMerise ne traite pas encore des associations avec des entités-types qui sont des propriétés monovaluées (cardinalité 0,1 / (1,1)).
)
mais ouvre la discussion dans le forum AMC[/EDiT]
Partager