Royaume du Maroc

Université Hassan Premier

Faculté des Sciences et Techniques De Settat

Bases de données
Chapitre 3 :Conception des bases de données, le Modèle
Relationnel

Kaoutar ERRAKHA Année Universitaire : 2022_2023

[email protected]
 PLAN DU COURS
 Définitions

 Règles de transformation MCD en modèle

relationnel

 Dépendance fonctionnelle

 Normalisation

Cours : Base de données 2

 DÉFINITIONS

 Modèle relationnel
 Le modèle relationnel a été défini par E.F. Codd en 1970 à IBM San José
 Aspects fondamentaux du modèle relationnel:
 une algèbre permettant de manipuler des tables et des relations
 une démarche de conception permettant de définir une collection de relations
 A ce type de modèle sont associées les notions suivantes:
 domaine
 table relationnelle
 attribut
 tuple (ou n-uplet)

Cours Base de donnèes 3

  DÉFINITIONS

 Domaine

 Un domaine est un ensemble de valeurs ayant une signification pour

 l'utilisateur

 Ex: le domaine des noms, le domaine des âges,...

Cours Base de donnèes 4

  DÉFINITIONS

 Une table relationnelle

 Une table relationnelle:= Sous-ensemble du produit cartésien d'une liste

 de domaines (non nécessairement distincts).

 n Une table relationnelle est généralement caractérisée par un nom

Cours Base de donnèes 5

  DÉFINITIONS

Modèle relationnel : Attribut

 L'ordre des colonnes est sans importance

 Plusieurs colonnes peuvent appartenir à un même domaine

 On associe un nom à chaque colonne

 Un attribut := colonne d'une table caractérisée par un nom

Cours Base de donnèes 6

  DÉFINITIONS

 Clé de table relationnelle

 Une clé est un ensemble minimal d'attributs qui détermine tous les autres.
 Il peut y avoir plusieurs clés pour une même relation; on en choisit en général une
comme clé primaire.

Cours Base de donnèes 7

  DÉFINITIONS

 Le lien entre 2 tables relationnelles est réalisé par la duplication de la clé primaire d’une
table dans l’autre
 Cette clé dupliquée est appelée clé externe (ou étrangère)

Cours Base de donnèes 8

  MODÈLE RELATIONNEL

 Degré de la relation

Nombre d'attributs (n) dans son schéma relationnel

Cette relation est de degré 7 :

ÉTUDIANT( Nom,NAS,Téléphone,Adresse, Téléphone parent, Âge, Département)

Cours Base de donnèes 9

  MODÈLE RELATIONNEL

Contraintes relationnelles

 Contraintes de domaines
 Contraintes de clés
 D’intégrité
 D’intégrité référentiel

=> Doivent être vérifiées par chaque instance du schéma

10

Cours Base de donnèes 10

  MODÈLE RELATIONNEL

 Contraintes de domaines
• La valeur de chaque attribut dans un tuple est atomique. (non divisible)
 Attributs composites ou multivalués
 La valeur doit respecter le format des données du domaine de l’attribut (entier, réel, date,
caractère)
 Contraintes de clés
 Chaque tuple dans une relation doit être unique.
 Toute relation doit posséder une clé qui identifie un tuple de façon unique.
 Une relation peut posséder plusieurs clés candidates.
 La clé choisie est appelé clé primaire.
Elle est soulignée dans la relation.
Exemple:
ÉTUDIANT(Nom,NAS,CodeApogee,Adresse,Téléphone,DateNaissance)
11

Cours Base de donnèes 11

  MODÈLE RELATIONNEL

 Contraintes d'intégrité-entité

Stipulent qu'aucune clé primaire ne doit être nulle.

Contraintes d'intégrité de référence contraintes spécifiées entre deux relations et utilisées pour
maintenir la consistance entre les tuples de deux relations.
concept de clé étrangère ( Foreign Key ).

12

Cours Base de donnèes 12

 •2
• Règles de
transformation
MCD en modèle
relationnel
  MCD EN MODÈLE RELATIONNEL

 Attribut
Une propriété est transformée en attribut
 Clé primaire

Un identifiant est transformé en une clé primaire

 Clé composée

Une concaténation d’identifiants est transformée en une clé composée

 Table issue d’entité Toute entité est transformé en table. Ses propriétés deviennent les

attributs de la table. L’identifiant devient clé primaire de la table

14

Cours Base de donnèes 14

 MCD EN MODÈLE RELATIONNEL

15

Cours Base de donnèes 15

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association binaire:

 (0,n)-(1,1)
 (1,n)-(1,1)

 La clé primaire de la table issue de l’entité côté cardinalités (0,n) ou (1,n) est dupliquée
dans la table issue de l’entité côté cardinalités (1,1) où elle devient clé étrangère.

 Table issue d’une association binaire

