cours deug mias 2Łme annØe - i5 · 2009. 9. 11. · base de donnØes cours deug mias 2Łme annØe...

Base de donnéesCours Deug MIAS 2ème année - I5

Version 1.0 - 12/02/02

E. [email protected]

Faculté des sciences et des techniquesDpt-Info, Bureau 203

Base de données – p.1/64

Introduction

� Éléments pour introduire les bases de données.

� Caractéristiques des bases de données(propriétés, normalisation).

� Éléments théoriques de base : Algèbrerelationnelle.

� Langage relationnel de référence : SQL.

� Introduction à la conception de basesrelationnelles.


Plan

� Introduction ;

� Expression relationnelle ;

� Algèbre des relations ;

� SQL ;

� Normalisation ;

� Contexte général des bases de données(généralités, Entité/Association...).


Introduction


Constat

� Comment gérer (mémoriser et traiter) un ensembled’étudiants (n

�

, nom, prénom, groupe, filière...) ?

Première solution : Fichiers typé ou fichiers textes.

Mais comment traiter les requêtes suivantes ?

n de Pierre Dupont ?

les étudiants du groupe 51 ?

moyenne de l’examen des redoublants en I5 ? ...

A chaque nouveau programme, de nouvelles questions ;

A chaque nouvelle question, de nouvelles procédures ;

Traitement dépendant de la structure des données stockéeset du langage de programmation.


Constat


�


� Première solution : Fichiers typé ou fichiers textes.

Mais comment traiter les requêtes suivantes ?

n de Pierre Dupont ?

les étudiants du groupe 51 ?

moyenne de l’examen des redoublants en I5 ? ...





Constat


�



� Mais comment traiter les requêtes suivantes ?

� n

�

de Pierre Dupont ?

� les étudiants du groupe 51 ?

� moyenne de l’examen des redoublants en I5 ? ...





Constat


�




� n

�

de Pierre Dupont ?



� A chaque nouveau programme, de nouvelles questions ;




Constat


�




� n

�

de Pierre Dupont ?




� A chaque nouvelle question, de nouvelles procédures ;



Constat


�




� n

�

de Pierre Dupont ?




� A chaque nouvelle question, de nouvelles procédures ;

� Traitement dépendant de la structure des données stockéeset du langage de programmation.


Solution

� Séparation entre données et traitementutilisation d’un Système de Gestion de Base

de Données (SGBD)

Exemples des SGBD :Système de réservation (Socrate, systèmes des compagniesaériennes...),

Bases de données bibliographiques et gestion desbibliothèques (catalogue de la BNF ou des BU, librairie ducongrès, Web...),

Vente par correspondance,

Comptes bancaires,

Jeux sur internet...


Solution

� Séparation entre données et traitementutilisation d’un Système de Gestion de Base

de Données (SGBD)

� Exemples des SGBD :

� Système de réservation (Socrate, systèmes des compagniesaériennes...),

� Bases de données bibliographiques et gestion desbibliothèques (catalogue de la BNF ou des BU, librairie ducongrès, Web...),

� Vente par correspondance,

� Comptes bancaires,

� Jeux sur internet...


Fonctions d’un SGBD

� Outils permettantd’insérer,de modifier,de supprimer,de contrôleret de rechercherefficacement des données spécifiques dans unegrande masse d’informations.

� Une interface entre les utilisateurs et la mémoiresecondaire facilitant le travail des utilisateurs.


Fonctions d’un SGBD1. Décrire les données des bases ;

2. Construire les bases ;

3. Interroger les bases ;

4. mettre à jour ;

5. Transformer les représentations des données ;

6. Assurer les contrôles d’intégrité ;

7. Assurer les contrôles de concurrence (multi-utilisateurs,BD réparties...) ;

8. Assurer les contrôles de sécurité.


Objectifs d’un SGBD (critères de Codd)

1. Indépendance physique ;

2. Indépendance logique ;

3. Manipulation des données par des langagesnon-procéduraux ;

4. Administration facilité des données ;

5. Efficacité des accès aux données ;

6. Redondance contrôlée des données ;

7. Cohérence des données ;

8. Partage des données ;

9. Sécurité des données.


Structure d’un SGBDApplications Terminaux Outils

SGBD Externe

SGBD interne

Système de gestion de fichiers

Disques


Structure d’un SGBD

� SGBD Externe :

� présentation et manipulation des données(concepteurs et utilisateurs),

� gestion des langages de requêtes de hautniveau,

� outils de présentation (états, formes...).

� SGBD Interne :

� placement et assemblage des données,

� gestion des liens et de l’accès rapide ;


Différents types de SGBD

� Les Bases hiérarchiques : base navigationnelles � gestion depointeurs entre enregistrements (structure arborescente) ;

Les Bases réseaux (20%) : plus ouvert ;

Les Bases relationnelles (75%) : données = tables ; basées surl’algèbre relationnelle et un langage déclaratif (SQL) ;

