définitions schéma et instance de relation algèbre

2-1

Le modèle relationnel

DéfinitionsSchéma et instance de relationAlgèbre relationnelleOpérateurs fondamentauxOpérateurs dérivésExemples de requêtes algébriques

2-2

Bases de données relationnellesConcepts de base

modèle de données : organise les données dans des tablesune BD relationnelle est un ensemble de tables

Avantagesconcepts simplesbase mathématique solide

• théorie des ensembleslangages de requêtes puissantsoptimisation de requêtesthéorie pour la conception

Standard industrielSQL

2-3

Modèle relationnelRelation

Une relation R avec les attributs A={A1, A2, …, An} définis sur n domaines de valeurs D={D1, D2, ..., Dn} est un ensemble fini de n-uplets <d1, d2, ..., dn> tels que d1 ∈ D1, d2 ∈ D2, ..., dn ∈ Dn Notation: R(A1, A2, …, An) ou R(A1: D1, A2: D2, …, An: Dn)Ex. de domaines : les entiers, les ages, les noms, etc.

Structure tabulaire oùR est l'entête de la tableles attributs sont des colonnes (columns)chaque n-uplet (tuple) est une ligne (row)

2-4

Schéma et instance de relation

Schéma de relationun schéma de relation est un nom et un ensemble d'attributsun schéma de BD relationnelle est un ensemble de schémas de relations

Instance de relation (ou relation)une relation est une instance d'un schéma de relationune relation r de schéma R = {A1, ..., An} est un sous-ensemble du produit cartésien des domaines des attributs, i.e.,

r ⊆ Dom1 × Dom2 × … × Domn

2-5

Schémas de relations

EMP(ENO, ENAME, TITLE)PROJ (PNO, PNAME, BUDGET)WORKS(ENO,PNO, RESP, DUR)PAY(TITLE, SALARY)

Les attributs soulignés sont les clés (identificateurs de tuples)

ENO

EMP

ENAME TITLE

PROJ

PNO PNAME BUDGET

RESP

WORKS

PNOENO DUR

PAY

SALARYTITLE

2-6

Instances de relationsENO ENAME TITLE

E1 J. Doe Elect. Eng.E2 M. Smith Syst. Anal.E3 A. Lee Mech. Eng.E4 J. Miller ProgrammerE5 B. Casey Syst. Anal.E6 L. Chu Elect. Eng.E7 R. Davis Mech. Eng.E8 J. Jones Syst. Anal.

EMP

ENO PNO RESP

E1 P1 Manager 12

DUR

E2 P1 Analyst 24E2 P2 Analyst 6E3 P3 Consultant 10E3 P4 Engineer 48E4 P2 Programmer 18E5 P2 Manager 24E6 P4 Manager 48E7 P3 Engineer 36

E8 P3 Manager 40

WORKS

E7 P5 Engineer 23

PROJ

PNO PNAME BUDGET

P1 Instrumentation 150000

P3 CAD/CAM 250000P2 Database Develop. 135000

P4 Maintenance 310000P5 CAD/CAM 500000

TITLE SALARY

PAY

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

2-7

PropriétésBasées sur la théorie des ensembles

pas d'ordre entre les attributs pas d'ordre entre les tuples

• mais les résultats de requêtes peuvent être triéspas de tuples dupliqués

• mais les SGBD autorisent les doublesorienté valeur: un tuple est identifié par sa valeur de clé

Les valeurs d'attributs sont atomiquespas de structure

Degré ou ariténombre d'attributs

Cardinaliténombre de tuples

2-8

Algèbre relationnelle

Forme

<Opérateur><parametres> <Opérandes> → <Résultat>↓ ↓

Relation (s) Relation

2-9

Opérateurs de l'algèbre relationnelleFondamentaux

unionrenommagedifférencesélectionprojectionproduit cartésien

Dérivésintersectionjointuredivision

Compatibilité pour l'union même degréattributs correspondants définis sur le même domaine

