Simulazione Basi di dati

Corso di Laurea in Ingegneria Informatica

Esercizio 1 (punti 4)

È necessario totalizzare almeno 2 punti in questo esercizio affinché il resto della prova venga

valutato.

Istruzioni GROUP BY e HAVING in SQL: sintassi ed esempi.

Esercizio 2 (punti 11)

Si vuole progettare il sistema informativo di un negozio per la vendita online di fumetti.

Il negozio ha diversi magazzini, ognuno dei quali identificato da un codice univoco all’interno

della regione in cui si trova; per ogni magazzino viene registrato anche l’indirizzo.

Il sistema memorizza sia il personale dei magazzini sia gli utenti che possono accedere online per

l’acquisto dei fumetti. Entrambe le tipologie di persone sono identificate dal codice fiscale e

vengono registrati gli usuali dati anagrafici, indirizzo, numero di telefono ed email. Gli utenti

sono identificati anche da uno username univoco. Il personale è identificato da un codice univoco

relativo al magazzino in cui lavorano e viene registrata anche la data di scadenza del contratto. In

un magazzino possono lavorare un massimo di 15 dipendenti e per ogni magazzino viene

indicato anche un capo magazzino.

Ogni fumetto è identificato da un codice univoco, ha un titolo, una descrizione ed un prezzo. I

fumetti si dividono in multimediali e cartacei; quelli cartacei hanno un ulteriore identificatore

costituito dal codice ISBN, hanno un autore e, nel caso in cui non siano in lingua originale, un

traduttore.

Per ogni magazzino si indica quali fumetti ha disponibili con il relativo numero di copie; un

fumetto è disponibile presso uno o più magazzini.

Gli utenti effettuano un ordine, con il vincolo che in una certa data di emissione, un utente può

effettuare al massimo un ordine; un ordine può contenere un massimo di 10 fumetti, un fumetto in

un ordine può comparire al massimo una volta. Ogni qual volta un ordine viene soddisfatto, viene

registrata anche la data di consegna dell'ordine; la data di consegna deve essere entro 30 giorni

lavorativi dalla data dell'ordine.

Viene richiesto di:

a) Progettare lo schema E/R, evidenziando gli eventuali vincoli non esprimibili nel modello

E/R. (9 punti)

b) Modificare lo schema precedente aggiungendo il seguente vincolo (farlo separato dall’ER

principale): (2 punti)

In una certa data di emissione, un utente può effettuare al massimo 5 ordini.

Esercizio 3 (7 punti)

Sia dato il seguente schema E/R

A partire dallo schema e dalle informazioni contenute nella tabella dei volumi dei

dati, viene richiesto di produrre:

1. Lo schema di navigazione e il costo della seguente operazione: (4 punti)

Data una città, calcolare la temperatura media utilizzando le temperature

rilevate dai sensori.

Risolvere l’esercizio completando una tabella dei costi del tipo

CONCETTO ACCESSI TIPO DESCRIZIONE /

MOTIVAZIONE

DELL’OPERAZIONE

Autore 1 S Si scrive il nome dell’autore

(ESEMPIO) (ESEMPIO) (ESEMPIO) (ESEMPIO)

2. Tradurre lo schema ER in schema logico relazionale. (3 punti)

Tavola dei volumi

CONCETTO TIPO VOL.

WEBCAM E 5000

STAZIONE E 10000

CITTÀ E 1000

SENSORE E 20000

DATO E 4800000

Si consideri che il 50% dei sensori sono sensori di temperatura, il 30% di umidità e il

20% di pioggia.

Esercizio 4 (11 punti)

Sia dato il seguente schema relazionale per la gestione del commercio elettronico.

PRODOTTO(COD_PRODOTTO, NOME, DESCRIZIONE, TIPO)

PREZZO(COD_PRODOTTO, DATA_INIZIO, DATA_FINE, PREZZO)

FK: COD_PRODOTTO REFERENCES PRODOTTO

CLIENTE(USERNAME, NOME, CITTA, COGNOME, DATA_NASCITA)

ACQUISTO(COD_ACQUISTO, COD_PRODOTTO, USERNAME,

DATA_ACQUISTO, QUANTITÀ)

FK: COD_PRODOTTO REFERENCES PRODOTTO NOT NULL

FK: USERNAME REFERENCES CLIENTE NOT NULL

Nota: un Prezzo ha sempre una data di inizio e una data di

fine. Un prodotto non può avere due prezzi

contemporaneamente, quindi ad ogni data di acquisto c’è un

solo prezzo valido.

Si richiede di scrivere in algebra relazionale e in SQL le seguenti interrogazioni:

a) Selezionare il nome e il prezzo d'acquisto (prezzo alla data dell'acquisto) dei

prodotti che sono stati acquistati da clienti nati prima del 01/01/1990 (punti 1+1)

b) Selezionare lo username dei clienti che hanno acquistato soltanto i prodotti che

Enrico Bianchini ha acquistato dopo il 20/11/2015 (punti 2+2)

Si richiede di scrivere in SQL le seguenti interrogazioni:

c) Creare una vista che mostri il COD_PRODOTTO dei prodotti di tipo “casalingo”,

che sono stati venduti per un totale complessivo superiore a 10000€ dal

01/01/2016. (punti 2)

d) Per ogni tipo di prodotto trovare lo username del cliente che ha speso di più per

quel tipo prodotto (punti 3)