© Éditions Eyrolles
231
chapitre n° 3
Le niveau physique : de SQL2 à SQL3
Tableau 3.27 Association n-aire par la solution universelle SQL3
Schéma logique
Script SQL3 (Oracle)
Avion[immat, typeav]
Compagnie[comp, nomcomp]
Troncon[refAvi, refDepart,
refArrivee, refComp, dateV,
tempsVol]
Aeroport[codeOACI, nomAero]
CREATE TYPE avion_type AS OBJECT
(immat VARCHAR(6), typeav VARCHAR(10))
CREATE TYPE aeroport_type AS OBJECT
(codeOACI VARCHAR(6), nomAero VARCHAR(30))
CREATE TYPE compagnie_type AS OBJECT
(comp VARCHAR(4), nomcomp VARCHAR(30))
CREATE TYPE troncon_type AS OBJECT
(refAvi
REF avion_type,
refDepart REF aeroport_type,
refArrivee REF aeroport_type,
refComp
REF compagnie_type,
dateV
DATE, tempsVol
NUMBER)
CREATE TABLE Avion OF avion_type
(CONSTRAINT pk_avion PRIMARY KEY(immat))
CREATE TABLE Aeroport OF aeroport_type
(CONSTRAINT pk_aeroport PRIMARY KEY(codeOACI))
CREATE TABLE Compagnie OF compagnie_type
(CONSTRAINT pk_compagnie PRIMARY KEY(comp))
CREATE TABLE Troncon OF troncon_type
(CONSTRAINT nn_refAvi
CHECK (refAvi IS NOT NULL),
CONSTRAINT fk_refAvi
refAvi REFERENCES Avion,
CONSTRAINT nn_refDepart
CHECK(refDepart IS NOT NULL),
CONSTRAINT fk_refDep
refDepart REFERENCES Aeroport,
CONSTRAINT nn_refArrivee CHECK
(refArrivee IS NOT NULL),
CONSTRAINT fk_refArr
refArrivee REFERENCES Aeroport,
CONSTRAINT nn_refComp CHECK
(refComp IS NOT NULL),
CONSTRAINT fk_refCom
refComp REFERENCES Compagnie,
CONSTRAINT nn_dateV CHECK (dateV IS NOT NULL))