Base de données - Cours

2014-06-10

1

Base de donnéesMODÈLE RELATIONNEL

1

Le modèle relationnel

Le modèle relationnel est créé par Edgar Frank Codd et présenté en 1970.

Après quelques années, le modèle est développé. Dès lors, les BDR

deviennent progressivement les SGBD les plus répandus.

L’intention du modèle relationnel (MR) est de créer une méthode

déclarative (non procédurale) permettant de spécifier l’organisation des

données ainsi que les moyens d’y accéder.

C’est le langage SQL qui permet de donner naissance au MR en ce sens.

Le modèle relationnel est basé sur la notion d’ensemble utilisant l’algèbre

relationnel ainsi que le calcul relationnel.

2

2014-06-10

2

Le modèle relationnelDéfinition

Dans le MR, la notion d’ensemble se présente sous la forme de relation.

Le MR est très simple puisqu’il est constitué d’une seul élément : la relation.

Une relation est définie ainsi :

elle porte un nom;

elle est composé d’attribut(s) nommé(s) ayant différentes contraintes;

elle est constitué de tuple(s).

Les liens existants entre les relations se font par le concept de clé étrangère.

Toutes les opérations du MR utilisent des relation (en entrée et en sortie).

3


Les attributs définissent la structure de la relation.

Les tuples correspondent aux données de la relation. Chacun décrit un

enregistrement unique.

On représente généralement la relation par une table où :

les colonnes correspondent aux attributs;

les lignes correspondent aux tuples.

4

2014-06-10

3


5

Nom Prénom NAS Courriel

Wong Tony 123456789 [email protected]

Individu

Cheriet Mohamed 987654321 [email protected]

Sabourin Robert 2468013579 [email protected]

attributcolonne

tupleligne

enregistrement

nom de la relationnom de la table

relationtable

Le modèle relationnelContraintes

Il existe plusieurs formes de contraintes :

les contraintes sur les attributs;

les contraintes sur les tuples;

les contraintes sur les valeurs;

les contraintes d’intégrité;

les contraintes sur la base de données.

6

2014-06-10

4

Le modèle relationnelContraintes sur les attributs

Chaque relation possède un nombre fini d’attributs :Na 1

Chaque attribut est associé à un domaine de valeur déterminé par le concepteur :Ai Di

Di est généralement constitué de 2 parties :

type (obligatoire);

contraintes supplémentaires (optionnelles).

On dit d’une relation qu’elle porte le degré du nombre d’attribut(s) qu’elle possède. Ainsi, une relation de degré 2 possède 2 attributs, une relation de degré 3 possède 3 attributs et ainsi de suite.

7

Le modèle relationnelContraintes sur les tuples

Chaque relation possède un nombre fini de tuple :

Nt 0

si Nt = 0 la relation est vide

Un tuple tj est composé de i valeurs correspondant aux i attributs définis :

Tj = { v1, v2, v3, …, vi } { D1, D2, D3, …, Di }

Formellement, la relation ne peut avoir deux tuples identiques.

ce qui n’est pas vrai en fait

8

2014-06-10

5

Le modèle relationnelContraintes sur les valeurs

La valeur d’un attribut doit respecter le domaine défini :

vji Di

Chaque valeur doit être atomique :

vji { x }

vji peut posséder une valeur nulle ayant deux interprétations possibles :

aucune valeur applicable (la date de mariage d’une personne célibataire);

aucune valeur connue (date de naissance inconnue).

9

Le modèle relationnelContraintes d’intégrité

Il existe trois contraintes d’intégrité :

intégrité de domaine;

intégrité d’enregistrement;

intégrité référentielle.

Il est important de comprendre que les SGBD modernes ont plusieurs

outils permettant d’assurer cet aspect fondamental des BDR.

L’intégrité de domaine correspond aux contraintes sur les valeurs :

valeur correspondant au domaine défini;

valeur atomique.

10

2014-06-10

6

Le modèle relationnelContraintes d’intégrité

Intégrité d’enregistrement :

chaque relation doit posséder une clé primaire définie par un ou plusieurs attributs;

chaque tuple doit avoir une valeur unique de clé primaire;

