cours des bases de données

Université Mohamed premier Année universitaire 11/12 Faculté des sciences Filière SMI, S5 Département de Mathématiques et d’informatique

Bases de données et systèmes Bases de données et systèmes d’informationd’information

Professeur : M. MOUSSIProfesseur : M. MOUSSI

1M. Moussi

Plan du cours :

Partie I : Concepts généraux. Partie II : Bases de données relationnelles Partie III : initiation au Langage SQL.

2M. Moussi

Première partie :Concepts généraux.

3M. Moussi

Plan : Introduction. Définitions. composants liés à la gestion d’une base de

données. Fonctions fournies par un SGBD. Indépendance des données. Architecture d’un SGBD. Principe de fonctionnement d’un SGBD. Cycle de vie d’une base de données.

4M. Moussi

Introduction

Tout le monde utilise régulièrement les bases de données (manuelles ou automatisées).

Inconvénients de la gestion manuelle : La recherche d’information est lente, exemple :

gestion des informations d’un meuble… Les feuilles peuvent être perdues ou déchirées,

donc perte de l’historique. Moins de sécurité : n’importe qui peut accéder aux

informations. …etc.

5M. Moussi

Introduction Avantages de la gestion automatique :

Les travaux ennuyeux de maintenance manuelle des fichiers sont éliminés.

La réduction ou la suppression du volume du papier.

L’efficacité d’accès aux données. La gestion d’un historique. Exactitude : des informations précises et

réactualisées sont disponibles à tout moment. …etc.

6M. Moussi

Introduction Exemples d’applications :

– Application de gestion des enseignements :

Numéro étudiant

Nom étudiant

Prénom étudiant

Adresse

Date de naissance

Numéro module

Numéro enseignant

Salle

date

Heure début

Durée

Numéro étudiant

numéro module

Date inscription

Numéro module

Intitulé module

Volume horaire

semestre

Pré requis

Numéro enseignant

Nom enseignant

Prénom enseignant

adresse

Date de naissance

Module

Enseignant

Inscription

Enseignement

Etudiant

7M. Moussi

IntroductionApplication de gestion des commandes :

Code produit

Désignation

Prix unitaire

Numéro commande

Numéro fournisseur

Code produit

Quantité

Numéro fournisseur

Nom fournisseur

Adresse

Ville

Fournisseur Commande Produit

8M. Moussi

Introduction– Application de réservation de places d’avion :

Numéro passager

Nom passager

Prénom passager

Adresse

Numéro ligne

Ville départ

Ville arrivée

Nom ville

Nom aéroport

Type avion

Nombre de places

Numéro réservation

Numéro passager

Numéro vol

Date réservation

Avion

Réservation

Ville

Ligne

Passage

Numéro vol

Numéro ligne

Jour&Heure du vol

Type avion

Vol

9M. Moussi

Définitions :

Base de données : Collection de données :

Persistantes. Opérationnelles. Enregistrées et utilisées par des systèmes

d’application (des programmes) d’une entreprise particulière.

10M. Moussi

Définitions :

Base de données : Structurée indépendamment d’une quelconque

application. Cohérente. De redondance minimale. Peut être accessible simultanément par plusieurs

utilisateurs.

11M. Moussi

Définitions

Modèle de données : Est un Ensemble de concepts et de règles

d’utilisation de ces concepts qui permettent de décrire les données d’une BD et de modéliser leur structure.

Est un moyen de description du monde réel. Types de modèles de données principaux : Le

modèle hiérarchique, le modèle réseau, le modèle relationnel, le modèle objet.

12M. Moussi

Définitions Schéma d’une base de données :

Est un groupe d’objet d’une base de données (table, index, des contraintes…) qui sont unis et reliés entre eux.

C’est une description de la base de données obtenue en employant un modèle de données.

Exemple : schéma relationnel :Propriétaire du schéma

tables index procédures

contraintesvues

13M. Moussi

Définitions Système de gestion de base de données :

Est un système informatique (logiciel) permettant de maintenir des données et de les rendre disponible à la demande;

Tout SGBD appartient à une classe associée à un modèle de données.

14M. Moussi

Composants liés à la gestion d’une base de données

Les données : Données intégrées : la BD peut être vue comme

une unification de plusieurs fichiers de données distincts, pas de redondance .

Exemple : Etudiant : NumEtudiant, nom, adresse, date de naissance…

Inscription : NumEtudiant, NumModule, DateInscription…

pour le nom d’un étudiant, il suffit de le chercher dans le fichier Etudiant.

15M. Moussi


Données partagées : chaque donnée de la base peut être partagée par plusieurs utilisateurs qui peuvent accéder simultanément à cette donnée.

Exemple :

Dans une application de gestion de la scolarité, les utilisateurs du service d’examen et du service d’inscription peuvent accéder aux informations relatives à l’étudiant dans le fichier Etudiant.

16M. Moussi


Le matériel : se compose des volumes de mémorisation pour le stockage des données, le processeur et la mémoire principale pour l’exécution du logiciel.

Le logiciel : c’est le SGBD, responsable de l’exécution de toutes les requêtes des utilisateurs pour accéder à la base de données et manipuler les données.

