Esercizi di appelli svolti di Basi di dati e Laboratorio web

Esercizio query luglio

Si consideri il seguente schema biblioteca:

1) Le date di morte degli autori dei libri letti da Paolo Bianchi

SELECT DISTINCT A.datamorte

FROM Cliente AS C, Prestito AS P, Libro AS L, Autore AS A

WHERE L.codiceAL = A.codiceA AND C.ntess = P.ntessP AND P.codiceLP = L.codiceL AND C.nome = “Paolo” AND C.cognome = “Bianchi” AND P.res IS NOT NULL

2) I titoli dei libri di Emilio Salgari che hanno letto Giovanni Bianchi e Paolo Neri

SELECT DISTINCT L.titolo

FROM Cliente AS C, Prestito AS P, Libro AS L, Autore AS A

WHERE L.codiceAL = A.codiceA AND C.ntess = P.ntessP AND P.codiceLP = L.codiceL AND A.nome = “Emilio” AND A.cognome = “Salgari” AND P.res IS NOT NULL AND C.nome = “Giovanni” AND C.cognome = “Bianchi”

INTERSECT

SELECT DISTINCT L.titolo

FROM Cliente AS C, Prestito AS P, Libro AS L, Autore AS A

WHERE L.codiceAL = A.codiceA AND C.ntess = P.ntessP AND P.codiceLP = L.codiceL AND A.nome = “Emilio” AND A.cognome = “Salgari” AND P.res IS NOT NULL AND C.nome = “Paolo” AND C.cognome = “Neri”

3) Le età dei lettori che hanno dato il massimo giudizio tra quelli espressi su Pinocchio di Carlo Collodi

SELECT DISTINCT C.età

FROM Review AS R, Cliente AS C, Libro AS L, Autore AS A

WHERE L.codiceAL = A.codiceA AND C.ntess = R.ntessP AND R.codiceLR = L.codiceL AND L.titolo = “Pinocchio” AND A.nome = “Carlo” AND A.cognome = “Collodi” AND R.giudizio = (SELECT MAX (R1.giudizio) FROM Review AS R1, Libro AS L1, Autore AS A1 WHERE L1.codiceAL = A1.codiceA AND R1.codiceLR = L1.codiceL AND L1.titolo = “Pinocchio” AND A1.nome = “Carlo” AND A1.cognome = “Collodi”)

4) Tutti i nomi dei lettori che hanno letto prima un libro scritto da Emilio Salgari e poi uno scritto da Umberto Eco

SELECT DISTINCT C.nome, C.cognome

FROM Cliente AS C, Libro AS L, Autore AS A, Prestito AS P, Libro AS L1, Autore AS A1, Prestito AS P1

[Le tabelle libro, autore e prestito compaiono due volte perché vanno confrontate le date di restituzione di libri di due autori diversi presi da uno stesso cliente]

WHERE L.codiceAL = A.codiceA AND C.ntess = P.ntessP AND P.codiceLP = L.codiceL AND P1.ntessP = C.ntess AND P1.codiceLP = L1.codiceL AND L1.codiceAL = A1.codiceA AND A.nome = “Emilio” AND A.cognome = “Salgari” AND A1.nome = “Umberto” AND A1.cognome = “Eco” AND P.res < P1.res

5) Tutti gli indirizzi di posta elettronica, senza ripetizioni, per sollecitare chi ha in prestito un libro da almeno 30 giorni

SELECT DISTINCT C.indirizzo

FROM Cliente AS C, Prestito AS P WHERE C.ntess = P.ntessP AND P.res IS NULL AND P.prel <= now() – interval30day

[Data corrente – 30 giorni -> se è minore sono passati più di 30 giorni]

Ridondanza con libri, editori e ordini

Sapendo che il:

• 1. Il costo di una scrittura è di 5 volte quello di una lettura,
• 2. Vi sono 10000 libri e 200 editori,
• 3. Per ogni editore vi sono 10 ordini in media

