Système de gestion de bases de données I ( SGBD I )
Examen Fin Module 1 :
Partie I ( plusieurs réponses possibles) (1 pt)1. Une base de données SQL Server :
a. Peut ne pas avoir de fichier journal
b. Contient au moins un groupe de fichiers
c. Ne peut contenir plus d’un fichier journal
d. Peut contenir plusieurs fichiers de données
e. Doit obligatoirement avoir une taille limite
f. Peut avoir des tables qui n’appartiennent à aucun schéma
2. Avant de supprimer une table Il faut supprimer :
a. Toutes les contraintes qui lui sont associées
b. Toutes les contraintes d’intégrité référentielles=foreing key qui lui sont associées
c. Tous ses champs
Partie II :
Le système d’information étudié est celui d'un artisan parfumeur de renommée internationale qui propose un ensemble de parfums à la vente. Il compte un ensemble de laboratoires répartis à travers le monde placés chacun sous la direction d’un responsable. L’artisan dispose au niveau du siège d’un serveur SQL Server où sont stockées ses bases de données. Il a une base de données GestionCommerciale pour la gestion des commandes et GestionPaie pour la paie du personnel. Son souhait prochain est de disposer d’une base de données pour la gestion de la production et de la composition des parfums. En fait les parfums sont réalisés dans les laboratoires. Chaque parfum se compose d’un ensemble de produits qu’on souhaite mémoriser. 1. La base de données Parfumerie doit contenir un fichier de données et un fichier journal respectant les règles suivantes :
a. Vous n’avez pas le droit de créer des bases de données. Sachant que la base de données en cours est la base GestionStagiaire. Quel script devra écrire l’administrateur système pour vous autoriser à créer des bases de données et pour vous autoriser à transmettre ce droit à d’autres utilisateurs ? (1 pt)
b. Maintenant vous êtes connecté avec votre login. Créer la base de données Parfumerie.(2 pts)
2. La base de données Parfumerie a la structure suivante :
- Parfum (#Codpar, NomPar, PrixVente, DateProduction, DateSortie, NumLabo, Classe)
- Laboratoire (#NumLabo, DesignationLabo)
- Produit (#CodPro, DesignationPro, UniteMesurePro, PrixUnitairePro)
- Composition (#CodPar, #CodPro, QuantiteUtilisee)
a. Créer un type utilisateur nommé Désignation basé sur le type varchar avec une longueur 100 et toujours non null (1 pt)
b. Créer une règle indiquant qu’un champ ne peut prendre que les valeurs de 1 à 5 (1 pt)
c. En supposant que les tables Laboratoire et Produit sont déjà créées, créer les tables Composition et Parfum en spécifiant les contraintes clés primaires et clés étrangères ainsi que les contraintes décrites ci-dessous : (5 pts)
- Classe peut prendre les valeurs de 1 à 5
- Deux parfums ne peuvent pas porter le même nom
- La date de sortie prend par défaut la date du jour
- Classe ne peut pas accepter des valeurs nulles
- Les champs DesignationPro et DesignationLabo sont de type Désignation
- L’unité de mesure peut prendre les valeurs ml ou g
- A la suppression d’un parfum sa composition sera supprimée
d. Ajouter le champ Responsable à la table Laboratoire. Il est de type varchar(50) et ne peut pas prendre de valeurs nulles (1 pt)
e. Créer un index Unique sur le champ Responsable qui vient d’être ajouté (1 pt)
f. Créer une contrainte indiquant que la date de sortie d’un parfum est toujours supérieure à la date de production (1 pt)
g. Modifier le type du champ DesignationLabo (varchar(200) au lieu de Désignation) (1 pt)
h. Créer deux logins SQL Server LogResponsable et LogArtisan ainsi que les utilisateurs correspondants sachant que LogResponsable a le droit d’accéder à la base de données Parfumerie et GestionCommerciale (La base de données par défaut est Parfumerie) et que LogArtisan n’a le droit d’accéder qu’à parfumerie (2 pts)
i. Créer un rôle de base de données pour parfumerie nommé RoleAgent contenant les utilisateurs des logins LogResponsable et LogArtisan (2 pts)
j. Donner au rôle RoleAgent le droit de consulter la table parfum (1 pt)
k. Interdire à LogResponsable de modifier la table Composition et autoriser RoleAgent à le faire (2 pts)
l. Autoriser LogResponsable à créer des vues (1 pt)
m. Créer un schéma S1 autorisant RoleAgent à faire des delete et interdisant à LogResponsable de faire des select(2 pts)
n. En supposant que la table parfum appartient au schéma S1, remplir le tableau représentant les droits qu’ont réellement les Logins LogResponsable et LogArtisan en mettant des V pour indiquer qu’il y’a droit et des X pour indiquer qu’il n’y a pas droit : (2 pts)
3. Les données sont manipulées de cette manière :
a. Créer une table ParfumsClasse1 contenant les champs NomPar et PrixVente et n’ayant pas de clé primaire, insérer dans cette table tous les parfums de la classe 1 (2 pts)
b. Supprimer tous les laboratoires où aucun parfum n’a été créé (1 pt)
c. Diminuer de 5% les prix des produits entrant dans la composition des parfums créés dans le laboratoire placé sous la responsabilité de ALAMI (2 pts)
4. Créer les requêtes suivantes :
a. Liste des noms de produits entrant dans la composition du parfum ‘Fleur d’été’ triés par prix (1 pt)
b. Le nombre de produits par parfum (Nom du parfum) (1 pt)
c. Le coût de chaque parfum (Somme (Qte * Prix)) (2 pt)
d. Les laboratoires (numéros) où ont été fabriqués plus de 10 parfums (2 pts)
e. Le nom du parfum le plus cher (2 pts)
Système de gestion de bases de données I ( SGBD I )
Examen Fin Module 2 :
Au niveau national, la natation est un sport géré par la Fédération Marocaine de Natation, puis par des clubs au niveau des différentes villes du Royaume.La fédération organise des entraînements de natation communs aux différents athlètes dans le but d’harmoniser les pratiques et de déceler les futurs talents. Ces entraînements communs nécessitent de disposer de créneaux horaires dans trois piscines différentes.
La fédération souhaite mettre en place une gestion informatisée afin de contrôler que chaque athlète suit bien son plan d'entraînement personnalisé. Pour chaque athlète, le plan d'entraînement proposé définit la distance (exprimée en mètres) à parcourir pour chaque entraînement.
Pour assurer cette gestion, le schéma relationnel suivant a été établi :
ATHLETE(#NumLicence, NomAthlete, PrenomAthlete, CategorieAthlete)
ENTRAINEMENT(#NumEntrainement, DateEntrainement, HeureDebut, HeureFin, NumPiscine#)
PLAN_ENTRAINEMENT(#NumEntrainement*, #NumLicence*, DistanceAParcourir, DistanceParcourue)
PISCINE(#NumPiscine, NomPiscine, AdressePiscine)
TRAVAIL À FAIRE
I. Création de la base de données
1. Créer la base de données sous SQL SERVER
2. Créer trois enregistrements par table
II. Contraintes
1. Les valeurs permises pour le champs CategorieAthlete sont (Catégorie1,Catégorie2,catégorie3)
2. La distance parcourue doit être positives et inférieure ou égale à la distance à parcourir
III. Requêtes
1. Afficher la liste des athlètes triés par ordre décroissant des catégories et ordre croissant de leur numéro de licence.
2. Afficher la liste de piscines triées par ordre croissant des noms, les noms doivent avoir le premier caractère en majuscule et les adresses des piscines en minuscule.
3. Afficher les athlètes qui ont participés au plan d’entrainement numéro 20, (nom,prénom,distanceparcourue,observation), le champs observation permettant d’afficher le mot débutant si la distance parcouru est inférieure à 2000 m sinon on affiche le mot expert.
4. Afficher les piscines (numéro, nom,adresse) qui seront disponible pour le mois janvier de l’année 2013.
5. Créer une vue vue1 affichant le nombre d’athlète par catégorie.
6. Créer une vue vue2 affichant le total des distances parcourue au niveau des différents entrainements pour chaque athlètes (numéro athlète, total distance parcourue)
7. En utilisant la question N°6 afficher les athlètes dont la distance parcourue dans les différents entrainements est supérieur à 2000 m
8. Afficher les entrainements dont leur distance à parcourir est la valeur maximale.
9. Créer une vue vue3 permettant d’afficher la listes des entrainements suivis(numéro,date,heure début,heure fin,Nom piscine,Distance à parcourir, distance parcourue)pour chaque athlète.
10. Afficher les athlètes qui ont participé à au moins 4 entrainements.
11. Créer une vue vue4 affichant le nom de piscine le plus utilisé par les athlètes de la catégorie 1.
Système de gestion de bases de données I ( SGBD I )
Examen Fin Module 3 :
On considère la base de données ‘bibliothèque’ qui gère les emprunts des adhérents.La base de données a la structure suivante :
Emprunteur (#IDemprunteur, Nom, Prénom, Ville)
Ouvrage (#IDlivre, Titre, catégorie, NbrExemplaire,#IDauteur)
Auteur (#IDauteur, NomAuteur, PrénomAuteur, DateNaiss)
Emprunt (#IDemprunteur*, #IDlivre*, datEmprunt, DatRetour)
Travail à Faire :
Partie I :
1- Ecrire la commande qui permet de créer la base de données ‘bibliothèque’ avec une taille de 3Mo pour le fichier de données et 2Mo pour le journal de transition. Prévoir une possibilité de croissance de 1Mo.
2-créer les tables de la base de données en respectant les contraintes (Clé primaire, clé étrangère, conditions, etc …) sachant que :
a- La date d’emprunt est la date du jour par défaut.
b- La date d’emprunt doit être inferieure ou égal à la date de retour.
c- NbrExemplaire est différent de 0
d- La catégorie de la table ouvrage ne peut prendre que les valeurs suivants A, B, C et D.
3- Ecrire la requête qui permet d’ajouter le prix d’ouvrage dans la table Ouvrage.
Partie II :
1. Créer une requête qui donne le nombre des ouvrages par catégorie.
2. Afficher les ouvrages qui ne sont pas empruntés.
3. Afficher les titres, la catégorie des livres empruntés par l’emprunteur nommé <
4. Afficher pour chaque emprunteur, les livres qui sont actuellement empruntés.
5. Afficher le prix moyen des ouvrages dont le titre commence par une lettre comprise entre A et M.
6. Afficher les noms des auteurs ayant plus de 20 ouvrages.
7. Afficher les noms des ouvrages empruntés par les personnes qui habitent ‘Rabat’.
8. Afficher l’ouvrage le plus de nombre fois emprunté.
9. On suppose qu’une table Archive ayant la même structure que la table Emprunt est crée.
- Archiver tous les emprunts retournés dans la table archive.
10- Supprimer les emprunts retournés de la table Emprunt.
Autres EFM (s) sur le lien suivant :
