Sujet + corrigé conception et SQL

Exercice 1 (Conception et SQL)

L’exercice porte sur une gestion simpliée de livres d’une bibliothèque. Le concepteur de la base a conçu un schéma relationnel composé des cinq relations Tarifs, Adherents, Oeuvres, Livres et Emprunts. La première partie de l’exercice consiste à comprendre et à expliquer ce schéma. La seconde partie sera composée de requêtes, et la troisième de variantes. Les sources sont au format PostGreSQL, et toutes les relations sont préxées par le nom du schéma Bibliotheque, que vous pouvez omettre dans vos réponses.
CREATE SCHEMA Bibliotheque ; La bibliothèque est constituée de livres. Chaque livre est en fait un des exemplaires d’une oeuvre. Les oeuvres sont décrites dans une relation BCNF.
CREATE TABLE Bibliotheque . Oeuvres ( −− Typage des attributs ISBN text NOT NULL, Titre text NOT NULL, Editeur text NOT NULL, Auteur text NOT NULL, −− Separer les auteurs par une virgule pour la l i s i b i l i t e −− Clefs candidates PRIMARY KEY (ISBN) ); Pour pouvoir emprunter simultanément plusieurs livres, chaque adhérent doit déposer une caution dont le tarif est décrit dans une relation BCNF.
CREATE TABLE Bibliotheque . Tarifs ( −− Typage des attributs NbEmpruntsAutorises integer CHECK ( NbEmpruntsAutorises >= 0) , Caution integer CHECK ( NbEmpruntsAutorises >= 0) , −− Clefs candidates PRIMARY KEY ( NbEmpruntsAutorises ) ); Les adhérents à la bibliothèque sont enregistrés dans une relation.
CREATE TABLE Bibliotheque . Adherents ( −− Typage des attributs Id s e r i a l NOT NULL, −− s e r i a l = sequence d ‘ entier 1 , 2 , 3 , . . . Nom text NOT NULL, Prenom text NOT NULL, NbEmpruntsAutorises integer NOT NULL, −− Clefs candidates PRIMARY KEY ( id ) , −− Clefs etrangeres FOREIGN KEY ( NbEmpruntsAutorises ) REFERENCES Bibliotheque . Tarifs ( NbEmpruntsAutorises ) );

Question 1.1

Pour la relation Adherents, donnez la seule dépendance fonctionnelle déclarée, puis expliquez à quoi correspond et à quoi sert la clef étrangère.
Réponse :
Id → (Nom,Prenom,NbEmpruntsAutorises) La clef étrangère NbEmpruntsAutorises fait référence à l’attribut de même nom de la relation Tarifs. Cela garantit que lors de l’ajout d’un adhérent, la valeur de cet attribut existe dans la relation Tarifs. Les livres sont décrits dans une relation.
CREATE TABLE Bibliotheque . Livres ( −− Typage des attributs Id s e r i a l NOT NULL, −− s e r i a l = sequence d ‘ entier 1 , 2 , 3 , . . . ISBN text NOT NULL, DateAchat date NOT NULL, −− Clefs candidates PRIMARY KEY ( Id ) , −− Clefs etrangeres FOREIGN KEY (ISBN) REFERENCES Bibliotheque . Oeuvres (ISBN) );

Question 1.2

Pour la relation Livres, donnez la seule dépendance fonctionnelle déclarée, puis expliquez à quoi correspond et à quoi sert la clef étrangère.
Réponse :
Id → (ISBN,DateAchat) La clef étrangère ISBN fait référence à l’attribut de même nom de la relation Oeuvre. Cela garantit que lors de l’ajout d’un livre, l’oeuvre existe bien existe dans la relation Oeuvres. Les emprunts sont enregistrés dans une relation.
CREATE TABLE Bibliotheque . Emprunts ( −− Typage des attributs Livre integer NOT NULL, DateEmprunt date NOT NULL, DateRetour date NOT NULL DEFAULT ‘ i n f i n i t y ‘ , Adherent integer NOT NULL, −− Clefs candidates PRIMARY KEY ( Livre , DateEmprunt ) , UNIQUE ( Livre , DateRetour ) , −− Clefs etrangeres FOREIGN KEY ( Adherent ) REFERENCES Bibliotheque . Adherents ( Id ) , FOREIGN KEY ( Livre ) REFERENCES Bibliotheque . Livres ( Id ) , −− Contraintes d ‘ integrite elementaire CHECK (DateEmprunt < DateRetour ) );
2/ 9
INF 159 : Bases de données Session 2, Année 2010/2011
−− PostgreSQL exige une fonction pour les contraintes d ‘ integrite avec SELECT CREATE FUNCTION Bibliotheque . DatesEmpruntRetourCorrectes ( integer , date , date) RETURNS boolean AS $$ SELECT NOT EXISTS ( SELECT ∗ FROM Bibliotheque . Emprunts WHERE ($1 = Livre AND $2 <= DateRetour AND $3 >= DateEmprunt )) $$ LANGUAGE SQL; −− Contrainte d ‘ integrite statique ALTER TABLE Bibliotheque . Emprunts ADD CONSTRAINT DatesEmpruntRetourPossibles CHECK( Bibliotheque . DatesEmpruntRetourCorrectes ( Livre , DateEmprunt , DateRetour)=TRUE);