Travaux Dirigés TD_analyse d`un système d`information

Terminale GSI B : services fournis par le système d'information et technologies associées
Eric CREPIN page 1
Travaux Dirigés TD_analyse d’un système d’information
Thème : Analyse d’un système d’information
travail à faire pour le : séquence de T.D.
Objectifs
Les objectifs de ce TD sont de vous permettre :
 Terminale B.1 La gestion des données (Interrogation d’une base de données)
 Comprendre la modélisation d’un système d’information
 Interroger un modèle relationnel
 Gérer une base de donnée relationnelle
 D’interroger une base de données
Le système d’information étudié
Modèle conceptuel des données
Dictionnaire des données
Mnémonique
Description
Type
Format
Remarque
Nom
Nom d’une personne
AN
50
obligatoire
Prenom
Prénom d’une personne
AN
50
obligatoire
Nom fonction
Nom d’une fonction (proviseur,
proviseur adjoint, conseiller
principal d’éducation, surveillant
professeur, élève, parent, assistante
sociale)
AN
50
obligatoire
Numero reunion
Numéro de la réunion
N
3
identifiant
Libelle reunion
Libellé de la réunion
AN
250
obligatoire
Date reunion
Date de la réunion
date
obligatoire
Heure reunion
Heure de la réunion
heure
obligatoire
Duree reunion
Durée de la réunion
heure
obligatoire
Numero salle
Numéro de la salle de la réunion
N
4
obligatoire
Numero personne
Numéro d’une personne
N
2
identifiant
Code fonction
Code de fonction
AN
1
identifiant
Numero salle
Numéro de la salle de la réunion
N
4
identifiant
Capacite
Nombre de place de la salle
N
4
obligatoire
Description
Description de la salle
AN
200