Exemple : Oracle, Informix, Sybase, Access, SQL Server…

17M. Moussi


Les intervenants : L’administrateur de la BD : responsable sur le

contrôle les données : définit les types de données à Stocker dans la

base, délivre des autorisations d’accès à la BD, décide des stratégies de maintenance et de

manipulation de ces données.

18M. Moussi


Le programmeur de l’application : développe l’application ( le programme) utilisée pour accéder à la base de données.

L’utilisateur final : accède à la base de données à travers une interface intégrée au logiciel ou bien une application.

19M. Moussi

Fonctions fournies par un SGBD

Description des données : Description des objets (Etudiant, Enseignant,

module…), leurs attributs (NumEtudiant, Nom, Adresse…).

Description des liens ( un Etudiant est inscrit dans un module).

Description des contraintes éventuelles sur les objets, leurs attributs ou leurs liens.

À l’aide du langage de description de données (LDD).

20M. Moussi

Manipulation des données : Concerne les outils et les mécanismes qui

permettent de faire communiquer une BD et l’utilisateur.

Effectuer des opérations de recherche, d’insertion, de modification et de suppression sur cette base.

A l’aide du langage de manipulation de données

( LMD ).

21M. Moussi



Assurer la cohérence des données : Soit une information représentée d’une façon

redondante dans la BD, la mise à jour de cette information dans un seul endroit rend la BD dans un état incohérent.

Une BD incohérente fournit des données incorrecte ou contradictoires.

Par l’élimination de la redondance ou bien son contrôle, le SGBD garantit la cohérence.

22M. Moussi


Exemple : deux étudiants avec les mêmes informations, si j’ai pas d’identifiant (numéro par exemple)?

Intégrité des données : Est relatif à la qualité de la valeur enregistrée,

celle-ci doit vérifier certains propriétés de façon à garantir l’exactitude et la cohérence des données. Ces propriétés sont appelées contraintes d’intégrité.

23M. Moussi

Fonctions fournies par un SGBD Exemples :

Chaque étudiant possède un numéro qui l’identifie. Un étudiant ne peut suivre que quatre module au

plus dans un semestre. Un étudiant ne peut pas être inscrit deux fois dans

un même module. On ne peut pas programmer deux cours pour le

même professeur dans le même créneau. On ne peut pas programmer deux cours dans la

même salle dans le même créneau. La durée d’une séance de cours ne peut excéder 3

heurs et ne peut être moins que 1Heure et ½.

24M. Moussi


Fiabilité : Risque de perte d’informations à cause d’un

incident matériel ou logiciel : erreur de programmation, panne système, coupure du courant, le disque dur qui contient la base est endommagé, ...

Le SGBD doit offrir la possibilité de remettre la BD dans un état opérationnel.

La journalisation des opérations réalisées sur la base et leur ré-exécution automatique en cas de besoin.

25M. Moussi


Les accès concurrents : Plusieurs utilisateurs peuvent accéder aux

données simultanément. Le SGBD doit gérer les conflits qui peuvent être

résultés de ces accès.

Exemple : Soient les deux opérations de mise à jour, notées OP1 et OP2, qui s’effectuent sur une même donnée V=100. On a deux cas :

26M. Moussi


OP1 et OP2 sont séquentielles :

Après exécution séquentielle des deux OP1 et OP2, on obtient V =250.

OP1 OP2

XLire(V) YLire(V)

XX+50 YY+100

Ecrire(X, V) Ecrire(Y, V)

27M. Moussi

Fonctions fournies par un SGBD Exécution d’une manière concurrente :

Après exécution concurrente, on obtient V =200.

OP1 OP2

XLire(V)

XX+50

YLire(V)

Ecrire(X, V)

YY+100

Ecrire(Y, V)

28M. Moussi


Conclusion :– la mise à jour de l’opération OP1 a écrasé celle de

l’opération OP2. Il y’a donc une perte de mise à jour.– Pour éviter ce genre de problème, la technique de

verrouillage est souvent utilisée lors d’accès en modification.

29M. Moussi


Confidentialité : Toute information doit être protégée contre l’accès

des utilisateurs non autorisés soit en écriture ou en lecture.

La confidentialité est assurée par : Le biais de mot de passe et de gestion des droits

d’accès(par utilisateur ou par groupe). L’utilisation des sous-schéma.

30M. Moussi


Exemple :

Dans l’application de la gestion des enseignements Les étudiants sont autorisés à lire les numéros des modules et les horaires de l’enseignement, mais ils ne sont pas autorisés à les modifier…

31M. Moussi

Architecture d’un SGBD

L’architecture d’un SGBD se compose de trois niveaux : interne, conceptuel et externe,et des liens entres ces niveaux.

Vueexterne A

Vueexterne B

Schémaconceptuel

Lien Externe/conceptuel A

Lien Externe/ conceptuel B

Lien Conceptuel/ interne

Niveau conceptuelNiveau externe

Niveau interne

Schéma interne

User A1User A2

User B1User B2User B3

Figure : Architecture d’un SGBD.

32M. Moussi


Le niveau interne ou physique : Est le niveau relatif à la mémoire physique. Il concerne la structure de stockage physique de la

