Contrôle de connaissance - corrigé succint
Dominante Informatique - Module Bases de Données
Lundi 9 mars 1998

Durée 1h30. Documents autorisés.

Bon courage...

A- requêtes relationnelles

Les informations concernant une base de données bibliographique sont stockées dans le schéma relationnel suivant:
Livres (numlivre, titre, annee)
Motsclefs (numlivre, motclef)
Coauteurs (numlivre, numaut)
Auteurs (numaut, nomaut, prenomaut, info)

A1- Requêtes relationnelles

Répondre en SQL aux questions suivantes :

• Quels sont les titres des livres dont les auteurs ne sont pas référencées dans la BD ?

  select titre from Livres
  where numlivre not in (select numlivre from Coauteurs);

• Combien d'auteurs ont signé un livre parlant de "relational data bases" ?

  select count(numaut)
  from Coauteurs, Motsclefs
  where Motscles.numlivre=Coauteurs.numlivre
  and motclef = 'relational data bases';

• Donner les titres des livres qui parlent à la fois de "relational join" et de "clustering" ?

  select titre from Livres
  where
  numlivre in (select numlivre from Motsclefs where motclef='relational join')
  and
  numlivre in (select numlivre from Motsclefs where motclef='clustering');

• Donner les noms des auteurs qui n'ont jamais écrit un livre seul ?

  select nomauteur from Auteurs
  where numauteur not in (
  select C1.numauteur from Coauteurs C1
  where C1.numlivre not in (
  select C2.numlivre from Coauteurs C2
  where C2.numauteur <> C1.numauteur [and C2.numlivre=C1.numlivre]));

  select nomauteur from Auteurs
  where numauteur not in (
  select C1.numauteur from Coauteurs C1, Coauteurs C2
  where (C1.numlivre, C2.numauteur) not in (
  select numlivre, numauteur from Coauteurs));

  select nomauteur from Auteurs
  where numauteur not in (
  select numauteur from Coauteurs
  where numlivre not in (
  select C1.numlivre from Coauteurs C1, Coauteurs C2
  where C2.numauteur <> C1.numauteur and C2.numlivre=C1.numlivre));

  select nomauteur from Auteurs
  where numauteur not in (
  select numauteur from Coauteurs
  where numlivre in (
  select numlivre from Coauteurs
  group by numlivre
  having count(numauteur)=1 ));

  select nomauteur from Auteurs
  where numauteur not in (
  select numauteur from Coauteurs
  where numlivre not in (
  select numlivre from Coauteurs
  group by numlivre
  having count(numauteur)>=2 ));

• supprimer les auteurs qui n'ont écrit aucun livre.

  delete from Auteurs
  where numauteur not in (select numauteur from Coauteurs);

A2- Vues

Créer une vue qui contient tous les auteurs qui ont écrit (ou coécrit) au moins un livre en 1997.

Create view Auteurs97 as
select * from Auteurs
where numauteur in (
select numauteur from Coauteurs, Livres
where annee=1997 and Coauteurs.numlivre=LIvres.numlivre)

Peut on mettre à jour cette vue (insertion, suppression)? Pourquoi ? Cette caractéristique est elle liée à la façon dont la vue a été définie ?

Oui en insertion. Mais le tuple ne sera pas visible dans la vue si Coauteurs n'a pas été mis à jour.

Oui en suppression, mais cela ne supprime pas les tuples de Coauteurs.

Dépend de la syntaxe de création de la vue.
Autre syntaxe ne supportant pas de mise à jour

Create view Auteurs97 as
select A.* from Auteurs A, Coauteurs C, Livres L
where A.numauteur=C.numauteur
annee=1997 and C.numlivre=L.numlivre

Parmi ces auteurs, lesquels ont écrit plus de dix livres sur l'ensemble de leur carrière ?

select nomauteur from Auteurs97 A97, Coauteurs C
where A97.numauteur=C.numauteur
group by A97.numauteur, nomauteur
havi9ng count (numlivre ) > 10