Le operazioni di interesse sono

• Op1: costo globale degli ordini per un certo editore, eseguita 10 volte al giorno
• Op2: inserire ordine ad un certo editore, eseguita 4 volte all'ora

Indicare se la ridondanza è conveniente dati i volumi e le operazioni descritte.

Tavola dei volumi

• Libro -> 10000 righe
• Editore -> 200 righe
• Ordine -> 10*200= 2000 righe

Schema senza ridondanza

OP 1 – costo globale degli ordini per un certo editore, eseguita 10 volte al giorno

Facciamo la Tavola degli accessi, per capire quanti accessi in lettura e/o scrittura servono per eseguire questa op.

Accedo alla tabella Editore in lettura 1 volta per guardare il codiceeditore e alla tabella Ordine in lettura 10 volte perché per ogni editore vi sono in media 10 ordini.

• Editore -> 1 accesso in Lettura
• Ordine -> 10 accessi in Lettura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

α è il costo della scrittura che costa 5.

Il peso dell’operazione invece ha valore 1 sempre di solito

Costo Op1 senza Rid = 10 * 1 * (5 * 0 + 11) = 10 *11 = 110

OP 2 – inserire ordine ad un certo editore, eseguita 4 volte all’ora

Tavola Accessi

Accedo alla tabella Editore in lettura una volta per leggere il codiceeditore, poi accedo a Ordine in scrittura una volta per inserire un ordine

• Editore -> 1 accesso in Lettura
• Ordine -> 1 accesso in scrittura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

Costo Op2 senza Rid = (4 * 24) * 1 * (5 * 1 + 1) = 96 * 6 = 576

Il costo totale dello schema senza ridondanze è:

(Si sommano i costi delle 6 operazioni)

Tot costo schema senza rid = 110 + 576 = 686

Memoria dello schema senza ridondanza

Adesso vediamo quanta memoria mi occupa lo schema senza ridondanze.

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo dell’entità Libro

• Cod. inventario: sarà un integer, quindi 4 bytes
• Tipo: sarà un varchar, lungo sui 25 o 30 caratteri, sarà 30 bytes
• Autore: sarà un varchar, lungo sui 50 caratteri, sarà 50 bytes
• Scaffale: sarà un integer, quindi 4 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Libro, ovvero 50000.

Libro = (4+30+50+4) * 50000= 4.400.000 bytes

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo dell’entità Editore

• Codice: sarà un integer, quindi 4 bytes
• Paese: sarà un varchar, lungo sui 25 o 30 caratteri, sarà 30 bytes
• Indirizzo: sarà un varchar, lungo sui 50 caratteri, sarà 50 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Editore, ovvero 100.

Editore = (4+30+50) * 100 = 8400 bytes

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo della relazione Ordine

• Data: sarà un datetime, quindi 8 bytes
• Costo: sarà uno smallint (arriva max a 32000), quindi 2 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Ordine, ovvero 2000.

Ordine = (8+2) * 2000 = 20000 bytes

La memoria totale occupata dallo schema senza ridondanze è quindi 4.400.000 + 8400 + 20000 = 4.428.400 bytes

Schema con ridondanza

OP 1 – costo globale degli ordini per un certo editore, eseguita 10 volte al giorno

Facciamo la Tavola degli accessi, per capire quanti accessi in lettura e/o scrittura servono per eseguire questa op.

Accedo alla tabella Editore in lettura 1 volta per guardare il codiceeditore e il costocomplessivo.

• Editore -> 1 accesso in Lettura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

α è il costo della scrittura che costa 5.

Il peso dell’operazione invece ha valore 1 sempre di solito.

Costo Op1 CON Rid = 10 * 1 * (5 * 0 + 1) = 10 *1 * 1 = 10

OP 2 – inserire ordine ad un certo editore, eseguita 4 volte all’ora

Tavola Accessi