Les Bases déductives : données = prédicats ; langaged’interrogation = calcul des prédicats et logique du 1er ordre ;

Les Bases objets : données = instances de classes hiérarchisées ;données actives (propres méthodes d’interrogation etd’affectation) ; héritage = mécanisme de factorisation de laconnaissance ;

Les Bases relationnelle-objets.




� Les Bases réseaux (20%) : plus ouvert ;

Les Bases relationnelles (75%) : données = tables ; basées surl’algèbre relationnelle et un langage déclaratif (SQL) ;








� Les Bases relationnelles (75%) : données = tables ; basées surl’algèbre relationnelle et un langage déclaratif (SQL) ;









� Les Bases déductives : données = prédicats ; langaged’interrogation = calcul des prédicats et logique du 1er ordre ;









� Les Bases objets : données = instances de classes hiérarchisées ;données actives (propres méthodes d’interrogation etd’affectation) ; héritage = mécanisme de factorisation de laconnaissance ;








� Les Bases objets : données = instances de classes hiérarchisées ;données actives (propres méthodes d’interrogation etd’affectation) ; héritage = mécanisme de factorisation de laconnaissance ;

� Les Bases relationnelle-objets.


La CNIL


Le modèle relationnel


Introduction

� SGBD relationnelles : données en tables (ou relations),requêtes exprimées à l’aide d’opérateurs sur ces tables ;

Avantage : pour un ensemble d’informations à trouver, ondécrit juste les opérateurs sur les relations qui permettent deles atteindre (expressions relationnelles);

Approche beaucoup plus détachée de l’implémentationphysique ;

Simple, facile à comprendre et fidèle à un cadremathématique (algèbre relationnelle, concept de relation dela théorie des ensembles), logique.


Introduction

� SGBD relationnelles : données en tables (ou relations),requêtes exprimées à l’aide d’opérateurs sur ces tables ;

� Avantage : pour un ensemble d’informations à trouver, ondécrit juste les opérateurs sur les relations qui permettent deles atteindre (expressions relationnelles);

� Approche beaucoup plus détachée de l’implémentationphysique ;

� Simple, facile à comprendre et fidèle à un cadremathématique (algèbre relationnelle, concept de relation dela théorie des ensembles), logique.


Domaine et t-uple

� Un domaine : ensemble fini ou infini de valeursdistinctes que peut prendre une donnéeélémentaire (entiers, booléens...)

Un n-uplet ou t-uple : élémentdu produit cartésien d’un ensemble de domaines

, ,..., , noté tel que.

Exemple : si et alors

NB : Le produit cartésien peut être généralisé sur desensembles quelconques et, en particulier, des ensembles det-uples.


Domaine et t-uple


� Un n-uplet ou t-uple : élément ��

du produit cartésien d’un ensemble de domaines

� , ,..., �, noté � � �� tel que�� .

Exemple : si et alors

NB : Le produit cartésien peut être généralisé sur desensembles quelconques et, en particulier, des ensembles det-uples.


Domaine et t-uple


� Un n-uplet ou t-uple : élément ��

du produit cartésien d’un ensemble de domaines

� , ,..., �, noté � � �� tel que�� .

� Exemple : si

�� et

�� ! �

alors�� " �� $# �� %� # �� ! %� # �� %� # �� ! %� # �� %� #

�� ! % �

� NB : Le produit cartésien peut être généralisé sur desensembles quelconques et, en particulier, des ensembles det-uples.


Exemples de domaines

� Des domaines :

� nom = {Dupont, Martin, Robert, Denou,Bellamy, ...}

� prénom = {Franck, Isabelle, Adrien, Michelle,Jean, Gustave, ... }

� note = [0..20]

� ...

Des t-uples :nom prénom = {<Dupont,Franck>,<Dupont,Isabelle>, ..., <Martin,Franck>,<Martin, Isabelle>, ... }nom note = {<Dupont,0>, <Dupont,1>... }...


Exemples de domaines

� Des domaines :

� nom = {Dupont, Martin, Robert, Denou,Bellamy, ...}

� prénom = {Franck, Isabelle, Adrien, Michelle,Jean, Gustave, ... }

� note = [0..20]

� ...

� Des t-uples :

� nom � prénom = {<Dupont,Franck>,<Dupont,Isabelle>, ..., <Martin,Franck>,<Martin, Isabelle>, ... }

� nom � note = {<Dupont,0>, <Dupont,1>... }

� ...Base de données – p.17/64

Tables/Relations

� Une table ou relation : sous-ensemble du produitcartésien d’une liste de domaines. Elle estidentifiée par un nom ;

Représentation : tableau dont les lignes sont lest-uples. Les noms de colonne sont les attributs dela table. Chaque attribut est unique ;NB1 : L’ordre des lignes et des colonnes est sans importance ;

NB2 : Tous les t-uples doivent être distincts (notion de clé) ;

Le degré d’une relation : nombre d’attributs( ) ;La cardinalité d’une relation : nombre de t-uples( ou ).


