Bases de Données

Année 2003-2004

24 Mars 2004

- durée 2 heures -

Les documents ne sont pas autorisés.

Les exercices sont indépendants.

L’ordre des questions ne détermine pas la difficulté.

Exercice 1 : Questions de cours (4 points)

Chose promise, chose due :

1. Donnez la définition d’une clé primaire.

2. Donnez la définition d’une clé étrangère.

3. Pourquoi a-t-on besoin de la notion de transaction dans les SGBD ?

Exercice 2 : Requêtes (12 points)

Le schéma relationnel suivant représente la gestion simplifiée d’un zoo. Les clés primaires sont soulignées, les clés étrangères sont suivies d’un dièse (#). Ce schéma est en BCNF.

Animal( NumA, Nom, Poids, Date de naissance, Lieu de naissance, Date de mort, Sexe, NumPere#, NumMere#, NumE# )

Emplacement( NumE, Taille )

Nourriture( NumN, Descriptif, Stock courant, Stock minimum pour rachat, Prix unitaire )

MangeParJour( NumA#, NumN#, Qte )

Personnel( NumP, Nom, Prénom, Fonction, Age )

SOccupeDe( NumP#, NumA# )

A – Ecrivez les requêtes suivantes en algèbre relationnelle puis en SQL (2 réponses par question)

1. Donnez la liste des animaux (description complète) du zoo.

2. Donnez la liste des animaux (description complète) encore en vie.

3. Donnez la liste des animaux (numéro et nom) mangeant plus de 30 kg par jour.

4. Donnez la liste des animaux (numéro et nom) avec leur emplacement ainsi que les personnes qui s’en occupent.

B – Ecrivez les requêtes suivantes en SQL uniquement

5. Donnez la liste de nourriture à commander.

6. Donnez pour chaque personne, la liste des animaux dont il a la charge.

7. Donnez la durée moyenne de vie des animaux par sexe.

8. Donnez la liste des animaux qui mangent plus par jour qu’il ne pèse.

9. Donnez la quantité de nourriture nécessaire par jour par emplacement.

10. Donnez la liste des animaux plus vieux que l’une des personnes qui en a la charge.

11. Quel est l’animal qui coûte le plus cher en nourriture.

12. Afficher tous les descendants de l’animal numéro 24. (BONUS. Information : laissez vos intuitions prendre le dessus sur votre raison).

Exercice 3 : Normalisation et Dépendances Fonctionnelles (4 points)

Modification de schéma et Normalisation

On souhaite modifier notre modèle afin d’éviter l’utilisation d’un même emplacement pour des animaux incompatibles c’est-à-dire où l’un peut est une proie pour l’autre.

1. Proposez un nouveau modèle permettant de mettre en évidence ce problème. Il faudra bien sûr vérifier que le modèle que vous proposez soit bien en BCNF (rappel : le schéma initial est en BCNF).

2. Proposez une requête SQL permettant d’afficher la liste des emplacements posant un problème.

Dépendances Fonctionnelles

Considérons la relation R(A, B, C, D) avec les DF A ® D, BD ® C et AD ® B.

3. Quel(s) est (sont) la (les) clé(s) de cette relation ?

4. Est-ce que cette relation est en 3 BCNF ?
Si oui, justifiez votre réponse.
Sinon, normalisez cette relation en 3 BCNF.

Rappel de cours :

Propriétés des DF : X et Y sont deux ensembles d’attributs.

Réflexivité	Si Y Í X	alors X ® Y (et donc X ® X)
Augmentation	Si X ® Y et W Í Z	alors X, Z ® Y, W
Transitivité	Si X ® Y et Y ® Z	alors X ® Z
Pseudo-transitivité	Si X ® Y et Y, Z ® W	alors X, Z ® W
Union	Si X ® Y et X ® Z	alors X ® Y, Z
Décomposition	Si X ® Y, Z	alors X ® Y et X ® Z

Première forme normale :

Une relation est en 1 NF, si tous ses attributs sont atomiques (mono-valués).

Deuxième forme normale :

Une relation est en 2 NF si et seulement si :

· Elle est en 1 NF

· Tout attribut n’appartenant pas à la clef primaire est en DF totale avec la clef primaire.

Troisième forme normale :

Une relation est en 3 NF si et seulement si :

· Elle est en 2 NF

· Elle ne contient pas de DF Transitive entre attributs non-clef

3 BCNF :

Une relation est en BCNF si et seulement si :

· Elle est en 2 NF

· Toute source de DF est une clef primaire minimale