introduction aux bases de données 2004/2005 · 2008-07-09 · introduction aux bases de données...

5.1

Introduction aux Bases de Données 2004/2005

Christian [email protected]

Ecole Nationale Supérieur de Physique de Strasbourg

Chapitre 5Le modèle relationnel

5.2Définition ExpressionsAlgèbre relationnelle Extensions

Le modèlerelationnel

Modélisation conceptuelle

IntroductionLes anciensmodèles logiquesPlan

Film

numFilmTitrePays

Client

numClientNom

*

DateDeDateAMagasin

*

location

Client

Location

C-L F-L

Film

Le langage SQL

Normalisation

SELECT FROM WHERE

=

F.Titre

Fonction

GroupeEmploye

Stockage physique

=

F.Titre

5.3

Le modèle relationnelCaractéristiques :

• Introduit 1969 par E.F. Codd, mathématicien chez IBM

• La base de la plupart de BD (Oracle, DB2, Informix, Teradata, mySQL, SQL Server, …)

• Une entité est traduite en relation (table)

• Absence de pointeurs : les liens sont stockés dans des nouvelles relations (tables)

• Simplicité : les requêtes sont écrites dans un langage non procédural (SQL) basé sur l’algèbre relationnelle

• Séparation entre schéma logique (« quoi ») et chemin d’accès (« comment »)

Définition ExpressionsAlgèbre relationnelle Extensions

5.4

La notion de base: la relation (la table)La table « Film »

Titre P AN Note DU LA CATCasablanca USA 1942 8,8 102 E DPerfect World USA 1993 7,0 138 E DThe Terminator USA 1984 7,9 108 E A

Die Hard USA 1988 8,0 131 E ASpeed USA 1994 7,1 116 E ALes Bronzés F 1978 7,2 87 F C

Le Pére Noël est une Ordure F 1982 7,5 88 F C

Pulp Fiction USA 1994 8,7 154 E D

The Silence of the Lambs USA 1991 8,5 118 E T,HThe Fugitive USA 1993 7,7 130 E A,TDances with Wolves USA 1990 7,7 180 E DDead Man USA 1995 7,5 121 E WMatrix USA 1999 8,5 136 E A,T,SF

Blade Runner USA 1982 8,2 117 E A,SFAlien USA 1979 8,3 117 E SF,HAliens USA 1982 8,2 137 E SF,H,A

Sen to Chihiro no Kamikakushi J 2001 8,6 125 J DAShrek USA 2001 8,1 90 E DA

Dracula USA 1992 7,1 128 E H

Dracula USA 1958 7,4 82 E HGood Bye, Lenin! D 2003 7,9 121 G C,DHead On D 2004 7,6 121 G DIndien A 1993 7,0 90 G C


5.5

Le domaineDomaine : un ensemble nommé de valeurs

Langue = {Français, Anglais, Allemand, Japonais, Espagnol}

Pays = {France, Belgique, USA, UK, Canada, Australie, Allemagne, Autriche, Suisse, Japon, Espagne}

Catégorie = {Comédie, Comédie dramatique, Thriller, Horreur, Dessin animé, Western, Science-fiction}

Produit cartésien:

AnglaisComédie dramatique

EspagnolComédie

JaponaisComédie

AllemandComédie

FrançaisComédie

AnglaisComédie

EspagnolHorreur

JaponaisHorreur

AllemandHorreur

FrançaisHorreur

AnglaisHorreur

EspagnolThriller

JaponaisThriller

AllemandThriller

FrançaisThriller

AnglaisThriller

EspagnolAction

JaponaisAction

AllemandAction

FrançaisAction

AnglaisAction

Languecatégorie

…Définition ExpressionsAlgèbre relationnelle Extensions

5.6

RelationDéfinition mathématique :Une relation est un sous-ensemble d’un produit cartésien

Exemple: la relation « < » sur les entiers [1-4]

Le modèle relationnel :Une relation est un sous-ensemble du produit cartésien d’une liste de domaines caractérisés par un nom

Titre Pays AnneeCasablanca USA 1942Perfect World USA 1993The Terminator USA 1984Head On D 2004Indien A 1993


5.7

Attribut / TupleAttribut : une colonne nommée d’une relationUn domaine peut exister plusieurs fois dans une relation : dans ce cas, il s’agit d’attributs différents ayant des noms différents.

Titre Pays Annee Note Duree LangueCasablanca USA 1942 8,8 102 EPerfect World USA 1993 7,0 138 EThe Terminator USA 1984 7,9 108 E