• (0,1)-(0,1)

 La clé primaire de la table issue de l’une des entités est dupliquée dans la table issue de
l’autre entité où elle devient clé externe qui peut être une valeur nulle.
16

Cours Base de donnèes 16

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association binaire

 (0,n)-(0,1)
 (1,n)-(0,1)

 La clé primaire de la table issue de l’entité côté cardinalités (0,n) ou (1,n) est dupliquée
dans la table issue de l’entité côté cardinalités (0,1) où elle devient clé externe qui peut
être une valeur nulle
 Si l’association est porteuse de propriétés :
 Les deux entités sont traités comme si l’association était (0,n) –(0,n)
 L’association est transformée en relation ayant comme clé la clé primaire de la relation du côté
(0,1). On rajoute ensuite, dans la relation une référence à la clé primaire de l’autre entité.

17

Cours Base de donnèes 17

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association binaire

 (0,1)-(1,1)

 La clé primaire de la table issue de l’entité côté cardinalités (0,1) est dupliquée dans la
table issue de l’entité côté cardinalités (1,1) où elle devient clé étrangère.

18

Cours Base de donnèes 18

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association binaire

 (0,n)-(0,n)
 (1,n)-(1,n)
 (1,n)-(0,n)

 Une table ayant comme clé une clé composée des identifiants des 2 entités est créée.
Les éventuelles propriétés de l’association deviennent les attributs de la table

19

Cours Base de donnèes 19

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une relation ternaire ou supérieure

 Une table ayant comme clé une clé composée des identifiants des entités est créée. Les
éventuelles propriété de l’association deviennent les attributs de la table

20

Cours Base de donnèes 20

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association réflexive (0,n)-(0,1)

 La clé primaire de la table issue de l’entité est dupliquée dans cette table où elle devient
une clé externe qui peut être une valeur nulle. Les éventuelles propriétés de
l’association deviennent des attributs de la table.
 Avec un même traitement particulier lorsque l’association est porteuse de propriétés.

21

Cours Base de donnèes 21

  MCD EN MODÈLE RELATIONNEL

 Table issue d’une association réflexive

 (0,n)-(0,n)
 (1,n)-(1,n)
 (1,n)-0,n)

 Une table ayant comme clé une clé composée de 2 fois l’identifiant de l’entité est créée.
Les éventuelles propriétés de l’association deviennent des attributs de la table.

22

Cours Base de donnèes 22

 MCD EN MODÈLE RELATIONNEL

23

Cours Base de donnèes 23

  SPÉCIALISATION /GÉNÉRALISATION

 Spécialisation : deux solutions

 Solution 1 : on exprime les sous-types par des relations spécifiques correspondant

en fait à des associations (0,1)-(1,1).

ASSURE( NumAssure,Adresse1,Adresse2,CP,Ville,Tel)
PARTICULIER(NumAssure,Profession,ClasseAge)
ENTREPRISE(NumASSURE,N°SIREN,FormeJuridique)

 On parle alors d’implémentation verticale

24

Cours Base de donnèes 24

  SPÉCIALISATION /GÉNÉRALISATION

Solution 2 : (applicable seulement si aucune association ne porte sur le sur type).

On duplique la totalité du sur-type dans les sous-types associés.

 PARTICULIER(NumAssure,Profession,ClasseAge, Adresse1,Adresse2,CP,Ville,Tel)

 ENTREPRISE(NumASSURE,N°SIREN,FormeJuridique,Adresse1,Adresse2,CP,Ville,Tl)

25

Cours Base de donnèes 25

  SPÉCIALISATION /GÉNÉRALISATION

 Critères de choix entre les deux implémentations(verticales ou horizontale)

 Selon l’usage ultérieur de la base et le type de requêtes

formulées.

 Exemple : que choisir dans le cas d’une bibliothèque avec des document (livres ou
périodiques).

26

Cours Base de donnèes 26

 SPÉCIALISATION /GÉNÉRALISATION

27

Cours Base de donnèes 27

 EXERCICE D’APPLICATION

 Une société loue des photocopieurs à ses clients. La location d’un photocopieur est
obligatoirement assortie d’un contrat de maintenance, qui prévoit l’intervention d’un
technicien dans les 24 h. Pour améliorer la gestion des interventions, La société
souhaite recourir à une base de données.

 Elaborer le diagramme entité-association (MCD)

 Convertir le diagramme MCD en modèle relationnel

28

Cours Base de donnèes 28

 EXERCICE D’APPLICATION

29

Cours Base de donnèes 29

 EXERCICE D’APPLICATION

30

Cours Base de donnèes 30

 •3
• Dépendance
fonctionnelle
  DÉPENDANCE FONCTIONNELLE

 Le modèle relationnel permet de structurer les données à stocker dans la BD.

