Negozio on line per la vendita di libri
Analisi dei dati
Entità
Le entità che possono essere individuate nel problema sono:
Libro, per rappresentare i libri che vengono venduti
Tipologia, per rappresentare gli sconti sui prezzi di copertina
Reparto, per rappresentare i reparti con i quali i libri sono organizzati
Utente, per rappresentare gli utenti registrati
Ordine, per rappresentare gli ordini degli utenti
DettaglioOrdine, per rappresentare le righe degli ordini.
Si osservi che nella classificazione dei libri non sono comprese le categorie “I più venduti” e “Ultimi arrivi”, che di fatto sono il risultato di due query.
Inoltre tra le entità non è stato inserito il Carrello Acquisti, che è soltanto l’interfaccia attraverso la quale gli utenti inviano gli ordini.
In una versione più rispettosa delle regole di normalizzazione, sarebbe opportuno introdurre le entità Formato dei libri, Carta di credito degli utenti, Autore, Editore, Collana, perché sono dati che possono comparire in modo ripetuto tra i valori degli attributi. Per semplicità della trattazione, qui vengono considerati tutti attributi.
Attributi
Gli attributi di Libro sono ISBN, Titolo, Autore, Editore, Prezzo, Numero pagine, Formato, Anno di pubblicazione, Immagine, Collana, Data di archiviazione. Tutti gli attributi sono di tipo carattere ad eccezione di Prezzo, Numero pagine, Anno, che sono di tipo numerico; inoltre Data archiviazione è di tipo Data e l’Immagine può essere un URL a un file.
Gli attributi di Reparto sono IDRreparto, Descrizione.
Gli attributi di Tipologia sono IDTipologia, Descrizione, Sconto. Lo sconto è di tipo numero decimale.
Gli attributi di Utente sono Username, Nome, Cognome, Indirizzo, CAP, Citta, Nazione, Telefono, Fax, Email, Numero Carta di credito, Tipo carta, Data scadenza (di tipo Data), Fattura (booleano, sì/no).
Gli attributi di Ordine sono IDOrdine, Data registrazione, Stato, Data evasione (entrambe di tipo Data).
Gli attributi di DettagliOrdine sono ID (numerico progressivo), Quantità (numerico intero).
Associazioni
Tra le entità Reparto e Libro esiste un’associazione uno a molti, perché un reparto organizza più libri e ogni libro è organizzato in un solo reparto.
Tra le entità Tipologia e Libro esiste un’associazione uno a molti, perché una tipologia può classificare più libri, e ogni libro può essere classificato in una sola tipologia.
Tra le entità Utente e Ordine esiste un’associazione uno a molti, perché ogni utente può effettuare più ordini, e ogni ordine deve essere effettuato da un solo utente.
Tra Ordine e Dettaglio ordine esiste un’associazione uno a molti, poiché ogni ordine specifica più dettagli ordine, e ogni dettaglio ordine è specificato da un solo ordine.
Tra Libro e Dettaglio ordine esiste un’associazione uno a molti, perché ogni libro si riferisce a più dettagli ordine, e ogni dettaglio ordine è riferito a un solo libro.
Modello E/R
Regole di lettura
Ogni reparto può organizzare uno o più libri, ogni libro deve essere organizzato in un solo reparto.
Ogni tipologia può classificare uno o più libri, ogni libro deve essere classificato in una sola tipologia.
Ogni utente può effettuare uno o più ordini, ogni ordine deve essere effettuato da un solo utente.
Ogni ordine può essere specificato in uno o più dettagli, ogni dettaglio deve specificare un solo ordine.
Ogni dettaglio deve contenere un solo libro, ogni libro può essere contenuto in un solo dettaglio di ordine.
Tabelle
Reparti (IDReparto, Descrizione)
Tipologie (IDTipologia, Descrizione, Sconto)
Libri (ISBN, Titolo, Autore, Prezzo, NumPagine, Formato, AnnoPubb, Editore, Collana, Immagine, DataArchivio, Reparto, Tipologia)
Utenti (Username, Nome, Cognome, Indirizzo, CAP, Citta, Nazione, Telefono, Fax, Email, NumeroCarta, TipoCarta, DataScadenza, Fatturazione)
Ordini (IDOrdine, DataReg, Stato, DataEvasione, CodiceUtente)
DettaglioOrdini (ID, Quantita, CodiceLibro, NumeroOrdine)
Le associazioni 1:N sono state rappresentate aggiungendo nelle tabelle vicino a molti le chiavi esterne (indicate in corsivo).
Definizione delle relazioni in linguaggio SQL
(Esempio per la tabella Libri)
CREATE TABLE Libri
(ISBN CHAR(13),
Titolo CHAR(40) Not Null,
Autore CHAR(30) Not Null,
Prezzo FLOAT Not Null,
NumPagine INT,
Formato CHAR(20),
AnnoPubb INT,
Editore CHAR(25),
Collana CHAR(20),
Immagine CHAR(25),
DataArchivio Date,
Reparto INT,
Tipologia INT,
primary key (ISBN),
foreign key (Reparto) references Reparti(IDReparto),
foreign key (Tipologia) references Tipologie(IDTipologia));
Interrogazioni
Ricerca per Reparto: libri della categoria “Da non perdere”
SELECT Libri.ISBN, Libri.Titolo, Libri.Autore, Libri.Prezzo
FROM Reparti, Tipologie, Libri
WHERE Libri.Tipologia = Tipologie.IDTipologia AND Libri.Reparto = Reparti.IDReparto AND Tipologie.Descrizione = 'Da non perdere'
AND Reparti.Descrizione = [nome del reparto]
ORDER BY Libri.Titolo;
Il parametro è indicato con le parentesi quadre.
Ricerca per Reparto: libri della categoria “I più venduti”
(supponiamo i primi 5 in testa alle vendite degli ultimi 30 giorni)
Prima occorre determinare il numero di copie vendute da ciascun libro negli ultimi 30 giorni, poi occorre selezionare i libri che hanno il codice corrispondente a quelli presenti tra i primi 5 della tabella risultante della precedente interrogazione. Useremo una tabella temporanea per salvare il risultato della prima query.
INSERT INTO Temp
SELECT Libri.ISBN As CodiceLibro, Sum(DettaglioOrdini.Quantita) As Copie
FROM Ordini, DettaglioOrdini, Libri, Reparti
WHERE Libri.Reparto = Reparti.IDReparto AND
AND Ordini.IDOrdine = DettaglioOrdini.NumeroOrdine
AND Libri.ISBN = DettaglioOrdini.CodiceLibro
AND DATEDIFF(current_date(), Ordini.Datareg) < 31
AND Reparti.Descrizione = [nome del reparto]
GROUP BY Libri.ISBN
ORDER BY Copie DESC
LIMIT 5;
Visualizziamo ora i dettagli dei primi 5 libri più venduti
SELECT Libri.ISBN, Libri.Titolo, Libri.Autore, Libri.Prezzo
FROM Libri
WHERE Libri.ISBN IN (SELECT Temp.CodiceLibro
FROM Temp);
Ordini in corso per un utente
SELECT Ordini.IDOrdine, Ordini.DataReg, DettaglioOrdini.Quantita, DettaglioOrdini.CodiceLibro
FROM Ordini, DettaglioOrdini
WHERE Ordini.IDOrdine = DettaglioOrdini.NumeroOrdine
AND Ordini.CodiceUtente = [quale username]
AND Ordini.Stato NOT IN (“evaso”, “chiuso”, “annullato”)
ORDER BY Ordini.IDOrdine;
