LE MODELE RELATIONNEL Invent par T Codd IBM

LE MODELE RELATIONNEL Inventé par T. Codd (IBM Recherche) Publication ACM 1970 1. Concepts pour la description 2. Concepts pour la manipulation 3. Concepts additionnels 1

1. CONCEPTS DESCRIPTIFS w Ensemble de concepts pour formaliser la description d'articles de fichiers plats w Modèle standardisé mais extensible n n n G. Gardarin Introduction de types de données variés (SQL 2) Introduction de la dynamique (produits, SQL 3) Introduction des objets (SQL 3) 2

Domaine w ENSEMBLE DE VALEURS w Exemples: n n n n G. Gardarin ENTIER REEL CHAINES DE CARACTERES EUROS SALAIRE = {4 000. . 100 000} COULEUR= {BLEU, BLANC, ROUGE} POINT = {(X: REEL, Y: REEL)} TRIANGLE = {(P 1: POINT, P 2: POINT, P 3: POINT)} 3

Produit cartésien w LE PRODUIT CARTESIEN D 1 x D 2 x. . . x Dn EST L'ENSEMBLE DES TUPLES (NUPLETS) <V 1, V 2, . . Vn> TELS QUE Vi� Di Bleu Vrai w Exemple: Bleu Faux n n G. Gardarin D 1 = {Bleu, Blanc, Rouge} D 2 = {Vrai, Faux} Blanc Rouge Vrai Faux 4

Relation w SOUS-ENSEMBLE DU PRODUIT CARTESIEN D'UNE LISTE DE DOMAINES w Une relation est caractérisée par un nom Coul. Vins w Exemple: n n G. Gardarin D 1 = COULEUR D 2 = BOOLEEN Coul Bleu Choix Faux Blanc Vrai Rouge Vrai 5

Attribut w VISION TABULAIRE DU RELATIONNEL n n n Une relation est une table à deux dimensions Une ligne est un tuple Un nom est associé à chaque colonne afin de la repérer indépendamment de son numéro d'ordre w ATTRIBUT n n G. Gardarin nom donné à une colonne d'une relation prend ses valeurs dans un domaine 6

Graphe d'une relation w Relation binaire R(A 1, A 2) w Une relation n-aire est une généralisation à n dimensions • 4 3 G. Gardarin a b DOM(A 2) 2 1 DOM(A 1) c • DOM(A 2) 1 2 3 4 • • • a b c DOM(A 1) 7

Exemple de relation VINS CRU MILL REGION COULEUR CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC TAVEL 1986 RHONE ROSE CHABLIS 1986 BOURGOGNE BLANC ST-EMILION 1987 BORDELAIS ROUGE G. Gardarin 8

Clé w GROUPE D'ATTRIBUTS MINIMUM QUI DETERMINE UN TUPLE UNIQUE DANS UNE RELATION w Exemples: n n {CRU, MILLESIME} DANS VINS ==> NV NSS DANS PERSONNE w CONTRAINTE D'ENTITE n G. Gardarin Toute relation doit posséder au moins une clé documentée 9

Schéma w NOM DE LA RELATION, LISTE DES ATTRIBUTS AVEC DOMAINES, ET LISTE DES CLES D'UNE RELATION w Exemple: n n VINS(NV: Int, CRU: texte, MILL: entier, DEGRE: Réel, REGION: texte) Par convention, la clé primaire est soulignée w INTENTION ET EXTENSION n n Un schéma de relation définit l'intention de la relation Une instance de table représente une extension de la relation w SCHEMA D'UNE BD RELATIONNELLE n G. Gardarin C'est l'ensemble des schémas des relations composantes 10

Clé Etrangère w GROUPE D'ATTRIBUTS DEVANT APPARAITRE COMME CLE DANS UNE AUTRE RELATION w Les clés étrangères définissent les contraintes d'intégrité référentielles n n n G. Gardarin Lors d'une insertion, la valeur des attributs doit exister dans la relation référencée Lors d'une suppression dans la relation référencée les tuples référençant doivent disparaître Elles correspondent aux liens entité-association obligatoires 11

