Bases de Donn ees Avanc ees - LIMSI

Bases de Donnees AvanceesUML et SQL 2/3

Thierry Hamon

Bureau H202Institut Galilee - Universite Paris 13

&LIMSI-CNRS

[email protected]

https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/

INFO2 – BDA

1/193

[email protected]

https://perso.limsi.fr/hamon/Teaching/P13/BDA-INFO2-2018-2019/

PlanDe UML a SQL 2/3, Objet-Relationnel, Oriente-Objet

Introduction

De UML a SQL2(du conceptuel au relationnel etendu – objet-relationnel)

De UML a SQL3(du conceptuel a l’oriente objet)

Conclusion

2/193

Conception, Developpement, Utilisation, Administration

1 Etape conceptuelle : Conception et Modelisation de bases dedonnees

2 Etape logique : Implantation d’une base de donnees

3 Etape physique :

4 Logiciels (SGBD, Interfaces, ...) & Materiels

3/193

Modelisation de Bases de DonneesPhase d’analyse : definition d’un schema conceptuelSchema Conceptuel de Donnees (SCD) : selon le formalismeutilise,

ensemble d’Entites et d’Associationsouensemble de Classes

Formalisme EA, ER

Formalisme UML :

4/193

Modelisation de Bases de Donnees

Differents formalismes de modelisation de schemas conceptuels deBD :

Formalisme EA, ER, EER

Modele Entite-Association (Entity-Relationship Model)Modele Entite-Association Etendu (ExtendedEntity-Relationship Model

Formalisme UML (Unified Modelling Language)

5/193

Modele Entite-AssociationRappel

Entite : tout concept concret ou abstrait individualisable

Classe ou type d’entites : regroupement d’entites de memenature (niveau generique)

Association : relation liant plusieurs entites

Classe ou type d’associations) : regroupement d’associationspresentant les memes caracteristiques

6/193

Modele entite-association etendu

Modele entite-association : jeu de concept reduit maissuffisant pour la modelisation de problemes simples (ou peucomplexes)

Modele Entite-association etendu : Modelisation plus preciseet plus expressive de problemes complexes et de grande taille

Introduction de mecanismes d’abstraction

de classificationd’heritaged’agregation

7/193

Types faibles

Type d’entites ou d’associations faibles :

existence d’une instance subordonnee a l’existence d’un autretype d’entite ou d’association

8/193

Classification

Regroupement d’entites dans des classes en fonction deproprietes communes

Possibilite de classer un objet dans plusieurs classes

Exemple :

Livre electronique : fichier electronique, et livre

Autocar : vehicule de transport en commun, vehicule amoteur a explosion

9/193

HeritageSpecialisation - Generalisation

Un type d’ entite A est une specialisation d’un autre type d’entiteB si

chaque entite de A est une entite de BUne seule entite (au plus) de B est associe a une entite de A

10/193

Agregation

Description de types d’entites complexesUn type d’associations entre types d’entites est considere commeun nouveau type d’entites

11/193

Modelisation de Bases de DonneesLes deux formalismes E/R et UML sont tres proches /� equivalents �

Entite/Association → UMLEntite ObjetType d’entite ClasseRelation ObjetType d’association ClasseAttribut/Propriete ProprieteRole / Label Role

Methode

Domaine Contrainte de domaineCle Contrainte de cleContrainte ContrainteCardinalite Multiplicite/Cardinalite0,1 1,1 0,n 1,n a,b a,a 0..1 1 0..* 1..* a..b a

Diagramme E/A Diagramme de Classe UML

12/193

De EA a SQL

Objectifs :

Implantation d’un schema conceptuel (SCD) dans un BDrelationnelle

Exploitation du SCD par le SGBD et les modules deprogrammation

→ Transformation dans un schema relationnel : Schema Logiquede Donnees (SLD)

13/193

De EA a SQLExemple de schema conceptuel EA (SCD)

14/193

De EA a SQLExemple de schema logique (schema relationnel) SLD

R1 ENT 2 ( B1 , B2 , B3 )R2 ENT 1 ( A1 , A2 , B1 ∗)R3 ENT 3 ( C1 , C2 )R4 ENT 4 ( D1 )R5 ASSOC 6 ( B1∗ , C1∗ , D1∗ , X1 )

15/193

Regles de passagedu modele Entite-Association au modele Relationnel

Tout type d’entite E est traduit en une relation R

La cle primaire de R est l’identifiant de ELes attributs de R sont ceux de E.

Tout type d’association est traduit :

en une cle etrangere dans une relation existante si lacardinalite est du type 1,1 ou 0,1

en une nouvelle relation si aucune cardinalite n’est du type 1,1

ou 0,1 (elles sont toutes du type 0,n ou 1,n)

Plusieurs algorithmes sont possibles selon l’interpretation de lacardinalite minimale egale a 0.

16/193

De UML a SQL

Traduction des associations binaires

Traduction des associations binaires recursives

Traduction des associations n-aires (n > 2)

Traduction des associations d’heritage

Traduction des contraintes d’heritage

Traduction des associations d’agregation

Traduction des contraintes d’integrite fonctionnelles(contraintes : Partition, Exclusion, Totalite, Simultaneite,Inclusion)

17/193

Integrite des donnees (1)

Les SGBD prennent en compte l’integrite des donnees definies via

la declaration de contraintes (constraints)

la programmation de

fonctions (functions)de procedures (procedures) catalogueesde paquetages (packages)de declencheurs (triggers)

Le principe etant d’assurer la coherence de la base apres chaquemise a jour par les commandes insert, update ou delete

18/193

Integrite des donnees (2)

Notation utilisee concernant les noms des contraintes :

la contrainte cle primaire d’une table se nomme pk table

la contrainte cle etrangere d’une table se nommefk table1 colonne table2

la contrainte de validite d’une colonne se nommeck table colonne

la contrainte de type non nulle sur une colonne se nommenn table colonne

la contrainte de type unique sur une colonne se nommeunique table colonne

19/193

Traduction des associations binaires (1)Association du type 1-1

REM ∗∗∗ Un s t a g e e s t e f f e c t u e par au p l u s un e t u d i a n t

create tab l e STAGE( NUMEROS number ( 7 ) ,

NOMENTREPRISE varchar ( 4 0 ) ,TELENT varchar ( 1 5 ) ,ADRENT varchar ( 5 0 ) ,con s t r a i n t PK STAGE primary key (NUMEROS)

) ;

20/193

Traduction des associations binaires (2)Association du type 1-1

REM ∗∗∗ Un e t u d i a n t e f f e c t u e o b l i g a t o i r e m e n t un s t a g e unique

c r ea te t ab l e ETUDIANT( NUMEROE number ( 7 ) ,

NOM va rcha r ( 1 0 ) ,PRENOM va rcha r ( 1 0 ) ,DATENAISSANCE date ,SEXE char ( 1 ) ,NUMEROS number ( 7 ) ,c o n s t r a i n t PK ETUDIANT pr imary key (NUMEROE) ,c o n s t r a i n t FK ETUDIANT NUMEROS STAGE f o r e i g n key (NUMEROS)

r e f e r e n c e s STAGE(NUMEROS) ,c o n s t r a i n t CK ETUDIANT SEXE check (SEXE i n ( ’M’ , ’ F ’ ) ) ,

−−c o n s t r a i n t NN ETUDIANT NUMEROS check (NUMEROS i s not n u l l ) ,c o n s t r a i n t UNIQUE ETUDIANT NUMEROS unique (NUMEROS)

) ;

21/193

Traduction des associations binaires (1)Association du type 1-N

REM ∗∗∗ Une p e r s o n n e peut p o s s e d e r p l u s i e u r s v o i t u r e s

c r ea te t ab l e PERSONNE(

NUMEROP number ( 7 ) ,NOM va rcha r ( 1 0 ) ,PRENOM va rcha r ( 1 0 ) ,DATENAISSANCE date ,SEXE char ( 1 ) ,c o n s t r a i n t PK PERSONNE pr imary key (NUMEROP)

) ;

22/193

Traduction des associations binaires (2)Association du type 1-N

REM ∗∗∗ Une v o i t u r e e s t o b l i g a t o i r e m e n t p o s s e d e e par une p e r s o n n e unique

c r ea te t ab l e VOITURE(

NUMIMMAT va rcha r ( 1 5 ) ,MARQUE va rcha r ( 2 0 ) ,TYPE va rcha r ( 3 0 ) ,NUMEROP number ( 7 ) ,c o n s t r a i n t PK VOITURE pr imary key (NUMIMMAT) ,c o n s t r a i n t FK VOITURE NUMEROP PERSONNE f o r e i g n key (NUMEROP)

r e f e r e n c e s PERSONNE(NUMEROP) ,

−−c o n s t r a i n t NN VOITURE NUMEROP check (NUMEROP i s not n u l l )

) ;

23/193

Traduction des associations binaires (1)Association du type N-N

REM ∗∗∗ Une p e r s o n n e peut c r e e r p l u s i e u r s e n t r e p r i s e s

c r ea te t ab l e PERSONNE(

NUMEROP number ( 7 ) ,NOMP va rcha r ( 1 0 ) ,PRENOM va rcha r ( 1 0 ) ,DATENAISSANCE date ,SEXE char ( 1 ) ,c o n s t r a i n t PK PERSONNE pr imary key (NUMEROP)

) ;

24/193


REM ∗∗∗ Une e n t r e p r i s e d o i t e t r e c r e e eREM par une ou p l u s i e u r s p e r s o n n e s

create tab le ENTREPRISE(

NSIRET varchar ( 2 0 ) ,NOME varchar ( 2 0 ) ,STATUTJUR varchar ( 1 0 ) ,const ra in t PK ENTREPRISE primary key ( NSIRET )

) ;

25/193


create tab l e CREER(

NUMEROP number ( 7 ) ,NSIRET varchar ( 2 0 ) ,DATECREATION date ,con s t r a i n t PK CREER primary key (NUMEROP, NSIRET ) ,con s t r a i n t FK CREER NUMEROP PERSONNE

f o r e i gn key (NUMEROP)r e f e r e n c e s PERSONNE(NUMEROP) ,

con s t r a i n t FK CREER NSIRET ENTREPRISEf o r e i gn key ( NSIRET )r e f e r e n c e s ENTREPRISE( NSIRET )

) ;

La cardinalite minimale de l’association creer pourra etre testee parl’intermediaire d’une procedure PL/SQL

26/193

Traduction des associations binaires (1)Association du type Reflexif/Recursif, UML ∼ EA