Terminale GSI B : services fournis par le système d'information et technologies associées
Eric CREPIN page 2
Schéma entités/associations
1,1
0,n 0,n1,n 0,n1,1
PERSONNE
numero personne
nom
prenom
FONCTION
code fonction
nom fonction
possede
REUNION
numero reunion
libelle reunion
date reunion
heure reunion
duree reunion
PARTICIPE
SALLE
numero salle
capacite
description
se déroule
ENTITE source
Association
Cardinalité
ENTITE cible
Une REUNION
fait participer
une ou plusieurs
PERSONNE
Une PERSONNE
participe à
aucune ou plusieures
REUNION
Une PERSONNE
posséde
une et une seule
FONCTION
Une FONCTION
est possédée par
aucune ou plusieures
PERSONNE
Dans une SALLE
se déroule
aucune, une ou plusieurs
REUNION(s)
Une REUNION
se déroule
dans une et une seule
SALLE
Modèle logique des données
Modèle relationnel
FONCTION(code fonction, nom fonction)
code fonction clé primaire
PARTICIPE(numéro réunion#, numéro personne#)
numéro réunion, numéro personne clé primaire
numéro réunion clé étrangère en référence à REUNION
numéro personne clé étrangère en référence à PERSONNE
PERSONNE(numéro personne, nom, prénom, code fonction#)
numéro personne clé primaire
code fonction clé étrangère en référence à FONCTION
REUNION(numéro réunion, libellé réunion, date réunion, numéro salle, heure réunion,
durée réunion, numero salle#)
numéro réunion clé primaire
numéro salle clé étrangère en référence à SALLE
SALLE(numero salle, Capacite, Description)
numéro salle clé primaire
Les clés primaires sont soulignées gras
Les clés étrangères sont en italique suivies d’un #

Terminale GSI B : services fournis par le système d'information et technologies associées
Eric CREPIN page 3
Les données du système d’information étudié
Table PERSONNE
Numéro personne
Prénom
Nom
Code fonction
1
Jean
CAISSE
E
2
Martin
PECHEUR
E
3
Martine
HAIE
P
4
Jeanne
HOLAPAIN
E
5
Céline
AIHAIRE
P
6
Jean
BONNOT
E
7
Jean
BONNOT
P
8
Inès
PERRET
P
9
Paul
ITTIC
E
10
Sylvain
HAIBON
E
11
Ali
MENTATION
R
12
Martine
HIQUE
A
13
Sarah
ZAIN
E
14
Annie
MASSION
D
15
Ellie
COPTAIRE
C
16
Anne
HANASSE
C
17
Paul
OCHON
T
18
Marc
ASSIN
T
19
Martin
GALL
T
20
Sylvie
HANKOR
T
Table FONCTION
Code Fonction
Nom Fonction
A
Assistante sociale
C
Conseiller principal d’éducation
D
Proviseur adjoint
E
Elève
P
Professeur
R
Proviseur
T
Parent
Table REUNION
Numero reunion
Libelle reunion
Date
reunion
Heure
reunion
Duree
reunion
Numero
salle
1
rentrée professeur
01-sept-05
09:00
02:30
15
2
rentrée élève
02-sept-05
10:15
02:00
1
3
nouveaux professeurs
01-sept-05
08:00
01:00
2
4
informations parents
07-oct-05
18:15
01:45
15
5
conseil enseignement SVT
12-nov-05
18:00
01:30
15

Terminale GSI B : services fournis par le système d'information et technologies associées
Eric CREPIN page 4
Table PARTICIPE
Numero reunion
Numéro personne
1
11
1
12
1
14
1
15
1
3
1
5
1
8
2
14
2
16
2
5
2
7
2
1
2
2
2
6
2
9
2
10
3
11
3
14
3
3
3
5
4
11
4
14
4
15
4
17
4
1
4
18
4
6
4
19
5
14
5
7
5
8
Table SALLE
Numéro salle
Capacité
Description
1
400
Amphithéâtre
2
50
Salle du conseil
3
40
Salle de cours standard
4
40
Salle de cours standard
6
16
Atelier SVT
7
16
Atelier Physique
10
16
Laboratoire informatique de la terminale informatique
13
18
Laboratoire informatique du BTS informatique
15
100
Salle de conférence

Terminale GSI B : services fournis par le système d'information et technologies associées
Eric CREPIN page 5
TRAVAIL à FAIRE
Requêtes SQL
Après avoir récupéré la base de données ACCESS TD06_PERS_FONC_sql.mdb
Ecrire les requêtes SQL la base répondant aux questions suivantes :
71. Quelles sont les réunions (numéro) auxquelles ont assisté le professeur ou l’élève Jean
BONNOT ?
SELECT distinct [numero reunion]
FROM PARTICIPE, PERSONNE, FONCTION
WHERE ([nom fonction] = 'Professeur' or [nom fonction] = 'Elève')
and prenom = 'Jean' and nom = 'BONNOT'
and PARTICIPE.[numero personne] = PERSONNE.[numero personne]
and PERSONNE.[code fonction] = FONCTION.[code fonction]
72. Quelles sont les réunions (numéro, libellé et numéro de salle) auxquelles ont assisté les
professeurs Céline AIHAIRE ou Martine HAIE ?
SELECT DISTINCT REUNION.[numero salle]
FROM REUNION, PARTICIPE, PERSONNE, FONCTION
WHERE [nom fonction] = 'Professeur'
and ( (prenom = 'Céline' and nom = 'AIHAIRE')
or (prenom = 'Martine' and nom = 'HAIE')
)
and REUNION.[numero reunion] = PARTICIPE.[numero reunion]
and PARTICIPE.[numero personne] = PERSONNE.[numero personne]
and PERSONNE.[code fonction] = FONCTION.[code fonction]
73. Quelles sont les salles (numéro) auxquelles ont assisté un professeur ?
select distinct [numero salle]
from REUNION,PARTICIPE,PERSONNE,FONCTION
where [nom fonction] = 'Professeur'
and REUNION.[numero reunion] = PARTICIPE.[numero reunion]
and PARTICIPE.[numero personne] = PERSONNE.[numero personne]
and PERSONNE.[code fonction] = FONCTION.[code fonction]
74. Quelles sont les salles (numéro, capacité) auxquelles ont assisté un professeur, triées par
capacité décroissante ?
SELECT DISTINCT SALLE.[numero salle], capacite
FROM SALLE, REUNION, PARTICIPE, PERSONNE, FONCTION
WHERE [nom fonction] = 'Professeur'
and SALLE.[numero salle] = REUNION.[numero salle]
and REUNION.[numero reunion] = PARTICIPE.[numero reunion]
and PARTICIPE.[numero personne] = PERSONNE.[numero personne]
and PERSONNE.[code fonction] = FONCTION.[code fonction]
ORDER BY capacite DESC
75. Donner la liste des personnes (nom, prénom, nom fonction) triée par nom de fonction ?
SELECT nom, prenom, [nom fonction]
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
order by [nom fonction]