B- Modélisation

Le fond documentaire décrit dans la partie A peut maintenant être utilisé au sein d'un service de prêts. On souhaite donc enrichir son schéma relationnel pour la gestion des adhérents de la bibliothèque.

Il y a deux types d'adhérents, enseignant ou élève, La durée standard d'un prêt est de 4 semaines. Un élève peut bénéficier d'un prêt de 3 mois, mais il doit alors faire garantir sa demande par un enseignant.

Pour créer cette base de données, on propose la relation universelle suivante :

RELUNIV (numlivre, numens, nomens, prenomens, adresseens, numeleve, nomeleve, prenomeleve, adresseeleve, datesortie, dateretour)

En cas d'emprunt d'un livre par un enseignant, seuls les attributs numlivre, numens, nomens, prenomens, adresseens, datesortie sont renseignés.

En cas d'emprunt standard d'un livre par un élève, seuls les attributs numlivre, numeleve, nomeleve, prenomeleve, adresseeleve, datesortie sont renseignés. En cas d'emprunt longue durée par un élève, les attributs numens, nomens, prenomens, adresseens sont aussi renseignés.

dateretour est mis à jour au moment où le document est rendu.

Hypothèse supplémentaire : on souhaite garder un historique des emprunts.

• Donner la liste des dépendances fonctionnelles en les validant par les hypothèses de l'énoncé ou par des hypothèses supplémentaires que vous préciserez.

  numens-> nomens, prenomens, adressens
  numeleve -> nomeleve, prenomeleve, adresseeleve
  numlivre,datesortie -> numens, numeleve, dateretour

  Ne prend pas en compte qu'un livre peut etre emprunte pour une journee.

• En déduire une cle de la relation RELUNIV.

  numlivre, datesortie

• Proposer une décomposition en 3 FN, sans perte et qui préserve les DFs.

  Enseignant(numens, nomens, prenomens, adressens)
  Eleve(numeleve, nomeleve, prenomeleve, adresseeleve)
  Emprunt(numlivre,datesortie, numens, numeleve, dateretour)

• Cette décomposition vous paraît elle perfectible ? Si oui, comment ?

  En factorisant Eleve et enseignant
  Personne (numpers, nompers, prenompers, adressepers)

• Un élève LAMBDA ayant suivi le cours de dominante informatique émet quelques critiques sur le schéma relationnel de la bibliothèque et suggère de partir plutôt d'une nouvelle liste d'attributs.

  RELUNIV2 (numlivre, numpers, nompers, prenompers, adrpers, typepers, numgarant, datesortie, dateretour)

  où typepers indique si la personne est enseignant ou élève et numgarant est le numéro de l'enseignant qui, en cas de prêt longue durée d'un élève, garantit ce prêt.

  A votre avis, LAMBDA a-t-il brillamment réussi le module de Bases de Données ? Expliquez pourquoi. (max 15 lignes et quelques DFs)

  un tel schema ne peut pas etre analyse simplement a l'aide de DFs. En particulier, le lien entre numgarant et numpers est une dependance d'inclusion et ne peut pas etre pris en compte dans une decomposition basee sur la theorie de la normalisation.

C- questions diverses

• SQL est il un langage déclaratif ou procédural ? Expliquez votre réponse. (5 lignes max)

  Oui et non. Oui car inspiration du calcul relationnel. Non car la negation implique imbrication et donc procéduralité.

• Supposons qu'on ait déclaré une contrainte de clé réferentielle entre l'attribut numauteur de la relation Coauteurs et l'attribut numauteur de la relation Auteurs. Quelles sont les deux manipulations permettant de vérifier que la contrainte est bien contrôlée par le SGBD ? Peut on vérifier une telle contrainte à l'aide de vues (définies avec des check option) ? (10 lignes max)

  Une insertion sur Coauteurs et une suppression sur Auteurs

  Insertion vérifiable par vue + check option , mais pas suppression.