2-10

Union

FormeR ∪ S={t | t∈R ou t∈S}

où R, S sont des relations, t est une variable tuplele résultat contient les tuples qui sont dans R ou dans S (élimination des doubles)R, S doivent être compatibles pour l'union

2-11

Renommage

Renomme certains attributs d’une tableForme

B1,…,Bn ←A1,…,An(R) =

{t’ | t∈R et t[Ai] = t’[Ai] pour i=1,n}où

R est une relation, t est une variable tuple{A1,…, An} est un sous-ensemble des attributs de R{B1,…, Bn} est un ensemble d’attributs

ExempleJOB ←TITLE (EMP) (ENO, ENAME, JOB)

2-12

Différence

Forme

R – S={t | t∈R et t∉S}

où R et S sont des relations, t est une variable tuple

le résultat contient tous les tuples qui sont dans R, mais pas dans S.

R – S ≠ S – R

R, S compatibles pour l'union

2-13

SélectionProduit un sous-ensemble horizontal de la relation

opérandeForme

σF(R)={t | t∈R et F(t) est vrai}où

R est une relation, t est une variable tupleF est une formule logique composée de

• opérandes: constantes ou attributs• opérateurs de comparaison

<, >, =, ≠, ≤, ≥• opérateurs logiques

∧, ∨, ¬

2-14

Exemple de sélection

ENO ENAME TITLEE1 J. Doe Elect. Eng.E2 M. Smith Syst. Anal.E3 A. Lee Mech. Eng.E4 J. Miller ProgrammerE5 B. Casey Syst. Anal.E6 L. Chu Elect. Eng.E7 R. Davis Mech. Eng.E8 J. Jones Syst. Anal.

EMP

ENO ENAME TITLE

E1 J. Doe Elect. EngE6 L. Chu Elect. Eng.

σ TITLE='Elect. Eng.'(EMP)

2-15

ProjectionProduit un partitionnement vertical d'une relationForme

ΠA1,…,An(R)={t[A1,…, An] | t∈R}

oùR est une relation, t est une variable tuple{A1,…, An} est un sous-ensemble des attributs de R

Note: la projection élimine les tuples dupliqués. Les SGBD relationnels (et SQL) permettent

projection (pure)projection sans élimination des doubles

2-16

Exemple de projection

PROJ

PNO BUDGET

P2 135000

P3 250000P4 310000P5 500000

PNAME

P1 150000InstrumentationDatabase Develop.

CAD/CAMMaintenanceCAD/CAM

ΠPNO,BUDGET(PROJ)

PNO BUDGET

P1 150000

P2 135000P3 250000P4 310000P5 500000

2-17

Produit cartésienSoient les relations

R de degré k1 , cardinalité n1S of degré k2 , cardinalité n2

Produit cartésien: R × S = {t [A1,…,Ak1

, Ak1+1,…,Ak1+k2] |

t[Ak1+1,…,Ak1+k2]∈S}

Le résultat de R × S est une relation de degré (k1+ k2) et contient (n1* n2) tuples, où chaque tuple est la concaténation d'un tuple de R avec un tuple de S.

2-18

Exemple de produit cartésienENO ENAME EMP.TITLE PAY.TITLE SALARY

E1 J. Doe Elect. Eng.E1 J. Doe Elect. Eng.E1 J. Doe Elect. Eng.E1 J. Doe Elect. Eng.

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

E2 M. Smith Syst. Anal.E2 M. Smith Syst. Anal.E2 M. Smith Syst. Anal.E2 M. Smith Syst. Anal.

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

E3 A. Lee Mech. Eng.E3 A. Lee Mech. Eng.E3 A. Lee Mech. Eng.E3 A. Lee Mech. Eng.

E8 J. Jones Syst. Anal.E8 J. Jones Syst. Anal.E8 J. Jones Syst. Anal.E8 J. Jones Syst. Anal.

