Examen MIP1 2005/2006

Master Sciences et Technologies – Mention MIP – 1ère année 2005 / 2006
U.E. TABLEUR et BASES de DONNEES
EXAMEN de BASES de DONNEES
Durée : 1h30
Documents non autorisés
Les exercices sont indépendants et peuvent être traités dans un ordre quelconque
Exercice 1 : Conception d'une base
Pour stocker les informations relatives à l'évaluation des étudiants dans les différentes matières
des unités d'enseignement (UE) d'un Master, on souhaite bâtir une base de données comportant :
- nom, prénom et adresse mail des étudiants
- nom, prénom et adresse mail des enseignants
- code, intitulé et enseignant responsable de chaque UE
- intitulé, UE et enseignant de chaque matière
- date, heure et lieu de l'examen et note obtenue par chaque étudiant
Remarque : date et heure peuvent être généralement stockées dans un attribut unique
On fait de plus les hypothèses suivantes :
- un seul enseignant intervient dans chaque matière et ne pose qu'un sujet dans cette matière
- toutes les matières ont le même coefficient (qui ne sera donc pas stocké ici)
- un même enseignant peut intervenir dans plusieurs matières
- une UE peut regrouper plusieurs matières
Faire l'analyse de ce problème et en proposer un modèle entité-association qui précisera les
attributs des entités et des associations. Dresser le schéma relationnel correspondant (en justifiant
le passage du modèle E-A au modèle relationnel).
Exercice 2 : Schéma relationnel et SQL
Pour stocker les informations relatives aux commandes d'une société qui commercialise des
produits, ainsi qu'à ses employés et ses clients, on a élaboré le schéma de base de données
relationnelle suivant sous Access :

1) Expliquer ce schéma :
-quelles sont les tables de la base et leurs identifiants ?
-que représentent les liaisons et leurs indications (1..∞), et que cela signifie-t-il pour les
données des différentes tables ?
-en particulier, quelle est l'utilité de la liaison entre les table "Employés" et "Employés_1"
-à quoi sert la table "Détails commandes" ?
-…
2) Ecrire les requêtes SQL (en SQL normalisé) correspondant aux questions suivantes :
a) Liste des clients (avec toutes leurs informations)
b) Produits dont il reste moins de 10 unités en stock
c) Nom, adresse et numéro de téléphone des clients situés dans la partie nord-est de la France
ou en Ile-de-France (i.e. n° de téléphone commençant par 03 ou 01)
d) Liste des employés avec le nom de leur supérieur hiérarchique
e) Numéro et adresse de livraison des commandes incluant au moins le produit nommé "Tofu"
f) Pour chaque commande, numéro, nombre de produits et total de facturation (hors remise)
g) Nom et nombre de commande des employés ayant passé au moins 100 commandes
Remarque : Dans les requêtes pour lesquelles aucune colonne particulière n'est explicitement
demandée pour le résultat, vous choisirez vous-mêmes les champs les plus pertinents.