VOTRE NOM et prénom :

TP sur la BD bibliothèque : fichier bibliotheque.sql

Répondre aux questions directement dans ce fichier, sous la question.
Pour toutes les questions donner une version SQL1 et une version SQL2.
Sous la réponse, préciser la clé primaire de la table résultat à chaque fois : CP = 
Sous la clé primaire, mettez la table des résultats.

Rendu : envoyer par mail à liaudet.bertrand@wanadoo.fr.
Le fichier avec les réponses en n’oubliant pas de mettre votre nom dans le fichier !

Les questions : 
*******************************************************************************************
*******************************************************************************************

SERIE 0 : La bibliothèque - Graphe des tables

1.	Faites le graphe des tables, de façon arborescente (flèches vers le bas)

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 1 : La bibliothèque - Consultation

1.	Consulter le contenu de toutes les tables : faites un select pour chaque table.
	Combien y a-t-il de tuples dans chaque tables ?

2.	Faire la jointure naturelle de la table des Livres avec la table des Oeuvres. 
	On projette nl, titre, auteur et editeur.
	a•	Quelle est la clé primaire de la la table résultat ?
	b.	Combien y a-t-il de tuples dans la table résultat ? Quelle formule donne le résultat ?
	c.  Créer la vue de la tables des livres avec les oeuvres : vue exemplaires

3.	Faire le produit cartésien de la table Emprunter avec la table des Livres et la table des Adhérents. 
	On projettera tous les attributs.
	a.	Comptez manuellement le nombre d’attributs dans la table résultat ? Quelle formule donne le résultat ?
	b.	Combien y a-t-il de tuples dans la table résultat ? Quelle formule donne le résultat ?
	c•	Quelle est la clé primaire de la la table résultat ?

4.	Faire la jointure naturelle de la table Emprunter avec la table des Livres et la table des Adhérents. 
	On projettera tous les attributs utiles.
	a.	Comptez manuellement le nombre de tuples dans la table résultat ? 
	b•	Quelle est la clé primaire ?
	c•	Trier les résultats par date d''emprunt	
	d•	Trier les résultats par adhérents et date d''emprunt

5.	Faire la jointure naturelle de la table Emprunter avec la table des Livres, la table des Oeuvres et la table des Adhérents. 
	On projettera tous les attributs utiles.
	a.	Comptez manuellement le nombre de tuples dans la table résultat ? 
	b•	Quelle est la clé primaire ?
	c•	Trier les résultats par date d''emprunt	
	d•	Trier les résultats par adhérents et date d''emprunt
	e•	Trier les résultats par auteur et oeuvres et date d''emprunt
	f.  Afficher le nombre d''emprunts par auteur en SQL 2

6.  Creer une vue permettant d''obtenir les livres avec les informations d''auteurs.
	On appellera la vue "livres_complets"

7.  Refaites les requête 5.e et 5.f avec la vue livres_complets de l''exercice 6.


*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 2 : La bibliothèque - Requêtes

1.	Quels sont les livres actuellement empruntés ?
2.	Quels sont les livres empruntés par Jeannette Lecoeur ? Vérifier dans la réponse qu’il n’y a pas d’homonymes.
3.	Quels sont tous les livres empruntés le mois dernier ?
4.	Tous les adhérents qui ont emprunté un livre de Fedor Dostoievski.
5.	Quels sont le ou les auteurs du titre « Voyage au bout de la nuit »
6.	Quels sont les ou les éditeurs du titre « Narcisse et Goldmund »
7.	Quels sont les adhérents actuellement en retard ?
8.	Quels sont les livres actuellement en retard ?
9.	Quels sont les adhérents en retard avec le nombre de livre en retard et la moyenne du nombre de jour de retard.
10.	Nombre de livres empruntées par auteur.
11.	Nombre de livres empruntés par éditeur.
12. Nombre de livres empruntées par auteur et par éditeur.
	Ajouter ensuite un with-rollup à la fin de la requête.
