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
Travaux Dirigés TD_analyse d’un système d’information
Thème :
travail à faire pour le :
Analyse d’un système d’information
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
Nom
Nom d’une personne
Prenom
Prénom d’une personne
Nom fonction
Nom d’une fonction (proviseur,
proviseur
adjoint,
conseiller
principal d’éducation, surveillant
professeur, élève, parent, assistante
sociale)
Numero reunion
Numéro de la réunion
Libelle reunion
Libellé de la réunion
Date reunion
Date de la réunion
Heure reunion
Heure de la réunion
Duree reunion
Durée de la réunion
Numero salle
Numéro de la salle de la réunion
Numero personne Numéro d’une personne
Code fonction
Code de fonction
Numero salle
Numéro de la salle de la réunion
Capacite
Nombre de place de la salle
Description
Description de la salle
Eric CREPIN
Type
AN
AN
AN
Format
50
50
50
Remarque
obligatoire
obligatoire
obligatoire
N
AN
date
heure
heure
N
N
AN
N
N
AN
3
250
identifiant
obligatoire
obligatoire
obligatoire
obligatoire
obligatoire
identifiant
identifiant
identifiant
obligatoire
4
2
1
4
4
200
page 1
Terminale GSI
B : services fournis par le système d'information et technologies associées
Schéma entités/associations
REUNION
SALLE
numero salle
capacite
description
se déroule
0,n
ENTITE source
Une REUNION
Une PERSONNE
Une PERSONNE
Une FONCTION
Dans une SALLE
Une REUNION
1,1
numero reunion
libelle reunion
date reunion
heure reunion
duree reunion
PARTICIPE
1,n
Association
fait participer
participe à
posséde
est possédée par
se déroule
se déroule
0,n
PERSONNE
numero personne
nom
prenom
possede
1,1
Cardinalité
une ou plusieurs
aucune ou plusieures
une et une seule
aucune ou plusieures
aucune, une ou plusieurs
dans une et une seule
0,n
FONCTION
code fonction
nom fonction
ENTITE cible
PERSONNE
REUNION
FONCTION
PERSONNE
REUNION(s)
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 #
Eric CREPIN
page 2
Terminale GSI
B : services fournis par le système d'information et technologies associées
Les données du système d’information étudié
Table PERSONNE
Numéro personne
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Table FONCTION
Code Fonction
A
C
D
E
P
R
T
Table REUNION
Numero reunion
1
2
3
4
5
Eric CREPIN
Prénom
Jean
Martin
Martine
Jeanne
Céline
Jean
Jean
Inès
Paul
Sylvain
Ali
Martine
Sarah
Annie
Ellie
Anne
Paul
Marc
Martin
Sylvie
Nom
Code fonction
CAISSE
PECHEUR
HAIE
HOLAPAIN
AIHAIRE
BONNOT
BONNOT
PERRET
ITTIC
HAIBON
MENTATION
HIQUE
ZAIN
MASSION
COPTAIRE
HANASSE
OCHON
ASSIN
GALL
HANKOR
E
E
P
E
P
E
P
P
E
E
R
A
E
D
C
C
T
T
T
T
Nom Fonction
Assistante sociale
Conseiller principal d’éducation
Proviseur adjoint
Elève
Professeur
Proviseur
Parent
Libelle reunion
rentrée professeur
rentrée élève
nouveaux professeurs
informations parents
conseil enseignement SVT
Date
reunion
01-sept-05
02-sept-05
01-sept-05
07-oct-05
12-nov-05
Heure
reunion
09:00
10:15
08:00
18:15
18:00
Duree
reunion
02:30
02:00
01:00
01:45
01:30
Numero
salle
15
1
2
15
15
page 3
Terminale GSI
B : services fournis par le système d'information et technologies associées
Table PARTICIPE
Numero reunion
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
3
3
3
3
4
4
4
4
4
4
4
4
5
5
5
Table SALLE
Numéro salle
1
2
3
4
6
7
10
13
15
Eric CREPIN
Numéro personne
11
12
14
15
3
5
8
14
16
5
7
1
2
6
9
10
11
14
3
5
11
14
15
17
1
18
6
19
14
7
8
Capacité
400
50
40
40
16
16
16
18
100
Description
Amphithéâtre
Salle du conseil
Salle de cours standard
Salle de cours standard
Atelier SVT
Atelier Physique
Laboratoire informatique de la terminale informatique
Laboratoire informatique du BTS informatique
Salle de conférence
page 4
Terminale GSI
B : services fournis par le système d'information et technologies associées
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]
Eric CREPIN
page 5
Terminale GSI
B : services fournis par le système d'information et technologies associées
76. Combien la base de données compte elle de professeurs ?
SELECT count(*) as NbPROF
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
and [nom fonction] = 'Professeur'
77. Combien chaque nom de fonction compte elle de personnes dans la base de données ?
SELECT [nom fonction], count(*) as NbPROF
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
group by [nom fonction]
78. Quelles sont les noms de fonction qui compte au moins 4 personnes dans la base de
données ?
SELECT [nom fonction], count(*) as NbPROF
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
group by [nom fonction]
having count(*) >= 4
79. Quelles sont les noms de fonction qui compte entre 2 et 4 personnes dans la base de
données (triés par nombre de personnes décroissant puis nom de fonction croissant) ?
SELECT [nom fonction], count(*) as NbPROF
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
group by [nom fonction]
having count(*) >= 2 and count(*) <=4
order by count(*) desc, [nom fonction]
ou
SELECT [nom fonction], count(*) as NbPROF
FROM PERSONNE, FONCTION
WHERE PERSONNE.[code fonction] = FONCTION.[code fonction]
group by [nom fonction]
having count(*) between 2 and 4
order by count(*) desc, [nom fonction]
Base de données

Dans la mesure où l’on voudrait supprimer la base de données table par table dans
ACCESS quel serait l’ordre de suppression des tables ? Pourquoi ?
1- PARTICIPE (clé primaire composée de deux clés étrangères en référence à PERSONNE, REUNION)
2- PERSONNE, REUNION
(il n’y a plus de références aux clés primaires de PERSONNE, REUNION
3-
les clés étrangères font référence à FONCTION et SALLE)
FONCTION, SALLE (il n’y a plus de références aux clés primaires de FONCTION et SALLE)
Eric CREPIN
page 6