Bases de Données Relationnelles

1. Samir Chouali samir.chouali@lifc.univ-fcomte.fr Bases de Données Relationnelles (à partir du cours de G.Cécé ) - 1 -

2. Bibliographie • « Conception et architecture des Bases de Données», R. Elmasri, S. Navathe et D. Serain, Pearson Education, 2004. • « Des Bases de Données à l'Internet », P. Mathieu, Vuibert, 2000.http://www.lifl.fr/~mathieu/bdd • « Bases de Données Objet & Relationnel »,G. Gardarin, Eyrolles, 1999. - 2 -

3. Plan • Généralités • Définitions • Propriétés des SGBD • Modélisation • Le Modèle Conceptuel de Données (MCD) • Le Modèle Relationnel (MR) • Passage du MCD au Modèle Relationnel • Contraintes d’intégrité • Algèbre relationnelle • Langage de requêtes SQL • Normalisation des relations - 3 -

4. Définitions • Base de données : • Ensemble de données qui modélisent une partie du monde réel pour une application informatique. • Système de Gestion de Base de Données (SGBD) : • Outil qui permet d’insérer, modifier, retirer et rechercher des données ; le tout de façon efficace. • Interface entre les utilisateurs et l’information brute • Présente les informations dans une forme exploitable - 4 -

5. Les Trois Couches d’un SGBD • Système de gestion de fichiers : • gère le stockage physique des informations (dépend du matériel). • SGBD interne : • assemble et place les données, gère les liens entre les données et y garantit un accès rapide. • SGBD externe : • s’occupe de la présentation et de la manipulation des données. Permet l’utilisation de langages de requêtes élaborés et d’outils de présentation adaptés. - 5 -

6. Propriétés des SGBD (1) • Indépendance physique • transparence de la gestion des données au niveau physique. • Indépendance logique • chacun possède sa propre vue des données. • Manipulable par des non informaticiens • utilisation de langages non procéduraux (pas de programmation). - 6 -

7. Propriétés des SGBD (2) • Accès aux données efficaces • optimisation des accès aux données. • Administration centralisées des données • outils de sauvegarde des données, de réplication, ... • Non redondance des données. • évite la duplication des informations ce qui facilite la gestion. • Cohérence des données • gestion automatique des contraintes d’intégrité. - 7 -

8. Propriétés des SGBD (3) • Partage des données • plusieurs personnes peuvent accéder aux données simultanément tout en conservant l’intégrité de la base. • Sécurité des données • protection contre les accès non autorisés. • tolérance aux pannes. - 8 -

9. Types de Bases de Données (1) • Les bases hiérarchiques • Les bases réseaux (CODASYL) • Les bases relationnelles • données sous formes de tables basées sur l’algèbre relationnelle et un langage, de manipulation, déclaratif (SQL). • Les bases déductives • données sous formes de tables (prédicats), le langage d’interrogation est basé sur le calcul des prédicats et la logique du premier ordre. - 9 -

10. Types de Bases de Données (2) • Les bases objets • données représentées sous forme d’instances de classes hiérarchisées. 75% des SGBD sont des bases relationnelles Les bases objets gagnent du terrain - 10 -

11. 4. Le marché des SGBD • Marché en 2003 : 7 milliards de dollars • Aujourd’hui 3 leaders : IBM, Oracle, Microsoft source: www.gartner.com Mai 2004

12. Modèle Conceptuel de Données(MCD) Modélisation - 12 -

13. Modélisation Réalité perçue Modélisation conceptuelle Modèle entité association Transformation dans un modèle supporté par un SGBD Modèle relationnel Définition de la structure de données de la base SQL - 13 -

14. Tâche Employé participe Nom de la tâche Coût de la tâche Numéro d’employé Nom Prénom Date d’embauche Fonction Rémunération Date début Date fin Modélisation • Le résultat de l’analyse est le Modèle Conceptuel de Données (MCD) qui décrit la future base de données à l’aide d’entités et d’associations. 0,n 1,n - 14 -

15. Vocabulaire (1) • Entité : • représentation d’un objet, matériel ou immatériel(ex. : Étudiant, Voiture, Vin, etc...). • une entité est composée de propriétés. • Propriété : • donnée élémentaire et indécomposable (ex. : age, note, nom, adresse, date de naissance, etc...). - 15 -

16. Vocabulaire (2) • Association • représentation d’un lien entre différentes entités. • des propriétés peuvent être attachées à une association. • Dimension • nombre d’entités intervenants dans l’association(1 : association réflexive; 2 : association binaire; n : association n-aire) • Cardinalité • caractérise le lien entre une entité et une association. Elle est constituée d’une borne minimale et d’une borne maximale. - 16 -

17. Vocabulaire (3) • Cardinalité (suite) • Nombre de fois qu’une occurrence de l’entité participe aux occurrences de l’association. • Identifiant • une ou plusieurs propriétés d’une entité telles qu’à chaque valeur de l’identifiant correspond une et une seule occurrence de l’entité. • l’identifiant d’une association est constitué de la réunion des identifiants des entités qui participent à l’association. - 17 -

