UE LMI 2.02 : Algorithmique et programmation impérative Introduction aux

1. UE LMI 2.02 : Algorithmique et programmation impérative Introduction aux Bases de Données relationnelles Laszlo Szathmary szathmar@loria.fr Module construit sur la base de travail de Malika Smaïl-Tabbone

2. Plan du cours • Introduction aux Bases de Données (BD) • Modèle relationnel de données •  Algèbre relationnelle • De l’algèbre relationnelle à SQL Travaux pratiques sur le système postgreSQL (logiciel libre ou open source sous licence BSD)

3. Quelques références • «Bases de données et systèmes d’information » N. Boudjlida, Dunod, 1999. • «Introduction aux bases de données» C.J. Date, Vuibert, 2004. - «Bases de données : les systèmes et leurs langages» G. Gardarin, Eyrolles, 1984.

4. 1. Introduction aux Bases de Données (BD) • Base de données (BD) : collection volumineuse de données • cohérentes • structurées indépendamment d’une application • de redondance minimale • accessibles par plusieurs utilisateurs à la fois • Système de Gestion de Bases de données (SGBD) : logiciel assurant structuration, stockage, maintenance, mise à jour et consultation de BDs

5. Fonctions d’un SGBD (1/4) • Définition des données : • Un Langage de Définition de Données permet de décrire : • des objets (personnes, véhicules) • Des attributs sur les objets (nom, n° d’immatr.) • Des liens entre objets (personne possède véhicule) • Des contraintes sur objets, attributs et liens (un véhicule n’a qu’un seul propriétaire) • Schéma d’une BD = description d’une BD à l’aide du LDD

6. Fonctions d’un SGBD (2/4) • Manipulation des données • Un Langage de Manipulation de Données permet de créer, rechercher, supprimer, modifier des données • Il existe d’autres façons de manipuler les données • interface graphique orientée utilisateur final • interface orientée programmeur d'applications (SQL immergé dans un langage de programmation). Le langage SQL comporte des commandes permettant de définir ou de manipuler des données.

7. Fonctions d’un SGBD (3/4) • Contrôler l'intégrité des données • Les données enregistrées doivent vérifier certaines propriétés appelées contraintes d’intégrité (CI) • Assurer la sécurité de fonctionnement Remettre rapidement une BD dans un état opérationnel après un incident hardware/software

8. Fonctions d’un SGBD (4/4) • Gérer les accès concurrents Permettre des accès simultanés par plusieurs utilisateurs • Assurer la confidentialité • Cacher certaines informations à certains utilisateurs