ex: même situation Y différents schémas relationnels Les SGBD-R sont plus efficaces si:

 Les requêtes sont faciles à écrire.

 Les données sont faciles à accéder.

 L’intégrité des données est conservée (mises à jour). Leur objectif est d’éviter tout
problème d’ajout, de suppression ou de mise à jour de données. Il faut normaliser les
données. Théorie de la normalisation
 Basée sur la notion de dépendance fonctionnelle

32

Cours Base de donnèes 32

  DÉPENDANCE FONCTIONNELLE

 Notion de dépendance entre attributs

La valeur de la clé pour tout tuple donné doit déterminer les valeurs pour tous les autres
attributs du tuple.

Contrainte de dépendance fonctionnelle

permet de normaliser une BD;
représente la généralisation du concept de clé.

33

Cours Base de donnèes 33

  DÉPENDANCE FONCTIONNELLE

 Une dépendance fonctionnelle est notée : X=> Y, où X et Y sont des attributs de R

X=>Y signifie que si on connaît la valeur de X, on peut connaître celle de Y (X
détermine Y ou Y a une dépendance fonctionnelle de X)

 Exemple

PRODUIT(numProd,nom,PrixUnit)

numProd =>nom,prixUnit

NOTE(no_controle,no_eleve,note)
no_eleve,no_controle => note 34

Cours Base de donnèes 34

  DÉPENDANCE FONCTIONNELLE

 DF Elémentaire

DF a->B est élémentaire , s’il n’existe pas C inclus dans A ,qui assure lui-même une DF
c->B.

Ex:
numArticle->nomArticle
numFact,RefArt->quantitéArt
numFacture,numArticle->nomArticle

35

Cours Base de donnèes 35

  DÉPENDANCE FONCTIONNELLE

 DF Directe

Une DF A->B est directe , s’il n’existe pas un attribut C qui engendrait une DF
transitive A->C->B.

Num_fact->num_représentant

Num_représentant->nom_représentant

Num_fact->nom_représentant

36

Cours Base de donnèes 36

  DÉPENDANCE FONCTIONNELLE

 Triviale

Une DF est triviale si et seulement si le membre B est un sous ensemble du membre A

𝐴1 ,𝐴2 ,……𝐴𝑛 → 𝐵1 ,𝐵2 … … . . 𝐵𝑛

Triviale: si B1….Bn est un sous ensemble de 𝐴1 ,𝐴2 ,……𝐴𝑛 .

Non triviale:si au moins un Bi n’appartient pas à 𝐴1 ,𝐴2 ,……𝐴𝑛

Complétement non triviale : si aucun des Bi n’appartient à 𝐴1 ,𝐴2 ,……𝐴𝑛

37

Cours Base de donnèes 37

  DÉPENDANCE FONCTIONNELLE

Les axiomes d'Armstrong

1. Réflexivité

 Tout groupe d'attributs se détermine lui même et détermine chacun de ses attributs (ou sous
groupe de ses attributs).Soient X et Y des attributs :
 XY→XY et XY→X et XY→Y

2. Augmentation

 Si un attribut X détermine un attribut Y, alors tout groupe composé de X enrichi avec d'autres
attributs détermine un groupe composé de Y et enrichi des mêmes autres attributs.

Soient X, Y et Z des attributs :

 X→Y ⇒ XZ→YZ
38

Cours Base de donnèes 38

  DÉPENDANCE FONCTIONNELLE

3. Transitivité

 Si un attribut X détermine un attribut Y et que cet attribut Y détermine un autre attribut

Z, alors X détermine Z.
 Soient X, Y et Z des attributs :

 X→Y et Y→Z ⇒ X→Z

4. Union

Si un attribut détermine plusieurs autres attributs, alors il détermine tout groupe
composé de ces attributs. Soient X, Y et Z des attributs :
X→Y et X→Z ⇒ X→YZ 39

Cours Base de donnèes 39

  DÉPENDANCE FONCTIONNELLE

5. Décomposition

 Si un attribut détermine un groupe d'attribut, alors il détermine chacun des attributs de

ce groupe pris individuellement.

Soient X, Y et Z des attributs : X→YZ ⇒ X→Z et X→Y

6. Projection

Soient X,Y des attributs x->YZ => x=Y

40

Cours Base de donnèes 40

  DÉPENDANCE FONCTIONNELLE

 Fermeture transitive F+

 On appelle fermeture transitive F+ d'un ensemble F de DF, l'ensemble de toutes les

DFE qui peuvent être composées par transitivité à partir des DFE de F.

Soit l'ensemble F = {A→B, B→C, B→D, A→E}.

 La fermeture transitive de F est F+ = { A→B, B→C, B→D, A→E, A→C, A→D }

41

