BENELALLAM - Institut National de Statistique et d’Economie … · 2017-04-23 · – Règle 2:...

CoursdeBasesdedonnées

1er année,FilièreInformatique2016/2017

Pr.ImadeBENELALLAM

i.benelallam@insea.ac.mawww.benelallam.org

1Cours deBasesdedonnées

InstitutNationaldeStatistiqueetd’EconomieAppliquée

Chapitre3Lemodèlerelationneletalgèbre

relationnelle

Cours deBasesdedonnées 2

GénéralitésLemodèlerelationnel:• IlaétéintroduitparCodd en1970.• Parrapportauxautresmodèlesilajoutelarigueurdesconceptss'appuyantsur– lalogiquedesprédicats(algèbrerelationnelle);– lesschémasdedonnées,sousformedetables,facilesàutiliser;

– unlangagedehautniveaunonprocédural(onn'apasbesoindeprécisercommentobtenirl’information)

– unebonneindépendanceentrelemodèlephysiqueetlemodèlelogique:(lemodèlenecomporteaucunedescriptionphysiquedel’organisationdesdonnées)

Cours deBasesdedonnées 3

Généralités

• Onappelle Domaine,noté Di,unensemble,énuméré ((bleu,blanc,rouge),marques devoitures,…)ou non(N,nombres réels,intervallesnumériques,etc..);

• Une relationrest une partie (unsous ensemble)d’unproduit cartésien dedomaines D1 xD2 x...xDn ;– Leproduit catrésien définit unensemblefinid’éléments (ondit aussi tuples ou nuplets)appartenant àlarelation

Cours deBasesdedonnées 4

Généralités• Unerelationestreprésentéeparunetable

– unelignereprésenteuntuple,– unecolonnereprésentelescomposantsappartenantàunDomainedonnédéterminéparunattribut.

– Àchaque attribut correspondundomaine :Att(Domaine)

• Attributsetdomainesconstituent unschémaderelation.R,notéR(A1,A2,…An).

• UnerelationrestuneinstancedeRc'est-à-direunetablecontenantdestuples (provenantdumonderéelengénéral).

Cours deBasesdedonnées 5

Généralités• exemple : relationlivre(numéro,auteur,titre,editeur).

• UnschémadebasededonnéerelationnelleBestunensembledeschémasderelationsRi.

• UnebasededonnéerelationnellebestuneinstancedeB(c'estdoncunensemblederelationsr1,r2,...rn).

Cours deBasesdedonnées 6

Livre numéro Auteur Titre Editeur

tuple1 2004 " François le Berre" " les bases de données bretonnes"

"les éditions du far"

tuple2 1515 " François 1er" " la bataille de Marignan"

" historia"

tuple3 12 693 " gilles desrocques" " les bases du paramoteur"

" cavole"

tuple4 309 " gilles desrocques" " introduction a l'ULM"

" cavole"

Généralités• exemple : schémadelabasededonnéesrelationnellebibliothèque

Personne(numInscription,nom,prénom,adresse)Livre(numero,auteur,titre,éditeur)Auteur(nom,pr énom,adresse)Editeur(nom,adresse)empruntéPar(numInscription,numéro,date)écritPar(nom,numéro)éditéPar(nom,numéro).

• Lanotiondeclé,définiedanslemodèleentitésassociationestrepriseiciets'appliqueégalementauxassociations.

• Lorsqu'ilyaplusieurscléspossibles,onchoisitl'uned'entreelles,appelécléprimaire.Lesautrescléss'appellentcléscandidates(envisageables).

Cours deBasesdedonnées 7

PassageduMCDentité-associationaumodèlerelationnel

• Unschéma entité- associationpeut êtretraduit mécaniquement enunschémarelationnel.(Certains logiciels,WinDesign,BD_Designer ...lefontautomatiquement).

• Nousallons voir ici commentpratiquer "àlamain"pourpasserduModèle Conceptuel desDonnées entité-associationaumodèlerelationnel..

Cours deBasesdedonnées 8

PassageduMCDentité-associationaumodèlerelationnel

• Nousallonsexposercestechniquesens'appuyantsurl'exemplesuivant

Cours deBasesdedonnées 9

PassageduMCDentité-associationaumodèlerelationnel

• PourlatransformationduMCD,nousappliquonssuccessivementtroisrègles:– Règle1 :Lesentitésdeviennentdesrelations(outables);l'identifiantdel'entitédevientlaclédelarelation;lespropriétésdel'entitédeviennentdesattributs.

– Règle2 :Quandontrouveunecardinalité1,1,onajoutel'identifiantcibleàlarelationsource(ildevientunecléétrangère)

– Règle3 :Uneassociationdelaformex,n -------y,n devientunerelationàpartentièredontlacléestlaconcaténationdesdeuxidentifiantsdesentitésconcernéesetdontlesattributssontlespropriétés(siellesexistent)del'association.

Cours deBasesdedonnées 10

PassageduMCDentité-associationaumodèlerelationnel

• Règle1 :Lesentitésdeviennent desrelations(outables);l'identifiantdel'entitédevientlaclédelarelation;lespropriétésdel'entitédeviennentdesattributs.

• Onobtientdoncimmédiatement lesrelations:CLIENT(numcli,nomcli,adcli)COMMANDE(numcom,datcom)ARTICLE(numart,libelart,pu)REPRESENTANT (numrep,nomrep)REGION(nomreg,nbh)

Cours deBasesdedonnées 11

PassageduMCDentité-associationaumodèlerelationnel

• Règle2 :Quandontrouveunecardinalité1,1,onajoutel'identifiantcibleàlarelationsource(ildevientunecléétrangère)

• L'entitéCOMMANDE(doncrelation)possèdedeuxassociationsavecdescardinalités1,1.Onmodifiealorsleschémarelationnelci-dessusdelafaçonsuivanteenappliquantlarègle2:COMMANDE(numcom,datcom,#numrep,#numcli)

Cours deBasesdedonnées 12

PassageduMCDentité-associationaumodèlerelationnel

• Règle3 :Uneassociationdelaformex,n -------y,ndevientunerelationàpartentièredontlacléestlaconcaténationdesdeuxidentifiantsdesentitésconcernéesetdontlesattributssontlespropriétés (siellesexistent)del'association.

• Onobtientdonclesrelationssupplémentairessuivantes:PROSPECTION (#numrep,#nomreg)LIGNE_COMMANDE(#numcom,#numart,qtecom)

Cours deBasesdedonnées 13

PassageduMCDentité-associationaumodèlerelationnel

• Endéfinitiveonaboutitaumodèlelogiquedesdonnées(dansleformalismerelationnel):CLIENT(numcli,nomcli,adcli)COMMANDE(numcom,datcom,#numrep,#numcli)ARTICLE(numart,libelart,pu)REPRESENTANT(numrep,nomrep)REGION(nomreg,nbh)PROSPECTION(#numrep,#nomreg)LIGNE_COMMANDE(#numcom,#numart,qtecom)

• NB:lesigne#permetderepérerlesclésétrangèresetnefaitpaspartiedunomdel'attribut.

Cours deBasesdedonnées 14

Exercice

Cours deBasesdedonnées 15

L'algèbrerelationnelle• L'algèbrerelationnelle estunethéoriemathématique

prochedela théoriedesensembles quidéfinitdesopérationsquipeuventêtreeffectuéessurdes relations -des matrices contenantunensemblede nuplets.

• L'algèbrerelationnelleaétéinventéeen 1970 par EdgarFrankCodd,ledirecteurderechercheducentre IBM de SanJosé.Cettealgèbreestconstituéed'unensembled'opérationsformellessurles relations.

• Lesopérationsrelationnellespermettentdecréerunenouvellerelation(table)àpartird'opérationsélémentairessurd'autrestables(parexemplel'union,l'intersection,ouencoreladifférence).

Cours deBasesdedonnées 16

L'algèbrerelationnelle• Lesprincipesdel'algèbrerelationnellesontbeaucouputilisésdenosjoursparles SGBD pourlagestiondes basesdedonnées informatiquescommele SQL, DBase,etc.

• Cependant,les basesdedonnéesrelationnelles nefonctionnent pastoutàfaitselonlesrèglesensemblistes del'algèbrerelationnelle.

• Eneffet, touteslescontraintessontgéréesen-dehorsdelathéorie.

Cours deBasesdedonnées 17

L'algèbrerelationnelle• L'algèbrerelationnelleestconstituéed'opérateursqui

prennentenargumentdesrelations:

– relationsconstantes(parexempledécritesenextension),– relationsvariables:nomsderelationsdontlecontenupeutvariermaisdontl'arité estfixée.

• Pourplusdecommodité,onsépare lesopérateurs endeuxcatégories:– opérateursdebase:union,différence,produit,projection,sélection,

– opérateursadditionnelsquis'exprimentàpartirdesopérateursdebase:intersection,quotient,c-jointure,jointurenaturelle,semi-jointure.

Cours deBasesdedonnées 18

Exemple:Sélection

Cours deBasesdedonnées 19

N° commande Date Montant

28293052

OctobreOctobreNovembreDécembre

198620241610512

Exemple :considérons larelation :commande

«donnezlescommandespassées,enmoisdeNovembre».

Lacondition peut contenir plusieurs critères.

N° commande Date Montant

30 Novembre 1610

Exemple:Projection

Cours deBasesdedonnées 20

N° Etudiant Nom Département Adresse Dpt

521642251662

KarimRachidNajatKarim

InformatiqueMathématiqueInformatiqueInformatique

CasaKhoribgaSettatSettat

Nom Département

KarimRachidNajat

InformatiqueMathématiqueInformatique

C’estuneopérationunairequiconsisteàsupprimerlescolonnesd’unerelation.Exemple:considéronslesrelations:

Laprojectionsurnom,département,donne:

Département Adresse Dpt

InformatiqueMathématiqueInformatiqueInformatique

CasaKhoribgaSettatSettat

Laprojectionsurdépartement,adresse,donne:

Exemple:Union

Cours deBasesdedonnées 21

N° Employé nom

151718

KarimNajatRachid

N° Employé nom

321

JamalSophia

N° Employé Nom Employé

321151718

JamalSophiaKarimNajatRachid

Ouvrier Cadre

Donnezl’ensembledes«EMPLOYES»

Employé

Exemple :Considéronslesrelations.

L’unionestuneopérationbinaireCetteopérationn’estpossiblequesurdesrelationsayantlesmêmeattributs.

Ellepermetdefusionnerlesdeuxrelationsenuneseule.

Exemple:intersection

Cours deBasesdedonnées 22

Exemple :Considéronslesrelations.

N° Employé Nom employé

10121315

TarikJamalRachidKarim

N° Employé Nom employé

131415

RachidLeilaKarim

N° Employé Nom Employé

1315

RachidKarim

Ingénieur Chef de service

«Donnezlesnumérosetnomsdeschefsdeservicequisontingénieurs ».

Exemple:différence

Cours deBasesdedonnées 23

«DonnezlenomdesétudiantsquisonttoujoursinscritsàuneU.V »

nom étudiant Unité valeur

TarikJamalTarikRachidJamal

MathsMathsPhysiqueChimieChimie

nom étudiant unité valeur

TarikJamalRachid

MathsMathsChimie

Nom Etudiants Unité Valeur

TarikJamal

PhysiqueChimie

Inscrits Reçus

Exemple :soient lesdeux relations :

Exemple:division

Cours deBasesdedonnées 24

Exemple :considérons les relations suivantes:

Elève Professeur

LeilaRachidKarimLeilaRachidLeilaKarim

FadouaSalimFadouaSalimFadouaAmineAmine

Elève

LeilaRachid

Donnez lenomdes professeurs qui enseignent conjointement aux élèves figurent dans ladeuxième relation ».

Professeur

FadouaSalim

Cetteopération binaire concerne une relation qui est «divisée »parune autrerelation contenant exclusivement desattributs de lapremière relation.

Ils’agit dedéterminer lesoccurrences de lapremière relationqui sont associées àtoutes les occurrences delaseconde.

Exemple:produit

Cours deBasesdedonnées 25

Couleur Edition

RougeBlancVert

Edition luxeBrochéCartonné

Titre Auteur

XY

RachidKarim

répondre àlaquestion associant les titres, auteurs, couleurs etéditions ?

LIVRE

Exemple :Soient lesdeux relations :

Titre Auteur Couleur Edition

XXXYYY

RachidRachidRachidKarimKarimKarim

RougeBlancVertRougeBlanc Vert

Edition luxeBrochéCartonnéEdition luxeBrochéCartonné

Exemple:thêtaproduit

Cours deBasesdedonnées 26

Nom chef Salaire

RachidKarim

2500012000

Exemple :Soient lesdeux relations :Nom Salaire

E1E2E3

20000100006000

«Donnezlenomdesemployés quigagnentplusqu’unchefdeservice»

Employé Chef

Nom Employé Salaire Employé Nom chef Salaire chef

E1E2E3E1E2E3

2000010000600020000100006000

RachidRachidRachidKarimKarim Karim

250002500025000120001200012000

Nom Employé Salaire Employé Nom Chef Salaire Chef

E1 20000 Karim 12000

Exemple:Jointurenaturelle

Cours deBasesdedonnées 27

N° Service Nom Service

17151428

ComptabilitéInformatiqueAchatVente

Exemple :Soient lesdeux relations :N° Employé Nom Employé N° Service

23253723

TarikLeilaJalilTarik

17151714

Pour répondre àlaquestion :«Donnezlenomdesemployésetleurservice », onutilise une jointure naturelle

N° Employé Nom Employé N° Service Nom Service

23253723

TarikLeilaJalilTarik

17151714

ComptabilitéInformatiqueComptabilitéAchat

Exemple:semi-jointure

Cours deBasesdedonnées 28

N° Bureau BATIMENT

40424762

A1B3B3C1

Exemple :N° Employé Nom N° Bureau

2383697085

TarikLeilaJalilKamalFarida

4718034047

Donnez l’opération qui permet d’avoir larelation suivante :

N° Employé Nom N° Bureau

237085

TarikKamalFarida

474047

SchémarelationneletContraintesd’Intégrité

• Soitleschémasuivant:

Cours deBasesdedonnées 29

SchémarelationneletContraintesd’Intégrité

• Clefprimaire:attributouensembled’attributspermettantd’identifieruntuple d’unerelation;

• Domaineprimaire:domainedéfinid’unattributdelaclef;

• Clefétrangère:attributdéfinisurundomaineprimaireetquin’estpasclefprimairedanssarelation;

• Degréd’unerelation:nombred’attribut;• Cardinalitéd’unerelation:nombredetuples;• Relationdynamique:possèdeuneclefétrangère;• Relationstatique:pasdeclefétrangère,indépendantedesautres.

Cours deBasesdedonnées 30

SchémarelationneletContraintesd’Intégrité

• Enfait,dansuneBDrelationnelle,desrèglesdecohérencedoiventêtreconstammentvérifiéespourgarantirlavaliditédesdonnées.

• CesrèglesdecohérencesontappeléesContrainted’Intégrité;

• OndistinguedeuxcatégoriesdeCI:– Dépendantesdel’application– Liéeauxconceptsdurelationnel

Cours deBasesdedonnées 31

SchémarelationneletContraintesd’Intégrité

– LesCILiéesauxconceptsdurelationnel• CIdedomaine:toutevaleurd’unattributdoitapparteniràsondomainededéfinition

• CIderelation:toutevaleurdeclefprimairedoitexisteretêtreunique(Remarque:unevaleurinexistantedanslabasededonnéesestappeléevaleurnulle(NULL)(valeurimpossibleouvaleurinconnue))

• CIderéférence:toutevaleurdeclefétrangèredoitexisterpourlaclefprimaireassociée,i.e.

Cours deBasesdedonnées 32

SchémarelationneletContraintesd’Intégrité

Cours deBasesdedonnées 33

SchémarelationneletContraintesd’Intégrité

• CIapplicativesoudynamiques :toutecontraintedecohérenceliéeàl’application– Exemples:

• Pasderecouvrementdevolsfaitsparlemêmepiloteoulemêmeavion

• Duréed’unvoltjrs>30minutes• Salairenepeutpasdécroître.

Cours deBasesdedonnées 34