Esercitazione Basi di dati

Esercitazione di Basi di Dati del 27/11/2014

Proff. Vincenzo Moscato, Antonio Picariello

Data la seguente base di dati progettata per la gestione degli acquisti da parte di utenti di

prodotti in un sito di e-commerce (i campi sottolineati sono chiavi primaria, mentre quelli con il

simbolo ‘*’ chiavi esterne): PRODOTTI

Codice Nome Descrizione Categoria Produttore Prezzo

AS001 JR MERCURIAL Scarpe da Calcio Articoli Nike 55

Sportivi

LF001 Basi di Dati per la Gestione Libro di Basi di Libri/Fumetti McGraw- 20

dell’Informazione Dati Hill

LF002 Batman la Leggenda Serie Platino Libri/Fumetti DC-Comics 6

Batman nr. 15

CARRELLO

Codice Prodotto* Utente* Qta Data

VI001 AS001 vinni 1 22/11/2014

AN001 LF001 angchian 25 22/11/2014

PI001 LF001 picus 1 22/11/2014

PI001 AS001 picus 1 22/11/2014

UTENTI

Username Nome Cognome Pwd

vinni Vincenzo Moscato wnapoli

angchian Angelo Chianese 123stella

picus Antonio Picariello hofame

carlosan Carlo Sansone carletto

Si effettui la creazione ed il popolamento della base di dati supponendo di volere un catalogo di

circa 1000 prodotti, 200 acquisti giornalieri (la tabella CARRELLO contiene solo gli acquisti del

giorno corrente) e 500 utenti. In particolare, si usino le credenziali sys/SysAdm per creare l’utente

proprietario del db (avente come nome la matricola dello studente seguito preceduto ‘c##’) e con

quest’ultima utenza si creino le tabelle.

Si esprimano (e si salvino come viste) poi le seguenti interrogazioni attraverso il linguaggio SQL:

1. Trovare i prodotti acquistati nella data odierna da Antonio Picariello.

2. Si elenchino tutti gli utenti della base di dati con i relativi prodotti acquistati (raggruppati

per cognome dell’utente).

3. Trovare per ogni categoria di prodotto il numero di acquisti.

4. Trovare per ogni utente l’ammontare della spesa in acquisti qualora sia maggiore di 50

euro.

5. Trovare gli utenti che hanno acquistato sia fumetti sia articoli sportivi.

6. Trovare i prodotti della categoria Libri/Fumetti che non sono stati mai venduti.

SOLUZIONE ESERCITAZIONE 27-11-2014

CREAZIONE TABELLE E DIMENSIONAMENTO

create table PRODOTTI (

Codice CHAR(5),

Nome VARCHAR2(100) NOT NULL,

Descrizione CLOB,

Categoria VARCHAR2(50) NOT NULL,

Produttore VARCHAR2(100),

Prezzo REAL,

CONSTRAINT PK_PRODOTTI PRIMARY KEY (Codice)

)

STORAGE (INITIAL 270K);

-- Un record della tabella prodotti occupa: 5 + 100 + 1 + 50 + +100 + 8 = 264b

-- I 1000 record della tabella richiedono un initial di 264Kb

create table UTENTI(

username VARCHAR2(20),

Nome VARCHAR2(50) NOT NULL,

Cognome VARCHAR2(50) NOT NULL,

Pwd VARCHAR2(20) NOT NULL,

CONSTRAINT PK_UTENTI PRIMARY KEY (username)

)

STORAGE (INITIAL 75K);

-- Un record della tabella utenti occupa: 20 + 50 + 50 + 20 = 140b

-- I 500 record della tabella richiedono un initial di 70Kb

create table CARRELLO (

Codice CHAR(5),

Prodotto CHAR(5),

Utente VARCHAR2(20) NOT NULL,

Qta NUMBER (4,0) NOT NULL,

Data Date,

CONSTRAINT PK_CARRELLO PRIMARY KEY(Codice, Prodotto)

)

STORAGE (INITIAL 15K);

-- Un record della tabella carrello (che contiene acquisti giornalieri) occupa: 5 + 5 + 30 + 4 + 7 = 51b

-- I 200 record della tabella richiedono un initial di 10,2Kb

ALTER TABLE CARRELLO ADD CONSTRAINT FK_CARRELLO_PRODOTTI FOREIGN KEY (Prodotto) REFERENCES

PRODOTTI(Codice);

ALTER TABLE CARRELLO ADD CONSTRAINT FK_CARRELLO_UTENTI FOREIGN KEY (UTENTE) REFERENCES

UTENTI(username);

INSERIMENTO DEI DATI

insert into PRODOTTI (Codice,Nome,Descrizione,Categoria,Produttore,Prezzo)

values ('AS001','JR MERCURIAL','Scarpe da Calcio','Articoli Sportivi','Nike',55);

insert into PRODOTTI (Codice,Nome,Descrizione,Categoria,Produttore,Prezzo)

values('LF001','Basi di Dati per la Gestione dell''Informazione','Libro di Basi di Dati','Libri/Fumetti','McGraw-

Hill',20);

insert into PRODOTTI (Codice,Nome,Descrizione,Categoria,Produttore,Prezzo)

values('LF002','Batman la Leggenda','Serie Platino Batman nr.15','Libri/Fumetti','DC-Comics',6);

commit;

insert into UTENTI(username,Nome,Cognome,Pwd)

values ('vinni','Vincenzo','Moscato','wnapoli');

insert into UTENTI(username,Nome,Cognome,Pwd)

values('angchian','Angelo','Chianese','123stella');

insert into UTENTI(username,Nome,Cognome,Pwd)

values ('picus','Antonio','Picariello','hofame');

insert into UTENTI(username,Nome,Cognome,Pwd)

values ('carlosan','Carlo','Sansone','carletto');

commit;

insert into CARRELLO(Codice,Prodotto,Utente,Qta,Data)

VALUES('VI001','AS001','vinni',1,TO_DATE('22/11/2014','DD/MM/YYYY'));

insert into CARRELLO(Codice,Prodotto,Utente,Qta,Data)

VALUES('AN001','LF001','angchian',25,TO_DATE('22/11/2014','DD/MM/YYYY'));

insert into CARRELLO(Codice,Prodotto,Utente,Qta,Data)

VALUES('PI001','LF001','picus',1,TO_DATE('22/11/2014','DD/MM/YYYY'));

insert into CARRELLO(Codice,Prodotto,Utente,Qta,Data)

VALUES('PI001','AS001','picus',1,TO_DATE('22/11/2014','DD/MM/YYYY'));

commit;