base de données, notamment, les types de fichiers utilisés, caractéristiques des enregistrements (longueur, composants), ainsi que l’accès aux données par exemple à travers les tables et les indexes.

33M. Moussi

Architecture d’un SGBD Le niveau externe :

il s’agit du niveau où les utilisateurs interrogent la BD.

A ce niveau, à chaque utilisateur ou groupe d’utilisateurs, des vues sur tout ou sur une partie du schéma sont associées.

34M. Moussi


Le niveau conceptuel ou logique : présente d’une façon globale et abstraite la

structure des données (les regroupements des données, les liens entre les regroupements) et des règles de gestion et de fonctionnement.

En général, la vue conceptuelle est censée être une vue des données telles qu’elles sont réellement.

35M. Moussi


Les liens : Le lien conceptuel/interne : définit la

correspondance entre la vue conceptuel et la BD en mémoire, spécifie la représentation des enregistrements et des champs au niveau interne.

Pour garder l’invariance du schéma conceptuel, les modifications au niveau interne entraîne des modifications au niveau de ce lien.

36M. Moussi


Un lien externe/conceptuel : définit la correspondance entre une vue externe

et la vue conceptuel.

37M. Moussi

Indépendance des données

Indépendance physique : Permet à l’administrateur de la BD d’être libre de

modifier la structure de mémorisation ou la technique d’accès, suite à un changement des besoins, sans avoir à modifier les applications existantes.

Exemple : des nouveaux types de données peuvent être introduits dans la BD, des nouveaux types de matériel de stockage disponibles, ou ajout d’une table d’index pour accélération.

38M. Moussi


Indépendance logique : Permet la modification du schéma conceptuel

sans avoir à modifier les programmes existants. Elle peut être définie comme la protection des

utilisateurs et des programmes contre tout changement de la structure logique de la BD.

L’administrateur doit pouvoir faire évoluer le système d’information sans remettre en cause la vue de chaque utilisateur ou groupe d’utilisateurs.

39M. Moussi


Exemple : Dans une BD contenant les informations suivantes :

Véhicule (numéro véhicule, marque, type, couleur)

Personne (CIN, nom, prénom)

Propriétaire (CIN, numéro véhicule, date achat)

Un groupe d’utilisateur ne s'intéresse qu'aux personnes qui possèdent une voiture :

Personne (CIN, nom, prénom, numéro véhicule).

Un autre groupe d’utilisateurs ne s'intéresse qu'aux véhicules vendus à une certaine date :

Voiture (numéro véhicule, type, marque, date achat).

40M. Moussi

La suite des phases qui permet de construire le processus d’accès aux données est :

1. L’utilisateur émet une requête au moyen d’un langage de données, en général SQL.

2. Le SGBD intercepte la requête et effectue une analyse sémantique et syntaxique.

3. Traduction au niveau logique : le SGBD inspecte le schéma externe de cet utilisateur, le lien externe/conceptuel correspondant et le schéma logique.

4. Contrôle de confidentialité, concurrence…

Principe de fonctionnement d’un SGBD

41M. Moussi

Principe de fonctionnement d’un SGBD

5. Si la requête est acceptée, le SGBD effectue une optimisation et découpe la requête en sous-requêtes élémentaires et les transfère au niveau interne.

6. Traduction au niveau interne : le SGBD inspecte le lien conceptuel/interne correspondant et la définition de la structure mémoire.

7. Exécution des opérations nécessaires au niveau de la base de données mémoire.

42M. Moussi

Cycle de vie d’une base de données

Les quatre phases suivantes présentent la vie d’une base de données.

Conception : consiste à construire le modèle conceptuel et le

convertir en modèle physique opérationnel. Comporte la définition et la description des

données, la création des structures des données, décrire les contraintes d’intégrité

Pour l’aspect physique, on peut indiquer l’ensemble des supports physiques pour le stockage des données.

43M. Moussi


Implantation des données : Création de la BD, il s’agit de créer un ensemble

structuré. Le SGBD offre des moyens pour implanter

physiquement les données et décrire les liens qui les unissent.

Utilisation : la fiabilité d’une BD est de mettre à la disposition

des données à être utilisées. Le LMD permet l’utilisation.

44M. Moussi


Maintenance : une BD est modifiée tout au long de sa vie, par

l’ajout ou la suppression de table, de colonne ou d’index…, L’insertion et la suppression de données entraînent une fragmentation.

La maintenance est ainsi importante pour garantir l’utilité et les performances d’une BD. Elle doit l’accompagner durant son existence.

S’occupe essentiellement de la façon dont les données sont stockées et du volume de données atteint après un certain temps d’utilisation.

45M. Moussi

Deuxième partie :Bases de données relationnelles.

46M. Moussi

Plan : Introduction. Principaux concepts du modèle relationnel. Contraintes d’intégrité. L’algèbre relationnelle ou langage algébrique. Normalisation.

47M. Moussi

Introduction

Proposé par Ted Codd en 1970. Son objectif essentiel l’accroissement de

l’indépendance des programmes vis-à-vis de la présentation des données.

