conception dune base de données passage au schéma relationnel

Conception d’une Base de Données

Passage au SchémaRelationnel

2

Conception d’une BD : les niveaux

• conceptuel• logique• physique• externe

schémaphysique

schémaconceptuel

schémalogique

R1R2

UtilisateursUtilisateurs

3

Conception d’une BD : les étapes

Monde ExtérieurMonde Extérieur

Modélisation(UML)

Modélisation(UML)

Produits

idProdésignationpoids

Fournisseurs

idFourraisonSocialeadresse

Fournir

prixAchat

<< Association >>1 1* 1..*

Schéma Conceptuel : DCSchéma Conceptuel : DC

Produits (IdPro, désignation, poids)

Fournir(IdPro, IdFour, PrixAchat)

Fournisseurs(IdFour, RaisonSociale, adresse)

Produits (IdPro, désignation, poids)

Fournir(IdPro, IdFour, PrixAchat)

Fournisseurs(IdFour, RaisonSociale, adresse)

Schéma Logique ou RelationnelSchéma Logique ou Relationnel

Règles depassageRègles depassage

Normalisation ?Normalisation ?

Mise en œuvre

de la BD

Mise en œuvre

de la BD Schéma PhysiqueSchéma Physique

4

Schéma Logique : Schéma Relationnel Présentation

• Base théorique du langage SQL• Modèle basé sur les valeurs

– Di = {valeurs atomiques}

• Attribut – ai prenant sa valeur dans Di

• Relation – R[a1,..., an] définie sur les domaines D1, ..., Dn

– R sous-ensemble du produit cartésien D1*D2*...*Dn ETUDIANT (INE, NOM,ADRESSE)

INE = {100, 200, 300}NOM = {‘Sylvie’,’Patrick’}ADRESSE = {‘Toulouse’,’Montauban’}

ETUDIANT (INE, NOM,ADRESSE)

INE = {100, 200, 300}NOM = {‘Sylvie’,’Patrick’}ADRESSE = {‘Toulouse’,’Montauban’}

5

Schéma relationnelConcepts et Terminologie

• Relation : nom de la structure tabulaire regroupant les informations de même nature

• Tuple ou N-Uplet : ligne d’une relation• Attribut : nom du domaine • Clé primaire : attribut(s) permettant d’identifier

d’une manière unique une ligne • Clé étrangère : attribut(s) permettant d’identifier

d’une manière unique une ligne d’une autre relation (correspondant à une clé primaire)

• Clé candidate : attribut(s) pouvant se substituer à la clé primaire

6

Schéma relationnelExemple

10 Informatique 2

20 Achats 3

IdSerIdSer

SERVICESERVICE

NomSerNomSer NombreEmpNombreEmp

100 Michel 20 300

200 Sylvie 10 400

300 Bernard 20 ------

400 Claude 10 ------

500 Thomas 10 400

EMPLOYEEMPLOYE

IdEmpIdEmp NomEmpNomEmp IdSerIdSer IdEmp_RespIdEmp_Resp

Clés PrimairesClés Primaires

Clés ÉtrangèresClés ÉtrangèresClé CandidateClé Candidate

7

Schéma relationnelQuelques postulats

• Clé primaire – Obligatoire pour toutes les relations– Peut être composée de plusieurs attributs – Ne peut pas avoir de valeur indéfinie (NULL)

• Clé étrangère – Aucune ou plusieurs par relation – Peut être composée de plusieurs attributs Contrainte d’Intégrité Référentielle

• Clé candidate– Accès rapide dans le schéma physique

8

Passage du S. Conceptuel au S. RelationnelLes règles de passage

• Règle n°1 : Classes normales– Chaque classe devient une relation – L’identifiant de la classe devient la clé primaire de la relation

• Règle n°2 : Classes d’Associations 1-N (Mère-Fille)– Cette classe disparaît– La clé de la relation mère glisse dans la relation fille Clé Étrangère

• Règle n°3 : Classes d’Associations N-M (et n-aires) – Cette classe devient une relation– La clé primaire est composée des clés associées (clé primaire

composée)

• Règle n°4 : Classes d’Associations 1-1 – Cas particulier : expliqué plus loin

• Règle n°5 : Généralisation et Spécialisation– Expliqué plus loin

9

