--
-- script per creare e popolare le tabelle del database PROVA
--
--    versione 2
--                 (rispetto completo dei vincoli relazionali)
--
-- lo script usa la sintassi SQL supportata da MYSQL ver 8.0.2 community
-- 
-- lo script presuppone che sia stato creato il database PROVA
-- eseguendo il comando CREATE DATABASE PROVA
--
-- le parti non standard dei comandi sono
--   1) la clusola IF EXISTS del comando DROP TABLE
--   
--   2) il tipo dati TIME (cosi' come quello DATE) sono un'estensione MySQL
--      del tipo dati DATETIME dello standard
--

--
use PROVA;

--
-- Le tabelle DIPART e DIPENDENTI sono  definite con chiave primaria
-- e con un vincolo esplicito di integrita' referenziale fra la chiave esterna DIPENDENTI(NUM_DIPART) e
-- DIPART(NUM_DIPART)
-- All'interno di DIPENDENTI l'attributp SUP e' definito sullo stesso dominio della chiave primaria
-- MATRICOLA, e puo' quindi essere considerato chiave "esterna" della stessa tabella
-- con un opportuno vincolo di integrita' referenziale. Il vincolo pero' impone forti limitazioni
-- a come si inseriscono le righe nella tabella (perche' ?), quindi per creare il database e' piu'
-- comodo creare la tabella SENZA il vincolo, caricare le tuple in modo che i dati rispettino
-- il vincolo, e successivamente MODIFICARE lo schema della tabella, con un opportuno comando
-- di ALTER TABLE, con il quale si aggiunge il vincolo
--

drop TABLE IF EXISTS DIPENDENTI;
drop TABLE IF EXISTS DIPART;
CREATE TABLE DIPART
        ( NUM_DIPART SMALLINT primary key,
          NOME_DIPART CHAR(14),
          CITTA CHAR(13));
CREATE TABLE DIPENDENTI
        ( MATRICOLA SMALLINT primary key,
          NOME_IMP CHAR(10),
          MANSIONE CHAR(11),
          SUP SMALLINT,
          DATA_ASS DATE,
          STIPENDIO INTEGER,
          COMMISS INTEGER,
          NUM_DIPART SMALLINT,
          FOREIGN KEY (NUM_DIPART) REFERENCES DIPART(NUM_DIPART)
        );

--
-- inserimento delle tuple nella tabella  DIPART
--

INSERT INTO DIPART VALUES (15,'SICUREZZA','FIRENZE');
INSERT INTO DIPART VALUES (12,'AMBIENTE','MANTOVA');
INSERT INTO DIPART VALUES (10,'DIREZIONE','ROMA');
INSERT INTO DIPART VALUES (20,'RICERCHE','FIRENZE');
INSERT INTO DIPART VALUES (30,'VENDITE','MILANO');
INSERT INTO DIPART VALUES (40,'CONTABILITA''','TORINO');
INSERT INTO DIPART VALUES (11,'PIANIFICAZIONE','ROMA');
INSERT INTO DIPART VALUES (50,'CONTROLLO','TORINO');
--
-- inserimento tuple nella tabella DIPENDENTI
--

INSERT INTO DIPENDENTI VALUES
        (7369,'ORTU','IMPIEGATO',7902,'1980-12-17',800000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7499,'VILLANI','VENDITORE',NULL,'1981-02-20',1600000,300000,30);
INSERT INTO DIPENDENTI VALUES
        (7521,'VILLA','VENDITORE',7698,'1981-02-12',1250000,500000,30);
INSERT INTO DIPENDENTI VALUES
        (7566,'FIRMANI','DIRIGENTE',7839,'1981-12-31',2975000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7654,'MAGRINI','VENDITORE',7698,'2001-09-30',1250000,1400000,30);
INSERT INTO DIPENDENTI VALUES
        (7698,'ROSSI','DIRIGENTE',NULL,'1981-05-15',2850000,NULL,30);
INSERT INTO DIPENDENTI VALUES
        (7782,'NOVELLI','DIRIGENTE',7839,'2006-03-24',2450000,NULL,10);
INSERT INTO DIPENDENTI VALUES
        (7788,'GAGGI','ANALISTA',7566,'1999-12-23',3000000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7839,'GIGLIO','PRESIDENTE',NULL,'2001-12-12',5000000,NULL,10);
INSERT INTO DIPENDENTI VALUES
        (7844,'ADRIANI','VENDITORE',7698,'1981-05-24',1500000,0,30);
INSERT INTO DIPENDENTI VALUES
        (7902,'ORLANDI','ANALISTA',7566,'1982-8-30',900000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7934,'ZUCCHI','IMPIEGATO',7782,'1980-11-12',1300000,NULL,10);
INSERT INTO DIPENDENTI VALUES
        (7370,'ROSSI','FUNZIONARIO',7470,'2003-01-23',850000,NULL,11);
INSERT INTO DIPENDENTI VALUES
        (7470,'VERDI','FUNZIONARIO',7654,'2000-10-10',1650000,300000,15);
INSERT INTO DIPENDENTI VALUES
        (7471,'VILLA','VENDITORE',7788,'1993-03-21',1250000,500000,11);
INSERT INTO DIPENDENTI VALUES
        (7570,'VILLARI','DIRIGENTE',7370,'1981-11-12',2975000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7670,'NEGRINI','FUNZIONARIO',NULL,'1981-12-31',1250000,1400000,50);
INSERT INTO DIPENDENTI VALUES
        (7675,'ROSSI','DIRIGENTE',NULL,'1989-01-25',2850000,NULL,15);
INSERT INTO DIPENDENTI VALUES
        (7770,'NERINI','DIRIGENTE',NULL,'2002-05-21',2450000,NULL,50);
INSERT INTO DIPENDENTI VALUES
        (7790,'GAGGIULO','DIRIGENTE',7566,'2005-04-14',3000000,NULL,11);
INSERT INTO DIPENDENTI VALUES
        (7800,'GIGLIOLI','PRESIDENTE',NULL,'2001-01-12',5000000,NULL,11);
INSERT INTO DIPENDENTI VALUES
        (7870,'ADRIANU','DIRIGENTE',7698,'2005-08-31',1500000,0,50);
INSERT INTO DIPENDENTI VALUES
        (7971,'ORLANDINI','ANALISTA',7566,'2006-02-27',900000,NULL,20);
INSERT INTO DIPENDENTI VALUES
        (7970,'ETTI','IMPIEGATO',7782,'2003-10-11',1300000,NULL,10);
INSERT INTO DIPENDENTI VALUES
        (7972,'MILANESI','ANALISTA',NULL,'2010-10-11',1350000,NULL,NULL);
--
-- Aggiungo ora il vincolo di integrita'Ê referenziale
--

ALTER TABLE DIPENDENTI add FOREIGN KEY (SUP) REFERENCES DIPENDENTI (MATRICOLA);

--
-- creazione delle tabelle dei FORNITORI, PRODOTTI,PARTI, FORNITURE, SPEDIZIONI
-- e SPED_DETTAGLI
-- le tabelle hanno opportuni vincoli di integritÌÄåÊ referenziali
--

drop TABLE IF EXISTS SPED_DETTAGLI;
drop TABLE IF EXISTS SPEDIZIONI;
drop TABLE IF EXISTS FORNITURE;
DROP TABLE IF EXISTS FORNITORI;
DROP TABLE IF EXISTS PRODOTTI;
DROP TABLE IF EXISTS PARTI;
CREATE TABLE FORNITORI
        ( COD CHAR(4) PRIMARY KEY,
          NOME CHAR(13),
          CITTA CHAR(13));
CREATE TABLE PARTI
        ( COD CHAR(4) PRIMARY KEY,
          NOME CHAR(20),
          COLORE CHAR(20),
          PESO INTEGER,
          CITTA CHAR(20));
CREATE TABLE PRODOTTI
        ( COD CHAR(4) PRIMARY KEY,
          NOME CHAR(13),
          CITTA CHAR(10));
CREATE TABLE FORNITURE
        (FCOD CHAR(4),
         PCOD CHAR(4),
         PRCOD CHAR(4),
         QUANTITA INTEGER,
         PRIMARY KEY (FCOD,PCOD,PRCOD),
         FOREIGN KEY (FCOD) REFERENCES FORNITORI(COD),
         FOREIGN KEY (PCOD) REFERENCES PARTI(COD),
         FOREIGN KEY (PRCOD) REFERENCES PRODOTTI(COD));
CREATE TABLE SPEDIZIONI
        (SPCOD CHAR(4) PRIMARY KEY,
         DATASP DATE,
         CITTADEST CHAR(20),
         CITTAPART CHAR(20),
         QTA_TOTALE SMALLINT);
CREATE TABLE SPED_DETTAGLI
        (SPCOD CHAR(4) ,
         FCOD CHAR(4) ,
         PCOD CHAR(4) ,
         PRCOD CHAR(4) ,
         PRIMARY KEY (SPCOD,FCOD,PCOD,PRCOD),
         FOREIGN KEY (SPCOD) REFERENCES SPEDIZIONI(SPCOD),
         FOREIGN KEY (FCOD,PCOD,PRCOD) REFERENCES FORNITURE(FCOD,PCOD,PRCOD));
--
-- inserimento tuple in PARTI
--

INSERT INTO PARTI VALUES
        ('P034', 'RUOTA1', 'NERO', 120000, 'PARMA');
INSERT INTO PARTI VALUES
        ('P002','BULLONE','ROSSO', NULL, 'MILANO');
INSERT INTO PARTI VALUES
        ('P005','MICROFONO','BIANCO' , 340, 'GENOVA');
INSERT INTO PARTI  VALUES
        ('P006','AURICOLARE','NERO' , 120, 'MILANO');
INSERT INTO PARTI VALUES
        ('P009','CORPO_TASTIERA','BEIGE' , 400, 'MILANO');
INSERT INTO PARTI  VALUES
        ('P033','RUOTA2','ROSSO' , 140000, NULL);
INSERT INTO PARTI VALUES
        ('P001','VITE','ROSSO' , 10, 'ROMA');
INSERT INTO PARTI VALUES
        ('P004','CORNETTA',NULL, 900, 'ROMA');
INSERT INTO PARTI VALUES
        ('P007','TASTO','NERO' , 120, 'PAVIA');
INSERT INTO PARTI VALUES
        ('P008','TASTO','BIANCO' , 120, 'PAVIA');
INSERT INTO PARTI VALUES
        ('P010','APPOGGIO_TAST','BEIGE' , 920000, NULL);
INSERT INTO PARTI VALUES
        ('P003','PENNINO','ROSSO' , 100, 'TORINO');
INSERT INTO PARTI VALUES
        ('P011','ADESIVO','METALLO' , 9, 'TORINO');
--
-- inserimento tuple in PRODOTTI
--

INSERT INTO PRODOTTI VALUES
        ('PR01','TASTIERA_IBM','TORINO');
INSERT INTO PRODOTTI VALUES
        ('PR02','TASTIERA_DEC',NULL);
INSERT INTO PRODOTTI VALUES
        ('PR03','SCHEDA_PC','MILANO');
INSERT INTO PRODOTTI  VALUES
        ('PR04','SCHEDA_COMP','ROMA');
INSERT INTO PRODOTTI VALUES
        ('PR05','XT_IBM','ROMA');
INSERT INTO PRODOTTI VALUES
        ('PR06','M24','TORINO');
INSERT INTO PRODOTTI VALUES
        ('PR07','AT_IBM','PAVIA');
INSERT INTO PRODOTTI VALUES
        ('PR08','MAC','TORINO');
INSERT INTO PRODOTTI VALUES
        ('PR11','TASTIERA_WIRE','TORINO');
INSERT INTO PRODOTTI VALUES
        ('PR12','TASTIERA_FLES','MILANO');
INSERT INTO PRODOTTI VALUES
        ('PR13','SCHEDA_AUDIO','MILANO');
INSERT INTO PRODOTTI  VALUES
        ('PR14','SCHEDA_VIDEO','PAVIA');
INSERT INTO PRODOTTI VALUES
        ('PR15','VAIO','LATINA');
INSERT INTO PRODOTTI VALUES
        ('PR26','DELL','VENEZIA');
INSERT INTO PRODOTTI VALUES
        ('PR37','CANON','PAVIA');
INSERT INTO PRODOTTI VALUES
        ('PR58','NOKIA_SET','FIRENZE');
--
-- inserimento tuple in FORNITORI
--

INSERT INTO FORNITORI VALUES
        ('F001','ROSSI','MILANO');
INSERT INTO FORNITORI VALUES
        ('F002','NERI','ROMA');
INSERT INTO FORNITORI  VALUES
        ('F003','BIANCHI','MILANO');
INSERT INTO FORNITORI  VALUES
        ('F004','DONATI','ROMA');
INSERT INTO FORNITORI VALUES
        ('F015','MARIANO','VENEZIA');
INSERT INTO FORNITORI VALUES
        ('F116','GILARDI','VENEZIA');
INSERT INTO FORNITORI VALUES
        ('F217','VERDI','PARMA');
INSERT INTO FORNITORI  VALUES
        ('F328','PUCCINI','LUCCA');
INSERT INTO FORNITORI  VALUES
        ('F339','CUGINI',NULL);
INSERT INTO FORNITORI VALUES
        ('F110','LUCINI','TORINO');
INSERT INTO FORNITORI  VALUES
        ('F211','BIANCHI','TORINO');
INSERT INTO FORNITORI  VALUES
        ('F130','BIZET','PAVIA');
INSERT INTO FORNITORI VALUES
        ('F313','MOSCONI','TORINO');
INSERT INTO FORNITORI VALUES
        ('F314','ANDREI',NULL);
INSERT INTO FORNITORI  VALUES
        ('F315','MONTELATICI','FIRENZE');
INSERT INTO FORNITORI  VALUES
        ('F316','OTTOZ','AOSTA');
INSERT INTO FORNITORI  VALUES
        ('F317','FRENI','MODENA');
INSERT INTO FORNITORI VALUES
        ('F218','VILLA','ROMA');
INSERT INTO FORNITORI VALUES
        ('F230','MOSCONI','ROMA');
INSERT INTO FORNITORI VALUES
        ('F332','ILLO','ROMA');
--
-- inserimento tuple FORNITURE
--

INSERT INTO FORNITURE VALUES
        ('F003','P004','PR05', 100);
INSERT INTO FORNITURE VALUES
        ('F001','P001','PR01', 100);
INSERT INTO FORNITURE VALUES
        ('F001','P001','PR02', 50);
INSERT INTO FORNITURE VALUES
        ('F218','P001','PR08', 200);
INSERT INTO FORNITURE VALUES
        ('F230','P001','PR01', 3000);
INSERT INTO FORNITURE VALUES
        ('F315','P001','PR01', 104);
INSERT INTO FORNITURE VALUES
        ('F003','P006','PR07', 1980);
INSERT INTO FORNITURE VALUES
        ('F003','P001','PR08', 9000);
INSERT INTO FORNITURE VALUES
        ('F003','P001','PR01', 4000);
INSERT INTO FORNITURE VALUES
        ('F313','P002','PR04', 204);
INSERT INTO FORNITURE VALUES
        ('F313','P002','PR05', 124);
INSERT INTO FORNITURE VALUES
        ('F130','P005','PR06', 440);
INSERT INTO FORNITURE VALUES
        ('F317','P002','PR07', 6003);
INSERT INTO FORNITURE VALUES
        ('F003','P002','PR08', 1400);
INSERT INTO FORNITURE VALUES
        ('F116','P001','PR04', 1030);
INSERT INTO FORNITURE VALUES
        ('F004','P001','PR06', 1003);
INSERT INTO FORNITURE VALUES
        ('F004','P001','PR01', 1020);
INSERT INTO FORNITURE VALUES
        ('F116','P001','PR01', 1008);
INSERT INTO FORNITURE VALUES
        ('F230','P002','PR01', 344);
INSERT INTO FORNITURE VALUES
        ('F230','P003','PR02', 2008);
INSERT INTO FORNITURE VALUES
        ('F314','P001','PR03', 9008);
INSERT INTO FORNITURE VALUES
        ('F314','P003','PR07', 1008);
INSERT INTO FORNITURE VALUES
        ('F315','P006','PR01', 10000);
INSERT INTO FORNITURE VALUES
        ('F316','P003','PR02', 90020);
INSERT INTO FORNITURE VALUES
        ('F217','P004','PR03', 10);
INSERT INTO FORNITURE VALUES
        ('F317','P007','PR04', 456899);
INSERT INTO FORNITURE VALUES
        ('F116','P002','PR04', 1030);
INSERT INTO FORNITURE VALUES
        ('F116','P001','PR03', 1003);
INSERT INTO FORNITURE VALUES
        ('F116','P002','PR02', 1020);
INSERT INTO FORNITURE VALUES
        ('F116','P007','PR01', 1008);
INSERT INTO FORNITURE VALUES
        ('F315','P003','PR03', 344);
INSERT INTO FORNITURE VALUES
        ('F230','P033','PR02', 2008);
INSERT INTO FORNITURE VALUES
        ('F314','P033','PR03', 9008);
INSERT INTO FORNITURE VALUES
        ('F314','P033','PR07', 1008);
INSERT INTO FORNITURE VALUES
        ('F315','P033','PR01', 10000);
INSERT INTO FORNITURE VALUES
        ('F001','P033','PR58', 90020);
INSERT INTO FORNITURE VALUES
        ('F217','P001','PR58', 10);
INSERT INTO FORNITURE VALUES
        ('F317','P008','PR58', 456899);
--
-- inserimento tuple SPEDIZIONI
--

INSERT INTO SPEDIZIONI VALUES('SP01','2005-10-01','MILANO','MILANO',200);
INSERT INTO SPEDIZIONI VALUES('SP02','2005-11-01','MILANO','TORINO',150);
INSERT INTO SPEDIZIONI VALUES('SP03','2005-12-01','TORINO','MILANO',300);
INSERT INTO SPEDIZIONI VALUES('SP04','2005-10-02','VENEZIA','BOLOGNA',10);
INSERT INTO SPEDIZIONI VALUES('SP05','2006-10-01','BOLOGNA','BOLOGNA',330);
INSERT INTO SPEDIZIONI VALUES('SP06','2006-10-01','VENEZIA','ROMA',50);
INSERT INTO SPEDIZIONI VALUES('SP07','2007-01-01','ROMA','NAPOLI',NULL);
INSERT INTO SPEDIZIONI VALUES('SP08','2007-02-17','ROMA','VENEZIA',1000);
INSERT INTO SPEDIZIONI VALUES('SP09','2007-03-11','NAPOLI','NAPOLI',2000);
--
-- inserimento tuple SPED_DETTAGLI
--

INSERT INTO SPED_DETTAGLI VALUES
        ('SP01','F003','P004','PR05');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP01','F001','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP01','F001','P001','PR02');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP01','F218','P001','PR08');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP01','F230','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP02','F315','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP02','F003','P006','PR07');
INSERT INTO SPED_DETTAGLI VALUES
       ('SP03','F003','P001','PR08');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP03','F003','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F313','P002','PR04');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F313','P002','PR05');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F130','P005','PR06');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F317','P002','PR07');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F003','P002','PR08');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F116','P001','PR04');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F004','P001','PR06');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F004','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F116','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP04','F230','P002','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F230','P003','PR02');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F314','P001','PR03');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F314','P003','PR07');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F315','P006','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F316','P003','PR02');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F217','P004','PR03');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F317','P007','PR04');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F116','P002','PR04');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F116','P001','PR03');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F116','P002','PR02');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F116','P001','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP05','F315','P003','PR03');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP06','F230','P033','PR02');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP07','F314','P033','PR03');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP07','F314','P033','PR07');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP07','F315','P033','PR01');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP07','F001','P033','PR58');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP08','F217','P001','PR58');
INSERT INTO SPED_DETTAGLI VALUES
        ('SP08','F317','P008','PR58');