Fondé sur la théorie mathématique des ensembles.

Exemples des SGBD relationnels : Oracle, Informix, Sybase, Dbase, Access, MySQL,…

48M. Moussi

Introduction

Trois aspects : Aspect structurel : l’utilisateur perçoit les données

comme des tables. Aspect intégrité : ces tables satisfont certains

contraintes d’intégrité. Aspect manipulation : les opérateurs dont dispose

l’utilisateur (par exemple, pour accéder aux données) génèrent de nouvelles tables à partir des tables existantes.

49M. Moussi

Introduction Trois opérations importantes : la restriction (ou

sélection), la projection et la jointure Exemples : soient les deux tables :

Etudiant

Num Nom Prénom Adresse Age

1 Ahmadi Ahmed Lazaret 25 19

2 Najjar Fatima Alkods 8 20

3 Hamadi mohamed Rue 1,21 18

Inscription

NumEtudiant NumModule Date inscription

1 25 15/09/2005

1 26 15/09/2005

3 27 15/09/2006

50M. Moussi

Introduction

Le résultat de restriction sur Etudiant où Age>18 est la relation :

Le résultat de la projection de Etudiant sur Num, Adresse est :




Num Adresse

1 Lazaret 25

2 Alkods 8

3 Rue 1,21

51M. Moussi

Introduction

Le résultat de jointure de Etudiant et Inscription sur Numéro étudiant est :

Num Nom Prénom Adresse Age NumModule Date inscription

1 Ahmadi Ahmed Lazaret 25 19 25 15/09/2009

1 Ahmadi Ahmed Lazaret 25 19 26 15/09/2009

3 Hamadi mohamed Rue 1,21 18 27 15/09/2010

52M. Moussi

Principaux concepts du modèle relationnel

Domaine : est un type de données, il est définit par un

ensemble de valeurs. Exemples :

Dnom : chaîne de caractères de longueur 30. Dnum : entiers compris entre 1 et 10000. Dcouleurs :{blanc, bleu, vert, jaune,

rouge } Dage : entiers compris entre 1 et 100. Ddate : [01 :31] / [01 :12] / [1920 :2011]

53M. Moussi


Relation : formellement, c’est un sous-ensemble du produit

cartésien d’une liste de domaines. Une relation n-aire sur les domaines D1, D2 ,…, Dn

est un sous-ensemble du produit cartésien

D1 D2 … Dn. Un élément appartenant à une telle relation sera

appelé n-uplet ou tuple ou occurrence. Il sera noté (d1, d2 ,…, dn ) où di Di i {1, …n}.

L’entier n est le degré, le nombre de n-uplets est la cardinalité de la relation.

54M. Moussi


Attribut : un attribut Ai de la relation est un identificateur

associé à un domaine Di.

On le désigne parfois par : nom de la relation.nom de l’attribut. les attributs Ai doivent être distincts.

Formellement, un tuple peut être définit comme un produit cartésien ( A1 : D1, A2 : D2 ,…, An : Dn). Une relation comme un ensemble paramétré par le tuple. Ai est une fonction qui associe à un tuple une valeur dans le domaine Di.

55M. Moussi


Un attribut peut ne pas être valué pour un tuple, on dit alors qu’il a une valeur nulle.

Les notions d’attribut multi-valué ou complexe n’existent pas dans le modèle relationnel. Il faut donc les modéliser autrement :

pour un attribut complexe, il faut choisir entre le composé ou les composants.

Pour un attribut multi-valué, il faut créer une autre relation.

56M. Moussi


Exemples :– Soit l’information Adresse : nom rue, N°, ville, code

postal. Par exemple, adresse prend la valeur :

Rue Med V, 2, Oujda, 60000. On a deux solutions :

Un attribut par composant :

Un seul attribut de domaine chaîne de caractères :

nom rue N° ville code postal

Rue Med V 2 Oujda 60000

Adresse

Rue Med V 2 Oujda 60000

57M. Moussi


– Mémoriser les différents cours suivis par les étudiants :

• L’utilisation de plusieurs attributs : cours1, cours2,… est incorrecte.

• Il faut créer une autre relation supplémentaire :

Numéro étudiant Cours

12 BD

13 BD

13 GL

20 LC

20 BD

58M. Moussi


Schéma de relation : un schéma de relation est le nom de la relation

suivi de la liste des attributs avec leurs domaines, par exemple : Etudiant (Numéro étudiant : entier, nom : chaîne, Prénom : chaîne, Adresse : alphanumérique(30), age : entier).

59M. Moussi


Exemple :

Etudiant




3 Hamadi mohamed Rue 1,21 18

Entier chaîne alphanumérique Entier Domaines

Tuple Ou

Occurrence

Attributs

60M. Moussi


Identifiant d’une table : l’unicité de l’identification des n-uples est assurée

par la notion de clé de relation. L’identifiant c’est un ou une liste d’attributs

appartenant au schéma de la relation et dont les valeurs permettent de distinguer les n-uplets.

Une clé minimale, c'est-à-dire comportant un minimum d’attributs, est choisie comme identifiant et est appelé clé primaire, les autres étant appelés clés candidates.

61M. Moussi