Accedo alla tabella Editore in lettura una volta per leggere il codiceeditore e il costo complessivo, poi accedo a Ordine in scrittura una volta per inserire un ordine e in Editore una volta in scrittura per aggiornare il costo complessivo.

• Editore -> 1 accesso in Lettura
• Editore -> 1 accesso in Scrittura
• Ordine -> 1 accesso in Scrittura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

Costo Op2 CON Rid = (4 * 24) * 1 * (5 * 2 + 1) = 96 * 11 = 1056

Memoria dello schema con ridondanza

Adesso vediamo quanta memoria mi occupa lo schema CON ridondanze.

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo dell’entità Libro.

Non avendo attributi in più rispetto all’altro schema, resterà identico.

• Cod. inventario: sarà un integer, quindi 4 bytes
• Tipo: sarà un varchar, lungo sui 25 o 30 caratteri, sarà 30 bytes
• Autore: sarà un varchar, lungo sui 50 caratteri, sarà 50 bytes
• Scaffale: sarà un integer, quindi 4 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Libro, ovvero 50000.

Libro = (4+30+50+4) * 50000= 4.400.000 bytes

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo dell’entità Editore.

Questa cambia perché abbiamo un attributo in più.

• Codice: sarà un integer, quindi 4 bytes
• Paese: sarà un varchar, lungo sui 25 o 30 caratteri, sarà 30 bytes
• Indirizzo: sarà un varchar, lungo sui 50 caratteri, sarà 50 bytes
• Costo complessivo: sarà uno smallint (arriva max a 32000), quindi 2 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Editore, ovvero 100.

Editore = (4+30+50+2) * 100 = 8600 bytes

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo della relazione Ordine.

Resta uguale all’altro schema.

• Data: sarà un datetime, quindi 8 bytes
• Costo: sarà uno smallint (arriva max a 32000), quindi 2 bytes

Adesso sommo questi valori e moltiplico il risultato per il numero di record contenuti in Ordine, ovvero 2000.

Ordine = (8+2) * 2000 = 20000 bytes

La memoria totale occupata dallo schema CON ridondanze è quindi 4.400.000 + 8600 + 20000 = 4.428.600 bytes

Confronto tra costi e memorie

Adesso confrontiamo i costi e le memorie e vediamo quale schema conviene, se con o senza ridondanza.

Tot costo schema senza rid = 686

Tot costo schema CON rid = 1056

(686/1056) * 100 = 64,9 % -> lo schema senza ridondanze ci costa il 100-64,9% = 35,1% in meno in termini di operazioni

Memoria senza ridondanza = 4.428.400 bytes

Memoria CON ridondanza = 4.428.600 bytes

(4.428.400/4.428.600) *100 = 99,9% -> lo schema senza ridondanze ci costa lo 0,1% in meno rispetto a quello con, in termini di memoria.

Concludiamo che lo schema SENZA ridondanza è il più conveniente, perché in termini di memoria rimane pressoché uguale, mentre in termini di operazioni fa risparmiare molto.

Dipendenze funzionali e terza forma normale

Si consideri lo schema:

Sapendo che:

• 1. Esistono più copie di un opera dello stesso autore offerte dallo stesso distributore
• 2. Tutte le copie della stessa opera hanno la stessa presentazione
• 3. Copie della stessa opera possono apparire in più stand

Evidenziare le dipendenze funzionali presenti nello schema e trasformarlo in terza forma normale:

Opera(codice, numerocopia, tipo, distributore, nome, valore, presentazione)

Stand(codicestand, codiceopera, numerocopia, presentazione, datainiziouso, datafine)

Esercizio dipendenze

1) Ok

2) codiceopera -> presentazione

3) Ok

Tab Opera

Codice, numerocopia -> tipo, distributore, nome, valore, presentazione

Tab Stand

Codicestand, codiceopera -> numerocopia, presentazione, datainiziouso, datafine

Step 1A – semplifico parte dx

2) codiceopera -> presentazione

Tab Opera

Codice, numerocopia -> tipo

Codice, numerocopia -> distribuzione

