Chapitre 3 : Le Modèle Relationnel

Chapitre 3 :

Le Modèle Relationnel

Mr. Mohamed Salah MEDDEB

http://meddeb.jimdo.com

1

2

Historique : E.F. CODD dans les années 70

• modèle basé sur la théorie des ensembles

• des langages pour manipuler les données (SQL)

• une démarche pour représenter le « monde réel »

Nombreux outils :

• MySQL (logiciel libre)

• Access (Microsoft) versions 1997 – 2003 – 2007 - 2010

• ORACLE (Oracle corp.)

• SQL Server (Microsoft)

• Ingres (Computer Associates)

Modèle relationnel - SQL

Concepts des Bases de Données Relationnelles

Relation, faite de 2 composantes:

Instance : une table, avec lignes et colonnes.

#lignes = cardinalité, #colonnes = degré / arité.

Schéma : spécifie le nom de la relation, plus le nom et le

domaine (type) de chaque colonne (attribut).

Une relation est un ensemble de lignes (tuples) distinctes;

chaque tuple a la même arité que le schéma de la relation.

Base de données relationnelles: un ensemble des relations de

la BD, chacune ayant un nom distinct.

Schéma d’une BD: ensemble de schémas des relations

dans la BD.

Instance de la BD: ensemble des instances relationnelles de

la BD.

3

Langages de Requêtes Relationnelles

Un avantage majeur du modèle relationnel est qu’ilsupporte de simples et puissantes requêtes sur lesdonnées.

Les requêtes peuvent être écrites de manière intuitive (i.e.déclarative), et le SGBD est responsable de leurévaluation efficiente.

L’utilisateur dit au SGBD quoi faire et le système cherchecomment faire ce qu’il y a à faire de manière efficiente!

La clé du succès: sémantique précise des requêtes.

Permet à l’optimisateur de réordonner les opérations touten garantissant que la réponse ne change pas.

4

5

Modèle Relationnel

SCHEMA D’UNE RELATION :

6

Modèle Relationnel

ATTRIBUT (COLONNES) : colonne d ’une relation

caractérisée par un nom et un domaine.

CLE D ’UNE RELATION : ensemble minimum d ’attributs

d ’une relation qui détermine les autres attributs.

N-UPLET (LIGNE, TUPLE) : correspond à une occurrence

(ou ligne) d ’une relation

VALEUR NULLE : Valeur inconnue d ’un attribut dans la

base de données. Différente de 0 (zéro) et de chaîne vide.

7

INTEGRITE DES DONNEES

Objectif : garantir la cohérence des données afin d ’avoir une

concordance entre la base et le monde réel qu ’elle modélise.

Intégrité de domaine : concerne le contrôle syntaxique et

sémantique d ’un attribut et fait référence au type de définition

du domaine.

Exemple : age : entier compris entre 0 et 120.

Intégrité d ’entité : concerne les valeurs prises par une clé

primaire qui doivent être unique et non nulles.

Exemple : pour la relation CLIENT, CODE_CLIENT est unique et non nul.

Intégrité de référence : Si un même attribut apparaît dans une

relation comme clé et dans une autre comme non clé, toute

valeur de l ’attribut non clé doit exister dans l ’attribut clé.

8

Règles de passage d ’un modèle E/A vers

un schéma Relationnel

REGLE n°1 : TOUTE ENTITE DEVIENT UNE RELATION

Les attributs traduisent les propriétés de l ’entité

la clé primaire traduit l'identifiant de l'entité

9

Règles (suite)

REGLE n°2 : UNE ASSOCIATION DE DIMENSION 2 AVEC

CARDINALITE 1 A PLUSIEURS SE REECRIT EN :

portant dans la relation fille la clé primaire de la

relation mère.

L'attribut ainsi ajouté s'appelle clé étrangère.

10

Règles (suite) REGLE n°3: UNE ASSOCIATION DE DIMENSION 2 AVEC

CARDINALITES PLUSIEURS A PLUSIEURS SE REECRIT EN :

créant une relation contenant comme attributs les identifiants

des 2 entités associées

ces attributs constituent à eux 2 la clé primaire de la relation

ajoutant la ou les éventuelles propriétés de l'association à

cette relation.

Règles (suite)

Règle 4: Une association binaire de type 1 :1 est traduite

comme une association binaire de type 1 :n sauf que la clé

étrangère se voit imposer une contrainte d’unicité en plus

d’une éventuelle contrainte de non vacuité (cette contrainte

d’unicité impose à la colonne correspondante de ne prendre

que des valeurs distinctes).

11

12

Règles (suite)

REGLE n°5: UNE ASSOCIATION DE DIMENSION

SUPERIEURE A 2 SE REECRIT SELON LA REGLE 3 :