9. Quelques applications des BDs • Gestion (salaires, stock…) • Transactionnel (comptes bancaires, centrales d'achat…) • Librairies électroniques (bibliothèques, journaux…) • Documentation technique (plans, dessins…) • Génie logiciel (programmes, manuels…) • …

10. Quelques métiers dans le domaine des BDs • Utilisateur de BD (naïf, occasionnel, spécialisé) • Concepteur et développeur de BD • Administrateur de (SG)BD • Réalisateur de SGBD ou d’outils périphériques (ex: outils de conception) • …

11. Autres enseignements liés aux BDs • Pour ceux qui suivront le parcours Informatique • En Licence 2 : UE Données Persistantes • En Licence 3 : UE SGBD, UE Conception de BD • En Master 2 Pro spécialité IL : UE Architecture et administration des SGBD

12. Plan du cours • Introduction aux Bases de Données (BD) • Modèle relationnel de données •  Algèbre relationnelle • De l’algèbre relationnelle à SQL

13. 2. Modèle relationnel de données Introduction intuitive Une Base de Données Relationnelle (BDR) peut être vue par l’utilisateur comme un ensemble de tableaux ou de tables. • Une table est un ensemble de lignes et de colonnes Exemple de BDR : • fournisseur est une table contenant le numéro (nof), le nom (nomf) et la ville (ville) de chaque fournisseur • pièce est une table contenant le numéro (nop), le nom (nomp) et le prix (prix) de chaque pièce • vente est une table indiquant qu’une pièce (nop) est vendue par un fournisseur (nof)

14. vente fournisseur pièce Une base de données relationnelle

15. Formalisation du modèle relationnel de données (théorie des ensembles) • Un domaine est un ensemble de valeurs • ex : L’ensemble des nombres entiers (Z) ; l’ensemble des chaînes de caractères de longueur 50 ; {jaune, vert, bleu} ; {x,y,z} • Le produit cartésien des domaines D1, D2, …, Dn noté D1xD2x…xDn est l’ensemble des tuples (v1,v2,…, vn) tel que vi  Di , 1  i  n • ex : n=2,D1 = {1,2}, D2 = {x,y}, D1xD2 ={(1,x), (1,y), (2,x), (2,y)} • Une relation ou table est un sous-ensemble du produit cartésien d’un ou plusieurs domaines: R  D1xD2x…xDn • ex : R = {(1,x), (1,y), (2,y)}

16. Formalisation du modèle relationnel de données Une relation R  D1xD2x…xDn est caractérisée par : • son nomR • Son aritén • Un ensemble d'attributsA1, A2, … An correspondant aux composantes d'un tuple • Di est le domaine de l'attributAi c'est à dire l'ensemble des valeurs possibles deAi (1  i  n) • Le schéma de la relation : R (A1, A2, … An)

17. Formalisation du modèle relationnel de données • n=2,D1 = {1,2}, D2 = {x,y}, R = {(1,x), (1,y), (2,y)} • attributchiffre pour la première composante • attributlettre pour la seconde composante • schéma : R(chiffre,lettre) domaine(chiffre)= D1 etdomaine(lettre)= D2 • Autre exemple de schéma de relation : Pièce (nop, nomp, prix) domaine (nop) : entiers positifs domaine (nomp) : chaînes de caractères domaine (prix) : réels positifs

18. Formalisation du modèle relationnel de données L'extension d'une relation : • est l'ensemble de ses tuples • est représentée par un tableau à deux dimensions • chaque colonne correspond à un attribut • chaque ligne correspond à un tuple Extension possible de la table pièce

19. Extension d'une table (ou relation) Vin Nom de la relation Nom d'attribut Vin tuple

20. Notion de valeur NULL La valeur d'un attribut dans un tuple peut être absente • Cette absence de valeur est notée NULL • Cas de l'attribut no-téléphone pour personne n'ayant pas de téléphone • Cas de l'attribut prix pour une pièce dont le prix n'est pas encore fixé Pièce

21. Autres notions dans un schéma de relation : Clé primaire • Clé primaire d'une relation : Groupe d’attributs minimum qui identifie de manière unique tout tuple dans une relation ex : {nop} dans la relationpièce {nof} dans la relationfournisseur. • Convention : clé primaire soulignée dans le schéma ex : Pièce (nop, nomp, prix)

22. Autres notions dans un schéma de relation : Clé étrangère • Clé étrangère d’une relation : l’attribut ou les attributs qui constituent la clé primaire d’une autre relation.  • Convention : clé étrangère en italique dans le schéma Soit la base de données : pièce (nop, nomp, prix) fournisseur (nof,nomf,ville) vente (nop,nof) vente.nopest une clé étrangère et référencepièce.nop vente.nofest uneclé étrangère et référencefournisseur.nof

23. Plan du cours • Introduction aux Bases de Données (BD) • Modèle relationnel de données • Algèbre relationnelle • De l’algèbre relationnelle à SQL

24. 3. Algèbre relationnelle (A.R.) A.R. : Langage de manipulation de données relationnelles A.R. : huit opérateurs s'appliquant à une ou deux relations et donnant une relation comme résultat - Union, intersection, différence - Restriction (ou sélection) - Projection - Produit cartésien - Jointure

25. Opérateur d'union Définition L'union de deux relations R et S de même schéma est une relation T de même schéma contenant l'ensemble des tuples de R et S. Notation T = UNION (R,S) T  R S

27. Opérateur d'intersection Définition L'intersection de deux relations R et S de même schéma est une relation T de même schéma contenant l'ensemble des tuples appartenant simultanément à R et à S. Notation T = INTERSECT (R,S) T  R S

29. Opérateur de différence Définition La différence de deux relations R et S de même schéma est une relation T de même schéma contenant l'ensemble des tuples appartenant à R et n'appartenant pas à S. Notation T = MINUS (R,S) T - S R

31. Opérateur de projection Définition La projection d'une relation R de schéma R(A1, A2,...,An) sur les attributs {Ai1, Ai2, ... , Aip} est une relation R' de schéma R' (Ai1, Ai2, ..., Aip) dont les tuples sont obtenus par élimination des attributs de R n'appartenant pas à R' et par suppression des tuples en double. Notation T = PROJECT (R / {Ai1, Ai2, ..., Aip}) T Ai1, Ai2, …Aip R

33. Opérateur de restriction Définition La restriction d'une relation R à l'aide d'une condition C est une relation R' de même schéma dont les tuples sont ceux de R satisfaisant la condition C. Notation T = RESTRICT (R / C) T C R

35. Condition de restriction La condition C d'une restriction est une formule logique quelconque avec des connecteurs ET () et OU () entre conditions simples de la forme Ai aoù • Aiest un nom d’attribut • a est un élément du domaine de Ai (constante) • est un opérateur de comparaison(=, <, >, , >=, <=) ex. (Cru="Chablis"  Cru="Mâcon") Millésime<1988

36. T x R S Opérateur de produit cartésien Définition Le produit cartésien de deux relations R et S (de schémas quelconques) est une relation T ayant pour attributs la concaténation de ceux de R et de S et dont les tuples sont toutes les concaténations d'un tuple de R à un tuple de S (renommage des attributs de même nom). Notation T = PRODUCT (R,S)

38. Opérateur de jointure Définition La jointure de deux relations R et S selon une condition C est l'ensemble des tuples du produit cartésien R X S satisfaisant la condition C. Notation T = JOIN (R, S/C) JOIN(R, S/C) = RESTRICT (PRODUCT (R,S)/C) T C R S

40. Jointure naturelle Définition La jointure naturelle de deux relations R et S est l 'équi-jointure (opérateur d'égalité) de R et S sur tous leurs attributs communs. Notation T = JOIN (R, S) T R S