Tables/Relations


� Représentation : tableau dont les lignes sont lest-uples. Les noms de colonne sont les attributs dela table. Chaque attribut est unique ;

NB1 : L’ordre des lignes et des colonnes est sans importance ;




Tables/Relations



�





Tables/Relations



�


�




Tables/Relations



�


�


� Le degré d’une relation : nombre d’attributs(

& '� � � (

) ;

La cardinalité d’une relation : nombre de t-uples( ou ).


Tables/Relations



�


�


� Le degré d’une relation : nombre d’attributs(

& '� � � (

) ;

� La cardinalité d’une relation : nombre de t-uples( )* + '� � � (

ou

,� � � ,

).


Schéma

� Le schéma d’une relation :

� le nom de la table,

� l’ensemble des noms des attributs,

� les contraintes,

� ...

Le schéma d’une base de données : ensemble desschémas de toutes les tables.


Schéma

� Le schéma d’une relation :

� le nom de la table,

� l’ensemble des noms des attributs,

� les contraintes,

� ...

� Le schéma d’une base de données : ensemble desschémas de toutes les tables.


Base de donnée relationnelle

� Une base de donnée relationnelle : base dedonnées dont le schéma est un ensemble deschémas de tables relationnelles et dont lesoccurrences sont des t-uples de ces tables.

� Une expression relationnelle : une expression desyntaxe précise composée de noms de relations,d’attributs, de constantes et d’opérations. Elledécrit une relation.

� Un langage relationnel : permet d’écrire desexpressions relationnelles.


Exemple de BDRÉtudiants noetu nom prénom

1 28936E Dupont Franck

2 46283B Dupont Isabelle

3 86719E Martin Adrien

4 99628C Robert Adrien

5 99321C Denou Michelle

6 99322C Dupont Isabelle- ./0 1 2 �43 �0 5 687 9 :3; 2 ./ 0 1 2 �43 �0 5 687 <


Exemple de BDRMatières codemat titre responsable diplôme

1 MIAS2I5 I5 E238 Deug MIAS


3 IUP2MA Automates E238 Licence IUP-MIAGE

4 LIL6 Systèmes E236 Licence Informatique

5 IUP2IS Systèmes E526 Licence IUP-MIAGE

6 MIAS2I3 Math-Info E426 Deug MIAS

7 LIL5 Algo E426 Licence Informatique- .= 30 �4>; > 5 67 ? :3; 2 .= 30 �4>; > 5 687 @


Exemple de BDRNotes noe codemat noteex notecc

1 99628C MIAS2I5 12 15.5

2 46283B MIAS2I6 8 11

3 46283B MIAS2I5 9.5 2

4 86719E IUP2MA 12 5.5

5 99321C LIL6 18 16.5

6 28936E MIAS2I5 13.5 13.5

7 86719E IUP2IS 8.5 10

8 99628C MIAS2I6 3 6

9 99321C LIL5 15 14.5

10 99322C MIAS2I5 12 15.5

11 28936E MIAS2I6 12 null- .A B0 > 5 687 ? :3 ; 2 .A B0 > 5 6 7 � �


Exemple de BDR«Tous les étudiants ayant leur moyenne à l’examen deI5»Select noetu, nom, prenom

From Etudiants, Notes, Matieres

Where Etudiants.noetu=Notes.noe And

Matiere.codemat=Notes.codemat And noteex>=10

And titre=’I5’;


Clé d’une relation (Def. 1)

� L’ensemble d’attributs permettant d’identifier demanière unique un t-uple ;

Permet de différencier chacun des t-uples d’unetable ;Attention : il peut y avoir plusieurs clés possiblespour une relation.



� L’ensemble d’attributs permettant d’identifier demanière unique un t-uple ;Permet de différencier chacun des t-uples d’unetable ;

� Attention : il peut y avoir plusieurs clés possiblespour une relation.


Exemple de BDRÉtudiants noetu nom prénom






6 99322C Dupont Isabelle


Exemple de BDRMatières codemat titre responsable diplôme



3 IUP2MA Automates E238 Licence IUP-MIAGE

4 LIL6 Systèmes E236 Licence Informatique

5 IUP2IS Systèmes E526 Licence IUP-MIAGE

6 MIAS2I3 Math-Info E426 Deug MIAS

7 LIL5 Algo E426 Licence Informatique


Exemple de BDRNotes noe codemat noteex notecc

1 99628C MIAS2I5 12 15.5

2 46283B MIAS2I6 8 11

3 46283B MIAS2I5 9.5 2

4 86719E IUP2MA 12 5.5

5 99321C LIL6 18 16.5

6 28936E MIAS2I5 13.5 13.5

7 86719E IUP2IS 8.5 10

8 99628C MIAS2I6 3 6

9 99321C LIL5 15 14.5

10 99322C MIAS2I5 12 15.5

11 28936E MIAS2I6 12 null


Algèbre relationnelle et SQL

� Algèbre relationnelle : Codd 70

