Modle relationnel et algbre relationnelle INT Management 62

Modèle relationnel et algèbre relationnelle INT Management 62

Plan du document n n Modèle relationnel Opérateurs de l’algèbre Exemples de requêtes Optimisation slide 64 slide 78 slide 94 slide 99 63

Les concepts descriptifs n n n n Modèle relationnel Notion de domaine Produit cartésien Relation Attribut Clé Schéma de relation et de BD Clé étrangère Métabase 64

Notion de domaine n Définition q n Modèle relationnel Ensemble de valeurs Exemples q q q Entier, réel, chaîne de caractères, booléen Salaire = 1000… 100000 (€) Couleur = {‘rosé’, ‘blanc’, ‘rouge’} 65

Modèle relationnel Produit cartésien n Définition q n Notation q n Le produit cartésien de D 1, . . , Dn est l'ensemble des nuplets (tuples) <V 1, . . , Vn> tel que Vi Di D 1 X. . X Dn Exemple : q q D 1 = {‘BD’, ‘IO’} (Code UV) D 2 = {‘Carpentier’, ‘Lalevée’, ‘Millot } (Prof) D 1 X D 2 D 1 D 2 BD Carpentier BD Lalevée BD Millot IO Carpentier IO Lalevée IO Millot 66

Modèle relationnel Relation n Définition q q n Sous-ensemble du produit cartésien d'une liste de domaines Caractérisée par un nom Exemple q q D 1 = Code UV D 2 = Prof UV D 1 D 2 BD Carpentier IO Lalevée 67

Modèle relationnel Relation (2) n n n Plus simplement, une relation est un tableau à deux dimensions Une ligne est un n-uplet (tuple) On associe un nom à chaque colonne afin de la repérer indépendamment de l'ordre = attribut q q Prend ses valeurs dans un domaine UV Exemple : code coord BD Carpentier IO Lalevée 68

Modèle relationnel Exemples de relations Elève Num Nom Adresse Age 1 Bélaïd Maisel 20 2 Millot CROUS 20 3 Meunier Maisel 21 Num. Elève Code. UV Note 2 BD 10 1 BD 20 2 IO 17 3 IO 18 Inscrit 69

Clé n Définition q n Une clé est un groupe d'attributs minimum qui détermine un nuplet unique dans une relation (à tout instant) Exemple q q q n Modèle relationnel Clé de Elève ? Clé de UV ? Clé de Inscrit ? Contrainte d'intégrité q Toute relation doit posséder une clé renseignée (sans valeur inconnue) 70

Schéma de relation n Définition q Le schéma d'une relation décrit : n n Son nom La liste des attributs qu'elle comporte et des domaines associés La liste des attributs composant la clé (la clé est soulignée) Exemple q n Modèle relationnel Elève(num : entier, nom : chaîne, adresse : chaîne, age : entier de 18 à 35) Intention vs. Extension q q q Schéma de relation : intention de la relation Table : extension Schéma d'une BD relationnelle : ensemble des schémas des relations 71

Clé étrangère n Modèle relationnel Définition q Une clé étrangère est un groupe d'attributs qui apparaît comme clé dans une autre relation R 1(A 1, A 2, . . , Ap+1, . . , An) R 2(B 1, B 2, . . . , Bn) n Rôle q Les clés étrangères définissent des contraintes d'intégrités référentielles entre relations 72

Clé étrangère (2) n Modèle relationnel Mises à jour et clés étrangères q Insertion: la valeur des attributs doit exister dans la relation référencée. n q Suppression dans la relation référencée; les nuplets référençant doivent disparaître. n q Insertion de (4, ‘BD’, 15) dans Inscrit ? Suppression de l’élève 2 dans Elève ? Les clés étrangères sont la traduction des associations du modèle E/A 73

Clé étrangère n Modèle relationnel Exemples Élève(num, nom, adresse, age) UV(code, nbh, coord) Inscrit(num. Elève, code. UV, note) Livre(côte, titre, num. Elève, date. Prêt) Chambre(no, prix, num. Elève) 74

Métabase n Définition q n Modèle relationnel base de données contenant l'ensemble des schémas et des règles de correspondances associées à une base de données Principe q Une base décrivant les autres bases, c'est-à-dire: n n q q les relations les attributs les domaines les clés. . . Notion de dictionnaire de données Base particulière, système, gérée par l'administrateur de BD 75