Toute relation possède une clé primaire. L’unicité des tuples est une propriété de

l’ensemble dans le sens mathématique. L’attribut(s) présentant la clé primaire n’admet pas

de valeurs nulles. Dans le schéma d’une relation, on souligne la clé

primaire.

Exemple :

Le schéma de relation Etudiant est donnée comme suit :

Etudiant (Numéro étudiant , nom , Prénom , Adresse,age)

62M. Moussi


L’identifiant externe : La clé étrangère est un attribut d’une relation et clé

primaire dans une autre relation référencée. Permet de vérifier automatiquement la présence

des données référencées dans des tables différentes.

Définit dans des tables enfant et assure qu’un enregistrement parent a été créé avant un enregistrement enfant et que l’enregistrement enfant sera supprimé avant l’enregistrement parent.

63M. Moussi


Exemple :Module(table parent)

NumModule intitulé volume horaire semestre

12 BD 70 S5

13 Arch 60 S5

14 Rés 70 S6

Inscription(table enfant)

numEtudiant numModule date inscription

1 12 09/11/10

2 14 08/01/11

4 12 09/11/11

1 14 08/01/11

clé étrangère

Référence une clé primaire

64M. Moussi


Schéma et base de données relationnels : Le schéma relationnel est constitué par l’ensemble

des schémas des relations avec les clés primaires, les clés étrangères (pour chaque relation) et éventuellement d’autres contraintes d’intégrité.

Une BD relationnelle est une collection des différentes relations du schéma relationnel, variables dans le temps. Chaque relation est vue comme un tableau dont les colonnes désignent les attributs et les lignes désignent les n-uplets.

65M. Moussi


Exemples de schéma : Relation : Etudiant Attributs : Numéro étudiant : entier non nul

Nom étudiant : Dnom non nul Age : Dage

Identifiant : Numéro étudiant

Relation : ModuleAttributs : Numéro module : entier non nul

Intitulé module : Dnom non nul Volume horaire : entier non nul

Semestre : enier non nul

Identifiant : Numéro module

66M. Moussi


Relation : Inscription Attributs : Numéro étudiant : entier non nul

Numéro module : entier non nul Date inscription : Ddate.

Identifiants : Numéro étudiant+ Numéro module + Date inscription. Identifiants externes : Numéro étudiant

référence un étudiant. Numéro module

référence un module.

67M. Moussi


Relation : Enseignant

Attributs : Numéro enseignent : entier non nul

Nom enseignant : Dnom non nul Statut : Dnom non nul Tel : entier

Compte : entier non nul

Identifiants : Numéro enseignent Relation : Cours

Attributs : Nom cours : Dnom non nul Numéro enseignant : entier non nul Salle : entier non nul Identifiant : Nom cours Identifiants externes : Numéro enseignant référence un

enseignant.

68M. Moussi

Contraintes d’intégrité

Un schéma de BD décrit les données ainsi que les contraintes que celles–ci doivent vérifier pour être considérées comme des données de qualité. Ces contraintes dites d’intégrité sont généralement des assertions qui doivent être vraies en des instants déterminés ou durant la vie de la base.

A chaque insertion, modification ou suppression les contraintes d’intégrité sont vérifiées automatiquement.

69M. Moussi


Types de contraintes d’intégrité : Contraintes d’intégrité local : portent sur un

ensemble de tuples ou sur des attributs au sein d’une relation :

Concerne un domaine (entier, réel, intervalle, liste de valeur…).

Impose q’un attribut ne puisse pas être sans valeur, c'est-à-dire non nul.

Spécifie une valeur par défaut pour un attribut.

70M. Moussi


Spécifie un ensemble d’attribut constituant la clé primaire, sa valeur doit être unique et non nulle, c’est ce qu’on appelle l’intégrité d’entité.

Impose l’unicité de valeur d’un ensemble d’attributs constituant une clé autre que la clé primaire.

Les contraintes inter-relations : dites aussi contraintes de référence, elles impliquent plus qu’une relation :

71M. Moussi


Permet de spécifier qu’un attribut ou ensemble d’attributs d’une relation ( apparaît comme clé étrangère) référence la clé primaire d’une autre relation.

Signifie qu’un tuple qui fait référence à un tuple d’une autre relation, doit faire référence à un tuple existant dans cette relation, c’est l’intégrité référentielle.

Exemple (voir le paragraphe précédent)

72M. Moussi


Contraintes dynamiques : permettent d’exprimer le fait que les valeurs des données d’un état de la base dépendent de l’état ou d’un des états précédents.

Exemples : Le salaire d’un employé ne peut pas diminuer. Le nouvel age d’une personne doit être supérieur

à l’ancien. Le passage d’une valeur de l’attribut situation

matrimoniale d’une personne à une autre valeur ne doit pas permettre de retour à la valeur célibataire si celle-ci a été modifiée.

73M. Moussi


Les contraintes temporelles : par exemple, Toutes les commandes de la semaine courante doivent avoir été facturées avant le premier jour ouvrable de la semaine suivante.

74M. Moussi

L’algèbre relationnelle ou langage algébrique

Projection : Signature : Relation liste d’attributs