Exemple de Schéma w EXEMPLE BUVEURS (NB, NOM, PRENOM, TYPE) VINS (NV, CRU, MILL, DEGRE) ABUS (NB, NV, DATE, QUANTITE) w CLES ETRANGERES ABUS. NV REFERENCE VINS. NV ABUS. NB REFERENCE BUVEURS. NB G. Gardarin 12

Diagramme des Liens BUVEURS NB NOM PRENOM ABUS G. Gardarin TYPE NB NV VINS DATE NV CRU MILL. DEGRE QUANTITE 13

Concepts Descriptifs : Bilan w RELATION ou TABLE w ATTRIBUT ou COLONNE w DOMAINE ou TYPE w CLE ETRANGERE G. Gardarin Questions ? 14

Synthèse : Create Table w CREATION DES TABLES EN SQL n n n CREATE TABLE <relation name> (<attribute definition>+) [{PRIMARY KEY | UNIQUE} (<attribute name>+)] w avec : n n <attribute definition> : : = <attribute name> <data type> [NOT NULL [{UNIQUE | PRIMARY KEY}] ] w Exemple : n n G. Gardarin n CREATE TABLE VINS ( NV INTEGER PRIMARY KEY CRU CHAR VARYING MILL INTEGER NOT NULL, DEGRE FIXED 5. 2 ) 15

2. CONCEPTS MANIPULATOIRES w Un ensemble d'opérations formelles n Algèbre relationnelle w Ces opérations permettent d'exprimer toutes les requêtes sous forme d'expressions algébriques w Elles sont la base du langage SQL n n Paraphrasage en anglais des expressions relationnelles Origine SEQUEL w Ces opérations se généralisent à l'objet n G. Gardarin Algèbre d'objets complexes 16

Opérations Ensemblistes w Opérations pour des relations de même schéma n n n UNION notée INTERSECTION notée DIFFERENCE notée — w Opérations binaires n Relation X Relation --> Relation w Extension n n G. Gardarin Union externe pour des relations de schémas différents Ramener au même schéma avec des valeurs nulles 17

Projection w Elimination des VINS Cru Mill Région Qualité attributs non désirés et VOLNAY 1983 BOURGOGNE A VOLNAY 1979 BOURGOGNE B suppression des tuples CHENAS 1983 BEAUJOLAIS A JULIENAS 1986 BEAUJOLAIS C en double w Relation -> Relation Cru, Région notée: (VINS) Cru Région VOLNAY BOURGOGNE A 1, A 2, . . . Ap (R) CHENAS BEAUJOLAIS JULIENAS BEAUJOLAIS G. Gardarin 18

Restriction w Obtention des tuples de R satisfaisant un critère Q w Relation ->Relation, notée Q(R) w Q est le critère de qualification de la forme : n n Ai Valeur =�{ =, <, >=, >, <=, !=} w Il est possible de réaliser des "ou" (union) et des "et" (intersection) de critères simples G. Gardarin 19

Exemple de Restriction VINS Cru VOLNAY Mill 1983 1979 CHENAS 1983 JULIENAS 1986 Région Qualité BOURGOGNE A BOURGOGNE B BEAUJOLAIS A C MILL>1983 VINS G. Gardarin Cru Mill JULIENAS 1986 Région Qualité BEAUJOLAIS C 20

Jointure w Composition des deux relations sur un domaine commun w Relation X Relation ->Relation n notée w Critère de jointure n n G. Gardarin Attributs de même nom égaux : l Attribut = Attribut l Jointure naturelle Comparaison d'attributs : l Attribut 1 � Attribut 2 l Théta-jointure 21

Exemple de Jointure VINS LOCALISATION VINSREG G. Gardarin Cru VOLNAY Mill 1983 1979 Qualité A B CHABLIS JULIENAS 1983 1986 A C Cru Région Qual. Moy VOLNAY Bourgogne A CHABLIS Californie B Cru Mill Qualité Région Qual. Moy VOLNAY 1983 A Bourgogne A VOLNAY CHABLIS 1979 1983 B A A Bourgogne Californie A A B 22

