chapitre 01-Le relationnel étendu

Base de données avancéeMaster 2 INFORMATIQUE

ACADEMIQUE

Enseignante: AICHA AGGOUNE

[email protected]

Département d’informatique

Faculté de mathématique et d’informatique et sciences de la nature

Université 8 mai 45 de GUELMA

Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

Chapitre 01:

Le relationnel étendu


Plan du cours

1. Base de données: Rappels & Bilan

2. Motivations

3. Limites du modèle relationnel

4. Le relationnel étendu

5. SQL3 et SGBD Objet-Relationnel


Qu’est ce qu’une base de données?


Bases de Données

• Un ensemble de données modélisant les objets d’une partie du monde réel et servant de support à une application informatique.

• Accessible par un LMD • Stockage et mise à jour de données• Recherche de données multi-critères

• Partagées par de multiples utilisateurs

• Assurant la fiabilité et sécurité des données

• Gérée par un SGBD (DBMS)


Modèles de bases de Données

Modèle Relationnel (Codd 70)

• Modèles relationnels imbriqués (80)Modèle à valeurs complexes

(84) • Modèles avec identité d’objet(86)

Modèle Entité/Association (Chen 76)

• Modèles sémantiques(80)

Modèle relationnels étendu(99)

Programmation par Règles

Programmation par Types Abstraits

Programmation par objets

Programmation logique

Modèle logique (85) Modèle objet (85)


SGBD (DBMS)• Ensemble de logiciels systèmes permettant :

• La description des données et des relations (LDD)• L’interrogation et la mise à jour de la base (LMD)• La gestion des transactions.

• Principales fonctionnalités :• Contrôle de la redondance d’information• Partage des données et confidentialité • Gestion des accès concurrents• Vérifications des contraintes d’intégrité• Sécurité et reprise sur panne

• Indépendance des données: Niveau physique, logique et externe (vue)


Evolution des SGBD

1955-1960: SGF

1960-1980:SGBD hiérarchiques

SGBD réseaux

1970-1990:SGBD relationnelLangage SQL

1985-1990:SGBD objet

Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

Depuis 1995…

Bases de données et internet• Accès distant au contenu des BD• Fédération et inter-opération des BD• Transformations des données et des requêtes

Bases de données et décisionnel• Entreposage de données• Analyse de données• Qualité des données

Bases de données réparties :• Permettre d’intégrer, d’interroger et de mettre à jour des

données provenant de sites répartis géographiquement


Bases de données et web:• Etendre les possibilités des SGBD pour répondre aux

applications fonctionnant dans l’environnement web

Bases de données objet :• Intégrer les concepts objets dans les SGBD (classe, objet)

Bases de données déductives :• Enrichir les SGBD de possibilités de déduction

Bases de données multimédia :• Intégrer dans un SGBD la gestion de données textuelles,

graphiques, images, sons etc..

Depuis 1995…


2. Motivations Le modèle relationnel connaît un très grand succès et s'avère très adéquat pour les applications traditionnelles des bases de données (gestion de stock, gestion des commandes, systèmes de réservation)

Néanmoins, ce modèle est moins adapté aux nouvelles applications plus complexes telles que :

• CAD/CAM (Computer-Aided Design/Computer-Aided Manufacturing).

• BD d'images et de graphiques.

• BD géographiques (GIS : Geographic Information Systems).

• BD multimédia (son, image, texte, etc. combines).

• Systèmes de gestion de réseaux,…etc.Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

2. Motivations Ces nouvelles applications ont des caractéristiques différentes, notamment : Structures d'objets plus complexes, des transactions de durée plus longue, nouveaux types de données pour le stockage d'images ou de gros documents de texte, la possibilité de définir des opérations non standards qui sont spécifiques aux applications.



• Structure de données trop simple :

• table → ensemble de n-uplets de valeurs atomiques (nombres, chaînes de caractères…)

• structure plate régulière



• Mauvais support des applications non standards.

• Opérations sur les données exclues du modèle (méthodes et procédures)• Pas de possibilité de référencer directement un objet composant (pointeurs) : • La seule solution dans une BD classique est d’effectuer des

jointures.• Pas de gestion des fonctions spécifiques à certaines données (le texte, l’image, la vidéo,…etc). • Pauvreté du système de typage • Types prédéfinis (entier, réel, chaîne, …) : pas de possibilité d’extension• Sémantique insuffisante :

• La seule solution est d’utiliser des contraintes d'intégrité Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

3. Limites du modèle relationnel • Faible puissance d’expression des langages de BD:

• Pas de récursion• Pas de structures de contrôle : conditionnelles, boucles• L’utilisation de deux langages (SQL + un langage de

programmation)

• Difficulté de réaliser des traitement des transactions de longue durée.

Deux types d'alternatives sont proposés et qui sont inspirés de l’approche Objet


Smalltalk, C++, ...

OODBMS

ODMG 93

ODMG II(97)

Codd (70)

SQL

SQL2

SQL3

SQL4Convergence ?

Deux approches en BD

Le modèle Orienté Objet (OO) Le modèle Objet Relationnel (OR)

CA-Jasmine,ObjectStore, O2Versant, POET, ...

Oracle, Informix, Sybase, IBM, DB2, CA-OpenIngres