une clé primaire ne peut être nulle.

Intégrité référentielle : chaque clé étrangère doit posséder l’un de ces

deux états :

la valeur de la clé primaire du tuple lié;

la valeur nulle s’il n’existe pas de liaison pour ce tuple.

11

Le modèle relationnelContraintes sur la BDR

Une base de données relationnelle (BDR) est constituée d’un

regroupement de plusieurs relations :

Nr 1

Chaque relation doit avoir un nom unique.

Finalement, le concepteur doit éviter la redondance d’information en

créant une seule entrée du même enregistrement dans tout le système.

12

2014-06-10

7

Le modèle relationnelReprésentation de la BDR

On représente graphiquement les BDR par :

le schéma relationnel;

Le diagramme du schéma relationnel.

13

Le modèle relationnelSchéma relationnel

Le schéma relationnel consiste à illustrer de façon compact une BDR.

Les règles d’affichage sont :

on représente les relations par leur nom;

on enchaine par la liste d’attribut(s) mis entre parenthèses;

les attributs liés à la clé primaire sont soulignés.

Par exemple :

Individu (Nom, Prénom, DateNaissance, NAS, Courriel)

Cours (Sigle, Nom, Description, NombreCrédit)

14

2014-06-10

8

Le modèle relationnelDiagramme du schéma relationnel

Le diagramme du schéma relationnel consiste à illustrer une BDR sous forme de tabloïde. Il permet de représenter autant la structure qu’un exemple d’instance.

Les règles d’affichage sont :

on représente les relations par un tableau au-dessus duquel on appose le nom;

sur la première ligne, on met dans chaque cellule le nom des attributs;

les attributs liés à la clé primaire sont soulignés;

si seulement la structure est présente, on ajoute généralement une deuxième ligne vide;

pour représenter une instance, on ajoute plusieurs lignes dans lesquelles on met les valeurs mises en exemple;

on représente généralement les clés étrangères par une flèche indiquant les liens.

15

Le modèle relationnelDiagramme du schéma relationnel

16


Individu

Sigle Nom

Département

Departement



Individu


Champliaud Henri 333444555 [email protected]

Sigle Nom

ELE Génie électrique

GPA Génie de la production automatisée

MEC Génie mécanique

Departement

GPA

GPA

MEC

Département

représentation structurelle

représentation structurelle avec un exemple d instance

relation Individu relation Département

2014-06-10

9

Le modèle relationnelImpacts du modèle

Suivant le dernier exemple, on pourrait être tenté de tout simplifier en

mettant toutes les données dans une seule relation. Comme ceci :

Toutefois, l’usage correct du MR permet de nombreux avantages en

évitant certains pièges comme le montre cet exemple.

17



Individu


Champliaud Henri 333444555 [email protected]

NomDepartement

Génie de la production automatisée

Génie de la production automatisée

Génie mécanique

SigleDepart

GPA

GPA

MEC

Le modèle relationnelImpacts du modèle

L’exemple précédent montre plusieurs problématiques :

perte de l’espace mémoire : répétition du nom de département pour chaque instance;

difficulté de mise à jour, donc difficulté de maintenir et garantir l’intégrité d’enregistrement : si un département change de nom, il faut s’assurer que toutes les instances sont modifiées;

insertion massif de valeurs nulles dès qu’une insertion est faite pour une partie des données : un individu n’ayant pas été assigné à un département par exemple.

Ainsi, en ayant deux relations distinctes, il est possible de dissocier les

« objets » du monde réel et de les manipuler plus efficacement.

18

2014-06-10

10

Le modèle relationnelTransformation : modèle EA vers MR

Tous les éléments présentés pour les modèle EA et EAE peuvent être

transformés vers le MR.

Néanmoins, la transformation ne garantie pas une forme optimale du MR.

Si le modèle de base présente déjà des lacunes de conception, il est

probable que ces dernières se propagent dans le MR.

Attention, ces lacunes ne sont pas nécessairement due à une mauvaise

modélisation mais souvent à un choix non optimal en fonction du

contexte.

L’étape finale de normalisation permettra de faire cette optimisation.

19

