Bases de données et SGBD relationnels

1. Bases de données et SGBD relationnels

2. Bases de données et SGBD relationnels (1) • Une base de données représente • un ensemble de données d’entreprise, • mémorisé par un ordinateur, • organisé selon un modèle de données • accessible à de nombreuses personnes. • Un Système de Gestion de Bases de Données (SGBD) représente • un ensemble coordonné de logiciels • permettant de décrire, mémoriser, manipuler, traiter, interroger les ensembles de données constituant la base.

3. Bases de données et SGBD relationnels (2) • Un SGBD doit satisfaire 5 conditions : • être une bonne représentation du monde réel, fiable et à jour : des contraintes d’intégrité doivent assurer un état cohérent de la base. • assurer la non-redondance de l’information : une information doit être implantée une et une seule fois. • maintenir l’indépendance des programmes par rapport aux données, permettant aux applications de partager les mêmes données.

4. Bases de données et SGBD relationnels (3) • assurer la sécurité et la confidentialité des données : les données doivent être protégées contre les accès non autorisés et contre les pannes intempestives. • autoriser le partage des données, permettant à plusieurs utilisateurs de modifier des données quasiment en même temps tout en assurant un résultat cohérent pour un utilisateur consultant la base.

5. Historique des SGBD (1) • Première génération (années 70) : modèle hiérarchique ou modèle en réseau. • Modèle hiérarchique : les données sont représentées sous forme d’une hiérarchie arborescente à plusieurs niveaux. Chaque niveau est constitué d’un ou de plusieurs groupes de données pouvant se décomposer à leur tour. • Modèle en réseau : extension du précédent, où les liens entre objets peuvent exister sans restrictions, indépendamment de la structure de l’arbre.

6. Historique des SGBD (2) • Deuxième génération (années 80) : modèle relationnel. • Les données sont représentées sous forme tabulaire, avec indépendance totale entre les logiciels et le support de stockage. • Langages spécifiques permettant d’accéder aux données de manière assez naturelle : QBE, SQL. • Quelques SGBD relationnels : ORACLE, INGRES, SYBASE, DBASE2, ACCESS, mySQL. • Troisième génération (fin des années 90) : modèle objets. • SGBD objets : O2, ORACLE.

7. Objectifs d’un modèle relationnel • Les principaux objectifs d’un modèle relationnel sont : • utiliser des structures de données simples  • une table relationnelle est un tableau • proposer des langages permettant d’accéder aux données de manière assez naturelle  • QBE, SQL • proposer l’indépendance entre les données et les traitements exécutés sur ces données • permettre à chaque utilisateur d’avoir une vue de la base de données appropriée à ses besoins.

8. Le modèle relationnel (1) • Le concept de base du modèle relationnel est la relation, de structure tabulaire • Une relation a plusieurs attributs • Le degré de la relation est le nombre de ces attributs • Exemple : la relation PRODUIT, de degré 3, comporte les attributs : num_produit, nom_produit et stock

9. Le modèle relationnel (2) • Occurrence : chaque ligne du tableau (un n-uplet) correspond à une occurrence de la relation. • Cardinalité : nombre d’occurrences de la relation dans la base. La relation PRODUIT est donc de cardinalité 4. • Clé : chaque relation contient un attribut particulier (ou un ensemble d’attributs) appelé clé, dont la valeur permet de distinguer une occurrence de toutes les autres. Pour la relation PRODUIT, l’attribut clé est num_produit.

10. Le modèle relationnel (3) • On représente symboliquement une relation R par un schéma SR de la forme : R(clé, attribut 2, attribut 3, …, attribut n). • On aura donc : PRODUIT(num_produit, nom_produit, stock).

11. Exemple de base de données relationnelle (1) • Relation VOL (numvol, depart, arrivee, numav, numpil, jdep, hdep, jarr, harr) numvol : numéro du vol (clé) depart : ville de départ arrivee : ville d’arrivée numav : numéro d’avion numpil : numéro du pilote jdep : jour de départ (clé) hdep : heure de départ jarr : jour d’arrivée harr : heure d’arrivée

12. Exemple de base de données relationnelle (2) • Relation PILOTE(numpilote, nom, prenom) numpilote : numéro du pilote (clé) nom : nom du pilote prenom : prénom du pilote • Relation AVION(numavion, type, cap) numavion : numéro de l’avion (clé) type : type de l’avion cap : capacité de l’avion

