Question 1

Examen du cours « Conception et Administration des Bases de Données – NFE113 »
Olivier Michelet - juin 2012
Durée de l’examen : 3 Heures
Support de cours autorisé (N’oubliez pas que vous détenez la réponse plus que votre support).
Pensez à faire des réponses claires et détaillées.
Soignez la présentation.
Toute information complémentaire nécessaire à la bonne compréhension ou à la réalisation des réponses sera
examinée; les argumentaires sont un des facteurs d’acceptation de votre réponse.
Question 1 : Modélisation de Données (6 points)
Votre fédération sportive vous confie la mise en place de son nouveau site web. On doit pouvoir gérer les
licenciés (nom, prénom, date de naissance, adresse postale, adresse mail) et les clubs (nom, adresse, nom du
dirigeant, téléphone, adresse web). Le site mis en place propose un accès sécurisé aux dirigeants de clubs,
tout comme aux licenciés, pour la gestion des données personnelles. Les authentifications seront applicatives
et donc intégrées à la base. L'accès dirigeant propose un certain nombre de rubriques: les règlements et
promotions pour les clubs, de même que la gestion des compétitions, organisation et collecte des résultats.
L'accès licencié donne la possibilité de procéder à des recherches sur les résultats de concours, le classement
national par catégorie sportive, par ligue, par département, par club ou de rechercher le classement d'un
compétiteur par son nom, avec affichage de tous les concours auxquels il a participé, pour l'année en cours
ou pour les années précédentes, selon sa catégorie. Le calendrier des compétitions est également disponible,
indiquant les dates et clubs organisateurs. Enfin, des infos généralistes seront affichées sur la page d'accueil
(des "news"). Les règlements et comptes-rendus de commissions seront également accessibles à tous.
1.1
Etablissez le modèle entité-relation étendu de ce cas.
Prenez en compte tous les éléments à votre disposition et justifiez vos choix le cas échéant.
1.2
Convertissez ce modèle en modèle relationnel.
Détaillez les clés primaires et étrangères, ainsi que les dépendances fonctionnelles.
Détaillez la méthode employée pour effectuer cette transformation.
Question 2 : Architecture du SGBD (2 points)
En quoi consiste l’isolation des transactions et comment le système gère-t-il la concurrence des accès aux
données ?
Quel est le rôle de ces fonctionnalités et en quoi constituent-elles une amélioration par rapport à un système
de fichiers indexés ?
Question 3 : Architecture / utilisation (4 points)
Quelles sont les mesures que vous devez prendre pour garantir la disponibilité en 24/7 de votre application?
Comment conciliez-vous les tâches d’administration de base et de traitements applicatifs (comptables, mises
à jour de base …) avec les impératifs de disponibilité demandés par les utilisateurs?
Comment procédez-vous pour vous assurer de ne jamais « perdre » de données ?
Exposez vos choix techniques et justifiez votre réponse.
Question 4 : Manipulation de Données - Algèbre relationnelle et langage SQL (5 points)
On considère le schéma relationnel suivant, modélisant une base de recettes de cuisine
TYPESINGREDIENTS (numerotype, descriptiontype) contenant par exemple le tuple (2, viande)
INGREDIENTS (numingredient, nomingredient, #numtypeingredient, #numtypemesure) contenant par
exemple le tuple (3, bœuf, 2, 5) le numéro de type de mesure décrit l’unité de mesure utilisée pour cet
ingrédient.
MESURES (numtypemesure, descriptionmesure) contenant par exemple le tuple (5, gramme)
INGREDIENTSRECETTE (#numrecette, numordreingredient, #numingredient, quantite) contenant par
exemple le tuple (12, 1, 3, 500)
RECETTES (numrecette, nomrecette, #numtyperecette, dureepreparation, niveaudifficulte) contenant par
exemple le tuple (12, bœuf Strogonoff, 1, 45, 1)
TYPERECETTES(numtyperecette, descriptiontyperecette) contenant par exemple (1, plat principal)
La clé de chaque table est soulignée. Les clés étrangères sont préfixées par #.
Pour chacune des requêtes ci-dessous, donnez l’arbre algébrique :
a) Y a-t-il des desserts comportant de la viande ?
b) Liste des recettes contenant du bœuf et des oignons.
Pour chacune des requêtes ci-dessous, donnez une écriture algébrique et SQL :
c) Affichez les types de recettes manquants (non recensés dans la table TypeRecettes).
d) Liste des ingrédients qui ne sont utilisés dans aucune recette.
Expliquez ce que calculent chacune des requêtes SQL ci-dessous :
e)
SELECT COUNT(numrecette)
FROM RECETTES
WHERE numtyperecette IN (SELECT numtyperecette
FROM TYPERECETTES
WHERE descriptiontyperecette=’plat principal’);
f)
SELECT * FROM INGRÉDIENTS
WHERE NOT EXISTS (SELECT * FROM RECETTES
WHERE NOT EXISTS (SELECT * FROM INGRÉDIENTSRECETTE
WHERE numrecette=Recettes.numrecette
AND numingrédient=Ingrédients.numingrédient)) ;
Question 5 : Administration (3 points)
Vous êtes amenés à auditer les performances d’une base de données relationnelle.
Quels aspects auditez-vous ?
Quelles préconisations seriez-vous amenés à faire pour améliorer les temps de réponse, d’une part, et la
fiabilité du SGBD, d’autre part ?