Association 1-N : Exemple

DiplomeDiplome EtudiantEtudiant

IdDipNomDipRespDip

IdDipNomDipRespDip

IneEtudiantNomEtudiantAdrEtudiant

IneEtudiantNomEtudiantAdrEtudiant**11

InscrireInscrire

DIPLÔME (IdDip, NomDip, RespDip)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant, IdDip#)

DIPLÔME (IdDip, NomDip, RespDip)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant, IdDip#)

10

Association N-N sans attribut : Exemple

EtudiantEtudiant StageStage

IneEtudiantNomEtudiantAdrEtudiant

IneEtudiantNomEtudiantAdrEtudiant

IdStageThémeStageEntrepriseRespStage

IdStageThémeStageEntrepriseRespStage

1..*1..***

ChoisirChoisir

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

11

Clé primaire composée : postulats

• Autant de composants que de classes associées (N-aires)

• Composée entièrement de clés étrangères• L’ordre des composants n’a pas d’importance

(pour le schéma relationnel)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

CHOISIR (IneEtudiant#, IdStage# )

STAGE (IdStage, Thèmestage, Entreprise, RespStage)

12

Association N-N avec attributs : Exemple

FournisseurFournisseur ProduitProduit

IdFourNomFourAdrFour

IdFourNomFourAdrFour

IdProduitNomProduitQtéStock

IdProduitNomProduitQtéStock

** 1..*1..*

FournirFournir

PrixDélaiLivQtéMin

PrixDélaiLivQtéMin

FOURNISSEUR(IdFour, NomFour, AdrFour)

FOURNIR (IdFour #, IdProduit#, Prix, DélaiLiv, QtéMin )

PRODUIT (IdProduit, NomProduit, QtéStock)

FOURNISSEUR(IdFour, NomFour, AdrFour)

FOURNIR (IdFour #, IdProduit#, Prix, DélaiLiv, QtéMin )

PRODUIT (IdProduit, NomProduit, QtéStock)

13

Association de type N-aires : Exemple

FournisseurFournisseur ProduitProduit

IdFourNomFourAdrFour

IdFourNomFourAdrFour

IdProduitNomProduitQtéStock

IdProduitNomProduitQtéStock

FournirFournir

PrixDélaiLivQtéMin

PrixDélaiLivQtéMin

ConditionnementConditionnement

IdCondLibCondIdCondLibCond

1..*1..*

**

**

14

Association de type N-aires : Exemple

FOURNISSEUR(IdFour, NomFour, AdrFour)

PRODUIT (IdProduit, NomProduit, QtéStock)

CONDITIONNEMENT (IdCond, LibCond)

FOURNIR (IdFour #, IdProduit#, IdCond#,

Prix, DélaiLiv, QtéMin )

FOURNISSEUR(IdFour, NomFour, AdrFour)

PRODUIT (IdProduit, NomProduit, QtéStock)

CONDITIONNEMENT (IdCond, LibCond)

FOURNIR (IdFour #, IdProduit#, IdCond#,

Prix, DélaiLiv, QtéMin )

15

Association 1-N réflexive : Exemple

EnseignantEnseignant

IdEnsNomEnsFonction

IdEnsNomEnsFonction

Est responsable deEst responsable de

A pour responsable A pour responsable **

0..10..1

ENSEIGNANT (IdEns, NomEns, Fonction, IdEns_Resp#)ENSEIGNANT (IdEns, NomEns, Fonction, IdEns_Resp#)

(nul autorisé)(nul autorisé)

16

Association N-N réflexive : Exemple

ProduitProduit

IdProduitLibProduitPrix

IdProduitLibProduitPrix

Est composé de Est composé de

Entre dans la composition de Entre dans la composition de **

**

ComposerComposer

QtéQté

PRODUIT (IdProduit, LibProduit, Prix)

COMPOSER (IdProduitComposé#, IdProduitComposant#, Qté)

PRODUIT (IdProduit, LibProduit, Prix)

COMPOSER (IdProduitComposé#, IdProduitComposant#, Qté)

17

Reprise de classe d’association : Exemple

JoueurJoueur TournoiTournoiIdLicenceNomJoueurClassement

IdLicenceNomJoueurClassement

IdTournoiNomTournoiVilleDate

IdTournoiNomTournoiVilleDate

** **

ParticiperParticiperRésultatRésultat

AmendeAmendeIdAmendeMontantIdAmendeMontant

** **

RecevoirRecevoirPrixPrix

IdPrixMontantIdPrixMontant

ObtenirObtenir

0..10..1**

18

Reprise de classe d’association : Exemple

JOUEUR (IdLicence, NomJoueur, Classement)

TOURNOI (IdTournoi, NomTournoi, Ville, date)

PARTICIPER (IdLicence#, IdTournoi#, Résultat, IdPrix#)

PRIX (IdPrix, Montant)

AMENDE (IdAmende, Montant)

RECEVOIR (IdAmende#,(IdLicence,IdTournoi)#)

JOUEUR (IdLicence, NomJoueur, Classement)

TOURNOI (IdTournoi, NomTournoi, Ville, date)

PARTICIPER (IdLicence#, IdTournoi#, Résultat, IdPrix#)

PRIX (IdPrix, Montant)

AMENDE (IdAmende, Montant)

RECEVOIR (IdAmende#,(IdLicence,IdTournoi)#)

(nul autorisé)(nul autorisé)

2 clés étrangères2 clés étrangères

19

Associations de type symétrique (1-1)Règle N°4 : 3 cas possibles

C1C1 C2C2

IdC1IdC1 IdC2IdC2

AssociationAssociation

11 11

C1 (IdC1, ……., IdC2#)

C2 (IdC2, ………..)

C1 (IdC1, ……., IdC2#)

C2 (IdC2, ………..)

C1 (IdC1, …….)

C2 (IdC2, ……….., IdC1#)

C1 (IdC1, …….)

C2 (IdC2, ……….., IdC1#)

C1 (IdC1, …….)

C2 (IdC2, ………..)

Association (IdC1#, IdC2#)

C1 (IdC1, …….)

C2 (IdC2, ………..)

Association (IdC1#, IdC2#)

UNIQUEUNIQUE

Valeurs nulles autorisées ou passelon les valeursminimums

Valeurs nulles autorisées ou passelon les valeursminimums

Dans le cas d’uneassociation optionnelle

Dans le cas d’uneassociation optionnelle

20

Associations de type 1-1 : Exemple

EtudiantEtudiant StageStage

IneEtudiantNomEtudiantAdrEtudiant

IneEtudiantNomEtudiantAdrEtudiant

IdStageThémeStageEntreprise

IdStageThémeStageEntreprise0..10..10..10..1

EffectuerEffectuer

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

STAGE (IdStage, ThèmeStage, Entreprise)

EFFECTUER (IdStage#, IdEtudiant#)

ETUDIANT (IneEtudiant, NomEtudiant, AdrEtudiant)

STAGE (IdStage, ThèmeStage, Entreprise)

EFFECTUER (IdStage#, IdEtudiant#)

UNIQUEUNIQUE

21

Généralisation et Spécialisation : Règle N°5

• Chaque classe devient une relation• Une ‘super classe’ peut contenir un attribut de

spécialisation• Chaque relation de l’ensemble possède la même

clé primaire• La vraie spécialisation avec l’héritage

correspondant sera pris en charge avec les SGBD Objet-Relationnel

22

Généralisation et Spécialisation : Exemple

PersonnelPersonnelIdPersNomIndice

IdPersNomIndice

ChercheurChercheur

SpécialitéLaboratoireSpécialitéLaboratoire

EnseignantEnseignantSectionTitreHeuresCours

SectionTitreHeuresCours

IngénieurIngénieurGradeFonctionDomaine

GradeFonctionDomaine

23

Généralisation et Spécialisation : Exemple

PERSONNEL (IdPers, Nom, Indice, TypePers)

INGENIEUR (IdPers, Grade, Fonction, Domaine)

ENSEIGNANT (IdPers, Section, Titre, HeuresCours)

CHERCHEUR (IdPers, Spécialité, laboratoire)

PERSONNEL (IdPers, Nom, Indice, TypePers)

INGENIEUR (IdPers, Grade, Fonction, Domaine)

ENSEIGNANT (IdPers, Section, Titre, HeuresCours)

CHERCHEUR (IdPers, Spécialité, laboratoire)

Attribut de SpécialisationAttribut de Spécialisation