12.	Quelle est la durée des emprunts rendus ?
13. Quelle est la durée moyenne des emprunts rendus.
14.	Quelle est la durée moyenne des retards pour l’ensemble des emprunts.
La fonction « if » permet de tester une valeur et de renvoyer ce qu’on souhaite selon la vérité ou la fausseté de la valeur testée.
if(a<0, 0, a) : permet de ramener les valeurs négatives de a à 0.
15.	Durée moyenne des retards parmi les seuls retardataires.

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 3 : La bibliothèque - Mise à jour

1.	La bibliothèque vient d’acquérir un nouveau exemplaire de : « Au cœur des ténèbres » de Joseph Conrad, chez Gallimard. Faire la mise à jour de la BD.
2.	Ecrire les commandes qui permettent de retirer tout ce que vous avez ajouté.
3.	La bibliothèque vient d’acquérir un nouveau livre de Hermann Hesse intitulé « Siddhartha » chez Folio. Faire la mise à jour de la BD.
4.	Ecrire les commandes qui permettent de retirer tout ce que vous avez ajouté (supprimer une œuvre et les livres correspondants).
5.	Un nouvel adhérent vient s’inscrire : Olivier DUPOND, 76, quai de la Loire, 75019 Paris, téléphone : 0102030405

6.	Martine CROZIER vient emprunter « Au cœur des ténèbres » que vous venez d’ajouter à la question 1
 ET Le rouge et le noir chez Hachette, livre n°23. 
 Faire les mises à jour de la BD pour les deux emprunts.
 Précisez à chaque fois en commentaire les informations que vous récupérez "à la main"

7.	M. Cyril FREDERIC ramène les livres qu’il a empruntés. Faire la mise à jour de la BD.
 Précisez à chaque fois en commentaire les informations que vous récupérez "à la main"
8.	M. Cyril FREDERIC essaye d’emprunter le livre n°23. Que constatez vous ?
9.	M. Cyril FREDERIC essaye d’emprunter le livre n°29. Que constatez vous ?

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 4 : La bibliothèque - Jointures externes et/ou requêtes imbriquées

1.	Quels sont les adhérents qui n’ont jamais emprunté de livres ?
2.	Quels sont les livres qui n’ont jamais été empruntés ?
3.	Quels sont les livres qui n’ont jamais été empruntés ?
4.	Combien d’exemplaires du titre : « Narcisse et Goldmund » sont disponibles ?
	a) On commencera par afficher tous les Narcisse.
	b) Puis tous les Narcisses actuellement empruntés
	c) Puis les Narcisses disponibles avec une requête imbriquée
	d) Enfin les Narcisse disponibles avec une jointure externes

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 5 : La bibliothèque - Requêtes avancées

1.	Pour le titre « Narcisse et Goldmund » afficher dans une même requête : 
		- le nombre d’exemplaires total, 
		- le nombre d’exemplaires disponibles 
		- et le nombre d’exemplaires actuellement empruntés.
2.	Quelle est la moyenne du nombre de livres empruntés par adhérent.
3.	Refaire la question 2 pour tous les titres actuellement sortis au moins une fois.

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 6 : La bibliothèque - Requêtes avec Thématiques

1.	Quels sont les livres "jeunesse" actuellement empruntés ?
2.	Afficher les catégories des livres actuellement empruntés.
3.	Afficher la liste des catégories par oeuvre.
4.	Quels sont les adhérents ayant empruntés des livres "jeunesse" ?
5.	Combien de titres "jeunesse" ont été empruntés par adhérents ?
	Commencer par lister les emprunts "jeunesse". Attention à compter les titres et pas les emprunts.
6.	Dans la requete précédente, afficher en plus la liste des titres empruntés.
7.	Afficher le nombre d''emprunts par catégorie.
8.	Afficher la durée moyenne des emprunts par catégorie.

*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 7 : La bibliothèque - Triggers, DDL, DML

1.	On souhaite ajouter l’attribut « emprunté » qui est un booléen et précise si un livre est actuellement emprunté ou pas. 
	Ecrire la requête qui permet d’ajouter cet attribut (ALTER TABLE).