13. Exemple de base de données relationnelle (3) • Les liens entre ces relations sont appelés des jointures : • l’attribut NUMAV de VOL représente le même type d’information que l’attribut NUMAVION de AVION. On écrira : VOL.NUMAV = AVION.NUMAVION • l’attribut NUMPIL de VOL représente le même type d’information que l’attribut NUMPILOTE de PILOTE. On écrira : VOL.NUMPIL = PILOTE.NUMPILOTE

14. Éléments de SQL, le langage relationnel

15. Éléments de SQL, le langage relationnel (1) • Le langage SQL (Structured Query Language) est le langage d’interrogation le plus utilisé. Il permet de déclarer les relations, de créer les occurrences, de les modifier, d’interroger et de manipuler les SGBD relationnelles. • La forme générale d’une interrogation SQL (appelée une requête) est : SELECT liste d’attributs FROM noms de relations WHERE conditions ; • Dans les conditions de la clause optionnelle WHERE, on peut utiliser les opérateurs AND et OR.

16. Éléments de SQL, le langage relationnel (2) Recherche des noms et prénoms des pilotes SELECT NOM, PRENOM FROM PILOTE  réalise une projection, c’est-à-dire ne prend qu’un sous-ensemble des attributs de la relation PILOTE.

17. Éléments de SQL, le langage relationnel (3) Recherche des attributs des pilotes de prénom Georges SELECT * FROM PILOTE WHERE PRENOM= 'Georges' ; réalise une sélection, c’est-à-dire affiche tous les attributs de la relation PILOTE mais ne prend qu’une partie des occurrences, celles vérifiant la condition : prenom= 'Georges'.

18. Éléments de SQL, le langage relationnel (4) Attributs croisés de tous les pilotes et de tous les vols SELECT * FROM VOL, PILOTE ; réalise le produit cartésien de la relation VOL par la relation PILOTE, en combinant toutes les occurrences de VOL avec toutes les occurrences de PILOTE. etc...

19. Éléments de SQL, le langage relationnel (5) Recherche sur les vols et sur les pilotes effectifs de ces vols SELECT * FROM PILOTE, VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL réalise une jointure naturelle, c’est-à-dire une sélection sur le produit cartésien de la relation VOL par la relation PILOTE, sélection réalisée par l’égalité d’attributs identiques : PILOTE.NUMPILOTE = VOL.NUMPIL.

20. Éléments de SQL, le langage relationnel (6) Numéro des vols et noms des pilotes de ces vols SELECT VOL.NUMVOL, PILOTE.NOM FROM PILOTE, VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL réalise une projection à partir d’une jointure naturelle.

21. Éléments de SQL, le langage relationnel (7) Numéro des vols sur l’avion de numéro ‘A0006’ et noms des pilotes de ces vols SELECT VOL.NUMVOL, PILOTE.NOM FROM VOL, PILOTE WHERE VOL.NUMPIL = PILOTE.NUMPILOTE AND VOL.NUMAV='A0006' ; réalise une projection d’une sélection sur une jointure naturelle, sélection réalisée par une condition sur un attribut : VOL.NUMAV='A0006'.

22. Éléments de SQL, le langage relationnel (8) Numéros de vols, types d’avion, capacités et noms des pilotes de ces vols SELECT VOL.NUMVOL, AVION.TYPE, AVION.CAP, PILOTE.NOM FROM AVION, PILOTE, VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL AND VOL.NUMAV = AVION.NUMAVION réalise une projection sur le résultat de deux jointures naturelles entre trois relations.

23. Éléments de SQL, le langage relationnel (9) Numéros de vols, types d’avion, capacités et noms des pilotes des vols de capacité comprise entre 200 et 350 Deux formulations possibles : 1)SELECT VOL.NUMVOL, AVION.TYPE, AVION.CAP, PILOTE.NOM FROM AVION, PILOTE, VOLWHERE PILOTE.NUMPILOTE = VOL.NUMPIL AND VOL.NUMAV = AVION.NUMAVION AND (AVION.CAP BETWEEN 200 AND 350)

24. Éléments de SQL, le langage relationnel (10) 2)SELECT VOL.NUMVOL, AVION.TYPE, AVION.CAP, PILOTE.NOM FROM AVION, PILOTE , VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL AND VOL.NUMAV = AVION.NUMAVION AND AVION.NUMAVION IN (SELECT AVION1.NUMAVION FROM AVION AVION1 WHERE (AVION1.CAP BETWEEN 200 AND 350))