EMP × PAY

ENO ENAME TITLE

E1 J. Doe Elect. EngE2 M. Smith Syst. Anal.E3 A. Lee Mech. Eng.E4 J. Miller ProgrammerE5 B. Casey Syst. Anal.E6 L. Chu Elect. Eng.E7 R. Davis Mech. Eng.E8 J. Jones Syst. Anal.

EMP

TITLE SALARY

PAY

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

2-19

Intersection

Forme

R ∩ S = {t | t∈R et t∈S}

= R – (R – S)

R, S compatibles pour l'union

2-20

JointureForme

R F(R.Ai,S.Bj) S={t[A1,…,An,B1,…,Bm]|t[A1,…,An]∈R et t[B1,…,Bm]∈Set F(R.Ai, S.Bj) est vrai}

oùR, S sont des relations, t est une variable tupleF (R.Ai, S.Bj) est une formule de sélection.

Se déduit du produit cartésienR F S = σF(R × S)

2-21

Exemple de jointureENO ENAME TITLE

E1 J. Doe Elect. EngE2 M. Smith Syst. Anal.E3 A. Lee Mech. Eng.E4 J. Miller ProgrammerE5 B. Casey Syst. Anal.E6 L. Chu Elect. Eng.E7 R. Davis Mech. Eng.E8 J. Jones Syst. Anal.

EMP

EMP EMP.ENO>WORKS.ENOWORKS

ENO PNO RESP

E1 P1 Manager 12

DUR

E2 P1 Analyst 24E2 P2 Analyst 6E3 P3 Consultant 10E3 P4 Engineer 48E4 P2 Programmer 18E5 P2 Manager 24E6 P4 Manager 48E7 P3 Engineer 36

E8 P3 Manager 40

WORKS

E7 P5 Engineer 23

EMP. ENAME TITLE WORKS. PNO RESP DURENO. ENO

E2 M. Smith Syst. Anal. E1 P1 Manager 12E3 A. Lee Mech. Eng. E1 P1 Manager 12E3 A. Lee Mech. Eng. E2 P1 Analyst 24E3 A. Lee Mech. Eng. E2 P2 Analyst 6E4 J. Miller Programmer E1 P1 Manager 12E4 J. Miller Programmer E2 P1 Analyst 24E4 J. Miller Programmer E2 P2 Analyst 6E4 J. Miller Programmer E3 P3 Consultant 10E4 J. Miller Programmer E3 P4 Engineer 48E5 B. Casey Syst. Anal. E1 P1 Manager 12E5 B. Casey Syst. Anal. E2 P1 Analyst 24E5 B. Casey Syst. Anal. E2 P2 Analyst 6E5 B. Casey Syst. Anal. E3 P3 Consultant 10E5 B. Casey Syst. Anal. E3 P4 Engineer 48E5 B. Casey Syst. Anal. E4 P2 Programmer 18E6 L. Chu Elect. Eng. E1 P1 Manager 12E6 L. Chu Elect. Eng. E2 P1 Analyst 24E6 L. Chu Elect. Eng. E2 P2 Analyst 6E6 L. Chu Elect. Eng. E3 P3 Consultant 10E6 L. Chu Elect. Eng. E3 P4 Engineer 48E6 L. Chu Elect. Eng. E4 P2 Programmer 18E6 L. Chu Elect. Eng. E5 P2 Manager 24… … … … … … …

2-22

Types de jointure

θ-jointurela formule F utilise l'opérateur <, >, ≠, ≤, ≥

Equi-jointurela formule F n'utilise que l'opérateur =R R.A=S.B S

Jointure naturelleEqui-jointure où on élimine un des deux attributs communsR S = ΠR.AσF(R × S)

2-23

Exemple de jointure naturelleENO ENAME TITLE