Relation La projection d’une relation R(X,Y) sur Y( où X

désigne la liste des attributs de R ), notée Y(R) ou ProjY(R) est une relation S(Y),

S(Y) = { y | x tel que x, y R(X,Y) }.

La relation est constituée par les colonnes Y de la relation R.

75M. Moussi


Exemple : La relation Genre_livre est le résultat de l’expression Genre(livre).

Relation livre Relation Genre_livre NumLivre Genre

L3 Poésie

L1 Poésie

L2 Roman

L4 Roman

Genre

Poésie

Roman

76M. Moussi


Sélection : Signature : Relation expression logique Relation La sélection permet de rechercher dans une relation

les n-uplets qui satisfont une propriété donnée sous la forme d’une expression logique. Elle est notée E R

ou Select(R, E), où E est l’expression de sélection. Le résultat est une relation de même schéma que la relation R et dont chaque n-uplet vérifie l’expression E, c'est-à-dire :

E R(X)= { x tel que x R(X) et E(X) }.

77M. Moussi


Exemple : soit la relation Livre de l’exemple précédent,

– la sélection : (Genre=’poésie’) (Livre ) est la relation

appelée LivrePoésie: Relation LivrePoésie

– la sélection (Genre=’Roman’ et NumLivre L2 ) (Livre ) est la

relation appelée Roman.

Relation Roman.

NumLivre Genre

L3 Poésie

L1 Poésie

NumLivre Genre

L4 Roman

78M. Moussi


Produit cartésien : Signature : Relation Relation Relation. Soit R(X) et S(Y) deux relations où X et Y sont des

ensembles disjoints d’attributs.

Le produit cartésien de R(X) par S(Y) , noté R S, est constitué par l’ensemble des n-uplets obtenus en concaténant chaque n-uplet de R avec chaque

n-ulpet de S, c'est-à-dire :

R S = { z tel que x R, y S, z T tq X(z)= x et Y(z )=y }.

79M. Moussi


Exemple : soit les deux relations suivantes :

Relation R Relation S

Le résultat du produit cartésien de R par S est la relation T donnée par :

Relation T

NumBib

B1

B2

NumLivre Genre

L3 Poésie

L1 Poésie

NumLivre Genre NumBib

L3 Poésie B1

L3 Poésie B2

L1 Poésie B1

L1 Poésie B2

80M. Moussi


jointure : Signature : Relation Relation-expression Relation La -jointure de deux relations R(X) et S(Y) permet de

constituer une relation sur la base d’une -expression qui doit être vérifiée par des attributs communs, une -expression étant une expression logique construite à l’aide d’expressions de type Xi Yj, est l’un des

opérateurs de comparaison : <, >,, , , =. Cette opération peut se définir comme la composition du

produit cartésien et d’une sélection utilisant la

-expression, soit : (-expression) (R S).

81M. Moussi


Exemple : soient les deux relations suivantes Relation livre Relation auteur

L’équi-jointure( est ‘=‘)sur livre.NumLivre=Auteur.NumLivre est :

Relation auteurlivre

NumLivre Genre

L3 Poésie

L1 Poésie

L2 Histoire

L4 Roman

NomAuteur NumLivre

Ahmed L3

Youssef L1

khaled L4

Mohammed L5

NumLivre Genre NomAuteur NumLivre

L3 Poésie Ahmed L3

L1 Poésie Youssef L1

L4 Roman khaled L4

82M. Moussi


Remarques :– La jointure naturelle de R et S (notée R*S ) est

l’équi-jointure de R et S sur les attributs ayant même nom dans R et S, suivie de la projection qui permet de conserver un seul de ces attributs égaux de même nom.

– La jointure se fait sur deux attributs qui représentent la même information même si ils ont des noms différents.

83M. Moussi


Union : Signature : Relation Relation Relation. L’union de deux relations R(X) et S(X) est une

relation T(X), l’ensemble de ses n-uplets est constitué par l’union des n-uplets de R et ceux de S, c'est-à-dire :

R S := T = {<x> tel que <x> R ou <x> S }.

84M. Moussi


Exemple : soient les deux relations : Relation livre1 Relation livre2

Livre 1 Livre 2

NumLivre Genre

L3 Poésie

L1 Poésie

L2 Histoire

L4 Roman

NumLivre Genre

L3 Poésie

L1 Poésie

L5 Roman

NumLivre Genre

L3 Poésie

L1 Poésie

L2 Histoire

L4 Roman

L5 Roman

85M. Moussi


Intersection : Signature : Relation Relation Relation. L’intersection de deux relations R(X) et S(X) est

une relation T(X) constituée par les n-uplets présents à la fois dans R et dans S :

R S := T = {<x> tel que <x> R et <x> S }.

86M. Moussi


Exemple :L’intersection des deux relations précédentes livre1 et

livre2 est la relation : Livre 1 Livre 2

NumLivre Genre

L3 Poésie

L1 Poésie

87M. Moussi


Différence : Signature : Relation Relation Relation. La différence entre deux relations R(X) et S(X)

est une relation T(X) constituée par les n-uplets présents dans R mais pas dans S :