25. Éléments de SQL, le langage relationnel (11) • Les fonctions ensemblistes MAX fournit la valeur maximale d’un attribut Capacité de l’avion de capacité maximale SELECT MAX(AVION.CAP) FROM AVION Capacité maximale des avions allant à Madrid SELECT MAX(AVION.CAP) FROM AVION, VOL WHERE AVION.NUMAVION = VOL.NUMAV AND (VOL.ARRIVEE='Madrid')

26. Éléments de SQL, le langage relationnel (12) MIN fournit la valeur minimale d’un attribut Capacité de l’avion de capacité minimale SELECT MIN(AVION.CAP) FROM AVION Heure de départ du premier vol du 15/5/2004 SELECT MIN(VOL.HDEP) FROM VOL WHERE (VOL.JDEP={d '2004-05-15'})

27. Éléments de SQL, le langage relationnel (13) COUNT permet de compter le nombre d’occurrences Nombre d’avions au départ de Paris SELECT COUNT(VOL.DEPART) FROM VOL WHERE (VOL.DEPART='Paris') Nombre de Boeing 747 SELECT COUNT(AVION.NUMAVION) FROM AVION WHERE (AVION.TYPE='Boeing 747')

28. Éléments de SQL, le langage relationnel (14) SUM permet d’additionner des attributs Capacité totale des avions SELECT SUM(AVION.CAP) FROM AVION Capacité totale des avions au départ de Paris SELECT SUM(AVION.CAP) FROM AVION, VOL WHERE VOL.NUMAV = AVION.NUMAVION AND (VOL.DEPART='Paris')

29. Éléments de SQL, le langage relationnel (15) AVG permet de calculer des moyennes d’attributs Moyenne des capacités des Boeing 747 SELECT AVG(AVION.CAP) FROM AVION WHERE (AVION.TYPE='Boeing 747') Moyenne des capacités des avions à destination d’Amsterdam SELECT AVG(AVION.CAP) FROM AVION, VOL WHERE VOL.NUMAV = AVION.NUMAVION AND (VOL.ARRIVEE='Amsterdam')

30. Éléments de SQL, le langage relationnel (16) VAR permet de calculer des variances d’attributs Variance des capacités des avions SELECT VAR(AVION.CAP) FROM AVION Variance des capacités des Boeing 747 SELECT VAR(AVION.CAP) FROM AVION WHERE (AVION.TYPE='Boeing 747')

31. Éléments de SQL, le langage relationnel (17) • Autres fonctions DISTINCT permet de n’obtenir qu’une seule fois chaque occurrence Types des avions SELECT DISTINCT AVION.TYPE FROM AVION Différentes capacités des avions SELECT DISTINCT AVION.CAP FROM AVION

32. Éléments de SQL, le langage relationnel (18) ORDER BY permet d’ordonner par ordre croissant ou décroissant (DESC) Type et capacité des avions par type croissant (ordre alphabétique) SELECT DISTINCT AVION.TYPE, AVION.CAP FROM AVIONORDER BY AVION.TYPE Liste des pilotes par ordre alphabétique décroissant SELECT DISTINCT PILOTE.NOM FROM PILOTE ORDER BY PILOTE.NOM DESC

33. Éléments de SQL, le langage relationnel (19) LIKE permet d’utiliser des jokers dans les chaînes de caractères _ veut dire un caractère quelconque% veut dire un nombre quelconque de caractères Numéros des vols dont le nom du pilote a pour deuxième lettre la lettre “ r ” SELECT VOL.NUMVOL FROM PILOTE, VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL AND (PILOTE.NOM LIKE '_r%')

34. Éléments de SQL, le langage relationnel (20) Villes de départ dont la deuxième lettre est la lettre “ o ” SELECT DISTINCT VOL.DEPART FROM VOL WHERE VOL.DEPART LIKE '_o%')

35. Exercices (1) Heure de départ du premier avion pour Madrid SELECT MIN(VOL.HDEP) FROM VOL WHERE (VOL.ARRIVEE='Madrid')

36. Exercices (2) Nombre de vols pilotés par Dupuis SELECT COUNT(VOL.NUMVOL) FROM VOL, PILOTE WHERE VOL.NUMPIL=PILOTE.NUMPILOTE AND PILOTE.NOM='Dupuis'

37. Exercices (3) Capacité totale des avions partant ou arrivant à Paris SELECT SUM(AVION.CAP) FROM AVION, VOL WHERE VOL.NUMAV=AVION.NUMAVION AND ((VOL.DEPART='Paris') OR (VOL.ARRIVEE='Paris'))