� Formaliser les opérations sur les ensembles

� SQL : langage relationnel extrêmement répenduSelect From Where ...;

� Opérations de base : ensemble minimald’opérations telles que toute autre opération peutêtre déduite par celles-ci;

� opérations unaires (2) ;

� opérations ensemblistes (3) ;

� Opérations dérivées : opérations définies à partirdes cinq opérateurs de base.


Opérations de base unaires

� Éliminer des attributs ou des t-uples dans unerelation ;

� Projection : réduire le nombre d’attributs pour negarder que ceux utiles ;

� Sélection : ne garder que les t-uples significatifs.


Opérations unaires IProjection :

Arguments

C .DFE GH H H G DJI 6

Notation

K 7 LNMOQPR R R P MS . C 6 7

T; B U >V 0 . C G 3E G H H H G 3W 6 7 C . 3E G H H H G 3W 6avec

XZY �

Schéma

K . 3E GH H H G 3 W 6

Valeur t-uples de

C

dont on ne garde que les at-tributs 3E G H H H G 3W , les doublons sont sup-primés.

SQL Select distinct A,C From R;

NB

- . L . C 6 6\[ - . C 6et

] L . C 6 ] [ ] C ]T

3E GH H H G 3W

R


Exemple de projectionR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select Distinct A,C From R1;

D G : ' ^ (

A C

1 a c

2 d f

3 c d



� Tous les tuples de la relation se distinguent deuxà deux par leur projection selon la clé.

, , _ , :` > ' ( ,

Étudiants noetu nom prénom






6 99322C Dupont Isabelle


Opérations unaires IIRestriction ou Sélection :

Arguments


Notation

K 7 ab . C 6 7 : B � 2b . C 6 7 C .c 6 7

d> ` >V 0 . C G c 6 7 C> 50 ; �V 0 . C G c 6

avecc

uneformule logique sur les attributs (négation,conjonction ou disjonction entre termes de laforme

Dfe gDfh ou

Dfe g3 oùDfe etDfh sont des

attributs, 3 une constante etg

un opérateur decomparaison

Schéma

K . DiE G H H H G DjI 6Valeur t-uples de

Csatisfaisant la qualification Q.

SQL Select * From R Where Q;

NB

- . a . C 6 687 - . C 6

et

] a . C 6 ] [ ] C ]T

c

R


Exemple de sélectionR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select * From R1 Where B=’b’;

kQl 7 m n m ' ^ (

A B C

1 a b c

2 c b d


Opérations de base ensemblistes

� Combinaison de deux relations pour en formerune troisième ;

� Union : regroupement de données similaires ;

� Différence : réduction d’un ensemble dedonnées ;

� Produit cartésien : peu utilisé directement(combinaison de données).


Opérations ensemblistes IUnion :

Arguments

C .DiE GH H H G DjI 6 d .DiE GH H H G DjI 6

Notation

K 7 C o d 7 p � � B � . C G d 6 7

q; . C G d 67 Dsr r > � 2 . C G d 6

Schéma

K . DFE G H H H G DJI 6

Valeur Union ensembliste surtE u twv u H H H u tI telle que :K7 x0 y0 z C{ 0 z d |SQL Select * From R UNION

Select * from S;

NB

- . C} d 687 - . C 687 - . d 6

et

] C } d ] [ ] C ]�~ ] d ]

T

o

R S


Exemple d’unionR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select * From R1 Union Select * From S1;��

A B C

1 a b c

2 d a f

3 c b d

4 b g a


Opérations ensemblistes IIDifférence :

Arguments

C .DFE GH H H G DJI 6 d .DFE GH H H G DJI 6

Notation

K 7 Ci� d 7 t � � � . C G d 6 7

C> � B� > . C G d 6 7 = � � 1 5 . C G d 6Schéma


Valeur Différence ensembliste surtE u twv u H H H u tI telle que :K7 x0 y0 z C� 0 y z d |SQL Select

DiE GH H H G DjI From R

EXCEPT Select

DFE G H H H G DJI

from S;

NB

- . C� d 6 7 - . C 687 - . d 6

et] C� d ] [ ] C ]

T

�

R S


Exemple de différenceR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select * From R1 Except Select * From S1;��

A B C

1 a b c

2 c b d


Opérations ensemblistes IIIProduit cartésien :

Arguments

C .DFE GH H H G DJI 6 d . lE GH H H G l � 6

Notation

K 7 C u d 7 T; B 2 . C G d 6 7

T; B 2 1V 0 . C G d 67 K � �> 5 . C G d 6Schéma

K . DFE G H H H G DJI G lE GH H H G l � 6

Valeur Ensemble de tous les t-uples ayant�~ � attributs dont les � premiersforment un t-uple de

Cet les �

derniers un t-uple ded

SQL Select * From R,S;

NB

- . C u d 6 7 - . C 6~ - . d 6

et

] C u d ] 7 ] C ]�� ] d ]

T

u

R S


