Université M’Hamed Bougara Boumerdès, Faculté des Sciences, Département Informatique
Master 1 – Janvier 2012
TD N°3 BDA : Contraintes d’intégrité, Vue et Contrôle de données
Resp. A. AIT BOUZIAD
Dans une entreprise, les employés se déplacent souvent en mission. Ils utilisent l’une des voitures de l’entreprise et partent parfois
pour plusieurs jours.
Voici le schéma relationnel du domaine de la gestion des missions :
EMPLOYE (EMP_Num, EMP_Titre, EMP_Nom, EMP_Salaire, SRV_Num, EMP_Responsable)
SERVICE (SRV_Num, SRV_Libelle)
MISSION (MIS_Num, VEH_Num, EMP_Num, MIS_JourDep, MIS_KlmDep, MIS_JourRetour, MIS_KlmRetour)
VEHICULE (VEH_Num, VEH_NumMatricule, COU_Code, VEH_DateAchat, VEH_PrixAchat, TYP_Num)
TYPE (TYP_Num, TYP_Nom, TYP_Puissance, TYP_Poids, TYP_Consommation, MAR_Num)
MARQUE (MAR_Num, MAR_Nom)
COULEUR (COU_Code, COU_Libelle)
Règles de gestion :
- La table MISSION ne contient que les missions du mois en cours et des 11 mois précédents.
- Le même jour, un employé ne peut partir plus de deux fois en mission.
- Une mission peut durer plus d’un jour.
- La Couleur d’un véhicule est soit noire ou blanche.
- Les voitures ne peuvent pas rouler plus de 150 000 Km.
- Le salaire de base de l'employé ne peut être inférieur à 15 000 DA
- Le prix d'achat d'un véhicule est compris entre 450 000 et 2000 000 DA.
- Le numéro de type est entre 1 et 100.
- Les dates de départ et de retour de missions supérieures à la date du jour.
Travail à faire :
Partie I : Donnez en SQL les commandes de création des tables précédentes en précisant le type de donnée de chaque colonne
ainsi que les contraintes d’intégrités permettant d’assurer la cohérence de données.
Partie II : Programmez en SQL les requêtes suivantes :
1. Liste des numéros et des noms des employés d'un service dont on ne connaît que les 2 premières lettres ‘FI’.
2. Liste des employés qui sont partis en mission le mois en cours.
3. Liste des responsables de service.
4. Liste des véhicules avec le nom de leur couleur, par marque puis par type et enfin dans l'ordre chronologique d'achat.
5. Liste des missions des chefs de service.
6. Nombre de missions, de kilomètres parcourus par ordre alphabétique des employés ces deux derniers mois.
7. Nombre de missions par nom d’employer (avec le nom de leur service) à condition qu'ils en aient effectué plus d'une
ces deux derniers mois.
8. Moyenne, maximum et minimum des kilométrages de toutes les missions.
9. Liste des missions dont le kilométrage est supérieur au kilométrage moyen des missions.
10. Nombre de kilomètres parcourus par chaque véhicule dans l'ordre décroissant.
11. Quel est le chef de service qui a le plus roulé.
12. Liste des employés avec leur nom et celui de leur chef.
13. Nombre de missions par employé. Ceux qui n’en ont pas effectué devront apparaître dans le résultat avec le chiffre zéro.
14. Existe-t-il une marque dont on a des véhicules de toutes les couleurs répertoriées ?
15. Donner à tous les employés le privilège SELECT sur toute la base de données.
16. Donner à l’utilisateur « TOTO », tous les privilèges sur la table « MISSION ».
17. Retirer à l’utilisateur « Directeur » tous les privilèges accordés sur la table « EMPLOYE ».
18. Augmenter de 5% le salaire de tous les chefs de service.
19. Supprimer toutes les missions effectuées avec des véhicules de marque Renault.
20. Apporter les déclarations nécessaires à la base de données pour que la suppression d’une couleur puisse toujours se
faire.
21. Créer la vue telle que le responsable du service « A003 » puisse voir les détails que du personnel qui travaille dans son
service.
22. Donnez aux utilisateurs « Gérant » et « Directeur » l’accès complet à la vue créée à la Q21, avec le privilège de
transmettre l’accès aux autres utilisateurs. Ensuite, révoquez les accès de cet utilisateur.
23. Dans le cas ou le SGBD ne prend pas en charge la vérification de contraintes d’intégrités. Proposer deux solutions
permettant d’y remédier pour la contrainte de référence « EMPLOYER(SRV_Num) SERVICE(SRV_Num) ». Détailler.