38. Exercices (4) Moyenne des capacités des avions pilotés par Dupuis SELECT AVG(AVION.CAP) FROM VOL, PILOTE, AVION WHERE PILOTE.NOM='Dupuis' AND VOL.NUMPIL=PILOTE.NUMPILOTE AND VOL.NUMAV=AVION.NUMAVION

39. Exercices (5) Types d'avions pilotés par Simon SELECT DISTINCT AVION.TYPE FROM AVION, PILOTE, VOL WHERE PILOTE.NOM='Simon' AND VOL.NUMPIL=PILOTE.NUMPILOTE AND VOL.NUMAV=AVION.NUMAVION

40. Exercices (6) Types des avions fabriqués par Boeing SELECT DISTINCT AVION.TYPE FROM AVION WHERE (AVION.TYPE Like 'Boeing%')

41. Exercices (7) Type des avions atterrissant dans une ville commençant par la lettre M SELECT DISTINCT AVION.TYPE FROM AVION, VOL WHERE VOL.NUMAV=AVION.NUMAVION AND (VOL.ARRIVEE LIKE 'M%')

42. Exercices (8) Nom du pilote et heure de départ du vol partant le plus tôt SELECT PILOTE.NOM, VOL.HDEP FROM PILOTE, VOL WHERE PILOTE.NUMPILOTE = VOL.NUMPIL AND VOL.HDEP = (SELECT MIN(VOL1.HDEP) FROM VOL VOL1 )

43. Éléments de SQL, le langage relationnel (21) GROUP BY permet d’effectuer des groupements Sommes des capacités des avions, groupés par ville de départ SELECT VOL.DEPART, SUM(AVION.CAP) FROM AVION, VOL WHERE VOL.NUMAV = AVION.NUMAVION GROUP BY VOL.DEPART

44. Éléments de SQL, le langage relationnel (22) GROUP BY est souvent utilisé avec la clause HAVING pour spécifier des caractéristiques du groupement : Heure de départ, ville de départ et d’arrivée du premier vol du 15/5/2004 SELECT VOL1.HDEP, VOL1.DEPART, VOL1.ARRIVEE FROM VOL, VOL VOL1 WHERE (VOL.JDEP={d '2004-05-15'}) AND (VOL1.JDEP={d '2004-05-15'}) GROUP BY VOL1.HDEP, VOL1.DEPART, VOL1.ARRIVEE HAVING (VOL1.HDEP=MIN(VOL.HDEP))

45. Éléments de SQL, le langage relationnel (23) Retour sur l’exercice 8 : Nom du pilote et heure de départ du vol partant le plus tôt Deuxième solution (sans requête imbriquée) : SELECT PILOTE.NOM, VOL.HDEP FROM PILOTE, VOL VOL, VOL VOL1 WHERE PILOTE.NUMPILOTE = VOL.NUMPIL GROUP BY PILOTE.NOM, VOL.HDEP HAVING (VOL.HDEP=MIN(VOL1.HDEP))

46. Éléments de SQL, le langage relationnel (24) BETWEEN a AND b teste un intervalle Départ et arrivée des vols partant entre 10 h et 14 h 30 SELECT DISTINCT VOL.DEPART, VOL.ARRIVEE FROM VOL WHERE (VOL.HDEP BETWEEN '10:00' AND '14:30')

47. Éléments de SQL, le langage relationnel (25) Départ et arrivée des vols de capacité comprise entre 250 et 410 SELECT VOL.DEPART, VOL.ARRIVEE, AVION.CAP FROM AVION, VOL WHERE VOL.NUMAV = AVION.NUMAVION AND (AVION.CAP BETWEEN 250 AND 410)

48. Éléments de SQL, le langage relationnel (26) IS NULL et IS NOT NULL permettent de vérifier si l’attribut est renseigné ou pas Numéros des vols auxquels sont affectés des pilotes SELECT VOL.NUMVOL FROM VOL WHERE (VOL.NUMPIL IS NOT NULL)

49. Exercices (1) Nombre d’avions à destination de Madrid SELECT COUNT(VOL.NUMAV) FROM VOL WHERE (VOL.ARRIVEE='Madrid')

50. Exercices (2) Moyenne des capacités des avions pilotés par le pilote P0002 SELECT AVG(AVION.CAP) FROM AVION, VOL WHERE (VOL.NUMPIL='P0002') AND (VOL.NUMAV=AVION.NUMAVION)