Exemple de produit cartésienR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select * From R1, S1;� " �

R1.A R1.B R1.C S1.A S1.B S1.C

1 a b c b g a

2 a b c d a f

3 d a f b g a

4 d a f d a f

5 c b d b g a

6 c b d d a fBase de données – p.37/64

Opérations dérivées

� Intersection ;

� Quotient ;

� Complément ;

� �

-jointure : opération majeure dans les SGBDR ;Elle permet d’enrichir des t-uples et de mettre enrelation des attributs ;

� Jointure naturelle ;

� Semi-jointure ;

� Jointure externe.


Opérations dérivées IIntersection :

Arguments

C .DiE GH H H G DjI 6 d .DiE GH H H G DjI 6

Notation

K 7 C� d 7 � �0 >; . C G d 6 7

D � 2 . C G d 6

Schéma


Valeur Intersection ensembliste surtE u twv u H H H u tI telle que :K 7 x0 y0 z C � 0 z d | 7

C� . C� d 687 d� . d� C 6

SQL Select * From R INTERSECT

Select * From S;

NB

- . C� d 687 - . C 687 - . d 6

et] C� d ] [ ] C ]

et

] C� d ] [ ] d ]T

�

R S


Exemple d’intersectionR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

Select * From R1 Intersect Select * From S1;��

A B C

1 d a f


Opérations dérivées IIQuotient :

Arguments

C .DFE GH H H G DJI 6 d .DJ� �E GH H H G DJI 6

Notation

K 7 C y d 7 CZ� d 7 t � � . C G d 6 7

D � 2 . C G d 6

Schéma

K . DFE G H H H G DJ� 6

Valeur t-uples qui concaténés à ceux ded

don-nent toujours un t-uple de

C.

K 7

C y d7 LN� . C 6� LN� . . LN� . C 6 u d 6� C 6

avec � 7 DFE G H H H G DJ�NB

- . C y d 67 - . C 6� - . d 6

et

] C y d ] [ ] C ]Concrètement, permet de chercher tous les t-

uples d’une table satisfaisant une sous-table. Per-

met de chercher les t-uples de

�

qui vérifient tous

les t-uples de

�

.

T

�

R S


Exemple de quotientR2 A B C D

1 a b c d

2 a b e f

3 b c e f

4 e d e d

5 e d e f

6 a b d e

S2 C D

1 e d

2 e f

��

A B

1 e d


Exemple de quotientR2 A B C D

1 a b c d

2 a b e f

3 b c e f

4 e d e d

5 e d e f

6 a b e d

S2 C D

1 e d

2 e f

��

A B

1 a b

2 e d


Opérations dérivées IIIComplément :

Arguments


Notation

K 7 � C 7 A r 0 . C G d 6 7

: B �r . C G d 6

Schéma


Valeur

K7 x0 y0 z tE u H H H u tI � 0 y z C |7. tE u H H H u tI 6� CNB

- . � C 67 - . C 6

et

] � C ]� ] C ]

T�

R


Opérations dérivées IV

�

-jointure (Jointure interne) :Arguments

� �� ¢¡ £ £ £¡ �F¤ ¥ � �¦� ¡ £ £ £¡ ¦�§ ¥

Notation

¨ © �«ª¬ � © ®°¯ ±³² � �¡ �¡ ´ ¥ ©®°¯ ±³² � �¡ � ¥

avec

´

une combinaison logique(

µ¡ ¶¡ ·) de termes de la forme

�¹¸ º¼» ¦¾½ etº¼» ¿ À ©¡ Á¡ Â¡ Ã¡ Ä¡ Å © Æ

Schéma

¨ �� ¢¡ £ £ £¡ �Ç¤ ¡ ¦�¢¡ £ £ £¡ ¦È§ ¥

Valeur t-uples de

�«É �

vérifiant la qualification Q.¨ © �ª¬ � © Ê � �«É � ¥SQL Select * From R,S Where Q ;

Select * From R Join S On Q;

NB

º¼» © Ë © Ì Ì : équijointure (sinon innéquijoin-ture) ;�ª¬ �

: autojointure.

T

ª¬

R S


Exemple de jointure interneR3 A B C

1 1 2 3

2 4 5 6

3 7 8 9

S3 D E

1 3 1

2 6 9

Select * From R3 Join S3 On B<D;Select * From R3, S3 Where B<D;�Í ÎÏ ¦ Á Ð � Í

A B C D E

1 1 2 3 3 1

2 1 2 3 6 9

3 4 5 6 6 9


Opérations dérivées VJointure naturelle :

Arguments

� ��³¡ £ £¡ �Ç¤ ¡ Ñ�³¡ £ £¡ ÑÈÒ ¥ � �¦� ¡ £ £¡ ¦È§ ¡ Ñ� ¡ £ £¡ ÑÈÒ ¥Notation

¨ © �ª¬ �

Schéma

¨ ��³¡ £ £¡ �i¤ ¡ ¦� ¡ £ £¡ ¦Ó§ ¡ Ñ�³¡ £ £¡ ÑÓÒ ¥

Valeur Ensemble de tous les t-uples ayant Ô Õ ² Õ » attributsdont les ² premiers et » derniers forment un t-uple de�

et les Ô Õ » derniers forment un t-uple de�

.¨ © �ª¬ � ©

ÖØ×¼ÙÛÚ ÜÞÝÚ ßS � Êà¸ á â� Ú Ò ãÚ ä�å ßÙ æ çå ßÙ � �«É � ¥ ¥

SQL Select * From R,S Where... ;

Select * From R Natural Join S;

NB Jointure la plus utilisée.

T

ª¬

R S


Exemple de jointure naturelleR4 A B C

1 a b c

2 d b c

3 b b f

4 c a d

S4 B C D

1 b c d

2 b c e

3 a d b

Select * From R4 Natural Join S4;

Select * From R4, S4 Where R4.B=S4.B And R4.C=S4.C ;�è éê � è

A B C D

1 a b c d

2 a b c e

3 d b c d

4 d b c e

5 c a d b Base de données – p.48/64

Opérations dérivées VISemi-jointure :

Arguments

� �� ¢¡ £ £ £¡ �F¤ ¡ Ñ� ¡ £ £ £¡ Ñ�Ò ¥ � �¦� ¡ £ £ £¡ ¦�§ ¡ Ñ�¢¡ £ £ £¡ Ñ�Ò ¥Notation

¨ © �¬ �

Schéma

¨ �� ¢¡ £ £ £¡ �i¤ ¡ Ñ�¢¡ £ £ £¡ ÑÓÒ ¥

Valeur Projection sur les attributs de

�

de la jointurenaturelle entre

�

et

�

.

¨ © �¬ � ©ÖØ× OÚ å å åÚ ×ìë Ú ßOÚ å å åÚ ß¹í � �ª¬ � ¥

T

¬

R S


Exemple de semi-jointureR4 A B C

1 a b c

2 d b c

3 b b f

4 c a d

S4 B C D

1 b c d

2 b c e

3 a d b

�î Ï � î

A B C

1 a b c

2 d b c

3 c a d


Opérations dérivées VIIJointure externe :

Arguments

� �� ¢¡ £ £ £¡ �F¤ ¥ � �¦� ¡ £ £ £¡ ¦�§ ¥

Notation

¨ © � åª¬ �4ï ¨ © �ñðª¬ �4ï ¨ © �ª ¬ ð �avec

´

une combinaison logique (

µ¡ ¶¡ ·) de termesde la forme

�¸ º¼» ¦¾½ et

º¼» ¿ À ©¡ Á¡ Â¡ Ã¡ Ä¡ Å © ÆSchéma

¨ �� ¢¡ £ £ £¡ �i¤ ¡ ¦�¢¡ £ £ £¡ ¦Ó§ ¥

Valeur t-uples de

�«É �

vérifiant la qualification Q.

+ Ajout les t-uples d’une relation (dite directrice) nevérifiant pas la condition en complétant les attributsde l’autre relation par des “null” (vide...). Les deuxrelations peuvent être directrices (position du point).

SQL Select * From R Left Outer Join S On Q;

Select * From R Right Outer Join S On Q;

Select * From R Full Outer Join S On Q;

Select * From R,S Where ...; avec

ò

du côté des at-tributs à compléter

T

åª¬

R S


Exemple de jointure externeR4 A B C

1 a b c

2 d b c

3 b b f

4 c a d

S4 B C D

1 b c d

2 b c e

3 a d b

Select * From R4 Left Outer Join S4;Select R4.A,R4.B,R4.C From R4, S4

Where R4.B=S4.B

ò

And R4.C=S4.C

ò;

C ?wó ôõ d ?

A B C D

1 a b c d

2 a b c e

3 d b c d

4 d b c e

5 b b f null

6 c a d bBase de données – p.52/64

Opérations de calcul

� Opérations “outils” qui sont des extensions desopérateurs de base et non fonction de cesopérateurs ;

� Comptage ;

� Somme ;

� Moyenne ;

� Minimum ;

� Maximum...


Opérations de calcul IComptage :Arguments

� �� ¡ £ £ £¡ �F¤ ¥

Notation

¨ © ö¯ Ô» ÷Qø ßOÚ å å åÚ ßí � � ¥ ©ö¯ù ² ÷ ßOÚ å å åÚ ß¹í � � ¥

ou

¨ ©ö¯ Ô» ÷Qø � � ¥

avec

ú ± ¿ û� £ £ » ü¡ Ñ¸ ¿À �� ¡ £ £ £¡ �F¤ Æ

Schéma

¨ � Ñ� ¡ £ £ £¡ ÑÓÒ¡ ö» ÷ ¥

ou

¨ � ö» ÷ ¥

Valeur Dénombrer les t-uples qui ont une mêmevaleur d’attributs en commun. Si les at-tributs ne sont pas spécifiés, c’est le nom-bre de t-uples.

SQL Select Count(*) From R [Group By ...];

NB

ý � ö¼¯ù ² ÷ � � ¥ ¥ © � ,ý � ö¼¯ù ² ÷ ßOÚ å åÚ ßí � � ¥ ¥ © » Õ �

þ ö¼¯ù ² ÷ � � ¥ þ © � etþ ö¼¯ù ² ÷ ßÿO � � í � � � ¥ þ © þ Ö ßÿO � � í � � � ¥ þ

T

ö¯ù ² ÷ ßOÚ å å åÚ ßñí

R


Exemple de comptageT A B C

1 a n 17

2 b o 14

3 c n 9

4 d p 13

5 e m 20

6 f m 10

Select Count(*) From T Group By B;

Select Count(*) From T;�� ¦ �

B Count

1 n 2

2 m 2

3 o 1

4 p 1

��

Count

1 6


Opérations de calcul IISomme :

Arguments

� ��³¡ £ £ £¡ �Ç¤ ¥

Notation

¨ © � ¯ Ô Ôø ßOÚ å å åÚ ß¹í � �¡ � ¥ ©�ù Ô ßOÚ å å åÚ ßí � �¡ � ¥

ou¨ © � ¯ Ô Ôø � �¡ � ¥

avecú ± ¿ û� £ £ » ü¡ Ñ¸ ¿ À �� ¡ £ £ £¡ �F¤ Æet

� ¿ À ��¡ £ £ £¡ �F¤ Æ

Schéma

¨ � Ñ� ¡ £ £ £¡ ÑÓÒ¡ ö» ÷ ¥

ou

¨ � ö» ÷ ¥Valeur Somme cumulée des valeurs d’un at-

tribu

�

pour chacune des valeurs dif-férentes des attributs. Si les attributs nesont pas spécifiés, c’est la somme des

�

pout tout les t-uples de�.

SQL Select Sum(...) From R Group By ...;

NB On peut généraliser au calculs de lamoyenne, du minimum, du maximum...

T

�ù Ô ßOÚ å å åÚ ßí

R


Exemple de sommeT A B C

1 a n 17

2 b o 14

3 c n 9

4 d p 13

5 e m 20

6 f m 10

Select Sum(C) From T Group By B;

Select Sum(C) From T;� � � ¦ � � �

B Sum

1 n 26

2 m 30

3 o 14

4 p 13

� � � � � �

Sum

1 83


Expression

� Une expression de l’algèbre relationnelle est uneexpression composée de relations et d’opérateursde l’algèbre relationnelle (de base ou non).

� Le résultat d’une expression est une relation surlaquelle on peut faire une autre opération.

N.B. En SQL, une table peut être une donnée de baseou le résultat d’une expression (notion de vueappelée “view”)


Exemple d’expressionR1 A B C

1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

�� ö © Ì� Ì �� ¦ © Ì� Ì � � � � � �

?

Select A From R1 Where B=’b’ And C=’d’ ;

A B C

1 c b d

A

1 c



1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

�� ö © Ì� Ì �� ¦ © Ì� Ì � � � � � �

?

Select A From R1 Where B=’b’ And C=’d’ ;C m7 a� � � � � . C � 6

A B C

1 a b c

2 c b d

A B C

1 c b d

A

1 c



1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

�� ö © Ì� Ì �� ¦ © Ì� Ì � � � � � �

?


A B C

1 a b c

2 c b dC m m7 a� � � � � . C m 6

A B C

1 c b d

A

1 c



1 a b c

2 d a f

3 c b d

S1 A B C

1 b g a

2 d a f

�� ö © Ì� Ì �� ¦ © Ì� Ì � � � � � �

?


A B C

1 a b c

2 c b dC m m7 a� � � � � . C m 6

A B C

1 c b d

L�� . a� � � � �� . C � 6 6 7 L� . C m m 6A

1 c


Propriétés

� !

-jointure, Union et Intersection sont commutatives

-jointure, Union et Intersection sont associatives

Si , alors


Propriétés

� !


� !


Si , alors


Propriétés

� !


� !


� � ´#" �� ´� � � � � $ � ´� µ ´#" � � �

Si , alors


Propriétés

� !


� !


� � ´#" �� ´� � � � � $ � ´� µ ´#" � � �

� � ´� � � � � � ´#" � � � $ � ´� ¶ ´#" � � �

Si , alors


Propriétés

� !


� !


� � ´#" �� ´� � � � � $ � ´� µ ´#" � � �

� � ´� � � � � � ´#" � � � $ � ´� ¶ ´#" � � �

� ��¡ £ £ £¡ � Ò �� ´ � � � � $ � ´ � ��¡ £ £ £¡ � Ò � � � �

