Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Cédric du Mouza – NFA011 1 Approfondissement Bases de Données NFA011 Cédric du

Cédric du Mouza – NFA011 1 Approfondissement Bases de Données NFA011 Cédric du Mouza (d’après des supports d’Elisabeth Métais) Cédric du Mouza – NFA011 2 SQL, le langage d'interrogation Rappels Algèbre relationnelle - les opérateurs Expression des requêtes à l'aide de l'algèbre relationnelle Projection et restriction Fonctionnalités diverses Requêtes imbriquées La jointure Opérateurs ensemblistes La division Groupement (GROUP BY) Cédric du Mouza – NFA011 3 Le modèle relationnel Une base de données est constituée d’un ensemble de relations Une relation possède un schéma, constitué d’un nom de relation et d’un ensemble d’attributs Un attribut d’une relation se caractérise par un nom unique et prend ses valeurs dans un domaine donné (ex.: chaîne de caractères, entier, date, etc) Exemple: AVION(Numav:entier,Capacité:entier,Type:string,Entrepot:string) PILOTE(Matricule:entier,Nom:string,Ville:string,Age:entier,Salaire: entier) Cédric du Mouza – NFA011 4 Schéma relationnel Une relation peut être représentée sous forme de table où: Chaque colonne correspond à un attribut Chaque ligne (appelée aussi nuplet) correspond à un élément de l’ensemble de la relation L’ordre des lignes n’a pas d’importance contrairement aux colonnes AVION Numav Capacite Type Entrepot 14 25 A400 Garches 345 75 B200 Maubeuge PILOTE Matricule Nom Ville 1 Figue Cannes 45 28004 2 Lavande Touquet 24 11758 Age Salaire Cédric du Mouza – NFA011 5 Langage d’interrogation Rappels Cédric du Mouza – NFA011 6 SQL (Structured Query Language) La définition et la modification du schéma de la base de donnée, La manipulation des données L'interrogation des données, Le contrôle des accès à la base. SQL permet: Cédric du Mouza – NFA011 7 Généalogie du langage SQL Algèbre relationnelle Calcul de tuples Autre SQL VOL DEPART Numvol Heure_départ Caen {V.Heure_Départ/ Vol (V) et V.Ville_arrivée = 'Caen' et D / Départ (D) et D.Numvol = V.Numvol et D.Date = '19-12-95'} Cédric du Mouza – NFA011 8 Compagnie Aerienne (1) AVION Numav Capacite Type Entrepot 14 25 A400 Garches 345 75 B200 Maubeuge PASSAGER Numab Nomab 1 Nifance 8 Téarice PILOTE Matricule Nom Ville 1 Figue Cannes 45 28004 2 Lavande Touquet 24 11758 Age Salaire Cédric du Mouza – NFA011 9 Compagnie Aerienne (2) RESERVATION Numab Numvol Date_dep 1 AL12 31-12-95 2 AL12 31-12-95 VOL Numvol Heure_départ Heure_arrivée Ville _départ Ville_arrivée AL12 08-18 09-12 Paris Lilles AF8 11-20 23-54 Vaux Rio DEPART Numvol Date_dep Numav Matricule AL12 31-12-95 14 1 AL12 19-12-95 345 2 Cédric du Mouza – NFA011 10 Langage d’interrogation Algèbre relationnelle - les opérateurs Cédric du Mouza – NFA011 11 Interrogation de la base de données à travers des opérateurs de type algébrique, c'est-à-dire qui peuvent se composer pour obtenir le résultat. 5 opérations de base pour exprimer toutes les requêtes. 2 Opérations unaires: Restriction Projection Opérateurs de l'algèbre relationnelle Cédric du Mouza – NFA011 12 3 opérations binaires Union Différence Produit cartésien Autres opérations dérivées Jointure Intersection Division Opérateurs de l'algèbre relationnelle Cédric du Mouza – NFA011 13 La restriction La restriction (ou sélection) d'une relation R selon un critère Q donne une relation de même schéma, avec pour tuples résultats ceux de R qui satisfont le critère Q. Elle est notée Q est une expression composée de connecteurs (ET ^, OU v) et de prédicats (<, >, ≤ , ≥ , ≠ , =) portant sur les attributs, évaluables à Vrai ou à Faux pour un tuple donné. σQ(R) Cédric du Mouza – NFA011 14 La restriction Critère de restriction: Entrepôt = 'Cannes' AVION NUMAV CAPAC. TYPE ENTREP. 1 2 3 4 150 100 45 200 B707 A500 C2000 B707 Cannes Cannes Rio Nice Cédric du Mouza – NFA011 15 La projection La projection d'une relation R sur un ensemble d'attribut C donne une relation ayant pour schéma cet ensemble d'attributs. Elle est notée Ses tuples sont ceux de la relation R, réduits aux attributs de la projection. πC(R) Cédric du Mouza – NFA011 16 La projection AVION NUMAV CAPAC. TYPE ENTREP. 1 2 3 4 150 100 45 200 B707 A500 C2000 B707 Cannes Cannes Rio Nice Attributs de projection: capacité, type Cédric du Mouza – NFA011 17 Les opérateurs de restriction et de projection peuvent se combiner pour sélectionner un résultat: Combinaison des deux opérations AVION NUMAV CAPAC. TYPE ENTREP. 1 2 3 4 150 100 45 200 B707 A500 C2000 B707 Cannes Cannes Rio Nice Cédric du Mouza – NFA011 18 UNION, DIFFERENCE ET INTERSECTION R1 R2 R1 R2 R2 R1 R2 UNION DIFFERENCE INTERSECTION Cédric du Mouza – NFA011 19 Sur l'exemple: AVION_1 NUMAV CAPAC. TYPE ENTREP. 1 2 150 100 B707 A500 Cannes Cannes AVION_2 NUMAV CAPAC. TYPE ENTREP. 3 2 4 45 100 200 C2000 A500 B707 Nice Cannes Nice Cédric du Mouza – NFA011 20 Sur l'exemple: UNION AVION_1  AVION_2 NUMAV CAPAC. TYPE ENTREP. 1 2 3 4 150 100 45 200 B707 A500 C2000 B707 Cannes Cannes Nice Nice Cédric du Mouza – NFA011 21 Sur l'exemple: INTERSECTION AVION_1  AVION_2 NUMAV CAPAC. TYPE ENTREP. 2 100 A500 Cannes Cédric du Mouza – NFA011 22 Sur l'exemple: DIFFERENCE AVION_1 AVION_2 NUMAV CAPAC. TYPE ENTREP. 1 150 B707 Cannes Cédric du Mouza – NFA011 23 Le produit cartésien Le produit cartésien de deux relations R1 et R2 est une relation dont le schéma est la concaténation des schémas de R1 et R2. Il est noté R1 x R2 Ses tuples sont ceux de R1, concaténés chacun à tous les tuples de R2. PASSAGER NUMAB NOM 1 2 Amandier Lavande RESERVATION NUMAB NUMVOL DATE_DEP 1 2 AI 100 AI200 19-DEC-95 23-DEC-95 2 AL 500 24-DEC-95 Cédric du Mouza – NFA011 24 Le produit cartésien P.NUMAB NOM 1 2 Amandier Lavande Réservation X Passager R.NUMAB NUMVOL DATE_DEP 1 2 AI 100 AI200 19-DEC-95 23-DEC-95 2 AL 500 24-DEC-95 1 2 2 AI 100 AI200 AL 500 19-DEC-95 23-DEC-95 24-DEC-95 2 2 1 1 Amandier Amandier Lavande Lavande Cédric du Mouza – NFA011 25 La jointure Critère: RESERVATION.NUMAB = PASSAGER.NUMAB Une jointure est un produit cartésien suivi d'une restriction sur un critère. P.NUMAB NOM 1 2 Amandier Lavande Réservation X Passager R.NUMAB NUMVOL DATE_DEP 1 2 AI 100 AI200 19-DEC-95 23-DEC-95 2 AL 500 24-DEC-95 1 2 2 AI 100 AI200 AL 500 19-DEC-95 23-DEC-95 24-DEC-95 2 2 1 1 Amandier Amandier Lavande Lavande Cédric du Mouza – NFA011 26 Résultat de la jointure RESULTAT: Jointure de la relation RESERVATION et de la relation PASSAGER sur le critère RESERVATION.NUMAB = PASSAGER.NUMAB P.NUMAB NOM 1 Amandier Lavande RESERVATION PASSAGER R.NUMAB NUMVOL DATE_DEP 1 AI 100 19-DEC-95 2 2 AI200 AL 500 23-DEC-95 24-DEC-95 2 2 Lavande numab Cédric du Mouza – NFA011 27 La division Le résultat de la division d'une relation R par une relation S est une relation Q telle que: (1) Le schéma de Q est constitué des attributs de R n'appartenant pas à S. (2) Les tuples de Q sont ceux qui, concaténés à n'importe quel tuple de S donnent un tuple de R. R 1 2 2 MATRICULE NUMAV 100 100 101 S NUMAV 100 101 Q MATRICULE 2 : = Cédric du Mouza – NFA011 28 Langage d’interrogation Expression des requêtes à l'aide de l'algèbre relationnelle Cédric du Mouza – NFA011 29 Exemple 1 REQUETE: Quels sont les numéros, types et capacités des avions ayant une capacité supérieure à 100 ? Cédric du Mouza – NFA011 30 Exemple 1 REQUETE: Quels sont les numéros, types et capacités des avions ayant une capacité supérieure à 100 ? NUMAV, TYPE, CAPACITE CAPACITE > 100 AVION Résultat Cédric du Mouza – NFA011 31 Exemple 1 2 opérations de restriction sont-elles commutatives? σA(σB(R))=σB(σA(R)) ?? 2 opérations de projection sont-elles commutatives? πA(πB(R)) = πB(πA(R)) ?? La restriction et la projection sont-elles des opérations commutatives? πA(σB(R)) = σB(πA(R)) ?? Cédric du Mouza – NFA011 32 REQUETE: Quels sont les noms des passagers voyageant sur le vol AF105 du 26 mars 1996? Exemple 2 Cédric du Mouza – NFA011 33 REQUETE: Quels sont les noms des passagers voyageant sur le vol AF105 du 26 mars 1996? Exemple 2 PASSAGER RESERVATION NOMAB Numab = Numab NUMVOL = 'AF105' AND DATE_DEP = '26-03-96' Cédric du Mouza – NFA011 34 Exemple 2 Une opération de sélection et de jointure sont-elles commutatives? σA(R∞S) = σA(R)∞S Une opération de projection et de jointure sont-elles commutatives? πA(R∞S) = πA(R)∞S Cédric du Mouza – NFA011 35 Exemple 3 REQUETE: Quels sont les pilotes habitant à Deauville et partant pour Las Vegas ? Cédric du Mouza – NFA011 36 Exemple 3 REQUETE: Quels sont les pilotes habitant à Deauville et partant pour Las Vegas ? DEPART VOL Nom PILOTE Numvol Matricule Ville = 'Deauville' Ville_arrivée = 'Las Vegas' Cédric du Mouza – NFA011 37 Exemple 3 2 jointures sont-elles commutatives? R∞(S∞T) = (R∞S)∞T ?? Cédric du Mouza – NFA011 38 Exemple 3 D’après les règles identifiées, proposer d’autres expressions sous forme d’arbre pour la requête de l’exemple 3. Conclusion? Cédric du Mouza – NFA011 39 Exemple 4 REQUETE: Quels sont les noms des pilotes effectuant le vol AL123 ? Cédric du Mouza – NFA011 40 Exemple 4 REQUETE: Quels sont les noms des pilotes effectuant le vol AL123 ? PILOTE DEPART Nom Matricule uploads/s3/ sql-base.pdf