Conseils sur la création des types utilisateurs ayant des collections et des références et création des tables objets dans Oracle SQL Developer Data Modeler
/*
Types utilisateurs, collections, références et tables objets
On vous demande d’implanter soit directement via le SQL, soit en éditant le modèle des types de données d’Oracle SQL Developer Data Modeler (voir annexe 3), le Graphe des Types Complet de l’annexe 4 en prenant en compte comme tables de stockage objet ADHERENT_OR, INSTRUCTEUR_OR, TYPE_ENGIN_OR, AVION_OR, VOL_OR. Ainsi l'implantation de l'héritage est horizontale : ADHERENT_OR contient toutes les instances de ADHERENT_TY qui ne sont pas dans ses sous-classes. En complément, on crée une table par sous-type, ici INSTRUCTEUR_OR. Les contraintes d’obligation (attribut MANDATORY dans le modèle des types données de d’Oracle SQL Developer Data Modeler) de ce document seront implantées dans les tables objet. Les collections seront des Nested Tables et auront un index définit sur le référencement par valeur.
A noter que ni dans Oracle, ni dans PostgreSQL, il n’y a la possibilité de créer des variables de classes dans les types utilisateurs. Les attributs de type paramètre seront donc dans une table PARAMETRE_OR de même structure que dans la solution purement relationnelle. Un déclencheur pour Oracle et une fonction utilisateur getSMIC_OR() : n dans un CHECK pour PostgreSQL garantiront des salaires fixes conformes. Des déclencheurs sur la table PARAMETRE_OR gèreront le changement de la valeur du SMIG à la hausse et à la baisse.
*/
L'objectif est de créer le diagramme des types utilisateurs (modèle des types de données dans Oracle SQL Developer Data Modeler). Puis d'utiliser ces types pour créer un modèle relationnel avec les 5 tables citées, toutes basées sur ces types. Enfin, on exportera un script DDL SQL.
L'objectif à atteindre est pour le modèle des types de données ceci :
Dans ce diagramme, les collections (AUTONOMIES, QUALIFICATIONS, AVIONS) ne sont jamais obligatoires (M pour Mandatory). De même, les références ne le sont pas également du fait du bi-référencement qui veut que l'on value la référence par valeur d'abord. Souvent, la table objet à référencer n'est même pas encore créée. Dans un second temps, une seule requête SQL mettra à jour toutes les références par pointeur. A noter que la référence par valeur numdirecteur n'est pas obligatoire car un vol peut ne pas avoir de directeur si l'adhérent est autonome pour le type d'avion du vol. Les autres attribut tirent leur caractère obligatoire du dictionnaire de données.
D'abord, il faut afficher le modèle des types de données.
On utilise l'icône suivante pour créer un nouveau type utilisateur : .
Pour le type ADHERENT_TY, on se rend directement dans l'onglet Attribut pour les éditer.
Pour le type de l'attribut, un type logique donne accès aux types élémentaires VARCHAR(longueur à préciser obligatoirement), INTEGER, NUMERIC(11) pour un entier long, NUMERIC(15,2) pour un réel ou monétaire. Un type structuré permet de préciser une composition (pas de référence) ou une référence à un type structuré déjà défini. Un type Ensemble permet de préciser que c'est une collection parmi celles qui ont déjà été définies.
Lorsque l'on a besoin d'un type qui n'existe pas encore, on peut utiliser dans un premier temps un type pré-défini comme SDO_GEOMETRY puis créer le type manquant et remodifier le type qui a une définition fausse. C'est possible pour le type INSTRUCTEUR_TY car il existe un type prédéfini. Ce n'est pas possible pour NT_REF_TYPEAVION_TY car il n'existe pas de type ensemble prédéfini. Il faudra alors quitter la définition du type ADHERENT_TY incomplète, créer le type elément REF_TYPEAVION_TY puis le type ensemble NT_REF_TYPEAVION_TY, puis retourner à la définition de ADHERENT_TY pour utiliser le type NT_REF_TYPEAVION_TY.
Pour créer un type ensemble, il faut faire au niveau de la liste des type dans le modèle des types de données :
Une collection se traduira par une nested table. Un array se traduira par un varray. La boite de dialogue est la suivante :
La relation d'héritage de type de INSTRUCTEUR_TY se définit dans son onglet général :
Dans les types, on peut ajouter des méthodes (CONSTRUCTOR, OVERRIDING). Les méthodes STATIC, MAP, ORDER ne semblent pas prises en compte. On peut préciser le corps de la méthode mais aucune facilité n'est prévue. C'est cependant utile pour générer une signature pour les méthodes non techniques déja définies dans le diagramme de classe ou étant attribut calculé dans le dictionnaire des données.
Une fois le modèle des types de données complet, il faut afficher le modèle relationnel actuel (il en existe toujours un).
L'objectif est d'atteindre le modèle relationnel final suivant :
Les pointeurs sont les liens mais ils sont définis dans les types de données.
Pour créer une nouvelle table : .
Une table objet-relationnelle est une table qui est basée sur un type de données dans l'onglet général de sa définition.
Lorsque l'on transforme un table en table objet en renseignant le type de base, les attributs ne sont pas encore disponible. Il faut faire Appliquer ou OK et revenir à la définition pour les avoir disponibles avec une première colonne correspondant à l'OID. Ce dernier est par défaut clé primaire.
Rendre l'OID comme clé primaire peut sembler redondant (il est unique mais codé sur 16 octets). Puisque l'on a opté pour le bi-référencement, chaque table objet a une clé par valeur qu'il vaut mieux choisir. Il faut éditer la clé primaire.
Pour la clé candidate NumAdherent de la table INSTRUCTEUR_OR, on peut la déclarer en tant que contrainte unique.
Pour chaque bi-référencement présent dans une table, on peut créer un index au niveau du référencement par valeur qui va être une clé de recherche, (clé secondaire). Ici NumInstructeurSuiveur dans la table ADHERENT_OR.
L'icône pour la substitution de type est : . Il faut ensuite cliquer sur la table INSTRUCTEUR_OR puis sur la table ADHERENT_OR.
Le seul lien est une substitution de type qui se traduira par un déclencheur objet dans le modèle physique Oracle SQL3.
Les attributs en commun dans INSTRUCTEUR_OR disparaissent alors. La table INSTRUCTEUR_OR ne sera créée. A sa place, un déclencheur objet sur la table ADHERENT_OR testant que seules les instances de ADHERENT_TY et de INSTRUCTEUR_TY peuvent être insérées comme tuple de ADHERENT_OR. Une substitution de type crée une implémentation de l'héritage aplatie et non pas horizontale. Aussi, il ne faut pas la faire dans ce cas précis. On peut détruire la relation pour revenir à la situation antérieure.
Il faut ensuite exporter en tant que script DDL.
On choisit la version du serveur Oracle de destination. Puis on appuie sur Générer.
On choisit les éléments à générer :
On peut prévisionner le script :
On peut éditer le script pour obtenir :
DROP TABLE ADHERENT_OR CASCADE CONSTRAINTS PURGE;
DROP TABLE INSTRUCTEUR_OR CASCADE CONSTRAINTS PURGE;
DROP TABLE AVION_OR CASCADE CONSTRAINTS PURGE;
DROP TABLE TYPE_ENGIN_OR CASCADE CONSTRAINTS PURGE;
DROP TABLE VOL_OR CASCADE CONSTRAINTS PURGE;
DROP TYPE NT_REF_Avion_Ty FORCE;
DROP TYPE NT_REF_TypeAvion_Ty FORCE;
DROP TYPE REF_Avion_Ty FORCE;
DROP TYPE REF_TypeAvion_Ty FORCE;
DROP TYPE Avion_Ty FORCE;
DROP TYPE TypeAvion_Ty FORCE;
DROP TYPE Adherent_Ty FORCE;
DROP TYPE Instructeur_Ty FORCE;
DROP TYPE Vol_Ty FORCE;
CREATE OR REPLACE TYPE Avion_Ty
;
/
CREATE OR REPLACE TYPE REF_Avion_Ty
AS OBJECT
(
NumAvion VARCHAR2 (32) ,
REFA REF Avion_Ty
) NOT FINAL
;
/
CREATE OR REPLACE TYPE NT_REF_Avion_Ty
IS TABLE OF REF_Avion_Ty
;
/
CREATE OR REPLACE TYPE TypeAvion_Ty
AS OBJECT
(
TypeAvion VARCHAR2 (32) ,
NBPlaces INTEGER ,
Puissance INTEGER ,
PrixHeureMoteur NUMBER (15,2) ,
AVIONS NT_REF_Avion_Ty
) NOT FINAL
;
/
CREATE OR REPLACE TYPE REF_TypeAvion_Ty
AS OBJECT
(
TypeAvion VARCHAR2 (32) ,
REFT REF TypeAvion_Ty
) NOT FINAL
;
/
CREATE OR REPLACE TYPE NT_REF_TypeAvion_Ty
IS TABLE OF REF_TypeAvion_Ty
;
/
CREATE OR REPLACE TYPE Avion_Ty
AS OBJECT
(
NumAvion VARCHAR2 (32) ,
TypeAERO REF_TypeAvion_Ty ,
AnneeAvion INTEGER ,
Hangar VARCHAR2 (2) ,
DateMiseEnService DATE
) NOT FINAL
;
/
CREATE OR REPLACE TYPE Instructeur_Ty
;
/
CREATE OR REPLACE TYPE Adherent_Ty
AS OBJECT
(
NumAdherent NUMBER (11) ,
NumInstructeurSuiveur NUMBER (11) ,
REFInstructeurSuiveur REF Instructeur_Ty ,
NomAdherent VARCHAR2 (32) ,
PrenomAdherent VARCHAR2 (32) ,
AdresseAdherent VARCHAR2 (200) ,
AUTONOMIES NT_REF_TypeAvion_Ty,
MEMBER FUNCTION isManaged RETURN NUMBER ,
MEMBER FUNCTION isInstructor RETURN NUMBER
) NOT FINAL
;
/
CREATE OR REPLACE TYPE BODY ADHERENT_TY
AS
MEMBER FUNCTION isManaged RETURN NUMBER
AS
result number(1):=0;
BEGIN
IF self.NumInstructeurSuiveur IS NOT NULL THEN
result:=1;
END IF;
RETURN result;
END isManaged;
MEMBER FUNCTION isInstructor RETURN NUMBER
AS
result number(1):=0;
BEGIN
IF self.NumInstructeurSuiveur IS NULL THEN
result:=1;
END IF;
RETURN result;
END isInstructor;
END
;
/
CREATE OR REPLACE TYPE Instructeur_Ty
UNDER Adherent_Ty (
NumInstructeur NUMBER (11) ,
SalaireFixe NUMBER (15,2) ,
QUALIFICATIONS NT_REF_TypeAvion_Ty
) FINAL
;
/
CREATE OR REPLACE TYPE Vol_Ty
AS OBJECT
(
NumVol NUMBER (11) ,
NumDirecteur NUMBER (11) ,
REFDirecteur REF Instructeur_Ty ,
NumPayeur NUMBER (11) ,
REFPayeur REF Adherent_Ty ,
NumAvion VARCHAR2 (32) ,
REFAvion REF Avion_Ty ,
DateVol DATE ,
DureeVol INTEGER
) NOT FINAL
;
/
CREATE TABLE ADHERENT_OR OF Adherent_Ty SUBSTITUTABLE AT ALL LEVELS
(
nomadherent NOT NULL ,
prenomadherent NOT NULL ,
adresseadherent NOT NULL ,
CONSTRAINT PK_ADHERENTS_OR PRIMARY KEY ( NumAdherent )
) NESTED TABLE AUTONOMIES STORE AS TAB_AUTONOMIES_OR
;
CREATE INDEX numinstructeursuiveur_idx ON ADHERENT_OR (numinstructeursuiveur);
DROP INDEX AEROCLUB_OR.LISTE_AUTONOMIES;
CREATE INDEX Liste_autonomies_idx ON TAB_AUTONOMIES_OR (typeavion);
-- Table INSTRUCTEURS: is part of type substitution hierarchy
CREATE TABLE INSTRUCTEUR_OR OF Instructeur_Ty SUBSTITUTABLE AT ALL LEVELS
(
nomadherent NOT NULL ,
prenomadherent NOT NULL ,
adresseadherent NOT NULL ,
numadherent NOT NULL UNIQUE,
salairefixe NOT NULL ,
CONSTRAINT PK_INSTRUCTEURS_OR PRIMARY KEY ( NumInstructeur )
)
NESTED TABLE AUTONOMIES STORE AS TAB_AUTONOMIES_INS_OR
NESTED TABLE QUALIFICATIONS STORE AS TAB_QUALIFICATIONS_OR
;
CREATE INDEX Liste_autonomies_ins_idx ON TAB_AUTONOMIES_INS_OR (typeavion);
CREATE INDEX Liste_qualifications_idx ON TAB_QUALIFICATIONS_OR (typeavion);
CREATE TABLE AVION_OR
OF Avion_Ty
SUBSTITUTABLE AT ALL LEVELS
(
numavion CONSTRAINT PK_AVION_OR PRIMARY KEY ,
TypeAERO NOT NULL ,
anneeavion NOT NULL ,
hangar NOT NULL CHECK(UPPER(hangar) IN ('H1','H2')),
datemiseenservice NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED
;
CREATE TABLE TYPE_ENGIN_OR
OF TypeAvion_Ty SUBSTITUTABLE AT ALL LEVELS
(
typeavion CONSTRAINT PK_TYPE_ENGIN_OR PRIMARY KEY ,
nbplaces NOT NULL ,
puissance NOT NULL ,
prixheuremoteur NOT NULL
) OBJECT IDENTIFIER IS SYSTEM GENERATED
NESTED TABLE AVIONS STORE AS TAB_AVIONS_OR
;
DROP INDEX AEROCLUB_OR.LISTE_AVIONS_IDX;
CREATE INDEX Liste_avions_idx ON TAB_AVIONS_OR (numavion);
CREATE TABLE VOL_OR
OF Vol_Ty
SUBSTITUTABLE AT ALL LEVELS
(
NumPayeur NOT NULL ,
NumAvion NOT NULL ,
datevol DEFAULT SYSDATE NOT NULL ,
dureevol NOT NULL ,
CONSTRAINT PK_VOLS_OR PRIMARY KEY ( NumVol )
)
OBJECT IDENTIFIER IS PRIMARY KEY
;
CREATE INDEX numdirecteur_idx ON VOL_OR (numdirecteur);
CREATE INDEX numpayeur_idx ON VOL_OR (numpayeur);
CREATE INDEX numavion_idx ON VOL_OR (numavion);
COMMENT ON TABLE adherent_or IS 'Les adherents de l''aeroclub';
COMMENT ON COLUMN adherent_or.numadherent IS 'Numero de l''adherent';
COMMENT ON COLUMN adherent_or.numinstructeursuiveur IS 'Numero de l''instructeur suiveur';
COMMENT ON COLUMN adherent_or.refinstructeursuiveur IS 'Reference de l''instructeur suiveur';
COMMENT ON COLUMN adherent_or.nomadherent IS 'Nom de l''adherent';
COMMENT ON COLUMN adherent_or.prenomadherent IS 'Prenom de l''adherent';
COMMENT ON COLUMN adherent_or.adresseadherent IS 'Adresse de l''adherent';
COMMENT ON COLUMN adherent_or.autonomies IS 'Un adherent est autonome pour un type d''avion';
COMMENT ON TABLE avion_or IS 'Les avions de l''aeroclub';
COMMENT ON COLUMN avion_or.numavion IS 'numéro d''immatrication de l''avion';
COMMENT ON COLUMN avion_or.typeaero IS 'référence au type de l''avion qui peut être la base de la famille d''avion';
COMMENT ON COLUMN avion_or.anneeavion IS 'année de construction de l''avion';
COMMENT ON COLUMN avion_or.hangar IS 'hangar de l''avion';
COMMENT ON COLUMN avion_or.datemiseenservice IS 'date de mise en service dans l''aeroclub';
COMMENT ON TABLE instructeur_or IS 'Les instructeurs de l''aeroclub';
COMMENT ON COLUMN instructeur_or.numinstructeursuiveur IS 'Numero de l''instructeur suiveur : NULL';
COMMENT ON COLUMN instructeur_or.refinstructeursuiveur IS 'Reference de l''instructeur suiveur : NULL ';
COMMENT ON COLUMN instructeur_or.nomadherent IS 'Nom de l''instructeur';
COMMENT ON COLUMN instructeur_or.prenomadherent IS 'Prenom de l''instructeur';
COMMENT ON COLUMN instructeur_or.adresseadherent IS 'Adresse de l''instructeur';
COMMENT
ON COLUMN instructeur_or.autonomies IS 'Un adherent instructeur est
autonome pour des types d''avion, au moins ses qualifications';
COMMENT ON COLUMN instructeur_or.numinstructeur IS 'Numero de l''instructeur';
COMMENT ON COLUMN instructeur_or.numadherent IS 'Numero de l''adherent qui permet de decrire completement l''instructeur';
COMMENT ON COLUMN instructeur_or.salairefixe IS 'Salaire fixe de l''instructeur';
COMMENT ON COLUMN instructeur_or.qualifications IS 'Un instructeur est qualifie pour au moins un type d''avion';
COMMENT ON TABLE type_engin_or IS 'Type des avions de l''aeroclub';
COMMENT ON COLUMN type_engin_or.typeavion IS 'Type d''avion';
COMMENT ON COLUMN type_engin_or.nbplaces IS 'Nombre de place de l''avion';
COMMENT ON COLUMN type_engin_or.puissance IS 'Puissance du moteur';
COMMENT ON COLUMN type_engin_or.prixheuremoteur IS 'Prix de l''heure du moteur';
COMMENT ON COLUMN type_engin_or.avions IS 'Les avions de la flotte de l''aeroclub correspondant au type d''avion';
COMMENT ON TABLE vol_or IS 'Les vols effectues dans l''aeroclub';
COMMENT ON COLUMN vol_or.numvol IS 'Numero du vol';
COMMENT ON COLUMN vol_or.numdirecteur IS 'Numero de l''instructeur qui dirige le vol';
COMMENT ON COLUMN vol_or.refdirecteur IS 'Reference de l''instructeur qui dirige le vol';
COMMENT ON COLUMN vol_or.numpayeur IS 'Numero de l''adherent qui paye le vol';
COMMENT ON COLUMN vol_or.refpayeur IS 'Reference de l''adherent qui paye le vol';
COMMENT ON COLUMN vol_or.numavion IS 'Numero de l''avion du vol';
COMMENT ON COLUMN vol_or.refavion IS 'Reference de l''avion du vol';
COMMENT ON COLUMN vol_or.datevol IS 'Date du vol';
COMMENT ON COLUMN vol_or.dureevol IS 'Duree du vol';
-- En PL/SQL, il n'y a pas de variable de classe mais par contre des méthodes de classes.
-- Sinon on aurait pu mettre valSMIG comme variable de classe du type Instructeur_Ty
DROP VIEW PARAMETRE_OR;
DROP SYNONYM PARAMETRE_OR;
DROP TABLE PARAMETRE_OR CASCADE CONSTRAINTS PURGE;
CREATE TABLE PARAMETRE_OR (
ValSmig NUMBER NOT NULL,
NoSmig NUMBER DEFAULT 1
NOT NULL
CONSTRAINT uqParemetre
UNIQUE
CONSTRAINT ckConstParametre -- garanti a tout instant
CHECK ( NoSmig IN (1) ) -- l'unicite de la valeur du SMIG
)
;
COMMENT ON TABLE PARAMETRE_OR IS 'Table parametres';
COMMENT ON COLUMN PARAMETRE_OR.ValSMIG IS 'Valeur actuelle du SMIG';
COMMENT ON COLUMN PARAMETRE_OR.NoSMIG IS 'Garantie de l''unicite du SMIG';
-- le salaire fixe d'un instructeur doit être supérieur au SMIG
CREATE OR REPLACE TRIGGER SalSupSMIG
BEFORE
INSERT
OR UPDATE OF salairefixe
ON instructeur_or
FOR EACH ROW
DECLARE
mauvais_salaire EXCEPTION;
vsalairesmig PARAMETRE_OR.ValSMIG%TYPE;
BEGIN
SELECT ValSmig INTO vsalairesmig
FROM PARAMETRE_OR;
IF :NEW.SalaireFixe < vsalairesmig THEN
RAISE mauvais_salaire;
END IF;
EXCEPTION
WHEN mauvais_salaire THEN
raise_application_error(-20000, 'Un instructeur doit avoir un salaire fixe supérieur au SMIG !');
WHEN NO_DATA_FOUND THEN
/*
Il n'y a pas de tuple dans SMIG
*/
raise_application_error(-20000, 'Le paramètre SMIG doit être fixé !');
END;
/
CREATE OR REPLACE TRIGGER SmigForceSal
AFTER
INSERT OR UPDATE OF ValSmig
ON PARAMETRE_OR
BEGIN
UPDATE INSTRUCTEUR_OR i
SET SALAIREFIXE=(
SELECT
CASE WHEN ValSmig<SalaireFixe THEN SalaireFixe
ELSE ValSMIG END
FROM INSTRUCTEUR_OR, PARAMETRE_OR
WHERE numInstructeur=i.numInstructeur);
END;
/
-- Eventuellement, écrire et tester un trigger
-- qui vérifie que le SMIG ne décroît jamais.
-- lecture de l'ancienne valeur=> Trigger
CREATE OR REPLACE TRIGGER SMIGDONOTDECREASE
BEFORE INSERT OR UPDATE OF VALSMIG ON PARAMETRE_OR FOR EACH ROW
BEGIN
IF (:OLD.VALSMIG > :NEW.VALSMIG) then
raise_application_error(-20002, 'Le SMIG ne peut pas baisser');
END IF;
END;
/
INSERT INTO PARAMETRE_OR(ValSmig) VALUES(800);