Le modèle relationnelTransformation EA MR

Étapes de transformation

1. entités fortes et faibles;

2. attributs participant à la clé primaire;

3. attributs simples, dérivés et

obligatoires;

4. attributs composites;

5. relations binaires 1:1;

6. relations binaires 1:N;

7. relations binaires M:N;

8. relations ternaires;

9. attributs multivalués;

10. association d’identification;

20

2014-06-10

11


Étape 1 : Transformation des entités fortes et faibles

les entités deviennent directement des relations dans le modèle relationnel;

le nom des relations est le nom des entités;

dans le MR, il n’existe aucune différence entre les deux types d’entités.

21

abc- - - -

abc

entité forte relation

def- - - -

def

entité faible relation


Étape 2 : Transformation des attributs participant à la clé primaire

un attribut participant à la clé primaire devient un attributs équivalent;

le nom reste le même.

22

def

abcdef - - -

abc

attribut participant à la clé primaire

attribut participant à la

clé primaire

2014-06-10

12


Étape 3 : Transformation des attribut simples, dérivés ou obligatoires

un attribut simple, dérivé ou obligatoire devient un attribut dans le MR;

les noms restent les mêmes;

Les attributs dérivés et obligatoires posséderontd’autres particularitéslors de l’implémentation.

23

ghi

abcdef ghi jkl -

abc

attributsattribut obligatoire

jkl

attribut dérivé

def

attribut simple


Étapes 4 : Transformation des attributs composites

un attribut composite est décomposé en attribut dans le MR de façon à représenter tous les constituants du dernier niveau hiérarchique;

il est d’usage courant de nommer les nouveaux attributs avec une contraction des constituants hiérarchiques du modèle EA.

24

abcabc_def abc_ghi abc_klm -

abc

attributs issus de la décomposition

attr

ibu

t co

mp

osit

e

abc ghi

def

klm

2014-06-10

13


Étapes 5 : Transformation des relations binaires 1:1

Trois approches peuvent être envisagées :

1. fusionner les deux entités vers une relation du MR (fusion);

2. créer une clé étrangère à l’intérieur d’une relation du MR vers la clé primaire de l’autre (clé étrangère);

3. créer une troisième relation dans le MR afin de lier les deux participant (relation d’association).

Selon le contexte, certaines approches ne sont pas possibles. De plus,

certaines pratiques sont recommandées alors que d’autres non.

25


Approche 1 : fusion des deux entités afin de créer une relation dans le MR

on identifie les entités selon A et B en fonction de leur contrainte de participation à la relation :

participation totale des deux côtés : A est l’entité la plus significative alors que B est

l’entité la moins significative;

participation totale du côté de l’entité B et participation partielle du côté de l’entité

A (non recommandée);

participation partielle des deux côtés : A est l’entité la plus significative alors que B est

l’entité la moins significative (non recommandée).

la relation B du MR disparaît et transfert tous ses attributs vers la relation A;

26

2014-06-10

14



les attributs participant à la clé primaire de B deviennent des attributs standards de A (sauf pour les identificateurs numériques arbitraires qui disparaissent);

si un même attribut existe dans les deux entités, un seul exemplaire est préservé;

cette approche est impossible avec les relations binaire 1:1 récursive.

27



28

NAS

Étudiant

Nom

No civique

Adresse

Ville

Réside Rue

NAS Nom NoCivique Rue

Étudiant

Ville

Id

1..1 1..1

2014-06-10

15


Approche 2 : création d’une clé étrangère

on identifie les entités selon A et B en fonction de leur contrainte de participation à la relation :

participation totale des deux côtés : A est l’entité la plus significative alors que B est

l’entité la moins significative;

participation totale du côté de l’entité A et participation partielle du côté de l’entité

B;

participation partielle des deux côtés : A est l’entité la plus significative alors que B est

l’entité la moins significative (non recommandée).

on ajoute une clé étrangère à la relation A du MR vers la clé primaire de la relation B.

29



30

NAS

Professeur

Nom

Sigle

Département

Nom

Supervise

NAS Nom Nom

Professeur Département

Sigle Superviseur

0..1 1..1

