Lernzettel: Gestion des emprunts en bibliothèque

📋 Plan du Cours

1. Schéma relationnel de la bibliothèque
2. Optimisation des requêtes
3. Contraintes d'intégrité par trigger
4. Transactions et niveaux d'isolation
5. Mapping JPA des entités
6. Estimation des sélectivités

📖 1. Schéma relationnel de la bibliothèque

🔑 Notions clés & Définitions

• BOOKS : Relation décrivant les livres avec identifiant, titre, auteur, catégorie et un indicateur de disponibilité.
• MEMBERS : Relation décrivant les membres avec identifiant, nom, email, abonnement et pénalités cumulées.
• SUBSCRIPTIONTYPES : Relation définissant les types d’abonnement avec durée maximale et limite de livres empruntés simultanément.
• BORROWINGS : Relation des emprunts avec dates d’emprunt et de retour, et une valeur NULL pour un emprunt en cours.

📝 Points essentiels

• Un emprunt en cours est repéré par return_date = NULL dans BORROWINGS.
• La table SUBSCRIPTIONTYPES fixe max_borrow_duration (jours) et max_borrow_limit (livres simultanés).
• MEMBERS.subscription_id doit référencer un SUBSCRIPTIONTYPES, via la contrainte MEMBERS[subscription_id] ⊆ SUBSCRIPTIONTYPES[subscription_id].
• BORROWINGS.member_id référence MEMBERS[member_id] et BORROWINGS.book_id référence BOOKS[book_id].
• La pénalité pénality de MEMBERS s’ajoute quand la durée d’emprunt dépasse la durée maximale de l’abonnement st.

💡 Astuce mémo

NULL en return_date = emprunt “encore ouvert”.

📖 2. Optimisation des requêtes

🔑 Notions clés & Définitions

• Sélectivité : Mesure d’à quel point un prédicat réduit le nombre de tuples candidats, utilisée pour estimer la taille des résultats.
• Jointure Nested Join externe : Stratégie de jointure où une table est balayée (scan) et l’autre est recherchée via une méthode d’accès indexée.
• Merge Join avec filtrage : Jointure par fusion nécessitant un tri/ordre, souvent précédée de filtres pour réduire les entrées.
• Index B-arbre : Index dont un parcours de niveau correspond à une lecture de page, utile pour estimer les coûts d’accès.

📝 Points essentiels

• Avec facteur de blocage 10, 10 tuples tiennent par page, et cela permet de convertir des cardinalités en pages.
• Le parcours d’un niveau d’index B-arbre correspond à 1 page pour le calcul de coût.
• Index BOOKS : book_id (primaire) B-arbre niveau 3, et category (secondaire) linéaire de 5 pages.
• Index MEMBERS : member_id (primaire) B-arbre niveau 2, et pas d’index indiqué pour subscription_id.
• Index BORROWINGS : borrow_id (primaire) B-arbre niveau 5, member_id secondaire B-arbre niveau 2, et book_id secondaire B-arbre niveau 2.

💡 Astuce mémo

Niveaux d’un B-arbre = pages lues (1 niveau → 1 page).

📖 3. Contraintes d'intégrité par trigger

🔑 Notions clés & Définitions

• Trigger : Mécanisme exécuté automatiquement lors d’opérations de modification, ici pour contrôler l’insertion/suppression d’emprunts.
• max_borrow_limit : Limite maximale du nombre de livres empruntés simultanément pour un abonnement donné.
• Emprunt actif : Emprunt dont return_date vaut NULL, donc compté comme non encore retourné.
• Opérations d’emprunt : Evénements considérés pour la contrainte : ajout et suppression d’emprunts, sans modification des emprunts existants.

📝 Points essentiels

• La contrainte à implémenter empêche qu’un membre dépasse max_borrow_limit lors d’un nouvel emprunt.
• La vérification doit se baser sur le nombre de livres empruntés simultanément, donc sur les emprunts avec return_date = NULL.
• Un trigger doit obtenir la limite depuis l’abonnement du membre via subscription_id vers SUBSCRIPTIONTYPES.
• Comme les emprunts ne sont pas modifiés, le cas principal est l’insertion (et, selon l’implémentation, la suppression peut aussi nécessiter une mise à jour de compte).

💡 Astuce mémo

Compter les emprunts “non rendus” (return_date = NULL) avant d’insérer.

📖 4. Transactions et niveaux d'isolation

🔑 Notions clés & Définitions

• read committed : Niveau d’isolation où une lecture ne doit voir que des données validées (committed) au moment de la lecture.
• serializable : Niveau d’isolation visant un comportement équivalent à un exécution en série, au risque de bloquer ou d’échouer certaines opérations.
• Multi-versioning : Technique où plusieurs versions de données coexistent, permettant à des transactions en lecture de choisir une version à lire.
• Images Avant/Après : Représentation des valeurs lues avant modification et des valeurs produites après modification, utilisée pour raisonner sur les anomalies.

📝 Points essentiels

• Les deux sessions fonctionnent en parallèle avec un calendrier d’opérations horodatées de 1 à 20.
• Chaque session a AUTOCOMMIT désactivé et commence une transaction à la première opération suivant un COMMIT.
• Session 1 reste en read committed, et Session 2 commence en read committed puis passe à serializable à l’opération 12.
• Les tags demandent le résultat de requêtes aux points  à , tandis que les autres opérations (insert/update/delete) modifient la table BW.
• Les cas de lecture répétée à expliquer portent sur les tags  et  (lecture répétée) et  et  (lecture répétée).