Complétude w L'algèbre relationnelle est complète n Les cinq (sept) opérations de base permettent de formaliser sous forme d'expressions toutes les questions que l'on peut poser avec la logique du premier ordre (sans fonction). w Exemple : n Nom et prénom des buveurs de volnay 1988 ? PROJECT (NOM, PRENOM, RESTRICT(CRU="VOLNAY" et MILL =1988, JOIN(VINS, ABUS, BUVEURS))) G. Gardarin 23

SQL w Une requête SQL est un paraphrasage d'une expression de l'algèbre relationnelle en anglais w Requête élémentaire : SELECT A 1, A 2, …Ap FROM R 1, R 2, …Rk WHERE Q [{UNION |INTERSECT | EXCEPT } … ] w �Sémantique du bloc select : PROJECT A 1, A 2, …Ap ( RESTRICT Q ( PRODUIT ( R 1, R 2, …, Rk) ) ) G. Gardarin 24

3. CONCEPTS ADDITIONNELS w Ensemble de concepts pour : n n n Etendre les fonctionnalités de manipulation Décrire les règles d'évolution des données Supporter des objets complexes (SQL 3) w Introduits progressivement dans le modèle : n n n G. Gardarin Complique parfois le modèle Standardisés au niveau de SQL 3 (1999) Des extensions multiples … 25

Fonction et Agrégat w FONCTION n n Fonction de calcul en ligne appliquée sur un ou plusieurs attributs Exemple : DEGRE * QUANTITE / 100 w AGREGAT n G. Gardarin Partitionnement horizontal d'une relation selon les valeurs d'un groupe d'attributs, suivi d'un regroupement par une fonction de calcul en colonne (SUM, MIN, MAX, AVG, COUNT, …) 26

Exemples d'agrégats VINS CRU DEGRE MILL QUANTITE CHABLIS 1977 10. 9 100 CHABLIS 1987 11. 9 250 VOLNAY 1977 10. 8 400 VOLNAY 1986 11. 2 300 MEDOC 1985 11. 2 200 SELECT AVG(DEGRE) FROM VINS; SELECT CRU, SUM(QUANTITE) FROM VINS AVG GROUP BY CRU; DEGRE 11. 2 SUM G. Gardarin CRU QUANTITE CHABLIS 350 VOLNAY 700 MEDOC 200 27

Vue w Relation d'un schéma externe déduite des relations de la base par une question w Exemple : Gros. Buveurs l l CREATE VIEW Gros. Buveurs AS SELECT NB, Nom, Prénom, FROM Buveurs, Abus WHERE Buveurs. NB = Abus. NB and Abus. Quantité > 100 w Calcul de la vue n n G. Gardarin Une vue est une fenêtre dynamique sur la BD et est recalculée à chaque accès. Une vue peut être matérialisée (vue concrète). 28

Déclencheur (Trigger) w Action base de données déclenchée suite à l'apparition d'un événement particulier w Forme : n n n G. Gardarin {BEFORE | AFTER} <événement> THEN <action> Un événement peut être : l une opération sur une table (début ou fin) l un événement externe (heure, appel, etc. ) Une action peut être : l une requête BD (mise à jour) l Une annulation (abort) de transaction l l'appel à une procédure cataloguée 29

Déclencheur avec condition (Règle) w Il est possible d'ajouter une condition afin de déclencher l'action seulement quand la condition est vérifiée n Une condition est une qualification portant sur la base. w Exemples : BEFORE UPDATE EMPLOYE IF SALAIRE > 100. 000 THEN ABORT TRANSACTION G. Gardarin 30

4. CONCLUSION w Un ensemble de concepts bien compris et bien formalisés w Un modèle unique, riche et standardisé n n intégration des BD actives intégration des BD objets w Un formalisme qui s'étend plutôt bien n algèbre d'objets w Un langage associé défini à plusieurs niveaux n G. Gardarin SQL 1, 2, 3 31