ODMGANSI X3H2ISO/IEC JTC1/ SC21/WG3


• Simplicité des concepts et du schéma

• Bon support théorique

• Langage d’interrogation déclaratif

• Haut degré d’indépendance des données

• Optimisation des accès à la BD • bonnes performances

• Gestion de contraintes d’intégrité

Apports du modèle relationnel


4. Le Relationnel Etendu Le relationnel étendu (Objet-Relationnel) : Extension du modèle

relationnel, ajoute: • Attributs multivalués : sacs, liste, tableau, ensemble, ...• Héritage sur relations et types• Définition d’un type abstrait de données propre à un

utilisateur (structure cachée + méthodes)• Identité d'objets et utilisation de référence (OID)

DomaineTableAttributCléRéférence

OBJET

Opération

Héritage

Identifiant

Types utilisateurs Collections

Le relationnel

ADT et encapsulation

Héritage et réutilisation

Référence et identité

Collection, objetsPolymorphisme


• La compatibilité est ascendante : les anciennes applications relationnelles fonctionnent dans le monde OR

• La norme SQL99 (SQL3) reprend beaucoup d’idées du modèle OR.

4. Le Relationnel Etendu

Objet Personne


4. Le Relationnel Etendu

4.1. Les ADT: différentes catégories de Types

4.2. Création de tables à partir des types

4.3. Définition d’attributs référence

4.4. Manipulation avancée de BD

4.5. Langage SQL3 et SGBD Relationnel-objet


Les ADT: Abstract Data Type

• En relationnel: types prédéfinis• CHAR, VARCHAR, NUMBER, DATE,…

• En relationnel étendu: nouveaux types définis pas utilisateurs

• constructeur CREATE TYPE…

• Plusieurs catégories de TYPES:• De type OBJET• De type COLLECTION

• VARRAY • Nested Table …


CREATE TYPE <nom ADT> AS <corps de l’ADT>

<corps de l’ADT> <OID option> ::= WITH OID [NOT] VISIBLE Objets sans OID par défaut <subtype clause> ::= UNDER <supertype clause> Possibilité d’héritage avec résolution de conflits explicite <member list> <attribute definition> : attributs publics ou privés <function declaration> : opérations publiques <operator name list> : opérateurs surchargés <ordering definition> : ordre (Equals, less, than, relative, hash) <cast clause> : fonction de conversion de types <procedure clause> : procédure avec corps défini dans SQL3 ou externe

Les ADT: syntaxe


Les ADT: Exemple

CREATE TYPE Person (PUBLIC name CHAR(50), address Address, Nationality VARCHAR, PRIVATE birth-date DATE, PUBLIC FUNCTION age RETURNS NUMBER);

• <attribute definition> permet de définir les droits d’accès aux éléments d’un objet.

• Elle joue le rôle d’un modificateur dans la définition des classes JAVA.

• PUBLIC un objet est accessible partout. • PRIVATE Un objet n'est accessible qu’à l'intérieur de la

classe qui le contient.

Exemple:


Catégories d’ADT: type OBJET • Objet complexes

• Informations incluses dans un type:

• Nom: identification de l’objet (unique dans le schéma): OID (Object IDentifier)

• Attributs

• Méthodes: fonctions ou procédures écrites en PL/SQL ou JAVA et stockées dans la BD ou en C et stockées à l’extérieur de la BD


ADT de type OBJET: syntaxe

• CREATE TYPE <nom type> AS OBJECT