R \ S := T = { <x> tel que <x> R et <x> S }

88M. Moussi


Exemple :La différence des deux relations livre1 et livre2 est la

relation :

Livre 1 \ Livre 2 NumLivre Genre

L2 Histoire

L4 Roman

89M. Moussi

Normalisation

Introduction : La constitution de la liste d’attributs du schéma

doit refléter un phénomène du domaine d’application et, d’autre part, ne doit pas occasionner de redondance, avec toutes ses implications.

Les formes normales des relations et les mécanismes pour les construire permettent d’obtenir des relations non redondante.

Ces mécanismes sont fondés sur les notions de clés de relations et de dépendances entre données.

90M. Moussi

Normalisation

Exemple de mauvaise relation : Relation Produit

cette relation présente différentes difficultés de mise à jour :

codeProduit libellé PU Qté NumFournisseur Nom Adresse

P1 peinture 55 100 F451 Toto Oujda

P2 Poudre 62,4 200 FH215 Titi Casa

P3 huile 45 400 F214 Tintin oujda

91M. Moussi

Normalisation

Anomalie de suppression : si je supprime un produit, je doit supprimer tout le tuple, ce qui entraîne la perte des informations concernant le fournisseur.

Anomalie d’insertion : pour ajouter un nouveau fournisseur, il faut obligatoirement fournir des valeurs pour le code Produit et la quantité.

Anomalie de modification : si on souhaite changer l’adresse d’un fournisseur , il faut parcourir tous les tuples concernés (tous les produits qu’il fournit), si non on aura une incohérence.

92M. Moussi

Normalisation

Décomposition en trois relations non redondantes sans occasionner de perte d’information. Relation initiale :Produit (code Produit, libellé, PU, Qté, NumFournisseur,Nom,dresse)

Première décomposition :Relation Produit1(code Produit, libellé, PU)Relation Produit2(code Produit, NumFournisseur, Qté, Nom,Adresse)

Deuxième décomposition :Relation Produit1(code Produit, libellé, PU)Relation Produit21(code Produit, NumFournisseur, Qté)Relation Produit22(NumFournisseur, Nom, Adresse)

93M. Moussi

Normalisation Dépendances fonctionnelles (DFs) :

Définition : Soit R(X, Y, Z) un schéma de relation avec Z éventuellement vide. On dit que X détermine Y dans R, ou Y dépend fonctionnellement de X dans R, si pour tout tuple <x, y, z>, <x’,y’,z’> de R, x =x’ y =y’. On note X Y.

Exemple : Dans le schéma : Produit(code Produit, libellé, PU, Qté, NumFournisseur,

Nom, adresse), on a les dépendances fonctionnelles : code Produit libellé, PU ; NumFournisseur Nom, adresse; code Produit, NumFournisseur Qté.

94M. Moussi

Normalisation

Propriétés des DFs : Réflexivité : si Y X alors X Y. Augmentation : si X Y alors X Z Y Z. Transitivité : si X Y et Y Z alors X Z. Union : si X Y et X Z alors X Y Z. Pseudo-transitivité : si X Y et Y W Z.

alors X W Z. Décomposition : si X Y et Z Y alors X Z.

95M. Moussi

Normalisation

Typologie des DFs : X Y est triviale si Y X. X Y est élémentaire si pour tout X’ X, la DF

X’ Y n’est pas vrai. i.e., Y ne dépend d’une partie de X. Par exemple, codeProduit, libellé PU n’est pas élémentaire.

X Y est canonique si sa partie droite ne comporte qu’un seul attribut et un ensemble F de DFs est canonique si chacune de ses dépendances est canonique.

X Y est directe si elle est élémentaire et si Y ne dépend pas transitivement de X.

96M. Moussi

Normalisation

Graphe des DFs : Les DFs peuvent être présentées à l’aide d’un

graphe dont les nœuds sont les attributs impliqués dans les Dfs et les arcs sont les dépendances. Par exemple, T(A, B, C, D, E) avec les DFs : E A , E B, E C et C D :

E

A B C

D

97M. Moussi

Normalisation

Exemple : Produit (code Produit, libellé, PU, Qté, NumFournisseur, adresse),

on a les DFs :– NumFournisseur adresse ;

– code Produit libellé ;

– code Produit PU ;

– NumFournisseur , code Produit Qté ;

et le graphe DFS :

NumFournisseur code Produit

Adresse Qté PU libellé

98M. Moussi

Normalisation

DFs et identifiants : Un attribut ou une liste d’attributs X est une clé

pour la relation R(X,Y,Z) si X Y Z. X est une clé minimale si X Y Z est

élémentaire. Le graphe minimum des DFs permet de trouver

les identifiants de la table. L’identifiant d’une table est l’ensemble minimal

des nœuds du graphe minimum à partir desquels on peut atteindre tous les autres nœuds.

99M. Moussi

Normalisation

Fermeture transitive : la fermeture transitive d’un ensemble F de DFs élémentaires est l’ensemble, noté F+, de DFs élémentaires obtenu en ajoutant à F les DFs élémentaires obtenues par transitivité.

Exemple : T(A, B, C, D, E), avec les DFS :