2.	Mettre à jour les valeurs de cet attribut pour tous les tuples de la table concernée (UPDATE).
3.	Concevoir puis coder le système de triggers qui permet de gérer cet attribut calculé.
4.	On souhaite ajouter l’attribut « dureeEmprunt » qui donne la durée de l’emprunt en jours après le retour du livre. Ecrire la requête qui permet d’ajouter cet attribut (ALTER TABLE).
5.	Mettre à jour les valeurs de cet attribut pour tous les tuples de la table concernée (UPDATE).
6.	Ecrire le système de triggers qui permet de gérer cet attribut calculé.
7.	On souhaite que la date d’emprunt soit désormais un attribut automatique qui vaut automatiquement la date du jour de la création du tuple correspondant dans la BD. Comment faire ça ? Ecrire le code correspondant.
8.	On souhaite que les noms de famille soient en majuscules et les prénoms en minuscules avec seulement la première lettre en majuscule. Ecrire le système de triggers qui permet de gérer cette demande.
9.	On souhaite interdire l’emprunt d’un livre déjà emprunté. Ecrire le trigger.
10.	On souhaite interdire l’emprunt de plus de 3 livres par le même adhérent. Ecrire le trigger.
11.	On souhaite que la date de retour prenne automatiquement la valeur de la date du jour du rendu du livre. 
	On souhaite interdire un rendu le jour même de l’emprunt et que la date de retour soit postérieure à la date d’emprunt. Ecrire le trigger.


*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
SERIE 8 : La bibliothèque - Procédures Stockées

1)	Créer la BD « biblio » à partir du script fourni.
2)	Ecrire une fonction qui calcule, pour un adhérent donné, le nombre de jours restant avant d’être en retard. 
Si l’adhérent n’a pas d’emprunts en cours, on renvoie NULL. 
Si l’adhérent est en retard, on renvoie un résultat négatif correspondant au nombre de jours de retard le plus grand pour ses emprunts en cours. Par exemple, s’il devait rendre un livre avant-hier et qu’il a un livre à rendre le lendemain, on renvoie « -2 » pour avant-hier.
Si l’adhérent n’est pas en retard, on renvoie un résultat positif correspondant au nombre de jours d’emprunt restant le plus petit pour ses emprunts en cours. Par exemple, s’il doit rendre un livre demain et un autre après-demain, on renvoie « +1 » pour demain. 
(Pour ces deux derniers cas, on prendra en compte la possibilité d’avoir des emprunts avec des dureeMax différentes et des emprunts en cours avec des dates d’emprunt différentes).
3)	Utiliser cette fonction pour afficher la situation de tous les adhérents.
4)	Ecrire une procédure qui permette de lister les emprunts d’un adhérent identifié par son numéro.
5)	Ecrire une procédure qui affiche les exemplaires disponibles d’un titre (on fera une version OUTER JOIN et une version NOT IN). Pour se faciliter la tâche, on a intérêt à d’abord traiter la question : « combien y a-t-il exemplaires disponibles du titre ‘NARCISSE ET GOLDMUND’ » avec les deux versions demandées, pour ensuite passer à l’écriture de la procédure stockée.
6)	Ecrire une procédure qui affiche les titres d’un auteur et le nombre d’exemplaires disponibles par titre. On testera avec LEWIS CAROLL, GILBERT HOTTOIS et kenneth white. Pour se faciliter la tâche, on a intérêt à commencer par traiter la question : « Les exemplaires dispo de Lewis Caroll » puis « Le nombre d’exemplaires dispo par titre de Lewis Caroll » pour enfin écrire la procédure stockée.
7)	Ecrire une procédure qui permette d’enregistrer un emprunt. 
8)	Modifier la table des emprunts : mettez la valeur par défaut de la durée max à 14.
9)	Ecrire une nouvelle procédure qui enregistre un emprunt et gère tous les cas d’erreur (le livre n’existe pas, l’adhérent n’existe pas, le livre est déjà emprunté, l’adhérent emprunte déjà 3 livres, etc.). La procédure impose la date du jour comme date d’emprunt. La procédure renverra un numéro de code pour chaque erreur.
Pour résoudre ce problème, on écrira d’abord : 
une fonction qui dit si un livre est disponible ou pas,
une fonction qui renvoie le nombre de livres actuellement empruntés par un adhérent.