Révisions MRD initiation SQL Contexte 1

Contexte de SI n° 1 – La SSII InfoDev
Dossier 3 : la Base de Données GESTPROJ (découverte)
A. Étude du schéma relationnel et de la base de données
Documents à exploiter :
 Base de données Gestproj sous MS Access
 Présentation de la base de données de suivi des projets
(schéma relationnel et modèle physique des données sous Access)
3.1. Qu’est-ce qui définit une relation ?
3.2. À quoi correspondent les clés primaires ? (NumCLient, CodeProjet…)
3.3. Pour une valeur connue de NumClient, quelles informations sur le client peut-on
obtenir ?
3.4. Que peut-on en déduire sur la relation entre NumClient et NomClient ou entre
NumClient et Telclient ?
3.5. Observez la relation PROJET : Quels sont les attributs connus grâce à une valeur du
CodeProjet ?
3.6. Que peut-on en déduire sur la relation entre CodeProjet et NumClient ?
Terminale STG GSI
Contexte 1 – Dossier 3
page 11
Voici la table PROJET en affichage "mode création" sous Access.
3.7. Quelles informations sont données ?
Quelles contraintes d'intégrité devra-t-on respecter pour remplir la table ?
Voici un extrait de la table CLIENT
3.8. Pourrait-on ajouter l’enregistrement suivant ?
Corbat, 15 rue de la Gaieté, 17700, BOISSE, 05 49 xx xx xx, 05 49 xx xx xx , Amélie Lecoeur
Pourquoi ?
Et celui-ci ?
41, Corbat, 15 rue de la Gaieté, 17700, BOISSE, 05 49 xx xx xx, 05 49 xx xx xx , Amélie Lecoeur
Pourquoi ?
Que pouvez-vous en conclure ?
3.9. Que doit-on vérifier avant d’ajouter l‘enregistrement suivant dans la table PROJET ?
P050321 ; ResCollLang ; 120 ; 1 ; mise en place d’un réseau… : Marchés ; 15/09/2006 ; 10/12/2006 ;
02/03/2006 ; Candidat
Terminale STG GSI
Contexte 1 – Dossier 3
page 12
Les contraintes d'intégrité
Les questions précédentes ont fait ressortir différentes contraintes d’intégrité du système :
Intégrité de domaine
Principe
Exemple
Intégrité d’entité
Principe
Exemple
Intégrité référentielle
Principe
Exemple
Terminale STG GSI
Contexte 1 – Dossier 3
page 13
B. Rappels sur les opérateurs relationnels (Prog. de 1STG)
3.10. Voici le résultat d'une requête faite à partir de la relation CLIENT.
Quelle opération relationnelle a-t-on faite ?
3.11. Voici le résultat d'une requête faite à partir de la relation PROJET
(NB la partie droite de l'écran a été coupée uniquement pour gagner de la place).
Quelle opération relationnelle a-t-on faite ?
3.12. Quelle information est fournie dans l’écran page suivante ?
De quelles tables sont issues ces données ?
Quels champs ont permis de rapprocher les données issues de ces tables. ?
Comment s’appelle ce rapprochement entre les tables ?
Terminale STG GSI
Contexte 1 – Dossier 3
page 14
Terminale STG GSI
Contexte 1 – Dossier 3
page 15
C. Rappels sur le SQL de base (Prog. de 1STG)
écran 1
écran 1 bis
NB il y a plus de lignes dans la réponse, elles ont été coupées uniquement pour le gain de place
écran 2
écran 2bis
NB il y a plus de lignes dans la réponse,
elles ont été coupées uniquement
pour le gain de place
Terminale STG GSI
Contexte 1 – Dossier 3
page 16
3.13. Que contiennent chacun des écrans page précédente ?
3.14. Déduisez-en la signification des instructions SELECT et FROM
3.15. Que représente l’étoile dans la requête 1 ?
3.16. Combien y aura-t-il de lignes en réalité dans la réponse à la requête 1 ? dans la
réponse à la requête 2 ?
3.17. À quel besoin d'information répond la requête 3 ?
3.18. Que pouvez-vous en déduire sur l’instruction WHERE ?
Terminale STG GSI
Contexte 1 – Dossier 3
page 17
3.19. À quel besoin d'information répond la requête 4 ?
3.20. Que pouvez-vous en déduire sur l’instruction ORDER BY ?
Quel est son rôle ?
Quelle est l’utilité du mot ASC ?
3.21. À quel besoin d'information répond la requête 5 (page suivante)?
3.22. Combien d’enregistrements donne cette requête ?
Cela vous semble-t-il cohérent ? Pourquoi ?
3.23. Quelle opération manque-t-il ?
3.24. Modifier la requête pour corriger cette erreur ?
Terminale STG GSI
Contexte 1 – Dossier 3
page 18
3.25. À quel besoin d'information répond la requête 6 ?
3.26. Déduisez-en l’utilité de l’opérateur LIKE et du caractère * dans "17*"
Terminale STG GSI
Contexte 1 – Dossier 3
page 19
D. Modification de requêtes existantes
Voici une liste de requêtes. Pour chacune d’elles, vous disposez du besoin d’information initial auquel elles
répondent, et du code SQL correspondant.
Le besoin d’information ayant évolué, il vous est demandé de modifier la requête existante afin qu’elle
réponde à ce nouveau besoin.
a) La liste des clients pour lesquels aucun contact n’est enregistré dans la base de données.
SELECT NumClient, NomClient
FROM Client
WHERE NomContactClient IS NULL;
Nouveau besoin : La liste des clients pour lesquels on connaît le contact (ce contact doit apparaître dans le
résultat, ainsi que le numéro de téléphone).
Requête modifiée
b) La liste des projets par client à l’état ‘candidat’ ou ‘en cours’.
SELECT CodeProjet, NomProjet, Client.NumClient, NomClient
FROM Projet, Client
WHERE Projet.NumClient=Client.NumClient
AND EtatProjet IN ("Candidat","Encours");
Nouveau besoin : La liste des projets par client à l’état ‘Candidat’ ou ‘En cours’ qui proviennent d’un appel
d’offres.
Requête modifiée
c) La liste des projets pour lesquels la réponse a été envoyée en Mars 2006
(le résultat est identique pour les 2 solutions ci-dessous à une condition, laquelle ?)
SELECT CodeProjet, NomProjet
FROM Projet
WHERE DateReponse BETWEEN #01/03/2006# AND #31/03/2006#;
SELECT CodeProjet, NomProjet
FROM Projet
WHERE Month(DateReponse) = 3;
Nouveau besoin : La liste des projets pour lesquels la réponse a été envoyée en Mars 2006 et qui doivent
débuter en Mai 2006.
Requête modifiée
Terminale STG GSI
Contexte 1 – Dossier 3
page 20
d) La liste des clients qui ne sont pas des établissements scolaires.
SELECT NumClient, NomClient
FROM CLient
WHERE NomClient NOT LIKE "*Collège *" AND NomClient NOT LIKE "*Lycée*";
Nouveau besoin : La liste des clients qui ne sont pas des établissements scolaires et pour lesquels il existe
un projet ‘En cours’.
Requête modifiée
E. Écriture de requêtes interrogation
Rédiger les requêtes SQL qui répondent aux besoins d’information suivants :
1. La liste des clients (Numéro, nom et ville) classée par ville, puis par ordre alphabétique.
2. La liste des clients situés à Vignolles et Yves
3. La liste des intervenants encore en activité dans l’entreprise
4. La liste des clients pour lesquels un projet réseau est à l’étude.
5. La liste des intervenants embauchés en 2005.
6. La liste des employés susceptibles d’intervenir sur le projet Hermione.
Terminale STG GSI
Contexte 1 – Dossier 3
page 21