E1 J. Doe Elect. EngE2 M. Smith Syst. Anal.E3 A. Lee Mech. Eng.E4 J. Miller ProgrammerE5 B. Casey Syst. Anal.E6 L. Chu Elect. Eng.E7 R. Davis Mech. Eng.E8 J. Jones Syst. Anal.

EMP

EMP PAY

ENO ENAME TITLE SALARY

E1 J. Doe Elect. Eng. 55000M. Smith 70000E2 Analyst

E3 A. Lee Mech. Eng. 45000

E4 J. Miller Programmer 60000E5 B. Casey Syst. Anal. 70000E6 L. Chu Elect. Eng. 55000E7 R. Davis Mech. Eng. 45000E8 J. Jones Syst. Anal. 70000

TITLE SALARY

PAY

Elect. Eng. 55000Syst. Anal. 70000Mech. Eng. 45000Programmer 60000

2-24

Division

Soient les relationsR de degré k1 (R = {A1,…,Ak1

}) S de degré k2 (S = {B1,…,Bk2

})

Soient A = {A1,…,Ak1} et B = {B1,…,Bk2

} et B ⊆ A

alors, T = R ÷ S est une relation de degré k1-k2 [c-à-d, T(Y) où Y = A-B] tel que pour qu'un tuple t soit dans T, il doit être dans R en combinaison avec chaque tuple de S

2-25

Division

x1x2x3

XS

x1 y1x2 y1x3 y1x4 y1x1 y2x3 y2x2 y3x3 y3x4 y3x1 y4x2 y4x3 y4

X YR

T1 ← ΠY(R)T2 ← ΠY((S × T1) − R)T ← T1 − T2

y1y4

TY

2-26

Exemple de division

ENO PNO PNAME

E1 P1 Instrumentation 150000

BUDGET

E2 P1 Instrumentation 150000E2 P2 Database Develop. 135000

E3 P4 MaintenanceE4 P2 InstrumentationE5 P2 InstrumentationE6 P4E7 P3 CAD/CAME8 P3 CAD/CAM

310000150000150000310000250000250000

EMP

Maintenance

E3 P1 Instrumentation 150000

ENO

E3

EMP÷PROJ

PROJ

PNO BUDGET

P2 135000

P3 250000P4 310000

PNAMEP1 150000Instrumentation

Database Develop.

CAD/CAMMaintenance

E3 P2 Database Develop. 135000E3 P3 CAD/CAM 250000

2-27

Requêtes algébriquesEmp (Eno, Ename, Title, City)Project(Pno, Pname, Budget, City)Pay(Title, Salary)Works(Eno, Pno, Resp, Dur)

Villes où il y a des employés ou des projets?ΠCity(Emp) ∪ ΠCity(Project)

Villes où il y a des projets mais pas d'employés? ΠCity(Project) − ΠCity(Emp)

2-28

Requêtes algébriquesEmp (Eno, Ename, Title, City)Project(Pno, Pname, Budget, City)Pay(Title, Salary)Works(Eno, Pno, Resp, Dur)

Noms des projets de budget > 225?ΠPname(σBudget>225 (Project))

Noms et budgets des projets où travaille l'employé E1?ΠPname, Budget(Project (σEno=‘E1’ (Works)))Π Pname, Budget(σEmp.Eno=Works.Eno (Project × σ Eno=‘E1’ (Works)))

Employés qui travaillent dans chaque projet?Π Eno, Pno(Works) ÷ ΠPno(Project)

2-29

Arbre algébrique

Utile pour manipuler les requêtes (optimisation, vues)Exemple

ΠPname, Budget(Project (σEno=‘E1’ (Works)))

ΠPname, Budget

Works

σ Eno=‘E1’ Project

2-30

Mise à jour d'une BD relationnelle

Avec les opérateurs relationnelsInsertion de tuples = union de la relation contenant les tuples à insérer avec la relation déjà existante dans la baseSuppression de tuples = différence entre la relation existante dans la base et la relation contenant les tuplesà supprimer Modification = suppression suivie d'une insertion