Die Hard USA 1988 8,0 131 ESpeed USA 1994 7,1 116 ELes Bronzés F 1978 7,2 87 F

Le Pére Noël est une Ordure F 1982 7,5 88 F

Pulp Fiction USA 1994 8,7 154 E

The Silence of the Lambs USA 1991 8,5 118 EThe Fugitive USA 1993 7,7 130 E

Tuple : la ligne d’une relation (≅ enregistrement)


5.8

Schéma/extension d’une relation

Film (Titre: string, Pays: string, Annee: int, Note: float, Duree: int, Langue: string, Categorie: string)

Schéma d’une relation :La structure d’une relation, invariant du temps.

Exemple:

Extension d’une relation : Le contenu (change au cours du temps)


5.9

Clef primaire« Primary key »

• Mécanisme permettant d’assurer l’unicité des tuples d’une relation

• Attribut, ou ensemble d’attributs, dont le contenu permet de caractériser, d’une façon unique, un tuple de la relation.

• « Surrogate key » une cléf ajoutée dans le cas ou aucun attribut ne peut servir

Exemples:

Film (numFilm, Titre, Pays, Annee, Note, Duree, Langue, Categorie)

Employé (Prenom, Nom, DateNaissance, DateEmbauche, Salaire, Magasin

Surrogate key


5.10

Passage du modèle E/A au relationnel

Entité

Champs

Relation

Attribut

Données :

Liens binaires (1:1) :

A B1..1 1..1 Ajout d’attributs de la

relation B à la relation AA B

0..1 1..1


5.11

Passage du modèle E/A au relationnelLiens fonctionnels (1:N) :

• Ajout de la clef primaire de la relation B comme attribut(s) à la relation A

• Ces attributs deviendront clefs étrangères « foreign key »

A B1..1*

Film Distributeur1..1*

Exemple:

Film (numFilm, …, numDistributeurFK)

clef primaire clef étrangère


5.12

Passage : liens maillés (N:M)

Film Client**

Exemple:Location

Film (numFilm, …)

Client (numClient, …)

Location (numFilmFK, numClientFK, DateDe, DateA, Magasin)

• Ajout d’une nouvelle relation correspondant au lien maillé• Les clefs primaires des deux relations liées deviennent

clefs étrangères dans la nouvelle relation.• Les propriétés des liens deviennent attributs dans la

nouvelle relation

Magasin,DateDe,DateA


5.13

La valeur nulle (NULL)Une valeur spéciale différente de toutes les valeurs qu’un attribut d’un domaine quelconque peut prendre

Utilisation:• Information manquante : (ex.: film sans langue)• Liens optionnels : si lien non existant, la clef étrangère est

NULLE• Jointures externes


5.14

Algèbre relationnelle• Langage « naturel « de manipulation de relations, introduit

par Codd.

• Opérateurs :• Des opérateurs ensemblistes : union, intersection,

différence, produit cartésien• Des opérateurs spécifiques : restriction, projection,

jointure, division• Des extensions : fermeture transitive, fonctions,

agrégation


5.15

UnionDéfinition : l’union des deux relations R1 et R2 est la relation contenant les tuples appartenant à R1 ou à R2.

Exemple:

∪

Résultat

R1 R2

• R1 ∪ R2• UNION (R1, R2)• APPEND (R1,R2)

Notations:

Classique Film3

Constance Film2

SF Movies1

NomnumDistributeur

Constance Film2

SF Movies1

NomnumDistributeur

Classique Film3

Constance Film2

NomnumDistributeurR1

R2

UNION (R1,R2)

Condition : les schémas sont égaux!Définition ExpressionsAlgèbre relationnelle Extensions

5.16

DifférenceDéfinition : la différence des deux relations R1 et R2 est la relation contenant les tuples appartenant à R1 et n’appartenant pas à R2

Exemple:

-

Résultat

R1 R2

• R1 – R2• DIFFERENCE (R1,R2)• REMOVE (R1,R2)• MINUS (R1,R2)

Notations:

Classique Film3

NomnumDistributeur

Constance Film2

SF Movies1

NomnumDistributeur

Classique Film3

Constance Film2


R2

DIFFERENCE (R1,R2)

Condition : les schémas sont égaux!Définition ExpressionsAlgèbre relationnelle Extensions

5.17

IntersectionDéfinition : l’intersection des deux relations R1 et R2 est la relation contenant les tuples appartenant à R1 et à R2

Exemple:

∩

Résultat

R1 R2

• R1 ∩ R2• INTERSECT (R1, R2)• AND (R1,R2)

Notations:

Constance Film3

NomnumDistributeur

Constance Film2

SF Movies1

NomnumDistributeur

Classique Film3

Constance Film2


R2

INTERSECT (R1,R2)

Condition: les schémas sont identiques!


5.18

Produit cartésienDéfinition : le produit cartésien des deux relations R1 et R2 est la relation ayant comme schéma l’union des schémas et contenant comme tuples toutes les combinaisons des tuples de R1 et R2

X

Résultat

R1 R2

• R1 X R2• PRODUCT (R1, R2)• TIMES (R1, R2)

Notations:


5.19

Produit cartésien : exemple

F877,21978FLes Bronzés6SF Movies1

E827,41958USADracula20SF Movies1

E1028,81942USACasablanca1SF Movies1

E1178,31979USAAlien15SF Movies1

F877,21978FLes Bronzés6Constance Film2

E827,41958USADracula20Constance Film2

E1028,81942USACasablanca1Constance Film2

E1178,31979USAAlien15Constance Film2

F877,21978FLes Bronzés6Classique Film3

E827,41958USADracula20Classique Film3

E1028,81942USACasablanca1Classique Film3

E1178,31979USAAlien15Classique Film3

LangueDureeNoteAnneePaysTitrenumFilmNomnumDistributeur

SF Movies1

Constance Film2

Classique Film3

NomnumDistributeur

F877,21978FLes Bronzés6

E827,41958USADracula20

E1028,81942USACasablanca1

E1178,31979USAAlien15

LangueDureeNoteAnneePaysTitrenumFilm

R1

R2

PRODUCT (R1,R2)


5.20

ProjectionDéfinition : la projection de la relation R1 sur les attributs (A1,A2, …,AN) est une relation ayant comme schéma seulement les attributs mentionnés et contenant tous les tuples de la relation R1Exemple: Résultat

A B

• ∏ A1,A2,…,AN (R1)• R1 [A1,A2,…,AN]• PROJECT (R1, A1, A2,

…,AN)

Notations:

R1

A1, A2,...,AN

∏ Titre, Pays (R1)G9071993AIndien23

G1217,62004DHead On22

E1287,11992USA Dracula19



AIndien

DHead On

USA Dracula

USADracula

PaysTitre


5.21

RestrictionDéfinition : la restriction d’une relation R1 par une condition et une relation contenant les tuplessatisfaisant cette condition

Exemple:

Résultat

A B

• σ CONDITION (R1)• R1 [CONDITION]• RESTRICT

(R1,CONDITION)

Notations:

R1

σ Annee>=2000 (R1)

G9071993AIndien23

G1217,62004DHead On22

G1217,92003DGood Bye, Lenin!21

E1287,11992USA Dracula19



CONDITION

G1217,62004DHead On22

G1217,92003DGood Bye, Lenin!21



5.22

Jointure (interne)Définition : la jointure des deux relations R1 et R2 sous une condition est une relation ayant comme schéma l’union des schémas et contenant comme tuples toutes les combinaisons des tuples de R1 et R2 satisfaisant cette condition.

La condition doit être du type:Ai θ Bj

Ai … un attribut de la relation R1Bj … un attribut de la relation R2

Résultat

R1 R2

• R1 R2CONDITION

• JOIN (R1, R2, CONDITION)

Notations:

Ai Bjθ

« (inner) join »

Jointure naturelle : omission de la condition s’il existe deux attributs ayant le même nom


5.23

Jointure interne : exemple

306/09/200405/09/2004318

203/09/200402/09/2004314

104/09/200403/09/2004213

103/09/200402/09/200424

114/02/200413/02/200423

numMagasindateadatedenumClientnumFilm

3E827,41958USADracula20

1E1378,21982USAAliens16

1E1178,31979USAAlien15

1E1178,21982USABlade Runner14

3F887,51982FLe Pére Noël est une Ordure7

3F877,21978FLes Bronzés6

2E13181988USADie Hard4

1E1087,91984USAThe Terminator3

3E1028,81942USACasablanca1

numDistributeurLangueDureeNoteAnneePaysTitrenumFilm

203/09/200402/09/20043141E1178,21982USABlade Runner

14

103/09/200402/09/2004242E13181988USADie Hard4

114/02/200413/02/2004231E1087,91984USAThe Terminator

3

numMagasin

dateadatedenumClient

location.numFilm

numDistributeur

LangueDureeNoteAnneePaysTitre

film.numFilm

Location

Film

JOIN (Location, Film,

Location.numFilm= Film.numFilm)


5.24

Semi - JointureDéfinition : la semi-jointure des deux relations R1 et R2 est une relation ayant le schéma de la relation R1 et contenant toutes les tuples participant à la jointure.

Equivalent à une jointure interne suivie par une projection sur les attributs de la relation R1.

Résultat

R1 R2

• R1 R2CONDITION

• JOIN (R1, R2, CONDITION)

Notations:

Ai Bjθ


5.25

Jointure externeDéfinition : la jointure externe des deux relations R1 et R2 sous une condition est une relation ayant comme schéma l’union des schémas et contenant comme tuples les tuples produites par une jointure interne et les tuples des relations R1 et R2 avec des valeurs NULL pour les attributs de l’autre relation

• R1 R2CONDITION

• EXT-JOIN (R1, R2, CONDITION)

Notations:

« (full) outer join »

Résultat

R1 R2

Ai Bj

θ

Résultat

R1 R2

Ai Bj

θ


5.26

05/09/2004318

02/09/2004314

03/09/2004213

02/09/200424

13/02/200423

datedenumClientnumFilm

Dracula20

Aliens16

Alien15

Blade Runner14

Le Pére Noël est une Ordure7

Les Bronzés6

Die Hard4

The Terminator3

Casablanca1

TitrenumFilm

Location

Film

EXT-JOIN (Location, Film, Location.numFilm = Film.numFilm)

Dracula20~~~

Aliens16~~~

Alien15~~~

Le Pére Noël estune Ordure

7~~~

Les Bronzés6~~~

Casablanca1~~~

~~05/09/2004318

Blade Runner1402/09/2004314

~~03/09/2004213

4

3

film.numFilm

02/09/2004

13/02/2004

Datede

Die Hard

The Terminator

Titre

24

23

numClientlocation.numFilm

Jointure externe: exemple


5.27

Jointures externes gauche et droite

Définition : a jointure externegauche des deux relations R1 et R2 sous une condition est une relation ayant comme schéma l’union des schémas et contenant comme tuples les tuples produites par une jointure interne et les tuples de la relation R1 avec des valeurs NULL pour les attributs de la relation R2

Définition de la jointure externe droite de façon similaire

« left join / right join»

• R1 R2CONDITION

• LEFT-JOIN (R1, R2, CONDITION)

Notations:

Résultat

R1 R2

Ai Bj

θ

Résultat

R1 R2

Ai Bj

θ

Left join

right join


5.28

05/09/2004318

02/09/2004314

03/09/2004213

02/09/200424

13/02/200423


Dracula20

Aliens16

Alien15

Blade Runner14


Les Bronzés6

Die Hard4

The Terminator3

Casablanca1

TitrenumFilm

Location

Film

LEFT-JOIN (Location, Film, Location.numFilm = Film.numFilm)

~~05/09/2004318


~~03/09/2004213

4

3

film.numFilm

02/09/2004

13/02/2004

Datede

Die Hard

The Terminator

Titre

24

23


Jointure externe gauche : exemple


5.29

05/09/2004318

02/09/2004314

03/09/2004213

02/09/200424

13/02/200423


Dracula20

Aliens16

Alien15

Blade Runner14


Les Bronzés6

Die Hard4

The Terminator3

Casablanca1

TitrenumFilm

Location

Film

RIGHT-JOIN (Location, Film, Location.numFilm = Film.numFilm)

Dracula20~~~

Aliens16~~~

Alien15~~~

Le Pére Noël estune Ordure

7~~~

Les Bronzés6~~~

Casablanca1~~~


4

3

film.numFilm

02/09/2004

13/02/2004

Datede

Die Hard

The Terminator

Titre

24

23


Jointure externe droite : exemple


5.30

DivisionDéfinition : la division de la relation R1 par la relation R2 est la relation contenant des tuples qui, concaténés à tout tuple de R2, font partie des tuples de R1

Condition :Schéma(R1) = Schéma(DIVISION(R1,R2)) + Schéma(R2) ÷

Résultat

R1 R2

• R1 ÷ R2• DIVISION (R1,R2)

Notations:


5.31

Division : exemples

1995EUSA

1994EUSA

1993EUSA

1992EUSA

1991EUSA

1990EUSA

1958EUSA

1942EUSA

2001JJ

1982FF

2004GD

1994EAU

1993EAU

1993GA

AnneelanguePays

1994E

1993E

Anneelangue

USA

AU

Pays

2001J

1994E

1993E

Anneelangue Pays

1942E

1994E

1993E

Anneelangue

USA

Pays

R1 R2

R3

R4

DIVISION (R1, R2)

DIVISION (R1, R3)

DIVISION (R1, R4)


5.32

Expressions algébriques : exempleDonner les titres des films distribués par « Constance Film »

=

Distributeur D

D.nom "Constance Film"

Film F

F.Titre

Résultat


(Version 1)

5.33

Expressions algébriques : exempleDonner les titres des films distribués par « Constance Film »

Distributeur D

=D.nom "Constance Film"

Film F

F.Titre

Résultat


(Version 2)

Remarque: la semi-jointure doit être remplacée par une jointure

5.34

Expressions algébriques : exemple

Donner les noms des clients ayant loués des films dans lesquels apparaît l’acteur « Michael Biehn »


=

Artist A

A.nom "Michael Biehn"Joue J

C.Nom

Résultat

Film F

Location L

Client C

5.35


Donner les adresses des magasins qui stockent au moins un film d’horreur, ainsi que les titres de ces films.


=

Categories Ca

Ca.code "H"Film F

F.Nom, B.Adresse

Résultat

Stock S

S.copies

Magasin B

> 0

5.36

Donner les noms des films réalisés par un artiste ayant de l’expérience professionnelle comme acteur aussi bien que comme réalisateur.



Film F

F.Nom

Résultat

Joue J Realisation R

J.numArtiste R.numArtiste=

R.numFilm F.numFilm=

5.37

Donner les adresses des magasins (aussi les distributeurs automatiques) et pour chaque Magasin le nom du directeur, s’il existe, et l’adresse du Magasin gérant s’il s’agit d’un distributeur.



B.Adresse, E.Nom, BG. Adresse

Résultat

B.numDirecteur

Employe EMagasin B

E.numEmploye

Magasin BG

B.numMagasinGestion BG.numMagasin

=

=

5.38

Extension : fermeture transitiveDéfinition : la fermeture transitive d’une relation R1 à deux attributs du même domaine contient les tuples R1 et les tuples {a,c} pour lesquelles {a,b} et {b,c} font partie de R1.

Remarque: cette opération demande une application itérative d’opérations de l’algèbre relationnelle!

La fermeture transitive n’est pas implémentée dans le langage SQL.

• τ (R1)• R1+

• CLOSE(R1)

Notations:

A1.ami A2.nom

Ami A2

A1.ami, A2.nom

Résultat

Ami A1

Ajout des tuples suivants à chaque itération:


5.39

Fermeture transitive : exempleAmit=0

DanielChristophe

ChristopheBarbara

BarbaraAntoine

aminom

DanielBarbara

ChristopheAntoine

aminom

DanielChristophe

DanielBarbara

ChristopheBarbara

ChristopheAntoine

BarbaraAntoine

aminom

DanielBarbara

DanielAntoine

ChristopheAntoine

aminom

Amit=1

DanielChristophe

DanielBarbara

ChristopheBarbara

DanielAntoine

ChristopheAntoine

BarbaraAntoine

aminom

Amit=2 = τ (Ami)


5.40

Extension : fonctionsLes fonctions permettent de se servir d’expressions arithmétiques pour affiner les restrictions, les jointures et les projections.

Exemples:

• R1 = RESTRICT (Employes, Salaire/echelon >1000)• R1 = JOIN (Employes, Echelon, (Salaire-1000)/200 =

echelon)• R1 = PROJECT (Film, Titre, 2004-Annee);


5.41

Extension : agrégationsLes agrégations ermettent de combiner les attributs de plusieurs tuples afin d’obtenir un seul résultat final agrégé, ou plusieurs résultats groupés.

Exemples:

9071993AIndien

1217,62004DHead On

1217,92003DGood Bye, Lenin!

827,41958USADracula

1258,62001JSen to Chihiro no Kamikakushi

887,51982FLe Pére Noël est une Ordure

877,21978FLes Bronzés

1028,81942USACasablanca

dureenoteanneepaystitre

1027,454USA

1258,63J

887,224F

1217,60,5D

90711A

"max(duree)""min(note""avg(2004-annee)"pays

125721,375

"max(duree)""min(note""avg(2004-annee)"

FilmAGREGAT (Film ; ; avg(2004-annee), min(note), max(duree))

AGREGAT (Film ; Pays ; avg(2004-annee), min(note), max(duree))

Fonctions disponibles: sum, avg, min, max, count


introduction aux bases de données 2004/2005 · 2008-07-09 · introduction aux bases de données...

Documents