Modèle relationnel Résumé des notions Relation 1 Attribut 2 v 1 w 1 v 1 w 2 Attributn Schéma Intention Schém a BD Nom. Rel Attribut Table Relation 1 Attribut 1 Tuple / n-uplet v 3 Relation 1 Attribut 2 w 2 vn … Clé Relation 2 Attributa Attributb w 1 Clé étrangère w 2 BD Métabase xi wn 76

Langages associés au modèle relationnel n Langages de Définition de Données (LDD) : q n Langages de manipulation de données (LMD) : q q n Définition /mise à jour des schémas des relations Interrogation : recherche de données Mises à jour : insertion, suppression, modification 2 classes de langages : q q Algébriques SQL Prédicatifs QBE 77

Les opérateurs de manipulation n n Algèbre relationnelle Tout résultat d'une opération est une relation; peut donc être réutilisée en entrée d'un nouvel opérateur. Les opérateurs peuvent être classifiés en : q q q opérateurs ensemblistes / opérateurs relationnels opérateurs de base / opérateurs dérivés opérateurs unaires / opérateurs binaires n n Unaires : sélection (restriction), projection, Binaires : union, intersection, différence, produit cartésien, jointure, division 78

Algèbre relationnelle Restriction n But q q n Inscrits en BD : code. UV=‘BD’(Inscrit) Permet de "sélectionner" des tuples Num. Elève Code. UV La restriction réduit la taille Resu de la relation verticalement Contraintes q q n n Unaire Spécifier une condition Notation q q Notation textuelle: T cond(R) Notation graphique: R Cond. T n Note 2 BD 10 1 BD 20 Majors (note > 15) de BD : code. UV=‘BD’ et note >15(Inscrit) Resu Num. Elève Code. UV Note 1 BD 20 79

Projection n But q q n Permet de "sélectionner" des n Adresses des élèves : attributs Adresse(Elève) La projection réduit la taille de la relation horizontalement Resu Contraintes q q n Algèbre relationnelle Unaire Spécifier une liste d'attributs Notation Pas de doublon Adresse Maisel CROUS Notation textuelle: T attributs(R) n Code et nb heures des UV : q Notation graphique: code, nbh(UV) R Resu Code IO attributs. BD T q nbh 45 15 80

Algèbre relationnelle Union n n But q n Prof Contraintes q q n Permet de fusionner 2 relations Nom des profs, des élèves Binaire Même schéma Notation q q Notation textuelle: T R S Notation graphique: R n Nom Elève 2 Nom Lalevée Bélaïd Carpentier Millot Meunier Nom des personnes à l’INT : Prof Eleve 2 Resu Nom Lalevée S Carpentier T Pas de doublon Millot Bélaïd Meunier 81

Intersection n But q n n Permet d’obtenir l’ensemble des tuples appartenant à deux relations q Nom des profs, des élèves Prof Contraintes q n Algèbre relationnelle Binaire Même schéma Nom Elève 2 Nom Lalevée Bélaïd Carpentier Millot Meunier Notation q q Notation textuelle: T R S Notation graphique: R n Noms communs élèves-profs : Prof Elève 2 S Resu Nom Millot T 82

Différence n But q n Obtenir l’ensemble des tuples d’une relation qui ne figurent pas dans une autre Contraintes q q q n Algèbre relationnelle n Nom des profs, des élèves Prof Binaire Même schéma Non commutatif Nom Elève 2 Nom Lalevée Bélaïd Carpentier Millot Meunier Notation q q Notation textuelle: T R - S Notation graphique: R S T n Noms des élèves qui ne portent pas le nom d’un prof : Eleve 2 -Prof Résu Nom Bélaïd Meunier 83

Produit cartésien n n But q Ensemble de tous les tuples obtenus par concaténation de chaque tuple de R avec chaque tuple de S Contraintes q Binaire q Schéma du résultat: n n q n Algèbre relationnelle R(a 1, a 2, . . , an), S(b 1, b 2, . . . , bp) T R X S, T(a 1, a 2, . . , an, b 1, b 2, . . . , bp) Card (R X S) = Card (R) * Card (S) Notation q Notation textuelle: T R X S q Notation graphique: R S X T 84

