DROP TABLE IF EXISTS categorie;
DROP TABLE IF EXISTS fabbriche;
DROP TABLE IF EXISTS modelli;
DROP TABLE IF EXISTS proprieta;
DROP TABLE IF EXISTS proprietari;
DROP TABLE IF EXISTS veicoli;


--
-- Table structure for table categorie
--

CREATE TABLE categorie (
  cod_categoria char(2) NOT NULL,
  nome char(30) DEFAULT NULL,
  PRIMARY KEY (cod_categoria)
) ;

--
-- Dumping data for table categorie
--

INSERT INTO categorie VALUES ('01','AUTOVEICOLO'),('02','MOTOVEICOLO'),('03','CICLOMOTORE');

--
-- Table structure for table fabbriche
--

CREATE TABLE fabbriche (
  cod_fabbrica char(3) NOT NULL,
  num_modelli_prodotti int(11) DEFAULT NULL,
  nome char(20) DEFAULT NULL,
  PRIMARY KEY (cod_fabbrica)
) ;

--
-- Dumping data for table fabbriche
--

INSERT INTO fabbriche VALUES ('001',5,'FIAT'),('002',3,'PIAGGIO'),('003',10,'RENAULT'),('004',8,'VOLKSWAGEN'),('005',5,'FORD');

--
-- Table structure for table modelli
--

CREATE TABLE modelli (
  cod_modello char(3) NOT NULL,
  nome char(30) DEFAULT NULL,
  versione decimal(10,0) DEFAULT NULL,
  cod_fabbrica char(3) DEFAULT NULL,
  PRIMARY KEY (cod_modello),
  KEY cod_fabbrica (cod_fabbrica),
  CONSTRAINT cod_fabbrica FOREIGN KEY (cod_fabbrica) REFERENCES fabbriche (cod_fabbrica) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

--
-- Dumping data for table modelli
--

INSERT INTO modelli VALUES ('001','PANDA',1,'001'),('002','CLIO',3,'003'),('003','GOLF',4,'004'),('004','PUNTO',1,'001'),('005','SKIPPER',1,'002'),('006','LIBERTY',3,'002');

--
-- Table structure for table proprietari
--

CREATE TABLE proprietari (
  codice_fiscale char(16) NOT NULL,
  cognome char(30) DEFAULT NULL,
  nome char(30) DEFAULT NULL,
  indirizzo char(30) DEFAULT NULL,
  citta char(30) DEFAULT NULL,
  provincia char(2) DEFAULT NULL,
  cap char(5) DEFAULT NULL,
  PRIMARY KEY (codice_fiscale)
) ;

--
-- Dumping data for table proprietari
--

INSERT INTO proprietari VALUES ('DFRPLA70A01D121X','Verdi','Giulia','Via Vittorio Emanuele,6','Milano','MI','20121'),('FRSNTO60G01D401J','Neri','Luca','Via Tevere,15','Pisa','PI','56100'),('NPOPSE75A01D401J','Bianchi','Mario','Via Roma,16','Milano','TO','10010'),('RSSASA75A01D401X','Rossi','Bianca','Via Po,25','Roma','RM','00100'),('RSSBNA71G02A403J','Rossi','Paolo','Via Tevere,112','Torino','TO','01023'),('RSSPLA70A01D401J','Neri','Marco','Via Tevere,6','Pisa','PI','56100');

--
-- Table structure for table veicoli
--

CREATE TABLE veicoli (
  targa char(10) NOT NULL,
  cilindrata decimal(10,0) DEFAULT NULL,
  cavalli_fiscali decimal(10,0) DEFAULT NULL,
  velocita decimal(10,0) DEFAULT NULL,
  posti decimal(10,0) DEFAULT NULL,
  data_immatricolazione date DEFAULT NULL,
  cod_modello char(3) DEFAULT NULL,
  cod_categoria char(2) DEFAULT NULL,
  PRIMARY KEY (targa),
  KEY cod_modello (cod_modello),
  KEY cod_categoria (cod_categoria),
  CONSTRAINT cod_categoria FOREIGN KEY (cod_categoria) REFERENCES categorie (cod_categoria) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT cod_modello FOREIGN KEY (cod_modello) REFERENCES modelli (cod_modello) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

--
-- Dumping data for table veicoli
--

INSERT INTO veicoli VALUES ('AA123356X',1600,100,220,5,'2008-10-27','003','01'),('BB18366J',1600,120,160,4,'2002-12-30','002','01'),('CF123456X',1200,80,160,5,'2001-10-27','001','01'),('DF7281',125,100,40,2,'2011-08-15','006','03'),('GB18956N',1200,80,160,4,'2008-05-12','004','01'),('NU87365T',2000,190,215,5,'2011-06-09','001','01');

--
-- Table structure for table proprieta
--

CREATE TABLE proprieta (
  targa char(10) NOT NULL,
  codice_fiscale char(16) NOT NULL,
  data_acquisto date DEFAULT NULL,
  data_cessione date DEFAULT NULL,
  KEY targa (targa),
  KEY codice_fiscale (codice_fiscale),
  CONSTRAINT codice_fiscale FOREIGN KEY (codice_fiscale) REFERENCES proprietari (codice_fiscale) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT targa FOREIGN KEY (targa) REFERENCES veicoli (targa) ON DELETE NO ACTION ON UPDATE NO ACTION
);

--
-- Dumping data for table proprieta
--

INSERT INTO proprieta VALUES ('AA123356X','DFRPLA70A01D121X','2001-05-17','2003-11-30'),('BB18366J','RSSBNA71G02A403J','2005-11-30',NULL),('BB18366J','DFRPLA70A01D121X','2002-11-30','2004-01-02');