Analyse des Bases de Données (BD&SI) - Notes de Cours PDF

# Passage au niveau logique - Le niveau logique (relationnel) est intermédiaire entre le modèle conceptuel et le modèle physique des données. - Il donne la représentation des tables telles qu’elles seront dans la base de données. - Le niveau logique n’est pas impacté par le système de bases de données utilisé. - Nous présenterons les structures ainsi : Table[cle_primaire, champ_1, ..., champ_N, cle_etrangere#] # Clé primaire et clé étrangère : Rappel - Une clé primaire est une contrainte d’unicité qui permet d’identifier de manière unique un enregistrement dans une table. Une clé primaire peut être composée d’un ou de plusieurs attributs de la table. - Une clé étrangère identifie une colonne ou un ensemble de colonnes d’une table comme référençant une colonne ou un ensemble de colonnes d’une autre table (la table référencée) - Les colonnes de la table référencée doivent faire partie d’une contrainte de clé primaire ou d’une contrainte d’unicité. - L’ensemble constitué d’une clé primaire et d’une clé étrangère sert à établir des relations entre tables. # Règle 1 (Transformation d'entités/classes) - Chaque entité devient une relation. L’identifiant de l’entité devient clé primaire pour la relation. - Chaque classe du diagramme UML devient une relation. Il faut choisir un attribut de la classe pouvant jouer le rôle d’identifiant. - Si aucun attribut ne convient en tant qu’identifiant, il faut en ajouter un de telle sorte que la relation dispose d’une clé primaire (les outils proposent l’ajout de tels attributs). | Merise | UML | |---|---| | Avion immat typeav | Avion immat typeav | | Compagnie ncomp nomcomp | Compagnie ncomp nomcomp | - **R1** Avion[immat, typeav] Compagnie[ncomp, nomcomp] # Règle 2 (Associations un-à-plusieurs) - Il faut ajouter un attribut de type clé étrangère dans la relation fils de l’association: - la relation dérivée de l’entité ayant la « multiplicité plusieurs » dans le modèle UML. - la relation dérivée de l’entité ayant la « cardinalité un » dans le modèle Merise. - L’attribut porte le nom de la clé primaire de la relation père de l’association. - **R1 et R2** | | | | |---|---|---| | Avion immat typeav 1,1 fils Possède 1 père Compagnie ncomp nomcomp | | Avion[immat, typeav, ncomp#] Compagnie[ncomp, nomcomp] | | | Avion immat typeav 0..* fils Possède O.N père Compagnie ncomp nomcomp | | # Règle 3 (Associations plusieurs-à-plusieurs et n-aires) - L’association ou classe-association devient une relation dont la clé primaire est composée par la concaténation des identifiants des entités (classes) connectés à l’association. - Ces attributs devient alors des clés étrangères. - Les attributs de la classe-association doivent être ajoutés à la nouvelle relation. Ces attributs ne sont ni clé primaire, ni clé étrangère. - **R1 et R3** | | | | |---|---|---| | Avion immat typeav O,N Affreter 1,N Compagnie ncomp nomcomp | Avion immat typeav 1..* Affreter 0..* Compagnie ncomp nomcomp | Avion[immat, typeav] Affreter[immat#,ncomp#, dateaff] Compagnie[ncomp, nomcomp] | # Exercice 4 Donnez les modèle logique des schémas suivants : ## A. | Personne | Reside | Adresse | |---|---|---| | ID Nom Prenom 0,1 | 0,N | ID Rue CP Ville | ## B. | Personne | Reside | Adresse | |---|---|---| | ID Nom Prenom 0,N | Date_emmen | ID Rue CP Ville | ## C. - Personne [ID#, Nom, Prenom] - Adresse [ID#, Rue, CP, Ville] - Reside [ID_P#, ID_A#, Date_emmen] - Reside [ID# ] ## D. - SG.BD - Personne [ID#, Nom, Prenom, ID-A#] - Adresse [ID-A#, Rue, CP, Ville] - Resida [ID-A#, ID-P#] # Associations réflexives ## Un-à-plusieurs | Pilote | ChefPilote | Pilote | ChefPilote | |---|---|---|---| | brevet nom fonction compagnie 1,N | 0,1 | brevet nom fonction compagnie 1..* | 0,1 | - **R1 et R2** Pilote [brevet, nom, fonction, compagnie, chef#] # Niveau physique - Le Modèle Physique de Données est la représentation exacte de la base de données. - Il est lié au système utilisé. - Il peut être obtenu automatiquement par des outils mais il correspond rarement au besoin. - Il peut permettre de créer automatiquement la base de données par des outils en générant le code SQL correspondant. - Par exemple en utilisant phpMyAdmin # Passage au niveau physique - Les tables sont les éléments du MPD. - Comme pour le modèle logique - Une table doit contenir une clé primaire permettant d’identifier de façon unique l’occurrence de la table. - Une table peut contenir une ou des clé étrangère permettant de la lier à une autre table. - Nous présenterons les structures soit par : Table [cle_primaire: type,champ1:type, ... champN:type, cle_etrangere#:type] - Ou | Table | | |---|---| | Cle_primaire | type | | champ1 | type | | ... | | | champN | type | | Cle_etranger# | type | # Passage au modèle physique (exemple1) ## Modèle entité-association | Personne | Reside | Adresse | |---|---|---| | ID Nom Prenom 0,1 | 0,N | ID Voie CP Ville | ## Modèle physique des données | Personne | Reside | Adresse | |---|---|---| | ID int Nom varchar(30) Prenom varchar(30) ress# int | ID_personne# int ID_adresse# int Date_emmen date | ID int Voie varchar(200) CP int Ville varchar(50) | # Passage au modèle physique (exemple2) ## Modèle entité-association | Personne | Reside | Adresse | |---|---|---| | ID Nom Prenom 0,n | Date_emmen | ID Voie CP Ville | ## Modèle physique des données | Personne | Reside | Adresse | |---|---|---| | ID int Nom varchar(30) Prenom varchar(30) | ID_personne# int ID_adresse# int Date_emmen date | ID int Voie varchar(200) CP int Ville varchar(50) | # Part 4 ## NORMALISATION # Problématique - Mélanger dans une même relation des informations relatives à plusieurs entités entraîne, en général des redondances d’information qui provoquent les anomalies suivantes : - anomalies d’insertion, - anomalies de mise à jour, - anomalies de suppression. # Exemple | isbn | titre | éditeur | pays | |---|---|---|---| | 2-212-09283-0 | Bases de données - objet et relationnel | Eyrolles | France | | 2-7117-8645-5 | Fondements des bases de données | Vuibert | USA | | 0-201-70872-8 | Databases and Transaction Processing | Addison Wesley | USA | | 2-212-09069-2 | Internet/Intranet et bases de données | Eyrolles | France | - Cette relation qui décrit des livres et leurs éditeurs, contient des redondances provoquant les anomalies suivantes : - insertion: il n’est pas possible d’insérer un livre sans connaître son éditeur. Attribuer une valeur nulle aux attributs éditeur et pays violerait l’intégrité d’entité : un attribut appartenant à la clé, ne doit pas avoir de valeur nulle. - mise à jour : si un éditeur change de pays, il faut modifier ce pays pour chacun des livres qu’il a édités. - suppression: si l’unique livre publié par un éditeur est supprimé, l’information sur cet éditeur est perdue. Attribuer une valeur nulle aux attributs isbn et titre, olerait l’intégrité d’entité. # Normalisation - La solution à ces problèmes consiste à normaliser la relation en cause. - Un schéma relationnel normalisé doit répondre aux exigences minimales suivantes : - Non redondance : un attribut n’appartient qu’à une seule relation, donc à une seule table, à moins qu’il n’agisse comme clé étrangère pour assurer l’association avec une autre table ; - Cohérence : les attributs qui décrivent le même objet appartiennent à la même table et dépendent chacun fonctionnellement et totalement de la clé primaire de la table. # Dépendance fonctionnelles. - L’attribut b dépend fonctionnellement d’un attribut a, si à une valeur de a correspond au plus une valeur b. - Code-apogé → nom-étudiant - In dit que nom-étudiant depend fonctionnelement du code-apogé, si je connais le code-apogé je connais le nom de l’étudiant. # Exemples - Supposons les exemples suivants, qui concernent des pilotes ayant un numéro, un nom, une fonction (copilote, commandant, instructeur...), et un nombre d’heures de vol pour un jour particulier: - numPilote,jour → nbHeures Vol est une DF, car à un couple (numPilote, jour) correspond au plus un nombre d’heures de vol; - numPilote → nomPilote, fonction est équivalente aux écritures numPilote → nomPilote et numPilote → fonction qui sont deux DF. En conséquence numPilote → nomPilote, fonction est une DF ; - nomPilote → fonction est une DF s’il n’y a pas d’homonymes dans la population des pilotes enregistrés dans la base de données. Dans le cas contraire, ce n’est pas une DF, car à un nom de pilote peuvent correspondre plusieurs fonctions ; # DF élémentaire - Une DF a,b → c est élémentaire si ni a → c, ni b→ c ne sont des DF. - Considérons les exemples suivants : - la dépendance fonctionnelle numPilote,jour → nbHeures Vol est élémentaire, car numPilote → nbHeures Vol n’est pas une DF (un pilote vole différents jours, donc pour un pilote donné, il existe plusieurs nombres d’heures de vol), pas plus que jour → nbHeures Vol (à un jour donné, plusieurs vols sont programmés); - la dépendance fonctionnelle numPilote, nomPilote → fonction n’est pas élémentaire, car le numéro du pilote suffit pour retrouver sa fonction numPilote → fonction est une DF). # DF directe - Une DF a → c est directe si elle n’est pas déduite par transitivité, c’est-à-dire s’il n’existe pas de DF a → bet b→ c. - Considérons l’exemple d’un avion avec les attributs suivants : - (immat : numéro d’immatriculation d’un avion; typeAvion : type de l’avion; nomConst : nom du constructeur de l’avion). - La dépendance immat → nomConst n’est pas une DF directe. - La dépendance immat → typeAvion est une DF directe. - La dépendance typeAvion → nomConst est une DF directe. # Propriétés de DF - Les propriétés suivantes sont appelées «axiomes d’Armstrong ». - Réflexivité : si b est un sous-ensemble de a alors a → b est une DF. - Augmentation : si a → b est une DF, alors a,c → b,c est une DF. - Transitivité : si a → b et b → c sont des DF, alors a→ c est une DF. - Union: si a → b et a → c sont des DF, alors a → b,c est une DF. - Pseudo-transitivité : si a → b et b,c → d sont des DF, alors a,c → d est une DF. - Décomposition : si a → b,c est une DF, alors a → b et a → c sont de DE # Normalisation - Les classifications formelles utilisées pour décrire le niveau de normalisation d’une base de données relationnelle sont appelées les formes normales (FN) - Il existe huit formes normales. Les quatre premières sont les plus pratiques et sont à connaître: - la lère forme normale (1FN), - la 2ème forme normale (2FN), - la 3ème forme normale (3FN), - la forme normale de Boyce-Codd qui est la plus aboutie (FNBC). # 1FN - Une relation est en 1FN si tous ses attributs ont une valeur atomique. - Pas en 1FN | isbn | auteur | |---|---| | 2-212-09283-0 | Gardarin | | 2-7117-8645-5 | Abiteboul, Hull, Vianu | - En 1FN | isbn | auteur | |---|---| | 2-212-09283-0 | Gardarin | | 2-7117-8645-5 | Abiteboul | | 2-7117-8645-5 | Hull | | 2-7117-8645-5 | Vianu | - Une fois on passe à la 1FN il faut designer la clé primaire ! - 1NF | Pavillon | Pavillon | Personne | |---|---|---| | N° pavillon Adresse Occupants | N° pavillon Adresse Code postal Ville | N° Personne Nom Prénom Âge 0,n | - Occuper 1,1 # 2FN - Une relation est en 2FN si elle est en 1FN et si chaque attribut non clé dépend totalement et non partiellement de la clé primaire (c-à-d que la dépendance est élémentaire). - Pas en 2FN | isbn | titre | bib | nb_ex | |---|---|---|---| | 2-212-09283-0 | Bases de données ... | Toulon | 3 | | 2-7117-8645-5 | Fondements des ... | Toulon | 1 | | 2-212-09283-0 | Bases de données | Aix-Marseille 3 | 7 | - En 2FN | isbn | bib | nb_ex | | |---|---|---|---| | 2-212-09283-0 | Toulon | 3 | | | 2-7117-8645-5 | Toulon | 1 | | | 2-212-09283-0 | Aix-Marseille 3 | 7 | | | isbn | titre | | | |---|---| | | | 2-212-09283-0 | Bases de données ... | | | | 2-7117-8645-5 | Fondements des ... | | | # 3FN - Une relation est en 3FN si elle respecte la 2FN et si les dépendances fonctionnelles entre la clé primaire et les autres attributs sont directes. - Pas en 3FN | isbn | titre | éditeur | pays | |---|---|---|---| | 2-212-09283-0 | Bases de données ... | Eyrolles | France | | 2-7117-8645-5 | Fondements des ... | Vuibert | USA | | 2-212-0969-2 | Internet/Intranet ... | Eyrolles | France | - En 3FN | isbn | titre | éditeur | pays | |---|---|---|---| | 2-212-09283-0 | Bases de données ... | Eyrolles | France | | 2-7117-8645-5 | Fondements des ... | Vuibert | USA | | 2-212-0969-2 | Internet/Intranet ... | Eyrolles | France | - En 3FN | isbn | titre | éditeur | éditeur | pays | |---|---|---|---|---| | 2-212-09283-0 | Bases de données ... | Eyrolles | Eyrolles | France | | 2-7117-8645-5 | Fondements des ... | Vuibert | Vuibert | USA | | 2-212-0969-2 | Internet/Intranet ... | Eyrolles | Eyrolles | France | # FNBC - Une relation est en forme normale de Boyce-Codd si elle est en 3FN et que le seul déterminant (membre gauche des DF) existant dans la relation est la clé primaire. - En 3FN | | | | |---|---|---| | rue | ville | cp | | cp | ville | | - Pas en FNBC | clés candidates: | | | |---|---|---| | {rue, ville} | | | | {rue,cp} | | | - En FNBC | cp | ville | rue | |---|---|---| | 83100 | Toulon | Rue des Lilas | | 13008 | Marseille | Rue des Palmiers | | 13013 | Marseille | Rue des Lauriers | - En FNBC | cp | ville | rue | |---|---|---| | 83100 | Toulon | | | 13008 | Marseille | | | 13013 | Marseille | | # Exemple - Considérons l’exemple des assurances de véhicules. Décomposer cette relation de manière à obtenir un schéma normalisé. | Ventes | | | | | | | | |---|---|---|---|---|---|---|---| | num | nom | nimm | type | ncons | nomCons | dateAchat | prixAchat | | 1 | Soutou | 6748-XW-31 | 320 i | 1 | BMW | 12-05-1999 | 62 500 | | 1 | Soutou | 734-AJH-31 | 1200 GSF | 3 | SUZUKI | 19-07-2000 | 46680 | | 1 | Soutou | 358-ALZ-31 | 320 Ci | 1 | BMW | 25-01-2001 | 213 970 | | 2 | Bidal | 955-NEH-75 | Scenic | 2 | RENAULT | 16-01-1995 | 105 000 | | 2 | Bidal | 52-AIM-31 | Beetle | 4 | VOLKSWAGEN | 16-01-2002 | 109 500 | # 1FN | Ventes 1 | | | | | | | | |---|---|---|---|---|---|---|---| | num | nom | nimm | type | ncons | nomCons | dateAchat | prixAchat | | 1 | Soutou | 6748-XW-31 | 320 i | 1 | BMW | 12-05-1999 | 62 500 | | 1 | Soutou | 734-AJH-31 | 1200 GSF | 3 | SUZUKI | 19-07-2000 | 46680 | | 1 | Soutou | 358-ALZ-31 | 320 Ci | 1 | BMW | 25-01-2001 | 213 970 | | 2 | Bidal | 955-NEH-75 | Scenic | 2 | RENAULT | 16-01-1995 | 105 000 | | 2 | Bidal | 52-AIM-31 | Beetle | 4 | VOLKSWAGEN | 16-01-2002 | 109 500 | # 2FN | Ventes 2 | | | | | | |---|---|---|---|---|---| | num# | nimm# | dateAchat | prixAchat | | | | 1 | 6748-XW-31 | 12-05-1999 | 62 500 | | | | 1 | 734-AJH-31 | 19-07-2000 | 46680 | | | | 1 | 358-ALZ-31 | 25-01-2001 | 213 970 | | | | 2 | 955-NEH-75 | 16-01-1995 | 105 000 | | | | 2 | 52-AIM-31 | 16-01-2002 | 109 500 | | | | Assures 2 | | |---|---| | num | nom | | 1 | Soutou | | 2 | Bidal | | | | | | |---|---|---|---| | Vehicules 2 | nimm | type | ncons | nomCons | | | 6748-XW-31 | 320 i | 1 | BMW | | | 734-AJH-31 | 1200 GSF | 3 | SUZUKI | | | 358-ALZ-31 | 320 Ci | 1 | BMW | | | 955-NEH-75 | Scenic | 2 | RENAULT | | | 52-AIM-31 | Beetle | 4 | VOLKSWAGEN | -La question à se poser: “De quoi dépend élémentairement chaque attribut de la relation? " | Ventes 1 | | | | | | | | |---|---|---|---|---|---|---|---| | num | nom | nimm | type | ncons | nomCons | dateAchat | prixAchat | | 1 | Soutou | 6748-XW-31 | 320 i | 1 | BMW | 12-05-1999 | 62 500 | | 1 | Soutou | 734-AJH-31 | 1200 GSF | 3 | SUZUKI | 19-07-2000 | 46680 | | 1 | Soutou | 358-ALZ-31 | 320 Ci | 1 | BMW | 25-01-2001 | 213 970 | | 2 | Bidal | 955-NEH-75 | Scenic | 2 | RENAULT | 16-01-1995 | 105 000 | | 2 | Bidal | 52-AIM-31 | Beetle | 4 | VOLKSWAGEN | 16-01-2002 | 109 500 | - Considérons tous les attributs : - num, nimm, dateAchat → prixAchat (prixAchat dépend de la clé); - nimm → ncons, type, nomCons; - num → nom. - Les relations déduites de ces DF sont par définition en 2FN # 3FN | Ventes 3 | | | | | |---|---|---|---|---| | num# | nimm# | dateAchat | prixAchat | | | 1 | 6748-XW-31 | 12-05-1999 | 62 500 | | | 1 | 734-AJH-31 | 19-07-2000 | 46680 | | | 1 | 358-ALZ-31 | 25-01-2001 | 213 970 | | | 2 | 955-NEH-75 | 16-01-1995 | 105 000 | | | 2 | 52-AIM-31 | 16-01-2002 | 109 500 | | | Assures 3 | | |---|---| | num | nom | | 1 | Soutou | | 2 | Bidal | | Vehicules 3 | | | |---|---|---| | nimm | type | ncons# | | 6748-XW-31 | 320 i | 1 | | 734-AJH-31 | 1200 GSF | 3 | | 358-ALZ-31 | 320 Ci | 1 | | 955-NEH-75 | Scenic | 2 | | 52-AIM-31 | Beetle | 4 | | Constructeurs 3 | | |---|---| | ncons | nomCons | | 1 | BMW | | 3 | SUZUKI | | 1 | BMW | | 2 | RENAULT | | 4 | VOLKSWAGEN |

Analyse des Bases de Données (BD&SI) - Notes de Cours PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue