Planning yves.bertini@yahoo.fr

11

PlanningPlanningyves.bertini@yahoo.fryves.bertini@yahoo.fr

11 Séances11 Séances

2 devoirs en TD2 devoirs en TD–Jeudi 25 marsJeudi 25 mars–Jeudi 13 maiJeudi 13 mai

2

Supports et outils en ligne

Site Web

ybertini.free.fr/aes

3

Bases de Données

Introduction aux Bases de Données et au langage SQL

4

Informations

Consulter

Stocker

But des Bases de Données (BDD)

5

Plan

I - Définitions et notations II - Modèle relationnel III- Langage SQL

6

I- Définitions (1)

Base de données (BDD) :

ensemble de donnéesorganisées suivant un modèleconsultable par de nombreux utilisateurs

7

Exemple de BDD minimaliste

Annuaire téléphonique

NOM PRENOM TEL.

Benoit Jean 04 76 45 65 56

Bernard Francois 04 76 56 68 32

Bourdan Pierre 04 76 23 54 66

Tableau

Libre accès

8

I- Définitions (2)

SGBD pour support informatique(Système de Gestion de Bases De Données)

Ensemble de logiciels capables :concevoir, enregistrer, consulter les données sur

support informatiquesécuriser les données :

intégrité du contenu droits d 'accès

9

Exemples de SGBD

Microsoft QUERY Microsoft ACCESS

Deux SGBD tout en un

10

Domaines d 'utilisation

SGBD (Système de Gestion de Bases De Données)

Traiter un grand nombre d'objets similairesclients pour les assurancespatients dans les hôpitaux livres dans les bibliothèquescomptes dans les banques…

11

Plan

I - Définitions II - Modèle relationnel III- Langage SQL

12

Modèle Relationnel (1)

BDD

Langage pour Manipuler

Modèle pour Organiser

Yves:

But des BDD : gerer des informations. Deux questions : comment les stocker, et comment les consulter.

Yves:

But des BDD : gerer des informations. Deux questions : comment les stocker, et comment les consulter.

13

Modèle Relationnel (2)BDD relationnelle

BDD relationnelle = collection de relations relation = tableau = table

RELATIONRELATION

RELATIONRELATIONRELATIONRELATION

RELATIONRELATIONRELATIONRELATION

RELATIONRELATIONRELATIONRELATION

RELATIONRELATIONRELATIONRELATION

15

Modèle Relationnel (4)La Relation - définition

ANNUAIRE

NOM PRENOM TEL

Benoit Jean 04 76 45 65 56

Benoit Francois 04 76 56 68 32

Bourdan Jean 04 76 23 54 66

Bouvier Jacqueline 04 76 18 34 35

Nom : ANNUAIRE

Attributs :(NOM, PRENOM, TEL)

Occurrences :• 4 abonnés • décrits suivant valeurs d'attributs

16

Modèle Relationnel (5)Clé de relation

Clé d 'une relation: un ou plusieurs attribut(s) si la valeur de la clé est fixée

pas deux occurrences possèdent cette valeur

Question : quelle est la clé d 'ANNUAIRE ? Nom Prenom Tel

NOM PRENOM TEL

Benoit Jean 04 76 45 65 56

Benoit Francois 04 76 56 68 32

Bourdan Jean 04 76 23 54 66

Bouvier Jacqueline 04 76 18 34 35

17

Modèle Relationnel (6)Clé de relation

Clé d 'une relation: un ou plusieurs attribut(s) si la valeur de la clé est fixée

pas deux occurrences possèdent cette valeur

Question : quelle est la clé d 'ANNUAIRE ? Nom Prenom Tel

NOM PRENOM TEL

Benoit Jean 04 76 45 65 56

Benoit Francois 04 76 56 68 32

Bourdan Jean 04 76 23 54 66

Bouvier Jacqueline 04 76 18 34 35

1 n° téléphone 1 seul abonné

18

Modèle Relationnel (7)Schéma de relation

Schéma d 'une relation :

Nom relation ( clé, attribut1, attribut2, … )

Schéma de l'ANNUAIRE :

ANNUAIRE ( TEL ), NOM, PRENOM=

ANNUAIRE ( NOM, PRENOM, TEL )

19

Exemple de BDDCompagnie d 'Aviation

Polycopié page 2

3 relations : vol, pilote et avion. schéma des relations :

vol(numvol, depart, arrivee, numav, numpil, jdep, hdep,jarr, harr)

pilote(numpilote,nom,prenom) Avion(numavion, type, cap)

Schéma de la BDD relationnelle (à completer …)

20

Plan

I - Définitions II - Modèle relationnel III- Langage SQL

définitionun exemple de requête et traitementrequêtes typiques

21

SQL - langage relationnel

BDD

Modèle pour Organiser

Langage pour Manipuler

22

Structured Query Langage (SQL) langage d'interrogation (Anglais) inventé par IBM (1973)

SQL - langage relationnel Définition

Requête SQL

Réponse : relation

SGBD

23

Requête en Français

Exemple :

Nom du pilote prénommé Antoine ?

Démarche Traduction en SQL : 1. relations (tables) mises en jeu2. attributs3. conditions de filtrage

valeurs d’attribut imposées aux occurrences

Dupuis

PRENOM = 'Antoine'

24

Requête en SQLDéfinition

3 parties (SFW)

Select <liste attributs à afficher résultat>

From <listes relations>

Where <condition de filtrage des occurrences>;

25

Requête en SQLExemple

En Français :Nom du pilote prénommé Antoine ?

Traduction SQL :

SelectFromWhere

pilote.nompilotepilote.prenom = 'Antoine';

attribut relation.attribut

’Antoine’ ≠ pilote.nom

<listes relations>

<liste attributs à afficher résultat>

<condition de filtrage des occurrences>;

26

Requête en SQL Execution par SGBD

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine' ;

nom

Dupuis

RELATION_REP

27

Requête en SQL Traitement des Requêtes par SGBD

1. Choix des relations 2. Sélection des occurrences 3. Projection des attributs

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine' ;

28

Traitement des Requêtes1. Choix des relations

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine';

numvol depart arrivee numav numpil jdep hdep jarr harrV0001V0002V0003V0004V0005V0006V0007V0008

ParisLondresBerlinLondresBruxellesBerlinParisNew York

San FranciscoMoscouMadridMadridRomeAmsterdamBruxellesParis

A0001A0003A0002A0004A0006A0005A0001A0001

P0002P0001P0003P0006P0005P0001P0002P0003

15/05/9915/05/9915/05/9916/05/9916/05/9916/05/9916/05/9917/05/99

10:0010:3011:1506:2010:0014:3018:0003:00

16/05/9915/05/9915/05/9916/05/9916/05/9916/05/9916/05/9917/05/99

09:3017:0018:0009:3015:1017:0020:0021:30

numpilote nom prenomP0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

numavion type capA0001A0002A0003A0004A0005A0006

Boeing 747Airbus 320Airbus 300Boeing 737DC 10Boeing 747

420300280250120410

VOL

PILOTE

AVION

29

Traitement des Requêtes2. Sélection des occurrences

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine';

numpilote nom prenomP0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

30

Traitement des Requêtes (3)3. Projection des attributs

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine';

numpilote nom prenomP0001 Dupuis Antoine

31

Traitement des Requêtes (4)Résultat

Select pilote.nomFrom piloteWhere pilote.prenom = 'Antoine'

nomDupuis

32

Plan

I - Définitions II - Modèle relationnel III- Langage SQL

définitionun exemple de requête et traitementrequêtes typiques

Projection Sélection jointure

34

Requêtes typiquesProjection

R1 : Nom et prénom de tous les pilotes ?

Select

From

Where

pilote

pilote.nom, pilote.prenom

nom prenomDupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

Select attribut1 , attribut2

35

Requêtes typiques (1) Sélection

R2 : Attributs des pilotes de prénom Georges?

Select

From

Where

pilote

*

pilote.prenom = 'Georges'

numpilote nom prenomP0002P0004

SimonAndré

GeorgesGeorges

36

Conditions élémentaires (1)

Syntaxe : <Attribut> <Opérateur> <Valeur> Valeur

nombre : 12.6mots : 'Georges'dates : { d '1999-05-15' }

Opérateurs de comparaisons=, <, <=, >, >=, <>

Conditions élémentaires : pilote.prenom = 'Georges'avion.cap > 300

’minus’ ≠ ’MAJUS’

37

Conditions élémentaires (1) Trouver les erreurs

1.vol.depart = "Londres"

2.vol.numpil = P0002

3.avion.cap < '300'

4.avion.type = 'AIRBUS 300'

5.avion.type = 'Airbus300'

6.vol.jdep > {d 1999-05-15}

7.vol.numavion = 'A0002'

8.avions.numavion = 'A0002'

9.vol.numav ≠ 'A0002'

vol.depart = 'Londres'

vol.numpil = 'P0002'

avion.cap < 300

avion.type = 'Airbus 300'

avion.type = 'Airbus 300'

vol.jdep > {'d 1999-05-15'}

vol.numav = 'A0002'

avion.numavion = 'A0002'

vol.numav <> 'A0002'

47

Conditions composées (2) Combinaisons logiques :

AND : vol.depart = 'Londres' And vol.arrivee = 'Madrid'

Question : numéro des vols qui vérifient cette condition ?

OR :avion.type = 'Airbus 300' Or avion.type = 'Airbus 320'

Question : numéro des avions qui vérifient cette condition ?

V0004

A0002

A0003

48

Conditions composées (2)

vol.arrivee = 'Madrid' And vol.arrivee = 'Rome'

avion.cap <= 200 Or avion.cap >= 300

vol.depart = ‘Londres' Andvol.arrivee = ‘Moscou' Or vol.arrivee = ‘Madrid'

Vols qui atterrissent simultanément à Madrid et Rome !!

Avions de moins de 200 places ou de plus de 300 places.

Vols Londres Moscou ou ? MadridV0002

V0003

V0004

49

Conditions composées (2)

Vols pour Madrid ou Rome

Avions de capacité entre 200 et 300

Vols de Londres vers Moscou ou Madrid

vol.arrivee = 'Madrid' Or vol.arrivee = 'Rome'

avion.cap >= 200 And avion.cap <= 300

vol.depart = ‘Londres' And( vol.arrivee = ‘Moscou' Or vol.arrivee = ‘Madrid' )

50

Requêtes typiques (2) Sélection

R3 ' : Attributs des vols décollant pour Madrid le 15/05/99 ?

Select

From

Where

vol*

vol.arrivee='Madrid' And vol.jdep={d '1999-05-15'} 

numvol depart arrivee numav numpil jdep hdep jarr harrV0003 Berlin Madrid A0002 P0003 15/05/99 11:15 15/05/99 18:00

51

Requêtes typiquesJointure

R6 ' : Nom du pilote du vol V0001 ?

numvol depart arrivee numav numpil jdep hdep jarr harrV0001V0002V0003...

ParisLondresBerlin...

San FranciscoMoscouMadrid...

A0001A0003A0002...

P0002P0001P0003...

15/05/9915/05/9915/05/99...

10:0010:3011:15...

16/05/9915/05/9915/05/99...

09:3017:0018:00...

numpilote nom prenomP0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

référence

52

Schéma de BDDSchéma des relations + références

numvol depart arrivee numav numpil jdep hdep jarr harrV0001V0002V0003...

ParisLondresBerlin...

San FranciscoMoscouMadrid...

A0001A0003A0002...

P0002P0001P0003...

15/05/9915/05/9915/05/99...

10:0010:3011:15...

16/05/9915/05/9915/05/99...

09:3017:0018:00...

numpilote nom prenomP0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

numavion type capA0001A0002A0003A0004A0005A0006

Boeing 747Airbus 320Airbus 300Boeing 737DC 10Boeing 747

420300280250120410

53

Requêtes typiquesJointure naturelle R5 : Attributs des vols et des pilotes des vols?

Select

From

Wherepilote, vol

*

pilote.numpilote = vol.numpilnumpilote nom prenom numvol depart arrivee numav numpil jdep hdep jarr harr

P0002P0001P0003P0006P0005P0001P0002P0003

SimonDupuisFrançoisMathieuArthurDupuisSimonFrançois

GeorgesAntoineLucFrançoisLouisAntoineGeorgesLuc

V0001V0002V0003V0004V0005V0006V0007V0008

ParisLondresBerlinLondresBruxellesBerlinParisNew York

San FranciscoMoscouMadridMadridRomeAmsterdamBruxellesParis

A0001A0003A0002A0004A0006A0005A0001A0001

P0002P0001P0003P0006P0005P0001P0002P0003

15/05/9915/05/9915/05/9916/05/9916/05/9916/05/9916/05/9917/05/99

10:0010:3011:1506:2010:0014:3018:0003:00

16/05/9915/05/9915/05/9916/05/9916/05/9916/05/9916/05/9917/05/99

09:3017:0018:0009:3015:1017:0020:0021:30

54

Requêtes typiquesProduit cartésien

Select *

From pilote, vol

numpilote nom prenom numvol depart arrivee numav numpil jdep hdep jarr harr

P0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

V0001V0001V0001V0001V0001V0001

ParisParisParisParisParisParis

San FranciscoSan FranciscoSan FranciscoSan FranciscoSan FranciscoSan Francisco

A0001A0001A0001A0001A0001A0001

P0002P0002P0002P0002P0002P0002

15/05/9915/05/9915/05/9915/05/9915/05/9915/05/99

10:0010:0010:0010:0010:0010:00

16/05/9916/05/9916/05/9916/05/9916/05/9916/05/99

09:3009:3009:3009:3009:3009:30

... ... ... ... ... ... ... ... ... ... ... ...P0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

V0008V0008V0008V0008V0008V0008

New YorkNew YorkNew YorkNew YorkNew YorkNew York

ParisParisParisParisParisParis

A0001A0001A0001A0001A0001A0001

P0003P0003P0003P0003P0003P0003

17/05/9917/05/9917/05/9917/05/9917/05/9917/05/99

03:0003:0003:0003:0003:0003:00

17/05/9917/05/9917/05/9917/05/9917/05/9917/05/99

21:3021:3021:3021:3021:3021:30

Page 4

55

Requêtes typiquesProduit cartésien

Select *

From pilote, vol

Where pilote.numpilote = vol.numpilnumpilote nom prenom numvol depart arrivee numav numpil jdep hdep jarr harr

P0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

V0001V0001V0001V0001V0001V0001

ParisParisParisParisParisParis

San FranciscoSan FranciscoSan FranciscoSan FranciscoSan FranciscoSan Francisco

A0001A0001A0001A0001A0001A0001

P0002P0002P0002P0002P0002P0002

15/05/9915/05/9915/05/9915/05/9915/05/9915/05/99

10:0010:0010:0010:0010:0010:00

16/05/9916/05/9916/05/9916/05/9916/05/9916/05/99

09:3009:3009:3009:3009:3009:30

... ... ... ... ... ... ... ... ... ... ... ...P0001P0002P0003P0004P0005P0006

DupuisSimonFrançoisAndréArthurMathieu

AntoineGeorgesLucGeorgesLouisFrançois

V0008V0008V0008V0008V0008V0008

New YorkNew YorkNew YorkNew YorkNew YorkNew York

ParisParisParisParisParisParis

A0001A0001A0001A0001A0001A0001

P0003P0003P0003P0003P0003P0003

17/05/9917/05/9917/05/9917/05/9917/05/9917/05/99

03:0003:0003:0003:0003:0003:00

17/05/9917/05/9917/05/9917/05/9917/05/9917/05/99

21:3021:3021:3021:3021:3021:30

56

Requêtes typiquesJointure

NE PAS OUBLIER CONDITION DE JOINTURE

2 tables 1 condition de jointure 3 tables 2 conditions de jointure

57

Encore des requêtes

R6' : Nom du pilote du vol V0001 ?

Select

From

Where

pilote, vol

pilote.nom

pilote.numpilote = vol.numpil And vol.numvol = 'V0001'

nom

Simonjointure AND condition

58

Encore des requêtes

numvol type cap nom

V0001V0002V0003V0004V0005V0006V0007V0008

Boeing 747Airbus 300Airbus 320Boeing 737Boeing 747DC 10Boeing 747Boeing 747

420280300250410120420420

SimonDupuisFrançoisMathieuArthurDupuisSimonFrançois

R8 : Numéro des vols, type et capacité des avions, et nom de leurs pilotes ?

59

Encore des requêtes

R8 : Numéro des vols, type et capacité des avions, et nom de leurs pilotes ?

Select

From

Where

pilote, vol, avion

vol.numvol, avion.type, avion.cap, pilote.nom

pilote.numpilote = vol.numpil And vol.numav = avion.numavion

61

Numéro des avions Boeing 747

Numéro des vols au départ de Paris

Numéro des vols qu’effectuent les Boeing 747

Nom des pilotes des Boeing 747

numavion

A0001

A0006

numvol

V0001

V0007

numvol

V0008

V0007

V0001

V0005

nom

François

Simon

Simon

Arthur

62

Numéro des avions Boeing 747 Numéro des vols au départ de Paris Numéro des vols qu’effectuent les Boeing

747 Nom des pilotes des Boeing 747

63

Numéro des avions Boeing 747 Numéro des vols au départ de Paris Numéro des vols qu’effectuent les Boeing

747 Nom des pilotes des Boeing 747

64

Numéro des avions Boeing 747 Numéro des vols au départ de Paris Numéro des vols qu’effectuent les Boeing

747 Nom des pilotes des Boeing 747