2014-06-10

16


Approche 3 : relation d’association

on crée une troisième relation dans le MR;

cette relation est possède comme attribut deux clés étrangères vers chacune des clés primaires de chaque relation liée;

un discriminant supplémentaire peut être nécessaire;

évites les valeurs nulles lorsque les contraintes de participation sont partielles.

31



32

Sigle

Cours

Nom

NAS

Surveillant

Nom

Supervise exam

Sigle Nom Nom

Cours Surveillant

NASSigleCours Surveillant

SurvExam

0..1 0..1

2014-06-10

17


Validation des trois approches :

33

Situation FusionClé

étrangèreRelation

d’associa.

Les entités sont intimement liées (l’un est une précision de l’autre) oui – peut oui – oui oui – non

Les entités représentent des objets différents dans le monde réelle oui – non oui – oui oui – non

Les deux contraintes de participation sont totales oui – peut oui – oui oui – non

Une contrainte de participation est totale et l’autre partielle oui – non oui – oui oui – non

Les deux contraintes de participation sont partielles oui – non oui – non oui – oui

Au moins une entité possède plusieurs relation vers d’autres entités non – non oui – oui oui – non

Les entités liées sont très sollicitées (soucis de performance) oui – oui oui – non oui – non

Légende : possible – recommandée


Étapes 6 : Transformation des relations binaires 1:N

Deux approches peuvent être envisagées :

1. créer une clé étrangère dans la relation du MR de l’entité de cardinalité 1 vers la clé primaire de l’entité de cardinalité N (clé étrangère);

2. créer une troisième relation dans le MR afin de lier les deux participant (relation d’association).

Encore une fois, c’est le contexte qui guidera la solution.

34

2014-06-10

18



35

NAS

Étudiant

Nom

Sigle

Département

Nom

Inscrit

NAS Nom Nom

Étudiant Département

Sigle

1..1 0..N

SigleDep



36

Sigle

Cours

Nom

Sigle

Département

Nom

Inscrit

Sigle Nom Nom

Cours Département

Sigle

0..1 0..N

Cours Departement

CoursDep

2014-06-10

19


Validation des deux approches :

37

SituationClé

étrangèreRelation

d’associa.

L’entité de cardinalité 1 est de participation totale oui – oui oui – non

L’entité de cardinalité 1 est de participation partielle oui – peut oui – oui

Les entités liées sont très sollicitées (soucis de performance) oui – oui oui – non

Légende : possible – recommandée


Étapes 7 : Transformation des relations binaires M:N

Une seule approche peut être envisagée : créer une troisième relation

dans le MR afin de lier les deux participant (relation d’association).

38

NAS

Étudiant

Nom

Sigle

Cours

Nom

Inscrit

NAS Nom Nom

Étudiant Cours

Sigle

0..N 0..N

Etudiant Cours

CoursEtu

2014-06-10

20


Pour les étapes 4 à 6 (relation binaire 1:1, 1:N et M:N), il faut aussi

considérer l’ajout des attribut de relation du modèle EA.

L’idée est relativement simple, on suit les mêmes règles que celles

présentées concernant la nature des transformations sur chaque type

d’attribut possible.

Lors des différentes transformations, les attributs se trouvent au même

endroit que les liens de ces relations.

39


40

NAS

Professeur

Nom

Sigle

Département

Nom

Supervise

NAS Nom Nom

Professeur Département

Sigle Superviseur

0..1 1..1

Date de début

DateDebutSup

2014-06-10

21


41

Sigle

Cours

Nom

Sigle

Département

Nom

Inscrit

Sigle Nom Nom

Cours Département

Sigle

0..1 0..N

Cours Departement

CoursDep

Date de création

DateCreation


Étapes 8 : Transformation des relations ternaires et généralisées (version

simple)

Hors cours…

42

2014-06-10

22


Étapes 9 : Transformation des attributs multivalués

Deux solution se présentent :

1. ajout d’un nombre fixe d’attributs;

2. création d’une relation supplémentaire possédant une clé étrangère vers la relation courante.

C’est le concepteur qui devrai faire le compromis le plus pertinent pour