% � & ' ± ( )+* ' ± ( , '� *- - - * '» .

Si , alors


Propriétés

� !


� !


� � ´#" �� ´� � � � � $ � ´� µ ´#" � � �

� � ´� � � � � � ´#" � � � $ � ´� ¶ ´#" � � �

� ��¡ £ £ £¡ � Ò �� ´ � � � � $ � ´ � ��¡ £ £ £¡ � Ò � � � �

% � & ' ± ( )+* ' ± ( , '� *- - - * '» .

� Si

/ '10 ( ) � '10 � ( , '� *- - - * '» ., alors��¡ £ £ £¡ � Ò �� ´ � � � � $ ��¡ £ £ £¡ � Ò �� ´ � ��°¡ £ £ £¡ � Ò¡ � ½ � � � � �


Propriétés

� � ´ ��¸ ¥ µ ´ Ì �¦¾½ ¥ � � Î Ï ´ Ì Ì � � $ � ´ ��¸ ¥ � � � Î Ï ´ Ì Ì � ´ Ì �¦¾½ ¥ � � �

si

sinon


Propriétés

� � ´ ��¸ ¥ µ ´ Ì �¦¾½ ¥ � � Î Ï ´ Ì Ì � � $ � ´ ��¸ ¥ � � � Î Ï ´ Ì Ì � ´ Ì �¦¾½ ¥ � � �

� � ´ � � � � � $ � ´ � � � � � ´ � � �

si

sinon


Propriétés

� � ´ ��¸ ¥ µ ´ Ì �¦¾½ ¥ � � Î Ï ´ Ì Ì � � $ � ´ ��¸ ¥ � � � Î Ï ´ Ì Ì � ´ Ì �¦¾½ ¥ � � �

� � ´ � � � � � $ � ´ � � � � � ´ � � �

� L32 . C ôõ b d 6 7456

L 2 78 9 . C 6 ô õ b L 2 7: 9 . d 6

si

;< h z c G < h z x DÈe | } x le |

L 2 . L=2 78 9> ? � Ù @ . C 6 ôõ b L 2 7 : 9> ? � Ù @ . d 6 6sinonx De | } x lh |BA c


Propriétés

� � ´ ��¸ ¥ µ ´ Ì �¦¾½ ¥ � � Î Ï ´ Ì Ì � � $ � ´ ��¸ ¥ � � � Î Ï ´ Ì Ì � ´ Ì �¦¾½ ¥ � � �

� � ´ � � � � � $ � ´ � � � � � ´ � � �

� L32 . C ôõ b d 6 7456

L 2 78 9 . C 6 ô õ b L 2 7: 9 . d 6

si

;< h z c G < h z x DÈe | } x le |

L 2 . L=2 78 9> ? � Ù @ . C 6 ôõ b L 2 7 : 9> ? � Ù @ . d 6 6sinonx De | } x lh |BA c

� �DC � � � � � $ � C � � ¥ � � � � �EC � � ¥ � � �


Remarque I

� Coût relatifs des opérateurs en terme de quantitéd’information produites (cas général)�

FGH

IJFG

H

KFL

H

MFN

H

OPN

Q

R


Remarque II

S Coût des opérateurs+ Possibilité de permutter l’ordre des opérations

(Cf. propriétés)Possibilité (et nécessité) de réduireconsidérablement la taille des relationsintermédiaires

S moins de place

S plus rapide

S Il existe plusieurs solutions pour obtenir unerelation : choisir la plus efficace.

Piste : en général, chercher à appliquer les opérateurs desélection et de projection le plus tôt possible.


Petit Exemple !R5

TUWV XV Y Z

A B C

1 a b 2

2 b b 12

3 a c 20

4 c c 1

5 b c 18

S5

T XV [V Y Z

A D E F

1 a k f 100

2 b b b 2

3 b a b 18

A/

B/

C/

A 84

30 24 54

6*7*2 84

5*7*2 70

5*2*2 20

B 70

30 24 54

3*3*2 24 42

5*7*2 70

20 20

C 32 (54)

30 24 54

5*2*2 2*2*3 32

3*2*2 2*2*3 24

5*3*2 30

20 20

Gain(A,C)

62%

(35.7%)


Petit Exemple !R5

TUWV XV Y Z

A B C

1 a b 2

2 b b 12

3 a c 20

4 c c 1

5 b c 18

S5

T XV [V Y Z

A D E F

1 a k f 100

2 b b b 2

3 b a b 18

A/

\^]`_ a bdce f gh bij kl mj n n

B/

\�]`_ a bdce f gh bij n k l mj n

C/

\^]`_ a bdce f gh b \^]_ e bi j n n kl \^]_ a b mj n n

A 84

30 24 54

6*7*2 84

5*7*2 70

5*2*2 20

B 70

30 24 54

3*3*2 24 42

5*7*2 70

20 20

C 32 (54)

30 24 54

5*2*2 2*2*3 32

3*2*2 2*2*3 24

5*3*2 30

20 20

Gain(A,C)

62%

(35.7%)


cours deug mias 2Łme annØe - i5 · 2009. 9. 11. · base de donnØes cours deug mias 2Łme annØe...

Documents