INT Programme Introduction aux BD et aux SGBD
© INT Programme • Introduction aux BD et aux SGBD • Le modèle relationnel +Le langage de requête SQL • La conception d’une BD relationnelle • Protection des informations • Perspectives des BD Evry, 29 -31 mai 2000 1
© INT SQL Structured Query Language Evry, 29 -31 mai 2000 2
© INT Schéma de la base de référence • Employé(Nom_emp, Prénom_emp, No_ss, Date_naiss, Adresse_emp, Sexe, Salaire, Comm, No_chef, Nod) • Département(Nom_d, No_dept, No_dir, Date_affect) • Dept_local(Num_dept, Id_loc) • Projet(Nom, No_proj, Local_proj, Nod) • Travaille_sur(No_ss, No_projet, Nb_heures) Evry, 29 -31 mai 2000 3
© INT Instance Evry, 29 -31 mai 2000 4
© INT Instance (suite) Evry, 29 -31 mai 2000 5
© INT Interrogation de la base de données l Présentation des résultats sous forme tabulaire où: – entête de colonne: attribut – largeur de colonne cf. définition du domaine de l'attribut l alias possible pour entête de colonne Evry, 29 -31 mai 2000 6
© INT Clauses minimales SELECT attr 1, attr 2, . . attrn FROM relation; • Exemples: SELECT Nom_d FROM Département; SELECT Nom, Nod "numéro" FROM Projet; Evry, 29 -31 mai 2000 7
© INT Joker SELECT * FROM Projet; Evry, 29 -31 mai 2000 8
© INT Elimination des doublons SELECT DISTINCT Local_proj FROM Projet; Evry, 29 -31 mai 2000 9
© INT Synonymes SELECT P. Nom, P. Nod FROM Projet P; Evry, 29 -31 mai 2000 10
© INT Clause WHERE SELECT attr 1, attr 2, . . attrn FROM relation WHERE condition; Exemple: Liste des employés travaillant plus de 20 heures sur un projet. SELECT No_ss, No_projet FROM Travaille_sur WHERE Nb_heures > 20; Evry, 29 -31 mai 2000 11
Exemples • Liste des employés dont la commission est supérieure à leur salaire Liste des employés qui ne sont pas du département 5 SELECT No_ss FROM Employé WHERE Nod <> 5; WHERE Comm > Salaire; • Liste des employés du département 5 dont le salaire est supérieur à 300 k. F © INT • • Liste des départements localisés à Paris ou à Fontainebleau SELECT Num_dept SELECT No_ss FROM Dept_local FROM Employé WHERE Id_loc IN ('Paris', WHERE Salaire > 300 'Fontainebleau'); AND Nod = 5; Evry, 29 -31 mai 2000 12
© INT Clause ORDER BY • ORDER BY {attribut [DESC | ASC]} • Liste des employés ordonnée par ordre croissant de salaire SELECT No_ss FROM Employé ORDER BY Salaire; Evry, 29 -31 mai 2000 13
© INT Exemples • Liste des employés et leurs départements ordonnée par ordre décroissant de salaire à croissant de salaire et par ordre partir de 300 k. F décroissant de département SELECT No_ss FROM Employé WHERE Salaire > 300 ORDER BY Salaire, 1, Nod DESC; ORDER BY Salaire DESC; Evry, 29 -31 mai 2000 14
© INT Requête multi-relations • Critère de jointure: clause WHERE • Notation préfixée (relation. attribut) • Exemple: Dans quelles villes sont les départements R&D? (Equi-jointure) SELECT Id_loc FROM Département, Dept_local WHERE Nom_d = 'R&D' AND No_dept = Num_dept; Evry, 29 -31 mai 2000 15
Exemples • Sur quels projets travaillent des © INT femmes ? • Employés qui gagnent plus que leur responsable (auto-jointure) SELECT No_projet SELECT Nom_emp FROM Travaille_sur, Employé FROM Employé E, Employé CHEF WHERE Sexe = 'F' WHERE E. No_chef = CHEF. No_ss AND Travaille_sur. No_ss = Employé. No_ss; AND E. Salaire > CHEF. Salaire ou SELECT No_projet FROM Travaille_sur T, Employé E WHERE Sexe = 'F' AND T. No_ss = E. No_ss; Evry, 29 -31 mai 2000 16
© INT Sous-requêtes SELECT attr 1, attr 2, . . attrn FROM relation WHERE attribut opérateur (SELECT. . . ); • Quels sont les employés des départements R&D ? SELECT Nom_emp FROM Employé WHERE Nod IN ( SELECT No_dept FROM Département WHERE Nom_d = 'R&D'); Evry, 29 -31 mai 2000 17
© INT Expressions et fonctions numériques dans la clause SELECT • Liste des employés et de leur rémunération totale (salaire et commission) SELECT E. No_ss, E. Salaire + E. Comm FROM Employé E; Evry, 29 -31 mai 2000 18
© INT Expressions numériques dans la clause WHERE • Liste des employés dont la commission dépasse la moitié de leur salaire. SELECT E. No_ss FROM Employé E WHERE E. Comm > 0. 5 * E. Salaire Evry, 29 -31 mai 2000 19
© INT Expressions numériques dans la clause ORDER BY • Liste des employés triée par ordre croissant de rémunération totale SELECT E. No_ss FROM Employé E ORDER BY E. Comm+ E. Salaire; Evry, 29 -31 mai 2000 20
© INT Fonctions agrégats • 5 fonctions agrégatives: – AVG ( ): calcul de la moyenne – SUM ( ): calcul de la somme – MIN ( ): calcul de la valeur minimale – MAX ( ): calcul de la valeur maximale – COUNT ( ): calcul du nombre de tuples Evry, 29 -31 mai 2000 21
© INT AVG ( ) • Donner la moyenne des salaires. SELECT AVG(Salaire) FROM Employé; • Donner la moyenne des revenus d'un salarié SELECT AVG(Salaire + Comm) FROM Employé; Evry, 29 -31 mai 2000 22
SUM( ) • Donner la somme des salaires, et la somme des commissions perçues par les employés. SELECT SUM(Salaire), SUM(Comm) FROM Employé; • Donner la somme des salaires des employés ne percevant pas de © INT commission. SELECT SUM(Salaire) FROM Employé WHERE Comm = 0; Evry, 29 -31 mai 2000 23
© INT MIN( ) • Quel est le revenu minimum (commission comprise) ? SELECT MIN(Salaire + Comm) FROM Employé; Evry, 29 -31 mai 2000 24
© INT MAX( ) • Quel est la commission maximale perçue ? SELECT MAX(Comm) FROM Employé; Evry, 29 -31 mai 2000 25
© INT COUNT() • Quel est le nombre d'employés du département 5 ? SELECT COUNT(No_ss) FROM Employé WHERE Nod = 5; ou SELECT COUNT(*) FROM Employé WHERE Nod = 5; Evry, 29 -31 mai 2000 26
© INT Exemples N Attention SELECT Nom_emp, AVG(Salaire) FROM Employé; • Donner le plus haut salaire, le plus bas salaire et la différence entre les deux SELECT MAX(Salaire), MIN(Salaire), MAX(Salaire) - MIN(Salaire) FROM Employé; Evry, 29 -31 mai 2000 27
© INT Clause GROUP BY • Regroupement de tuples • Relation initiale partitionnée horizontalement selon les valeurs d'un attribut ou d'un groupe d'attributs. • Permet de répondre aux questions du type: – donner le nombre d'employés par département. – constituer des groupes d'employés pour chaque département – évaluer au sein de chacun de ces groupes le nombre d'éléments Evry, 29 -31 mai 2000 28
© INT Exemple • Donner le nombre d'employés par département. SELECT Nod, COUNT(*) FROM Employé GROUP BY Nod; Evry, 29 -31 mai 2000 29
© INT Evaluation du résultat • Relation initiale Evry, 29 -31 mai 2000 30
© INT Evaluation du résultat • Tri et partitionnement Evry, 29 -31 mai 2000 31
© INT Evaluation du résultat • Résultat Evry, 29 -31 mai 2000 32
© INT Exemples N Attention: tous les attributs figurant dans la clause SELECT sans être soumis à un agrégat doivent apparaître dans la clause GROUP BY SELECT Nom_emp, AVG(Salaire) FROM Employé GROUP BY Nod; þ Problème Evry, 29 -31 mai 2000 33
© INT Exemple • Donner le salaire moyen des femmes par département. SELECT Nod, AVG(Salaire + Comm) FROM Employé WHERE Sexe = 'F' GROUP BY Nod; Evry, 29 -31 mai 2000 34
© INT Clause HAVING • Exprimer des conditions sur le résultat après partitionnement • Exemple: « Donner la liste des salaires moyens par département si celui-ci excède 400 k. F. » SELECT Nod, AVG(Salaire) FROM Employé GROUP BY Nod HAVING AVG(Salaire) > 400; Evry, 29 -31 mai 2000 35
© INT Exemple • Donner la liste des salaires moyens des femmes par département si celui-ci excède 400 k. F. SELECT Nod, AVG(Salaire) FROM Employé WHERE Sexe = 'F' GROUP BY Nod HAVING AVG(Salaire) > 400; Evry, 29 -31 mai 2000 36
© INT Opérateurs ensemblistes • Opérateurs binaires • Deux relations de même schéma en entrée Evry, 29 -31 mai 2000 37
© INT Union • Donner la liste des dépenses composant la masse salariale (salaire et commission). SELECT Salaire FROM Employé UNION SELECT Comm FROM Employé • L'opérateur d'union élimine automatiquement les doublons Evry, 29 -31 mai 2000 38
© INT Exemple N Attention aux schémas • Donner la liste des départements avec leurs directeurs et des départements avec leurs localisations SELECT No_dept, No_dir FROM Département UNION SELECT Num_dept, Id_loc FROM Dept_local þ Problème Evry, 29 -31 mai 2000 39
© INT Intersection • Quels sont les employés qui encadrent d'autres employés et qui travaillent sur un projet ? SELECT No_chef FROM Employé INTERSECT SELECT No_ss FROM Travaille_sur Evry, 29 -31 mai 2000 40
© INT Différence • Donner la liste des employés qui n'encadrent personne. SELECT No_ss FROM Employé EXCEPT SELECT No_chef FROM Employé Evry, 29 -31 mai 2000 41
© INT Questions quantifiées • Prédicats – ANY – ALL – EXISTS Evry, 29 -31 mai 2000 42
© INT ANY • Teste si la valeur d'un attribut satisfait un critère de comparaison avec au moins un résultat d'une sous-requête • Exemple: « Quels sont les employés travaillant sur au moins un des projets de l'employé 13334. » SELECT No_ss FROM Travaille_sur WHERE No_projet = ANY (SELECT No_projet FROM Travaille_sur WHERE No_ss =13334) Evry, 29 -31 mai 2000 43
© INT ALL • Teste si la valeur d'un attribut satisfait un critère de comparaison avec tous les résultats d'une sous-requête. • Exemple: « Quels sont les employés les mieux payés. » SELECT No_ss FROM Employé WHERE Salaire >= ALL (SELECT Salaire FROM employé) Evry, 29 -31 mai 2000 44
© INT EXISTS • Teste si la réponse à une sous-requête est vide. • Exemple: « Donner le nom des employés travaillant au moins sur un projet. » SELECT Nom_emp FROM Employé E WHERE EXISTS ( SELECT No_projet FROM Travaille_sur T WHERE T. No_ss = E. No_ss); Evry, 29 -31 mai 2000 45
© INT Exemple • Quels sont les noms d'employés ayant au moins un autre employé sous leurs ordres. SELECT Nom_emp FROM Employé E 1 WHERE EXISTS ( SELECT No_ss FROM Employé E 2 WHERE E 2. No_chef = E 1. No_ss) ORDER BY Nom_emp; Evry, 29 -31 mai 2000 46
Prédicat EXISTS pour exprimer une division • Quels sont les départements répartis sur tous les sites? © INT – Un département est conservé s'il n'existe aucun site sur lequel il ne soit pas situé. SELECT No_dept FROM département D WHERE NOT EXISTS (SELECT Id_loc FROM Dept_local L 1 WHERE NOT EXIST (SELECT L 2. * FROM Dept_local L 2 WHERE L 2. Id_loc = L 1. Id_loc AND L 2. Num_dept = D. No_dept)) Evry, 29 -31 mai 2000 47
Synthèse SQL • SELECT liste d'attributs, agrégats FROM liste de relations WHERE conditions ou autre requête • GROUP BY liste d'attributs • HAVING condition, agrégats • ORDER BY liste d'attributs • UNION | INTERSECTION | EXCEPT © INT Equivalent AR Projection Produit cartésien Restriction + jointure Division, jointure Union, intersection, différence Evry, 29 -31 mai 2000 48
Exemple complet • Liste des départements avec le nombre d'employés ne percevant pas © INT de commission et travaillant sur un projet si celui-ci est supérieur à 1 triée par ordre croissant de département. SELECT D. Nom_d, D. No_dept, COUNT(*) FROM Département D, Employé E, Travaille_sur T WHERE E. Comm=0 AND D. No_dept =E. Nod AND E. No_ss = T. No_ss GROUP BY No_dept HAVING COUNT(*) > 1 ORDER BY No_dept; Evry, 29 -31 mai 2000 49
© INT Commandes de mise à jour • Insertion de nouveaux tuples: INSERT • Mise à jour de valeurs: UPDATE • Suppression de tuples: DELETE • Suppression de tous les tuples d'une relation: TRUNCATE Evry, 29 -31 mai 2000 50
© INT INSERT • Insérer des tuples dans une relation en spécifiant les valeurs à insérer INSERT INTO <relation> VALUES (<liste de valeurs>); INSERT INTO relation(<liste d'attributs>) VALUES (<liste de valeurs>); • Liste des noms d'attributs optionnelle Evry, 29 -31 mai 2000 51
© INT Exemples • Créer le nouveau département "Distribution" de numéro 6, dirigé par 18886 à partir du 15/09/96. INSERT INTO département VALUES ('Distribution', 6, 18886, '15/09/96'); • Créer le nouveau département "Distribution" de numéro 6, dirigé par 18886. La date d'affectation est pour l'instant inconnue. INSERT INTO département (No_dept, nom_d, No_dir) VALUES (6, 'Distribution', 18886); Evry, 29 -31 mai 2000 52
© INT Insertion à partir d'autres relations • Insertion de tuples provenant d'une requête considérée comme une sous-requête INSERT INTO relation SELECT. . . • Création de relation avec insertion simultanée de données CREATE TABLE relation AS SELECT. . Evry, 29 -31 mai 2000 53
© INT UPDATE • Modification des valeurs d'un ou de plusieurs attributs, dans un ou plusieurs tuples existants d'une relation. UPDATE <relation> SET <attribut> = <valeur> WHERE <expression logique> • En l'absence de clause WHERE toutes lignes sont mises à jour Evry, 29 -31 mai 2000 54
© INT Exemple • Augmenter de 10 % tous les employés n'ayant pas de commission. UPDATE Employé SET Salaire = Salaire *1. 1 WHERE Comm = 0; Evry, 29 -31 mai 2000 55
© INT DELETE • Suppression des tuples d'une relation vérifiant un certain critère DELETE FROM <relation> WHERE <expression logique> • En l'absence de la clause WHERE tous les tuples sont supprimés Evry, 29 -31 mai 2000 56
© INT Exemple • Traiter la fin du projet Produit Y – DELETE FROM Travaille_sur – WHERE Nom = 'produit. X'; Evry, 29 -31 mai 2000 57
© INT TRUNCATE • Suppression de tous les tuples d'une relation TRUNCATE TABLE relation; • Vider la relation "projets" (après un archivage par exemple) TRUNCATE Projet; Evry, 29 -31 mai 2000 58
© INT SQL : aspect programmation Evry, 29 -31 mai 2000 59
© INT Contexte • Limites du SQL interactif : – absence de structure de contrôle – absence de variable => Adopter un langage de type procédural • SQL doit rester le seul langage d'accès aux données => SQL intégré (Embedded SQL) Evry, 29 -31 mai 2000 60
© INT Traitement d'une requête SQL SELECT nom, âge FROM Employé WHERE âge >30 Analyse Syntaxique Vérification DD Optimisation Génération d'un plan d'exécution Forme exécutable de la requête Exécution Evry, 29 -31 mai 2000 61
© INT Concepts • Précompilateur – Programme + ordres SQL => programme + appels bas niveaux BD • Variable hôte – Variable déclarée dans le programme hôte pour le transfert des données entre les ordres SQL intégrés et le programme hôte – Utilisations: v Variables paramètres des ordres SQL (programme -> SGBD); v Zone de réception des données renvoyées par une interrogation (SGBD -> programme). Evry, 29 -31 mai 2000 62
Exemple: Pro*C main() if (sqlca. sqlcode == 0) { { printf("nom: %s, salaire: %fn", exec sql include sqlca; nom, sal); exec sql begin declare section; } int num; /* no employé*/ else char nom[16]; /*nom employé*/ { if (sqlca. sqlcode == 100) float sal: /* salaire*/ { printf("Pas d'employé ayant ce exec sql end declare section; numéron"); printf("entrer le numéro de l'employé: "); } scanf("%d", &num); else { printf("erreur: %dn", exec sql select nom_emp, salaire sqlca. sqlcode); from Employé } Where num_emp =: num } into: nom, : sal; exit(); } © INT Evry, 29 -31 mai 2000 63
- Slides: 63