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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
NomnumDistributeurR1
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
NomnumDistributeurR1
R2
INTERSECT (R1,R2)
Condition: les schémas sont identiques!
Définition ExpressionsAlgèbre relationnelle Extensions
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:
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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
E827,41958USADracula20
LangueDureeNoteAnneePaysTitrenumFilm
AIndien
DHead On
USA Dracula
USADracula
PaysTitre
Définition ExpressionsAlgèbre relationnelle Extensions
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
E827,41958USADracula20
LangueDureeNoteAnneePaysTitrenumFilm
CONDITION
G1217,62004DHead On22
G1217,92003DGood Bye, Lenin!21
LangueDureeNoteAnneePaysTitrenumFilm
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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θ
Définition ExpressionsAlgèbre relationnelle Extensions
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
θ
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
5.28
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
LEFT-JOIN (Location, Film, Location.numFilm = Film.numFilm)
~~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 gauche : exemple
Définition ExpressionsAlgèbre relationnelle Extensions
5.29
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
RIGHT-JOIN (Location, Film, Location.numFilm = Film.numFilm)
Dracula20~~~
Aliens16~~~
Alien15~~~
Le Pére Noël estune Ordure
7~~~
Les Bronzés6~~~
Casablanca1~~~
Blade Runner1402/09/2004314
4
3
film.numFilm
02/09/2004
13/02/2004
Datede
Die Hard
The Terminator
Titre
24
23
numClientlocation.numFilm
Jointure externe droite : exemple
Définition ExpressionsAlgèbre relationnelle Extensions
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:
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions
(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
Définition ExpressionsAlgèbre relationnelle Extensions
(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 »
Définition ExpressionsAlgèbre relationnelle Extensions
=
Artist A
A.nom "Michael Biehn"Joue J
C.Nom
Résultat
Film F
Location L
Client C
5.35
Expressions algébriques : exemple
Donner les adresses des magasins qui stockent au moins un film d’horreur, ainsi que les titres de ces films.
Définition ExpressionsAlgèbre relationnelle Extensions
=
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.
Définition ExpressionsAlgèbre relationnelle Extensions
Expressions algébriques : exemple
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.
Définition ExpressionsAlgèbre relationnelle Extensions
Expressions algébriques : exemple
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:
Définition ExpressionsAlgèbre relationnelle Extensions
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)
Définition ExpressionsAlgèbre relationnelle Extensions
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);
Définition ExpressionsAlgèbre relationnelle Extensions
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
Définition ExpressionsAlgèbre relationnelle Extensions