ENCG de Kenitra
Semestre Autonome
S5
Pr S.MOQQADDEM
AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Plan du cours:
I Concepts généraux
II Le modèle E-A
III Le modèle relationnel de données
VI L’algèbre relationnelle
V Langage SQL
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
Introduction
Concepts de base
Règles de passage du MCD au MLD
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.1. Introduction (Motivation d’un MLD)
• La description conceptuelle a permis de représenter le plus fidèlement
possible les réalités de l’univers à informatiser.
• Mais cette représentation ne peut pas être directement manipulée et
acceptée par un système informatique, car le MCD est une
représentation des données dans un formalisme compris par les
concepteurs et pas par la machine.
• Il est donc nécessaire de passer du niveau conceptuel à second un
niveau plus proche des capacités des systèmes informatiques. Ce
niveau, appelé niveau logique, consiste à choisir un
modèle(hiérarchique, réseau, relationnel….) qui repose sur des
techniques d’organisation des données.
• Dans ce chapitre l’accent sera mis sur le modèle relationnel.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Structures de données)
• Ce modèle a été proposé par Codd chez IBM en 1970 ;
• Organisation des données en tables 2D
• Chaque table est identifiée par un nom unique
– Une table 2D = une relation
– Une colonne d’une table = un attribut
– Une ligne d’une table = un tuple (n-uplet)
• Manipulation des données par des opérateurs
– L’algèbre relationnelle
• Cohérence des données gérée par des règles et normes
– Dépendances fonctionnelles
– Théorie de la normalisation
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Relation & attribut)
Chaque colonne d’une table (Attribut), appartient à un ensemble de
valeurs possibles appelé son domaine.
Le domaine désigne toutes les valeurs permises qui peuvent apparaître
dans la colonne.
Si « attrib » est le nom d’une colonne, alors « Dom(attrib) » désigne le
domaine de cette colonne.
Un domaine se définit soit :
• en extension : couleur_voiture = {bleu, vert, marron, noir, gris, rouge}
D1 = D2 = { chaines de caractères }
D3 = { entiers }
• en compréhension : âge_enfants = {x dans N tel que x <15}
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Relation & attribut)
Le schéma de relation représente la structure invariante d’une
relation.
Il est constitué du nom de la relation suivi de la liste des attributs
et de leurs domaines associés; par exemple:
PRODUIT (NPRODUIT : entier, NOM : chaîne, QTESTOCK : entier>0)
Les attributs constituant la clé de r sont écrits en premier dans le
schéma de relation et sont généralement soulignés.
Une BD relationnelle est un ensemble de relations r1, r2,..., rm
Cette représentation est purement logique et non physique : dans un
SGBDR, les données sont logiquement perçues sous forme de tables,
mais elles ne le sont pas physiquement.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Relation & attribut)
Le degré d’une table est le nombre de ses attributs.
Exemple : La table Produit (code produit, libellé du produit,
prix unitaire, quantité en stock).
Le degré de la table « Produit » est égale à 4.
Les domaines sont :
Dom(code produit) : un sous-ensemble des chaînes de caractères
désignant le code d’un produit.
Dom(libellé) : l’ensemble des noms de tous les produits.
Dom(prix unitaire) : un sous-ensemble de l’ensemble des nombres
réels positifs.
Dom(quantité en stock) : un sous ensemble de l’ensemble des
nombres entiers positifs.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Tuple & clé)
Les lignes ou les tuples d’une table sont également nommées des
enregistrements.
Les enregistrements d’une table ont le même format et
représentent des objets ou des relations du monde réel.
Le nombre total des enregistrements présents dans une table est
appelé la cardinalité de la table.
Remarques :
• Le degré d’une table est fixe (ne change pas).
• La cardinalité d’une table est dynamique : elle peut changer
au cours du temps (par effet d’ajout ou de suppression
d’enregistrements de la table).
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Relation: Résumé)
– Format général d’une table (représentation logique
d’une relation) : Attributs
Attrib1 Attrib2 Attrib3 Attrib4 … AttribN
Val11 Val12 Val13 Val14 ... Val1N
Ligne Val21 Val22 Val23 Val24 ... Val2N
... ... ... ... ... ...
ValM1 ValM2 ValM3 ValM4 ... ValMN
Colonne
Pr S.MOQQADDEM AU: 2020/2021
Exemple de table
Nom de la table 3 champs
Commande N°Commande DateCommande Montant
27 13/2/2007 120
65 12/1/2008 34
2 10/06/2006 27
34 14/12/2007 1500
N°Commande est du type numérique entier
4 enregistrements
Montant est du type numérique réel
DateCommande est du type date
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Exemple de relations)
Propriétaire numero nom prenom naissance
5 Madani Hossame 04-févr-80
13 Azzouz Mohcine 15-mai-76
76 Daoud Ali 29-nov-82
Véhicule immatriculation marque proprietaire
3452 A 13 BMW 13
9835 B 72 Renault 76
1234 A 2 Peugeot 5
9878 A 4 BMW 76
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Règles pour une relation)
Une table formée de colonnes et de lignes, a un nom unique.
Chaque colonne a un nom unique.
L’ordre des colonnes est sans aucune importance.
Toutes les lignes ont le même format et le même nombre
d’entrée.
Les valeurs de chaque colonne appartiennent au même domaine.
Chaque entrée dans chaque ligne doit être une valeur unique.
L’ordre des lignes est sans importance du fait qu’elles sont
identifiées par leur contenu et non pas par leur position.
Il n’y a pas deux lignes identiques dans toutes leurs entrées.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base (Règles pour une relation)
– Exemple d’une table « Vol » :
Numéro Départ Arrivée H_départ H_arriv
1 Oujda Rabat 9:55 11:30
2 Oujda Rabat 12:00 12:47
3 Marrakech Agadir 6:00 7:00
4 Tanger Fès 1:13 2:19
5 Laayoune Casablanca 8:15 9:59
6 Tanger Rabat 8:00 9:00
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Clés candidates et clé primaire)
La notion de clé est un concept fondamental du modèle relationnel
parce qu’elle fournit le mécanisme de base pour récupérer des tuples
dans une table de la BD.
On appelle clé candidate d’une relation r sur le schéma relationnel R,
tout sous-ensemble K = {B1, B2, …, Bj} de R (1 j < n) vérifiant les
deux conditions suivantes :
La propriété d’unicité de la clé. Elle implique qu’il n’y a pas
deux tuples différents de r à avoir des entrées identiques dans
tous les attributs de l’ensemble K.
La propriété de minimalité de la clé. Elle signifie qu’aucun
élément de K ne peut être éliminé sans perdre la propriété
d’unicité.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Clés candidates et clé primaire)
Exemple : des clés candidates de la table « Vols »
peuvent être :
• {Numéro}.
• {Départ, Arrivée, H_départ}.
Remarque : une relation peut comporter plusieurs clés
candidates. L’une d’elles est désignée comme clé
primaire.
Les valeurs de la clé primaire permettent d’identifier de
manière univoque un tuple de la relation.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Clés candidates et clé primaire)
Dès qu’une clé primaire a été choisie, les autres clés candidates, si
elles existent sont appelées clés alternatives de la relation.
Un SGBD n’autorise qu’une clé primaire par table.
La clé primaire peut être atomique s’il comporte un seul attribut ou
composé s’il contient au moins deux attributs.
Exemple : la clé primaire {Numéro} est atomique alors que
{Départ, Arrivée, H_départ} est composée.
Les attributs de la clé primaire sont soulignés pour les distinguer
des autres attributs ne faisant pas partie de la clé.
Exemple : le schéma relationnel de la table se présente ainsi :
• Vol {Numéro, Départ, Arrivée, H_départ, H_arriv} ou bien :
• Vol {Numéro, Départ, Arrivée, H_départ, H_arriv}.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Clés candidates et clé primaire)
Les clés primaires peuvent être: naturelles ou artificielles.
Dans une table contenant des données descriptives sur les
éléments chimiques, par exemple, « le poids atomique » serait
une clé primaire naturelle.
Il faut noter que la plupart des clés primaires sont inventées
(artificielles).
Exemples :
• Numéro d’assurance sociale
• Numéro de permis de conduire
• Numéro de carte de crédit
• Numéro de carte guichet
• CIN : code d’identité nationale
• CNE : code national d’étudiant
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Clés candidates et clé primaire)
Remarques :
Les clés sont des éléments très importants dans la
conception d’une BD relationnelle, puisqu’elles
forment la base pour représenter les relations entre
les tables.
Les clés sont les éléments qui lient les tables entre
elles.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(la valeur NULL )
une valeur conventionnelle notée NULL et appelée valeur nulle.
Dans une relation, la valeur NULL représente des données
manquantes, inconnues ou des données inapplicables.
La valeur NULL correspond à une entrée non renseignée (non
saisie, non introduite, non obligatoire).
La règle précédente signifie que la saisie des valeurs pour les
attributs de la clé primaire est obligatoire.
Un attribut n’appartenant pas à la clé primaire peut avoir la valeur
NULL.
Attention :
• La valeur NULL n’est pas égale à 0 (zéro) et ne représente
aucune valeur particulière pour l’ordinateur.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
• Les règles à observer pour assurer la cohérence des
données dans une base sont:
INTÉGRITÉ DE DOMAINE
INTÉGRITÉ DE RELATION(DE CLÉ)
INTÉGRITÉ DE RÉFÉRENCE
Ces contraintes sont définies au moment de la
création des tables.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
LES CONTRAINTES D’INTÉGRITÉ DE DOMAINE
• Toute comparaison d'attributs n'est acceptée que si ces attributs
sont définis sur le même domaine.
• Le SGBDR doit donc constamment s'assurer de la validité des
valeurs d'un attribut. C'est pourquoi la commande de création de
tables doit préciser, en plus du nom, le type de chaque colonne.
• Par exemple, pour la table Commande, on précisera que
le N°Commande est du type numérique entier et DateCommande
est du type date. Lors de l'insertion d’un tuple dans cette table, le
système s'assurera que les différents champs du tuple satisfont les
contraintes d'intégrité de domaine des attributs précisés lors de la
création de la base. Si les contraintes ne sont pas satisfaites, le tuple
n'est, tout simplement, pas inséré dans la table.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
LES CONTRAINTES D’INTÉGRITÉ DE DOMAINE
• Exemples:
Contrôle des valeurs des attributs:
o par exemple, 4.000 ≤ salaire ≤ 20.000
o liste de couleurs possibles [bleu, rouge, vert, jaune]
Contrôle entre valeurs des attributs:
o QTE-STOCK ≥ QTE-COMMANDE
Contrôle des opérateurs entre attributs
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
INTÉGRITÉ DE LA RELATION
• Unicité de la clé primaire;
• Aucun des attributs de la clé primaire ne doit être NULL
(du fait que la clé primaire identifie de manière unique
les tuples d’une relation).
Tout SGBD relationnel doit vérifier l'unicité et le
caractère défini (NOT NULL) des valeurs de la clé
primaire.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et Contrainte d’Intégrité Référentielle
Le concept de clé étrangère (ou clé extérieure) permet au
SGBDR de maintenir la cohérence des lignes de deux
relations ou des lignes de la même relation.
Définition : une clé étrangère est un attribut appartenant à
une table qui existe aussi en tant que clé primaire dans une
autre table.
Une clé étrangère d’une table référence une clé primaire
d’une autre table.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et Contrainte d’Intégrité Référentielle
Exemple :
Considérons les deux relations :
• Etablissement= (Code Etablissement, Nom, Directeur)
• Employé= (Numéro Employé, Nom employé, Prénom
employé, #Code Etablissement).
L’attribut « Code Etablissement» dans la table
«Etablissement» est la clé primaire. C’est une clé
étrangère dans la table « Employé».
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Clés étrangères et CIR
Code Nom Directeur
Etablissement Etablissement
1 3GTelecom Ahmed Assou Etablissement
2 PSoft Yassine Adil
3 Kanal Said Said
Numéro Nom Prénom Code
Etablissement
1000 Baali Hamadi 2
Employé
1001 El Madani Mounir 2
1002 Louakil Morad 1
1003 Salhi Fadoua 3
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et CIR
C'est ainsi que dans la table « Employé» la clé étrangère « Code
Employé» (faisant référence à la clé primaire de même nom dans la
table « Etablissement») traduit les deux règles de gestion suivantes :
• Un Employé appartient à un Etablissement.
• Un Etablissement est composée de plusieurs Employés.
Les clés étrangères traduisent donc des liens sémantiques entre
plusieurs tables d’une BD.
Chaque clé étrangère est une référence à la clé primaire d’une autre
table.
Dans la plupart des cas, il faut s’assurer que ces références sont
cohérentes (non contradictoires).
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et CIR
Les valeurs de clé étrangère qui apparaissent dans une table
doivent être les mêmes que celles de clé primaire dans la table
connexe.
Exemples dans le monde réel :
• Une commande doit provenir d’un client.
• Un produit commandé doit être quelque chose que nous gardons
normalement en stock.
• Un cours est assuré par un professeur.
• Un étudiant doit avoir une note finale dans une matière de la filière à qui
est inscrit l’étudiant.
Ces contraintes d’intégrité référentielle(CIR) doivent être
supportées par le SGBD d’une façon automatique.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et CIR
Un SGBDR doit appliquer les règles suivantes afin d’assurer les CIR:
Ajout : lors de l’ajout d’une ligne contenant une valeur de clé
étrangère, le SGBD vérifie que cette valeur existe aussi comme
valeur de clé primaire dans la table connexe.
Suppression : lors de la suppression d’une ligne, le SGBD vérifie
qu’aucune clé étrangère dans les tables connexes ne possède la même
valeur que la clé primaire de la ligne supprimée.
Mise à jour (ou modification) : lors de la mise à jour d’une valeur de
clé primaire, le SGBD vérifie qu’aucune clé étrangère dans les tables
connexes n’a la même valeur.
Dans le premier cas, le SGBD rejettera simplement l’ajout d’une
ligne contenant une valeur clé étrangère inconnue.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(LES RÈGLES D'INTÉGRITÉ)
Clés étrangères et CIR
Dans les deux derniers cas, un concepteur de BD conserve
habituellement un certain contrôle sur la façon dont l’application
référentielle est appliquée.
Lorsqu’une ligne contenant une clé primaire est supprimée, le
concepteur peut spécifier au SGBD de supprimer toutes les lignes des
autres tables ayant des clés correspondantes ou de régler toutes les
clés étrangères correspondantes à NULL.
Un choix similaire est possible lors de la modification d’une valeur de
clé primaire.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Les relations entre tables)
Les relations entre tables
Dans une BD, certaines tables sont en relation les unes avec les autres.
Dans les BD relationnelles, les relations entre tables sont
généralement établies par l’intermédiaire de clés primaires et
étrangères.
On répartie les données dans des tables que l’on relie pour réduire la
redondance des données.
Relation « un à un » : une ligne d’une table A est en relation avec
une seule ligne d’une autre table B.
Relation « un à plusieurs » : une ligne d’une table A peut être en
relation avec plusieurs lignes d’une autre table B.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Les relations entre tables)
Les relations entre tables
Exemples de relations entre tables :
• Un recteur dirige une université :
– Relation de type un à un.
• Un propriétaire possède plusieurs voitures, une voiture
appartient à un seul propriétaire :
– Relation de type un à plusieurs.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Les relations entre tables
IDREC NOM PRENOM
1 Benjaloun Wail
2 Touimi Rahma
Table « Recteur »
IDUNIV NOM VILLE IDREC
100 Mohamed V Rabat 1
200 Hassan II Mohamédia 2
Table « Université »
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Les relations entre tables
IDPROP NOM PRENOM
1 Razi Mohamed
2 Nafia Mostafa
Propriétaire
3 Adili Said
MATRICULE MODELE IDPROP
1234 A 1 Peugeot 205 1
7588 A 48 Renault 19 1
25254 D 13 Fiat Uno 2
Voiture
25482 B 6 Toyota 3
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Exercice
– Considérons la table Département et les lignes montrées à la
suite. Expliquez si ces les lignes peuvent être ou non insérées dans
cette table.
N° département NOM LIEU Effectif
20 Ventes Rue FAR 10
10 Marketing Rue Fès 5
10 R&D Bd Oqba 15
Informatique Anfa 13
15 Comptabilité Agdal 23
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
Solution de l’exercice
10 R&D Bd Oqba 15
Informatique Anfa 13
15 Comptabilité Agdal 23
Non : cette ligne ne peut pas être insérée, car elle viole le
principe d’unicité de la clé primaire (la valeur 10 existe déjà
dans la table).
Non : cette ligne ne peut pas être insérée, car elle viole la
contrainte d’intégrité de la clé primaire (la clé primaire ne
peut pas être NULL).
Oui : cette ligne peut être insérée sans problème, car aucune
contrainte n’est violée.
5-déc.-20 37
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Le modèle relationnel permet de structurer les données à stocker
dans la BD.
ex: même situation 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
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Cette notion vise à caractériser des relations qui peuvent être
décomposées sans perte d’information.
Principe:
Soit R(A1, A2, ...., An) un schéma de relation
Soit X et Y des sous ensembles de {A1,A2,...An) On dit que Y
dépend fonctionnellement de X (X→Y) si à chaque valeur de
X correspond une valeur unique de Y
on écrit : X → Y on dit que : la valeur de X détermine la
valeur de Y
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Exemples:
Adresse (nom, ville, codepostal, département)
on a codepostal → ville
mais pas ville → département
Etudiant (nom, prénom, moyenne, âge, enseignant)
(nom, prénom) → moyenne
(nom, prénom) → âge
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(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.
Ex: Information sur les réclamations d'une compagnie d'assurances
Réclamation Numéro Client Adresse
120 Imane Rabat
121 Samir Tanger
122 Imane Rabat
Répétition
de l’adresse
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Notion de dépendance entre attributs
Décomposition en deux relations à l'aide de la clé primaire client
Numéro Client
Client Adresse
120 Imane
Imane Rabat
121 Samir
Samir Tanger
122 Imane
À partir du nom du client, on obtient:
son adresse
le numéro de ses réclamations
On dit qu'il y a dépendance entre l'attribut client et les autres
attributs
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Propriétés des dépendances fonctionnelles (DF)
Réflexivité Si Y est inclus dans X (Y ⊆ X), alors X → Y
Augmentation Si X → Y et W est un ensemble d'attributs, alors
nous pouvons dire WX → WY
Transitivité Si X → Y et Y → Z, alors X → Z
Pseudo transitivité Si X→Y et YW→Z, alors XW→Z
Démonstration: 1. X → Y par augmentation XW → YW
2. XW → YW , YW → Z par transitivité XW → Z
Union Si X→Y et X → Z, alors X →YZ
Démonstration: 1. X→Y par augmentation XZ →YZ
2. XZ→YZ, X→Z par pseudo-transitivité X → YZ
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Dépendance Fonctionnelle)
Propriétés des dépendances fonctionnelles (DF)
Décomposition Si X →YZ, alors X→Y et X → Z
Démonstration:
1. D’après la réflexivité YZ → Y et YZ → Z
2. X → YZ et YZ → Y par transitivité X → Y
3. X → YZ et YZ → Z par transitivité X → Z
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
But de la Normalisation
Une mauvaise répartition des données entre les relations peut
occasionner de graves problèmes lors de l'évolution de la base
La normalisation des relations permet d'éviter ces problèmes,
essentiellement en évitant les redondances
Les problèmes viennent en fait des dépendances fonctionnelles
internes aux relations
La normalisation permet de garantir que les schéma relationnel
est un bon schéma, i.e., a de bonnes propriétés.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Exemple
Une entreprise de vente de bateaux qui souhaite constituer un système
d ’information relatif à leur activité
– Achats(IdClient, NomClient, PrénomClient, AdresseClient,
ImmatriculationBateau, ModèleBateau, LongueurBateau, DateAchat,
MontantAchat)
Problèmes potentiels
– redondance de données : une personne qui achète plusieurs bateaux
– incohérence suite à une Mise à jour : une personne qui change d’adresse
– anomalies lors d’insertion/suppression :
• un client potentiel ne peut être enregistré dans la BD s’il n’a pas encore
acheté un bateau
• lorsqu’un client vend son bateau, il est supprimé du système
d’information.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
• Normaliser un schéma relationnel c'est remplacer chaque
relation du schéma par des relations qui sont dans la
forme normale voulue
• Le schéma est équivalent si l'union des attributs communs
des relations obtenues redonne la relation de départ
• On extrait les DF internes qui posent des problèmes, en
les transférant dans de nouvelles relations
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Exemple:
Soit donné la relation FILM
FILM(titre, année, durée, type, studio, acteur)
Comment éviter des tuples identifiés par {titre,année} avec
différents durées, types ou studios?
On sépare la relation en FILM1 et FILM2
FILM1(titre, année, durée, type, studio)
FILM2(titre, année, acteur)
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Degrés de Normalisation: Il existe plusieurs degrés de normalisation; de la
1ère forme normale à la 5ème
• 1NF → première forme normale
• 2NF → deuxième forme normale
• 3NF → troisième forme normale (« minimum »)
• BCNF → forme normale de Boyce-Codd
• 4NF → quatrième forme normale
• 5NF → cinquième forme normale
Plus le degré est grand, moins on risquera de rencontrer des anomalies lors
des mises à jour des données
1ère, 2ème, 3ème NF sont largement suffisantes, Il faut donc toujours faire des
schémas au moins en 3NF
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Comment normaliser ?
Décomposition des relations jusqu’à ce que toutes respectent les 3 Formes
Normales suivantes :
Première Forme Normale
Une relation est dite en Première Forme Normale ssi tous ses
attributs sont atomiques ;
aucun des attributs ne doit être une relation entre « sous-attributs »
pas de colonnes dans une colonne
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Première Forme Normale(Exemples)
Toutes les attributs sont atomiques (ne sont pas multivalués)
• Livre(codeISBN, titre, auteurs)
peut être décomposée en 2 vraies relations :
• Livre(codeISBN, titre)
• Auteurs(codeISBN, auteur)
Il n'existe pas d'attributs répétitifs
• Étudiant (numéro, nom, adresse, cours1, cours2, cours3)
peut être décomposée en 2 vraies relations :
• Étudiant(numéro, nom, adresse)
• Cours(numéro, cours)
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Seconde Forme Normale
Une relation est en 2NF:
Si elle est en Première Forme Normale
Si tous les attributs n’appartenant pas à la clé sont en
dépendance fonctionnelle totale avec la clé
dès qu’un attribut non-clé dépend d’une partie de la clé, la
relation n’est pas en 2FN.
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Seconde Forme Normale (Exemple):
Prêt_livre (numcli, numlivre, nomcli, adrcli, titre_livre, dateprêt)
DF
• numcli nomcli, adrcli
• num_livre titre_livre
• numcli, numlivre dateprêt
Prêt_livre n ’est pas en 2 FN car
• nomcli dépend uniquement de numcli et pas de numcli et de
numlivre
décomposition à l’aide des DF
• client (numcli, nomcli,adrcli)
• Livre (numlivre, titre_livre)
• prêt (numcli, numlivre, dateprêt)
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Troisième Forme Normale
Une relation est en 3NF ssi :
• elle est en Seconde Forme Normale (2FN)
• Aucun de ses attributs non-clés dépend d’un autre attribut
non-clé ;
il n ’existe pas de DF entre 2 attributs non-clés
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Troisième Forme Normale(Exemple)
• ville(num_ville, nom_ville, num_dept, nom_dept)
DF
• num_ville nom_ville, num_dept, nom_dept
• num_dept nom_dept
ville n’est pas en 3 FN car
• nom_dept dépend de num_dept
décomposition à l’aide des DF
• ville(num_ville, nom_ville, num_dept)
• dept(num_dept, nom_dept)
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Exercices(ex3-1)
On a les données suivantes sur des élèves avec le DFs:
Matricule, Nom, Age, Club, Salle (attributs)
Matricule → Nom , AGE
Matricule → Club
Club → Salle
1. Que signifie chaque DFs?
2. Mettre ces informations dans un ensemble de schémas de
relations en 3FN
Pr S.MOQQADDEM AU: 2020/2021
UNIVERSITE IBN TOFAIL ENCGK
III. Modèle Relationnel
III.2. Concepts de base(Normalisation)
Exercices(ex3-2)
En considérant la relation suivante, qui concerne les employés
d’une société implantée sur plusieurs bâtiments?
EMPLOYES (NumE, Nom, Salaire, Département, Bâtiment)
Sachant qu’un employé travaille dans un département donné, et
qu’aucun département ne possède des locaux dans plusieurs
bâtiments.
1. Quelle est la clé de cette relation ?
2. En quelle forme normale elle est ?
3. La mettre en 3FN le cas échéant..
NB: Déterminer d'abord les DFs.
Pr S.MOQQADDEM AU: 2020/2021