18. encadre Employé Projet Numéro d’employé Nom Prénom Date d’embauche Fonction Rémunération Numéro du projet Thème du projet Titre du projet Date de début Date de fin coordonne Tâche participe Constitué_de Date début Date fin Nom de la tâche Coût de la tâche Exemple de MCD a pour chef 0,1 0,n est chef de 0,n 1,1 1,n 0,n 1,n 1,1 - 18 -

19. Modèle Conceptuel des Données • Exemple "KaafKaaf" • PARTIE 1 • La société "KaafKaaf" désire informatiser son système de facturation. Les factures devraient se présenter de la façon suivante • Créez un MCD, qui permet de modéliser correctement le système d'information nécessaire, sachant que: • Un client peut bien sûr recevoir plusieurs factures, mais il est uniquement considéré comme tel à partir du moment où il reçoit sa première facture. • Une facture concerne un et un seul client.

20. Modèle Conceptuel des Données • Remarque: • Bien que le numéro du client n'apparaisse pas en tant que tel sur la facture, il est préférable d'ajouter cette propriété artificielle à l'entité Client, et de la définir comme identifiant de cette entité. Cela nous empêche de devoir définir un identifiant composé de trop de propriétés.

21. Modèle Conceptuel des Données • PARTIE 2 • Il s'agit d'étendre le MCD de la partie 1. • Le responsable de la facturation de la société désire rendre les factures plus informatives. Comme un client peut acheter plusieurs articles différents en même temps, la facture devrait indiquer pour chaque article le numéro , un libellé, le prix unitaire, la quantité vendue et le prix total pour ce type d'article. • Voici l'aspect que la facture devrait avoir: • Proposez un nouveau MCD qui reflète ces modifications, en respectant que: • Tous les articles disponibles sont stockés (p.ex. No=234 Libellé="Marteau" PU=470 Luf.). Même si un article n'est pas encore considéré par une facture, il existe dans le système d'information.

22. Modèle Conceptuel des Données • Remarques: • L'entité Facture ne contient plus la propriété Montant. Il existe une règle générale de conception qui dit: • Aucune propriété qui peut être calculée à partir d'autres propriétés existantes, ne devra être stockée dans le MCD. • Pour la même raison, on n'a pas besoin de modéliser explicitement le prix à payer pour l'achat d'une quantité d'articles donnés. Le prix pour chaque article figurant sur la facture peut être calculé à partir du prix unitaire et de la quantité

23. Modèle Conceptuel des Données • Exemple "Gestion d'école" • PARTIE 1 • Dans une école, on veut informatiser le système d'information qui gère les classes. • Elaborez un MCD sachant que: • Un élève est caractérisé par son no. matricule, son nom et prénom, ainsi que sa date de naissance. • Une classe est caractérisée par le nom de la classe et par une indication du cycle. • Il faudra prévoir de connaître la fréquentation des classes des élèves sur plusieurs années consécutives. • Un élève enregistré dans le système fréquente au moins une classe au cours des années.

24. Modèle Conceptuel des Données • PARTIE 2 • Il s'agit maintenant de concevoir une extension au MCD précédent qui permet de représenter la situation suivante: • La direction de l'école désire également saisir tous les professeurs dans le système d'information. Un professeur est caractérisé par un code interne unique , son nom et prénom et la matière qu'il enseigne. Nous supposons que chaque professeur enseigne une seule matière. • Modélisez le fait que chaque classe est enseignée chaque année par un ou plusieurs enseignants. Un enseignant peut bien sûr donner des cours dans plusieurs classes, mais peut également ne pas donner des cours pendant une ou plusieurs années.

25. Exercices • Exercice 1 Un magasin de sport a besoin de stocker ses informations principales dans une base de données. Il s'agit de stocker toutes les informations relatives à ses produits, ses clients et ses fournisseurs. Il souhaite que l'organisation soit facilitée par le regroupement des produits en différentes catégories. Et il souhaite à partir de là pouvoir facilement consulter ses stocks, la liste de ses meilleurs clients, le top 10 de ses produits vendus.Créer le MCD correspondant. - 25 -

26. Exercices • Exercice 2 Une agence immobilière a besoin d'une base de données pour gérer ses locations. Il faut entre autres qu'elle soit capable de cibler les logements pouvant convenir à un client donné. Elle souhaite également pouvoir facilement dresser la facture d'un client donné, savoir ce qu'elle doit verser à un propriétaire donné, ou calculer les primes de ses agents.Créer le MCD correspondant. - 26 -

27. Le Modèle Relationnel - 27 -

28. Modèle Relationnel • Les SGBD relationnels • organisent les données en tables • sont basés sur l’algèbre relationnelle (théorie des ensembles). - 28 -