F = { E A, E B, E C, C D}, F+ = F { E D }.

Deux ensembles de DFs sont dits équivalents s’ils ont la même fermeture transitive.

100M. Moussi

Normalisation

Couverture minimale : Ensemble F de DFs élémentaires vérifiant :

– Aucune dépendance dans F n’est redondante, i.e., pour toute DF f de F, F-f n’est pas équivalente à F

– Toute DF élémentaire des attributs est dans F+ En général, n’est pas unique. Est utile pour la décomposition sans perte

d’information. Exemple : l’ensemble des DFs F = { E A, E B, E C, C D}, admet comme

couvertuture minimale, l‘ensemble : { E A, E B, E C, C D},

101M. Moussi

Normalisation

Normalisation par décomposition : Notion de décomposition :

R(X, Y, Z) est décomposable selon (X, Y) et (X, Z) s’il existe R1 et R2 telles que R1=ProjX,Y(R),

R2= ProjX,Z(R) et R=R1*R2. i.e., R est la jointure

naturelle de R1 et de R2. La décomposition en sous-relations doit vérifier

deux critères fondamentaux :– être sans perte d’information et – préserve les dépendances fonctionnelles.

102M. Moussi

Normalisation

Décomposition sans perte d’information : Une décomposition est sans perte d’information

si la jointure naturelle des sous-relations donne exactement tous les tuples de la relation initiale.

Exemple : Relation R

avec les DFs :

A B,C ;

C D ;

D B.

A B C D

a 5 x 2

b 5 y 1

c 5 x 2

103M. Moussi

Normalisation

La relation R est décomposée en deux sous-relations R1(A, B) et R2(B, C, D) :

Relation R1 Relation R2

La jointure de R1 et R2 fournit 6 tuples alors que la relation R n’en possédait que 3.

B C D

5 x 2

5 y 1

A B

a 5

b 5

c 5

104M. Moussi

Normalisation

Théorème de décomposition : Si R(X, Y, Z) est une relation avec X Y. alors R(X, Y, Z) est décomposable sans perte d’information en R1(X,Y) et R2(X, Z), i.e., R est une jointure des deux relations R1 et R2.

105M. Moussi

Normalisation

Décomposition avec préservation des DFs : Décomposition d’une relation R en R1, R2,…Rn,

telle que F+ des DFs de R est la même que celle de l’union des DFs de R1,R2,…Rn.

Exemple : La décomposition de la relation R(A,B,C,D) précédente en R1(A, B, C) et R2(A,D) est sans perte d’information mais avec perte de DFs, D B ne peut pas se retrouver sur la projection des DFs initiales sur la décomposition.

106M. Moussi

Normalisation

Formes normales : Première forme normale : une relation est dite

en première forme normale (1FN), si :– chaque valeur de chaque attribut de chaque tuple

est une valeur simple et mono‑valué. Exemple : cours (numEtudiant, listcours ) n’est pas

en 1FN car l’attribut listcours n’est pas simple.

107M. Moussi

Normalisation

Deuxième forme normale : Une relation est dite en deuxième forme normale (2FN) si et seulement si :

– elle est en 1FN et– tout attribut n’appartenant pas à une clé ne dépend

pas d’une partie de la clé. Exemple :

Produit2(code Produit,NumFournisseur, Qté, Nom, adresse) n’est pas en 2FN, les attributs nom et adresse ne dépendent que du NumFournisseur.

108M. Moussi

Normalisation Troisième forme normale : une relation est en

troisième forme normale (3FN) si– elle est en 2FN et– si tout attribut ne fait pas partie d’une clé ne dépend

pas d’un attribut non clé.

Exemple : Enseignant (Nom, Bureau, Département, Discipline, TélBur)

Avec des règles de gestions qui se traduisent par les DFs suivantes:

– Département Discipline– Département, Bureau TélBur– TélBur Bureau, département,

N’est pas en 3FN.

109M. Moussi

Normalisation

Théorème : toute relation a au moins une décomposition en 3FN qui est sans perte d’information et qui préserve les DFs.

110M. Moussi

Normalisation

Algorithme de décomposition en 3FN :– Entrée : R(X) avec F est l’ensemble des DF.– Sortie : S = { R1, R2, …,Rn } avec Ri(Xi) en 3NF.

• étape 1 : Rechercher une couverture minimale CM(F) de F.

• étape 2 : Partitionner CM(F) en F1, F2, …, Fn tels que toutes les DFs d'un même groupe aient la même partie gauche.

• étape 3 : Construire les Ri(Xi) avec Xi constituée de l'union des attributs de Fi.

111M. Moussi

Normalisation

Exemple1 : soit la relation T(A, B, C, D, E), avec les DFS élémentaires suivantes : E A, E C,

A B et C D.

l‘algorithe précédent donne les trois relations :

T1(E, A, C), T2(A, B) et T3(C, D). Exemple 2 : soit la relation R(A, B, C,D, E), avec les

DFS élémentaires suivantes : A B, A C,

C, D E et B D. l‘algorithe donne les trois relations :

R1(A,B,C), R2(C,D,E) et R3(B,D)

112M. Moussi

cours des bases de données

Documents