exemples

1-n 1-1

13

exemples

0-1 1-1

14

exemples

0-1 0-1

15

Exemples

1-1 1-1

Si fonctionnellement, le marin est le plus important…

MARIN(numMarin , nomMarin , numVoilier ,nomVoilier)

Si fonctionnellement, le voilier est le plus important…

VOILIER(numVoilier , nomVoilier , numMarin , nomMarin)

Si le modèle peut évoluer ou si on a une distinction fonctionnelle

forte entre marin et voilier…

VOILIER(numVoilier , nomVoilier , numMarin)

MARIN(numMarin , nomMarin , #numVoilier)

une course à la voile en solitaire :

16

Exemples

0-1 1-1

APPARTEMENT (numAppartement , superficie)

PLACE_PARKING (numPlace , Etage ,

#numAppartement)

Dans un immeuble, un appartement peut bénéficier

d’une place de parking ou pas mais jamais de plusieurs.

17

Exemples

0-1 0-1Une activité culturelle peut disposer d’un animateur ou

pas mais jamais de plusieurs. Un animateur peut

s’occuper au maximum d’une activité culturelle.

ANIMATEUR (numAnimateur , nom)

ACTIVITE_CULTURELLE (idActivite , nomActivite)

ANIMER (#numAnimateur , # idActivite)

18

exemples

1-n 1-n

19

exemples

0-n 0-n 1-n

20

21

22

23

Réflexive

SALARIE (matricule, nom, prénom, fonction,…,

#matricule_chef)

PIECE (référence, libellé)

COMPOSITION(#référence_composé, #référence_composant,

nombre)

24

Transformation de l'identifiant relatif

25

Propriétaire (Numpro, Ruepro,…)

PP (Numpro#, NomPP…)

SCI (Numpro#, NomSCI, Refbanque, Fraisges…)

26

Intérêt de la normalisation Risques:

Exemple 1: Soit la relation

PRODUIT (prod_id, libellé, pu, qte, dep_id, adr, volume)

Anomalies

o -Redondance : libellé et pu apparaissent pour chaque instance d'un

produit

o -Risque d'introduction d'incohérence lors de l'insertion d'une nouvelle

instance de p1

o -Risque de perte d'information : la suppression du produit p3 entraine

la perte de son libellé, son pu et des information relatives au dépôt 4

27

Intérêt de la normalisationExemple 2: Soit la relation

Emprunt_Amis (N°DVD, Nom, Prénom, Contact, Date_emp)

Pourquoi cette relation a t-elle des anomalies?

Anomalies:

Anomalie de mise à jour (exp : Michel Valon a changé de numéro de portable :

mise à jour dans tous les tuples concernés)

Anomalie d’insertion (exp : Nouvel ami, Dupreu Jean : je ne peux l’entrer dans la

base que lorsqu’il m’empruntera un DVD)

Anomalie de suppression (exp : J’ai perdu le DVD n°230 : si je le

supprime de la base, je perds les information sur Prunier Marie)

28

Objectifs:

Supprimer les anomalies précédentes

élimination des redondances : éviter les incohérences + minimiser

l’espace de stockage

Normaliser une relation consiste à décomposer une relation ayant

des anomalies en plusieurs sous relations sans anomalies.

Normalisation s’inscrit dans la partie conception d’une base de

données -> peut être vu comme un outil théorique de vérification

Problème :

Comment décomposer une relation , sachant qu’une mauvaise

décomposition peut mener à une perte d’informations ????

La Normalisation

La Normalisation

PRINCIPE

Appliquer les règles : Formes Normales

Une forme normale: une méthode de classification de table qui

repose sur les dépendances fonctionnelles

30

Dépendance fonctionnelle (Définition)Définition :

Un attribut ou une liste d'attributs Y dépend fonctionnellement

d'un attribut ou d'une liste d'attributs X dans une relation R, Si étant

donnée une valeur de X, il ne lui est associé qu'une seule valeur de

Y dans toute instance de R.

On note x y une telle dépendance.

Exemples :

PRODUIT (prod_id, libellé, pu, qte, dep_id, adr, volume)

prod_id libellé

prod_id pu

dep_id adr, volume

prod_id, dep_id qte

31

Un attribut ou une liste d'attributs X est une clé pour la relation

R(X,Y) si Y dépend fonctionnellement de X dans R

X est une clé minimale si x y est élémentaire

Si une relation possède plusieurs clés, nous on choisissons une

qui sera

appelée clé primaire (soulignée dans les schémas de relation).

Les autres clés seront appelées clés secondaires

Exemple : la relation

Voiture(immat , chassis, type, marque, puissance)

admet « immat » et « chassis » comme clés

Dépendance fonctionnelle (clé d’une relation)

33

Les dépendance Fonctionnelles

DEPENDANCE FONCTIONNELLE (DF) SIMPLE :

Il existe une dépendance fonctionnelle entre deux attributs d ’une

relation, si à toute valeur de A on ne peut associer à tout instant

qu’une et une seule valeur de B.

A B : A détermine B

Exemple : Le numéro de salarié dans une entreprise détermine son

nom

DF DE PLUSIEURS VARIABLES:

A <-> B A détermine plusieurs B

Exemple : Le nom d’un professeur détermine la liste de ses étudiants

34


DF ELEMENTAIRE :

A B est élémentaire s ’il n ’existe pas X A tel que X B

Autrement dit A,X B, X B n ’est pas une DF élémentaire

Exemple :

R1(Fournisseur,Article,Adresse,Prix)

Fournisseur,Article Prix est une DF élémentaire

Fournisseur,Article Adresse est juste mais redondante car

Fournisseur Adresse, donc dans R1, Adresse ne dépend que

d ’une partie de la clé.

R1 doit être décomposée en :

R2(Fournisseur, Adresse)

R3(Fournisseur, Article, Prix)

35


DF DIRECTE :

A B est directe s ’il n ’existe pas X tel que A X et X B

Exemple :

R1(Numéro_Véhicule, Marque, Type_Véhicule, Puissance, Couleur)

Les dépendances fonctionnelles sont les suivantes :

Numero_Vehicule Type_Vehicule,Couleur

Type_Vehicule Marque,Puissance

Type_Vehicule est donc une clé pour Marque et Puissance

donc R1 doit être décomposée en :

R2(Type_Vehicule, Marque,Puissance)

R3(Numero_Vehicule,Couleur,Type_Vehicule)

1NF :

Une relation est dite en première forme normale si

chacun de ses attributs a un domaine atomique mono-valué

Exemple :

Personne(id, nom, les_diplômes) n'est pas en 1NF

où « les_diplômes » est l'ensemble des diplômes obtenus par

une personne

Personne(id, nom) est en 1NF

Diplôme(id, unDiplome) est en 1NF

Formes normales (1FN)

37


1FN: exemple 2

Emprunteur LivrecodEmprunteur

NomTitre

emprunter

1,3 1,1

CodEmprunt Livre1 Livre2 Livre3

1501

1502

La relativité

Tintin et le Lotus bleu

Alice au pays..

Mobby Dick

CodEmpru

1501

1502

NOM

Durand

Leroux

Titre CodEmp

La relativité

Alice au pays ..

Tintin et le Lotus

Mobby Dick

1501

1501

1502

1502

Formes normales (2FN)Une relation R est dite en deuxième forme normale si

(i) elle est en 1NF

et

(ii) tout attribut n'appartenant pas à une clé ne dépend pas d'une partie de la clé de R

Exemple :

Stock(prod_id, dep_id, libellé, qte) n'est pas en 2NF

car : prod_id, dep_id qte

prod_id libellé

Stock(prod_id, dep_id, qte) est en 2NF

Produit(prod_id, libellé) est en 2NF

Exercices : Est-ce que ces rélations sont en 2FN

Lignes_Commandes( Num_Comm, Num_produit, Désgnation, Qté, PU)

Emprunt_Amis (N°DVD, Nom, Prénom,Titre, Réalisateur, Genre, Contact, DateEmp)

39

Formes Normales 2FN : Exemple

NumSalarié Nom NumProjet Heures

NumSalarié Nom

NumSalarié NumProjet Heures

2036

3690

4502

4502

Durand

Durand

Leroux

Frank

Frank

1

2

2

3

1

18

6.5

8.5

23

4.8

2036

3690

4502

Durand

Leroux

Frank

2036

2036

2036

3690

4502

4502

1

2

2

3

1

18

6.5

8.5

23

4.8

3NF :

Une relation est dite en troisième forme normale si

(i) elle est en 2NF

et

(ii) tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut non

clé toutes les DF sont directes

Exemple :

La relation AVION

n'est pas en 3NF

car

type capacité, constructeur et type n'est pas une clé

Avion(no_avion, type, propriétaire) est en 3NF

Modèle(type, constructeur, capacité) est en 3NF


41

Formes Normales

3FN: Exemple

Nom NumSalarié Date_naiss Service Nomservice NumChef

NumSalarié Nom Date_naiss Service

Service NomService NumSalarié_chef

Durand

Martin

501

502

15/01/1950

12/04/1957

5

6

Vente

Info

458

412

5

6

vente

Info

458

412

501

502

Durand

Martin

15/01/1950

12/04/1957

5

6

42

La partie opérationnelle : l'algèbre

relationnelle

Les langages de base de données relationnelle tels que SQL,

se base sur des opérateurs dérivés de la théorie des

ensembles pour pouvoir interroger et mettre à jour les tables

d’une base de données relationnelle.

4 opérations ensemblistes (union, intersection, différence,

produit cartésien)

3 opérations spécifiques des BD relationnelles (sélection,

projection, jointure)

43

Opérations ensemblistes (Union)

L'opérateur UNION renvoie les lignesissues des deux interrogations aprèsélimination des doublons.

Prof_debut Prof_fin

A B

Matricule Nom Prénom Matière

0001 Baccouch Hajer Mobiles

0002 Meddeb Mohamed Base des données

0003 Guerbouj Sonia Java

0004 Bahri Taher Admin Système




0005 Chaabani Nizar Sécurité Informtique

0006 Ferjani Anis ProgrWeb







0006 Ferjani Anis Programmation Web

Prof_debut Prof_fin

44

Opérations ensemblistes (Intersection)

L'opérateur INTERSECT renvoie les lignes

communes aux deux interrogations.











Prof_debut Prof_fin

Prof_debut Prof_fin




Opérations ensemblistes (Différence)

L'opérateur MINUS renvoie toutes les lignes

distinctes sélectionnées par la première

interrogation, mais non présentes dans

l'ensemble de résultats de la deuxième

interrogation.

45

A B











Prof_debut Prof_fin






Prof_debut - Prof_fin

46

Opérations ensemblistes

Produit cartésien

Numéro

coureur

Nom Coureur Code

équipe

Code

pays

8 ULLRICH Jan TEL ALL

31 JALABERT Laurent ONC FRA

61 ROMINGER Tony COF SUI

91 BOARDMAN Chris GAN G-B

Code

pays

Nom Pays

ALL Allemagne

FRA France

SUI Suisse

G-B Grande -

Bretagne

Numéro

coureur

Nom Coureur Code

équipe

Code

pays

Code

pays

Nom Pays

8 ULLRICH Jan TEL ALL ALL Allemagne

8 ULLRICH Jan TEL ALL FRA France

8 ULLRICH Jan TEL ALL SUI Suisse

8 ULLRICH Jan TEL ALL G-B Grande -Bretagne

31 JALABERT Laurent ONC FRA ALL Allemagne…

R

R1 R2

-

47

Opérations spécifiques

Sélection (ou restriction) : relation composée de n-upletsvérifiant une condition

Numéro

coureur

Nom Coureur Code

équipe

Code

pays





114 CIPOLLINI Mario SAE ITA

Quels sont les coureurs suisses ?

R = SELECTION(COUREUR, CodePays = ‘‘SUI’’)

NOTATION : CodePays = ‘‘SUI’’(COUREUR)

Relation

résultat

48


Projection : relation restreinte aux attributs spécifiés dans

la projection

Exemple : Nom et nationalité des coureurs ?

R = PROJECTION(COUREUR, NomCoureur, Nationalité)

NOTATION : {NomCoureur,Nationalité}(COUREURS)

Nom Coureur Code pays

ULLRICH Jan ALL

JALABERT Laurent FRA

ROMINGER Tony SUI

BOARDMAN Chris G-B

CIPOLLINI Mario ITA

Relation

résultat

49


Jointure :

Code pays Nom Pays

ALL Allemagne

FRA France

SUI Suisse

G-B Grande -Bretagne

Numéro

coureurNom Coureur

Code

équipe

Code

pays





Numéro

coureurNom Coureur

Code

équipe

Code

paysNom Pays

8 ULLRICH Jan TEL ALL Allemagne

31 JALABERT Laurent ONC FRA France

61 ROMINGER Tony COF SUI Suisse

91 BOARDMAN Chris GAN G-B Grande -

Bretagne

Relation

résultat

50


Jointure (suite)

Notation : Coureur Pays

-jointure : jointure sous condition autre que

l’égalité

Code

pays

Code

pays

51


Division : relation composée des n-uplets tels que le produit

cartésien avec le diviseur soit un sous-ensemble de la relation

dividende (…?)

Athlète Epreuve Epreuve Athlète

Dupont 200 m 200 m Dupont

Durand 400 m 400 m

Dupont 400 m 110 m H

Martin 110 m H

Dupont 110 m H

Martin 200 m

Participer Epreuve

Relation résultat de

Particier Epreuve

Quels sont les athlètes qui ont participé à toutes les épreuves ?

52

a

b

c

x

y

a

a

b

b

c

c

x

y

x

y

x

y

Produit cartésien

Sélection Projection

Union Intersection Différence

Documents

Chapitre 3 : Le Modèle Relationnel