Cours Base de donnèes 41

  NOTION DE GRAPHE DES DFE

 On peut représenter un ensemble de DFE par un graphe orienté (ou plus précisément un
réseau de Pétri), tel que les nœuds sont les attributs et les arcs les DFE
 (avec un seul attribut en destination de chaque arc et éventuellement plusieurs en
source).
Soit la relation Voiture(NVH, Marque, Type, Puis, Couleur) avec l'ensemble des DF F =
{NVH→Type, Type→Marque, Type→Puis, NVH→Couleur}.
On peut représenter F par le graphe ci-dessous :

42

Cours Base de donnèes 42

  EXERCICE

 Soit R(A,B,C,D,E,G,H) F = { AB->C ; B-> D ; CD-> E ; CE->GH ; G-> A }. En

utilisant les axiomes d’Armstrong, montrer que l’on peut déduire de cet ensemble :

 AB->E
 BG->C

 AB->G

43

Cours Base de donnèes 43

  SOLUTION EXERCICE

AB->E

 B->D donc AB->D par augmentation

 AB->C et AB-> D donc AB->CD par union
 AB->CD et CD->E donc AB->E par transitivité

BG->C

 G -> A donc BG -> A par augmentation,

 BG ->BG donc BG ->B par projection,
 BG -> A et BG ->B donc BG ->AB par union,
 BG -> AB et AB -> C donc BG ->C par transitivité.
44

Cours Base de donnèes 44

  SOLUTION EXERCICE

 AB->G

AB -> E et AB->C donc AB-> CE par additivité,

AB -> CE et CE -> GH donc AB->GH par transitivité,
AB ->GH donc AB-> G par projection.

45

Cours Base de donnèes 45

 •4
• Normalisation
  NORMALISATION

Cette procédure fait subir à une relation une série de tests pour certifier qu’elle
appartient à une certaine forme normale. Codd (mathématicien,chercheur de
IBM,1972) a proposé trois formes normales:

1NF : première forme normale,

2NF : deuxième forme normale,
3NF : troisième forme normale.

Ces formes normales sont basées sur les dépendances fonctionnelles entre les attributs
d'une relation

47

Cours Base de donnèes 47

  NORMALISATION

Avec le temps, d'autres formes normales se sont développées:

 BCNF : forme normale de Boyce-Codd ¨

 4NF : quatrième forme normale

 5NF : cinquième forme normale

 PJNF : forme normale à projections jointives

 DKN : forme normale à domaines-clés

Elles traitent des cas particuliers non résolus par l'application successive des trois
premières formes normales.

48

Cours Base de donnèes 48

  NORMALISATION

 1NF : Première forme normale

La première forme normale contient 2 règles:

 Une relation est en 1NF si elle possède au moins une clé et si tous ses attributs sont
atomiques.

 Un attribut est atomique si il ne contient qu'une seule valeur pour un tuple donné,
et donc s'il ne regroupe pas un ensemble de plusieurs valeurs.

49

Cours Base de donnèes 49

  NORMALISATION

 2NF : Deuxième forme normale

Une relation est en 2NF si elle est en 1NF et si tout attribut n'appartenant à aucune clé
candidate ne dépend pas d'une partie seulement d'une clé candidate.

Personne(#NumeroEmployé, #Profession, Nom, Prénom, Salaire) .

Soit les DF suivantes sur cette relation :

 NumeroEmployé, Profession→Nom
 NumeroEmployé, Profession→Prénom

 NumeroEmployé, Profession→Salaire

 Profession→Salaire
50

Cours Base de donnèes 50

  NORMALISATION

 Une relation est en 3NF si elle est en 2NF et si tout attribut n'appartenant à aucune clé candidate ne dépend
directement que de clés candidates.
 C'est à dire que toutes les DFE vers des attributs n'appartenant pas à une clé, sont issues d'une clé.

Profession(#Profession, Salaire, Prime)

Soit les DF suivantes sur cette relation :

Profession→Salaire

Profession→Prime

Salaire→Prime

Cette relation n'est pas en 3NF car Salaire, qui n'est pas une clé, détermine Prime.

Pour avoir un schéma relationnel en 3NF, il faut décomposer Profession 51

Cours Base de donnèes 51

  CONSÉQUENCES DU NON RESPECT DE LA NORMALISATION

 Redondance de l'information; les attributs qui réfèrent à un attribut non clé seront
répétés pour chaque apparition de cet attribut non clé

 Inconsistance; à toute les fois qu'une donnée est modifiée, toutes les réplications de ces
données doivent être modifiées.

52

Cours Base de donnèes 52

  AVANTAGES DE LA NORMALISATION

1. Redondance réduite

 Moins de données dans la base, donc une plus petite BD.

 Plus petite est la BD et plus rapides sont les E/S.

2. Plus petites relations et plus petits tuples

3. Assure que la BD est dans un état consistant

53

Cours Base de donnèes 53