Algèbre relationnelle Produit cartésien (2) Elève Num Nom Adresse Age 1 Bélaïd Maisel 2 Millot 3 Meunier Elève X UV UV Code Nbh Coord 20 IO 45 Lalevée CROUS 20 BD 15 Carpentier Maisel 21 Num Nom Adresse Age Code Nbh Coord 1 Bélaïd Maisel 20 IO 45 Lalevée 2 Millot CROUS 20 IO 45 Lalevée 3 Meunier Maisel 21 IO 45 Lalevée 1 Bélaïd Maisel 20 BD 15 Carpentier 2 Millot CROUS 20 BD 15 Carpentier 3 Meunier Maisel 21 BD 15 Carpentier 85

Jointure n n But q Permet d’établir le lien sémantique entre les relations Contraintes q Binaire q Schéma du résultat: n n n Algèbre relationnelle R(a 1, a 2, . . , an), S(b 1, b 2, . . . , bp) T R S T(a 1, a 2, . . , an, b 1, b 2, . . . , bp) Notation q Notation textuelle: T R S condition q Notation graphique: R S condition T 86

1 er exemple de jointure Elève Num Nom Adresse Age 1 Bélaïd Maisel 20 2 Millot CROUS 20 3 Meunier Maisel 21 Elève Chambre n n Elève. Num=Chambre. num. Elève Chambre No Prix num. Elève 10 200 3 21 150 2 Num Nom Adresse Age No Prix num. Elève 2 Millot CROUS 20 21 150 2 3 Meunier Maisel 21 10 200 3 1 tuple de Chambre 1 tuple de résultat 1 tuple de Elève 0 ou 1 tuple de résultat q On a perdu Bélaïd ! 87

2ème exemple de jointure Inscrit Elève Num. Elève Code. UV Note 2 BD 10 1 BD 2 3 Inscrit. Num. Elève=Elève. Num Elève Num Nom Adresse Age 20 1 Bélaïd Maisel 20 IO 17 2 Millot CROUS 20 IO 18 3 Meunier Maisel 21 Num Nom Adresse Age Num. Elève Code. UV Note 1 Bélaïd Maisel 20 1 BD 20 2 Millot CROUS 20 2 IO 17 2 Millot CROUS 20 2 BD 10 3 Meunier Maisel 21 3 IO 18 n 1 tuple de Inscrit 1 tuple de résultat n 1 tuple de Elève 0 à n tuples de résultat n On a dupliqué Millot ! 88

Algèbre relationnelle Division n But q q n Répondre aux requêtes de type « tous les » Un tuple t est dans T si et seulement si pour tout tuple s de S, le tuple <t, s> est dans R Contraintes q q Binaire Schéma du résultat: n n n Notation q q n R(a 1, a 2, . . , an, b 1, b 2, . . . , bp), S(b 1, b 2, . . . , bp) T R S, T(a 1, a 2, . . , an) Notation textuelle: T = R S Notation graphique: Dérivation q q Projection + Produit cartésien + Différence. R S T 1 - T 2 avec: n n R S T T 1 schéma(R) - schéma(S) (R) T 2 schéma(R) - schéma(S) (( schéma(R) - schéma(S) (R) X S) - R) 89

Algèbre relationnelle Division (2) n Exemple q Quels sont les élèves inscrits à toutes les UVs ? Inscrit Num. Elève Code. UV Note 2 BD 10 1 BD 20 2 IO 17 3 IO 18 90

Algèbre relationnelle Division (3) n Exemple q q Construire R : ensemble de toutes les informations dont on a besoin = attributs Num. Elève et Code. UV de Inscrit (R) Construire S : ensemble correspondant à "tous les" (UV) = code. UV (S) Résultat R S Vérification : n q R Num. Elève, Code. UV(Inscrit) R q Num. Elève Code. UV 2 BD 1 BD 2 IO 3 IO S Code(UV) S Code. UV BD Résultat X S R IO q Résultat Resu Num. Elève 2 91

Algèbre relationnelle Bilan : sémantique et notations des opérateurs Opérateur Sémantique Notation textuelle Restriction « Sélectionner » des tuples T cond(R) Projection « Sélectionner » des attributs T attributs(R) Union Fusionner les extensions de 2 relations T R S Intersection Obtenir l’ensemble des tuples communs à deux relations T R S Différence Tuples d’une relation qui ne figurent pas dans une autre T R - S - Produit cartésien Concaténer chaque tuple de R avec chaque tuple de S T R X S X Jointure Etablir le lien sémantique entre les relations T R S Division Répondre aux requêtes de type « tous les » T R S condition Notation graphique Cond. attributs. condition 92