Codice, numerocopia -> nome

Codice, numerocopia -> valore

Codice, numerocopia -> presentazione

Tab Stand

Codicestand, codiceopera -> numerocopia

Codicestand, codiceopera -> presentazione

Codicestand, codiceopera -> datainiziouso

Codicestand, codiceopera -> datafine

Step 1B – semplifico parte sx

2) codiceopera -> presentazione

Tab Opera

Codice, numerocopia -> tipo

Codice, numerocopia -> distribuzione

Codice, numerocopia -> nome

Codice, numerocopia -> valore

Codice, numerocopia -> presentazione

Tab Stand

Codicestand, codiceopera -> numerocopia

Codicestand, codiceopera -> presentazione

Codicestand, codiceopera -> datainiziouso

Codicestand, codiceopera -> datafine

Step 1C – ok

Step 2

F1= {codiceopera -> presentazione}

F2 = {Codice, numerocopia -> tipo, Codice, numerocopia -> distribuzione, Codice, numerocopia -> nome, Codice, numerocopia -> valore}

F3 = {Codicestand, codiceopera -> numerocopia, Codicestand, codiceopera -> datainiziouso, Codicestand, codiceopera -> datafine}

Infine trasformazione in tabelle in terza forma normale

OperaPresentazione (codiceopera, presentazione

Opera (Codice, numerocopia, tipo, distribuzione, nome, valore)

Stand (Codicestand, codiceopera, numerocopia, datainiziouso, datafine)

Esercizio query giugno

Si consideri il seguente schema Magazzino

table Grezzo( nome :varchar(20),dimensione: integer,scorta :integer check 0 <=scortacodp:integer autoincrement),

table Fornitore( nomeProd :varchar(20),email: varchar(50),citta:varchar(20) ,numeroordini: integer,codf: integer autoincrement),

table Produce( forn: references Fornitore(codf),parte : references Grezzo(codp),prezzo: integer) ,

table Ordini( mat: references grezzo(codP),forn: references Fornitore (codf),numero:integer,dataord : date NOT NULL,dataric: date)

table Qualità( forn: references Fornitore (codf),giudizio : tiny check 0<giudizio <10)

1) Il numero dei grezzi ordinati e non ricevuti da Bianchi ed il prezzo complessivo

SELECT SUM (O.numero) AS PezziOrdinati, SUM(O.numero * P.prezzo) AS PrezzoComplessivo

FROM Ordini AS O, Fornitore AS F, Produce AS P

WHERE O.forn = F.codf AND P.forn = F.codf AND O.mat = P.parte AND F.nomeProd = “Bianchi” AND O.dataric IS NULL

2) Il numero dei pezzi già ricevuti da ogni produttore

SELECT F.codf, SUM (O.numero) AS PezziRicevuti

FROM Ordini AS O, Fornitore AS F, Produce AS P

WHERE O.forn = F.codf AND P.forn = F.codf AND O.mat = P.parte AND O.dataric IS NOT NULL

GROUP BY F.codf

3) Gli email a cui ordinare i pezzi la cui scorta è inferiore a 50 ordinati per giudizio

SELECT DISTINCT F.email

FROM Grezzo AS G, Fornitore AS F, Qualita AS Q, Produce AS P

WHERE Q.forn = F.codf AND P.forn = F.codf AND P.parte = G.codp AND G.scorta < 50

ORDER BY Q.giudizio DESC (x ordinare dal più grande al più piccolo)

4) I fornitori di Pisa che possono fornire bulloni, con un prezzo inferiore alla media ed un giudizio di almeno 3

SELECT F.codf

FROM Grezzo AS G, Fornitore AS F, Qualita AS Q, Produce AS P,

WHERE Q.forn = F.codf AND P.forn = F.codf AND P.parte = G.codp AND F.città = “Pisa” AND G.nome = “bulloni” AND Q.giudizio >= 3 AND P.prezzo < (SELECT AVG(P1.prezzo) FROM Produce AS P1, Grezzo AS G2 WHERE P2.parte = G2.codp AND G2.nome = “bulloni”)

