Progetto Basi di dati

QTA FLOAT,

SCADENZA DATE NOT NULL,

ID_PRODOTTO INTEGER UNIQUE

) 6

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

STORAGE (INITIAL 60K);

ALTER TABLE MANGIMI

ADD CONSTRAINT FK_MAN_PROD FOREIGN KEY(ID_PRODOTTO)

REFERENCES PRODOTTI (ID_PRODOTTO)

ON DELETE SET NULL;

ACCESSORI:

CREATE TABLE ACCESSORI (

ID_ACCESSORI INTEGER PRIMARY KEY,

USO VARCHAR2(20),

ID_PRODOTTO INTEGER

)

STORAGE (INITIAL 73K);

ALTER TABLE ACCESSORI

ADD CONSTRAINT FK_ACC_PROD FOREIGN KEY(ID_PRODOTTO)

REFERENCES PRODOTTI (ID_PRODOTTO)

ON DELETE SET NULL;

ANIMALE:

CREATE TABLE ANIMALI(

ID_ANIMALE INTEGER PRIMARY KEY,

RAZZA VARCHAR2(20) NOT NULL,

TIPO VARCHAR2(20) NOT NULL,

MISURA VARCHAR2(20),

NANO NUMBER(1) CHECK (NANO =0 OR NANO=1),

COLORE VARCHAR2(20),

ID_PRODOTTO INTEGER

)

STORAGE(INITIAL 134K);

ALTER TABLE ANIMALI

ADD CONSTRAINT FK_ANI_PROD FOREIGN KEY(ID_PRODOTTO)

REFERENCES PRODOTTI (ID_PRODOTTO)

ON DELETE SET NULL; 7

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

 Specifiche

 Info prodotti

- Il negozio è specializzato nella vendita di particolari animali oltre ad accessori e

mangime per animali.

Per ogni PRODOTTO si vuole tener traccia di un codice identificativo,

del nome, del prezzo, di una opportuna descrizione e della

quantità ancora disponibile in negozio.

Per quanto riguarda gli ANIMALI è necessario considerare il tipo di classe di

appartenenza, mammiferi e uccelli, e a loro volta il tipo di animale in vendita:

in particolare per MAMMIFERO si considerano cane, gatto, criceto e conigli,

mentre per UCCELLI si considerano pappagalli e canarini.

In dettaglio, per particolari Mammiferi(cane,criceto,coniglio) si tiene conto della

misura degli stessi .

Inoltre gli ACCESSORI sono memorizzati nella base di dati con un codice univoco,

la marca e l'uso mentre per il MANGIME contrassegnato da un codice, si considera

la marca e la quantità.

 Info clienti e tessere

- Ogni CLIENTE tesserato viene salvato nella base di dati con Codice Fiscale,

nome, cognome, indirizzo e recapito nel caso in cui riceva offerte oppure vinca ac-

cessori per ogni totale di punti ottenuti da ogni acquisto.

Dunque ad ogni cliente viene associata una TESSERA

contrassegnata da un codice sulla quale vengono salvati il

punti guadagnati grazie all’acquisto di

numero dei prodotti

nel negozio.

Sulla Tessera è memorizzato il livello di Fiducia (1-5) del cliente cosicché il nego-

ziante possa considerare il caso in cui fare ulteriori sconti al cliente che ha un livel-

lo di fiducia alto.

Inoltre è registrata la data dell'emissione della stessa e quindi anche la relativa sca-

denza.

 Info Fornitori

E' opportuno che il negoziante abbia informazioni memorizzate sulla base di dati

relative ai fornitori dei prodotti acquistati.

Per ogni FORNITORE è memorizzato un codice univoco, nome, cognome, recapito e

la modalità di pagamento con la quale il negoziante ha pagato i prodotti.

8

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

 Normalizzazione

E' possibile verificare che la base di dati rispetta le 3 forme normali.

9

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

 Query e Trigger

Implementazione di query e trigger nella base di dati.

Query



