Progetto di Basi di Dati
Anno accademico 2023/24
Biblioteca Babele
Alessandro Di Pasquale - 2075544
Alessandro Bernardello - 2077674
Biblioteca Babele è una biblioteca pubblica, munita di svariate sedi, che permette la libera iscrizione degli utenti.
Ogni utente è in grado di richiedere prestiti ed effettuare donazioni di libri.
La biblioteca è suddivisa in aree, ognuna del corrispettivo genere, da cui si possono sottrarre libri per darli in prestito agli utenti, similmente è possibile aggiungerne tramite donazioni degli stessi. Si possono spostare i libri in altre aree (per esempio, nel caso in cui ci fossero dei libri occasione, verrebbero ricollocati in un’apposita Area temporanea, per poi tornare in quella d’appartenenza).
Ogni libro è identificabile univocamente dal suo codice ISBN e numero di copia e può essere di una fra 3 tipologie, ovvero brossura, tascabile e illustrato.
La biblioteca presenta anche delle aule studio adibite appunto allo studio individuale ed alla consultazione del materiale preso in prestito.
Per usufruire dell’aula studio è necessario effettuare in anticipo una prenotazione utilizzando l’apposito portale web, che consente inoltre di visualizzare i posti liberi.
La biblioteca conserva in magazzino delle copie di riserva di alcuni libri che potrebbero
venire danneggiati richiedendo dunque di essere sostituiti, contiene anche i nuovi arrivi
che devono ancora essere classificati e disposti nelle apposite Aree.
La biblioteca organizza molteplici Eventi che avranno luogo nella rispettiva Aula Magna ed ogni
Utente nel momento dell’iscrizione alla biblioteca può decidere se iscriversi anche
ad una Newsletter di suo interesse che lo notifichi degli Eventi di quel genere.
2.1 Descrizione
Il database gestisce tutta la logistica di distribuzione dei libri nei vari magazzini e nelle aree delle rispettive sedi. Vengono inoltre amministrati tutti i servizi a disposizione dell’utente, dunque iscrizione alla biblioteca, alla newsletter e la prenotazione di un posto a sedere in una qualsiasi aula di una qualunque sede.
Un Libro è definito da:
- Un codice ISBN (codice univoco standard per i libri)
- Un Numero Copia (codice numerico autoincrementale aggiunto ad ogni libro, indipendentemente dal
suo ISBN)
- Un Editore
- Un Titolo
Esistono tre tipologie di libri: Brossura, Tascabile ed Illustrato
I libri possono essere conservati in Magazzino oppure esposti in un Area.
Un Magazzino è definito da:
Un’area invece sarà descritta da:
Ogni area apparterrà ad una Sede fisica, questa sarà descritta con:
I libri potranno essere prestati ad Utenti oppure donati da uno di essi.
Per ogni prestito dovranno essere registrate:
Ogni utente viene definito come:
Gli utenti possono prenotare posti a sedere nelle varie aule adibite allo studio.
Un posto verrà prenotato fino a fine giornata.
Per ogni prenotazione si vorrà quindi tener traccia di:
Un posto a sedere è caratterizzato da:
I posti a sedere si trovano all’interno di determinate Aule.
Le aule a loro volta apparterranno alle rispettive Sedi.
Ogni aula viene descritta da:
Un’aula può essere un’aula studio oppure un’aula magna
Un’aula studio è descritta da:
In un’aula magna possono essere organizzati eventi di vario genere, come ad esempio, la presentazione di un libro.
Un evento verrà definito con:
Un evento può essere incluso in una o più newsletter.
Una newsletter è caratterizzata da:
2.2 Glossario
Newsletter: Lista a cui ci si iscrive per ricevere in automatico notifiche o avvisi di determinati eventi riguardanti l’area di interesse della newsletter stessa. Quest’ultima è spesso messa a disposizione, come in questo caso, da chi accoglie i vari eventi, che può disporre di molteplici newsletter per ogni tipologia di evento.
Posto: Rappresenta un posto a sedere in un'Aula di qualsiasi genere.
Evento: Incontro generico organizzato in Aula Magna (i.e. Firmacopie, presentazione di un libro, spettacolo, etc.)
Area: Inteso come scaffale o serie di scaffali contenenti tutti i libri di una determinata categoria (i.e. Biologia, narrativa, filosofia, etc.), ogni Area è accessibile dagli utenti.
Numero Copia: Ogni libro possiede il suo codice ISBN, inoltre vi è il “Numero Copia”, ovvero un codice univoco autoincrementale che viene aggiunto ad ogni libro una volta che la biblioteca se ne impossessa.
2.3 Operazioni Frequenti
Operazione | Frequenza |
Donazione di un Libro da parte di un Utente | 200 al giorno |
Iscrizione ad una Newsletter | 300 al giorno |
Aggiunta di un nuovo Utente | 400 al giorno |
Prenotazione di un Posto in un’Aula | 2000 al giorno |
Prestito di un Libro ad un Utente | 2500 al giorno |
Ricerca di tutte le copie di un Libro dal titolo | 3000 al giorno |
Visualizzazione posti disponibili in un’Aula | 5000 al giorno |
3.1 Descrizione Entità
Ogni attributo è NOT NULL a meno che non venga esplicitamente detto diversamente.
Libro
Libro si suddivide ulteriormente in:
Brossura
Tascabile
Illustrato
Prestito
Magazzino
Utente
Area
Sede
Posto
Prenotazione
Aula
Aula si suddivide ulteriormente in:
AulaStudio
AulaMagna
Newsletter
Evento
3.2 Descrizione Relazioni
Relazione | Cardinalità delle entità coinvolte | Descrizione |
Contenimento | Libro (0,1) | Un libro può essere contenuto in una sola area. |
Iscrizione | Newsletter(0,N) | Un utente può essere iscritto a più newsletter Ad una newsletter possono essere iscritti più utenti |
Collocazione | Aula(0,N) | Un posto è in una sola aula Un'aula può avere più |
Inclusione | Newsletter (1,N) Evento (0,N) | Una Newsletter può includere più eventi Un Evento può essere incluso in più Newsletter |
Conservazione | Magazzino(0,N) | Un libro può essere contenuto in un solo magazzino. |
Posizione | Aula(1,1) | Un’aula si trova in una singola sede. |
Reperibilità | Area(1,1) | Un’area si trova in una sola sede |
Comprensione | Libro(0,N) | Un libro può essere compreso in nessuno o più prestiti passati |
Effettuazione | Prestito(1,1) | Un prestito di un libro può essere effettuato ad un solo utente destinatario |
Richiesta | Utente(0,N) | Un utente può richiedere nessuna o più prenotazioni di un posto a sedere |
Allocazione | Prenotazione(1,1) | Ad una prenotazione può essere allocato unicamente un posto |
Donazione | Libro(1,1) | Un libro può essere donato da un singolo utente Un utente può donare più libri |
Organizzazione | Evento (1,1) AulaMagna (0,N) | Un Evento può essere organizzato unicamente in un’AulaMagna In un’AulaMagna è possibile organizzare più eventi |
3.3 Lista Generalizzazioni
Aula: Generalizzazione totale ed esclusiva di AulaStudio e AulaMagna.
Libro: Generalizzazione totale ed esclusiva di Brossura, Tascabile e Illustrato.
3.4 Schema Concettuale E-R
3.5 Regole Gestionali
Un utente prenota al più un singolo posto alla volta che rimarrà prenotato tutto il giorno.
Un libro non può essere contemporaneamente contenuto in un’area e conservato in magazzino o in prestito ad un utente.
Un libro può essere prenotato da un solo utente alla volta.
4.1 Analisi Ridondanze
Durante la revisione dello schema ER l’attributo PostiDisponibili di Aula è stato identificato come ridondante dato che il numero di posti disponibili è ricavabile anche senza farne uso, pertanto di seguito viene riportata l’analisi delle ridondanze condotta per determinarne la convenienza di utilizzo.
Concetto | Costrutto | Volume |
Aula | Entità | 150 |
Posto | Entità | 4500 |
Prenotazione | Entità | 2000 |
Collocazione | Relazione | 4500 |
Allocazione | Relazione | 2000 |
L’ammontare dei posti totale deriva dalla capienza media di un’aula di 30 posti.
4.1.1 Operazione 1
Visualizzazione i posti disponibili in un’Aula (5.000 volte al giorno)
Con ridondanza:
Concetto | Costrutto | Accesso | Tipo |
Aula | Entità | 1 | Lettura |
Si effettua 1 access
o di tipo lettura all’attributo PostiDisponibili di Aula circa 5000 volte al giorno.
Il costo totale ammonta a 5000*1 = 5000 letture al giorno.
Senza ridondanza:
Concetto | Costrutto | Accesso | Tipo |
Aula | Entità | 1 | Lettura |
Posto | Entità | 30 | Lettura |
Prenotazione | Entità | 1 | Lettura |
Si effettua 1 accesso di tipo lettura ad Aula a cui si sommano 30 accessi in lettura ai rispettivi posti dell’aula e 1 accesso in lettura per verificare l’occupazione dei posti nella data corrente.
Il costo totale sarà (1+30+1)*5000 = 160.000
4.1.2 Operazione 2
Prenotazione di un Posto in un’Aula (2.000 volte al giorno)
Con ridondanza:
Concetto | Costrutto | Accesso | Tipo |
Prenotazione | Entità | 1 | Scrittura |
Aula | Entità | 1 | Lettura |
Aula | Entità | 1 | Scrittura |
Allocazione | Relazione | 1 | Scrittura |
Viene effettuato un accesso in scrittura all’entità Prenotazione e la relativa relazione Allocazione per segnare il posto nel sistema come prenotato. Viene fatto un accesso all’entità Aula per leggere i posti disponibili e un accesso in scrittura per decrementare i posti disponibili stessi.
Il costo totale, considerando che una scrittura vale il doppio, ammonta a 2.000*(2+1+2+2) = 14.000
Senza ridondanza:
Concetto | Costrutto | Accesso | Tipo |
Prenotazione | Entità | 1 | Scrittura |
Allocazione | Relazione | 1 | Scrittura |
Vengono effettuate le scritture necessarie per registrare l’occupazione del posto sull’entità Prenotazione e sulla relazione Relazione.
Il costo totale, considerando che una scrittura vale il doppio, ammonta a 2.000*(2+2) = 8.000
4.1.3 Conclusioni Analisi Ridondanze
Confrontiamo quindi le operazioni con la presenza o la rimozione della ridondanza:
Sulla base di questi risultati si è giunti alla conclusione di mantenere la ridondanza.
4.2 Eliminazione Generalizzazioni
Aula è una generalizzazione totale ed esclusiva, legata da:
L’entità figlia AulaMagna si relaziona con:
Dato che l'entità figlia AulaMagna, a differenza di quanto accade per Aula ed AulaStudio, possiede una relazione con l’entità Evento, si è deciso di eliminare la generalizzazione e realizzare due entità separate per AulaMagna ed AulaStudio.
Libro è una generalizzazione totale ed esclusiva, legata da:
Dato che nessuna delle entità figlie presenta attributi propri, si è deciso di accorpare le entità figlie all’entità genitore Libro.
4.3 Partizionamento/Accorpamento Entità e Relazioni
Le entità Sede, Magazzino ed Utente possiedono tutte un attributo composto Indirizzo.
Siccome è possibile avere zone utilizzate come magazzino nello stesso stabile di una sede della Biblioteca o più utenti che risiedono nello stesso indirizzo, l’attributo è ridondante.
Viene quindi creata una nuova entità indirizzo, contenente i seguenti attributi:
La nuova entità avrà relazioni 1 a 1 con Sede, Utente e Magazzino.
4.4 Scelta Identificatori Primari
Per la nuova entità Indirizzo è stato deciso di adoperare come chiave primaria l’insieme di CAP, Via e Civico, in quanto si assume che tramite la loro combinazione sia possibile identificare univocamente ogni indirizzo. Verranno mantenuti Comune e Provincia, ma come attributi non primari.
4.5 Diagramma E-R Ristrutturato
4.6 Descrizione Schema Relazionale & Vincoli Integrità Referenziale
Ogni attributo contrassegnato con un asterisco viene inteso come opzionale (Attributo*).
Ogni attributo sottolineato viene inteso come primario (Attributo).
Libro(ISBN, NumeroCopia, Tipologia, Titolo, Anno, Editore, Autore, NomeArea*, NomeSede*, MagazzinoCAP*, MagazzinoVia*, MagazzinoCivico*, CFUtente*)
I.R: Libro.(NomeArea, NomeSede) → Area.(Nome, NomeSede)
I.R: Libro.(MagazzinoCAP, MagazzinoVia, MagazzinoCivico) → Magazzino.(CAP, Via, Civico)
I.R: Libro.CFUtente → Utente.CF
Indirizzo(CAP, Via, Civico, Comune, Provincia)
Utente(CF, Email, Nome, Cognome, CAP, Via, Civico)
I.R: Utente.(CAP, Via, Civico) → Indirizzo.(CAP, Via, Civico)
Prestito(DataInizio, DataFine, ISBN, NumeroCopia, CFUtente)
I.R: Prestito.(ISBN, NumeroCopia) → Libro.(ISBN, NumeroCopia)
I.R: Prestito.CFUtente → Utente.CF
Sede(Nome, AnnoApertura, CAP, Via, Civico)
I.R: Sede.(CAP, Via, Civico) → Indirizzo.(CAP, Via, Civico)
Magazzino(CAP, Via, Civico, Capienza)
I.R: Magazzino.(CAP, Via, Civico) → Indirizzo.(CAP, Via, Civico)
Area(NomeSede, Nome, Capienza)
I.R: Area.NomeSede → Sede.Nome
Posto(ID, NomeSedeAulaStudio*, NomeSedeAulaMagna*, NomeAulaStudio*, NomeAulaMagna*)
I.R: Posto.(NomeAulaStudio, NomeSedeAulaStudio) → AulaStudio.(Nome, NomeSede)
I.R: Posto.(NomeAulaMagna, NomeSedeAulaMagna) → AulaMagna.(Nome, NomeSede)
Prenotazione(Data, IDPosto, CFUtente)
I.R: Prenotazione.CFUtente → Utente.CF
I.R: Prenotazione.IDPosto → Posto.ID
AulaStudio(NomeSede, Nome, PostiDisponibili, Internet)
I.R: AulaStudio.NomeSede → Sede.Nome
AulaMagna(NomeSede, Nome, PostiDisponibili)
I.R: AulaMagna.NomeSede → Sede.Nome
Newsletter(Nome, Tema*)
Evento(NomeSedeAula, NomeAula, Data, Ora, Titolo)
I.R: Evento.(NomeAula, NomeSedeAula) → AulaMagna.(Nome, NomeSede)
Iscrizione(CFUtente, NomeNewsletter)
I.R: Iscrizione.CFUtente → Utente.CF
I.R: Iscrizione.NomeNewsletter → Newsletter.Nome
Inclusione(NomeSedeEvento, NomeAulaEvento, DataEvento, OraEvento, NomeNewsletter)
I.R: Inclusione.NomeNewsletter → Newsletter.Nome
I.R: Inclusione.(OraEvento, DataEvento, NomeAulaEvento, NomeSedeEvento)
→ Evento(Ora, Data, NomeAula, NomeSedeAula)
5.1 Query
5.1.1: Visualizzazione di tutti i libri custoditi nei vari magazzini
SELECT l.ISBN, l.Titolo, l.Autore, m.CAP, m.Via, m.Civico
FROM Libro l
JOIN Magazzino m ON l.MagazzinoCAP = m.CAP AND l.MagazzinoVia = m.Via AND l.MagazzinoCivico = m.Civico
ORDER BY m.CAP, m.Via, m.Civico, l.Titolo;
5.1.2: Visualizzazione Numero totale di libri in un area per ogni sede (GROUP BY) (i.e. Libri appartenenti all’area Saggistica)
SELECT a.NomeSede AS Sede, a.Nome AS Area, COUNT(l.ISBN) AS TotaleLibri
FROM Area a
LEFT JOIN Libro l ON a.Nome = l.NomeArea AND a.NomeSede = l.NomeSede
WHERE a.Nome = 'Saggistica'
GROUP BY a.NomeSede, a.Nome
ORDER BY TotaleLibri DESC;
5.1.3: Visualizzare i libri più popolari per numero di prestiti effettuati (GROUP BY)
SELECT L.ISBN, L.Titolo, COUNT(P.ISBN) AS NumeroPrestiti
FROM Libro L
LEFT JOIN Prestito P ON L.ISBN = P.ISBN
GROUP BY L.ISBN, L.Titolo
ORDER BY NumeroPrestiti DESC;
5.1.4: Visualizzare le informazioni principali di tutte le copie di un determinato Libro in base al titolo
SELECT ISBN, NumeroCopia, Editore, Titolo, Autore, Anno, Tipologia
FROM Libro WHERE Titolo = '1984';
5.1.5 Visualizzare gli eventi che sono inclusi in più di una newsletter, mostrando i dettagli dell’evento ed il numero di newsletter (GROUP BY + HAVING)
SELECT E.Titolo, E.NomeSedeAula, E.NomeAula, E.Data, E.Ora, COUNT(I.NomeNewsletter) AS NumNewsletter
FROM Evento E
JOIN Inclusione I ON E.NomeSedeAula = I.NomeSedeEvento
AND E.NomeAula = I.NomeAulaEvento
AND E.Data = I.DataEvento
AND E.Ora = I.OraEvento
GROUP BY E.Titolo, E.NomeSedeAula, E.NomeAula, E.Data, E.Ora
HAVING COUNT(I.NomeNewsletter) > 1
ORDER BY NumNewsletter DESC;
5.1.6 Visualizzare tutti i libri attualmente in prestito con i dettagli dell’utente che li ha presi in prestito
SELECT l.ISBN, l.Titolo, l.Autore, u.Nome, u.Cognome, p.DataInizio, p.DataFine
FROM Libro l
JOIN Prestito p ON l.ISBN = p.ISBN AND l.NumeroCopia = p.NumeroCopia
JOIN Utente u ON p.CFUtente = u.CF
WHERE p.DataFine > CURRENT_DATE;
5.2 Indici
Gli utenti ricercano ogni giorno circa 3000 libri in base al titolo per verificare che siano di dominio della Biblioteca Babele e per averne una panoramica generale dei principali dettagli. Per ottimizzare al meglio la query 5.1.4, che serve a svolgere tale operazione, si è deciso di creare un index sull’attributo Titolo dell’entità Libro.
CREATE INDEX IF NOT EXISTS idx_titololibro ON Libro(Titolo)
L’indice riportato è di tipo B-Tree, più conveniente nel nostro caso in quanto gli indici B-Tree sono molto efficienti per le operazioni di ricerca, come l'uguaglianza e l’intervallo e quando si cerca un libro per il titolo, l'indice riduce il numero di pagine del disco da esaminare, permettendo di trovare rapidamente i record.
È stato realizzato un applicativo per il collegamento al database ed effettuare query parametriche.
Segue screenshot della schermata principale:
Per selezionare le operazioni da fare basta muoversi nel menu tramite le freccette e confermare la selezione premendo il tasto invio.
Per compilare il codice basterà eseguire il seguente comando:
gcc -I/usr/include/postgresql AleDB.c -lpq (testato su ubuntu 18.04)
È possibile cambiare i parametri di connessione al database modificando le righe da 17 a 21.
NB: Il codice fa ampio utilizzo di sequenze di escape ANSI per la visualizzazione delle interfacce, è necessario quindi utilizzare un terminale che le supporti.