💡 Astuce mémo

read committed = pas de lecture des versions non validées; serializable = cohérence “comme une série”.

📖 5. Mapping JPA des entités

🔑 Notions clés & Définitions

• Entité Member : Classe JPA représentant un membre, liée à son abonnement via subscription_id.
• Entité SubscriptionType : Classe JPA correspondant au type d’abonnement, avec notamment max_borrow_duration et max_borrow_limit.
• Entité Borrowing : Classe JPA représentant un emprunt, reliant un livre et un membre avec des dates dont return_date peut être NULL.
• Annotations de mapping : Métadonnées JPA (relations et colonnes) nécessaires pour relier correctement les champs aux clés étrangères.

📝 Points essentiels

• Le problème demande de compléter les annotations pour Member et Borrowing (les relations ne sont pas forcément bidirectionnelles).
• Member doit être mappé avec une association vers SubscriptionType via subscription_id.
• Borrowing doit être mappé avec ses associations vers Member et vers Book via member_id et book_id.
• Les annotations doivent refléter que return_date peut prendre la valeur NULL dans Borrowing.

💡 Astuce mémo

Fidèle aux clés étrangères : subscription_id pour Member, member_id et book_id pour Borrowing.

📖 6. Estimation des sélectivités

🔑 Notions clés & Définitions

• Facteur de sélectivité S(p) : Valeur reliant la sélectivité d’un prédicat p à la cardinalité des ensembles filtrés.
• CARD(X) : Cardinalité de l’ensemble X, notée dans la formule pour déterminer S quand la taille est connue.
• Sélectivité de la conjonction : Règle de combinaison des sélectivités pour F = F1 ∧ F2, basée sur une hypothèse de dépendance simplifiée.
• Sélectivité de la disjonction : Règle de combinaison des sélectivités pour F = F1 ∨ F2, basée sur une règle d’inclusion-exclusion en termes de S.

📝 Points essentiels

• Si card(X) est connue, alors S = 1/CARD(X) pour un prédicat correspondant à un ensemble X.
• Si card(X) n’est pas connue, alors S = 1/10 par défaut pour estimer la sélectivité.
• Pour F = F1 ∨ F2, S(F) = S(F1) + S(F2) - S(F1)*S(F2), et pour F = F1 ∧ F2, S(F) = S(F1)*S(F2).
• Pour F = ¬F1, S(F) = 1 - S(F1).
• Pour un prédicat de type X > valeur (ou X < valeur), S se calcule avec une fraction basée sur (valmax - valeur) ou (valeur - valmin) sur (valmax - valmin).

💡 Astuce mémo

OR : inclusion-exclusion; AND : produit; NOT : 1 − S.

📊 Tableaux de synthèse

Comparaison des plans P1 et P2

Plan	Type de jointure	Filtres avant jointure
P1	Nested Join	Aucun filtrage anticipé indiqué, MEMBERS scanné par Full Scan et index sur member_id côté interne
P2	Merge Join	Filtrage préalable sur M.subscription_id = 1 et B.category = "thriller" puis tri buffer de 10p

⚠️ Pièges & confusions fréquents

1. Confondre le niveau d’index B-arbre avec le nombre de tuples et oublier que 1 niveau correspond à 1 page.
2. Interpréter return_date = NULL comme une date réelle et non comme un marqueur d’emprunt en cours.
3. Appliquer la limite max_borrow_limit sans compter seulement les emprunts simultanés (donc seulement ceux avec retour non encore fait).
4. Raisonner en read committed comme si un SELECT pouvait voir des modifications non validées d’une autre session.
5. Mélanger les formules de sélectivité pour OR/AND/NOT et introduire une autre règle que celle donnée dans l’annexe.

✅ Checklist Examen

1. Calculer Q1 : l’estimation de la cardinalité (nombre de tuples) du résultat de la requête RA avec return_date is NULL.
2. Calculer Q2 : le coût d’exécution pour P1 puis P2, et conclure sur le meilleur plan pour RA.
3. Proposer Q3 : un arbre algébrique optimal pour la requête RB (sans optimiser la projection).
4. Proposer Q4 : un chemin d’accès optimal pour RB basé sur Nested Join, en exploitant intelligemment les index existants.
5. Calculer Q5 : l’estimation du coût pour le plan obtenu en Q4 pour RB.
6. Écrire Q6 : un trigger qui empêche un membre d’emprunter plus que max_borrow_limit, en ne considérant que ajouts et suppressions d’emprunts.
7. Donner Q7 : le résultat renvoyé aux points  à , en associant chaque tag à la requête correspondante.
8. Expliquer Q8 : les lectures répétées aux tags ,  puis ,  via multi-versioning (lecture) et images Avant/Après.
9. Compléter Q9 : les annotations JPA de mapping pour les classes Member et Borrowing, en respectant les associations et la non-bidirectionnalité éventuelle.
10. Savoir utiliser l’annexe : calculer des sélectivités S(p) pour valeurs simples, inconnues, plages, et combinaisons OR/AND/NOT.