Aller au contenu

Base Bibliothèque⚓︎

Soit le système d’information simplifié d’une bibliothèque dont voici le schéma relationnel :

base bibliothèque

Télécharger fichier Création de la base BIBLI ⬇️

  1. Ecrire le schéma relationnel "en intention" ou détaillé de la base bibilothèque
  2. Expliquer pourquoi le champ dateemprunt fait partie de la clé primaire.
  3. Écrire les requêtes suivantes :

R1 : Qui est le lecteur n°30 ?
R2 : Liste des lecteurs dont le nom de famille commence par la lettre « S »
R3 : Liste des ouvrages qui traitent de la méthode Merise.
R4 : Liste des ouvrages qui traitent du « registre » à partir de l’année 2000.
R5 : Liste des lecteurs qui portent le nom (ou le prénom) « BERTRAND »
R6 : Quels sont le titre et l’année de parution de l’ouvrage le plus ancien ?
R7 : Titre des ouvrages actuellement empruntés ou ayant déjà été empruntés
R8 : Nom des lecteurs ayant effectué au moins un emprunt depuis le début de l’année 2012.
R9 : Liste des emprunts (titres et dates) réalisés par le lecteur nommé « HAMSI »
R10 : Liste des emprunts en cours. Pour chaque emprunt, nom du lecteur, titre de l’ouvrage et date d’emprunt.
R11 : Nombre total d’emprunts enregistrés à ce jour.
R12 : Ajouter l’ouvrage suivant dans la base : N°40, Titre ouvrage : « Réseaux de neurones », Année de parution : 2004
R13 : Enregistrer le retour de l’ouvrage n°17 à la date du jour. La fonction date() retourne la date du système.
R14 : Supprimer tous les emprunts régularisés (ouvrages restitués) de l’année 2010.
R15 : Combien reste t-il d’emprunts non régularisés pour l’année 2011 ?
R16 : Nombre total d’emprunts réalisés par lecteurs.

Un nouveau lecteur souhaite emprunter un livre sur l’intelligence artificielle. La bibilothèque n’est dispose. Elle le commande pour que ce lecteur puisse l’emprunter dès que possible.
Comment cela se matérialise dans la base de donnée ?
R17 : Insérer le nouveau lecteur
R18 : Insérer le livre
R19 : Insérer l’emprunt
R20 :Il y a une faute d’écriture sur le nom de ce client. Corrigez là.
R21 : On introduit un nouvel attribut à la table lecteur, le nombre de livre autorisé en emprunt. Mettez cette valeur à jour à 4.
R22 : Une mise à jour de la bibliothèque est nécessaire. Faites sortir du stock tous les livres antérieur à 2000.

Télécharger fichier Correction de la base BIBLI ⬇️