Algèbre relationnelle Bilan : contraintes des opérateurs Opérateur Unaire/ Binaire Schémas « Paramètres » Restriction T cond(R) Unaire Schéma(T) = Schéma(R) Condition sur attributs de R Projection T attributs(R) Unaire Schéma(T) Schéma(R) Liste d’attributs de R Union T R S Binaire Schéma(R)=Schéma(S)=Schéma(T ) Intersection T R S Binaire Schéma(R)=Schéma(S)=Schéma(T ) Différence T R - S Binaire Schéma(R)=Schéma(S)=Schéma(T ) Produit cartésien T R X S Binaire Schéma(T)=Schéma(S) Schéma( R) Jointure T R S Binaire Schéma(T)=Schéma(S) Schéma( R) Division T R S Binaire Schéma(R)=Schéma(S)+Schéma(T ) condition Condition de jointure sur attributs de R et S 93

Exemples de requêtes en algèbre relationnelle n Base de données exemple : les vins Vins(num, cru, annee, degre) Recoltes(nvin, nprod, quantite) Producteurs(num, nom, prenom, region) Buveurs(num, nom, prenom, ville) Commandes(ncde, date, nb, nvin, qte) Livraisons(ncde, no_ordre, qte. Livree) Exemples V R P B C L 94

Modèle E/A de la BD des vins Vins Buveurs num cru degré annee 0, n recoltes Exemples num nom prenom ville 0, n concerne Passer quantite 0, n Producteurs num nom prenom region 1, 1 Commandes ncde qté date 0, n Donner_lieu Livraisons (1, 1) No_ordre qte. Livrée date 95

Composition des opérateurs n Ecriture textuelle : Temp annee=1995 (Vins) Resultat num(Temp) Resultat num( annee=1995 (Vins)) n Exemples Arbre algébrique : Vins. annee=1995 num Résultat 96

Noms des producteurs de Muscadet Mus cru=‘Muscadet’ (V) Rec. Mus R Exemples Vins cru=‘Muscadet’. Mus. num=R. nvin Recoltes num=nvin Prod. Mus Rec. Mus P Rec. nprod=P. num Producteurs nprod=num Resultat nom(Prod. Mus) nom Résultat 97

Exemples Numéros des vins ne faisant l’objet d’aucune commande Num. Vins num(V) Vins. Cdes nvin(C) Resultat Num. Vins – Vins. Cdes Vins Commandes num nvin Résultat 98

Optimisation n Optimisation Noms et prénoms des buveurs habitant Paris ayant commandé du Mâcon 1995 avant le 01 janvier 2000 q q Plusieurs réponses possibles Optimiser = choisir la meilleure façon 99

Réponse 1 Optimisation Buveurs ville=‘Paris’ Commandes Vins date < 01/01/2000 cru=‘Mâcon’ et annee=1995 num=nb nvin=num nom, prenom Résultat 100

Réponse 2 Optimisation Buveurs ville=‘Paris’ Commandes Vins date < 01/01/2000 cru=‘Mâcon’ et annee=1995 nvin=num num=nb nom, prenom Résultat 101

Réponse 3 Optimisation Buveurs Commandes Vins num=nb nvin=num ville=‘Paris’ et date < 01/01/2000 et cru=‘Mâcon’ et annee =1995 nom, prenom Résultat 102

Réponse 4 Optimisation Buveurs ville=‘Paris’ nb, nom, prenom Commandes Vins date < 01/01/2000 cru=‘Mâcon’ et annee=1995 nvin, nb num=nb nvin, nom, prenom nvin=num nom, prenom Résultat 103

Optimisation par le SGBD n Optimiser : q q q n Optimisation Le nombre d’E/S Le temps UC … Facteurs déterminants : q q Ordre d'exécution des opérations algébriques Algorithme implantant les opérations algébriques Placement des données sur le disque Taille des relations intermédiaires 104

Restructuration n Heuristique la plus employée q q n Optimisation Remonter les opérateurs unaires (restriction, projection) => diminution de la taille des relations Descendre les jointures Propriétés utilisées q q Associativité des jointures Commutativité restriction / projection Commutativité restriction / jointure Commutativité projection / jointure 105

Problème n Libérer l’utilisateur des problèmes d’optimisation q q q n Optimisation Exprimer une question sans préciser l’enchaînement des opérations algébriques langage non procédural langage déclaratif Responsabilités du SGBD : q q q Traduction sous la forme d’un arbre algébrique Optimisation de l’arbre Exécution de l’arbre optimisé 106