Page 1 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODE DE CONCEPTION D ’UN SCHEMA
RELATIONNEL DE C. DATE & T. CODD
Méthode pratique (utilisée dans le cours « Administrateur BD » chez IBM)
Version Anglaise de cette méthode présentée dans :
« RELATION DB : Selected writing »,
C. DATE, Addison Wesley, 1986
Version française dans :
« COMPRENDRE et CONCEVOIR des BD RELATIONNELLES »
S. MIRANDA, Editest, 1988 et livre DUNOD 2001
Professeur Serge MIRANDA
Université de Nice Sophia Antipolis
Directeur du Dess MBDS de l’ UNSA
Page 2 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
APPREHENSION DE LA REALITE ?
1) LOI D ’INCERTITUDE D ’HEIZENBERG (1927) :
« Les concepts humains ne pourront pas décrire la réalité
(l’observateur déforme) ».
2) THEORIE D ’INDECIDABILITE DE GODEL (1931) :
« Aucun système ne permet de s’auto-décrire (d’où niveau META) »
Page 3 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
PLAN
I INTRODUCTION
II L ’APPROCHE SYNTHETIQUE :
Classification des Entités et Définition des « Relations »
III L ’APPROCHE ANALYTIQUE
Normalisation des relations
IV EXEMPLE
Page 4 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
INTRODUCTION
« L ’œuvre d ’art nous apprend que nous n ’avons pas vu ce que nous
voyons »
Paul Valéry
« Pourquoi faire simple quand c ’est si facile de tout compliquer »
J.L. Godard
Page 5 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LA MODELISATION DES DONNEES
UNIVERS REEL
MODELE DE DONNEES
SCHEMA
DE
DONNEES
ABSTRAIT MANIPULABLE
CONCRET INACCESSIBLE
Page 6 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
MODELE DE DONNEES ?
MODELE
DE
DONNEES
STRUCTRUES
OPERATEURS
REGLES
D ’INTEGRITE
+ (THEORIE FORMELLE)
Page 7 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
MODELE DE DONNEES ?
)
1) Modèles « Informatiques » - 1968- 1988 (Ex : IMS, DBTG,..)
2) Modèles « Mathématiques » –1980 – 2000 (Ex : Modèle Relationnel, SQL2)
3) Modèles « Sémantiques » –1990- 2010 (Ex : Modèles Objet, OR, SQL3)
Page 8 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
TYPES PRINCIPAUX D ’ABSTRACTION
(Modèles de données « sémantiques »)
AGREGATION : [« PART OF »]
PILOTE = {PL#, PLNOM, ADR}
GENERALISATION : [« IS - A »]
employé
pilote hôtesse mécanicien
ASSOCIATION/COUVERTURE : [« SET OF »]
équipage
pilote hôtesse
Page 9 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
TYPE D ’ABSTRACTIONS ET
MODELE RELATIONNEL
La seul abstraction représentée c ’est l ’agrégation « simple »
(types simples d ’attributs)
Les autres types d ’abstractions :
généralisation (IS-A)
association/couverture (Set-of)
récursivité
version de (Version of)
doivent être représentées en fonction de l ’agrégation simple.
Seul le produit cartésien intervient dans la définition d ’une relation
(pas l ’ensemble des parties)
Modèle
Relationnel
de données
Agrégation
Simple
Typage statique (et réduit)
Page 10 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODOLOGIE DE CONCEPTION
D ’UN SCHEMA RELATIONNEL
2 phases conceptuelles
Définition d ’un schéma conceptuel avec tous les types
d ’abstraction
Définition du schéma relationnel avec seulement
l ’agrégation simple
Page 11 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODE DE CONCEPTION D ’UNE BASE
DE DONNEES (RELATIONNELLE) DANS LA PRATIQUE
UNIVERS REEL
MODELE DE DONNEES
CONCEPTUEL
SCHEMA
CONCEPTUEL
MODELE DE DONNEES
LOGIQUE
SCHEMA
LOGIQUE
Modèle entité association étendu
NIAM
Modèle RM-T de Codd
Avec le maximum d ’informations sémantiques :
(agrégation, généralisation, association, récursion)
Modèle orientés « réalisation » :
Hiérarchique, Réseau, Relationnel
Page 12 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODE DE CONCEPTION D ’UNE BASE
DE DONNEES RELATIONNELLE :
la méthode « CODD & DATE »
UNIVERS REEL
SCHEMA « RELATIONNEL »
SCHEMA RELATIONNEL
NORMALISE
APPROCHE SYNTHETIQUE
Classification des « Entités » composant l ’univers à modéliser
et Génération de « Relations »
APPROCHE ANALYTIQUE
Définition de « bonnes » relations au sens de CODD
Page 13 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
OBJECTIF D ’UNE METHODE DE
CONCEPTION RELATIONNELLE
Faire cohabiter plusieurs perceptions de l ’univers réel
Indiquer simplement quand on doit créer les relations de « base »
avec leurs clés primaires
Indiquer simplement où placer les clés étrangères dans les relations
de base
Page 14 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODE DE CODD AND DATE DE
CONCEPTION D ’UNE SCHEMA
RELATIONNEL
1ère phase : Définition des relations de base/CP et des clés étrangères
définition et classification des entités
définition des « relations de base » associées avec les :
clés primaires
clés étrangères
identification du schéma relationnel
2ème phase : Définition des « bonnes relations »
pour les opérations
de mise à jour normalisation
d ’interrogation dénormalisation
Page 15 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
I L ’APPROCHE SYNTETIQUE
de la méthode de Codd&Dte
I - CONCEPTS DE BASE
- Notion d ’Entité et de Clé
- Classification des Liens entre entités
- Classification des Propriétés
- Classification des Entités
II - METHODOLOGIE DE CONCEPTION
- Présentation générale du processus de conception
- Analyse des 4 étapes de la démarche
- Conseils méthodologiques
III - CAS PARTICULIERS
- Représentation du Lien Sous-Type (Généralisation)
- Interblocage d ’Initialisation
Page 16 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
« ROND » ?
FORME ? ETAT ?
BALLON ? INDIVIDU ?
Page 17 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CONCEPTS DE BASE
1 m 70 Rouge Bleu Rond DONNEE
TAILLE COULEUR FORME PROPRIETE
PERSONNE BALLON ENTITE
VOITURE
PROPRIETAIRE
LIEN
Page 18 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CONCEPT STRUCTURE DE BASE
ENTITE 1
CLE (primaire)
« Tout ce qui est
DIFFERENTIABLE »
« Any distinguishable object »
« OBJET » (début définition dans composante structurelle)
(BD-O2)
Identifiant de l ’Entité
Note : La référence à une entité se fera par sa clé
Une seule structure conceptuelle RELATION
1 = Ensemble (SET) de « propriétés »
Ensemble (SET) de « Valeur/Données »
Paradigme VALEUR
Page 19 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LES LIENS ?
« All things are connected »
Seattle (chef indien) cité par Ph. Labro
(« Un été Américain »)
Liens entre structures de données = liens entre ENSEMBLES
Page 20 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CLASSIFICATION DES LIENS
LIENS FONCTIONNELS N : 1 « DETERMINATION »
Soient A et B, deux propriété ou Groupes de Propriétés (Entités) :
DETERMINANT DETERMINE
A B
N : 1
1 Au plus 1
0.1 ou N 1
Exemples :
NUMERO_VOLN : 1 VILLE_DEPART
VILLE_ARRIVEE
NUMERO_PILOTEN : 1 NOM_PILOTE
ADRESSE_PILOTE
HOMMEN : 1 FEMME
MARIAGE (polyandrie)
Page 21 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS N : 1
Page 22 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS FONCTIONNELS N : 1
« DETERMINATION »
REGLES DE GENERICITE (AMSTRONG)
Réflexivité
Transitivité
Augmentation du Déterminant
A
A B CN : 1
N : 1
N : 1
D
AB
Numero_Vol Ville_Depart
Numero_Vol Ville_Depart
Heure_Depart
Page 23 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS 1 : N
Page 24 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS HIERARCHIQUES 1 : N(inverse des liens N : 1)
Soient A et B, deux Propriétés ou Groupes de Propriétés (Entités) :
A B
1:N
1 0,1 ou N
Au plus 1 1
Exemples :
PILOTE1:N
VOL
HOMME1:N FEMME
MARIAGE (polygamie)
Règles :
Règles de généricité
Règles de réciprocité
A B
1:N
N : 1
Page 25 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS N : M
Page 26 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIENS MAILLES N : M
Soient A et B, deux Propriétés ou Groupes de Propriétés (Entités) :
A B
N : M
1 0,1 ou M
0,1 ou N 1
Exemples :
PILOTEN : M
AVION
HOMMEN : M
FEMME
MARIAGE (mariage de groupe)
Règles :
Règles de généricité
Règles de symétrie
A B
N : M
1:NC1 : M
Page 27 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia AntipolisCLASSIFICATION DES ENTITES
(jargon secondaire)
« STATIQUES »Entités indépendantes
ex : PILOTE, AVION, VOL, DEPARTEMENT
« DYNAMIQUES » Entités dépendantes d ’autres entités (liens MULTIVALUES)
« ASSOCIATIVES » N : M
Entités représentant un lien N : M
ex : VOL représente le lien
PILOTE AVIONN : M
« DESCRIPTIVES »
Entités dont la seule fonction est de décrire une autre entité
(lien 1 : N avec contrainte d ’existence
ex : PIL-ENFANT est « descriptive » de PILOTE
PILOTE PIL-ENFANT1 : N
PILOTE VOL1 : N
AVION VOL1 : N
,
1 : N
Page 28 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia AntipolisCLASSIFICATION DES ENTITES
« DETERMINANTES » N : 1 (liens MONOVALUES)
Quel que soit son type, une entité peut DERMINER (lien N : 1) une autre entité
Ex :
PILOTE DEPARTEMENT
N:1
VOL PILOTE
N:1
VOL AVION
N:1
,
Page 29 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia AntipolisCLASSIFICATION DES ENTITES
Type d ’entité Exemple Existence perçue
indépendante
Type de
lien
Statique PILOTE
AVION
EMPLOYE
DEPARTEMENT
VOL
OUI
-
Dynamique NON -
associative VOL (PILOTE, AVION) NON N : M
descriptive PIL-ENFANT
(la seule raison d ’être de
PIL-ENFANT est de décrire les
enfants d ’un pilote
VOL
NON
1 : N
Déterminante Un EMPLOYE détermine un
DEPARTEMENT
Un VOL détermine un
PILOTE et un AVION
NON
N : 1
Une même entité VOL peut être perçue de 3 manière indépendantes :
- statique (et indépendante), - associative, - descriptive
Page 30 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
TERMINOLOGIE SUR LA CLASSIFICATION
DES ENTITES
FR (MIRA88 et MIRA2001 ) GB (DATE86)
Statique Kernel
Associative (N : M) Association
Descriptive (1 : N) Characteristics
Déterminante (N : 1) Referencing
RM-T de
CODD (80)
Page 31 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
REPRESENTATION GRAPHIQUE DE
WINSBERG (1986)
ENTITE STATIQUE PILOTE
ENFANT
ENTITE DESCRIPTIVE (Lien 1:N)(N) (1)
Clé étrangère au niveau de la plume de la flèche
ENTITE ASSOCIATIVE (Lien N:M)
AVION
VOL
PILOTE
DEPARTEMENT
DETERMINATION (LIEN N : 1)
(N) (1)
Page 32 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
REPRESENTATION GRAPHIQUE
SIMPLIFIEE
Entité traduite par une relation / table de base
Lien N : 1
notation fonctionnelle (rien de plus simple
et cohérent) indiquant la présence d ’une clé
étrangère au niveau de la plume.
(Possibilité d ’étiqueter une flèche par un prédicat bidirectionnel comme avec
NIAM dans le cas de l ’existence de plusieurs liens entre les mêmes entités).
Page 33 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
REPRESENTATION GRAPHIQUE (1986)
EXEMPLE : AIRBASE
DEPARTEMENT (D#)
PILOTE (PL#)
PILENFANT
EMPLOYE (E#)
HOTESSE
STEWARD
AVION (AV#)
VOL
PILFORM
(E#)
(E#)Généralisation
(D#, E#)
(PL#)
(PL#, AV#)
(PL#)
Agrégation
Entité (correspondant à une relation de base du schéma)
La CP est indiquée dans le rectangle.La CE est indiquée en dehors
Lien fonctionnel N:1 (présence d ’une clé étrangère au
niveau de la plume)
Page 34 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
PRESENTATION
Le processus consiste à remplir progressivement un tableau décrivant les entités et leurs
propriétés au fur et à mesure de leur définition.
Le schéma de Winsberg peut être élaboré parallèlement.
PROPRIETESENTITES
IDENTIFICATION
clé primaire
REFRENCE
clé étrangèresCARACTERISTIQUES
Page 35 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
METHODOLOGIE DE CONCEPTION
Démarche Descendante (« TOP-DOWN ») et Itérative
4 Etapes :
ETAPE 1 : Définition Des Entités Statiques
ETAPE 2 : Représentation des Liens multivalués entre Entités
- Liens N : M (Associations)
- Liens 1 : N (Descriptions)
ETAPE 3 :Lien N : 1 (pas de création d ’Entité ; seul cas mais rajout
de clé étrangère)
ETAPE 4 : Rattachement des propriétés aux entités.
Itération du processus si toutes les information ne sont pas
représentées dans le schéma (reprise étape 1)
Page 36 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 1 : ENTITES STATIQUES
a - IDENTIFICATION des Entités statiques et de leurs clés primaires
b - REPRESENTATION « relationnelle » de ces entités :
Relation de Base sans Clé étrangère
Ex : PILOTE (P PL#)
AVION (A AV#)
DEPARTEMENT (D DEP#)
VOL (VOL#)
Page 37 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 2 : LIENS MULTIVALUES INTER
ENTITES
Identification et Représentation des Liens multivalués (N:M et 1:N) entre Entités
1 - ASSOCIATIONS Liens N : M
Ex : PILOTE AVIONN:M
FOURNISSEUR DEPARTEMENTN:M
Création d ’une relation de base avec des clés étrangères identifiant les entités liées
Ex : VOL ( VOL#, V-PL#, V-AV# )
FOURN_PROD ( PROD#, FOUR# )
2- DESCRIPTION Lien 1 : N
Ex : PILOTE VOL1:N
Création d ’une relation de base ; rajout de la clé primaire de l ’entité de départ
Comme clé étrangère dans l ’entité d ’arrivée
ex : rajout de PL# dans VOL (VOL#, V-PL#)
Page 38 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 3 : LIENS MONOVALUES (N:1)
INTER-ENTITES
DETERMINATION Lien N : 1
Ex : VOL PILOTEN:1
PILOTE DEPARTEMENTN:1
Pas de création de nouvelle relation (seul cas).
Rajout de la clé primaire de l ’entité déterminée comme clé étrangère
dans l ’entité déterminante
Ex :
Rajout de DEP# dans PILOTE (P PL# , P-DEP#)
RAJOUT DE PL# DANS VOL (VOL#, V-PL#)
LIENS PRESENCE DE CLES
ETRANGERES +++
Page 39 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CLASSIFICATION DES PROPRIETES
3 types de PROPRIETES :
IDENTIFICATION Clé Primaire
Ex : Numéro-Vol dans l ’entité VOL
REFERENCE Clé étrangère
Ex : Numéro-Pilote dans l ’entité VOL
CARACTERISTIQUE
Ex : Ville-Départ, Ville-Arrivée dans VOL
Page 40 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 4 : PROPRIETES
RATTACHEMENT DES PROPRIETES AUX ENTITES
Une propriété peut-être
- Monovaluée ou Multivaluée
et
- Immédiate (entité de rattachement. Connue)
ou Non Immédiate (entité de rattachement inconnue)
4 CAS :
Monovaluée/Immédiate
Attribut de la relation concernée
Ex : PLNOM, P-VILLE dans PILOTE
Monovaluée/Non Immédiate
Création d ’une nouvelle relation statique (cf.Normalisation 3NF)
Ex : CAP dans AVION
A_AV# A_TYPAV CAP N:1 N:1
Création d ’une entité statique CAPACITE ( C-TYPAV, CAP)
A-TYPAV dans AVION devient Clé étrangère
Page 41 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 4 : PROPRIETES
Multivaluée/Immédiate
Création d ’une entité descriptive « toute clé » ou définition de plusieurs attributs
(cf. Normalisation INF)
Multivaluée/Non Immédiate
Création d ’une entité DESCRIPTIVE représentée par une relation de base avec
la clé étrangère de l ’entité décrite.
Ex : AGE des enfants d ’un PILOTE
On crée l ’entité PILENFANT (E-PL#, PRENOM, AGE)
FORMATION et DUREE de formation d ’un PILOTE
On crée l ’entité
PILFORM (F-PL#, F-TYPAV, FORMAT, DUREE)
Note : pour chaque propriété il faut définir son domaine (typage sémantique)
Ex : Auteurs d ’un LIVRE
LIVRE (LIV#, ………, AUT#1, AUT#2)
ou LIV-AUTEUR (LIV#, AUT#)
Page 42 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ETAPE 4 : TEST
SI toutes les informations relatives à l ’univers à modéliser sont représentées
dans le schéma
ALORS passage à l ’approche analytique
SINON nouvelle itération (reprise à l ’étape 1)
Page 43 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLE : AIRBASE
PROPRIETESENTITES
IDENTIFICATION
clé primaire
REFRENCE
clé étrangèresCARACTERISTIQUES
PILOTE P-PL# DEP# PLNOM, PL-VILLE
AVION AV-AV# A-TYPAV, CAP
AV-VILLE
DEPARTEMENT D-DEP# DEPNOM
VOL VOL# V-PL#, V-AV# VD-VILLE,
VA-VILLE
HD-HEURE,
HA-HEURE
PILENFANT ENF#, E-PL# E-EPL# PRENOM, AGE
PILEFORM P-TYPAV, F-PL# DUREE, FORMAT
F-PL#
Page 44 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
NOTE : IDENTIFICATION DU SCHEMA
« QU ’y a-t-il en un nom ?
Ce que nous nommons ROSE
sous un tout autre nom
sentirait aussi bon …. ? »
W. Shakespeare (Roméo et Juliette)
Page 45 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
DEFINITION DES CLES PRIMAIRE
R1 : Eviter si possible les clés primaire multi-attributs pour les
raisons suivantes :
- problèmes d ’utilisation : jointure multi-attribut
- problème de définition (pour les relations statiques) si le concept de « domaine primaire »
existe ;
- problème de visualisation pour éviter les redondances logiques
(ex : ETUDIANT (E #, C#, C-OFFERT, NOTE, …….)
Intérêt pour l ’administrateur d ’introduire des clés primaires mono-attributs surtout
pour les relations statiques.
Page 46 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
DEFINITION DES DOMAINES
R2 : Lorsque la comparaison des valeurs de deux attributs a un sens, définir un domaine
commun pour ces attributs (union des valeurs possibles)
définition des domaines par l ’administrateur au fur et à mesure de la conception du
schéma relationnel.
* (choix crucial pour un contrôle sémantique des opérateurs)
Note : toute clé étrangère doit avoir le même domaine que la clé primaire associée.
R3 : Règle d ’identification des attributs et des domaines
2 cas :
1) s ’il n ’y a pas dans la même relation deux attributs définis sur le même
domaine :
Nom d ’attribut = nom de domaine
2) si plusieurs attributs de la même relation sont définis sur le même domaine :
Nom d ’attribut = nom de domaine préfixé ou suffixé
Note : ce programme d ’identification a un double intérêt :
1) avertir l’ utilisateur
2) permettre d ’introduire des domaines fictifs à SQL1 et SQL2
R4 : Noms d ’attribut … unique avec un langage relationnel fermé (ce n ’est pas le cas
de SQL)
Page 47 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
SCHEMA RELATIONNEL AVEC NOTATION
CODD & DATE DES ATTRIBUTS /
DOMAINES
EXEMPLE :
AVION (AV#, AVNOM, CAP, LOC-VILLE)
PILOTE (PL#, PLNOM, SAL, ADR-VILLE)
VOL (VOL#, V-PL#, V-AV#, VD-VILLE, VA-VILLE, HD-HEURE,
HA-HEURE)
Page 48 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
II L ’APPROCHE ANALYTIQUE
de la méthode de Codd&Date
I - PRINCIPE DU PROCESSUS DE NORMALISATION
II - LA PREMIERE FORME NORMALE (1NF)
III - LES FORMES NORMALES 2NF, 3NF, BCNF, 4NF, 5NF
Page 49 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
DEFINITION DU SCHEMA RELATIONNEL
NORMALISE
2IEME PHASE
DOUBLE OBJECTIF :
Chaque relation possède une clé primaire qui représente l ’identifiant
d ’un certain type d ’entité ;
Chaque relation comprend plusieurs attributs MONOV ALUES qui représentent
des propriétés de l ’entité identifiée par la clé primaire (une relation contient
donc de l ’information sur un seul type d ’entité).
MISE EN 1ière FORME NORMALE EN SUPPRIMANT LES ATTRIBUTS
MULTIVALUES
ATTRIBUTS « MULTIVALUES » :
cardinalité inconnue : traitement comme entité descriptive (lien 1 : N) en
créant nouvelle relation : exemple attribut multivalué AUTEUR de l ’entité :
LIVRE (L#, TITRE, EDITEUR)
LIV-AUTEUR (L#, AUTEUR)
cardinalité connue : création nouvel attribut
exemple : capacité minimum et maximum d ’un avion
AVION (AV#, TYPE, CAP-MIN, CAP-MAX, LOC)
Page 50 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ATTRIBUT MULTIVALUE AVEC
CARDINALITE VARIABLE
Considérons la relation LIVRE ayant les attributs AUTEUR et MOTCLE multivalués
LIVRE L# TITRE EDITEUR ANNEE AUTEUR MOT-CLE
1 BD EN BD JESEPA 1988 GO HUMOUR
MILO BD
VULGARISATION
LIVRE L# TITRE EDITEUR ANNEE
1 BD EN BD JESEPA 1988
LIV-AUTEUR L# AUTEUR
1 GO
1 MILO
L# MOT-CLE
1 HUMOUR
1 BD
1 VULGARISATION
LIV-MOTCLE
« Simple mais naturel ! »
Page 51 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CAS PARTICULIER D ’UN ATTRIBUT
MULTIVALUE
LE VECTEUR
Deux représentations sont possibles :
- orientée tuple (cardinalité fixe)
- orientée attribut
Prenons l ’exemple des salaires mensuels d ’un pilote
1) Représentation orientée tuple
PILOTE PL# PLNOM SAL-JAN SAL-FEV SAL-MARS … SAL-DEC
1 Pierre 20 KF 20 KF 21 KF 22 KF
2 Serge 17 KF 17 KF 18 KF 19 KF
… … … … … …
Page 52 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CAS PARTICULIER D ’UN ATTRIBUT
MULTIVALUE
LE VECTEUR
2) Représentation orientée attribut ou création de nouvelles relations :
2.1 (sans création de nouvelle relation)
PILOTE PL# PLNOM MOIS SALAIRE
1 Pierre Jan 20 KF
1 Pierre Fev 20 KF
1 Pierre Mars 21 KF
… …
1 Pierre Dec 22 KF
2 Serge Jan 17 KF
2 Serge Fev 17 KF
2 Serge Mars 18 KF
… … …
2 Serge Dec 19 KF
Page 53 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
CAS PARTICULIER D ’UN ATTRIBUT
MULTIVALUE
LE VECTEUR
2) Représentation orientée attribut ou création d ’une nouvelle relation :
2.2 (avec création d ’une nouvelle relation)
PILOTE PL# PLNOM ADR ...
1 Pierre Paris
2 Serge Nice
PIL-SAL PL# MOIS SALAIRE
1 Jan 20 KF
1 Fev 20 KF
1 Mars 21 KF
…
1 Dec 22 KF
2 Jan 17 KF
2 Fev 17 KF
2 Mars 18 KF
… ...
2 Dec 19 KF
Page 54 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
FORME 2 NF, 3NF,
BCNF
LIENS N : 1
(monovalués)
Liens monovalués et formes normales
Page 55 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
ILLUSTRATION DU BESOIN DE
NORMALISATION SUR UN EXEMPLE
Considérons la relation suivante AVION dont la clé primaire est AV# :
AVION AV# AVNOM CAP LOC
100 AIRB 250 TOUL
101 AIRB 250 TOUL
102 AIRB 250 PARIS
103 CAR 100 TOUL
104 B707 150 PARIS
105 B707 150 PARIS
Supposons que l ’on ait la contrainte C1 suivante dans la compagnie aérienne :
« tous les avions de même nom ont la même capacité ».
Cette contrainte est traduite par le lien N : 1 appelé aussi « dépendance fonctionnelle »
suivant :
AVNOM CAP
Notons que deux avions de noms différent peuvent avoir la même capacité, du fait
de l ’existence de cette dépendance fonctionnelle, on a :
- une redondance logique
- des anomalies de stockage
- le problème de reconnexion
N:1
Page 56 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
PROBLEME DE RECONNEXION
Considérons les 2 projections suivantes :
AVION1 AV# AVNOM
100 AIRB101 AIRB102 AIRB103 CAR104 B707105 B707
AVION2 AVNOM CAP LOC
AIRB 250 TOULAIRB 250 PARISCAR 100 TOULB707 150 PARIS…
AVION3 AV# AVNOM CAP LOC
100 AIRB 250 TOUL
100 AIRB 250 PARIS
101 AIRB 250 TOUL
101 AIRB 250 PARIS
... ... ... ...
AVION3 = AVION1 [AVNOM = AVNOM] AVION2 = AVION
La normalisation a pour but :
- de supprimer la redondance logique,
- d ’éviter les anomalies de stockage,
- de résoudre le problème de reconnexions.
Page 57 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
DEPENDANCE FONCTIONNELLE (DF)(nom d ’un lien N : 1)
Soit X, Y des groupes d ’attributs de la relation R, nous avons une dépendance
fonctionnelle de X dans Y notée : f : X ------Y ou X ------ > Y
si t1 (X) = t2(X) alors t1(Y) = t2(Y), avec ti(A) sous-tuple de R
correspondant aux attributs A.
Pour chaque valeur de X il existe au plus (0 ou 1) une
valeur de Y associée (plusieurs valeurs de X peuvent correspondre à la même valeur
de X) ; X est appelé « déterminant » et Y « déterminé ».
EXEMPLE : Les dépendances fonctionnelles traduisent les contraintes existant dans
l ’univers réel :
« un pilote habite dans une seule ville » : PL# ADR
« un pilote n ’a qu ’un seul nom » : PL# ------- PLNOM
En conséquence PL# est clé candidate (et primaire) dans la relation PILOTE1
(PL#, PLNOM, ADR)
Nous rappelons que la clé primaire est soulignée dans l ’énoncé des attributs de la
relation.
N : 1
Page 58 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
THEOREME DE DECOMPOSITION
Ce théorème de décomposition (sans perte d ’information) est souvent appelé théorème
de décomposition de CASEY et DELOBEL
Soient R(U) et (X, Y, Z) une partition de U avec X Y
Alors R(U) = R(U1)(X = X)R(U2) avec
U1 = X Y (union des attributs intervenant dans la DF)
U2 = X Z (union du déterminant, attribut de la jointure, avec les autres attributs de U)
EXEMPLE : AVION(AV#, AVNOM, CAP, LOC) avec AV## clé primaire et
AVNOM CAP
Le théorème de décomposition permet d ’avoir deux relations
DF
DF
AVION1 AVNOM CAP
AIRB 250
CAR 100
B707 150
AVION2 AV# AVNOM LOC
100 AIRB TOUL
101 AIRB TOUL
102 AIRB PARIS
103 CAR TOUL
104 B707 PARIS
105 B707 PARIS
Page 59 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
SURVOL DES FORMES NORMALES
2NF, 3NF, BCNF Liens N:1 ou 1:1
Liens 1:N ou N:M
4NF (multivalués)
INDEPENDANTS
Liens 1:N ou N:M
5NF (multivalués)
DEPENDANTS avec contrainte
de symétrie
Principe de localité
un seul concept sémantique dans une relation
sinon on décompose
Page 60 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
Définition 3NF
Définition de Sharman
« Une Relation est en 3NF si tout déterminant d’un lien N:1 est clé primaire »
Page 61 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLE DE LIENS MULTIVALUES
INDEPENDANTS
un pilote conduit plusieurs avions (N:M)
un pilote assure plusieurs trajets (VD, VA) (N:M)
Ces liens sont indépendants
TRAJET PILOTE AVION
N M N M
TRAJET PILOTE
VOL1
AVION
VOL1 (PL#, AV#, VD, VA) en 3NF
contient 2 liens N:M indépendants
Représentation graphique de Winsberg associé à VOL1
Page 62 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
REPRESENTATIONS POSSIBLES
DISJOINTES
VOL1 PL## AV## VD VA
10 100 @ @
10 101 @ @
10 102 @ @
10 @ Nice Toulouse
10 @ Paris Nice
PRODUIT
CARTESIEN
[« indépendance »]
VOL1 PL## AV## VD VA
10 100 Paris Nice
10 100 Nice Toulouse
10 101 Paris Nice
10 101 Nice Toulouse
10 102 Paris Nice
10 102 Nice Toulouse
Page 63 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
REPRESENTATIONS POSSIBLES
ALEATOIRE
VOL1 PL## AV## VD VA
10 100 Paris Nice
10 101 @ @
10 102 Nice Toulouse
10 @ Paris Nice
10 102 @ @
COMPRESSE
VOL1 PL## AV## VD VA
10 100 Paris Nice
10 101 Nice Toulouse
10 102 @ @
Page 64 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
DECOMPOSITION POUR ISOLER LES
LIENS MULTIVALUES INDEPENDANTS
VOL11 PL## AV##
10 100
10 101
10 102
VOL12 PL## VD VA
10 Paris Nice
10 Nice Toulouse
4NF
3NF
+
pas de lien multivalué
indépendant
Page 65 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIEN MULTIVALUE DEPENDANT
EXEMPLE : un pilote conduit des avions sur différents trajets ….
PILOTE
AVION TRAJET
N
M P
VOL 1+
Contrainte de symétrie
Le pilote P conduit l ’avions A
(et) si l ’avion A est utilisé sur le trajet T
(et) le trajet T est assuré par le pilote P
SI
Alors
le pilote P conduit l ’avion A sur le trajet T
VOL2
Page 66 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
LIEN MULTIVALUE DEPENDANT
VOL2 PL## AV## VD VA
11 102 Madrid Paris
10 100 Paris Madrid
10 100 Madrid Paris
10 101 Madrid Paris
10 101 ParisMadrid
VOL21 PL## AV##
11 102
10 100
10 101
VOL23 AV## VD VA
100 Madrid Paris
100 Paris Madrid
101 Madrid Paris
101 Paris Madrid
102 Madrid Paris
VOL23 PL## VD VA
11 Madrid Paris
10 Paris Madrid
10 Madrid Paris
Isolement de chaque
lien inter-dépendant
PROJECT
Exemple : anomalie insertion dans VOL2 : (10, 100, Nice, Toulouse)
insertion (10, 101, Nice, Toulouse)
Page 67 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
NORMALISATION
5NF :
4NF + isolement des liens multivalués symétriques interdépendants)
Opérateur algébrique pour :
-décomposition
-recomposition
Dépendance
- JOIN
- PROJET
-UNION
- SELECT
- DIVISION
- PROJECT
fonctionnelle
2NF
3NF
BCNF
(3,3)NF
-
multivaluées 4NF (3,3)NF -
De jointure 5NF - -
Iso-dépendance - - B-NF
Page 68 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLES ET CAS PARTICULIERS
EXEMPLE 1
PILOTE
PILENFANT
1
N
PILOTE (PL#, PILNOM, ADR, SAL)
PILENFANT (PL#, E#, PILNOM, ADR, SAL)
Un pilote comprend les propriété suivantes
- monovaluées : PL#, PLNOM, ADR, SAL
- multivaluées : E#, AGE, PRENOM de ses enfants
Page 69 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLES ET CAS PARTICULIERS
EXEMPLE 2
COURS
COURS -
OFFERT
1
N
ETUDIANT
1
N
COURS (C##, INTITULE)
COURS-OFFERT (C##, OFF#, DATE, LOCA)
ETUDIANT (C##, OFF##, E##, DIPLÔME)
Chaque cours comprend un numéro, un intitulé et un ensemble de valeurs indiquant la
date et le lieu où le cours est donné.
Chaque cours donné comprend une date, un lieu et un ensemble d ’étudiant suivant
le cours.
Chaque étudiant comprend un numéro et le nom du diplôme préparé.
Page 70 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLES et CAS PARTICULIERS
EXEMPLE 3
Schéma suivant ?
VOL1 (VOL##, VD-VILLE, VA-VILLE, HD, HA)
PAV (VOL##, PL##, AV##)
:1) VOL est une entité « déterminante » (de PILOTE et d ’AVION)
(H1) VOL## (PL##, AV##)
Schéma relationnel :
VOL (VOL##, AV##, PL##, VD, VA, HD, HA)
N:1
Page 71 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLES et CAS PARTICULIERS
EXEMPLE 3
2) VOL est une entité « associative »
(lien N:M entre AVION et PILOTE)
(H2) VOL## (PL##, AV##)
Schéma relationnel :
VOL1 (VOL##, VD-VILLE, VA-VILLE, HD, HA)
PAV (VOL##, PL##, AV##)
EXEMPLE : avec N = 2
VOL2 (VOL##, PL##CHEF, COPL##,
AV##INIT, AV##RECH, VD, VA, HD, HA)
avec
PL##CHEF : numéro du pilote en chef
COPL## : numéro du copilote
AV##INIT : numéro d ’avion principal
AV#RECH : numéro d ’avion de rechange
N : M
Page 72 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
EXEMPLES et CAS PARTICULIERS
EXEMPLE 4
EMPLOYE (E-E##, ENOM, EADR, SAL)
PILOTE (E-P##,… NBHV, SPEC)avec
NBHV : nombre d ’heures de vol
SPEC : spécialité particulière (principale)
HOTESSE (E-H##,… RANG, ANCIEN.)
AVION (AV##, AVNOM, CAP, LOC-VILLE)
EQUIPAGE (EQ##; P1#, P2#, H1#, H2#, H3#)
VOL (VOL##, EQ##, AV##, VD-VILLE,
VA-VILLE, HD, HA)
Note : en CAMPUS cette double déclaration est impossible ; il faut donc créer une clé
primaire multi attribut ; exemple dans la relation HOTESSE la clé serait le couple d ’attribut
(H#, RANG).
Page 73 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
GENERALISATION
Considérons l ’entité EMPLOYE de la compagnie aérienne avec l ’extension suivante
EMPLOYE E# ENOM SAL NBHVOL RANG STATUT
1 Pierre 20 KF 2000 - Pilote
2 Serge 30 KF 3000 - Pilote
3 Elodie 15 KF - 1 Hôtesse
4 Martine 20 KF - 3 Hôtesse
EMPLOYE E# ENOM SAL STATUT
1 Pierre 20 KF Pilote
2 Serge 30 KF Pilote
3 Elodie 15 KF Hôtesse
4 Martine 20 KF Hôtesse
PILOTE E# NBHVOL
1 2000
2 3000
Hotesse E# RANG
3 1
4 3
Double Déclaration :
« clé primaire et
étrangère
Elimination de valeurs
nulles inapplicables
Page 74 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
GENERALISATION
Autre représentation possible
EMPLOYE E# STATUTPour intégrité
référentielle
PILOTE PL# PLNOM SAL NBHVOL
HOTESSE H# HNOM SAL RANG
Intérêt : relation dynamique mettant en jeu un lien avec EMPLOYE
INTERROGATION RELATIONNELLE
{E#} {PILOTE HOTESSE}
QL relationnel : UNION de jointure sur les clés.
Page 75 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
RESUME
Modification / « NORMALISATION »
VS (contre)
Consultation / « DENORMALISATION »
2ième phase : (approche analytique)
Normalisation :
Définition des « bonnes » relations pour les opérations de mise à jour.
- 1ière forme normlae : tout attribut est monovalué
(création de nouvelle relations ou de noveau attributs)
- 3ième forme normale : tout déterminant d ’un lien N:1 est clé
Processus de décomposition réversible
(un seul concept sémantique par relation)
Règle finale sur le schéma relationnel
R1 : « Deux relations indépendantes ne peuvent avoir la même clé primaire sauf si
l ’une est aussi clé étrangère ».
Cette double déclaration est nécessaire pour prendre en compte la généralisation et
résoudre simplement le problème d ’interblocage d ’initialisation.
Sur-normalisation (performance d ’accès)
Dénormalisation (interrogations privilégiées)
Page 76 Copyright S. MIRANDA Conception Relationnelle
DESSde l’Université de
Nice Sophia Antipolis
Exemples 2NF, 3NF et BC -NF
VOL (VOL#, Jour, PL#, AV#, TYPE-VOL)
-Initialisez par des tuples
-Montrez les anomalies de stockage
-Normalisez
1) VOL# TYPE-VOLNormalisation 2NF (dépendance partielle sur la clé)
2) PL# AV#Normalisation 3NF (dépendance transitive)
3) PL# JourNormalisation BCNF