1) Elencare i prodotti con i rispettivi prezzi presenti nel negozio.

SELECT NOME, PREZZO

FROM PRODOTTI;

2) Elenca l'identificativo tessera e nome dei tesserati del giorno 20/01/2015.

SELECT C.NOME, T.ID_TESSERA AS NUMERO_TESSERA

FROM TESSERE T, CLIENTI C

WHERE T.CF=C.CF AND DATA_EMISSIONE='20-gen-15';

3) Il prezzo del mangime della marca Prolife acquistato dal fornitore gennaro

mocerino.

SELECT P.PREZZO

FROM ((FORNITORI F JOIN PRODOTTI P ON F.ID_FORNITORE=P.ID_FORNITORE) JOIN

MANGIMI M ON P.ID_PRODOTTO=M.ID_PRODOTTO)

WHERE M.MARCA='PROLIFE' and F.NOME='gennaro' and F.COGNOME='mocerino';

4) Per ogni tipo di animale, il numero dei clienti che lo hanno acquistato.

SELECT A.TIPO AS ANIMALE, COUNT(C.CF) AS NUMERO_COMPRATORI

FROM (ANIMALI A JOIN DETTAGLI D ON A.ID_PRODOTTO=D.ID_PRODOTTO) JOIN

CLIENTI C ON C.CF=D.CF

GROUP BY A.TIPO;

5) Trovare per ogni cliente l'ammontare della spesa in acquisti qualora sia

maggiore di 100 euro.

SELECT C.NOME, SUM(P.PREZZO*D.QTA) AS SPESA

FROM (CLIENTI C JOIN DETTAGLI D ON C.CF=D.CF) JOIN PRODOTTI P ON

P.ID_PRODOTTO=D.ID_PRODOTTO

GROUP BY C.NOME

HAVING SUM(P.PREZZO*D.QTA)>100; 10

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

6) Elencare nome , cognome ed e-mail del cliente che ha meno punti sulla tessera.

SELECT C.NOME, C.COGNOME, C.EMAIL

FROM CLIENTI C JOIN TESSERE T ON T.CF=C. CF

WHERE T.PUNTI= ( SELECT min(T1.PUNTI)

FROM TESSERE T1 );

7) Nome e cognome del fornitore che ha fornito sia cani sia criceti.

SELECT F.NOME, F.COGNOME

FROM (FORNITORI F JOIN PRODOTTI P ON F.ID_FORNITORE=P.ID_FORNITORE) JOIN

ANIMALI A ON P.ID_PRODOTTO=A.ID_PRODOTTO

WHERE A.TIPO='cane'

INTERSECT

SELECT F.NOME,F.COGNOME

FROM (FORNITORI F JOIN PRODOTTI P ON F.ID_FORNITORE=P.ID_FORNITORE)JOIN

ANIMALI A ON P.ID_PRODOTTO=A.ID_PRODOTTO

WHERE A.TIPO='criceto' ; 11

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

 Trigger

CREATE OR REPLACE TRIGGER T1

AFTER INSERT ON DETTAGLI

FOR EACH ROW

DECLARE PRICE PRODOTTI.PREZZO%TYPE;

DECLARE INCREMENTO NUMBER(4,2) NOT NULL :=0.0;

BEGIN

SELECT P.PREZZO INTO PRICE

FROM PRODOTTI P, DETTAGLI D

WHERE P.ID_PRODOTTO=D.ID_PRODOTTO;

INCREMENTO:=(PRICE*QTA)/10;

IF(INCREMENTO>=10) THAN

UPDATE TESSERE SET PUNTI=PUNTI+INCREMENTO

WHERE CF=:NEW.CF;

ELSE

RAISE_APPLICATION_ERROR(-20000,'PREZZO BASSO PER INCREMENTARE I

PUNTI');

END IF;

END; 12

Progetto di Esposito D'Onofrio Gennaro e Esposito Ciro

Allegati

- Modelli E/R e E/R semplificato

- Progettazione logica

- Dimensionamento base di dati 13