c r ea te t ab l e PERSONNES(

NUMERO number ( 7 ) , NOM va rcha r ( 1 5 ) , PRENOM va rcha r ( 1 5 ) ,DATENAISSANCE date , SEXE char ( 1 ) , PERE number ( 7 ) ,MERE number ( 7 ) ,c o n s t r a i n t PK PERSONNES pr imary key (NUMERO) ,c o n s t r a i n t FK PERSONNES PERE PERSONNES f o r e i g n key (PERE)

r e f e r e n c e s PERSONNES,c o n s t r a i n t FK PERSONNES MERE PERSONNES f o r e i g n key (MERE)

r e f e r e n c e s PERSONNES,c o n s t r a i n t CK SEXE PERSONNES check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;

27/193


c r ea te t ab l e PRODUITS(

NUMERO number ( 3 ) , NOM va rcha r ( 1 5 ) , QSTOCK number ( 5 ) ,c o n s t r a i n t PK PRODUITS pr imary key (NUMERO) ,c o n s t r a i n t CK QSTOCK PRODUITS check (QSTOCK >= 0)

) ;

28/193


c r ea te t ab l e NOMENCLATURE(

COMPOSER number ( 3 ) , COMPOSANT number ( 3 ) , QFABR number ( 5 ) ,c o n s t r a i n t PK NOMENCLATURE pr imary key (COMPOSER, COMPOSANT) ,c o n s t r a i n t FK NOMENCL COMPOSER PRODUITS f o r e i g n key (COMPOSER)

r e f e r e n c e s PRODUITS ,c o n s t r a i n t FK NOMENCL COMPOSANT PRODUITS f o r e i g n key (COMPOSANT)

r e f e r e n c e s PRODUITS ,c o n s t r a i n t CK QFABR NOMENCLATURE check (QFABR >= 0)

) ;

→ Definition de requetes recursives

29/193

Traduction des associations d’heritageTraduction des contraintes d’heritage

Gestion du personnel dans une universite

30/193

Associations d’heritage dans UML (1)

Recensement des differents cas d’heritage en fonction desinstances

Modelisation des differents heritages, dans le formalismeUML, a l’aide des contraintes

partitionexclusiontotalite

31/193

Associations d’heritage dans UML (2)

Expression des cas d’heritage a l’aide de

couverture

disjonction

d’instances dans une population donnee

Quatre type de contraintes sont recenses :

partition

totalite

exclusion

absence de contrainte

32/193

Contraintes d’heritages PARTITION et TOTALITE

Disjonction & Couverture → Partition

Non-Disjonction & Couverture → Totalite

33/193

Contraintes d’heritages EXCLUSION et ABSENCE DE

CONTRAINTE

Disjonction & Non-Couverture → ExclusionNon-Disjonction & Non-Couverture → Absence de contrainte

34/193

Exemple (1)Gestion du personnel dans une universite

Couverture + Disjonction → Partition

Personnel (P) est egal a l’Union de Enseignant (EC) et deBIATOS (B) et l’Intersection de EC et de B est Vide

Couverture + Non-Disjonction → Totalite

Personnel (P) est egal a l’Union de Enseignant (EC) et deBIATOS (B) et l’Intersection de EC et de B n’est pas Vide

35/193

Exemple (2)Gestion du personnel dans une universite

Non-Couverture + Disjonction → Exclusion

L’Union de Enseignant (EC) et de BIATOS (B) est inclusedans P et l’Intersection de EC et de B est Vide

Non-Couverture + Non-Disjonction → Absence de contraintes

L’Union de Enseignant (EC) et de BIATOS (B) est inclusedans P et l’Intersection de EC et de B n’est pas Vide

36/193

Transformation des associations d’heritage

Traduction d’une association d’heritage en fonction des contraintesde l’association d’heritage→ 3 familles de decomposition :

Decomposition par distinction

Decomposition descendante (push-down)

Decomposition ascendante (push-up)

37/193

Decomposition par distinction

Transformation de chaque sous-classe en une relation

Migration de la cle primaire de la sur-classe dans la ou lesrelations issues des sous-classes

La cle primaire de la sur-classe devient a la fois cle primaire etcle etrangere

DistinctionPERSONNEL( Numero , Nom, Prenom ,

DateNaissance , Sexe )

ENSEIGNANT( Numero∗ ,Echelon ,I n d i c e , S p e c i a l i t e )

BIATOS( Numero∗ , DateEmbauche ,S e r v i c e )

38/193

Decomposition descendante

Deux cas possibles selon la contrainte d’heritage :

Contrainte de totalite ou de partition sur l’association :Possibilite de ne pas traduire la relation issue de la sur-classe→ Migration de tous les attributs dans la ou les relationsissues de la ou des sous-classes

Sinon : Migration de tous les attributs dans la ou les relationsissues de la ou des sous-classes→ Duplication des donnees

39/193

Decomposition descendanteExemple

Contrainte de partition :

Aucun personnel ne peut etre a la fois enseignant et BIATOS

Il n’existe pas non plus un personnel n’etant ni enseignant nibiatos.

DescendanteENSEIGNANT( Numero ,

Nom, Prenom , DateNaissance ,Sexe , Echelon , I n d i c e ,S p e c i a l i t e )

BIATOS( Numero , Nom, Prenom ,DateNaissance , Sexe ,DateEmbauche , S e r v i c e )

40/193

Decomposition ascendante

Suppression de la ou les relations issues de la ou dessous-classes

Migration des attributs dans la relation issue de la sur-classe

Exemple : (absence de contrainte)Ascendante

PERSONNEL( Numero , Nom, Prenom ,DateNaissance , Sexe ,Echelon , I n d i c e ,S p e c i a l i t e ,DateEmbauche , S e r v i c e )

41/193

Transformation des associations d’heritage multiple

Memes regles ; plusieurs possibilitesExemple : (decomposition ascendante)Contrainte d’exclusion sur enseignant et BIATOS

PERSONNEL( Numero , Nom, Prenom ,DateNaissance , Sexe )

ENSEIGNANT ( Numero∗ ,Echelon , I n d i c e , S p e c i a l i t e ,DateDebutStage , DateF inStage )

BIATOS( Numero∗ , DateEmbauche ,S e r v i c e ,

DateDebutStage , DateF inStage )

42/193

Transformation des associations d’heritage en SQL 2Exemple

Gestion du personnel dans une universite

43/193

Transformation des associations d’heritageDecomposition par distinction

DistinctionPERSONNEL( Numero , Nom, Prenom ,

DateNaissance , Sexe )

ENSEIGNANT( Numero∗ ,Echelon ,I n d i c e , S p e c i a l i t e )

BIATOS( Numero∗ , DateEmbauche ,S e r v i c e )

REM ∗∗∗ Un p e r s o n n e l a l ’ U n i v e r s i t e

c r e a t e t a b l e PERSONNEL( NUMERO number ( 7 ) ,

NOM v a r c h a r ( 1 0 ) ,PRENOM v a r c h a r ( 1 0 ) ,DATENAISSANCE date ,SEXE c h a r ( 1 ) ,c o n s t r a i n t PK PERSONNEL p r i m a r y key (NUMERO) ,c o n s t r a i n t CK SEXE PERSONNEL check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;

44/193

Transformation des associations d’heritageDecomposition par distinction

REM ∗∗∗ P e r s o n n e l e n s e i g n a n t

c r ea te t ab l e ENSEIGNANT( NUMERO number ( 7 ) , ECHELON number ( 2 ) ,

INDICE number ( 5 ) , SPECIALITE va rcha r ( 2 0 ) ,c o n s t r a i n t PK ENSEIGNANT pr imary key (NUMERO) ,c o n s t r a i n t FK ENS PERS f o r e i g n key (NUMERO)

r e f e r e n c e s PERSONNEL) ;

REM ∗∗∗ P e r s o n n e l BIATOS ( Ing , Adm, Tech , Ouv , S e r v )

c r ea te t ab l e BIATOS( NUMERO number ( 7 ) , DATEEMBAUCHE date ,

SERVICE va rcha r ( 2 0 ) ,c o n s t r a i n t PK BIATOS pr imary key (NUMERO) ,c o n s t r a i n t FK BIATOS PERS f o r e i g n key (NUMERO)

r e f e r e n c e s PERSONNEL) ;

45/193

Transformation des associations d’heritageDecomposition descendante




REM ∗∗∗ P e r s o n n e l e n s e i g n a n t

c r ea te t ab l e ENSEIGNANT( NUMERO number ( 7 ) , NOM va rcha r ( 1 0 ) ,

PRENOM va rcha r ( 1 0 ) , DATENAISSANCE date ,SEXE char ( 1 ) , ECHELON number ( 2 ) ,INDICE number ( 5 ) , SPECIALITE va rcha r ( 2 0 ) ,c o n s t r a i n t CK SEXE ENSEIGNANT check

(SEXE i n ( ’M’ , ’ F ’ ) )c o n s t r a i n t PK ENSEIGNANT pr imary key (NUMERO)

) ;

46/193

Transformation des associations d’heritageDecomposition descendante




REM ∗∗∗ P e r s o n n e l BIATOS

c r ea te t ab l e BIATOS( NUMERO number ( 7 ) , NOM va rcha r ( 1 0 ) , PRENOM va rcha r ( 1 0 ) ,

DATENAISSANCE date , SEXE char ( 1 ) , DATEEMBAUCHE date ,SERVICE va rcha r ( 2 0 ) ,c o n s t r a i n t CK SEXE BIATOS check (SEXE i n ( ’M’ , ’ F ’ ) )c o n s t r a i n t PK BIATOS pr imary key (NUMERO)

) ;

46/193

Transformation des associations d’heritageDecomposition ascendante

AscendantePERSONNEL( Numero , Nom, Prenom ,

DateNaissance , Sexe ,Echelon , I n d i c e ,S p e c i a l i t e ,DateEmbauche , S e r v i c e )

REM ∗∗∗ P e r s o n n e l

c r ea te t ab l e PERSONNEL( NUMERO number ( 7 ) , NOM va rcha r ( 1 0 ) , PRENOM va rcha r ( 1 0 ) ,

DATENAISSANCE date , SEXE char ( 1 ) , ECHELON number ( 2 ) ,INDICE number ( 5 ) , SPECIALITE va rcha r ( 2 0 ) ,DATEEMBAUCHE date , SERVICE va rcha r ( 2 0 ) ,c o n s t r a i n t CK SEXE PERSONNEL check (SEXE i n ( ’M’ , ’ F ’ ) )c o n s t r a i n t PK PERSONNEL pr imary key (NUMERO)

) ;

47/193

Traduction des contraintes d’heritageDecomposition par distinction

→ Contrainte de partition

Contrainte de totalite

Contrainte d’exclusion

Sans ContrainteContraintes d’heritage :

(Contrainte A) Il n’existe pas de personnel a la fois enseignantet BIATOS

(Contrainte B) Il n’existe pas de personnel ni enseignant niBIATOS

48/193


Implementation de la contrainte A : 2 declencheurs

REM ∗∗∗ D e c l e n c h e u r s u r ENSEIGNANT

c r ea te or r e p l a c e t r i g g e r TRIG ENSEIGNANTb e f o r e i n s e r t or update o f NUMERO on ENSEIGNANTf o r each rowd e c l a r e

num number ;b e g i n

s e l e c t NUMERO INTO numfrom BIATOS where NUMERO = : new .NUMERO;r a i s e a p p l i c a t i o n e r r o r (−20001 , ’ Le p e r s o n n e l ’ | |

t o c h a r (num ) | | ’ e s t d e j a BIATOS ! ! ! ’ ) ;except i on

when n o d a t a f o u n d then n u l l ;end ;/

49/193


REM ∗∗∗ D e c l e n c h e u r s u r BIATOS

c r ea te or r e p l a c e t r i g g e r TRIG BIATOSb e f o r e i n s e r t or update o f NUMERO on BIATOSf o r each rowd e c l a r e

num number ;b e g i n

s e l e c t NUMERO INTO numfrom ENSEIGNANT where NUMERO = : new .NUMERO;r a i s e a p p l i c a t i o n e r r o r (−20001 , ’ Le p e r s o n n e l ’ | |

t o c h a r (num ) | | ’ e s t d e j a e n s e i g n a n t ! ! ! ’ ) ;except i on

when n o d a t a f o u n d then n u l l ;end ;/

50/193


Implementation de la contrainte B :

procedures cataloguees (Insertion, Suppression)

declencheurs (Modification)

REM ∗∗∗ Ajout d ’ un E n s e i g n a n t

c r e a t e or r e p l a c e p r o c e d u r e AJOUT ENSEIGNANT(NUM number , NOM v a r c h a r , PREN v a r c h a r , DNAIS date ,

SEXE v a r c h a r , ECHEL number , IND number , SPEC v a r c h a r ) i sb e g i n

i n s e r t i n t o PERSONNEL v a l u e s (NUM, NOM, PREN, DNAIS , SEXE ) ;i n s e r t i n t o ENSEIGNANT v a l u e s

(NUM, ECHEL , IND , SPEC ) ;end ;/

51/193


REM ∗∗∗ Ajout d ’ un BIATOS

c r e a t e or r e p l a c e p r o c e d u r e AJOUT BIATOS(NUM number , NOM v a r c h a r , PREN v a r c h a r , DNAIS date ,

SEXE v a r c h a r , DEMB date , SERV v a r c h a r ) i sb e g i n

i n s e r t i n t o PERSONNEL v a l u e s (NUM, NOM, PREN, DNAIS , SEXE ) ;i n s e r t i n t o BIATOS v a l u e s (NUM, DEMB, SERV ) ;

end ;/

52/193


REM ∗∗∗ S u p p r e s s i o n d ’ un E n s e i g n a n t

c r e a t e or r e p l a c e p r o c e d u r e SUPPR ENSEIGNANT(NUM number ) i sb e g i n

d e l e t e from ENSEIGNANT where NUMERO = num ;d e l e t e from PERSONNEL where NUMERO = num ;

end ;/

REM ∗∗∗ S u p p r e s s i o n d ’ un BIATOS

c r e a t e or r e p l a c e p r o c e d u r e SUPPR BIATOS(NUM number ) i sb e g i n

d e l e t e from BIATOS where NUMERO = num ;d e l e t e from PERSONNEL where NUMERO = num ;

end ;/

53/193


REM ∗∗∗ D e c l e n c h e u r pour l a r e p e r c u s s i o n de l a m o d i f i c a t i o nREM ∗∗∗ du numero du PERSONNEL v e r s ENSEIGNANT e t BIATOS

c r ea te or r e p l a c e t r i g g e r TRIG ENSBIATOSb e f o r e update o f NUMERO on PERSONNELf o r each rowb e g i n

b e g i nupdate ENSEIGNANTs e t NUMERO = : new .NUMEROwhere NUMERO = : o l d .NUMERO;

except i onwhen n o d a t a f o u n d then n u l l ;

end ;update BIATOS

s e t NUMERO = : new .NUMEROwhere NUMERO = : o l d .NUMERO;

except i onwhen n o d a t a f o u n d then n u l l ;

end ;/

54/193

Traduction des contraintes d’heritageUtilisation

REM ∗∗∗∗∗∗ I n s e r t i o n s des donn ees s o u s SQLPLUSREM ∗∗∗∗∗∗ Lancement des p r o c e d u r e s

s e l e c t ’ I n s e r t i o n d e s d o n n e e s ’ from d u a l ;PAUSE

execute AJOUT ENSEIGNANT ( 1 , ’TRAIFOR ’ , ’ Cl ement ’ ,’ 17−09−1958 ’ , ’M’ , 6 , 780 , ’BD ’ ) ;

execute AJOUT ENSEIGNANT ( 2 , ’TRAIFOR ’ , ’ C l e m e n t i n e ’ ,’ 22−11−1969 ’ , ’ F ’ , 6 , 780 , ’ IA ’ ) ;

execute AJOUT BIATOS ( 3 , ’FAITOUT ’ , ’ A lex ’ ,’ 16−10−1960 ’ , ’M’ , ’ 01−01−2002 ’ , ’ Commercia l ’ ) ;

PAUSE

s e l e c t ∗ from PERSONNEL ;s e l e c t ∗ from ENSEIGNANT ;s e l e c t ∗ from BIATOS ;PAUSE

55/193


→ Contrainte de totalite

Contraintes d’heritage :

(Contrainte B) Il n’existe pas de personnel ni enseignant ni BIATOS

(Contrainte C) Il peut exister un personnel a la fois enseignant etBIATOS

Implementation :

Contrainte B : voir ci-dessus

Contrainte C : equivaut a ne pas programmer la contrainte Aprecedente

⇒ Pas de mise en œuvre les declencheurs des tables ENSEIGNANT etBIATOS : TRIG_ENSEIGNANT, TRIG_BIATOS

56/193


→ Contrainte d’exclusion


(Contrainte A) Il n’existe pas de personnel a la fois enseignant etBIATOS

(Contrainte D) Il peut exister un personnel ni enseignant ni BIATOS

Implementation :

Contrainte A: voir ci-dessus

Contrainte D : equivaut a ne pas programmer la contrainte Bprecedente

⇒pas de mise en œuvre les quatre procedures (ajout et suppression)et le declencheur TRIG_ENSBIATOS

57/193


→ Sans Contrainte

Aucune contrainte n’est a programmer !

58/193

Traduction des contraintes d’heritageDecomposition descendante




Contrainte de partition ? → aucun personnel ne peut etre a lafois enseignant et iatos et il n’existe pas non plus un personneln’etant ni enseignant ni iatos

Contrainte de totalite ?

Contrainte d’exclusion ? → il faudrait la table personnel pourles personnels non enseignant et non BIATOS

Sans contrainte !

59/193

Traduction des contraintes d’heritageDecomposition ascendante

→ Contrainte de partition

Contrainte de totalite

Contrainte d’exclusion

Sans Contrainte


(Contrainte A) Il n’existe pas de personnel a la fois enseignantet BIATOS


60/193


Implementation des contraintes A et B :

au niveau de la table personnel

a l’aide des contraintes de type CHECK

Contrainte A :Verifier que les colonnes ECHELON, INDICE, SPECIALITE,DATEEMBAUCHE et SERVICE ne soient pas toutes initialisees

Contrainte B :Verifier que les colonnes ECHELON, INDICE, SPECIALITE,DATEEMBAUCHE et SERVICE ne soient pas toutes nulles

61/193


REM ∗∗∗∗ CONTRAINTE A

a l t e r t a b l e PERSONNELadd c o n s t r a i n t CK CONTRAINTE A

check ((ECHELON i s n u l l and INDICE i s n u l l and

SPECIALITE i s n u l l )or (DATEEMBAUCHE i s n u l l and SERVICE i s n u l l )

) ;

REM ∗∗∗∗ CONTRAINTE B

a l t e r t a b l e PERSONNELadd c o n s t r a i n t CK CONTRAINTE B

check ((ECHELON i s not n u l l or INDICE i s not n u l l or

SPECIALITE i s not n u l l )or (DATEEMBAUCHE i s not n u l l or SERVICE i s not n u l l )

) ;

62/193


→ Contrainte de totaliteContraintes d’heritage :


(Contrainte C) Il peut exister un personnel a la fois enseignantet BIATOS

63/193



Contrainte B : voir ci-dessus

Contrainte C : Suppression ou desactiver la contrainte Aprecedente (DROP CONSTRAINT ouDISABLE CONSTRAINT)

DROP CONSTRAINT : en cas de reactivation de lacontrainte, il est necessaire de la recreer(ADD CONSTRAINT)DISABLE CONSTRAINT : en cas de reactivation de lacontrainte, il faut simplement la reactiver avec la requeteENABLE CONSTRAINT

64/193



REM La c o n t r a i n t e C r e v i e n t a f a i r e l aREM D e s a c t i v a t i o n de l a CONTRAINTE A

a l t e r t a b l e PERSONNELd i s a b l e c o n s t r a i n t CK CONTRAINTE A ;

65/193



Contrainte d’heritage :

Contrainte A→ Reactivation de la contrainte A en supprimantau prealable les tuples ne repondant pas a cette contrainte

Non-contrainte B → Desactivation de la contrainte B

66/193



REM ∗∗∗∗∗ R e a c t i v a t i o n de l a CONTRAINTE Aa l t e r t a b l e PERSONNEL

e n a b l e c o n s t r a i n t CK CONTRAINTE A ;

REM ∗∗∗∗∗ D e s a c t i v a t i o n de l a CONTRAINTE Ba l t e r t a b l e PERSONNEL

d i s a b l e c o n s t r a i n t CK CONTRAINTE B ;

67/193


→ Sans Contrainte

Aucune contrainte de type CHECK n’est a programmer !

68/193

Recursivite

Sous Oracle,

possibilite de representation et de manipulation

de donnees ayant une structure de donnees recursive (arbre)

par une representation tabulaire et avec la commande SQL :

SELECT . . . FROM . . .WHERE . . .CONNECT BY PRIOR c o l o n n e o p e r a t e u r c o l o n n e[CONNECT BY c o l o n n e o p e r a t e u r PRIOR c o l o n n e ]START WITH . . .LEVEL . . .

69/193

Exemples de parcours destructure de donnees de type recursif

Structures de donnees recursives de type ArbresExemples :

Arbre Genealogique

Nomenclature d’un produit (Compose, Composant)

Oracle offre la commande CONNECT BY

SELECT . . . FROM . . .WHERE . . .CONNECT BY PRIOR c o l o n n e o p e r a t e u r c o l o n n eCONNECT BY c o l o n n e o p e r a t e u r PRIOR c o l o n n e

START WITHLEVEL

70/193

Structures hierarchiques, arborescentesArbre Genealogique

Oracle permet la representation et la manipulation des donneesayant une structure arborescente par le modele relationnel

c r ea te t ab l e PERSONNES (NUMERO number ( 7 ) , NOM va rcha r ( 1 5 ) , PRENOM va rcha r ( 1 5 ) ,DATENAISSANCE date , SEXE char ( 1 ) , PERE number ( 7 ) , MERE number ( 7 ) ,c o n s t r a i n t PK PERSONNES pr imary key (NUMERO) ,c o n s t r a i n t FK PERSONNES PERE PERSONNES f o r e i g n key (PERE) r e f e r e n c e s PERSONNES,c o n s t r a i n t FK PERSONNES MERE PERSONNES f o r e i g n key (MERE) r e f e r e n c e s PERSONNES,c o n s t r a i n t CK SEXE PERSONNES check (SEXE i n ( ’M’ , ’ F ’ ) ) ) ;

71/193

Exemple de requete recursive

Recherche des ancetres d’une personne

s e l e c t d i s t i n c t NUMERO, NOM, PRENOM,DATENAISSANCE from PERSONNES

connect by p r i o r PERE=NUMERO orp r i o r MERE=NUMERO

s t a r t w i t h NUMERO=&num ;

72/193

Conclusion / Bilan

Aucune des solutions ne constitue la panacee.

Il faut mesurer les performances des requetes.

Voir aussi le type de requetes

73/193

Transformation des associations d’heritage en SQL 3

Heritage de types

Existe depuis la version 9.1 d’Oracle (novembre 2001)Uniquement heritage de typePas d’heritage multiple

Un type peut heriter d’un seul autre type (sur-type)Un sur-type peut permettre de definir plusieurs sous-typesChaque sous-type est specialise par rapport au sur-type qui estdit plus general

Mecanisme d’heritage automatiquement repercute au niveaudes tables objet a par du moment ou les types definissant lestables sont issus eux-memes d’une hierarchie d’heritage

Heritage de tables ?

74/193

Heritage de types

Definition d’un personnel a l’Universite

−−− ∗∗∗ C r e a t i o n du t y p e de l a sur−c l a s s ec r ea te t y p e PERSONNEL TYPE AS OBJECT(

NUMERO number ( 7 ) ,NOM va rcha r ( 1 0 ) ,PRENOM va rcha r ( 1 0 ) ,DATENAISSANCE date ,SEXE char ( 1 )

)NOT FINAL /∗ peut i n c l u r e des s o u s c l a s s e s ∗//

75/193

Heritage de types

Definition d’un enseignant

−−− ∗∗∗ C r e a t i o n du t y p e de l a sous−c l a s s ec r ea te t y p e ENSEIGNANT TYPE UNDER PERSONNEL TYPE(

ECHELON number ( 2 ) ,INDICE number ( 5 ) ,SPECIALITE va rcha r ( 2 0 )

)FINAL/

76/193

Creation des tables objet et contraintes

Creation de tables objet en fonctions des types precedemmentdefinis

Aucune directive ne precise l’heritage : il est induit par lahierarchie de type existante

−−− ∗∗∗ P e r s o n n e l de l ’ u n i v e r s i t ec r ea te t ab l e PERSONNEL OF PERSONNEL TYPE(

c o n s t r a i n t PK PERSONNEL pr imary key (NUMERO) ,c o n s t r a i n t CK SEXE PERSONNEL check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;−−− ∗∗∗ P e r s o n n e l e n s e i g n a n tc r ea te t ab l e ENSEIGNANT OF ENSEIGNANT TYPE ;

IMPORTANT : les contraintes ne sont definies que dans la tablepersonnel

77/193

Creation des tables objet et contraintesIllustration

NB : Les contraintes ne sont definies que dans la table personnel

→ On herite d’un type

Insertion des donnees dans la table personnel :

i n s e r t i n t o p e r s o n n e l va l u e s ( 1 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ ) ;i n s e r t i n t o p e r s o n n e l va l u e s ( 1 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ ) ;∗ERREUR a l a l i g n e 1 :

ORA−00001: v i o l a t i o n de c o n t r a i n t e unique (FB . PK PERSONNEL)

s e l e c t ∗ from p e r s o n n e l ;NUMERO NOM PRENOM DATENAISSA S−−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −−−−−−−−−− −

1 B F 17−09−2004 M

78/193

Creation des tables objet et contraintesIllustration

NB : Les contraintes ne sont definies que dans la table personnel

→ On herite d’un type

Insertion des donnees dans la table enseignant :

i n s e r t i n t o e n s e i g n a n t va l u e s ( 7 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ; 1 l i g n e c r e e e .i n s e r t i n t o e n s e i g n a n t va l u e s ( 7 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ; 1 l i g n e c r e e e . ! ! !i n s e r t i n t o e n s e i g n a n t va l u e s ( 8 , ’B ’ , ’D ’ , ’ 17−10−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ; 1 l i g n e c r e e e .

s e l e c t ∗ from e n s e i g n a n t ;

NUMERO NOM PRENO DATENAISSA S ECHELON INDICE SPECIALITE−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

7 B F 17−09−2004 M 2 780 BD7 B F 17−09−2004 M 2 780 BD8 B D 17−10−2004 M 2 780 BD

79/193

Creation des tables objet et contraintes (2)

Ci-dessous :

Creation des tables objet en fonctions des typesprecedemment definis

Definition des contraintes au niveau des tables

−−− ∗∗∗ P e r s o n n e l de l ’ u n i v e r s i t ec r ea te t ab l e PERSONNEL OF PERSONNEL TYPE(

c o n s t r a i n t PK PERSONNEL pr imary key (NUMERO) ,c o n s t r a i n t CK SEXE PERSONNEL check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;−−− ∗∗∗ P e r s o n n e l e n s e i g n a n t

80/193

Transformation des associations d’heritage en SQL 3

c r ea te t ab l e ENSEIGNANT OF ENSEIGNANT TYPE(

c o n s t r a i n t PK ENSEIGNANT pr imary key (NUMERO) ,c o n s t r a i n t CK SEXE ENSEIGNANT check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;

ATTENTION :

Definition des contraintes, aussi dans la table enseignant

Heritage d’un type

81/193

Creation des tables objet et contraintes (2)illustrations

Les contraintes doivent etre definies aussi dans la tableenseignant : heritage d’un type

SQL> i n s e r t i n t o e n s e i g n a n t va l u e s ( 7 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ; 1 l i g n e c r e e e .SQL> i n s e r t i n t o e n s e i g n a n t va l u e s ( 7 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ;i n s e r t i n t o e n s e i g n a n t va l u e s ( 7 , ’B ’ , ’ F ’ , ’ 17−09−2004 ’ , ’M’ , 2 , 780 , ’BD ’ )∗ ERREUR a l a l i g n e 1 : ORA−00001: v i o l a t i o n de c o n t r a i n t e unique (FB . PK ENSEIGNANT)

SQL> i n s e r t i n t o e n s e i g n a n t va l u e s ( 8 , ’B ’ , ’D ’ , ’ 17−10−2004 ’ , ’M’ , 2 , 780 , ’BD ’ ) ; 1 l i g n e c r e e e .SQL> i n s e r t i n t o e n s e i g n a n t va l u e s ( 9 , ’B ’ , ’D ’ , ’ 17−10−2004 ’ , ’K ’ , 2 , 780 , ’BD ’ ) ;i n s e r t i n t o e n s e i g n a n t va l u e s ( 9 , ’B ’ , ’D ’ , ’ 17−10−2004 ’ , ’K ’ , 2 , 780 , ’BD ’ )∗ ERREUR a l a l i g n e 1 : ORA−02290: v i o l a t i o n de c o n t r a i n t e s (FB . CK SEXE ENSEIGNANT)

de v e r i f i c a t i o n

SQL> s e l e c t ∗ from e n s e i g n a n t ;NUMERO NOM PRENO DATENAISSA S ECHELON INDICE SPECIALITE−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

7 B F 17−09−2004 M 2 780 BD8 B D 17−10−2004 M 2 780 BD

82/193

Transformation des associations d’heritage en SQL 3Heritage de tables : A venir

REM ∗∗∗ Un p e r s o n n e l a l ’ U n i v e r s i t ec r e a t e t a b l e PERSONNEL(

NUMERO number ( 7 ) , NOM v a r c h a r ( 1 0 ) ,PRENOM v a r c h a r ( 1 0 ) , DATENAISSANCE date ,SEXE c h a r ( 1 ) ,c o n s t r a i n t PK PERSONNEL p r i m a r y key (NUMERO) ,c o n s t r a i n t CK SEXE PERSONNEL check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;

83/193

Transformation des associations d’heritage en SQL 3Heritage de tables : A venir

REM ∗∗∗ P e r s o n n e l e n s e i g n a n tc r ea te t ab l e ENSEIGNANT under PERSONNEL(

ECHELON number ( 2 ) ,INDICE number ( 5 ) ,SPECIALITE va rcha r ( 2 0 )

) ;

REM ∗∗∗ P e r s o n n e l b i a t o sc r ea te t ab l e BIATOS under PERSONNEL(

DATEEMBAUCHE date ,SERVICE va rcha r ( 2 0 )

) ;

84/193


85/193

Traduction des associations d’agregationREM ∗∗∗ Un co−p r o p r i e t a i r e peut p o s s e d e r p l u s i e u r s immeublesc r ea te t ab l e COPROPRIETAIRE(

NUMCO number ( 7 ) ,NOMCO va rcha r ( 1 0 ) ,TELCO va rcha r ( 1 5 ) ,ADRCO va rcha r ( 5 0 ) ,c o n s t r a i n t PK COPROPRIETAIRE pr imary key (NUMCO)

) ;

REM ∗∗∗ Un immeuble d o i t e t r e p o s s e d e par un ouREM p l u s i e u r s c o p r o p r i e t a i r e sc r ea te t ab l e IMMEUBLE(

NUMIMM number ( 7 ) ,ADRIMM va rcha r ( 5 0 ) ,c o n s t r a i n t PK IMMEUBLE pr imary key (NUMIMM)

) ;

86/193


c r ea te t ab l e DEPENSE(

NUMCO number ( 7 ) ,NUMIMM number ( 7 ) ,DATEDEP date ,MTTDEP number ( 1 0 , 2 ) ,LIBDEP va rcha r ( 5 0 ) ,c o n s t r a i n t PK DEPENSE pr imary key (NUMCO,NUMIMM) ,c o n s t r a i n t FK DEPENSE NUMCO COPROPR f o r e i g n key (NUMCO)

r e f e r e n c e s COPROPRIETAIRE(NUMCO) on de l e t e cascade ,c o n s t r a i n t FK DEPENSE NUMIMM IMMEUBLE f o r e i g n key (NUMIMM)

r e f e r e n c e s IMMEUBLE(NUMIMM ) on de l e t e cascade) ;

→ La cardinalite minimale de l’association depenser pourra etretestee par l’intermediaire d’une procedure PL/SQL

87/193

Traduction des contraintes d’integrite fonctionnelles

Contraintes : Partition, Exclusion, Totalite, Simultaneite, Inclusion...Toutes les contraintes peuvent etre definies ou programmees via :

la declaration de contraintes (constraints)

la programmation de

fonctions (functions)procedures (procedures)paquetages (packages)declencheurs (triggers)

en PL/SQL ou avec un langage hote tel que le C, C++, Java

88/193

Contrainte d’inclusion

c r ea te t ab l e STAGE(

NUMEROS number ( 7 ) ,NOMENTREPRISE va rcha r ( 4 0 ) ,TELENT va rcha r ( 1 5 ) ,ADRENT va rcha r ( 5 0 ) ,c o n s t r a i n t PK STAGE pr imary key (NUMEROS)

) ;

89/193

Contrainte d’inclusionUne table par classe

c r ea te t ab l e ETUDIANT(

NUMEROE number ( 7 ) ,NOM va rcha r ( 1 0 ) ,PRENOM va rcha r ( 1 0 ) ,DATENAISSANCE date ,SEXE char ( 1 ) ,NUMEROS number ( 7 ) ,c o n s t r a i n t PK ETUDIANT pr imary key (NUMEROE) ,c o n s t r a i n t FK ETUDIANT NUMEROS STAGE f o r e i g n key (NUMEROS)

r e f e r e n c e s STAGE(NUMEROS) ,c o n s t r a i n t CK ETUDIANT SEXE check (SEXE i n ( ’M’ , ’ F ’ ) )

) ;

90/193

Contrainte d’inclusion

c r ea te t ab l e VOEUX(

NUMEROE number ( 7 ) ,NUMEROS number ( 7 ) ,c o n s t r a i n t PK VOEUX pr imary key (NUMEROE,NUMEROS) ,c o n s t r a i n t FK VOEUX NUMEROE ETUDIANT f o r e i g n key (NUMEROE)

r e f e r e n c e s ETUDIANT (NUMEROE) ,c o n s t r a i n t FK VOEUX NUMEROS STAGE f o r e i g n key (NUMEROS)

r e f e r e n c e s STAGE(NUMEROS)) ;

a l t e r t a b l e ETUDIANT addc o n s t r a i n t FK EFFECTUER INCLUSION VOEUXf o r e i g n key (NUMEROE,NUMEROS)referencesVOEUX (NUMEROE,NUMEROS) ;

91/193

Contrainte d’inclusionContrainte d’inclusion : Le logiciel doit etre installe sur un serveurdu departement qui a achete le programme.

Un logiciel L achete par le departement D est installe sur unserveur S, destine entre autres, a ce departement

92/193

Contrainte d’inclusionUne table par Classe

c r ea te t ab l e DEPARTEMENT(

NUMDEP number ( 7 ) ,NOMDEP va rcha r ( 1 0 ) ,SPECIALITE va rcha r ( 2 0 ) ,c o n s t r a i n t PK DEPARTEMENT pr imary key (NUMDEP)

) ;

c r ea te t ab l e LOGICIEL(

NUMLOG number ( 7 ) ,NOMLOG va rcha r ( 1 0 ) ,VERSIONLOG va rcha r ( 1 0 ) ,c o n s t r a i n t PK LOGICIEL pr imary key (NUMLOG)

) ;

93/193

Contrainte d’inclusionUne table par Classe

c r ea te t ab l e SERVEUR(

NUMSERV number ( 7 ) ,NOMSERV va rcha r ( 1 0 ) ,TYPESERV va rcha r ( 1 0 ) ,c o n s t r a i n t PK SERVEUR pr imary key (NUMSERV)

) ;

94/193

Contrainte d’inclusionUne table par Association ou par Classe-Association

c r ea te t ab l e ACHETER (NUMDEP number ( 7 ) , NUMLOG number ( 7 ) , DATEACHAT date ,c o n s t r a i n t PK ACHETER pr imary key (NUMDEP,NUMLOG) ,c o n s t r a i n t FK ACHETER NUMDEP DEPARTEMENT

f o r e i g n key (NUMDEP) r e f e r e n c e s DEPARTEMENT(NUMDEP) ,c o n s t r a i n t FK ACHETER NUMLOG LOGICIEL

f o r e i g n key (NUMLOG) r e f e r e n c e s LOGICIEL (NUMLOG)) ;

c r ea te t ab l e UTILISER (NUMDEP number ( 7 ) , NUMSERV number ( 7 ) ,c o n s t r a i n t PK UTILISER pr imary key (NUMDEP,NUMSERV) ,c o n s t r a i n t FK UTILISER NUMDEP DEPARTEMENT

f o r e i g n key (NUMDEP) r e f e r e n c e s DEPARTEMENT(NUMDEP) ,c o n s t r a i n t FK UTILISER NUMSERV SERVEUR

f o r e i g n key (NUMSERV) r e f e r e n c e s SERVEUR(NUMSERV)) ;

95/193

Contrainte d’inclusionUne table par Association ou par Classe-Association

c r ea te t ab l e INSTALLER (NUMLOG number ( 7 ) , NUMSERV number ( 7 ) ,c o n s t r a i n t PK INSTALLER pr imary key (NUMLOG,NUMSERV) ,c o n s t r a i n t FK INSTALLER NUMLOG LOGICIEL

f o r e i g n key (NUMLOG) r e f e r e n c e s LOGICIEL (NUMLOG) ,c o n s t r a i n t FK INSTALLER NUMSERV SERVEUR

f o r e i g n key (NUMSERV) r e f e r e n c e s ERVEUR(NUMSERV) ) ;

96/193

Contrainte d’InclusionDeclencheur

Un logiciel L achete par le departement D est installe sur un serveur S, destine entre

autres, a ce departement

c r ea te or r e p l a c e t r i g g e r t r i g c o n t r a i n t e i n c l u s i o nb e f o r e i n s e r t on INSTALLERf o r each rowd e c l a r eLOGIC number ( 7 ) ;SERV number ( 7 ) ;b e g i n

s e l e c t ACHETER.NUMLOG, UTILISER .NUMSERV i n t o LOGIC , SERVfrom ACHETER, UTILISERwhere ACHETER.NUMDEP = UTILISER .NUMDEP and

ACHETER.NUMLOG = : new .NUMLOG andUTILISER .NUMSERV = : new .NUMSERV;

except i onwhen n o d a t a f o u n d thenr a i s e a p p l i c a t i o n e r r o r (−20100 ,

’ Le l o g i c i e l d o i t e t r e i n s t a l l e s u run s e r v e u r du d epar tement a c h e t e u r ’ ) ;

end ;/

97/193

Programmation Objet – SQL 3

Objet-relationnel – Objet

Passage UML → Objet / Objet relationnel

98/193

Schema relationnel / SQL2

Schema relationnel :

COURS ( NUM COURS, NOMC, NBHEURES, ANNEE )

PROFESSEURS ( NUM PROF, NOMP, SPECIALITE , DATE ENTREE ,DER PROM, SALAIRE BASE , SALAIRE ACTUEL

)

CHARGE( NUM PROF∗ , NUM COURS∗ )

99/193

Schema relationnel / SQL2SQL2 :c r ea te t ab l e COURS( NUM COURS NUMBER( 2 ) NOT NULL ,

NOMC VARCHAR( 2 0 ) NOT NULL ,NBHEURES NUMBER( 2 ) ,ANNE NUMBER( 1 ) ,c o n s t r a i n t PK COURS pr imary key (NUM COURS)

) ;

c r ea te t ab l e PROFESSEURS( NUM PROF NUMBER( 4 ) NOT NULL ,

NOMP VARCHAR2( 2 5 ) NOT NULL ,SPECIALITE VARCHAR2( 2 0 ) ,DATE ENTREE DATE,DER PROM DATE,SALAIRE BASE NUMBER,SALAIRE ACTUEL NUMBER,c o n s t r a i n t PK PROFESSEURS pr imary key (NUM PROF)

) ;

c r ea te t ab l e CHARGE( NUM PROF NUMBER( 4 ) NOT NULL ,

NUM COURS NUMBER( 4 ) NOT NULL ,c o n s t r a i n t PK CHARGE pr imary key (NUM COURS,

NUM PROF)) ;

100/193

Schema relationnel / SQL2

a l t e r t a b l e CHARGEadd c o n s t r a i n t FK CHARGE COURS

f o r e i g n key (NUM COURS)r e f e r e n c e s COURS (NUM COURS ) ;

a l t e r t a b l e CHARGEadd c o n s t r a i n t FK CHARGE PROFESSEUR

f o r e i g n key (NUM PROF)r e f e r e n c e s PROFESSEURS (NUM PROF ) ;

101/193

Schema relationnel-objet / SQL3

Schema relationnel-objet

COURS ( NUM COURS, NOMC, NBHEURES, ANNEE )

PROFESSEURS (NUM PROF, NOMP, SPECIALITE , DATE ENTREE ,DER PROM, SALAIRE BASE , SALAIRE ACTUEL ,EnsembleDe (COURS)

)

102/193

Schema relationnel-objet / SQL3SQL3 :c r ea te t y p e c o u r s t y p e as o b j e c t( num cours number ( 2 ) , nomc va rcha r2 ( 2 0 ) ,

n b h e u r e s number ( 2 ) , annee number ( 1 ) )/

c r ea te t y p e l e s c o u r s t y p e as t ab l e o f c o u r s t y p e/

c r ea te t y p e p r o f e s s e u r t y p e as o b j e c t( num prof number ( 4 ) , nom va rcha r2 ( 2 5 ) ,

s p e c i a l i t e va rcha r2 ( 2 0 ) , c o u r s l e s c o u r s t y p e . . . )/

c r ea te t ab l e p r o f e s s e u r o f p r o f e s s e u r t y p e( pr imary key ( num prof ) )n e s t e d t a b l e c o u r s s t o r e as tabemp/

103/193

Types ObjetType de donnees

Principaux type de donnees Oracle :

104/193

Types ObjetPersistance

Sous Oracle, 3 categories d’objets :

Objets colonne (column objects) : stockes en tant quecolonne structuree dans une table relationnelle ;Objets ligne (row objects) : stockes en tant que ligne d’unetable objet.

possedent un identificateur unique appele OID (ObjectIdentifier)peuvent etre indexes et partitionnes

Objets non persistants : non stockes

ni dans une colonne d’une table relationnelleni dans une ligne d’une table objet

Ces objets n’existent que durant l’execution d’un programmePL/SQL

105/193

Types Objet

Definition de chaque objet a partir d’un type decrivant

une structure de donnees se positionnant dans une hierarchied’heritagedes methodes

Utilisation d’un type :

Construire d’autres typesDefinir une ou plusieurs tables objetDefinir une colonne d’une table relationnelleConstruire des vues objet

106/193

Types ObjetCreation d’un type

Creation

CREATE [OR REPLACE TYPE] schema . nomType[AS OBJECT | UNDER schema . nomSurType ]

(REM ∗∗∗ d e f i n i t i o n de l a s t r u c t u r ec o l o n n e 1 type1 , c o l o n n e 2 type2 , . . . ,

REM ∗∗∗ d e f i n i t i o n du comportementmethode1 ( p a r a m e t r e s 1 ) , methode2 ( p a r a m e t r e s 2 ) . . .

)[ [NOT] INSTANTIABLE ]

REM ∗∗∗ p o s i t i o n n e m e n t dans l e graphe d ’ h e r i t a g e[ [ NOT] FINAL ]/

107/193

Creation d’un typeDirective FINAL

Directives FINAL et NOT FINAL : positionnement d’un typedans le graphe d’heritage

Directive NOT final : a appliquer aux types generiques

Par defaut, tout type est FINALUn type FINAL ne peut servir a definir des sous-types

108/193

Creation d’un typeDirective FINAL – Exemples

CREATE TYPE a d r e s s e t AS OBJECT (nrue NUMBER( 3 ) , r u e VARCHAR( 4 0 ) , v i l l e VARCHAR( 3 0 )

/

CREATE TYPE P e r s o n n e l t AS OBJECT(nom VARCHAR ( 1 0 ) , prenom VARCHAR( 1 0 ) , a d r e s s e a d r e s s e t ) )NOT FINAL

/

CREATE TYPE E n s e i g n a n t t UNDER P e r s o n n e l t (Eche lon NUMBER, i n d i c e NUMBER)FINAL

109/193

Creation d’un typeDirective INSTANTIABLE

Directives INSTANTIABLE et NOT INSTANTIABLE : capacited’instancitation d’un typeTous les types crees sont par defaut INSTANTIABLENOT INSTANTIABLE : similaire a la notion de classe abstraiteChaque type possede

un constructeur permettant de creer des objets (persistants ounon) a l’aide de la commande NEW ou au sein d’un commandeINSERT

un constructeur (par defaut) et plusieurs dans le cas desurcharge

Un type NOT INSTANTIABLE ne peut pas etre FINAL

Un sous-type NOT INSTANTIABLE peut heriter d’un typeINSTANTIABLE

110/193

Creation d’un typeDirective INSTANTIABLE -- Exemples

CREATE TYPE P e r s o n n e l t AS OBJECT(nom VARCHAR ( 1 0 ) , prenom VARCHAR( 1 0 ) , a d r e s s e a d r e s s e t ) )NOT INSTANTIABLE NOT FINAL

/

CREATE TYPE E n s e i g n a n t t UNDER P e r s o n n e l t (Eche lon NUMBER, i n d i c e NUMBER)INSTANTIABLE FINAL

/

111/193

Types ObjetSuppression d’un type

DROP TYPE nomType [ FORCE | VALIDATE ] ;

Directives :

FORCE : suppression du type meme s’il y a des objets de cetype dans une baseOracle marque les colonnes dependant de ce type, UNUSED, etelles deviennent inaccessibles (non recommande)

VALIDATE : Verification si les instances du type a supprimerpeuvent etre substitues par un sur-type.

Exemple :

DROP TYPE P e r s o n n e l t FORCE

112/193


Specification de l’objetCREATE TYPE Bank Account AS OBJECT (

acct number INTEGER ( 5 ) ,b a l a n c e REAL ,s t a t u s VARCHAR2( 1 0 ) ,

MEMBER PROCEDURE open( amount IN REAL ) ,

MEMBER PROCEDURE v e r i f y a c c t(num IN INTEGER ) ,

MEMBER PROCEDURE c l o s e(num IN INTEGER , amount OUT REAL)

) ;

CREATE TYPE BODY Bank Account AS. . .

END ;

113/193


Definition des methodes associees a l’objet

CREATE TYPE BODY Bank Account AS

MEMBER PROCEDURE open ( amount IN REAL) I SBEGIN −− open account w i t h i n i t i a l d e p o s i t

IF NOT amount > 0 THENRAISE APPLICATION ERROR(−20104 , ’ bad amount ’ ) ;

END IF ;−− SELECT a c c t s e q u e n c e . NEXTVAL INTO acct number FROM d u a l ;s t a t u s := ’ open ’ ;b a l a n c e := amount ;

END open ;

114/193


MEMBER PROCEDURE v e r i f y a c c t (num IN INTEGER) I SBEGIN −− check f o r wrong account number o r c l o s e d account

IF (num <> acct number ) THENRAISE APPLICATION ERROR(−20105 , ’ wrong number ’ ) ;

ELSIF ( s t a t u s = ’ c l o s e d ’ ) THENRAISE APPLICATION ERROR(−20106 , ’ account c l o s e d ’ ) ;

END IF ;END v e r i f y a c c t ;

MEMBER PROCEDURE c l o s e (num IN INTEGER , amount OUT REAL) I SBEGIN −− c l o s e account and r e t u r n b a l a n c e

v e r i f y a c c t (num ) ;s t a t u s := ’ c l o s e d ’ ;amount := b a l a n c e ;

END c l o s e ;END ;

115/193

Types ObjetExtraction de la description d’un type

Definition de nouvelles vues du DD pour prendre en compte lestypesExemple :

c r ea te t y p e emp type as o b j e c t ( n i n s e e va rcha r2 ( 1 3 ) ,age number , nom va rcha r2 ( 3 0 ) )

/

Description de la structure du 1er niveau d’un type :

SQL> DESC emp type

116/193

Types ObjetExtraction de la description d’un type

Exemples de vues : (USER_..., DBA_..., ALL_...)

Description :

des collections : USER_COLL_TYPES

des index sur les types : USER_INDEXTYPES

des types d’une maniere generale : USER_TYPES

des attributs des types : USER_TYPE_ATTRS

des methodes des types : USER_TYPE_METHODS

des versions des types : USER_TYPE_VERSIONS

117/193

Passage a l’objetTables relationnelles

−− Table : MAGASINS2 SQL2c r ea te t ab l e MAGASINS2( NUMMAG INTEGER ,

NOMMAG CHAR( 3 0 ) ,TELMAG CHAR( 1 5 ) ,ADRNUMMAG VARCHAR2( 1 0 ) ,ADRRUEMAG VARCHAR2( 5 0 ) ,ADRCPMAG VARCHAR2( 1 0 ) ,ADRVILLEMAG VARCHAR2( 5 0 ) ,ADRPAYSMAG VARCHAR2( 5 0 ) ,c o n s t r a i n t PK MAGASINS2

pr imary key (NUMMAG) ) ;

−− Table : CLIENTS2 SQL2c r ea te t ab l e CLIENTS2( NUMCLI INTEGER ,

NOMCLI CHAR( 2 0 ) ,TELCLI CHAR( 1 5 ) ,ADRNUMCLI VARCHAR2( 1 0 ) ,ADRRUECLI VARCHAR2( 5 0 ) ,ADRCPCLI VARCHAR2( 1 0 ) ,ADRVILLECLI VARCHAR2( 5 0 ) ,ADRPAYSCLI VARCHAR2( 5 0 ) ,c o n s t r a i n t PK CLIENTS2

pr imary key (NUMCLI ) ) ;

i n s e r t i n t o MAGASINS2 va l u e s ( 1 , ’FB ’ , ’ 0145454545 ’ , ’ 13 ’ , ’ Avenue de l a p a i x ’ ,’ 75015 ’ , ’ P a r i s ’ , ’ France ’ ) ;

NUMMAG NOMMAG TELMAG ADRNU ADRRUEMAG ADRCP ADRVILLEMA ADRPAYSMAG1 FB 0145454545 13 Avenue de l a p a i x 75015 P a r i s France2 FB 0155555555 20 Avenue de l a l i b e r t e 06100 Nice France3 FB 0155555555 10 Avenue des Amis 6050 B r u x e l l e s B e l g i q u e4 FB 71226002 10 Avenue du s o l e i l 1001 Tunis T u n i s i e

NUMCLI NOMCLI TELCLI ADRNU ADRRUECLI ADRCP ADRVILLECL ADRPAYSCLI1 TRAIFOR 0645454545 13 Avenue de l a p a i x 75015 P a r i s France2 CLEMENT 0607080910 17 Avenue de l a p a i x 75015 P a r i s France3 SOUCY 98980307 77 Route de l a c o r n i c h e 4001 Sousse T u n i s i e

118/193

Tables ObjetCreation d’un type – TAD

Premiere extension du modele relationnel : Types Abstraits de Donnees(TAD)TAD (contexte BD) :

Nouveau type d’attribut defini par l’utilisateur

Enrichissement de la collection existante de types disponibles par defaut(number, date, char, varchar ...)

Structure de donnees partagee

Utilisation du type dans une ou plusieurs tablesParticipation a la composition d’un ou plusieurs autres types

Remarques :

Un TAD inclut des methodes qui sont des procedures ou des fonctions

Elles permettent de manipuler les objets du type abstrait

119/193

Tables ObjetCreation d’un type – exemple de TAD

c r ea te t y p e ADRESSE TYPE as o b j e c t( ADRNUM VARCHAR2( 1 0 ) ,

ADRRUE VARCHAR2( 5 0 ) ,ADRCP VARCHAR2( 1 0 ) ,ADRVILLE VARCHAR2( 5 0 ) ,ADRPAYS VARCHAR2( 5 0 ) )

/

c r ea te t y p e MAG TYPE as o b j e c t( NUMMAG INTEGER ,

NOMMAG CHAR( 3 0 ) ,TELMAG CHAR( 1 5 ) ,ADRMAG ADRESSE TYPE )

/

c r ea te t y p e CLI TYPE as o b j e c t( NUMCLI INTEGER ,

NOMCLI CHAR( 3 0 ) ,TELCLI CHAR( 1 5 ) ,ADRCLI ADRESSE TYPE )

/

120/193

Tables ObjetCreation d’une table – Exemples

create tab l e MAGASINS3 OF MAG TYPE( con s t r a i n t PK MAGASINS3 primary key (NUMMAG) ) ;

create tab l e CLIENTS3 OF CLI TYPE( con s t r a i n t PK CLIENTS3 primary key (NUMCLI) ) ;

121/193

Tables ObjetCreation d’un type

Remarques :

Un type ne peut pas contenir de contraintes (NOT NULL,CHECK, UNIQUE, DEFAULT, PRIMARY KEY, FOREIGN KEY, etc.).

Les contraintes doivent etre declarees au niveau de la tableobjet

Acces a la description des types a partir du Dictionnaire deDonnees :

SQL > s e l e c t tab le name , o b j e c t i d t y p e , t a b l e t y p e o w n e r ,t a b l e t y p e from u s e r o b j e c t t a b l e s ;

122/193

Tables ObjetCreation/description d’une table Exemples

SQL> desc c l i e n t s 2Nom NULL ? Type−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−− −−−−−−−−−−−−−−−−−−−−−NUMCLI NOT NULL NUMBER( 3 8 )NOMCLI CHAR( 2 0 )TELCLI CHAR( 1 5 )ADRNUMCLI VARCHAR2( 1 0 )ADRRUECLI VARCHAR2( 5 0 )ADRCPCLI VARCHAR2( 1 0 )ADRVILLECLI VARCHAR2( 5 0 )ADRPAYSCLI VARCHAR2( 5 0 )

SQL> desc c l i e n t s 3Nom NULL ? Type−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− −−−−−−−− −−−−−−−−−−−−−−−−−−−−−NUMCLI NOT NULL NUMBER( 3 8 )NOMCLI CHAR( 3 0 )TELCLI CHAR( 1 5 )ADRCLI ADRESSE TYPE

123/193

Tables ObjetObject identifier (OID)

OID bases sur la cle primaire : Utilisation de l’option primarykeyc r ea te t ab l e CLIENTS3 OF CLI TYPE

( c o n s t r a i n t PK CLIENTS3 pr imary key (NUMCLI) )o b j e c t i d e n t i f i e r i s pr imary key ;

Index sur OID :c r ea te t ab l e CLIENTS3 OF CLI TYPE

( c o n s t r a i n t PK CLIENTS3 pr imary key (NUMCLI) )o b j e c t i d e n t i f i e r i s system g e n e r a t e d OIDINDEX n d x c l i e n t s 3 ;

c r ea te t ab l e CLIENTS3 OF CLI TYPE( c o n s t r a i n t PK CLIENTS3 pr imary key (NUMCLI) )o b j e c t i d e n t i f i e r i s system g e n e r a t e dOIDINDEX n d x c l i e n t s 3 ( s t o r a g e ( i n i t i a l 100K next 50 k

m i n e x t e n t s 1 maxextents 50)) ;

124/193

Tables ObjetInstanciation - exemples

Insertion d’une � ligne � (ou plutot d’un objet) :

i n s e r t i n t o MAGASINS3 va l u e s (MAG TYPE( 1 , ’FB ’ , ’ 0145454545 ’ ,ADRESSE TYPE( ’ 13 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ , ’ P a r i s ’ , ’ France ’ ) ) ) ;

i n s e r t i n t o MAGASINS3 va l u e s (MAG TYPE( 2 , ’FB ’ , ’ 0155555555 ’ ,ADRESSE TYPE( ’ 20 ’ , ’ Avenue de l a l i b e r t e ’ , ’ 06100 ’ , ’ N ice ’ , ’ France ’ ) ) ) ;

i n s e r t i n t o MAGASINS3 va l u e s (MAG TYPE( 3 , ’FB ’ , ’ 0155555555 ’ ,ADRESSE TYPE( ’ 10 ’ , ’ Avenue des Amis ’ , ’ 6050 ’ , ’ B r u x e l l e s ’ , ’ B e l g i q u e ’ ) ) ) ;

i n s e r t i n t o MAGASINS3 va l u e s (MAG TYPE( 4 , ’FB ’ , ’ 71226002 ’ ,ADRESSE TYPE( ’ 10 ’ , ’ Avenue du s o l e i l ’ , ’ 1001 ’ , ’ Tunis ’ , ’ T u n i s i e ’ ) ) ) ;

SQL> s e l e c t ∗ from magas ins3 ;NUMMAG NOMMAG TELMAG ADRMAG(ADRNUM, ADRRUE, ADRCP, ADRVILLE , ADRPAYS)

1 FB 0145454545 ADRESSE TYPE( ’ 13 ’ , ’ Avenue de l a p a i x ’ ,’ 75015 ’ , ’ P a r i s ’ , ’ France ’ )

2 FB 0155555555 ADRESSE TYPE( ’ 20 ’ , ’ Avenue de l a l i b e r t e ’ ,’ 06100 ’ , ’ N ice ’ , ’ France ’ )

3 FB 0155555555 ADRESSE TYPE( ’ 10 ’ , ’ Avenue des Amis ’ ,’ 6050 ’ , ’ B r u x e l l e s ’ , ’ B e l g i q u e ’ )

4 FB 71226002 ADRESSE TYPE( ’ 10 ’ , ’ Avenue du s o l e i l ’ ,’ 1001 ’ , ’ Tunis ’ , ’ T u n i s i e ’ )

125/193

Tables ObjetInstanciation - exemples

i n s e r t i n t o CLIENTS3 va l u e s ( CLI TYPE ( 1 , ’TRAIFOR ’ , ’ 0645454545 ’ ,ADRESSE TYPE( ’ 13 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ , ’ P a r i s ’ , ’ France ’ ) ) ) ;

i n s e r t i n t o CLIENTS3 va l u e s ( CLI TYPE ( 2 , ’CLEMENT ’ , ’ 0607080910 ’ ,ADRESSE TYPE( ’ 17 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ , ’ P a r i s ’ , ’ France ’ ) ) ) ;

i n s e r t i n t o CLIENTS3 va l u e s ( CLI TYPE ( 3 , ’SOUCY ’ , ’ 98980307 ’ ,ADRESSE TYPE( ’ 77 ’ , ’ Route de l a c o r n i c h e ’ , ’ 4001 ’ , ’ Sousse ’ , ’ T u n i s i e ’ ) ) ) ;

SQL> Se l e c t ∗ from c l i e n t s 3 ;NUMCLI NOMCLI TELCLI ADRCLI (ADRNUM, ADRRUE, ADRCP, ADRVILLE , ADRPAYS)

1 TRAIFOR 0645454545 ADRESSE TYPE( ’ 13 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ ,’ P a r i s ’ , ’ France ’ )

2 CLEMENT 0607080910 ADRESSE TYPE( ’ 17 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ ,’ P a r i s ’ , ’ France ’ )

3 SOUCY 98980307 ADRESSE TYPE( ’ 77 ’ , ’ Route de l a c o r n i c h e ’ , ’ 4001 ’ ,’ Sousse ’ , ’ T u n i s i e ’ )

126/193

Tables ObjetInstanciation

Table objet-relationnelle :

Table dependante d’un type

Enregistrements (lignes) dans cette table consideres commedes objets car ils possedent tous un OID (Object Identifier)unique

SQL> SELECT ∗ FROM c l i e n t s 3 ;NUMCLI NOMCLI TELCLI ADRCLI (ADRNUM, ADRRUE, ADRCP, ADRVILLE , ADRPAYS)

1 TRAIFOR 0645454545 ADRESSE TYPE( ’ 13 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ ,’ P a r i s ’ , ’ France ’ )

2 CLEMENT 0607080910 ADRESSE TYPE( ’ 17 ’ , ’ Avenue de l a p a i x ’ , ’ 75015 ’ ,’ P a r i s ’ , ’ France ’ )

3 SOUCY 98980307 ADRESSE TYPE( ’ 77 ’ , ’ Route de l a c o r n i c h e ’ , ’ 4001 ’ ,’ Sousse ’ , ’ T u n i s i e ’ )

127/193

Tables ObjetInstanciation

Renvoi des OID des objets de la table :

SQL> SELECT REF( c ) FROM c l i e n t s 3 c ;

REF(C)−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−0000280209 E9E229206EDF47DF9996946C4BBD571C4EB9AF259F2F42BC813E18E51603C0D40240014600000000280209550141 E8898C4859AF0F3D48FA3041944EB9AF259F2F42BC813E18E51603C0D40240014600010000280209 C2C96804847047F6856499690AAC9E254EB9AF259F2F42BC813E18E51603C0D4024001460002

128/193

Tables ObjetMises a jour

Modifications/Suppressions de �lignes� ou d’objets

Mise a jour d’une colonne standard

update c l i e n t s 3s e t NOMCLI = ’CBON ’ where NUMCLI=2;

Modification d’une colonne appartenant a un type imbrique

update c l i e n t s 3 cs e t c . ADRCLI . ADRVILLE = ’MAVILLE ’ where c . NUMCLI=2;

Suppression d’objet

de l e t e from c l i e n t s 3where n u m c l i = 3 ;

de l e t e from c l i e n t s 3 cwhere upper ( c . ADRCLI .ADRPAYS) = ’FRANCE ’ ;

129/193

Tables ObjetInterrogations

Utilisation de colonnes standards

s e l e c t numcl i , n o m c l i from c l i e n t s 3 ;NUMCLI NOMCLI

−−−−−−−−−− −−−−−−−−−−1 TRAIFOR2 CLEMENT3 SOUCY

Utilisation d’une colonne appartenant a un type imbrique

s e l e c t numcl i , nomcl i , c . ADRCLI .ADRPAYSfrom c l i e n t s 3 c ;

NUMCLI NOMCLI ADRCLI .ADRPAYS−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−

1 TRAIFOR France2 CLEMENT France3 SOUCY T u n i s i e

130/193


avec formatage

c o l nom format A10c o l l o c fo rmat A15s e l e c t n u m c l i as c l i , n o m c l i as nom ,

c . ADRCLI . ADRVILLE | | ’ ’ | | c . ADRCLI .ADRPAYS as l o cfrom c l i e n t s 3 c ;

CLI NOM LOC−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−

1 TRAIFOR P a r i s France2 CLEMENT P a r i s France3 SOUCY Sousse T u n i s i e

131/193


avec contraintes

SQL> c o l c . ADRCLI .ADRPAYS format A10SQL> c o l c . ADRCLI . ADRVILLE format A10SQL> s e l e c t numcl i , nomcl i , c . ADRCLI . ADRPAYS,

2 c . ADRCLI . ADRVILLE from c l i e n t s 3 c3 WHERE upper ( c . ADRCLI . ADRVILLE ) l i k e ’P%’ ;

NUMCLI NOMCLI ADRCLI .ADRPAYS ADRCLI . ADRVILLE−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−− −−−−−−−−−−−−−−−

1 TRAIFOR France P a r i s2 CLEMENT France P a r i s

132/193

Tables imbriquees(NESTED TABLE)

Table imbriquee (NESTED TABLE) : collection non ordonnee et nonlimitee d’elements de meme typeExemple : table Departement

NumDep Budget EmployesNInsee Nom Age

1 table contenant une colonne (table) :Association du type 1-N

??? 1 ou plusieurs tables : Association du type N-N

133/193

Tables imbriquees (NESTED TABLE)Creation

c r ea te t y p e emp type as o b j e c t( n i n s e e va rcha r2 ( 1 3 ) , age number , nom va rcha r2 ( 3 0 ) )

/

c r ea te t y p e emps type as t ab l e o f emp type/c r ea te t y p e d e p a r t e m e n t t y p e as o b j e c t

( numdep va rcha r2 ( 1 1 ) , budget number ,employes emps type )

/c r ea te t ab l e departement o f d e p a r t e m e n t t y p e

( pr imary key ( numdep ) )n e s t e d t a b l e employes s t o r e as tabemp

/

clause NESTED TABLE : definition d’une table imbriqueeclause STORE AS : nommage de la structure interne qui stocke les�enregistrements� de cette table imbriquee

134/193

Tables imbriquees (NESTED TABLE)Exemple

c r ea te t y p e emp type as o b j e c t ( n i n s e e va rcha r2 ( 1 3 ) ,age number , nom va rcha r2 ( 3 0 ) )

/c r ea te t y p e emps type as t ab l e o f emp type/c r ea te t y p e d e p a r t e m e n t t y p e as o b j e c t ( numdep va rcha r2 ( 1 1 ) ,

budget number , employes emps type )/c r ea te t ab l e departement o f d e p a r t e m e n t t y p e

( pr imary key ( numdep ) )n e s t e d t a b l e employes s t o r e as tabemp

/

135/193

Tables imbriquees (NESTED TABLE)Exemple

SQL> desc departementNom NULL ? Type−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−NUMDEP NOT NULL VARCHAR2( 1 1 )BUDGET NUMBEREMPLOYES EMPS TYPE

SQL> desc emps typeemps type TABLE OF EMP TYPENom NULL ? Type−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−NINSEE VARCHAR2( 1 3 )AGE NUMBERNOM VARCHAR2( 3 0 )

136/193

Tables imbriquees (NESTED TABLE)Insertion

Insertion des donnees dans une table imbriquee

i n s e r t i n t o departement va l u e s ( ’D1 ’ , 100000 , emps type ( ) ) ;i n s e r t i n t o departement va l u e s ( ’D2 ’ , 200000 , emps type ( ) ) ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−D1 100000 EMPS TYPE( )D2 200000 EMPS TYPE( )

137/193


Attention : dans l’exemple suivant, la table vide est noninitialisee

i n s e r t i n t o departement ( numdep , budget )va l u e s ( ’D3 ’ , 3 0 0 0 0 0 ) ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 100000 EMPS TYPE( )D2 200000 EMPS TYPE( )D3 300000

138/193


Insertion des donnees dans une table imbriquee

i n s e r t i n t o departement va l u e s ( ’D4 ’ , 400000 ,emps type ( emp type ( ’N5 ’ , 25 , ’ B i b i ’ ) ,

emp type ( ’N6 ’ , 26 , ’ C i c i ’ ) ,emp type ( ’N7 ’ , 27 , ’ D i d i ’ ) ,emp type ( ’N8 ’ , 28 , ’ F i f i ’ ) ) ) ;

139/193


SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 100000 EMPS TYPE( )D2 200000 EMPS TYPE( )D3 300000D4 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,

EMP TYPE( ’N6 ’ , 26 , ’ C i c i ’ ) ,EMP TYPE( ’N7 ’ , 27 , ’ D i d i ’ ) ,EMP TYPE( ’N8 ’ , 28 , ’ F i f i ’ ) )

Remarque : Commande INSERT avec les constructeurs des typesde la NESTED TABLE

stocke un objet dans la tableinitialise la table imbriquee associee avec des enregistrements

140/193


Insertion des donnees dans une table imbriqueei n s e r t i n t o departement va l u e s ( ’D5 ’ , 400000 ,

emps type ( emp type ( ’N5 ’ , 25 , ’ B i b i ’ ) ,emp type ( ’N8 ’ , 28 , ’ F i f i ’ ) ) ) ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 100000 EMPS TYPE( )D2 200000 EMPS TYPE( )D3 300000D4 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,


D5 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,EMP TYPE( ’N8 ’ , 28 , ’ F i f i ’ ) )

141/193

Tables imbriquees (NESTED TABLE)Insertion avec l’operateur TABLE

Insertion avec l’operateur TABLE dans une table imbriquee(D1 et D2 etaient initialises a vides)

i n s e r t i n t o TABLE ( s e l e c t d . employes from departement dwhere d . numdep = ’D1 ’ )

va l u e s ( ’N1 ’ , 21 , ’CLEMENT ’ ) ;i n s e r t i n t o TABLE ( s e l e c t d . employes from departement d

where d . numdep = ’D2 ’ )va l u e s ( ’N2 ’ , 22 , ’CLEMENTINE ’ ) ;

NB : l’operateur THE est obsolete et a ete remplace par l’operateurTABLE

142/193


SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−− −−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 100000 EMPS TYPE(EMP TYPE( ’N1 ’ , 21 , ’CLEMENT ’ ) )D2 200000 EMPS TYPE(EMP TYPE( ’N2 ’ , 22 , ’CLEMENTINE ’ ) )D3 300000D4 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,


Remarques :

Commande INSERT INTO TABLE (SELECT ...) : stockage d’unenregistrement dans la table imbriquee designee par TABLE

SELECT apres le TABLE : Retourne un seul objet, ce qui permet deselectionner la table imbriquee associee

143/193


Insertion avec l’operateur TABLE dans une table imbriquee

(D3 n’etait pas initialise a vide)Insertion d’un employe dans le departement D3alors que celui-ci n’a pas ete initialise

i n s e r t i n t o TABLE ( s e l e c t d . employes from departement dwhere d . numdep = ’D3 ’ )va l u e s ( ’N3 ’ , 23 , ’NE MARCHE PAS ’ ) ;

144/193


SQL> i n s e r t i n t o t ab l e ( s e l e c t d . employes from departement d2 where d . numdep = ’D3 ’ ) va l u e s ( ’N3 ’ , 23 , ’NEMARCHEPAS ’ ) ;

i n s e r t i n t o t ab l e ( s e l e c t d . employes from departement dwhere d . numdep = ’D3 ’ )

∗ERREUR a l a l i g n e 1 :ORA−22908: r e f e r e n c e a une v a l e u r de t a b l e NULL

Explications :

1 Le departement D3 est bien un objet de la table Departement

2 mais il ne possede pas de table imbriquee

3 car celle-ci n’a pas ete creee lors de l’insertion.

Il faut detruire l’objet D3 puis le recreer !

145/193

Tables imbriquees (NESTED TABLE)Modification

Mise a jour de la table principaleupdate departement d

s e t d . budget = d . budget ∗ 1 . 5where d . budget <= 200000 ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−−− −−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 150000 EMPS TYPE(EMP TYPE( ’N1 ’ , 21 , ’CLEMENT ’ ) )D2 300000 EMPS TYPE(EMP TYPE( ’N2 ’ , 22 , ’CLEMENTINE ’ ) )D3 300000D4 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,



146/193

Tables imbriquees (NESTED TABLE)Modification (suite)

Mise a jour de la table principale selon un predicat dans latable imbriqueeupdate departement d s e t d . budget = d . budget + 777

where e x i s t s ( s e l e c t ∗ fromtab l e ( s e l e c t dt . employes from departement dt

where dt . numdep =d . numdep ) nt

where nt . age < 25 ) ;

Description :

Requete qui retourne les employes de chaque departement

s e l e c t dt . employes from departement dtwhere dt . numdep = d . numdep

Condition sur un attribut de la table imbriquee :

where nt . age < 25

Alias de la table imbriquee : nt

147/193


Mise a jour de la table principale selon un predicat dans latable imbriquee

148/193


SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−− −−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 150777 EMPS TYPE(EMP TYPE( ’N1 ’ , 21 , ’CLEMENT ’ ) )D2 300777 EMPS TYPE(EMP TYPE( ’N2 ’ , 22 , ’CLEMENTINE ’ ) )D3 300000D4 400000 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,



149/193


Mise a jour de la table principale selon un predicat dans latable imbriquee

update departement d s e t d . budget = d . budget + 999where e x i s t s

( s e l e c t ∗ from tab l e( s e l e c t dt . employes from departement dt

where dt . numdep = d . numdep ) ntwhere nt . age > 25 ) ;

150/193





Remarque : les memes employes sont dans deux departements

151/193


Mise a jour dans la table imbriquee

updatet ab l e ( s e l e c t d . employes from departement d

where d . numdep = ’D2 ’ ) nts e t nt . age = 44

where nt . n i n s e e = ’N2 ’ ;

152/193





Remarque : Il est impossible de modifier plusieurs enregistrementsde differentes tables imbriquees avec une seule commandeUPDATE !

153/193

Tables imbriquees (NESTED TABLE)Suppression

Suppression dans la table principale

de l e t e from departementwhere numdep = ’D3 ’ ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−− −−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 150777 EMPS TYPE(EMP TYPE( ’N1 ’ , 21 , ’CLEMENT ’ ) )D2 300777 EMPS TYPE(EMP TYPE( ’N2 ’ , 44 , ’CLEMENTINE ’ ) )D4 400999 EMPS TYPE(EMP TYPE( ’N5 ’ , 25 , ’ B i b i ’ ) ,



154/193


Suppression a partir d’une valeur de la table imbriqueeElimination des departements qui emploient une personnedont le nom est FIFI

de l e t e from departement dwhere e x i s t s ( s e l e c t ∗ fromtab l e ( s e l e c t dt . employes from departement dt

where dt . numdep = d . numdep ) ntwhere upper ( nt . nom) l i k e ’%F I F I%’ ) ;

155/193


SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−− −−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 150777 EMPS TYPE(EMP TYPE( ’N1 ’ , 21 , ’CLEMENT ’ ) )D2 300777 EMPS TYPE(EMP TYPE( ’N2 ’ , 44 , ’CLEMENTINE ’ ) )

156/193


Suppression d’une table imbriqueeElimination des departements qui emploient une personnedont le nom est CLEMENT

de l e t e t ab l e ( s e l e c t dt . employesfrom departement dt where dt . numdep = ’D1 ’ ) ntwhere nt . nom = ’CLEMENT ’ ;

SQL> s e l e c t ∗ from departement ;NUMDEP BUDGET EMPLOYES( NINSEE , AGE, NOM)−−−−−−− −−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−D1 150777D2 300777 EMPS TYPE(EMP TYPE( ’N2 ’ , 44 , ’CLEMENTINE ’ ) )

157/193

Tables imbriquees (NESTED TABLE)Interrogation

Quels sont les numeros et les noms des employes dudepartement D4 ?




158/193


s e l e c t nt . n i n s e e , nt . nomfrom tab l e ( s e l e c t dt . employes from departement dtwhere dt . numdep = ’D4 ’ ) nt ;

NINSEE NOM−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−N5 B i b iN6 C i c iN7 D i d iN8 F i f i

159/193


Quels sont les numeros et les noms des employes dudepartement D4 qui ont moins de 26 ans ?




160/193


s e l e c t nt . n i n s e e , nt . nomfrom tab l e ( s e l e c t dt . employes from departement dtwhere dt . numdep = ’D4 ’ ) nt where nt . age < 2 6 ;

NINSEE NOM−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−N5 B i b i

161/193


Requete : Quel est le nombre d’employes du departementD4 ?




162/193


s e l e c t COUNT(∗ ) ”Nombre d ’ employ e s ”from tab l e ( s e l e c t dt . employes from departement dtwhere dt . numdep = ’D4 ’ ) nt ;

Nombre d ’ employ e s−−−−−−−−−−−−−−−−−

4

163/193


Quels sont les numeros et les noms des employes desdepartements D1 et D2 ?




164/193


s e l e c t s e l e c t nt . n i n s e e , nt . nom from tab l e( s e l e c t dt . employes from departement dt

where dt . numdep = ’D1 ’ ) ntunions e l e c t nt . n i n s e e , nt . nom from tab l e

( s e l e c t dt . employes from departement dtwhere dt . numdep = ’D2 ’ ) nt ;

NINSEE NOM−−−−−−−−−−−−− −−−−−−−−−−−−−−−−−−−−−−−−−−−−−−N1 CLEMENTN2 CLEMENTINE

165/193

Plusieurs Tables imbriqueesCreation

Regroupement des tables imbriquees Professeurs et Formations dans la

table Cours

NumC Titre Professeurs FormationsNom Specialite Filiere Horaire

c r ea te t y p e p r o f t y p e as o b j e c t(nom va rcha r2 ( 3 0 ) , s p e c i a l i t e va rcha r2 ( 3 0 ) )

/c r ea te t y p e p r o f s t y p e as t ab l e o f p r o f t y p e/c r ea te t y p e f o r m a t i o n t y p e as o b j e c t

( f i l i e r e va rcha r2 ( 3 0 ) , h o r a i r e number ( 5 ) )/c r ea te t y p e f o r m a t i o n s t y p e as t ab l e o f f o r m a t i o n t y p e/

166/193

Plusieurs Tables imbriqueesCreation

c r ea te t y p e p r o f t y p e as o b j e c t(nom va rcha r2 ( 3 0 ) , s p e c i a l i t e va rcha r2 ( 3 0 ) )

/c r ea te t y p e p r o f s t y p e as t ab l e o f p r o f t y p e/c r ea te t y p e f o r m a t i o n t y p e as o b j e c t

( f i l i e r e va rcha r2 ( 3 0 ) , h o r a i r e number ( 5 ) )/c r ea te t y p e f o r m a t i o n s t y p e as t ab l e o f f o r m a t i o n t y p e/c r ea te t y p e c o u r s t y p e as o b j e c t

( numc va rcha r2 ( 5 ) , t i t r e va rcha r2 ( 1 5 ) ,p r o f e s s e u r s p r o f s t y p e , f o r m a t i o n s f o r m a t i o n s t y p e )

/c r ea te t ab l e c o u r s o f c o u r s t y p e

( c o n s t r a i n t p k c o u r s pr imary key ( numc ) )n e s t e d t a b l e p r o f e s s e u r s s t o r e as t a b p r o f s ,n e s t e d t a b l e f o r m a t i o n s s t o r e as t a b f o r m a t i o n s ;

167/193

Plusieurs Tables imbriqueesInsertion

Insertion d’un objet dans la table Cours, sans le lier a desprofesseurs ou a des formations

i n s e r t i n t o c o u r s va l u e s ( ’BD ’ , ’ Bases de Donnees ’ ,p r o f s t y p e ( ) , f o r m a t i o n s t y p e ( ) ) ;

s e l e c t ∗ from c o u r s ;

NUMC TITRE PROFESSEURS(NOM, SPECIALITE ) FORMATIONS( FILIERE , HORAIRE)−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−BD Bases de Donnees PROFS TYPE ( ) FORMATIONS TYPE( )

168/193


Insertion, avec VALUES, dans 2 tables imbriquees :

i n s e r t i n t o c o u r s va l u e s ( ’DW’ , ’ Data WareHouse ’ ,

p r o f s t y p e ( p r o f t y p e ( ’ Cl emence ’ , ’BD ’ ) ,p r o f t y p e ( ’Adam ’ , ’BD ’ ) ) ,

f o r m a t i o n s t y p e ( f o r m a t i o n t y p e ( ’ Master1 ’ , 1 00 ) ,f o r m a t i o n t y p e ( ’DESS EID ’ , 20 0) ,f o r m a t i o n t y p e ( ’DEA AIOC ’ , 200) ) ) ;

169/193



NUMC TITRE PROFESSEURS(NOM, SPECIALITE ) FORMATIONS( FILIERE , HORAIRE)−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−BD Bases de Donnees PROFS TYPE ( ) FORMATIONS TYPE( )DW Data WareHouse PROFS TYPE( FORMATIONS TYPE(

PROF TYPE( ’ Cl emence ’ , ’BD ’ ) , FORMATION TYPE( ’ Master1 ’ , 10 0) ,PROF TYPE( ’Adam ’ , ’BD ’ ) ) FORMATION TYPE( ’ Master 2P ’ , 20 0) ,

FORMATION TYPE( ’ Master 2R ’ , 2 0 0 ) )

170/193


Insertion, avec VALUES, dans 2 tables imbriquees

i n s e r t i n t o c o u r s va l u e s ( ’BDA ’ ,’ Bases de Donnees Avanc ees ’ ,

p r o f s t y p e ( p r o f t y p e ( ’ Cl emence ’ , ’BD ’ ) ,p r o f t y p e ( ’ T r a i f o r ’ , ’BD ’ ) ,p r o f t y p e ( ’ Le Bon ’ , ’BD ’ ) ) ,

f o r m a t i o n s t y p e (f o r m a t i o n t y p e ( ’ Master 2P ’ , 2 00 ) ,f o r m a t i o n t y p e ( ’ Master 2R ’ , 200) ) ) ;

171/193


CoursNumC Titre Professeurs Formations

Nom Specialite Filiere HoraireBD Bases de donneesDW DataWareHouse Clemence BD Master 1 100

Adam BD Master 2P 200Master 2R 200

BDA Bases de donnees avances Clemence BD Master 2P 200Traifor BD Master 2R 200Le Bon BD

L’affichage SQL+ est tres mauvais ...s e l e c t ∗ from c o u r s ;

NUMC TITRE PROFESSEURS(NOM, SPECIALITE ) FORMATIONS( FILIERE , HORAIRE)BD Bases de Donnees PROFS TYPE ( ) FORMATIONS TYPE( )DW Data WareHouse PROFS TYPE(PROF TYPE( ’ Cl emence ’ , ’BD ’ ) , PROF TYPE( ’Adam ’ , ’BD ’ ) )

FORMATIONS TYPE(FORMATION TYPE( ’ Master1 ’ , 10 0) ,FORMATION TYPE( ’ Master 2P ’ , 20 0) , FORMATION TYPE( ’ Master 2R ’ , 2 0 0 ) )BDA Bases de Donnees Avanc ees PROFS TYPE(PROF TYPE( ’ Cl emence ’ , ’BD ’ ) , PROF TYPE( ’ T r a i f o r ’ , ’BD ’ ) ,

PROF TYPE( ’ Le Bon ’ , ’BD ’ ) )FORMATIONS TYPE(FORMATION TYPE( ’ Master 2P ’ , 20 0) ,

FORMATION TYPE( ’ Master 2R ’ , 2 0 0 ) )

172/193


Insertion, avec TABLE et VALUES, dans 2 tables imbriqueesEnregistrement des donnees : les professeurs Traifor et Parisi enseignentles BD

i n s e r t i n t o t ab l e ( s e l e c t c . p r o f e s s e u r s from c o u r s cwhere numc = ’BD ’ ) va l u e s ( ’ T r a i f o r ’ , ’ S I ’ ) ;

i n s e r t i n t o t ab l e ( s e l e c t c . p r o f e s s e u r s from c o u r s cwhere numc = ’BD ’ ) va l u e s ( ’ P a r i s i ’ , ’DM’ ) ;



BD Bases de donnees Traifor SIParisi DM

DW DataWareHouse Clemence BD Master 1 100Adam BD Master 2P 200

Master 2R 200BDA Bases de donnees avances Clemence BD Master 2P 200

Traifor BD Master 2R 200Le Bon BD

173/193


Insertion, avec TABLE et VALUES, dans 2 tables imbriqueesLe cours BD :

appartient au cursus INFO1

requiert un volume horaire de 70 heures

i n s e r t i n t o t ab l e ( s e l e c t c . f o r m a t i o n s from c o u r s cwhere numc = ’BD ’ ) va l u e s ( ’ INFO1 ’ , 7 0 ) ;



BD Bases de donnees Traifor SI INFO1 70Parisi DM




174/193


Insertion , avec TABLE et SELECT, dans 2 tables imbriquees

Le cours BD doit etre enseigne desormais dans toutes les filieresconcernees par la matiere DW a condition que celle-ci aient unvolume de moins de 150 heures

i n s e r t i n t o t ab l e ( s e l e c t c . f o r m a t i o n s from c o u r s cwhere c . numc = ’BD ’ )

s e l e c t n e s t e d f . f i l i e r e , n e s t e d f . h o r a i r efrom tab l e ( s e l e c t c . f o r m a t i o n s from c o u r s c

where c . numc = ’DW’ ) n e s t e d fwhere n e s t e d f . h o r a i r e < 1 5 0 ;

175/193




BD Bases de donnees Traifor SI INFO1 70Parisi DM Master 1




176/193

Plusieurs Tables imbriqueesModification – Exemple

Dans la matiere Data WareHouse, le professeur Adam est remplacepar le professeur Saitout et que les horaires pour le Master 2Paugmentent de 30%

update t ab l e ( s e l e c t c . p r o f e s s e u r s from c o u r s cwhere c . t i t r e = ’ Data WareHouse ’ ) n e s t e d p r f

s e t n e s t e d p r f . nom = ’ S a i t o u ’ where n e s t e d p r f . nom = ’Adam ’ ;

update t ab l e ( s e l e c t c . f o r m a t i o n s from c o u r s cwhere c . t i t r e = ’ Data WareHouse ’ ) n e s t e d f r m

s e t n e s t e d f r m . h o r a i r e = h o r a i r e ∗ 1 . 3where n e s t e d f r m . f i l i e r e l i k e ’ Master 2P%’ ;

177/193

Plusieurs Tables imbriqueesModification



DW DataWareHouse Clemence BD Master 1 100Saitou BD Master 2P 260



178/193

Plusieurs Tables imbriqueesModification – Exemple

Explications :

Modification a l’aide de la commande UPDATE d’un ouplusieurs attributs dans une des deux tables imbriquees de latable cours

Modification d’un professeurs et une formation dans le cadred’une matiere donnee : 2 requetes UPDATE distinctes(car les 2 tables imbriquees sont concernees)

Necessite de recourir a un alias pour identifier l’objet dans latable imbriquee

179/193


Pour la matiere DW, remplacement de la filiere Master1 par lafiliere MASTER 2 et enregistrement d’un volume horaire de 150heures

update t ab l e ( s e l e c t c . f o r m a t i o n s from c o u r s cwhere c . t i t r e = ’ Data WareHouse ’ ) n e s t e d f r m

s e t n e s t e d f r m . h o r a i r e = 150 , n e s t e d f r m . f i l i e r e = ’MASTER 2 ’where n e s t e d f r m . f i l i e r e = ’ Master1 ’ ;

180/193




DW DataWareHouse Clemence BD MASTER 2 150Saitou BD Master 2P 260



181/193

Plusieurs Tables imbriqueesSuppression

Suppression dans 2 tables imbriqueesLe professeur Parisi n’enseigne plus la matiere BD. Enregistrementde cette information

de l e t e t ab l e ( s e l e c t c . p r o f e s s e u r sfrom c o u r s c where c . numc = ’BD ’ ) ntwhere nt . nom = ’ P a r i s i ’ ;


BD Bases de donnees Traifor SI INFO1 70Master 1




182/193

Plusieurs Tables imbriqueesSuppression

Suppression dans 2 tables imbriqueesLa filiere Master1 n’inclut plus la matiere BD dans son cursus.Enregistrement de cette information

de l e t e t ab l e ( s e l e c t c . f o r m a t i o n sfrom c o u r s c where c . numc = ’BD ’ ) ntwhere nt . f i l i e r e = ’ Master1 ’ ;


BD Bases de donnees Traifor SI INFO1 70




183/193

Plusieurs niveaux d’imbrication

NumC Titre Professeurs FormationsNom Specialite Filiere Horaire Dates

JourBD Bases de donnees Traifor SI INFO1 70




Oracle 8 ne permet pas d’implanter plusieurs niveaux d’imbricationdans une table objet-relationnelle?? dans oracle 9i et/ou 10g ??

184/193

Tableaux pre-dimensionnes (VARRAY)VARRAY (Varrying ARRAY ) : collection ordonnee et limiteed’elements de meme typeSi le nombre d’elements maximum contenus dans une tableimbriquees est connu a prioripossibilite d’utiliser un tableau de type VARRAY a la placed’une table imbriqueeExemple : stockage de 3 numeros de telephone maximum parprofesseur

Professeurs :NumP NomP Adresse Telephones

AdrNum AdrRue AdrVille AdrCP NumTel

185/193

Tableaux de taille pre-dimensionnee (VARRAY)Exemple

Stockage de 3 numeros de telephone maximum par professeur

Creation

c r ea te t y p e A A d r e s s e t y p e as o b j e c t(AdrNum va rcha r2 ( 1 0 ) , AdrNom va rcha r2 ( 3 0 ) ,

A d r V i l l e va rcha r2 ( 2 0 ) , AdrCP va rcha r2 ( 5 ) )/c r ea te t y p e t e l t y p e as o b j e c t ( NumTel va rcha r2 ( 2 0 ) )/

c r ea te t y p e t e l s t y p e as v a r r a y ( 3 ) o f t e l t y p e/

c r ea te t y p e p r o f e s s e u r t y p e as o b j e c t(nump va rcha r2 ( 5 ) , nomp va rcha r2 ( 2 0 ) ,

A d r e s s e A A d r e s s e t y p e , T e le p h o ne s t e l s t y p e )/c r ea te t ab l e p r o f e s s e u r s o f p r o f e s s e u r t y p e

( c o n s t r a i n t p k p r o f e s s e u r s pr imary key (nump ) ) ;

186/193

Tableaux de taille pre-dimensionnee (VARRAY)Insertion : INSERT avec VALUES

Stockage de 3 objets de type Professeur avec respectivement aucun,trois et deux numeros de telephone (enregistrements du VARRAY)

i n s e r t i n t o p r o f e s s e u r s va l u e s ( ’ P1 ’ , ’ Cl emence ’ ,A A d r e s s e t y p e ( 7 , ’ Avenue de l a Pa ix ’ , ’ P a r i s ’ , ’ 75009 ’ ) ,t e l s t y p e ( ) ) ;

i n s e r t i n t o p r o f e s s e u r s va l u e s ( ’ P2 ’ , ’Adam ’ ,A A d r e s s e t y p e ( 7 7 , ’ Rue de l a l i b e r t e ’ , ’ P a r i s ’ , ’ 75015 ’ ) ,t e l s t y p e ( t e l t y p e ( ’ 01 53 80 07 99 ’ ) ,

t e l t y p e ( ’ 06 14 56 07 06 ’ ) ,t e l t y p e ( ’ 01 49 40 07 40 ’ ) ) ) ;

i n s e r t i n t o p r o f e s s e u r s va l u e s ( ’ P3 ’ , ’ S a i t o u ’ ,A A d r e s s e t y p e ( 1 , ’ Rue de l a l i b e r t e ’ , ’ P a r i s ’ , ’ 75015 ’ ) ,t e l s t y p e ( t e l t y p e ( ’ 01 53 80 53 80 ’ ) ,t e l t y p e ( ’ 06 14 56 14 77 ’ ) , NULL ) ) ;

187/193

Tableaux de taille pre-dimensionnee (VARRAY)

NumP NomP Adresse TelephonesAdrNum AdrRue AdrVille AdrCP NumTel

P1 Clemence 77 Avenue de la paix Paris 75009 NULLNULLNULL

P2 Adam 7 Rue de la liberte Paris 75015 01 53 80 07 9906 14 56 07 0601 49 40 07 40

P3 Saitou 1 Rue de la liberte Paris 75015 01 53 80 53 8006 14 56 14 77

NULL

188/193

Tableaux de taille pre-dimensionnee (VARRAY)

Insertion : INSERT dans un VARRAY avec PL/SQL

Avec les tableaux VARRAY, l’operateur TABLE n’est pasoperationnel (Version 8 d’oracle – a verifier sur les V9 et V10g)Pour manipuler les tableaux, il est necessaire d’utiliser unprogramme PL/SQL

DECLAREn e w t e l s t e l s t y p e := t e l s t y p e ( t e l t y p e ( ’ 01 55 55 55 55 ’ ) ,

t e l t y p e ( ’ 06 06 98 98 98 ’ ) ,t e l t y p e ( ’ 01 40 40 40 40 ’ ) ) ;

BEGINupdate p r o f e s s e u r ss e t t e l e p h o n e s = n e w t e l swhere nump = ’ P1 ’ ;

END ;/

189/193

Tableaux de taille pre-dimensionnee (VARRAY)NumP NomP Adresse Telephones

AdrNum AdrRue AdrVille AdrCP NumTelP1 Clemence 77 Avenue de la paix Paris 75009 01 55 55 55 55

06 06 98 98 9801 40 40 40 40

P2 Adam 7 Rue de la liberte Paris 75015 01 53 80 07 9906 14 56 07 0601 49 40 07 40

P3 Saitou 1 Rue de la liberte Paris 75015 01 53 80 53 8006 14 56 14 77

NULL

Remarque :

Inserer un seul numero de telephone pour le professeur P1 etle placer au 2eme rang dans le tableau telephonesRedaction ci-dessous de l’instruction d’affectation :

n e w t e l s t e l s t y p e := t e l s t y p e (NULL ,t e l t y p e ( ’ 06 06 98 98 98 ’ ) , NULL ) ;

190/193

ConclusionComparaison entre NESTED TABLE et VARRAY

A verifier selon les versions d’Oracle

Possibilite de definir un index dans un NESTED TABLE

Le nombre d’elements n’est pas limite dans une tableimbriquee

Pas de possibilite de definir d’index dans un VARRAY

Le nombre d’elements est limite dans une tableaupre-dimensionne

Possibilite d’acceder directement aux enregistrements stockesdans les deux structures de donneesfonctions : EXISTS, FIRST, LAST, etc.

Performances ? : NestedTable > Varray

191/193

Bilan

192/193

To be continued...

193/193

Documents

Bases de Donn ees Avanc ees - LIMSI