(<nom-attribut1> [ref] <type>,

(<nom-attribut2> [ref] <type>,

… (<nom-attributn> [ref] <type>,

[définition méthodes] )

/

Remarque : après la création d’un ADT et uniquement dans ce cas ‘/’ remplace le ‘;’ pour terminer un ordre SQL


Address

Num Street City Country

CREATE TYPE Address AS OBJECT (

Num INTEGER,

Street VARCHAR(20),

City CHAR(15),

Country CHAR(10));

ADT de type OBJET: Exemple


ADT de type OBJET: Utilisation

• Les types objet peuvent être utilisés comme:

• Domaine de valeur d’une colonne d’une table

Ex: CREATE TABLE Contacts (contact Person, date DATE)

• Domaine de valeur d’un attribut d’un autre type défini par l’utilisateur

Ex: CREATE TYPE contacts (contact Person, date DATE)

• Domaine de relation

Ex: CREATE TABLE LesPersonnes OF Person Chaque ligne de la table LesPersonnes: type Person


Catégories d’ADT: Héritage type

• La possibilité de définir un sous-type d'un seul super type existant.

• Un type hérite des méthodes et de la structure de données de son super type.

• La possibilité de redéfinir des méthodes existantes, en définir de nouvelles et spécialiser la structure de données.

• Le mot clé UNDER désigne un type qui hérite d’un autre type.


ADT : Héritage type Exemple:

CREATE TYPE personne (

nom VARCHAR(10),

adresse VARCHAR(30),

Date naissance DATE)

/

CREATE TYPE etudiant UNDER personne (

universite VARCHAR(20),

no-etudiant INTEGER);

Super Type

Sous Type


Catégories d’ADT: type Collection• Permet de créer des attributs multi-valués

• Plusieurs catégories de type collection• SET• LIST• ARRAY ....

• Ces collection permettent de représenter les associations 1:N et N:M

• Ils ont des constructeurs de même nom que le nom du type:

CREATE TYPE <nom-type> AS (Table | Varray|..(<longueur>))

OF <type>;


Type Collection: SET, MULTILIST,LIST

• Ensembles (sans doublons) représentés par SET(T) avec T désigne un type de données quelconque.

• Sacs (avec des doublons) sont des ensembles avec des éléments en double,représentés par MULTISET(T)

• Listes (ordonnées et indexés) représentées par LIST(T)

Personne

age nom{prenom} phone

Liste_prenoms Tel

Ex: CREATE TYPE personne (age INTEGER, nom VARCHAR, liste_prenoms LIST (varchar), Tel SET (phone)) ;


Type Collection: ARRAY, VARRAY

• ARRAY: Tableau à une dimension (ensemble ordonné d’éléments de même type).

• VARRAY: Tableau de taille variable (variable- array)

• Syntaxe:• CREATE TYPE <nom-type1> AS VARRAY (nb-max) OF

<nom-type2>

• Définit un type tableau nommé nom-type1 de maximum éléments nb-max de type nom-type2.

• nom-type2 peut être n’importe quel type


Type Collection: VARRAY

CREATE TYPE Prénoms

AS VARRAY (3) OF VARCHAR (20) ;

• Le type VARRAY nommé Prénoms ne peut pas posséder

plus de 3 éléments

• Chaque élément est de type VARCHAR (20)

EXEMPLE:


Type Collection: VARRAY (Utilisation)

• Le type VARRAY peut être utilisé comme:

• Domaine de valeur d’une colonne d’une table

Ex: CREATE TABLE Personne (nom VARCHAR (20)

telephone TELEPHONES)


Ex: CREATE TYPE Personne (nom VARCHAR (20) telephone

TELEPHONES)

• Dans une autre collection (collection de collection)


Type Collection: NESTED TABLE• Nested table est une table à l'intérieur d'une autre table.

• Utiliser particulièrement dans la modélisation des relations 1-N

• Ensemble non ordonné d’éléments de même type.

• Valeur multivaluée de type table: Pas de nombre maximum de valeurs (non fini), Pas d'ordre et Indexable.

• Syntaxe : CREATE TYPE nom_type1 AS TABLE OF nom_type2

• ORCALE crée physiquement une table annexe dans laquelle il stockera les tuples de l'attribut de type Nested table :

NESTED TABLE nom-attribut-de-type-table-imbriquée STORE AS nom-table-annexe ;


Type Collection: NESTED TABLE (Utilisation)

Attention : Une Nested Table n’est pas une table objet ; il

n’associé pas une référence OID à un élément de collection.

• Les types NESTED TABLES peuvent être utilisés comme:

• Domaine de valeur d’une colonne d’une tableEX: CREATE TABLE Personnes (nom VARCHAR (15), prenom

liste_prenom, age INTEGER, voitures ens_voiture) % collection NESTED TABLE voitures STORE AS Toutes_voitures;


Ex: CREATE TYPE ens_voiture AS TABLE OF Tvoiture;

• Aussi dans une autre collection (collection de collection) Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

Type Collection: NESTED TABLE (Exemple)

• Création du type stockant chaque ligne de voitureCREATE TYPE Tvoiture AS OBJECT(modele VARCHAR (15), annee, DATE, no INTEGER) /

• Création d'une table contenant les voitures:CREATE TYPE ens_voiture AS TABLE OF Tvoiture /

• Création de la table personne qui contient une colonne de type NESTED TABLE:CREATE TABLE Personne (nom VARCHAR (15), prenom liste_prenom, age INTEGER, voitures ens_voiture) % collection NESTED TABLE voitures STORE AS Toutes_voitures;

nom prenom age voitures

‘Liera’ ‘Dupond’

(‘Pierre’,’Paul’) (‘Zoé’,’Marie’)

3540

Id1Id2

Nested_table_Id Tvoiture

Id1Id1Id2Id1

‘207’‘407’ ‘308’ ‘206’

‘2010’ ‘2003’ ‘2011’ ‘2005’

123

121Index


Manipulation de type NESTED TABLE • Une collection dans une Nested Table peut être

• Insérée • Supprimée• Mise à jour

Exemple: INSERT INTO Personnes VALUES (‘Durant’, liste_prenom(‘Karim’, ’Amin’), ’35’, ens_voiture (Tvoiture(‘2ch’, 1975, 128), Tvoiture(‘Mégane’, 1998, 179)));

• Parcourir dans une Nested Table

Exemple: Numéro des voitures des personnes :SELECT v.No FROM personnes p, TABLE (p.voitures) v;


Passage de modèle R vers modèle OR• L’association 1:N Le type de l’attribut doit être une collection (table ou varray)

Exemple: personne (Num, nom, prénom , Date_naissance)Enfants (nom, prénom , Date_naissance, Num_p )

CREATE TYPE personne;

CREATE TYPE ens-enfant AS table OF REF personne;

CREATE TYPE personne AS OBJECT

(Num INTEGER, nom varchar(10), prénom varchar(20),

Date_naissance DATE, enfants ens-enfant, Num_p INTEGER);


Passage de modèle R vers modèle OR• L’association N:M Le type des attributs doit être une collection

Exemple:

personne (Num, nom, prénom , Date_naissance);Voiture (Matricule, modèle , type);Conduire (Num, Matricule);

Create type Voiture;

Create type ens-voitures as table of ref Voiture;

Create type Personne as object (conduit ens-voitures… );

Create type ens-pers as table of ref personne;

Create type Voiture as object (conduite-par ens-pers, …);


4.2. Création des tables à partir des types

• Les données d’un type ne sont persistantes que si elles sont rangées dans une table.

• On peut créer des tables à partir d’un type de données, dans ce cas ces tables sont dites «tables objet-relationnelle»

• Ses lignes sont considérées comme « objets », avec la portée globale d’OID pour les désigner.

• La syntaxe suivante permet de créer une table objet :

CREATE TABLE nom_table OF nom_type[ ( spécification_contrainte 1[, …, spécification_contrainte n ] ) ]


Associer aux tables les contraintes usuelles de SQL :

• PRIMARY KEY (nom-col*)

• UNIQUE (nom-col*)

• FOREIGN KEY (nom-col*) REFERENCES nom-table

[(nom-col*)] [action]

• CHECK (condition particulière sur les valeurs)

Exemple:

CREATE TABLE Person_table OF Person (

PRIMARY KEY (NumAssu));

Création des tables à partir des types


Création des tables à partir des types

• Possibilité de créer à la fois une table avec un nouveau type

ADT grâce à la syntaxe suivante :

CREATE TABLE <nom de table> OF NEW TYPE <nom de type>

<corps de l’ADT> ;

• La manipulation des tables objet-relationnelles:-Insertion,- Suppression,-Mise à jour,…..


Manipulation des tables à partir des types

1. Suppression d’une table Objet-relationnelleDROP TABLE <nom_table>. Ex : DROP employé ;

2. Modification de la définition d’une table Objet-relationnelleALTER TABLE ne peut s’appliquer qu’ à une table relationnelle. Modifier la définition d’une table objet-relationnelle revient à modifier le type d’objets sur lequel elle est définie (CREATE OR REPLACE TYPE).

3. Insertion d’une instance objet :INSERT INTO nom_table_objet_relationnelleVALUES( nom_type_objet(val 1 , …, val k ) )Avec: vali= valeur atomique/nouvelle instance de type d’objets


Ou bien la totalité des valeurs figurent dans la base de données, et dans ce cas on remplace VALUES par la sous-requête chargée de ramener toutes les valeurs.val i = (select donnée from table …) *INSERT INTO nom_table ( SELECT données FROM table(s) WHERE condition(s))

• Dans le cas où le type est un type utilisé par un autre type, l’utilisation du constructeur du type est obligatoire :Ex : INSERT INTO employé VALUES (1, 'Toto', 12000, adresse_type (12, 'Victor Hugo', 'Nice'));

4. Mise à jour d’une table Objet-relationnelle-UDPADTE-DELETE




• UPDATE:UPDATE nom_table SET nom_att = nouvelle_valeur [ WHERE condition(s)]

Exemple :UPDATE employé eSET salaire = 72000,e.adresse.numero = 24WHERE nom = 'Dupond';

Remarque: SQL3 fournit aussi la notation «..» pour désigner un attribut d’une colonne de type ADT.EX: e.adresse..numero = 24



• DELETE: supprimer des éléments selon certaines conditions.DELETE FROM nom_table [WHERE condition(s)]

5. Consultation d’une table Objet-relationnelle (SELECT)Exprimer le chemin qui permet de naviguer à travers les objets.

SELECT <nom de table>.<nom type ADT>.<nom type>FROM <nom de table>

Exemple : Dans la table employé, l’affichage du nom et la rue d’un employé est :SELECT nom, e. adresse_type.rueFROM employé e


6. Héritage d’une table Objet-relationnelle (INHERITS)

CREATE TABLE document ( Titre VARCHAR (30), Auteur VARCHAR (30), Année INTEGER);

Nous voulons créer la table livre comme une sous table de la table document :

CREATE TABLE livre ( nb_pages INTEGER) INHERITS (document);



Comparaison avec le relationnel

• Accès en relationnel

• Accès en objet-relationnel

select p.effdate, p.name, p.vehicleyrfrom policy pwhere p.carmodel.model = ‘ferrari’

SELECT date, name, vehicleyrFROM policy, customers, vehiclesWHERE policy.custno = customers.custnoAND policy.vehicleno = vehicles.vehiclenoand model = ‘ferrari’


4.3. Définition d’attributs référence• SGBD relationnel:

clé = un ensemble minimum d’attributs• Danger lors des : - mises à jour de la clé - changements d'attribut clé• Identité dépendante de la valeur

• En SGBD Objet-relationnel:

OID= identificateur d’objet (Object IDentifier )• Indépendant de la valeur• Unique• Permanant • Immuable


4.3. Définition d’attributs référence• Chaque instance d’une table d’objets possède un identificateur d’objet qui :

• Identifie l’objet stocké dans ce n-uplet de façon unique,• C'est une colonne cachée, générée par le système,• Sert à référencer l’objet,• Oracle lui associe un index.

• Référence: Pointeur vers une instance d'une table objet

• Conserver dans les tables une référence à une valeur, plutôt qu’une clé étrangère.

• Permet de réduire les jointures.

Attention: impossible de référencer une collection !


Définition d’attributs référence• Création d'une référence par «REF»REF <nom du type>

Exemple: CREATE TYPE Tlogement AS OBJECT (Type VARCHAR(15), surface NUMBER(4), nbchambre INTEGER)/

Cas1: valeur structurée (des objets)CREATE TABLE Habitant1 (Num INTEGER, Nom VARCHAR(20), Prénoms prénoms_type, Habite Tlogement)/

Cas 2: OID de l’attribut HabiteCREATE TABLE Habitant2 (Num INTEGER, Nom VARCHAR(20), Prénoms Tprénoms, Habite REF Tlogement);


• La manipulation des attributs référence objet:

• REF (objet)=>l'OID de l'objet• DEREF (oid)=>valeur structurée: opération inverse de REF• VALUE (objet)=>valeur structurée.

• La manipulation de l’opérateur REF:

• Insertion avec référence.• Modification avec référence.• SCOPE.• REFERENCES.

…

Définition d’attributs référence


Opérateur REF

• Utilisation de l’opérateur REF qui renvoie l’adresse d’un n-uplet dans une table.

1. Insertion avec référence :

Exemple: Insérer dans TPersonnes une nouvelle personne: Duran née le 16/02/1960 dont la liste des prénoms {Pierre, Paul, Jacques}, de voiture modèle i30.

INSERT INTO TPersonnes VALUES (‘Duran’, liste_prénom(‘Pierre’, ‘Paul’, ‘Jacques’), ’16-05-1963’, (SELECT REF(v) FROM TVoitures v WHERE v. modèle = ‘i30’));


2. Modification avec référence : UPDATE

Exemple: Changer le modèle de voiture de l’année 2010 par I30.

UPDATE Personnes pSET p.voiture = (SELECT REF(v) FROM TVoitures v WHERE v. modèle = ‘I30’)WHERE p.voiture.annee =2010;

Attention: modifier une voiture à travers une personne est impossible !

Remarque : cette instruction peut très bien la valeur NULL dans la colonne voiture car le select renvoie NULL si la voiture de modèle I30 n’existe pas !

Opérateur REF


Référencer du vide

• Références qui n'existent plus.

• Pour tester l’existence d’une instance référencée :• Utilisation du prédicat IS DANGLING.

Exemple :

UPDATE Personnes SET voiture = NULLWHERE voiture IS DANGLING

• Ajouter la contrainte NOT NULL sur la colonne voiture:voiture REF voiture NOT NULL. Le type « REF Tvoiture » restreint le type référencé mais pas la table référencée.


• La clause SCOPE restreint la colonne référencée ; elle peut être ajoutée lors de la définition d’une table.

•voiture REF voiture_type SCOPE IS TVoiture.

• Cette expression indique que voiture référencera une ligne de la table TVoiture et pas une ligne d’une autre table créée à partir du type voiture_type.

• Une contrainte REFERENCES permet d’éviter les références perdues (comme avec les tables relationnelles) :

• voiture REF voiture_type REFERENCES TVoiture.

Référencer du vide


Références croisées • Les références croisées consistent à créer un type vide pour être référencé dans un second type et on modifie le premier type ensuite. On crée les tables en dernier.

Exemple :

CREATE TYPE Tvoiture /CREATE TYPE Tpersonne AS OBJECT(nom VARCHAR2(15), prenoms liste_prenom, date_naissance DATE, voiture REF Tvoiture) /CREATE TYPE Tvoiture AS OBJECT(modele VARCHAR2(15), annee DATE, No INTEGER, personne REF Tpersonne) /

CREATE TABLE voitures OF Tvoiture;CREATE TABLE personnes OF Tpersonne;


Opérateur DEREF

• L’opérateur DEREF permet de déréférencer une référence pour obtenir l’objet référencé.

Exemple:SELECT p.Nom, p.prénoms, DEREF(p.voiture) FROM TPersonne p WHERE p.voiture.année > 2010;

Nom {Prénoms} date_naissance @ voiture

prénoms_type

DupondMartine 16-05-1963 voiture_type (12584,

Range Rover , 2012)RobertSmith


• Pour obtenir la valeur d’un objet, on utilise la fonction VALUE, qui prend la référence d’un objet et renvoie sa valeur.

Exemple 1: type référencé (voiture)

Opérateur VALUE

SELECT p.nom, p.voiture FROM Personnes p - renvoie le nom et l’oid de voiture

SELECT p.nom, VALUE(p.voiture) FROM Personnes p - renvoie pour chaque personne p le nom et la valeur de

sa voiture

• Avec select : récupération de l’ensemble de valeurs des attributs d’une table.


Opérateur VALUE

• Select value(p) from Projets p; Tprojet(1, ‘Amber’) Tprojet(2, ‘Murmur')

Exemple 2: (type non référencé)

CREATE TYPE Tprojet AS OBJECT (Pno NUMBER, pnom VARCHAR2(20)) / CREATE TABLE Projets OF

Tprojet;

• Select * from Projets;1 ¦ ‘Amber’

2 ¦ ‘Murmur'


4.4. Manipulation avancée de BD

• Le langage SQL a été étendu pour permettre la définition et l’interrogation des BD objet-relationnelles.

• Une manipulation avancée nécessite l’utilisation des:

• Méthodes (fonctions et procédures),• Triggers,• Requêtes récursives,• Requêtes à préférences

…


Méthode: définition & activation

• Une méthode est un sous-programme déclaré dans la spécification d'un type objet par le mot-clé MEMBER.

• La méthode ne peut pas avoir le même nom que le type objet ni qu'aucun des attributs.

• Il existe deux types de méthode: les Procédures et les Fonctions.

• CREATE TYPE nom-type AS OBJECT( déclaration des attributs, déclaration des signatures des méthodes )


Méthode: définition & activation

• Signature d'une méthode

• MEMBER FUNCTION nom-méthode (nom-paramêtre 1 [ IN / OUT ] type 1 , ….) RETURN type n

• MEMBER PROCEDURE nom-méthode (nom-paramêtre 1 [ IN / OUT ] type 1 , ….)

• Le code peut contenir des :• instructions PL/SQL (ou JAVA)• instructions SQL• appels de méthodes


Corps des méthodes

CREATE [OR REPLACE ] TYPE BODY nom_type ISspécification_méthode 1 IS[Declarations_variables_curseurs_et_exceptions ]BEGINInstructions_PL/SQL_ou_requêtes_SQL[ EXCEPTION Traitement_des_erreurs ]END nom_méthode 1 ;[spécification_méthode 2 IS … END nom_méthode 2 ;…]END ;


Le paramètre SELF• Toute méthode d'un type objet accepte une instance de ce type comme premier paramètre (SELF).

• Ce paramètre peut être implicitement ou explicitement déclaré. Il est proche de paramètre THIS du JAVA.

• Par défaut, le mode de déclaration est implicite : IN pour les fonctions, IN OUT pour les procédures.

• SELF.nom_attribut

• Cette expression peut aussi bien apparaître dans une instruction (affectation, appel de fonction ou de procédure, …) que dans une requête SQL.

La méthode CONSTRUCTOR

• Chaque objet est pourvu d'une méthode de construction appelée CONSTRUCTOR, définie par le système avec le même nom que le type objet.

• Une méthode de construction est une fonction utilisée pour initialiser et retourner une instance de ce type objet.

• Le type des paramètres ainsi que leur ordre sont identiques à ceux des attributs du type objet.

• La suppression du corps d’un type d’objets:DROP TYPE BODY nom_type


Exemple : définition, appel CREATE TYPE Vehicule_type AS OBJECT (

numero INTEGER, modele VARCHAR (20), quantité_essence INTEGER,

MEMBER PROCEDURE ajouter_essence (quantite IN INTEGER));

CREATE TYPE BODY Vehicule_type IS

BEGIN

CONSTRUCTOR FUNCTION Vehicule_type (numero IN INTEGER)

RETURN SELF AS resultat IS

BEGIN

SELF.numero := numero;

END;

MUMBER PROCEDURE ajouter_essence (quantite IN INTEGER)

BEGIN

SELF. quantité_essence := quantite;

END;

END;


Les TRIGGERS

• Un triggers (ou déclencheur) est une règle, dite active, de la forme : ”événement-condition-action”.

• Procédure stockée dans la base qui est déclenchéeautomatiquement par des événements spécifiés par leprogrammeur et ne s’exécutant que lorsqu’une condition estsatisfaite.

• Les triggers permettent :

1. La possibilité d’éviter les risques d’incohérence dus à la présence de redondance.

2. L’enregistrement automatique de certains événements.


3. La spécification de contraintes liées à l’évolution de données.

Exemple : un salaire ne peut qu’augmenter.

4. De définir toutes règles complexes liées à l’environnementd’exécution (restrictions sur des horaires, des clients, . . .).

• La séquence Evénement-Condition-Action est exécutée selon les trois étapes suivantes :

1. Trigger déclenché par un événement spécifié par le programmeur.

Ex : Insertion, destruction, modification sur une table.

Les TRIGGERS


2. Test de la condition : si cette dernière n’est pas vérifiée, alors l’exécution s’arrête.

3. Si vérifiée, l’action est réalisée avant ou après un évènement.

• Un seul trigger utilisé par événement sur une table.

• La syntaxe suivante permet de créer un trigger :

CREATE TRIGGER <nom-trigger> BEFORE/AFTER <Événement> OF <Attribut> ON <Table> REFERENCING OLD/NEW AS <action> •Pour supprimer un Trigger : DROP TRIGGER <nom de trigger>

Les TRIGGERS


• BEFORE/AFTER: la possibilité de déclencher avant ou après l'événement.

• <événement> = INSERT, UPDATE, DELETE. Précise le ou les évènements concernés par le déclenchement. S’il y a plusieurs événements, on les sépare avec ”OR”.

• REFERENCING OLD/NEW AS : la possibilité de référencer les valeurs avant ou après mise à jour.

• <action> = opération sur table (INSERT..) avec condition possible («WHEN» «WHERE»)

Les TRIGGERS


Exemple 1: Définition d’un déclencheur après toute mise à jour du champ «salaire» de la table Pilote qui met à jour l’attribut «augmentation» de la table CUMUL.

CREATE TRIGGER augmenter-trigger AFTER UPDATE OF salaire ON Pilote REFERENCING OLD AS ancien_salaire, NEW AS nouveau_salaire UPDATE CUMUL SET Augmentation = Augmentation + nouveau_salaire - ancien_salaire

Exemple 2: Vérification qu’un prix ne peut pas baisser. CREATE TRIGGER pas_baisse_prix BEFORE UPDATE OF prixUnitaire ON Article FOR EACH ROW WHEN (OLD.prixUnitaire > NEW.prixUnitaire)BEGINraise_application_error(-20100, ”le prix d’un article ne peut pas diminuer”) ;

END ;

Les TRIGGERS: Exemples


Vues Objet-Relationnelles

• Une vue objet-relationnelle permet de visualiser les données différemment de la façon dont elles sont organisées.

• Une vue classique peut être construite sur une seule table ou résulter de la jointure de plusieurs tables:

• CREATE VIEW nom_vue AS [(att1_vue, att2_vue, …)] AS

SELECT att1, att2, … Requête SQL FROM nom_table [, autre_table…]

[ WHERE condition selection et/ou jointure ]


Vues Objet-Relationnelles• La création d’une vue objet-relationnelle se fait à partir d’une requête SELECT permettant d’extraire les données de la table, ou des tables.

CREATE VIEW nom_vue_or OF type_objet WITH OBJECT IDENTIFIER ( att_oid ) [UNDER nom_vue_mère ] AS SELECT att1, att2 [,CAST(MULTISET(…)][, type_nuplet (att_np1, att_np2, …)] … [ REF(alias)] [ MAKE_REF(nom_autre_vue, att_oid)] FROM nom_table alias [, nom_table2 alias2]* WHERE condition(s)


Vues Objet-Relationnelles: Exemples

CREATE VIEW vo_eleve OF ty_eleveWITH OBJECT IDENTIFIER (att_matricule)AS SELECT matricule , nom , prenom , d_naiss ,ty_adresse ( rue_num , code_post , ville) AS adresseFROM t_eleve t_e ;

• CAST et MULTISET sont utilisés pour gérer les éléments qui ne respectent pas la seconde forme normale dans le type :

• CAST s'occupera de forcer l'élément retourné dans un type déclaré.

• MULTISET prend en charge la création d'une collection (VARRAY ou Nested Table).

Exemple 1:


Exemple 2:

CREATE VIEW vo_classe OF ty_classe

WITH OBJECT IDENTIFIER (att_code)

AS SELECT code, niveau, obligation,

CAST(MULTISET(

SELECT Ref(vo_e)

FROM vo_eleve vo_e, t_eleve t_e

WHERE vo_e.att_matricule = t_e.matricule

Vues Objet-Relationnelles: Exemples


Requêtes récursives• Une requête récursive est une requête dans laquelle une requête initiale est exécutée de façon répétée pour retourner des sous-ensembles de données jusqu'à l'obtention de l'ensemble de résultats complet.

• Elle permet de simplifier le code

• SQL3 : clause WITH RECURSIVE … AS

• La récursivité est utilisée dans les applications:• Utilisant des graphes (Ex: Logistique, Réseau de transport,

de télécom,…)

• Utilisant des données semi-structurées (documents XML,..)

• Basées sur la déduction logique (système expert,…)Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma Module: Base de Données Avancée © A.AGGOUNE Niveau: Master2AC Université de Guelma

Requêtes récursives: Arbres

• La récursivité en SQL est réalisée par plusieurs structures de table:• Arbre (TREE)• Graphe ….

• Une des méthodes pour parcourir l’arbre d’une requête récursive est la définition d’un point de départ au processus de récursion qui se fait avec 2 requêtes liées.

• La première requête indique où l’on doit commencer • La seconde où l’on doit se rendre ensuite.• Ces 2 requêtes doivent être jointes par l’opérateur UNION

ALL.• La hiérarchie est représentée grâce à une auto-référence



Requêtes récursives: Arbres• Pour obtenir la hiérarchie d’une requête récursive, il faut commencer par déterminer le point de départ qui joue le rôle d’une clé étrangère.

Exemple:Nous désirons obtenir la hiérarchie du « Poste X » de la table Travail:NiveauId INTEGER PRIMARY KEY, Desc VARCHAR(50), fkNiveauParent INTEGER FOREIGN KEY

Compagnie XYZ | -- Division XYZ | |-- Département 1 | |-- Département 2| | |-- Poste X|-- Division XYW | |-- Département 6| | |-- Poste U| | |-- Poste W



Requêtes récursives: ArbresSELECT Desc, fkNiveauParent FROM Travail WHERE Desc = "Poste X" UNION ALL SELECT Desc, fkNiveauParent FROM Travail

• Le cycle de récursion est fait par la jointure entre la table OR et la table tree. WITH tree (src, dest) AS (requetes) SELECT * FROM tree

Dans l’exemple précédent:

WITH tree (desc, id) AS( SELECT Desc, fkNiveauParent FROM Travail WHERE Desc = "Poste X" UNION ALL SELECT Desc, fkNiveauParent FROM Travail w INNER JOIN tree ON tree.id = w.NiveauId ) SELECT * FROM tree


• Limite de récursivité en SQL3

• Apparaît qu'une seule fois dans la clause FROM

• Pas de négation dans la récursivité

Requêtes récursives

Résultat Donnée id --------------- ------ --- Poste X 8 Départ 2 6 Division XYZ 3 Compagnie XYZ NULL


Requêtes à préférences

• Requêtes à préférences : requêtes qui expriment des propriétés désirées sur les résultats issus d’un processus de recherche.

• Préférence est une relation binaire entre les tuples présents dans la réponse à une requête.

• Soit une relation r de schéma R(A 1 , .., A k) tel que U i , 1≤i≤k, est le domaine de l'attribut A i , une relation est une relation de préférence sur r si elle constitue un sous-ensemble de (U1 x…xU k )x(U 1 x…xU k )

• Intuitivement, est une relation binaire entre les tuples de la même relation d'une BD.


• On dit que le n-uplet t 1 domine (ou est préférable à) le n-uplet t 2 dans le contexte de la relation si t 1 t 2

• Si ni t1 t2 et ni t2 t1 ne sont vérifiées, on dit que t1 et t2 sont incomparables : t1 ~ t2

• Une requête à préférence RP permet de:• Capturer les profils utilisateurs (personnaliser).• Formuler des politiques afin d'améliorer et automatiser la

prise de décision.• Requête flexible est une RP modélisée par la logique floue

Exemple: SELECT * FROM HotelsWHERE city = 'Nassau‘SKYLINE OF price MIN, distance MIN;

Requêtes à préférences


Langage SQL3 et SGBD Relationnel-objet

• Extensions RICE du modèle relationnel :

• Réutilisation (Héritage, polymorphisme)• IDENTITE et références• Objets Complexes (Collections et produit)• Encapsulation (types et fonctions utilisateur)

• Extensions du langage SQL (SQL3) pour la recherche et la modification des données. Il est défini par ISO.

• SGBD relationnels étendus utilisent le langage SQL étendu

• L’OR supporte l’héritage de type pour profiter du polymorphisme et faciliter la réutilisation


•1970: invention du modèle relationnel (CODD)

• 1981: début des projets Système R et Ingres

• 1986: normalisation de SQL1

• 1990: extensions aux objets et règles

• 1990: normes SQL2

• 1999: normes SQL3 (SQL 99)

• Vers un SQL4….

SQL: quelques dates….


• SQL92 ne permet pas de créer de nouveaux types manque de souplesse et une interface difficile avec les applications orientées objet

• SQL3 ( ou SQL99) standard en évolution, est proposé par tous les grands constructeurs (Oracle, Sybase, IBM, etc.)

• Utilisant les concepts objet • Programmation intégrée et modulaire • Compatibilité avec le relationnel.• Possibilité de structuration en modules (PSM) procedure

storage module

SQL3 (SQL99)


• De multiples facettes :– Un langage de définition de types– Un langage de programmation– Un langage de requêtes– Un langage temporel ...

• Manipulation avancée:– Définition des types complexes avec héritage– Appels de méthodes en résultat et qualification– Imbrication des appels de méthodes– Surcharge d'opérateurs– Triggers

SQL3 (SQL99)


SQL3 – Les composants• Part 1: Framework: description de la structure du document

• Part 2: Foundation: Le noyau de spécification, incluant les ADT.

• Part 3: SQL/CLI: l’interface d’appel client, CLI (Call Level Interface).

• Part 4: SQL/PSM: le langage de spécifications de procédures stockées

• Part 5: SQL/Bindings: les liens SQL dynamique et SQL repris de SQL-92.

• Part 6: SQL/XA: Une spécification de l’interface XA pour moniteur transactionnel.

• Part 7: SQL/Temporal: Le support du temps dans SQL3

•Autres spécifications : ADTs multimédia (SQL/MM), RDA protocol,…


SQL3 - Les procédures (PSM)

Langage de programmation de procédures:

– déclaration de variables– assignation– conditionnels CASE, IF– boucles LOOP, FOR– exceptions SIGNAL, RESIGNAL– De stockage de procédures dans la base– possibilité d’appel des procédures et fonctions externes


SQL3 - Les Objets• Extensibilité des types de données

– Définition de types abstraits– Possibilité de types avec ou sans OID

• Support d’objets complexes– Constructeurs de types (tuples, set, list, …)– Utilisation de référence (OID)

• Héritage – Définition de sous-types– Définition de sous-tables

• Support de large objets : – Type BLOB (Binary Large Object). Ex: picture BLOB(12M)

– Type CLOB (Character Large Object). Ex: resume CLOB(75K)


Extension des SGBD relationnels:

• Ajout au relationnel des propriétés les plus utiles de l'objet

• Possibilité de créer des types complexes et des tables de types

complexes

• Ajout des types LOB pour données texte, multimédia, etc.

• Identifiants d'objet et types référence

• Possibilité de définir des types abstraits et des méthodes sur

ces types

• Héritage entre types

SGBD objet-relationnels


SGBD objet-relationnels

Exemples : • Nouveaux: Illustra, UniSQL, ODB II, Versant• Relationnels étendus ("universels"): POSTGRES ,

Ingres, Oracle 9i, DB2 , Informix


La Fin

Documents

chapitre 01-Le relationnel étendu