R1 : Qui est le lecteur n°30 ?
🐬 SQL
SELECT*
FROM LECTEUR
WHERE NumLecteur = 30;
R2 : Liste des lecteurs dont le nom de famille commence par la lettre « S »
🐬 SQL
SELECT*
FROM LECTEUR
WHERE NomLecteur LIKE "S*";
R3 : Liste des ouvrages qui traitent de la méthode Merise.
🐬 SQL
SELECT*
FROM OUVRAGE
WHERE TitreOuvrage LIKE "*Merise*";
R4 : Liste des ouvrages qui traitent du « registre » à partir de l’année 2000.
🐬 SQL
SELECT*
FROM OUVRAGE
WHERE TitreOuvrage LIKE "*Registre*"
AND AnneeParution > 2000;
R5 : Liste des lecteurs qui portent le nom (ou le prénom) « BERTRAND »
🐬 SQL
SELECT*
FROM LECTEUR
WHERE NomLecteur = "BERTRAND"
OR PrenomLecteur = "Bertrand";
R6 : Quels sont le titre et l’année de parution de l’ouvrage le plus ancien ?
🐬 SQL
SELECT  TitreOuvrage, AnneeParution
FROM OUVRAGE
WHERE AnneeParution = (SELECT MAX(AnneeParution)
FROM OUVRAGE)
ORDER BY TitreOuvrage ASC;
R7 : Titre des ouvrages actuellement empruntés ou ayant déjà été empruntés
🐬 SQL
SELECT DISTINCT TitreOuvrage
FROM OUVRAGE
INNER JOIN EMPRUNTER
ON OUVRAGE.NumOuvrage = EMPRUNTER.NumOuvrage
WHERE DateEmprunt IS NOT NULL; /* La clause WHERE est ici facultative*/
R8 : Nom des lecteurs ayant effectué au moins un emprunt depuis le début de l’année 2012.
🐬 SQL
SELECT DISTINCT NomLecteur
FROM LECTEUR
INNER JOIN EMPRUNTER
ON LECTEUR.NumLecteur = EMPRUNTER.NumLecteur
WHERE DateEmprunt LIKE  "*/*/2012";
R9 : Liste des emprunts (titres et dates) réalisés par le lecteur nommé « HAMSI »
🐬 SQL
SELECT OUVRAGE.TitreOUvrage, OUVRAGE.AnneeParution
FROM ((LECTEUR
INNER JOIN EMPRUNTER
ON LECTEUR.NumLecteur = EMPRUNTER.NumLecteur)
INNER JOIN OUVRAGE
ON OUVRAGE.NumOuvrage = EMPRUNTER.NumOuvrage)
WHERE NomLecteur ="HAMSI";
R10 : Liste des emprunts en cours. Pour chaque emprunt, nom du lecteur, titre de l’ouvrage et date d’emprunt.
🐬 SQL
SELECT LECTEUR.NomLecteur, OUVRAGE.TitreOUvrage, EMPRUNTER.DateEmprunt
FROM ((LECTEUR
INNER JOIN EMPRUNTER
ON LECTEUR.NumLecteur = EMPRUNTER.NumLecteur)
INNER JOIN OUVRAGE
ON OUVRAGE.NumOuvrage = EMPRUNTER.NumOuvrage)
WHERE DateRetour IS NULL;
R11 : Nombre total d’emprunts enregistrés à ce jour.
🐬 SQL
SELECT COUNT(DateEmprunt) AS NbEmprunt
FROM EMPRUNTER;
R12 : Ajouter l’ouvrage suivant dans la base : N°40, Titre ouvrage : « Réseaux de neurones », Année de parution : 2004
🐬 SQL
INSERT INTO OUVRAGE (NumOuvrage, TitreOuvrage, AnneeParution )
VALUES (40,"Réseaux de neurones",2004);
R13 : Enregistrer le retour de l’ouvrage n°17 à la date du jour. La fonction date() retourne la date du système.
🐬 SQL
UPDATE EMPRUNTER 
SET  DateRetour = Date ()
WHERE NumOUvrage = 17
AND DateRetour IS NULL ;
R14 : Supprimer tous les emprunts régularisés (ouvrages restitués) de l’année 2010.
🐬 SQL
DELETE FROM EMPRUNTER
WHERE DateEmprunt LIKE "*/*/2010"
AND DateRetour IS NOT NULL;
R15 : Combien reste t-il d’emprunts non régularisés pour l’année 2011 ?
🐬 SQL
SELECT COUNT (DateEmprunt) AS NbOK2011
FROM EMPRUNTER
WHERE DateEmprunt LIKE "*/*/2011"
AND DateRetour IS NULL;
R16 : Nombre total d’emprunts réalisés par lecteurs.

🐬 SQL
SELECT COUNT(EMPRUNTER.DateEmprunt), LECTEUR.NomLecteur
FROM EMPRUNTER
INNER JOIN LECTEUR
ON LECTEUR.NumLecteur = EMPRUNTER.NumLecteur
GROUP BY LECTEUR.NomLecteur ;

R17 : Insérer le nouveau lecteur
🐬 SQL
INSERT INTO LECTEUR (`numlecteur`, `nomlecteur`, `prenomlecteur`) VALUES
(13, 'ALAMARD', 'Frédéric') ;
R18 : Insérer le livre
🐬 SQL
INSERT INTO OUVRAGE (`numouvrage`, `titreouvrage`, `anneeparution`) VALUES
(1, 'Passeport pour l''algorithmique objet', 1998) ;
R19 : Insérer l’emprunt
🐬 SQL
INTO EMPRUNTER (`numlecteur`, `numouvrage`, `dateemprunt`, `dateretour`) VALUES (13, 17, '2012-01-18', NULL) ;
R20 :Il y a une faute d’écriture sur le nom de ce client. Corrigez là.
🐬 SQL
UPDATE LECTEUR 
SET ‘nomlecteur’= ‘ALAMART’
WHERE ‘nomlecteur’= ‘ALAMARD’ AND `prenomlecteur` = ‘'Frédéric' ;
R21 : On introduit un nouvel attribut à la table lecteur, le nombre de livre autorisé en emprunt. Mettez cette valeur à jour à 4.
🐬 SQL
UPDATE LECTEUR 
SET nbLibreSorti = 4 ;
R22 : Une mise à jour de la bibliothèque est nécessaire. Faites sortir du stock tous les livres antérieur à 2000.
🐬 SQL
DELETE FROM OUVRAGE 
WHERE anneeparution < 2000 ;