29. Exemple - 29 -

30. Domaines • Exemples • ENTIER • REEL • CHAINES DE CARACTERES • SALAIRE = {4 000..100 000} • COULEUR= {BLEU, BLANC, ROUGE} • POINT = {(X:REEL,Y:REEL)} • TRIANGLE = {(P1:POINT, P2:POINT, P3:POINT)} - 30 -

31. Produit Cartésien •  Le produit cartésien D1x D2x ... x Dn est  l'ensemble des tuples (n-uplets) : <V1,V2, …, Vn> tel que Vi Di • Exemple • D1 = {Bleu, Blanc, Rouge} • D2 = {Vrai, Faux} Bleu Vrai Bleu Faux Blanc Vrai Blanc Faux Rouge Vrai Rouge Faux - 31 -

32. CoulVins Coul Choix Bleu Faux Blanc Vrai Rouge Vrai Relation (ou Table) • Sous-ensemble du produit cartésien d'une  liste de domaines • Une relation est caractérisée par un nom • Exemple • D1 = COULEUR • D2 = BOOLEEN - 32 -

33. VINS CRU MILL REGION COULEUR Exemple de Relation CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC TAVEL 1986 RHONE ROSE CHABLIS 1986 BOURGOGNE BLANC ST-EMILION 1987 BORDELAIS ROUGE - 33 -

34. Attribut • Vision tabulaire du relationnel • Une relation est une table à deux dimensions • Une ligne est un tuple • Un nom est associé à chaque colonne afin de la repérer autrement que par sa position • Attribut • nom donné à une colonne d'une relation • prend ses valeurs dans un domaine - 34 -

35. Clé • Groupe d'attributs minimum qui détermine un tuple unique dans une relation • Exemples • {CRU,MILLESIME} dans VINS • NSS dans PERSONNE • Clé étrangère • Groupe d'attributs formant la clé d’une autre relation - 35 -

36. Schéma • D’une relation • Nom de la relation, liste des attributs avec domaines et clés de la relation • Exemple • VINS(NV :entier, CRU :texte, MILL :entier, DEGRE :réel, REGION :texte) • Par convention, la clé primaire est soulignée • Schéma d'une bd relationnelle • C’est l'ensemble des schémas des relations composantes - 36 -

37. Exemple de Schéma • Exemple BUVEURS (NB, NOM, PRENOM, TYPE) VINS (NV, CRU, MILL, DEGRE) ABUS (NB, NV, DATE, QUANTITE) • Clés étrangères ABUS.NV fait référence à VINS.NV ABUS.NB fait référence à BUVEURS.NB - 37 -

38. VINS BUVEURS ABUS Diagramme des Liens NB NOM PRENOM TYPE NV CRU DEGRE MILL. NB NV DATE QUANTITE - 38 -

39. Vocabulaire comparé - 39 -

40. Du MCD vers les Tables Relationnelles - 40 -

41. Transformation • Traitement des entités • Chaque entité devient une table. • Chaque propriété devient une colonne de cette table. • L’identifiant d’une entité devient la clé primaire de la table correspondante. • Traitement des associations • Le traitement des associations dépend des cardinalités des ces associations. - 41 -

42. Association binaire - 42 -

43. Vin Producteur NoVin Cru Mill. Degré NoP Nom Région Exemple : (*,1) - (*,*) De : 1,1 1,n Recolte Quantite À : - 43 -

44. Association n-aires • création d’une nouvelle table dont la clé est au moins composée des identifiants des différentes entités. • les propriétés de cette association migrent aussi vers la nouvelle table. - 44 -

45. Prof Cours NoProf Nom NoCours Intitulé Enseigne 1,n 1,n VolumeHoraire Exemple De : À : - 45 -

46. encadre Employé Projet Numéro d’employé Nom Prénom Date d’embauche Fonction Rémunération Numéro du projet Thème du projet Titre du projet Date de début Date de fin coordonne Tâche participe Constitué_de Date début Date fin Nom de la tâche Coût de la tâche Exercice a pour chef 0,1 0,n est chef de 0,n 1,1 1,n 0,n 1,n 1,1 - 46 -

47. Contraintes - 47 -

48. Contraintes d’Intégrité • Une contrainte d’intégrité est une assertion (c-à-d une propriété) qui doit être vérifiée par les données de la base • Si une contrainte d’intégrité n’est pas respectée lors d’une modification des données, la modification est rejetée et l’utilisateur est averti - 48 -

49. Contraintes de Clé   • Tous les constituants d’une clé primaire doivent être renseignés. Il ne peuvent prendre la valeur Null. • Deux enregistrements différents ne peuvent avoir de valeurs de clé identiques. - 49 -

50. Contraintes de Types de Données • Permet de spécifier le domaine de validité des valeurs des attributs. • Exemples : • une note doit être comprise entre 0 et 20. • la date d’emprunt d’un livre est antérieure à sa date de retour - 50 -