5) Scrivere un trigger che inserisce un ordine di 500 pezzi al fornitore con la qualità massima quando la quantità di Viti è inferiore a 50

CREATE TRIGGER InserisciOrdine

AFTER UPDATE OF scorta ON Grezzo

FOR EACH ROW

WHEN NEW.nome = “viti” AND NEW.scorta < 50

INSERT INTO Ordini (mat, forn, numero)

SELECT G.codp, F.codf, 500

FROM Grezzo AS G, Qualità AS Q, Fornitore AS F, Produce AS P

WHERE Q.forn = F.codf AND P.forn = F.codf AND P.parte = G.codp AND G.nome =”viti” AND Q.giudizio = (SELECT MAX (Q1.giudizio) FROM Qualita as Q1, Produce AS P1, Grezzo AS G1 WHERE Q1.forn = P1.forn AND P1.parte = G1.codp AND G1.nome = “viti”)

LIMIT 1

Esercizio operazioni

• 1. Il costo di una scrittura è di 2 volte quello di una lettura,
• 2. Vi sono 10000 libri e 30 editori,
• 3. Per ogni editore vi sono 10 ordini in media

Le operazioni di interesse sono

• Op1: costo globale degli ordini per un certo editore, eseguita 40 volte al giorno
• Op2: inserire ordine ad un certo editore, eseguita 1 volte all'ora
• Op3: cancellare ordine ad un certo editore eseguita 8 volte al giorno

Indicare se la ridondanza è conveniente dati i volumi e le operazioni descritte

Tavola dei volumi

• Libro -> 10000 righe
• Editore -> 30 righe
• Ordine -> 30*10= 300 righe

Schema senza ridondanza

OP 1 – costo globale degli ordini per un certo editore, eseguita 40 volte al giorno

Facciamo la Tavola degli accessi, per capire quanti accessi in lettura e/o scrittura servono per eseguire questa op.

• Editore -> 1 accesso in Lettura
• Ordine -> 10 accessi in Lettura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

α è il costo della scrittura che costa 2.

Il peso dell’operazione invece ha valore 1 sempre di solito

Costo Op1 senza Rid = 40 * 1 * (2 * 0 + 11) = 40 *1 * 11 = 440

OP 2 – inserire ordine ad un certo editore, eseguita 1 volta l’ora

Tavola Accessi

Accedo alla tabella Editore in lettura una volta per leggere il codiceeditore, poi accedo a Ordine in scrittura una volta per inserire un ordine

• Editore -> 1 accesso in Lettura
• Ordine -> 1 accesso in scrittura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

Costo Op2 senza Rid = 24 * 1 * (2 * 1 + 1) = 24 * 3 = 72

OP 3 – cancellare ordine ad un certo editore eseguita 8 volte al giorno

Tavola Accessi

Accedo alla tabella Editore in lettura una volta per leggere il codiceeditore, accedo alla tabella Ordine in lettura una volta per trovare il record che voglio cancellare e in scrittura una volta per cancellare un ordine

• Editore -> 1 accesso in lettura
• Ordine -> 1 accesso in lettura
• Ordine -> 1 accesso in scrittura

CostoOp = (frequenza operazione) * peso operazione (interattiva o batch) * (α * numero di accessi in scrittura + numero accessi in lettura)

Costo Op3 senza Rid = 8 * 1 * (2 * 1 + 2) = 8 * 4 = 32

Tot costo schema senza rid = 440 + 72 + 32 = 544

Memoria dello schema senza ridondanze

Adesso vediamo quanta memoria mi occupa lo schema senza ridondanze.

Supponiamo quanti caratteri e quindi bit può contenere ogni campo attributo dell’entità Libro

Cod. inventario: sarà un integer, quindi 4 bytes

Tipo: sarà un varchar, lungo sui 25 o 30 caratteri, sarà