son application. Si le nombre de valeur possible est faible et stable, la

première solution peut être la plus intéressante mais on privilégie plus

souvent la deuxième.

43


Approche 1 : ajout d’un nombre fixe d’attributs

on tente de déterminer le nombre maximum de valeurs différentes possibles;

on ajoute autant d’attributs qu’identifiés.

44

def

abcdef1 def2 def3 -

abc

attributs accueillant les valeurs possible de l attribut principal

attribut multivalué

2014-06-10

23


Approche 2 : création d’une relation supplémentaire

dans le MR, on crée une relation étrangère ayant au moins un attribut standard ayant le nom de l’attribut multivalué;

on ajoute une clé étrangère dans la nouvelle relation vers la clé primaire de la relation initiale.

45

NAS

Étudiant

Nom

Courriel

NAS Nom

Étudiant

Id Courriel

Courriel

Étudiant


Étapes 10 : Transformation des association d’identification (relation des

entités faibles)

Dans le MR, on ajoute une clé étrangère dans l’entités faible vers la clé primaire de l’entité forte.

La clé primaire devient la combinaison de l’identifiant et de la clé étrangère.

46

2014-06-10

24


Étapes 10 : Insertion des association d’identification

47

NAS

Employé

Nom

Nom

Dépendant

Lien

A desdép.

NAS Nom Nom

Employé Dépendant

EmployeNAS

0..N 1..1

Lien

Le modèle relationnelTransformation EAE MR

Étapes de transformation (suite pour EAE)

11. généralisation et spécialisation;

12. catégorisation;

13. agrégation.

48

2014-06-10

25


Étapes 11 : Transformation des généralisations et spécialisations

On peut rappeler que la notion d’héritage implique que les enfants ont toujours un lien vers un parent alors que l’inverse n’est pas nécessairement vrai.

On peut dire qu’il y a un lien étroit entre une relation binaire de type 1:1 et certaines situations d’héritage.

du point de vue de l’enfant, la situation est identique;

du point de vue du parent, la situation est différente car l’enfant peut être multiple.

Il existe cinq méthodes de transformation qui s’appliquent selon le contexte.

49


Il existe quatre types de transformation de base liés à l’héritage :

méthode 1 – multiples relations (parents et enfants);

méthode 2 – multiples relations (enfants seulement);

méthode 3 – une seule relation (une seule spécialisation);

méthode 4 – une seule relation (plusieurs spécialisations).

C’est le contexte qui permet de préciser quelle est la solution à utiliser.

50

2014-06-10

26


Méthode 1 – multiples relations (parents et enfants)

dans le MR, on crée autant de relation qu’identifiée dans le modèle EAE;

les relations enfants possèdent une clé étrangère vers la clé primaire du parent;

cette clé étrangère est aussi la clé primaire.

Cette méthode préserve entièrement la modularité de la modélisation.

51


52

Individu

Employé Visiteur

d

Étudiant

NAS Salaire

DateVisite

Employé

Visiteur

NAS

NAS CodePerma

Étudiant

NAS Nom

SalaireCode

permanentDate de visite

NAS Nom

Individu

2014-06-10

27


Méthode 2 – multiples relations (enfants seulement)

dans le MR, on crée autant de relation que d’enfants identifiés dans le modèle EAE;

la relation parent est fusionnée pour chacun des enfants;

la clé primaire de la relation est celle définie dans le parent.

Cette méthode ne préserve pas complètement la modularité de la

modélisation. Elle reste tout de même efficace sans redondance et

espaces réservés inutilement.

53


54

Individu

Employé Visiteur

d

Étudiant

NAS Salaire

DateVisite

Employé

Visiteur

NAS

NAS CodePerma

Étudiant

NAS Nom

SalaireCode


Nom

Nom

Nom

2014-06-10

28


Méthode 3 – une seule relation (une seule spécialisation)

dans le MR, on crée une relation faisant la fusion de tous les attributs du parent et des enfants;

la clé primaire de la relation est celle définie par le parent;

on ajoute un attribut permettant de spécifier la spécialisation active.


modélisation. Elle peut générer un grand nombre de valeurs mises à nulle.

