M. BENJELLOUN 2006-07
publicité
Les Bases de données relationnelles de la théorie à la pratique Mohammed BENJELLOUN Service d’Informatique Faculté Polytechnique de Mons [email protected] 2006-2007 M. BENJELLOUN 2006-07 Bases de données relationnelles - 1 Objectifs - Comprendre les concepts et techniques sur lesquels reposent les fonctions principales d'un système de gestion de bases de données. - Pouvoir représenter dans une base de données le contenu d'information d'un domaine d'application. - Pouvoir utiliser ces fonctions pour mettre en oeuvre une application simple reposant sur une base de données (Analyse, conception, structuration des données, mise en oeuvre d’une base de donnée relationnelles avec intéraction... ). Contenu L'approche base de données Conception d'une base de données Bases de données relationnelles Pratique d'un SGBD M. BENJELLOUN 2006-07 Bases de données relationnelles - 2 Etapes et Démarche de modélisation 1. Analyse de la situation existante et des besoins 2. Création d'une série de modèles qui permettent de représenter tous les aspects importants 3. A partir des modèles, implémentation d'une base de données M. BENJELLOUN 2006-07 Bases de données relationnelles - 3 Un modèle de base de données est un ensemble d’éléments qui décrit les données et permet d’exprimer les propriétés et les liens entre ces données. Le modèle est souvent représenté de manière graphique. Il se compose d’une description des données et de leurs relations ainsi que d’un ensemble de contraintes concernant la valeur que peuvent prendre les données ou concernant les liens qui les relient. Un schéma de base de données est une description de la structure des données à gérer via l’utilisation d’un langage déterminé. M. BENJELLOUN 2006-07 Bases de données relationnelles - 4 Définitions Information : Une information est un élément qui permet de compléter notre connaissance sur un objet, un événement, une personne ... . Exemple: Le nom d'une personne est une information concernant cette personne. Système d'information : Un système d'information est constitué par l'ensemble des informations relatives à un domaine bien défini. Exemple: Librairie : stock, commandes, ventes … Un S. I. existe indépendamment des techniques informatiques. Il contient les données et les traitements nécessaires pour assimiler et stocker les informations entrantes et produire les informations sortantes. M. BENJELLOUN 2006-07 Bases de données relationnelles - 5 Définitions Base de données : Une base de données (BD) est un ensemble bien structuré de données relatives à un sujet global. Ces données peuvent être de nature et d'origine différentes. Les données sont des faits, connus et qui ont un sens pour l’utilisateur. Ces données doivent avoir une relation entre elles. “collection de données enregistrées ensemble, sans redondance pénible ou inutile, pour servir plusieurs applications, on y enregistre les données de façon à ce qu’elles soient indépendantes des programmes qui les utilisent, on utilise une approche commune et contrôlée pour ajouter, modifier, retrouver des données”. James Martin Collection de données persistantes utilisées par des systèmes d’application de certaines entreprises … M. BENJELLOUN 2006-07 Bases de données relationnelles - 6 Définitions Une base de données correctement construite permet de partager les données entre plusieurs utilisateurs, de restreindre l’accès ou la modification des données, d’assurer l’intégrité des données et d’équilibrer les conflits des besoins tout en réduisant les redondances et en évitant les incohérences Exemples: M. BENJELLOUN 2006-07 FPMs ⇨ BD ⇨ sur tous les étudiants. Une banque ⇨ BD ⇨ sur tous les clients. Une société d'assurances ⇨ BD ⇨ contrats d'assurances et sinistres. Bases de données relationnelles - 7 Qu'attendre Pourqoui ---- une base de données ? Lorsqu’on a besoin d’organiser les données en un ensemble structuré et : • • • • • • • • • Contrôle centralisé des données Redondance réduite Incohérence évitée Données partagées Normes imposées Restrictions de sécurité possibles Intégrité assurée (36/15/2005 est impensable) Conflits des besoins équilibrés Et stocker, consulter, modifier des informations M. BENJELLOUN 2006-07 Bases de données relationnelles - 8 Système de gestion de bases de données : Un système de gestion de bases de données (SGBD) est un programme qui permet la représentation informatique des données, qui nous permet de créer, de modifier et d'exploiter des bases de données. Ce système constitue donc notre interface pour accéder aux données. SGBD BD logiciel gérant une BD. Il permet à un utilisateur de communiquer (requêtes) avec une base de données pour : - décrire et organiser les données sur les mémoires, - rechercher, sélectionner et modifier les données. M. BENJELLOUN 2006-07 Bases de données relationnelles - 9 Qu'attendre ---- un SGBD? Pourqoui Un SGBD assure - la description des données, - leur recherche et mise à jour, - la sûreté : vérifier les droits d’accès des utilisateurs ; limiter les accès non autorisés ; crypter les informations sensibles - la sécurité : sauvegarde et restauration des données ; limiter les erreurs de saisie, de manipulation - l’intégrité : définir des règles qui maintiennent l’intégrité de la base de données (contraintes d’intégrité) - la concurrence d’accès : détecter et traiter les cas où il y a conflit d’accès entre plusieurs utilisateurs et les traiter correctement. M. BENJELLOUN 2006-07 Bases de données relationnelles - 10 +ieurs MODELES de BASES de DONNEES •a) le modèle hiérarchique: les données sont classées hiérarchiquement, selon une arborescence descendante. Ce modèle utilise des pointeurs entre les différents enregistrements. Le plus ancien, peu souple. •b) le modèle réseau: Comme le modèle hiérarchique ce modèle utilise des pointeurs vers des enregistrements. Moyennement souple, complexe pour le développement, performance moyenne. •c) le modèle relationnel (SGBDR, Système de gestion de bases de données relationnelles): les données sont enregistrées dans des tables. La manipulation de ces données se fait selon la théorie mathématique des relations , théorie ensembliste. (du mathématicien CODD). Fort souple, aisé à développer. •d) le modèle objet (SGBDO, Système de gestion de bases de données objet): les données sont stockées sous forme de classes. •a) M. BENJELLOUN 2006-07 •b) fort souple, aisé à développer •c) Bases de données relationnelles - 11 •d) Les caractéristiques L'architecture à trois niveaux définie par le standard ANSI/SPARC permet d'avoir une indépendance entre les données et les traitements. D'une manière générale un SGBD doit avoir les caractéristiques suivantes: • Indépendance physique: Le niveau physique peut être modifié indépendamment du niveau conceptuel. Cela signifie que tous les aspects matériels de la base de données n'apparaissent pas pour l'utilisateur, il s'agit simplement d'une structure transparente de représentation des informations • Manipulabilité: des personnes ne connaissant pas la base de données doivent être capables de décrire leur requêtes sans faire référence à des éléments techniques de la base de données • Rapidité des accès: le système doit pouvoir fournir les réponses aux requêtes le plus rapidement possible, cela implique des algorithmes de recherche rapides • Administration centralisée: le SGBD doit permettre à l'administrateur de pouvoir manipuler les données, insérer des éléments, vérifier son intègrité de façon centralisée • Limitation de la redondance: le SGBD doit pouvoir éviter dans la mesure du possible des informations redondantes, afin d'éviter d'une part un gaspillage d'espace mémoire mais aussi des erreurs • Vérification de l'intégrité: les données doivent être cohérentes entre elles, de plus lorsque des éléments font références à d'autres, ces derniers doivent être présents • Partageabilité des données: le SGBD doit permettre l'accès simultané à la base de données par plusieurs utilisateurs • Sécurité des données: Le SGBD doit présenter des mécanismes permettant de gérer les droits d'accès aux données selon les utilisateurs M. BENJELLOUN 2006-07 Bases de données relationnelles - 12 Historique M. BENJELLOUN 2006-07 Bases de données relationnelles - 13 Modèle relationnel Les concepts mis en oeuvre dans le modèle relationnel sont fondés sur une théorie mathématique directement issue de l'algèbre relationnelle, de la théorie des ensembles et de la logique formelle. Cette technologie a vu le jour dans les années 70 avec les travaux de Codd * Objets simples : table, ligne, colonne * Basé sur des objets mathématiques bien connus : - Relation, n-tuple, ensemble, etc. * Opérations d'interrogation - Sélection, projection, jointure Actuellement le modèle le plus répandu (de loin) 1980 : Les systèmes de gestion de bases de données relationnels apparaissent sur le marché. 1990 : Les systèmes de gestion de bases de données relationnels dominent le marché. M. BENJELLOUN 2006-07 Bases de données relationnelles - 14 Les objectifs du modèle relationnel : · proposer des schémas de données faciles à utiliser, · fournir une approche méthodologique dans la construction des schémas. · améliorer l'indépendance logique et physique, · mettre à la disposition des utilisateurs des langages de haut niveau pouvant éventuellement être utilisés par des non informaticiens, · optimiser les accès à la base de données, · améliorer l'intégrité et la confidentialité, •Manipulations relationnelles, en général exprimées en SQL, transforment des tables en une table Les données sont perçues par l’utilisateur comme des tables M. BENJELLOUN 2006-07 Algèbre Relationnelle JOIN: relie 2 tables grâce aux valeurs communes de 2 colonnes communes Bases de données relationnelles - 15 Opérations relationnelles • Sélection : – Projection – Restriction – Jointure – Division – Agrégation • Opération suppl. • Mise à jour • Création d ’une vue M. BENJELLOUN 2006-07 Bases de données relationnelles - 16 Liste non exhaustive de SGBD relationnels : Adabas de Software AG Access de Microsoft DB2 : IBM Informix : Unix Ingres : Vax, IBM, Sun, HP, Dos MS-sql MySQL (logiciel libre) Oracle : Oracle (multi plateforme) Progress : Unix, Dos, VMS, OS/2 PostgreSQL (logiciel libre) SqlServer de Microsoft Sybase de Sybase M. BENJELLOUN 2006-07 Bases de données relationnelles - 17 Mise en oeuvre d’un SGBD On distingue trois niveaux d’appréhension définis par la norme ANSI/SPARC (architecture de référence d'un SGBD). A chaque niveau correspond un schéma de représentation : - le niveau interne avec le schéma physique Description du stockage des données au niveau des unités de stockage - le niveau conceptuel avec le schéma conceptuel Description de la structure des données de la base, description de leurs propriétés (relations qui existent entre elles), sans soucis d'implémentation physique ni de la façon de s'en servir. le niveau externe avec les vues (comment l’utilisateur voit les données) Description pour chaque utilisateur de sa perception des données. ES ES ES CS IS M. BENJELLOUN 2006-07 Bases de données relationnelles - 18 ES ES CS IS M. BENJELLOUN 2006-07 ES ESs : Schémas Externes CS : Schéma Conceptuel IS : Schéma Interne L'administrateur aura pour rôle : • la conception du modèle à partir du monde réel à représenter, • le réglage du schéma physique pour certaines optimisations de performances, • le maintien de la base de données physique, • la description des schémas externes à l'usage des utilisateurs finaux. Bases de données relationnelles - 19 Schémas Externes (ESs) Définit une vue de la BD Vues SQL, Vbasic, orientés Web notamment (HTML, XML…) … ES ES ES CS IS • Une BD est en général munie de plusieurs différentes ESs • Mais tous ont le CS comme racine commune M. BENJELLOUN 2006-07 Bases de données relationnelles - 20 Schéma Interne (IS) CS Comment transformer les objets? Comment ils seront stockés? Comment y accèdes? Schéma Conceptuel Merise Merise est une des méthodes de conception et de développement de projets informatiques. Cette méthode date de 1978-1979, et fait suite à une consultation nationale lancée en 1977 par le ministère de l’Industrie français dans le but de choisir des sociétés de conseil en informatique dont la mission était de définir une méthode de conception de systèmes d’information. Une des techniques permettant de concevoir une base de données relationnelle est basée sur cette méthode. En effet, une des caractéristiques principales de la méthode Merise est la séparation des données et des traitements du futur système d’information. M. BENJELLOUN 2006-07 Bases de données relationnelles - 21 Objets relationnels: domaines et relations Terme relationnel formel Equivalent informel • • • • • • • • relation n-uplet cardinalité attribut degré clé primaire clé étrangère domaine relation perçue entre entités (!!table) ligne ou enregistrement nombre de lignes colonne ou champ nombre de colonnes identificateur unique référence = attribut principal ailleurs Ensemble de valeurs légales (ensemble des valeurs d’un attribut) M. BENJELLOUN 2006-07 Bases de données relationnelles - 22 Les composants d'une base de données relationnelle Quatre types d'objets. Tables, Requêtes, Formulaires, Rapports. 1. Les Tables Une table est une collection de données relatives à un domaine bien défini. N° Mat Table : Employés_D isney NOM SALAIRE Code post. 159 Donald 1500 € 7000 132 Obélix 1900 € 5060 1187 Picsou 1134 € 1000 354 ….. …. …. Valeurs de l’attribut M. BENJELLOUN 2006-07 Enregistrement, N_Uplet Un champ de données (Attribut) Bases de données relationnelles - 23 Clé primaire Pour identifier de manière unique chaque enregistrement de la table. La clé primaire, constituée d'un ou de plusieurs champs, nous permet d'identifier de manière unique chaque enregistrement d'une table. Pour définir des liens entre plusieurs tables la clé primaire est indispensable. 2. Les requêtes (angl. Queries) Les requêtes ≡ "questions" qu'on pose au SGBD. Le résultat est toujours un sous-ensemble d'une ou de plusieurs tables. Il existe 4 types de requêtes: 1. Requêtes de sélection. 2. Requêtes d'insertion. 3. Requêtes de modification. 4. Requêtes de suppression. Formuler la requête Select Insert Update Delete Analyser et Exécuter la requête Pour chaque requête nous retrouvons le cycle suivant: Résultat de la requête M. BENJELLOUN 2006-07 Bases de données relationnelles - 24 3. Les formulaires (angl. Forms) Les formulaires pour ajouter, modifier ou supprimer des données dans les tables. Les formulaires offrent certains avantages: facilité d'utilisation, sécurité des donnée 4. Les rapports (angl. Reports) Pas de dialogue interactif avec l'utilisateur. Un rapport se base généralement sur une ou plusieurs tables ou le résultat d'une requête. M. BENJELLOUN 2006-07 Bases de données relationnelles - 25 Niveaux d’abstraction Cahier des charges en accord avec le client (activité et besoins) créer une représentation virtuelle de la réalité. produire quatre modèles de données relatifs à quatre niveaux d’abstraction. Le niveau conceptuel identifie et décrit formellement l’ensemble des informations du domaine géré par le futur système d’information. Ce niveau amène donc le concepteur à construire une représentation formelle de la signification des données. Le niveau organisationnel exprime la répartition organisationnelle des données informatisées, la sécurité des données par rapport aux acteurs des unités organisationnelles et précise quelles sont, parmi les données définies au niveau conceptuel, celles qui seront prises en compte par le futur système informatisé. On ne développera pas cet aspect vu qu’il dépend fortement de l’environnement d’intégration du système d’information. Le niveau logique fournit une description des données prenant en compte les moyens informatiques de mémorisation et l’implémentation du système par un SGBD. C’est également ici que l’on retrouvera l’algèbre relationnelle. Le niveau physique exprime les choix techniques et décrit les données de la base de données dans la syntaxe du système de gestion adopté. M. BENJELLOUN 2006-07 Bases de données relationnelles - 26 Cycle d'abstraction de conception des S.I. La conception du système d'information se fait par étapes, afin d'aboutir à un système d'information fonctionnel reflétant une réalité physique. Il s'agit donc de valider une à une chacune des étapes en prenant en compte les résultats de la phase précédente. D'autre part, les données étant séparées des traitements, il faut vérifier la concordance entre données et traitement afin de vérifier que toutes les données nécessaires aux traitements sont présentes et qu'il n'y a pas de données superflues. Cette succession d'étapes est appelée cycle d'abstraction pour la conception des systèmes d'information: Niveau M. BENJELLOUN 2006-07 Statique (données) Conceptuel MCD Organisationnel ou logique MLD Opérationnel ou physique MPD Dynamique (traitements) MCT MOT (QUI ? QUAND ?) Bases de données relationnelles - 27 MOPT Donc la démarche classique d'un projet en BD comprend les étapes suivantes: 1. Analyse de la situation existante et des besoins 2. Création d'une série de modèles qui permettent de représenter tous les aspects importants 3. A partir des modèles, implémentation d'une base de données Méthodologie pour traduire un système d'information naturel en une base de données Univers de l’application Niveaux d’abstractions Elaboration du Modèle E-R Passage au Modèle Relationnel Implémentation Sur SGBD-R M. BENJELLOUN 2006-07 Niveau Conceptuel Analyse Réel Perçu MCD Schéma Conceptuel Niveau Logique Niveau Physique Bases de données relationnelles - 28 MLD MPD Schéma Logique Application? Choix de l’application / groupe Rapport Univers de l’applicatio n Elaboration du Modèle E-R Def. Dom. C.Chg Niveau Conceptuel Schéma Conceptuel Passage au Modèle Relationnel Niveau Logique Implémentation Sur SGBD-R Niveau Physique Schéma Logique Implémentation Sur SGBD-R M. BENJELLOUN 2006-07 Bases de données relationnelles - 29 Schéma Physique 1er rapport : mars 2006 ème 2 rapport : ….. 3ème rapport : ….. Cahier de charges. Cahier de charges et MCD. Rapport final Votre rapport doit respecter scrupuleusement la table des matières suivante : 1. 2. 3. 4. 5. 6. 7. Cahier de charges de l’application MCD MLD MPD (qlq tables et relations) Implémentation Conclusion Explications du fonctionnement de votre base de données Utilisez : Formulaires, index, requêtes, rapport. Pouvoir faire : Trier, exécuter des requêtes en SQL, utiliser des macros en SQL. Genre de documents à éditer : Liste alphabétique des clients,( étudiants, … ) par ville (secteur …) .. Liste par chiffre d’affaire des clients (moyenne pour les étudiants) … Lors de la conclusion : Les besoins futurs ! Quelles seront les évolutions possibles de votre base de données ? N’oubliez pas de joindre une disquette ou un CD de votre base de données. Indiquez aussi les noms des étudiants qui composent le groupe sur le support informatique et le rapport. M. BENJELLOUN 2006-07 Bases de données relationnelles - 30 Analyse Elaboration du Modèle E-R Niveau Conceptuel Schéma Conceptuel 1. Le niveau conceptuel, se base directement sur l'analyse, c’est une représentation du monde réel par un seul modèle. Il décrit, de façon formelle, l'ensemble des données du système d'information, sans tenir compte de l'implémentation informatique de ces données. Ce niveau représente donc la signification des données, se traduit par un formalisme que nous appelons: Modèle conceptuel des données MCD Permet de définir les informations pertinentes pour l’application et d’envisager leur structure. Doit refléter le plus fidèlement possible la réalité à modéliser dans la BD, à tout niveau : données, relation, contraintes de cohérence de données, .. M. BENJELLOUN 2006-07 Bases de données relationnelles - 31 La construction du schéma conceptuel comporte normalement les étapes suivantes : • Définir les objectifs • Analyser la réalité • Tracer le schéma conceptuel Pour la construction du modèle conceptuel, beaucoup de méthodes ont été mises en place mais aucune ne donne réellement satisfaction. On peut cependant les répartir en deux catégories : modélisation directe : Elle consiste à identifier, à partir d’une description exprimée en langage naturel, les entités et les associations en appliquant les règles suivantes : les noms deviennent des entités les verbes deviennent des associations La partie analytique consiste essentiellement à transformer des phrases décrivant certains aspects de la réalité en entités, relations et cardinalités. M. BENJELLOUN 2006-07 Bases de données relationnelles - 32 modélisation par analyse des dép. fonctlles Identifier toutes les propriétés du S.I. à analyser. Cette étape aboutit au dictionnaire des données épuré qui devra comporter ni synonyme, ni donnée calculée ... Il semble que la bonne approche de construction d’un modèle conceptuel des données soit un compromis entre la méthode directe, qui laisse une large part à l’intuition et la méthode basée sur l’étude des dépendances fonctionnelles. Quelle que soit la technique utilisée, le modèle doit être vérifié, normalisé et enrichi de concepts étendus pour représenter le plus fidèlement possible l’application. M. BENJELLOUN 2006-07 Bases de données relationnelles - 33 Passage au Modèle Relationnel Niveau Logique Schéma Logique 2. Le niveau logique se base sur le modèle conceptuel des données. Ce niveau introduit la notion des tables logiques, et constitue donc le premier pas vers les tables des SGBD. Ce niveau est représenté par le: Modèle logique des données : MLD Implémentation Sur SGBD-R Niveau Physique Schéma Physique 3. Le niveau physique, qui se base sur le modèle logique des données, contient finalement toutes les définitions et détails relatifs à l'utilisation d'un SGBD spécifique (p.ex. Access, dBASE, Oracle, Caché ...). A partir de ce niveau, on peut directement créer la base de données. Ce niveau est représenté par le: Modèle physique des données : MPD M. BENJELLOUN 2006-07 Bases de données relationnelles - 34 Le schéma conceptuel Un des modèles possibles pour le schéma conceptuel est le modèle “entité-association”. Proposé par Peter Chen en 1976, il est notamment utilisé dans la méthodologie Merise. Traduire l’analyse du système réel établie préalablement en entité, en relations, en propriétés et en cardinalités. Une entité permet de modéliser un ensemble d’objets de même nature. Les relations sont des liens sémantiques qui peuvent exister entre plusieurs entités. CLIENT Numéro Nom … Les “cardinalités” représentent le nombre possible d’interactions entre les entités et les “attributs” . M. BENJELLOUN 2006-07 Bases de données relationnelles - 35 1, N Passer 1,1 Commande Numéro Date Quantité Le Modèle Conceptuel des Données (MCD) Analyse ⇨ (MCD) : "Schéma Entité-Relation" ou "Schéma Entité-Association". ENTITE Nom d’entité COMMANDE Relation ou Association Nom de la Relation CONCERNER PRODUIT No Cde Date Cde Quantité Cdée No Prod Désignation Prix Unitaire Propriété d’entité Propriété de la Relation M. BENJELLOUN 2006-07 Bases de données relationnelles - 36 ENTITE Dans l'exemple, l'entité Produit ∑ produits S.I.. et l'entité Commande ∑ commandes S.I. PRODUIT Nom de l’entité Propriété 1 Propriété 2 Propriété 3 Propriété 4 No Prod Désignation Prix Unitaire NOM Identifiant Attributs L'identifiant est une propriété (une ou plusieurs) particulière d'un objet telle qu'il n'existe pas deux occurrences de cet objet pour lesquelles cette propriété pourrait prendre une même valeur. Un identifiant est une colonne dont les valeurs permettent de repérer une seule ligne Le choix d'un identifiant correcte est très important pour la modélisation: Choix ? M. BENJELLOUN 2006-07 Bases de données relationnelles - 37 Choix ? Comme choix pour l'identifiant d'une entité nous distinguons généralement 3 possibilités: 1. Une propriété naturelle Exemple: Le nom d'un pays pour une entité Pays 2. Une propriété artificielle qui est inventée par le créateur du MCD Exemple: Le numéro d'un client pour une entité Client, Produit, Commande, … 3. Une propriété composée d'autres propriétés naturelles Exemple: Le nom et la localité pour une entité Entreprise Le modèle conceptuel des données propose de souligner les identifiants M. BENJELLOUN 2006-07 Bases de données relationnelles - 38 Attributs caractéristiques des entités obligatoires ou facultatives avec un domaine (type) de valeurs CLIENT No Nom Adresse VEHICULE Personnes No_Matric. Marque Modèle Année Cylindrée ID_Personne Nom Prénom Sexe Email Adresse Qu'est ce qu'un bon schéma Entité-Relation (« formes normales ») ? ni perte d'information ni redondance contraintes (d’intégrité) entre les valeurs des attributs le but: indépendance / applications (vues particulières) M. BENJELLOUN 2006-07 Bases de données relationnelles - 39 Associations ou Relations Obtenir Personnes Diplômes Année_d_obtention ID_Personne Nom Prénom Sexe Adresse Téléphone Email Code_diplôme Titre_diplôme Abréviation CLIENT VEHICULE APPARTIENT No Marque Modèle Année Cylindrée M. BENJELLOUN 2006-07 No Nom Adresse Bases de données relationnelles - 40 SIGNE CONTRAT No Type Date La notion de relation Une relation décrit un lien entre deux ou plusieurs entités. Chaque relation possède un nom, qui est généralement constitué par un verbe à l'infinitif. Chaque relation a implicitement un identifiant, qui est composé par les identifiants des entités auxquelles elle est liée. CLIENT MCD Relation binaire Passer Commande Numéro Nom Prénom Adresse Code_postal Localité LAC Numéro Date Quantité RIVIERE Déverser Relation ternaire Décharge Relation réflexive (relation récursive) M. BENJELLOUN 2006-07 EMPLOYE CONJOINT N° Matricule Bases de données relationnelles - 41 Cardinalité Les cardinalités précisent la participation de l'entité concernée à la relation. cardinalité minimale CLIENT Numéro Nom Prénom Adresse Code_postal Localité cardinalité maximale 1, N Passer 1,1 Commande Numéro Date Quantité Entre l'entité CLIENT et la relation Passer, nous avons les cardinalités suivantes: * Cardinalité minimale = 1, ce qui veut dire que chaque client passe au moins une commande. * Cardinalité maximale= N, ce qui veut dire que chaque client peut passer plusieurs (N) commandes. Entre l'entité Commande et la relation Passer, nous retrouvons les cardinalités suivantes: * Cardinalité minimale = 1, donc chaque commande est passée par au moins un client. * Cardinalité maximale =1, chaque commande est passée au maximum par un seul client. M. BENJELLOUN 2006-07 Bases de données relationnelles - 42 Autres exemples COMMANDE 1,N No Cde Date Cde CONCERNER 0,N Quantité Cdée PRODUIT No Prod Désignation Prix Unitaire Une occurrence de commande est concernée au moins 1 fois. Une occurrence de commande peut être concernée plusieurs (N) fois. Une occurrence de produit peut ne pas être concernée (0). Une occurrence de produit peut être concernée plusieurs (N) fois. Employé Utiliser 0,N Ordinateur 1,N Numéro Nom Prénom Adresse Code_postal Numéro_PC Type Configuration Entre l'entité Employé et la relation Utiliser, nous avons: Cardinalité minimale = 0 Certains employés n'utilisent pas d'ordinateur Cardinalité maximale = N Service Entre l'entité Ordinateur et la relation Utiliser, nous avons: Cardinalité minimale = 1 Cardinalité maximale =N M. BENJELLOUN 2006-07 Bases de données relationnelles - 43 1,1 : Un COUREUR Provient au min d’1 PAYS et au max d’1 PAYS. Un COUREUR Provient d’un et d’un seul PAYS. un plusieurs plusieurs 0,n : Un PAYS est représenté au min par 0 COUREUR et au max n. Un PAYS est représenté par aucun ou plusieurs COUREURS. plusieurs M. BENJELLOUN 2006-07 Bases de données relationnelles - 44 CONFIGURATIONS POSSIBLES : 0,1 1,1 0,N 1,N Une occurrence participe au moins 0 fois et au plus 1 fois à l’association Une occurrence participe exactement 1 fois à l’association Une occurrence peut ne pas participer ou participer plusieurs fois Une occurrence participe au moins 1 fois, voire plusieurs Le fait d'indiquer pour cardinalité minimale '1' implique une contrainte forte : elle signifie qu'une entité ne peut exister indépendamment d'une autre. De telles entités sont dites ''faibles'' . ! Insistons sur le point suivant : les cardinalités n'expriment pas une vérité absolue, mais des choix de conception. Ils ne peuvent être déclarés valides que relativement à un besoin. Plus ce besoin sera exprimé précisément, et plus il sera possible d'apprécier la qualité du modèle. M. BENJELLOUN 2006-07 Bases de données relationnelles - 45 Les cardinalités, du point de vue de l'association, dans une interprétation ensembliste RELATIONS OBLIGATOIRES Notation E-A 1,1 <-> 1,1 1,N <-> 1,N Explication Relation ensembliste TOUTE occurrence de A a un homologue UNIQUE parmi les occurrences de B et réciproquement ???? TOUTE occurrence de A a AU MOINS un homologue parmi les occurrences de B et réciproquement RELATIONS Optionnelles Notation E-A Explication 0,N <-> 0,1 UNE occurrence de A peut avoir 0,1,N vis-à-vis. UNE occurrence de B est limitée à 0 ou 1 homologue 1,N <-> 0,N TOUTE occurrence de A a AU MOINS un homologue. Mais UNE occurrence de B peut ne pas en avoir, en avoir 1 ou plusieurs. M. BENJELLOUN 2006-07 Relation ensembliste Bases de données relationnelles - 46 Exercice 0: Cardinalités? CLIENT APPARTIENT 0-N No Nom Adresse SIGNE 0-N 1-1 CONTRAT 1-1 No Type Date VEHICULE No Marque Modèle Année Cylindrée 1-N 0-N M. BENJELLOUN 2006-07 CONCERNE Bases de données relationnelles - 47 ACCIDENT No Date (Montant) Exercice 1: CLIENT Laquelle des deux modélisations est correcte ? Passer 1,N Numéro Nom Prénom Adresse Code_postal Localité 1,1 Commande Numéro Date Quantité CLIENT Passer 0,N Numéro Nom Prénom Adresse Code_postal Localité 1,1 Commande Numéro Date Quantité Une commande est toujours passée par au moins un client. Une commande est également passée au maximum par un client. Une commande est donc toujours passée par un et un seul client. Un client passe au moins une commande et au maximum plusieurs (N) commandes. Cette modélisation ne tient pas compte des clients qui ne passent aucune commande. Un client est uniquement considéré comme tel s'il passe au moins une commande Un client peut passer aucune commande et au maximum plusieurs (N) commandes. Cette modélisation tient compte des clients qui ne passent aucune commande. M. BENJELLOUN 2006-07 Bases de données relationnelles - 48 Exemple CLIENT Disposer 0,N Numéro Nom Prénom Adresse Code_postal Localité Carte_Membre 1,1 No_Carte Type_Abonnement Date_création On dit que CLIENT est l'entité indépendante par rapport à l'association Disposer, tandis que Carte_Membre est l'entité dépendante. Une occurrence d'un client peut très bien exister sans carte de membre, mais une carte de membre ne peut pas exister sans client. ! La cardinalité minimale indique donc quelle entité est indépendante(0) et quelle entité est dépendante(1). On dit qu'une entité est indépendante par rapport à une relation lorsque sa cardinalité minimale vaut 0. ! Une relation ne peut pas être liée uniquement à des entités dépendantes qui ont une cardinalité maximale de 1. M. BENJELLOUN 2006-07 Bases de données relationnelles - 49 La modélisation suivante par exemple n'est pas correcte !!! Carte_Membre CLIENT Numéro Nom Prénom Adresse Code_postal Localité Disposer 1,1 1,1 No_Carte Type_Abonnement Date_création ! Dans ce cas, il faut réunir les propriétés des deux entités dans une seule. M. BENJELLOUN 2006-07 Bases de données relationnelles - 50 Exercice 2 Voici le résultat simplifié d'une analyse faite auprès d'une compagnie d'assurance qui désire informatiser la gestion des contrats auto. Un client peut assurer plusieurs voitures auprès de la compagnie. Chaque voiture est assurée par un seul contrat. Un contrat assure une seule voiture. En ce qui concerne un client, la compagnie désire connaître son nom, prénom, adresse complète, numéro de téléphone ainsi qu'un numéro de compte bancaire avec indication de la banque. Chaque contrat contient un numéro de contrat unique, la prime annuelle à payer, la date de paiement annuel, la marque de la voiture, le modèle de la voiture, le numéro d'immatriculation de la voiture, la valeur de la voiture et la date d'acquisition de la voiture. M. BENJELLOUN 2006-07 Bases de données relationnelles - 51 Visites dans un centre médical Medicament Medecin Patient Marticul Nom Code Libelle No_SS Nom Mutuelle 0,N 0,N 1,N Donner Prescrit Nb Prises Assister 1,1 0,N Consultation 1,1 No_Cons Date 1. 2. 3. 4. Un patient peut-il effectuer plusieurs visites? Un médecin peut-il recevoir plusieurs patients dans la même consultation? Peut-on prescrire plusieurs médicaments dans une même consultation? Deux médecins différents peuvents-ils prescrire le même médicament. M. BENJELLOUN 2006-07 Bases de données relationnelles - 52 Oui Non Oui Oui Exercice 3 " LabDB " CLIENT Obtenir 1,N Facture 1,1 No_Client Nom PARTIE 1 Prénom LabDB SPRL Adresse La société "LabDB" Code_postal 5, avenue SGBD 7000 FPMs désire informatiser son Localité système de facturation. Les factures devraient se présenter de la façon suivante: No_Facture Date Montant Facture No. 0001 Mons, le 15.02.2003 Client Nom : Prénom : Adresse : Code_postal : Localité : Nom_Client Pre_Client Serv. Info, 15 7000 Mons Montant de la facture : 3400 € 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 ou il reçoit sa première facture. Une facture concerne un et un seul client. M. BENJELLOUN 2006-07 Bases de données relationnelles - 53 PARTIE 2 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. la facture devrait avoir: Voici l'aspect que la facture devrait avoir: Proposez un nouveau MCD qui reflète ces modifications, en respectant le fait que tous les articles disponibles sont stockés (p.ex. No=233 Libellé="Analyse" PU=1000 €). Même si un article n'est pas encore considéré par une facture, il existe dans le système d'information. LabDB SPRL 5, avenue SGBD 7000 FPMs Client Nom : Prénom : Adresse : Code_postal : Localité : No. Article 233 Libellé Facture No. 0002 Mons, le 15.02.2003 Nom_Client Pre_Client Serv. Info, 15 7000 Mons Quantité Prix Analyse Prix unitaire 1000 € 1 1000 € 025 MCD 700 € 2 1400 € 142 MLD 1000 € 1 1000 € Montant total de la facture : M. BENJELLOUN 2006-07 Bases de données relationnelles - 54 3400 € Solution de l’exercice " LabDB " Obtenir CLIENT PARTIE 1 1,N Remarque: No_Client en + ⊵ propriété artificielle définit comme identifiant. Sinon il faut définir un identifiant composé de +ieur propriétés. No_Client Nom Prénom Adresse Code_postal Localité No_Facture Date Montant Obtenir CLIENT 1,N PARTIE 2 Remarque: 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. M. BENJELLOUN 2006-07 Facture 1,1 No_Client Nom Prénom Adresse Code_postal Localité Facture 1,1 No_Facture Date 1,N Porter Quantité 0,N Article ! Bases de données relationnelles - 55 No_Article Libellé Prix_Unitaire Exercice 4 : Structure administrative On considère un sous-ensemble d’une structure administrative. D’une direction (caractérisée par un nom unique et le nom de son PDG) dépendent plusieurs départements (dotés chacun d’un nom unique dans sa direction et du nom de son directeur). Un département est découpé en services, ayant un nom (unique dans son département) et un responsable. Un service a la charge d’un certain nombres de dossiers (identifiés par un numéro et dotés d’un titre, d’une date et d’une description). Dans chaque service travaillent des employés identifiés par un numéro et caractérisés par leur nom et leur adresse. M. BENJELLOUN 2006-07 Bases de données relationnelles - 56 Structure administrative DIRECTION dir-dép 0-N 1-1 DEPARTEMENT dép-serv 0-N Nom_Dep Directeur 1-1 SERVICE traite 0-N Nom_Serv Responsable 0-N travaille 1-1 1-1 DOSSIER No_Dos Titre Date Description M. BENJELLOUN 2006-07 PERSONNE No_Pers Nom Adresse Bases de données relationnelles - 57 Nom PDG Peut – il devenir comme cela ? Entité traite 0-N Nom Responsable travaille 0-N 1-1 1-1 DOSSIER No_Dos Titre Date Description M. BENJELLOUN 2006-07 PERSONNE No_Pers Nom Adresse Bases de données relationnelles - 58 Notion de dépendance fonctionnelle Une dépendance fonctionnelle (df) existe lorsqu'un ensemble d'attributs détermine parfaitement un autre ensemble d'attributs. Pour une table T(relation R), un attribut Y de T dépend fonctionnellement d ’un attribut X de T ssi chaque valeur de X est associée a une et une seule valeur de Y XY Ex: {Nom, Tel.} {Nom, Prénom} définit {ID} {Adresse,Sexe,Téléphone,Email} //Vrai s’il n’y a pas de problème d’homonymie Les formes normales 2NF, 3NF et BCNF sont basées sur des contraintes en relation avec la notion de dépendance fonctionnelle. M. BENJELLOUN 2006-07 Bases de données relationnelles - 59 Notion de dépendance fonctionnelle Considérons l’entité suivante et quelques une de ses occurrences : Cette entité est juste mais elle implique une redondance d’information relative à la catégorie. L’association entre le numéro de la catégorie et son libellé est en effet répétée dans chaque occurrence de l’entité ARTICLE. M. BENJELLOUN 2006-07 Bases de données relationnelles - 60 Normalisation Le processus de normalisation du modèle relationnel à pour objectif d’établir une meilleure représentation conceptuelle des données d’une application par des tables relationnelles. Cela consiste, essentiellement, à décomposer les tables (entités, relations) contenant trop d’informations en tables (E/R) plus petites. Un mauvais schéma relationnel pouvant entraîner des anomalies lors des manipulations. Définition: Le processus de Normalisation permet, par étapes, d'aboutir à des relations ayant les bonnes propriétés. On peut mesurer la qualité d’une relation par son degré de normalisation. Ainsi, au plus une relation appartient à une forme normale avancée, au plus sa qualité augmente. M. BENJELLOUN 2006-07 1 FN (Codd, 1971) 2 FN (Codd, 1971) 3 FN (Codd, 1971) BCFN (Boyce, Codd, 1971) 4 FN (Fagin, 1977) 5 FN (Fagin, 1979) FN=Forme Normale. Bases de données relationnelles - 61 Normalisation NORMALISATION Programmation plus facile des applications Relations plus simples à gérer Normaliser un schéma relationnel c'est le remplacer par un schéma équivalent où toutes les relations vérifient certaines propriétés. Ces propriétes sont basées sur l'analyse des dépendances fonctionnelles à l intérieur de chaque relation. La normalisation permet de: - éviter les redondances (perte de place et incohérences) - minimiser l’espace de stockage - éviter les problèmes de mises à jour. M. BENJELLOUN 2006-07 Bases de données relationnelles - 62 Normalisation 1FN Définition: Une relation est en Première Forme Normale (1FN) si et seulement si elle ne contient que des valeurs simples et élémentaires (si tout attribut est atomique= non décomposable ). Personnes Nom Adresse ID_Personne Picsou 9, rue de Houdain, 7000 Mons … Adresse Nom N° Rue CP Ville Picsou 9 Houdain 7000 Mons Non 1FN PERE ENFANT P1 Enf11 PERE ENFANT P1 Enf12 P1 { Enf11, Enf12, Enf13 } P1 Enf13 P2 { Enf21, Enf22, Enf23 } P2 Enf21 P2 Enf22 M. BENJELLOUN 2006-07 Bases de données relationnelles - 63 Non 1FN En 1FN En 1FN Normalisation Exercice: Normaliser la relation COMMANDE COMMANDE PRODUITS Cd1 { Bureau 50€, Chaise 49€ , Ecran 199€ } Cd2 { Ecran 199€, Souris 19€ } Solution: COMMANDE Cd1 PRODUIT Bureau PRIX en € 50 Cd1 Cd1 Cd2 Cd2 Chaise Ecran Ecran Souris 49 199 199 19 M. BENJELLOUN 2006-07 Bases de données relationnelles - 64 2FN et 3FN Normalisation Une relation est en 2FN si elle est en 1FN et si tout attribut n’appartenant pas à la clé dépend totalement et non-partiellement de la clé. On dit alors que chaque attribut est en dépendance irréductible avec la clé. C’est la phase d’identification des clés. Cette étape est très importante vu qu’elle évite de nombreuses redondances. ( 2FN: 1FN + si tous les attributs qui ne participent à aucune clé pour l'entité, sont des attributs d'entités et non pas d'autres entités.) (2FN: 1FN + toute colonne qui n'appartient pas à une clé dépend pleinement de la clé et ne peut se déduire d'un sous-ensemble de cette clé. ) Une relation est en 3FN si elle est en 2FN et si tous les attributs qui n’appartiennent pas à la clé primaire sont mutuellement indépendants. Ceci correspond à la non transitivité des dépendances fonctionnelles et permet d’éviter les redondances. La forme 3FN implique que chaque attribut peut être mis à jour indépendamment des autres. 3FN si: Elle est en 2FN, Il n’existe aucune DF entre deux attributs non clé primaire (tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut non-clé.) M. BENJELLOUN 2006-07 Bases de données relationnelles - 65 Normalisation 2e ET 3e FORME NORMALE Exemple : Soit la relation concernant des dons de bienfaiteurs pour une association. Nom redondance Ville Rue Montant Date Picsou Picsou Picsou Disney Pas de sous Disney Pas de sous Disney Pas de sous 10 € 12 € 30 € 3/10/99 13/1/01 23/7/03 Donald Disney 500€ 15/6/05 Bcp de sous Supposer Picsou change d'adresse (VILLE = Mons, RUE = Houdain). Risque de ne pas corriger toutes les lignes concernées. D'où BD incohérente M. BENJELLOUN 2006-07 • Difficulté maintenance intégrité. Bases de données relationnelles - 66 Normalisation Problème dit «Anomalies de Mise à Jour»: Cause: Les Redondances d'informations sont sources d'Incohérences Solution: On aurait aimé la structure suivante: PERSONNE Nom Picsou Ville Disney Rue Pas de sous Donald Disney Bcp de sous • L'adresse de Picsou figure une seule fois. • On a séparé des informations distinctes (sur la personne, sur les dons). DONS Nom Montant Date Picsou 10 € 3/10/99 Picsou 12 € 13/1/01 Picsou 30 € 23/7/03 Donal d 500€ «Le Bon Attribut au Bon Endroit» M. BENJELLOUN 2006-07 Bases de données relationnelles - 67 15/6/05 Normalisation Normalisation du schéma - élimination des redondances internes 1 fait 1 donnée 2FN EMPLOYE NumEmp Nom DEPARTEMENT Localisation DEPARTEMENT IdDepart Localisation 0-N dépend Localisation ne dépend que de DEPARTEMENT 1-1 EMPLOYE NumEmp Nom M. BENJELLOUN 2006-07 Bases de données relationnelles - 68 Normalisation Autre Exemple : Soit la table décrivant des fournisseurs d'une société NOM_FOUR VILLE CD_POSTAL PIECE QTE_EXP F1 Mons 7000 Bureau 300 F1 Mons 7000 Ecran 500 F1 Mons 7000 Armoire 20 F2 Charleroi 6000 Bureau 500 F2 Charleroi 6000 Ecran 600 F3 Keumiée 5060 Ecran 500 Quelques anomalies: FOUR1 deux tables distinctes : fournisseur (info fournisseur) pièces fournies. • Redondances. • Difficulté de maintenance. • Mémoriser l’adresse fournisseur impossible si pas de pièce fournie. e.g. <F4, Mons, 7000> • Suppression de toutes les pièces fournies par F2, fait perdre aussi son adresse. M. BENJELLOUN 2006-07 Bases de données relationnelles - 69 Normalisation On décompose donc (PROJECTION) FOUR1 NOM_FOUR VILLE CD_POSTAL PIECE QTE_EXP F1 Mons 7000 Bureau 300 F1 Mons 7000 Ecran 500 F1 Mons 7000 Armoire 20 F2 Charleroi 6000 Bureau 500 F2 Charleroi 6000 Ecran 600 F3 Keumiée 5060 Ecran 500 EXPEDITION 2FN NOM_FOUR PIECE QTE_EXP F1 Bureau 300 F1 Ecran 500 NOM_FOUR VILLE CD_POSTAL F1 Armoire 20 F1 Mons 7000 F2 Bureau 500 F2 Charleroi 6000 F2 Ecran 600 F3 Keumiée 5060 F3 Ecran 500 F4 Mons 7000 M. BENJELLOUN 2006-07 FOUR2 Bases de données relationnelles - 70 Normalisation • Dans la relation FOUR1, des attributs non clé (e.g. VILLE), «dépendaient» d'une partie de la clé (NOM_FOUR). • Les anomalies précédentes ont ainsi été éliminées, renforçant l'intégrité de la base. (on a pu insérer F4... par exemple). En fait, les redondances ont juste été minimisées. Car la relation FOUR2 souffre encore de quelques anoma1ies. Exercice: Lesquelles? Considérer toujours le tuple supplémentaire <F4, ..., Mons, 7000> NOM_FOUR M. BENJELLOUN 2006-07 FOUR2 VILLE CD_POSTAL F1 Mons 7000 F2 Charleroi 6000 F3 Keumiée 5060 F4 Mons 7000 Bases de données relationnelles - 71 Normalisation • On décompose encore la relation FOUR2 FOUR3 COMMUNE 3FN NOM_FOUR VILLE VILLE CD_POSTAL F1 Mons Mons 7000 F2 Charleroi Charleroi 6000 F3 Keumiée Keumiée 5060 F4 Mons On dit qu'on est passé à la 3e Forme Normale •Dans la relation FOUR2, des attributs non clé (e.g. CD_POSTAL), «dépendaient» d'un autre attribut non clé ( ici VILLE) . • Il n'y a plus de redondances RÉSULTAT FINAL: FOUR3 NOM_FOUR VILLE M. BENJELLOUN 2006-07 COMMUNE VILLE CD_POSTAL EXPEDITION NOM_FOUR Bases de données relationnelles - 72 PIECE QTE_EXP Normalisation Exces : Deuxième forme normale (2FN)!!! Une relation est en 2FN si: Elle est en 1FN, Tout attribut, non clé primaire, est dépendant de la clé primaire. Exemple de relation en 1FN mais pas en 2FN: Projet NumProjet NumEmployé Pourquoi? Fonction NomEmployé Problèmes - on ne peut enregistrer un employé que s'il participe à un projet - si un employé participe à plusieurs projets, on doit répéter les informations sur cet employé (redondance et problèmes de m-à-j) Une solution peut être proposée qui consiste à extraire la dépendance fonctionnelle: 1. On créé une nouvelle relation contenant l'attribut déterminé par une partie de la clé primaire 2. La clé primaire de la nouvelle relation est cette partie de la clé Employé NumEmployé NomEmployé Projet NumProjet NumEmployé NumEmployé est à la fois clé primaire et clé externe dans Projet M. BENJELLOUN 2006-07 Bases de données relationnelles - 73 Fonction Normalisation On peut aussi étudier d'autres relations comme: CLIENT N°client nom prénom date de naissance rue ville Cette relation est en 2FN par contre la suivante n'est pas en 2FN. COMMANDE_PRODUIT N°produit quantité N°fournisseur ville Pourquoi? Relation en 2FN mais pas en 3FN Employé NumEmployé M. BENJELLOUN 2006-07 NomEmployé NumService Pourquoi? Un autre exemple: COMPAGNIE Pourquoi? Vol Avion Pilote Bases de données relationnelles - 74 NomService Contraintes d’intégrité Après MLD Le modèle d'une base de données relationnelle implique, par sa conception, un certain nombre de contraintes d'intégrité (C. I. ) qui traduisent les propriétés sémantiques des données : Intégrité de domaine : concerne le contrôle des valeurs des attributs, le contrôle entre valeurs des attributs ainsi que le contrôle des opérateurs entre attributs. Contraintes d’intégrité statiques : utilisant le mot réservé CHECK Le concepteur peut également définir ses propres domaines : CREATE DOMAIN Domaine_Sexe CHAR(1) CHECK (VALUE IN (‘M’, ‘F’)) ; Intégrité de clé primaire : la contrainte de clé primaire d'une relation implique la non duplication des lignes, c'est-à-dire que chaque objet du monde réel peut être enregistré sans ambiguïté par une seule ligne, un seul "tuple"; - on peut ajouter à une colonne la contrainte de non-nullité qui implique que cette colonne ne peut pas avoir de valeur nulle, c'est-à-dire ne peut pas être indéfinie ou inutilisable; BENJELLOUN 2006-07et de non-nullité Bases de données relationnelles LesM. contraintes de domaine sont gérées en langage- 75 SQL, lors de la création de la table. C. I. Intégrité de référence : L’intégrité de référence (ou intégrité référentielle) est un ensemble de contraintes contrôlant les dépendances et indépendances des relations. Elle concerne principalement l’intégrité des clés étrangères dont les valeurs sont ‘NULL’ ou des valeurs de la clé primaire. L’objectif des clés étrangères étant de repérer un enregistrement dans différents espaces, des contraintes d’intégrité référentielle doivent assurer la validité des références entre tables. L’intégrité référentielle signifie qu’il n’y a pas de valeurs de clés étrangères invalides. Pour cela, il faut donc prendre des décisions quant à la suppression ou la modification de la cible que référence la clé étrangère. FOREIGN KEY Nom_Clé REFERENCES Nom_Table [ON DELETE option] [ON UPDATE option] M. BENJELLOUN 2006-07 Bases de données relationnelles - 76 Intégrité de relationnelle : C. I. L’intégrité relationnelle contrôle la sémantique et gère les contraintes de type if…then… Voici l’exemple d’une règle d’intégrité qui assure la suppression du numéro de téléphone d’une personne décédée. CREATE INTEGRITY RULE Règle_Décès FORALL Personnes (IF Personnes.Décédé = True THEN Personnes.Téléphone = NULL) ON ATTEMPTED VIOLATION Reject ; M. BENJELLOUN 2006-07 Bases de données relationnelles - 77 Contraintes d’intégrité fonctionnelle Les contraintes d’intégrité fonctionnelles (CIF) contrôlent la détermination absolue d’une entité participant à une association du modèle conceptuel par une ou plusieurs autres participant à la même association. Ce type de contrainte permet donc d’identifier les dépendances entre entités. Les CIFs ne doivent pas être confondues avec les dépendances fonctionnelles, qui permettent de déterminer les dépendances qui existent entre deux groupes d’attributs au sein d’une même relation L’identification des CIFs permet de corriger certaines erreurs de conception de bases de données relationnelles. En effet, si la connaissance d’une ou plusieurs entités d’une association conduit à la détermination complète d’une autre entité participant à la même association, on peut considérer qu’il y a redondance d’information dans le modèle. M. BENJELLOUN 2006-07 Bases de données relationnelles - 78 CIF Personnes 0 N Obtenir Année_d_obtention 0 N ID_Personne Nom Prénom Sexe Adresse Téléphone Email Diplômes Code_diplôme Titre_diplôme Abréviation 0N CIF Universités Code_université Nom_université Abréviation Si on imagine re-normaliser les diplômes de telle manière que chaque université possède l’exclusivité du diplôme qu’elle délivre, dans ce cas, la connaissance du diplôme implique celle de l’université. Il y a donc une contrainte d’intégrité fonctionnelle entre l’entité Diplômes et l’entité Universités. Personnes 0 N Obtenir Année_d_obtention M. BENJELLOUN 2006-07 0 N Diplômes 0 1 Délivrer Bases de données relationnelles - 79 0 N Universités CIF Trouver un MCD équivalent à ce MCD : 1,1 Camion 0,N No_Camion Activité Conduire 0,N Mois Mois Cumul Nb H d’activité Chauffeur 1,N Matri_Chauf. 0,N Camion 1,1 0,N No_Camion Activité Conduire 0,N Cumul Nb H d’activité Mois Mois Chauffeur 1,N Matri_Chauf. Un camion n’ayant qu’un chauffeur, l’activité du chauffeur par camion est l’activité du camion M. BENJELLOUN 2006-07 Bases de données relationnelles - 80 CIF Principe de simplification par les contraintes d’intégrité fonctionnelles Relation binaire non porteuse de données avec 1,1 sur l’une des pattes 1,1 Entité 1 4 1 ! CIF Relation Entité 2 2 3 On supprime la patte n°3. CONTRAINTE D'INTEGRITE FONCTIONNELLE Une Contrainte d'Intégrité Fonctionnelle (en abrégé : CIF) se définit par le fait que l'une des entités participant à l'association est complètement déterminée par la connaissance d'une ou plusieurs autres entités participant dans cette même association. M. BENJELLOUN 2006-07 Bases de données relationnelles - 81 CIF 1,1 Camion 1,N 0,N Type de Client No_Camion 0,N Type Activité Cumul Nb H d’activité Chauffeur 0,N Matri_Chauf. Client 1,N 1,1 1,1 Camion 1,N 1,1 0,N Affecter 1,N N°Client 0,N Type de Client No_Camion 0,N Type Activité Cumul Nb H d’activité Chauffeur 0,N Matri_Chauf. 1,1 M. BENJELLOUN 2006-07 Client 1,N 1,N Affecter N°Client 0,N Bases de données relationnelles - 82 1,1 Les associations transitives Considérons le modèle suivant : L’association binaire qui relie l’entité « CONTRAT » et l’entité « PROPRIETAIRE» doit être ôtée du modèle car on peut retrouver le propriétaire à partir des associations « Concerner » et « Appartenir ». Il s’agit d’une association transitive. M. BENJELLOUN 2006-07 Bases de données relationnelles - 83 CIF Gestion FPMS_Etudiants La FPMs veut ré-informatiser son système d'information qui gère les étudiants, Groupes d’étudiants (classes)(1er_A,... , 3eme_IG,…) . Sachant que : Un étudiant est caractérisé par [no. matricule, nom, prénom, date de naissance, adresse, Email ]. Une classe est caractérisée par le nom de la classe (p.ex 1er) et par une indication du groupe ou spécialité (P.ex : 1er_A, 4eme_Elec) ainsi que par le nombre d’étudiants qui la fréquente. Un étudiant enregistré dans le système fréquente au moins une classe au cours des années. On désire également saisir tous les enseignants dans le système d'information. Un enseignant est caractérisé par un code interne (CodeProf) , nom, prénom, Email et la matière qu'il enseigne. Une matière (cours) peut être composée de cours, T.P. et Exercices. Chacun des modules présente un poids en pourcentage de la côte globale à l’examen. Un module peut être donné par un ou plusieurs enseignants. Une matière est représentée au moins par un code matière (p.ex. INF_B = Informatique de base, BD = Base de données, etc.) et un libellé complet qui exprime le terme général ( p.ex "Informatique" etc.). M. BENJELLOUN 2006-07 Bases de données relationnelles - 84 Modélisez le fait que chaque classe soit enseignée chaque année par un ou plusieurs enseignants. Un enseignant peut bien sûr donner des cours (cours, Labo., Exercices) à plusieurs classes, enseigner plusieurs matières différentes pendant une ou plusieurs années, mais peut également ne pas donner de cours pendant une ou plusieurs années. Exprimez aussi le fait que l’étudiant puisse suivre des cours en deux années différentes. Exprimez la contrainte suivante : connaissant le code postal de l’étudiant, nous pouvons choisir parmi les localités, celle qui correspond à l’adresse de l’étudiant, ou connaissant la localité le code postal est capté automatiquement. M. BENJELLOUN 2006-07 Bases de données relationnelles - 85 Le Modèle Logique des Données (MLD) Définition Analyse Le niveau logique, qui se base sur le modèle conceptuel des données,MCD introduit la notion des tables logiques, et constitue donc le premier pas vers les MLD tables des SGBD. Passage du MCD au MLD Réel Perçu Schéma Conceptuel Schéma Logique Le passage du schéma conceptuel à la structure relationnelle (MLD) se fait facilement et obéit à certaines règles. Le MLD est toujours basé sur MCD. Un MLD est essentiellement composé de tables logiques reliées entre elles par des flèches… M. BENJELLOUN 2006-07 Bases de données relationnelles - 86 M. BENJELLOUN 2006-07 Bases de données relationnelles - 87 modèle conceptuel modèle logique Entité Toute entité est transformée en table. Les propriétés de l'entité deviennent les attributs de la table. L'identifiant de l'entité devient la clé primaire de la table. EMPLOYE Matricule Nom Fonction M. BENJELLOUN 2006-07 Identifant Clé primaire EMPLOYE (Mat, Nom, Fonc) Propriété Attribut Bases de données relationnelles - 88 Association ou relation Notion Père_fils ou mère_fille Les relations du modèle conceptuel peuvent, sous certaines conditions, soit disparaître, soit devenir une table. M. BENJELLOUN 2006-07 Bases de données relationnelles - 89 Relation un-à-plusieurs (participation totale) (1,1) (1,N) : (1,1) (0,N) Porter dans la relation fille la clé primaire de la relation mère. L'attribut ainsi ajouté s'appelle clé étrangère. Par convention, nous la symboliserons au moyen de #. Une clé étrangère est une colonne constituée de l’identifiant d’une autre table (et joue le rôle de référence à une ligne de cette table) (contrainte référentielle) M. BENJELLOUN 2006-07 Bases de données relationnelles - 90 (1,1) - (0,N) : se fait comme une relation un-à-plusieurs normale. DEPARTEMENT Nom Adresse DEPARTEMENT Nom Adresse 0-N Occupe 1-1 EMPLOYE Matricule Nom Fonction M. BENJELLOUN 2006-07 EMPLOYE Matricule Nom Fonction Bases de données relationnelles - 91 NomDpt Relation plusieurs-à-plusieurs (1,N) - (1,N) : (0,N) - (0,N) : (0,N) - (1,N) 1,N CLIENT COMMANDER 1,N Référence Désignation Prix Unitaire Quantité Cde Client Nom Adresse CLIENT(CdCli, Nom, Adresse) PRODUIT PRODUIT(Ref, Des, Prix) COMMANDER(#CdCli, #Ref, Quant) Clé primaire M. BENJELLOUN 2006-07 Clés étrangères Bases de données relationnelles - 92 0,N : La même règle s'applique CLIENT 0,N COMMANDER 0,N Référence Désignation Prix Unitaire Quantité Cde Client Nom Adresse CLIENT CdCli Nom PRODUIT Adresse Ref COMM CdCli M. BENJELLOUN 2006-07 PRODUIT Ref Qu Bases de données relationnelles - 93 Des Prix Relation un-à-un (1,1)—(1,1) : L'une des entités devient un attribut de l'autre entité. Exemple : ! Client(No_Client, Nom, Adresse) Carte_Membre(No_Carte,Type_Abonnement) Deviennent: Client(No_Client, Nom, Adresse, #No_Carte,Type_Abonnement). M. BENJELLOUN 2006-07 Bases de données relationnelles - 94 Disposer CLIENT 0,1 Numéro Nom Prénom Adresse Code_postal Localité Carte_membre 1,1 No_Carte Type_Abonnement Date_création CLIENT Carte_membre NoClient Nom Prénom Adresse Cde_postal Localité Disposer No_Carte NoClient Type_Abonnement Date_création Relation (0,1) – (0,1) : Idem que précédemment avec choix. Si l’association contient elle même des propriétés, alors >>> ajouter avec la clé étrangère. M. BENJELLOUN 2006-07 Bases de données relationnelles - 95 DIMENSION SUPERIEURE A 2: Si la relation entre chacune des paires d'entités ne peut déterminer la troisième entité. Une table pour chacune des entités et une table pour la relation. Cette dernière possédera une clé primaire composée d'au moins trois champs. Médecin Nom-médecin Adresse 0-N Pratiquer 0-N Lieu Acte Code-acte Désignation 1-N Patient N°Sec.Soc Nom Medecin(Nom-Med, Adr) Acte(Cd-Acte, Des) Patient(NSS, Nom-Pat) Pratiquer(#Nom-Med, #Cd-Acte, #NSS, Lieu) M. BENJELLOUN 2006-07 Bases de données relationnelles - 96 Résumé : Relation binaire : A (1,1) – (1,N) et (1,1) - (0,N) : (1,1) - (0,N) A ( id_A, a1, a2, …, #id_B) B ( id_B, b1,b2,…) (1,N) - (1,N) , (0,N) - (1,N) : (0,N) - (0,N) A ( id_A, a1, a2, …,) B ( id_B, b1,b2,…) A-B (#id_A, #id_B, a-b,…) (1,1) - (1,1) : A ( id_A, a1, a2, …, id_B, b1,b2,…) (0,1)- (1,1) : A ( id_A, a1, a2, …) B ( id_B, b1,b2,…, #id_A) (0,1) – (0,1) : Idem que (0,1)- (1,1) avec choix du placement de la clé étrangère. M. BENJELLOUN 2006-07 A-B Bases de données relationnelles - 97 B Relation ternaire -N A -N A-B-C B -N C A ( id_A, a1, a2, …,) B ( id_B, b1,b2,…) C ( id_B, b1,b2,…) A-B-C (#id_A, #id_B, #id_C, a-b-c,…) M. BENJELLOUN 2006-07 Bases de données relationnelles - 98 EXCE : M. BENJELLOUN 2006-07 Bases de données relationnelles - 99 M. BENJELLOUN 2006-07 Bases de données relationnelles - 100 Exercice "LabDB" Transformez le MCD suivant, qui représente la facturation de la société "LabDB" en un MLD en respectant toutes les règles du passage MCD à MLD. Obtenir CLIENT 1,N No_Client Nom Prénom Adresse Code_postal Localité Facture CLIENT No_Facture Date No_Client Nom Prénom Adresse Code_postal Localité 1,1 1,N Porter Obtenir Facture N°_Facture N°Client Date Quantité 0,N Porter Article No_Article Libellé Prix_Unitaire N° Facture No_Article Quantité Article No_Article Libellé Prix_Unitaire M. BENJELLOUN 2006-07 Bases de données relationnelles - 101 Exercice_Obélix La société Obélix et Compagnie fournit des menhirs dans le monde entier et gère les commandes à l’aide d’un micro-ordinateur. Exemple d’une commande: Obélix et Compagnie Livreur de menhirs Village gaulois Donner : Le MCD Les cardinalités et leur signification. Le Modèle des données. M. BENJELLOUN 2006-07 Nº client Date commande: 22.02.2003 Nº commande: 012 Nom client: BISCORNUS Prénom: M. Adresse: BABAORUM Code Libellé Quantité Prix unitaire 12 MENHIR CLASSIC 2 500 21 MENHIR SE/30 1 1500 25 MENHIR II FX 1 2500 Bases de données relationnelles - 102 4711 Rep_Obélix Schéma Entité-Association 0,N CLIENT 1,1 CLI_COM COMMANDE 1,N Signification: - Une commande est passée par un (1) client. - Un client peut passer plusieurs (N) commandes. - Une commande peut concerner plusieurs (N) produits. - Un produit peut intervenir dans plusieurs (N) commandes. L’association CLI_COM est du type (1,N). L’association COM_PRO est du type (N,N). Modèle des données CLIENT(NUM_CLI, NOM_CLI, PRE_CLI, ADR_CLI) COMMANDE(NUM_COM, DAT_COM, #NUM_CLI) PRODUIT(NUM_PRO, LIB_PRO, PRI_UNI) COM_PRO(#NUM_COM, #NUM_PRO,QTE_COM) M. BENJELLOUN 2006-07 Bases de données relationnelles - 103 COM_PRO 0,N PRODUIT Modèle relationnel textuel : Client(NoClient, Nom, Prénom) Commande (NoCde, DateCde, NoClient#) Produit(RefPdt, Désignation, Prix) Ligne(NoCde#, RefPdt#, Qté) Client NoClient Nom Commande Prénom NoCde DateCde NoClient# 1 Lassus Annick 100 14/04/2001 2 2 Mundubeltz Armelle 101 14/04/2001 1 3 Chaulet Bernadette Produit RefPdt Désignation Ligne Prixen € NoCde# RefPdt# Qté VE45 Vélo 150 100 VE45 1 VE32 Kit 2 roues 30 100 VE32 1 Bases relationnelles - 104VE21 15de données 101 2 M. BENJELLOUN 2006-07 VE21 Kit éclairage CLIENT NumClient 001 002 003 Nom Albert Francois Brabo PRODUIT Ville Bruxelles Liège Anvers NumPiece 0001 0002 0003 Descr. Table Chaise Armoire Cout 500 300 1.000 COMMANDE NumClient NumPiece 001 0002 002 0002 002 0003 Quantite 3 1 5 des relations existent entre les tableaux d ’une BD, ici: Albert de Bruxelles a commandé 3 chaises à 300 EUR M. BENJELLOUN 2006-07 Bases de données relationnelles - 105 CLIENT APPARTIENT 0-N NumCli Nom Adresse SIGNE 0-N 1-1 1-1 CONTRAT VEHICULE 0-1 NumCtr Type Date 0-N ACCIDENT (1-N) NumAcc Date (Montant) NumVeh Marque Modèle Année Cylindrée 1-1 COUVERT 0-N M. BENJELLOUN 2006-07 CONCERNE Bases de données relationnelles - 106 CLIENT NumCli Nom Adresse CONTRAT VEHICULE NumVeh ... NumCli NumCtr Numcli NumCtr Type Date ACCIDENT NumAcc CONCERNE NumVeh M. BENJELLOUN 2006-07 NumAcc Bases de données relationnelles - 107 Date (Montant) Exemple - SQL create table VEHICULE ( NumVeh char (16) not null, not null, Marque char (30) not null, not null, Modele char (30) not null, not null, Annee decimal (4) not null, ) Cylindree decimal (6) not null, NumCli char (12) not null, create table CONTRAT ( Ncli char (12) not null, NumCli char (12) not null, NumCtr decimal (8) not null, NumCtr char (8) not null, primary key (NumVeh), Type decimal (4) not null, unique (Numcli, NumCtr), Date date not null Foreign key primary key (NumCtr) (Numcli) references CLIENT, Foreign key (NumCli) references CLIENT ) Foreign key (Numcli, NumCtr) references CONTRAT ) create table CLIENT ( NumCli char (12) Nom char (38) Adresse char (60) primary key (NumCli) M. BENJELLOUN 2006-07 Bases de données relationnelles - 108 create table ACCIDENT ( NumAcc char (10) Date date Montant decimal (6), primary key (NumAcc)) not null, not null, not null, create table CONCERNE ( NumVeh char (16) not null, NumAcc char (10) not null, primary key (NumVeh, NumAcc), foreign key (NumVeh) references VEHICULE, foreign key (NumAcc) references ACCIDENT) M. BENJELLOUN 2006-07 Bases de données relationnelles - 109 Access M. BENJELLOUN 2006-07 Bases de données relationnelles - 110 M. BENJELLOUN 2006-07 Bases de données relationnelles - 111 SQL S.Q.L. « Structured Query Language », "langage structuré de requête" est un langage pour interroger et gérer les bases de données relationnelles. Il permet de créer, modifier et sélectionner des données. Le SQL peut se diviser en trois parties: DDL (data definition language), sert à définir la structure: créer, modifier, effacer... DML (data manipulation language), sert à manipuler les données: choisir, ajouter, effacer des tuples. DCL (data control language), sert à contrôler l'accès à l'information. M. BENJELLOUN 2006-07 Bases de données relationnelles - 112 On retrouve dans le DDL les commandes principales suivantes: CREATE TABLE CREATE INDEX CREATE VIEW ALTER TABLE DROP TABLE Création d'une table Création d'un index Création d’une vue Modification de la structure Effacement d'une table On retrouve dans le DML les commandes principales suivantes: INSERT UPDATE DELETE SELECT M. BENJELLOUN 2006-07 Insérer un tuple Modifier un tuple Effacer un tuple Choisir un ensemble de tuples Bases de données relationnelles - 113 Il existe de plus des fonctions: De tri (ORDER BY) et de regroupement (GROUP BY) arithmétiques, mathématiques et statistiques (moyenne, maximum, minimum, etc.) logiques (UNION, INTERSECTION, etc.) etc. Les données sont définies selon des types (entier,caractères, date,etc.). Types de données SMALLINT INTEGER DECIMAL (m,n) FLOAT CHAR (n) VARCHAR DATE TIME M. BENJELLOUN 2006-07 entier (16 bits) entier (32 bits) décimaux de m chiffres (dont n après la virgule) réels flottants chaîne de n caractères chaîne variable d’au plus n caractères dates (année, mois et jour) instants (heure, minute, seconde) Bases de données relationnelles - 114 On peut aussi inclure dans la définition des attributs des mots de contrôle pour forcer la saisie. Par exemple: NOM CHAR(20) NOT NULL définit une variable "NOM" qui sera constituée d'un champs de 20 caractères auquel on devra obligatoirement attribuer une valeur si on désire ajouter un tuple contenant cet attribut. M. BENJELLOUN 2006-07 Bases de données relationnelles - 115 Exercice "LabDB" Obtenir CLIENT No_Client Nom Adresse 1,N Facture 1,1 Obtenir CLIENT 1,N No_Facture Date 1,N Porter No_Client Nom Boite_Post Rue Code_postal Localité Facture 1,1 No_Facture Date 1,N Porter Quantité 0,N M. BENJELLOUN 2006-07 Quantité 0,N Article Article No_Article Libellé Prix_Unitaire No_Article Libellé Prix_Unitaire Bases de données relationnelles - 116 Obtenir CLIENT 1,N No_Client Nom Boite_Post Rue Code_postal Localité Facture CLIENT No_Facture Date No_Client Nom Boite_Post Rue Code_postal Localité Obtenir 1,1 1,N Porter Facture N°_Facture #N°Client Date Quantité Porter 0,N N° Facture No_Article Quantité Article No_Article Libellé Prix_Unitaire Article No_Article Libellé Prix_Unitaire M. BENJELLOUN 2006-07 Bases de données relationnelles - 117 Aperçu des opérations les plus courantes Création d’une table CREATE TABLE Client ( CLIENT No_Client Nom Boite_Post Rue Code_postal Localité M. BENJELLOUN 2006-07 create table No_Client Nom Bte_Post Rue Code_P Localite ); CHAR (4), CHAR (12), INTEGER, CHAR(30), INTEGER, CHAR (20) Bases de données relationnelles - 118 Identifiant CREATE TABLE Client ( No_Client CHAR (4) NOT NULL, Nom CHAR (12), … Code_P primary key INTEGER, Localite CHAR (20), PRIMARY KEY (No_Client) ); M. BENJELLOUN 2006-07 not null Bases de données relationnelles - 119 CLIENT No_Client Nom Boite_Post Rue Code_postal Localité Facture CREATE TABLE Porter( N°_Facture #N°Client Date No_Facture CHAR (8) NOT NULL, No_Article CHAR (8) NOT NULL, Quantité INTEGER, Porter PRIMARY KEY (No_Facture, No_Article), N° Facture No_Article Quantité FOREIGN KEY (No_Facture) REFERENCES Facture, FOREIGN KEY (No_Article) REFERENCES Article Article No_Article Libellé Prix_Unitaire ); M. BENJELLOUN 2006-07 foreign key Clé étrangère Bases de données relationnelles - 120 Ajouter une colonne à une table ALTER TABLE Client ADD Prenom CHAR(25); Détruire une table DROP TABLE FOURNISSEUR; Créer un index CREATE INDEX PR-Cli1 ON Client (Localite); Détruire un index DROP INDEX PR-Cli1 M. BENJELLOUN 2006-07 Bases de données relationnelles - 121 CLIENT No_Client Nom Prenom Boite_Post Rue Code_postal Localité Consultation d’une BD SELECT Nom, Localite précise les valeurs (colonnes, valeurs calculées) qui constituent chaque ligne du résultat FROM Client indique les tables desquelles le résultat tire ses valeurs WHERE Localite = ‘Charleroi’ donne la condition de sélection que doivent satisfaire les lignes qui fournissent le résultat M. BENJELLOUN 2006-07 Bases de données relationnelles - 122 Manipulation des données : Sélection de tous les attributs: SELECT * FROM Client; Sélection de certains attributs: SELECT Nom, Prenom FROM Client; Sélection de certains attributs et tuples: SELECT Nom, Code_P FROM Client WHERE Code_P < 7000 AND …; Sélectionner le nom de tous les Clients qui vivent à Mons: SELECT Nom FROM Client WHERE VILLE= ‘MONS’; M. BENJELLOUN 2006-07 Bases de données relationnelles - 123 Extraction SELECT ID_Personnel, Nom, Code_Postal FROM Client M. BENJELLOUN 2006-07 Bases de données relationnelles - 124 Extraction SELECT * FROM Client M. BENJELLOUN 2006-07 Bases de données relationnelles - 125 Extraction SELECT ID_Personnel, Nom, Code_Postal FROM Client WHERE Nom = ‘Bros’ M. BENJELLOUN 2006-07 Bases de données relationnelles - 126 Le DISTINCT permet d'obtenir une liste qui ne contient qu'une fois chaque Localite. DISTINCT AVANT SELECT DISTINCT Localite FROM Client WHERE Categorie = ‘1’ APRES APRES SANS DISTINCT M. BENJELLOUN 2006-07 AVEC DISTINCT Bases de données relationnelles - 127 Conditions de sélection and or not () SELECT FROM WHERE AND Nom, Compte Client Localite = ‘Charleroi’ Compte >= 0 M. BENJELLOUN 2006-07 SELECT TableClient.Nom, TableClient.compte FROM TableClient Access WHERE (((TableClient.Localite)='Charleroi') AND ((TableClient.compte)>=0)); Bases de données relationnelles - 128 Trouver les N°_Com avec leur date de tous les Clients de Charleroi. EXCE : Facture N°_Facture #N°Client Date M. BENJELLOUN 2006-07 Bases de données relationnelles - 129 Facture Sous-requêtes SELECT FROM WHERE N°_Facture #N°Client Date No, Date AVANT Commande NoClient in (SELECT No FROM Client WHERE Localite = ‘Charleroi’) M. BENJELLOUN 2006-07 Bases de données relationnelles - 130 EXCE : Trouver les noms de tous les fournisseurs qui vendent la pièce numéro 2. Rep : SELECT NOM FROM FOURNISSEUR WHERE NOFOUR IN (SELECT NOFOUR FROM ASSOCIATION WHERE NOPCE=2); Le IN signifie inclus dans. Il existe une autre méthode : JOIN Pour comprendre les requêtes (ou query) imbriquées, il faut exécuter d'abord la requête entre parenthèses. M. BENJELLOUN 2006-07 Bases de données relationnelles - 131 Les clients qui habitent dans la même localité que le client n°2 SELECT FROM WHERE * Client Localite IN ( SELECT Localite FROM Client WHERE Client.No = ‘3’) M. BENJELLOUN 2006-07 Bases de données relationnelles - 132 Différents opérateurs = IN NOT IN CONTAIN DOES NOT CONTAIN COUNT(*) AVG(colonne) SUM(colonne) MIN(colonne) MAX(colonne) Différentes fonctions COUNT SUM AVG MIN MAX nombre de lignes trouvées moyenne des valeurs de la colonne somme des valeurs de la colonne minimum des valeurs de la colonne maximum des valeurs de la colonne On peut également se servir de mots de contrôle tels: BETWEEN, NULL, LIKE, EXISTS, IN, ALL, SOME, etc.. M. BENJELLOUN 2006-07 Bases de données relationnelles - 133 Données calculées et duplication de lignes distinct nombre de commandes ??? SELECT COUNT (NoClient) FROM Commande APRES 9 nombre de commandes !!! SELECT COUNT (DISTINCT NoClient) APRES 6 FROM Commande nombre de commandes !!! M. BENJELLOUN 2006-07 Bases de données relationnelles - 134 Mise à jour : UPDATE ASSOCIATION SET PRIX=23 WHERE NOPCE=5 AND NOFOUR="ZZ"; Dans la table ASSOCIATION pour tous les enregistrements dont le NOPCE=5 et NOFOUR=ZZ, la colonne PRIX sera mise à 23. Suppression : DELETE FOURNISSEUR WHERE NOFOUR="ZZ"; Dans la table FOURNISSEUR supprime l'enregistrement dont NOFOUR=ZZ. Il faut s'assurer que ce fournisseur n'est pas utilisé dans la table ASSOCIATION. M. BENJELLOUN 2006-07 Bases de données relationnelles - 135 Création : INSERT INTO ASSOCIATION (NOPCE,NOFOUR,PRIX) VALUES(1,"KK",10); Crée dans la table ASSOCIATION un enregistrement en assignant des valeurs aux colonnes. M. BENJELLOUN 2006-07 Bases de données relationnelles - 136