42. Exemples de requêtes algébriques (1/3) Soit la base de données : pièce (nop, nomp, prix) fournisseur (nof,nomf,ville) vente (nop,nof ) Exemple de question : Trouver le numéro des fournisseurs vendant le produit boulon. Requête algébrique : PROJECT ( JOIN (vente , RESTRICT (pièce / nomp = 'boulon')) / {nof})

43. vente fournisseur pièce Une base de données relationnelle

44. Exemples de requêtes algébriques (2/3) Base de données VPC (Vente par Correspondance) Client (numCli, nom, prénom, rue, CP, ville) Produit (numProd, libellé, pu) Commande (numCli, numProd, date, quantité) Exemple de question : Trouver les produits commandés en quantité supérieure à 100 et dont le prix dépasse 1000€ . On affichera les numéros de produit, leur libellé et leur prix unitaire ainsi que la date de la commande. Notons Res la relation résultat et R1, R2, R3 des relations intermédiaires

45. Res numProd,libellé,pu,date R3 R1 R2 pu > 1000 qté > 100 Produit Commande Exemple de requête algébrique (3/3) R1 = RESTRICT(Produit/ pu >1000) R2 = RESTRICT (Commande/qté > 100) R3 = JOIN(R1,R2) Res = PROJECT (R3/{numProd,libellé,pu,date})

46. A vous : construire les requêtes algébriques répondant aux questions (BD VPC) 1) Libellé et prix unitaire de tous les produits 2) Libellé des produits de prix inférieur à 50€ 3) Nom et prénom des clients ayant commandé le produits numéro 56. 4) Nom des clients n'ayant pas commandé le produit numéro 56.

47. Plan du cours • Introduction aux Bases de Données (BD) • Modèle relationnel de données •  Algèbre relationnelle • De l’algèbre relationnelle à SQL

48. 4.De l’algèbre relationnelle à SQL Standard SQL • Interrogation de données • Définition de données • Mise à jour de données

49. a) Standard SQL • SQL : Structured Query Language • SQL est un standard ANSI/ISO depuis 1986 • Version SQL-92 ou SQL2 (standard bien supporté) • Version SQL-99 ou SQL3 (dernier standard, peu supporté) • postgreSQL implémente bien SQL2 • Dans ce cours : commandes SQL2 pour • Interrogation de données • Définition de données • Mise à jour de données

50. a) Interrogation de données SQL : langage pour les BDR basé sur l’algèbre relationnelle • Traduction des opérateurs de l’algèbre en SQL (projection, restriction, jointure …) • commande SELECT • Opérateurs supplémentaires pour trier ou agréger des données