55


56

Individu

Employé Visiteur

d

Étudiant

Salaire DateVisite

Individu

NAS CodePerma

NAS Nom

SalaireCode


Nom TypeIndividu

2014-06-10

29


Méthode 4 – une seule relation (plusieurs spécialisations)

dans le MR, on crée une relation faisant la fusion de tous les attributs du parent et des enfants;

la clé primaire de la relation est celle définie par le parent;

pour chacun des enfants, on ajoute un attribut permettant de définir un état d’activation (vrai/faux).


modélisation. Elle peut générer un nombre variable de valeurs mises à

nulle.

57


58

Individu

Employé Visiteur

r

Étudiant

Salaire DateVisite

Individu

NAS CodePerma

NAS Nom

SalaireCode


Nom TypeIndividu EstEmploye EstEtudiant EstVisiteur

2014-06-10

30


Héritage multiple

L’héritage multiple peut devenir un problème difficile à résoudre dans certains cas.

D’ailleurs, plusieurs langages de programmation l’interdisent et ceux qui le permettent font souvent plusieurs mises en garde sur son usage.

Le premier problème est la présence d’attributs ayant le même nom ou le même sens.

Si un même nom est utilisé, un changement de nom est requis. Il est d’usage courant de faire une contraction du nom de la relation et de l’attribut afin de lever l’ambiguïté.

Lorsque deux attributs ont le même sens et décrivent le même objet du monde réel, on ne garde qu’un seul des attributs lors des méthodes de fusion.

59


L’héritage multiple à un niveau est relativement simple et peut se traduire

par chacune des techniques vues précédemment.

L’héritage multiple à

plusieurs niveaux

divergents présentent

les mêmes défis

que l’héritage multiple

à un niveau.

60

Individu

Employé Visiteur

d

Étudiant Gestionnaire

Responsable du club

Club étudiant

Gestionnaire

d

2014-06-10

31


L’héritage multiple convergent à plus d’un niveau doit partager la même

clé primaire pour chacun des niveaux jusqu’au point de convergence. Or

les SGBDR gère les contraintes d’intégrité référentielle selon un schéma

binaire et ne permettent pas de

définir une clé étrangère vers

plusieurs relations différentes.*

Ce type d’héritage est plus difficile à

mettre en place car il requiert

l’usage des méthodes

3 et 4 sur plusieurs

niveaux.

61

Individu

Employé Visiteur

r

ÉtudiantGestionnaire

Directeur de département


Catégorisation

La catégorisation est possible par l’usage d’une relation substitut qui permet de faire le lien entre l’enfant et le parent.

dans le MR, on crée autant de relations que de parents et une relation pour l’enfant;

pour la relation enfant, on crée une clé primaire substitut;

on définie ensuite une clé étrangère pour chaque parent vers la clé primaire de l’enfant;

cette clé étrangère devient la clé primaire de la catégorie.

62

2014-06-10

32


63

Individu

c

Étudiant étranger Résident permanentCitoyen canadien

NAS Nom

Pays d'origineVille de

naissanceDate de

résidence

NAS VilleNaissance

DateRés

Citoyen

RésidentPerm

NAS

NAS PaysOrigine

ÉtudiantÉtranger

NAS Nom

Individu


Agrégation

Il existe plusieurs formes d’agrégation. La forme simple déjà vue est transformée par l’usage d’une relation d’association.

on débute la transformation de l’agrégation en utilisant une relation d’association dans le MR pour la relation interne du modèle EA;

on utilise cette relation du MR pour joindre la partie externe et pratiquant une transformation conventionnelle.

Plusieurs alternatives sont possibles selon le contexte, la nature de

l’agrégation et les ratios de cardinalité.

64

2014-06-10

33


65

supporte(0, 3)

Employé TuteurSupporte

est supporté par(0, N)

Rédaction

Rapport

rédaction d un rapport(1, 1)

documentation d un acte de tutorat(1, 1)

EmployéNAS NoRapport

ActeTutorat

NAS Nom

Employé

NAS Nom

Tuteur

No Date

Rapport

TuteurNAS

Détail

Documents

Base de données - Cours