· 7 years ago · Jan 27, 2019, 09:16 PM
1
2
3/*
4 * SQL structure
5 *
6 */
7
8DROP TABLE IF EXISTS `VenditaOffline`;
9DROP TABLE IF EXISTS `VenditaOnline`;
10DROP TABLE IF EXISTS `Spedizione`;
11DROP TABLE IF EXISTS `Inserzione`;
12DROP TABLE IF EXISTS `Auto`;
13DROP TABLE IF EXISTS `Moto`;
14DROP TABLE IF EXISTS `Categoria`;
15DROP TABLE IF EXISTS `Agente`;
16DROP TABLE IF EXISTS `Camionista`;
17DROP TABLE IF EXISTS `Impiegato`;
18DROP TABLE IF EXISTS `Camion`;
19DROP TABLE IF EXISTS `Filiale`;
20DROP TABLE IF EXISTS `Responsabile`;
21DROP TABLE IF EXISTS `Citta`;
22DROP TABLE IF EXISTS `Provincia`;
23DROP TABLE IF EXISTS `Regione`;
24
25DROP TABLE IF EXISTS `Cliente`;
26DROP TABLE IF EXISTS `Utente`;
27
28
29/*
30 * unico problema Inserzione e Vendita_Offline codiceauto codicemoto unique
31 * codice vendita SPEDIZIONE codicevenditaoofline e codicevenditaonline
32 */
33
34CREATE TABLE `Regione`(
35 `Id` int AUTO_INCREMENT PRIMARY KEY,
36 `Nome` varchar(20)
37)Engine=InnoDB CHARSET=utf8;
38INSERT INTO `Regione` (`Id`,`Nome`) VALUES
39(1, 'Veneto'),
40(2, 'Lombardia'),
41(3, 'Piemonte'),
42(4, 'Lazio');
43
44CREATE TABLE `Provincia`(
45 `Id` int AUTO_INCREMENT PRIMARY KEY,
46 `Nome` varchar(20),
47 `CodiceRegione` int NOT NULL,
48 FOREIGN KEY(`CodiceRegione`) REFERENCES `Regione`(`Id`)
49)Engine=InnoDB CHARSET=utf8;
50INSERT INTO `Provincia` (`Id`,`Nome`,`CodiceRegione`) VALUES
51(1, 'Padova', 1),
52(2, 'Venezia', 1),
53(3, 'Treviso', 1),
54(4, 'Milano', 2),
55(5, 'Torino', 3),
56(6, 'Roma', 4);
57
58CREATE TABLE `Citta`(
59 `Id` int AUTO_INCREMENT PRIMARY KEY,
60 `Nome` varchar(20),
61 `CodiceProvincia` int NOT NULL,
62 FOREIGN KEY(`CodiceProvincia`) REFERENCES `Provincia`(`Id`)
63)Engine=InnoDB CHARSET=utf8;
64INSERT INTO `Citta` (`Id`,`Nome`,`CodiceProvincia`) VALUES
65(1, 'Padova', 1),
66(2, 'Due Carrare', 1),
67(3, 'Venezia', 2),
68(4, 'Chioggia', 2),
69(5, 'Treviso', 3),
70(6, 'Castelfranco Veneto', 3),
71(7, 'Milano', 4),
72(8, 'Torino', 5),
73(9, 'Cirie', 5),
74(10, 'Roma', 6),
75(11, 'Citta del Vaticano', 6);
76
77
78
79CREATE TABLE `Cliente`(
80 `Id` int AUTO_INCREMENT PRIMARY KEY,
81 `Nome` varchar(20) NOT NULL,
82 `Cognome` varchar(20) NOT NULL,
83 `Indirizzo` varchar(30),
84 `Telefono` varchar(15),
85 `Mail` varchar(50)
86)Engine=InnoDB CHARSET=utf8;
87INSERT INTO `Cliente` (`Id`, `Nome`, `Cognome`, `Indirizzo`, `Telefono`, `Mail`) VALUES
88(1, 'Marco', 'Rosen', 'via Monte Grappa', '3329499785', 'marcorosen@gmail.com'),
89(2, 'Mario', 'Rossi', 'via Calle Nuova', NULL, 'mariorossi@hotmail.it'),
90(3, 'Anna', 'Franceschi', 'via 4 Novembre', '3317788990', 'annafrance@alice.it'),
91(4, 'Luigi', 'Piton', 'via Roma', NULL, 'luipi@gmail.com'),
92(5, 'Aldo', 'Maronni', 'via del Santo', '3312345671', 'aldomaronni@tin.it');
93
94
95CREATE TABLE `Utente`(
96 `Id` int AUTO_INCREMENT PRIMARY KEY,
97 `Nome` varchar(20) NOT NULL,
98 `Cognome` varchar(20) NOT NULL,
99 `Indirizzo` varchar(30),
100 `Telefono` varchar(15),
101 `Mail` varchar(50)
102)Engine=InnoDB CHARSET=utf8;
103INSERT INTO `Utente` (`Id`, `Nome`, `Cognome`, `Indirizzo`, `Telefono`, `Mail`) VALUES
104(1, 'Alessandro', 'Ron', 'via del Monte', '3219599850', 'alexron@gmail.com'),
105(2, 'Marco', 'Rossi', 'via Franceschi', NULL, 'marcorossi@hotmail.it'),
106(3, 'Giianpaolo', 'Franceschi', 'via 4 Novembre', '3317403990', 'giampyfrance@alice.it'),
107(4, 'Luis', 'Piron', 'via Fosso', NULL, 'luispir@gmail.com'),
108(5, 'Alda', 'Mari', 'via Gorghizzolo', '3312345671', 'aldamari@tin.it');
109
110
111CREATE TABLE `Responsabile`(
112 `Id` int AUTO_INCREMENT PRIMARY KEY,
113 `Nome` varchar(20) NOT NULL,
114 `Cognome` varchar(20) NOT NULL,
115 `Indirizzo` varchar(30),
116 `Telefono` varchar(15),
117 `Mail` varchar(50),
118 `Stipendio` decimal(9,2) NOT NULL,
119 `Filiale` int NOT NULL
120
121)Engine=InnoDB CHARSET=utf8;
122INSERT INTO `Responsabile` (`Id`,`Nome`,`Cognome`,`Indirizzo`,`Telefono`,`Mail`,`Stipendio`,`Filiale`) VALUES
123(1, 'Romeo', 'Francese','via San Marco','3219877766','romeofrancee@gmail.com',2050.10,1),
124(2, 'Alfredo', 'Degianni','via Calle Nuova','3332456777','alfredodegianni@gmail.com',3000,2),
125(3, 'Lucrezia', 'Sapiente','via Roma','3332213449','lucresap@gmail.com',2300,3),
126(4, 'Lorenzo', 'Franto','via San Marco','3265477766','lorenzofranto@gmail.com',2150.10,4),
127(5, 'Alfie', 'DellaCalle','via Abbazia',NULL,'alfie@gmail.com',2000,5),
128(6, 'Lorenzina', 'Sasse','via Roma','3332211849','lorenzinasasse@gmail.com',2500,6),
129(7, 'Silvio', 'Nando','via San Marco','3212347766','silvionando@gmail.com',2350.10,7),
130(8, 'Nunzio', 'Loli','via Calle Nuova','3398766777','nunziololi@gmail.com',3200,8),
131(9, 'Lara', 'Nonnata','via Roma','3720946244','laranannata@gmail.com',2700,9),
132(10, 'Lollo', 'Coronto','via San Marco','3394777766','lollocoronto@gmail.com',3150,10),
133(11, 'Silvia', 'Desille','via Roma',NULL,'silviadesille@gmail.com',3000,11),
134(12, 'Diletta', 'Ranasse','via Gorghizzolo','3496021725','dilettaranasse@gmail.com',4500,12);
135
136CREATE TABLE `Filiale`(
137 `Id` int AUTO_INCREMENT PRIMARY KEY,
138 `Indirizzo` varchar(30) NOT NULL,
139 `CodiceCitta` int NOT NULL,
140 FOREIGN KEY(`CodiceCitta`) REFERENCES `Citta`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
141 `CodiceResponsabile` int NOT NULL,
142 FOREIGN KEY(`CodiceResponsabile`) REFERENCES `Responsabile`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
143)Engine=InnoDB CHARSET=utf8;
144INSERT INTO `Filiale` (`Id`, `Indirizzo`, `CodiceCitta`, `Codiceresponsabile`) VALUES
145(1, 'via San Marco',1,1),
146(2, 'via Alessandro Manzoni',1,1),
147(3, 'via Roma',2,1),
148(4, 'via Calle Nuova', 3,1),
149(5, 'vicolo Rosa', 4,2),
150(6, 'via Monfenera', 5,2),
151(7, 'via Ospedale', 6,2),
152(8, 'via Roma', 7,2),
153(9, 'viale Porta Nuova', 8,2),
154(10, 'via Cirie', 9,3),
155(11, 'via Roma', 10,3),
156(12, 'via della Posta', 11,3);
157
158CREATE TABLE `Camion`(
159 `Id` int AUTO_INCREMENT PRIMARY KEY,
160 `DataUltimoTagliando` date,
161 `DataProssimarevisione` date,
162 `MaxAuto` smallint,
163 `Filiale` int NOT NULL
164)Engine=InnoDB CHARSET=utf8;
165INSERT INTO `Camion` (`Id`, `DataUltimotagliando`,`DataProssimaRevisione`,`MaxAuto`,`Filiale`) VALUES
166(1, '2018-05-10', '2019-11-24', 5,1),
167(2, '2018-09-23', '2019-02-22', 3,2),
168(3, '2019-01-22', '2020-01-10', 7,4),
169(4, '2015-03-14', '2018-08-19', 2,6);
170
171 CREATE TABLE `Impiegato`(
172 `Id` int AUTO_INCREMENT PRIMARY KEY,
173 `Nome` varchar(20) NOT NULL,
174 `Cognome` varchar(20) NOT NULL,
175 `Indirizzo` varchar(30),
176 `Telefono` varchar(15),
177 `Mail` varchar(50),
178 `Stipendio` decimal(9,2) NOT NULL,
179 `Filiale` int NOT NULL,
180 FOREIGN KEY(`Filiale`) REFERENCES `Filiale`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
181)Engine=InnoDB CHARSET=utf8;
182INSERT INTO `Impiegato`(`Id`,`Nome`,`Cognome`,`Indirizzo`,`Telefono`,`Mail`,`Stipendio`,`Filiale`) VALUES
183(1,'Vincenzo','Asco',NULL,NULL,NULL,1340,1),
184(2,'Alberto','Sipido',NULL,NULL,NULL,1450,1),
185(3,'Anna','Scotto',NULL,NULL,NULL,1240,2),
186(4,'Federico','Lorto',NULL,NULL,NULL,1550,3),
187(5,'Mario','Sottollo',NULL,NULL,NULL,1640,3),
188(6,'Matteo','Pidollo',NULL,NULL,NULL,1750,4),
189(7,'Laurenzia','Rocco',NULL,NULL,NULL,1340,4),
190(8,'Hamza','Rudo',NULL,NULL,NULL,1050,5),
191(9,'Helena','Isco',NULL,NULL,NULL,1640,6),
192(10,'Gianluigi','Ippido',NULL,NULL,NULL,1850,7);
193
194
195CREATE TABLE `Agente`(
196 `Id` int AUTO_INCREMENT PRIMARY KEY,
197 `Nome` varchar(20) NOT NULL,
198 `Cognome` varchar(20) NOT NULL,
199 `Indirizzo` varchar(30),
200 `Telefono` varchar(15),
201 `Mail` varchar(50),
202 `Stipendio` decimal(9,2) NOT NULL,
203 `Filiale` int NOT NULL,
204 FOREIGN KEY(`Filiale`) REFERENCES `Filiale`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
205)Engine=InnoDB CHARSET=utf8;
206INSERT INTO `Agente`(`Id`,`Nome`,`Cognome`,`Indirizzo`,`Telefono`,`Mail`,`Stipendio`,`Filiale`) VALUES
207(1,'Vince','Sosco',NULL,NULL,NULL,1320,1),
208(2,'Alessia','Sissido',NULL,NULL,NULL,1550,1),
209(3,'Artemisio','Scetto',NULL,NULL,NULL,1740,2),
210(4,'Fede','Lorro',NULL,NULL,NULL,1550,3),
211(5,'Marco','Spoto',NULL,NULL,NULL,1640,3),
212(6,'Mattia','Pidione',NULL,NULL,NULL,1750,4),
213(7,'Laura','Rosa',NULL,NULL,NULL,1340,4),
214(8,'Yassine','Raado',NULL,NULL,NULL,1050,5),
215(9,'Eleonora','Risisco',NULL,NULL,NULL,1230,6),
216(10,'Gianni','Illaoo',NULL,NULL,NULL,1570,7);
217
218CREATE TABLE `Camionista`(
219 `Id` int AUTO_INCREMENT PRIMARY KEY,
220 `Nome` varchar(20) NOT NULL,
221 `Cognome` varchar(20) NOT NULL,
222 `Indirizzo` varchar(30),
223 `Telefono` varchar(15),
224 `Mail` varchar(50),
225 `Stipendio` decimal(9,2) NOT NULL,
226 `Filiale` int NOT NULL,
227 FOREIGN KEY(`Filiale`) REFERENCES `Filiale`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
228)Engine=InnoDB CHARSET=utf8;
229
230 INSERT INTO `Camionista`(`Id`,`Nome`,`Cognome`,`Indirizzo`,`Telefono`,`Mail`,`Stipendio`,`Filiale`) VALUES
231(1,'Mauro','Amino',NULL,NULL,NULL,1720,1),
232(2,'Mauro','Amino',NULL,NULL,NULL,1720,2),
233(3,'Mauro','Amino',NULL,NULL,NULL,1720,3),
234(4,'Artemisio','Delaro',NULL,NULL,NULL,1850,1),
235(5,'Miroslav','Spootter',NULL,NULL,NULL,1940,4),
236(6,'Miroslav','Spootter',NULL,NULL,NULL,1940,5),
237(7,'Laurenzio','LaRosa',NULL,NULL,NULL,2000,6),
238(8,'Laurenzio','LaRosa',NULL,NULL,NULL,2000,7),
239(9,'Laurenzio','LaRosa',NULL,NULL,NULL,2000,8),
240(10,'Giangianni','Astio',NULL,NULL,NULL,1370,9),
241(11,'Letizia','Slotta', NULL,NULL,NULL,2000,10),
242(12,'Letizia','Slotta', NULL,NULL,NULL,2000,11),
243(13,'Letizia','Slotta', NULL,NULL,NULL,2000,12);
244
245CREATE TABLE `Categoria`(
246 `Id` int AUTO_INCREMENT PRIMARY KEY,
247 `Nome` varchar(50) NOT NULL
248)Engine=InnoDB CHARSET=utf8;
249INSERT INTO `Categoria`(`Id`,`Nome`) VALUES
250(1,'Sport'),
251(2,'Cross'),
252(3,'Enduro'),
253(4,'Custom'),
254(5,'Cafe racer'),
255(6,'Scooter'),
256(7,'Turismo'),
257(8,'Quad'),
258(9,'Epoca'),
259/* adesso iniziano categorie auto*/
260(10,'Utilitaria'),
261(11,'Berlina'),
262(12,'Station wagon'),
263(13,'Monovolume'),
264(14,'SUV'),
265(15,'Cabrio'),
266(16,'Epoca'),
267(17,'Coupe');
268
269CREATE TABLE `Auto` (
270 `Id` int AUTO_INCREMENT PRIMARY KEY,
271 `Marca` varchar(50) NOT NULL,
272 `Modello` varchar(50) NOT NULL,
273 `AnnoProduzione` smallint NOT NULL,
274 `DataImmatricolazione` date NOT NULL,
275 `TipoCarburante` varchar(10) NOT NULL,
276 `NumProprietari` smallint NOT NULL,
277 `Prezzo` decimal(9,2) NOT NULL,
278 `Colore` varchar(10),
279 `ClasseEmissioni` varchar(5),
280 `Cilindrata` int(10),
281 `Potenza` smallint,
282 `Kilometraggio` int(10),
283 `DataUltimoTagliando` date,
284 `DataProssimaRevisione` date,
285 `NumPosti` smallint,
286 `NumPorte` smallint,
287 `Optional` varchar(50),
288 `Cambio` varchar(10),
289 `Trazione` varchar(15),
290 `Categoria` int NOT NULL,
291 FOREIGN KEY(`Categoria`) REFERENCES `Categoria`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
292 `Citta` int NOT NULL,
293 FOREIGN KEY(`Citta`) REFERENCES `Citta`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
294 `Filiale` int NOT NULL,
295 FOREIGN KEY(`Filiale`) REFERENCES `Filiale`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
296)Engine=InnoDB CHARSET=utf8;
297INSERT INTO `Auto`(`Id`,`Marca`,`Modello`,`AnnoProduzione`,`DataImmatricolazione`,`TipoCarburante`,`NumProprietari`,`Prezzo`,`Colore`,
298`ClasseEmissioni`,`Cilindrata`,`Potenza`,`Kilometraggio`,`DataUltimoTagliando`,`DataProssimaRevisione`,`NumPosti`,`NumPorte`,`Optional`,
299`Cambio`,`Trazione`,`Categoria`,`Citta`,`Filiale`) VALUES
300(1,'Alfa Romeo','Giulietta','2017','2018-01-22','Diesel','1',20000,'Nera','Euro5',2000,110,100000,'2017-02-22','20-03-2019',5,4,'Premium','Manuale','Posteriore','11','2','3'),
301(2,'Opel','Astra','2017','2018-04-13','Benzina','1',15000,'Rossa','Euro6',1000,90,20000,'2017-02-22','20-08-2019',5,4,'Base','Manuale','Posteriore','10','1','1'),
302(3,'Fiat','500','2017','2018-01-22','Benzina','1',10000,'Bianca','Euro3',1200,80,120000,'2018-04-12','20-10-2019',5,4,'Premium','Automatico','Anteriore','13','1','2'),
303(4,'Fiat','Panda','2017','2018-04-13','Benzina','1',15000,'Rossa','Euro6',1000,90,100000,'2017-12-30','20-11-2019',5,4,'Base','Manuale','4x4','10','3','4'),
304(5,'Alfa Romeo','Giulietta TurboDiesel','2017','2018-04-13','TurboDiesel','1',30000,'Rossa','Euro6',2800,90,100000,'2017-12-30','20-11-2019',5,4,'Base','Manuale','4x4','10','4','5');
305
306
307
308CREATE TABLE `Moto` (
309 `Id` int AUTO_INCREMENT PRIMARY KEY,
310 `Marca` varchar(50) NOT NULL,
311 `Modello` varchar(50) NOT NULL,
312 `AnnoProduzione` smallint NOT NULL,
313 `DataImmatricolazione` date NOT NULL,
314 `NumProprietari` smallint NOT NULL,
315 `Prezzo` decimal(9,2) NOT NULL,
316 `Colore` varchar(10),
317 `ClasseEmissioni` varchar(5),
318 `Cilindrata` int(10),
319 `Potenza` smallint,
320 `Kilometraggio` int(10),
321 `DataUltimoTagliando` date,
322 `DataProssimaRevisione` date,
323 `Categoria` int NOT NULL,
324 FOREIGN KEY(`Categoria`) REFERENCES `Categoria`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
325 `Citta` int NOT NULL,
326 FOREIGN KEY(`Citta`) REFERENCES `Citta`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
327 `Filiale` int NOT NULL,
328 FOREIGN KEY(`Filiale`) REFERENCES `Filiale`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
329)Engine=InnoDB CHARSET=utf8;
330INSERT INTO `Moto`
331(`Id`,`Marca`, `Modello`, `AnnoProduzione`,`DataImmatricolazione`,`NumProprietari`,`Prezzo`,`Colore`,`ClasseEmissioni`,`Cilindrata`,`Potenza`,`Kilometraggio`,
332`DataUltimoTagliando`,`DataProssimaRevisione`,`Categoria`,`Citta`,`Filiale`) VALUES
333(1,'Kawasaki','125','2012','2013-10-10',2,2000,'Blu','Euro4',700,47,10000,'2018-10-24','2020-01-10',1,5,6),
334(2,'Vespa','50','2010','2011-01-20',1,200,'Nero','Euro3',250,47,10000,'2018-02-12','2019-04-20',4,6,7),
335(3,'Husqvarna','250','2011','2011-10-10',2,3000,'Blu','Euro4',250,37,5000,'2017-09-14','2019-05-14',2,7,8),
336(4,'Yamaha','600','2010','2011-10-10',2,2500,'Bianco','Euro4',600,30,1000,'2018-02-04','2020-06-21',8,8,9),
337(5,'Honda','250','2012','2013-10-10',2,2000,'Blu','Euro4',700,47,10000,'2018-10-24','2020-01-10',1,9,10);
338
339CREATE TABLE `Inserzione`(
340 `Id` int AUTO_INCREMENT PRIMARY KEY,
341 `DataPubblicazione` date,
342 `Descrizione` varchar(100),
343 `CodiceUtente` int NOT NULL,
344 FOREIGN KEY(`CodiceUtente`) REFERENCES `Utente`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
345 `CodiceAuto` int,
346 FOREIGN KEY(`CodiceAuto`) REFERENCES `Auto`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
347 `CodiceMoto` int,
348 FOREIGN KEY(`CodiceMoto`) REFERENCES `Moto`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
349 CHECK(NOT(`CodiceAuto` IS NOT NULL AND `CodiceMoto` IS NOT NULL))
350)Engine=InnoDB CHARSET=utf8;
351INSERT INTO `Inserzione`(`Id`,`Descrizione`,`CodiceUtente`,`CodiceAuto`,`CodiceMoto`) VALUES
352(1,'Scooterino promettente',2,NULL,2),
353(2,'SuperAuto',2,5,NULL),
354(3,'Quad ben messo',3,NULL,4),
355(4,'Fiat 500',4,3,NULL),
356(5, 'Opel Astra',5,2,NULL);
357
358
359CREATE TABLE `Spedizione`(
360 `Id` int AUTO_INCREMENT PRIMARY KEY,
361 `DataPartenza` date,
362 `DataArrivoPrevisto` date,
363 `DataArrivoEffettivo` date,
364 `Costo` decimal(9,2),
365 `CodiceCamionista` int NOT NULL,
366 FOREIGN KEY(`CodiceCamionista`) REFERENCES `Camionista`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
367 `CodiceCamion` int NOT NULL,
368 FOREIGN KEY(`CodiceCamion`) REFERENCES `Camion`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
369)Engine=InnoDB CHARSET=utf8;
370INSERT INTO `Spedizione`(`Id`,`DataPartenza`,`DataArrivoPrevisto`,`DataArrivoEffettivo`,`Costo`,`CodiceCamionista`,`CodiceCamion`) VALUES
371(1,'2018-12-23','2019-01-10','2019-01-12',350,6,3),
372(2,'2018-02-21','2018-03-01','2018-02-27',400,10,3),
373(3,'2019-01-13','2019-01-20','2019-01-18',550,7,1),
374(4,'2018-07-12','2018-07-20','2018-07-22',300,4,2),
375(5,'2018-05-15','2018-05-25','2018-05-25',500,1,4);
376
377
378CREATE TABLE `VenditaOnline`(
379 `Id` int AUTO_INCREMENT PRIMARY KEY,
380 `DataAcquisto` date,
381 `CodiceAcquirente` int NOT NULL,
382 FOREIGN KEY(`CodiceAcquirente`) REFERENCES `Utente`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
383 `CodiceInserzione` int NOT NULL,
384 FOREIGN KEY(`CodiceInserzione`) REFERENCES `Inserzione`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
385 `CodiceSpedizione` int NOT NULL,
386 FOREIGN KEY(`CodiceSpedizione`) REFERENCES `Spedizione`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE
387)Engine=InnoDB CHARSET=utf8;
388INSERT INTO `VenditaOnline`(`Id`,`DataAcquisto`,`CodiceAcquirente`,`CodiceInserzione`,`CodiceSpedizione`) VALUES
389(1,'2018-12-22',1,2,1),
390(2,'2018-02-20',3,1,2),
391(3,'2019-01-10',4,5,3);
392
393CREATE TABLE `VenditaOffline`(
394 `Id` int AUTO_INCREMENT PRIMARY KEY,
395 `DataAcquisto` date,
396 `CodiceAuto` int,
397 FOREIGN KEY(`CodiceAuto`) REFERENCES `Auto`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
398 `CodiceMoto` int,
399 FOREIGN KEY(`CodiceMoto`) REFERENCES `Moto`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
400 `CodiceAgente` int NOT NULL,
401 FOREIGN KEY(`CodiceAgente`) REFERENCES `Agente`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
402 `CodiceCliente` int NOT NULL,
403 FOREIGN KEY(`CodiceCliente`) REFERENCES `Cliente`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
404 `CodiceSpedizione` int NOT NULL,
405 FOREIGN KEY(`CodiceSpedizione`) REFERENCES `Spedizione`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
406 CHECK(NOT(`CodiceAuto` IS NOT NULL AND `CodiceMoto` IS NOT NULL))
407)Engine=InnoDB CHARSET=utf8;
408INSERT INTO `VenditaOffline`(`Id`,`DataAcquisto`,`CodiceAuto`,`CodiceMoto`,`CodiceAgente`,`CodiceCliente`,`CodiceSpedizione`) VALUES
409(1,'2019-01-10',1,NULL,1,1,3),
410(2,'2018-05-14',4,NULL,1,2,5),
411(3,'2018-05-13',NULL,1,2,3,5),
412(4,'2018-07-11',NULL,5,2,1,4);
413
414
415
416
417
418
419/*
420 * I Operazione: Classifica vendite agenti nel 2018
421 */
422CREATE OR REPLACE VIEW Agenti AS
423select Agente.Id,Agente.Nome,Agente.Cognome,Agente.Stipendio,count(*) as Vendite
424from Agente join VenditaOffline on Agente.Id=VenditaOffline.CodiceAgente
425where DataAcquisto<'2019-01-01'
426group by Agente.Id
427order by Vendite DESC;
428/*
429 * II Operazione: Agente che ha venduto di piu nel 2018
430 */
431CREATE OR REPLACE VIEW AgenteConPiuVendite AS
432select Id,Nome,Cognome,Stipendio,Vendite
433from Agenti
434where Vendite=any(select max(Vendite) from Agenti);
435
436/*
437 * III operazione: Auto vendute
438 */
439CREATE OR REPLACE VIEW Autovendute AS
440 (select Inserzione.CodiceAuto,Auto.Marca,Auto.Modello,Auto.Categoria
441 from VenditaOnline,Inserzione,Auto
442 where VenditaOnline.CodiceInserzione=Inserzione.Id and Auto.Id=Inserzione.CodiceAuto
443 order by VenditaOnline.Id)
444 UNION
445 (select VenditaOffline.CodiceAuto,Auto.Marca,Auto.Modello,Auto.Categoria
446 from VenditaOffline join Auto on Auto.Id=VenditaOffline.CodiceAuto
447 order by VenditaOffline.Id);
448
449/*
450 * IV OPERAZIONE: Categoria Auto più venduta
451 */
452
453CREATE OR REPLACE VIEW Vistaausiliaria AS
454 select CodiceAuto,Marca,Modello,Categoria,count(Categoria) as Cate
455 from Autovendute
456 group by Categoria;
457CREATE OR REPLACE VIEW CategoriaAutoPiuVenduta AS
458 select Vistaausiliaria.Categoria,Categoria.Nome
459 from Vistaausiliaria join Categoria on Categoria.id=Vistaausiliaria.Categoria
460 where Cate=any(select max(Cate) from Vistaausiliaria);
461
462/*
463 * V OPERAZIONE: Moto vendute
464 */
465CREATE OR REPLACE VIEW Motovendute AS
466 (select Inserzione.CodiceMoto,Moto.Marca,Moto.Modello,Moto.Categoria
467 from VenditaOnline,Inserzione,Moto
468 where VenditaOnline.CodiceInserzione=Inserzione.Id and Moto.Id=Inserzione.CodiceMoto
469 order by VenditaOnline.Id)
470 UNION
471 (select VenditaOffline.CodiceMoto,Moto.Marca,Moto.Modello,Moto.Categoria
472 from VenditaOffline join Moto on Moto.Id=VenditaOffline.CodiceMoto
473 order by VenditaOffline.Id);
474
475/*
476 * VI OPERAZIONE: Categoria Moto più Venduta
477 */
478
479CREATE OR REPLACE VIEW Vistaausiliaria2 AS
480 select CodiceMoto,Marca,Modello,Categoria,count(Categoria) as Cate
481 from Motovendute
482 group by Categoria;
483CREATE OR REPLACE VIEW CategoriaMotoPiuVenduta AS
484 select Vistaausiliaria2.Categoria,Categoria.Nome
485 from Vistaausiliaria2 join Categoria on Categoria.id=Vistaausiliaria2.Categoria
486 where Cate=any(select max(Cate) from Vistaausiliaria2);
487
488/*
489 * VII OPERAZIONE: Cancellazione Inserzione
490 */
491
492DELIMITER |
493CREATE OR REPLACE PROCEDURE Cancellazione_Inserzione ( C_id INTEGER )
494BEGIN
495 DELETE FROM VenditaOnline WHERE CodiceInserzione = C_id;
496 DELETE FROM Inserzione WHERE Id=C_id;
497END |
498DELIMITER ;
499
500/*
501-- Oppure direttamente la selezione SQL, al posto di <C_id> mettere l'id dell'inserzione--
502 DELETE FROM VenditaOnline WHERE CodiceInserzione = <C_id>;
503 DELETE FROM Inserzione WHERE Id=<C_id>;
504*/
505
506
507/*
508 * VIII OPERAZIONE: Utente con più annunci online
509 */
510
511CREATE OR REPLACE VIEW Vistaausiliaria3 AS
512 select Id,CodiceUtente,count(*) as NumAnnunci
513 from Inserzione
514 group by CodiceUtente;
515
516CREATE OR REPLACE VIEW UtenteConPiuAnnunci AS
517 select Vistaausiliaria3.CodiceUtente,Utente.Nome,Utente.Cognome,Vistaausiliaria3.NumAnnunci
518 from Vistaausiliaria3 join Utente on Vistaausiliaria3.CodiceUtente=Utente.Id
519 where NumAnnunci=any(select max(NumAnnunci) from Vistaausiliaria3);
520
521
522
523/*
524 * IX OPERAZIONE: Codice identificativo camion con revisione che scadrà prima del 16/09/2019
525 */
526
527CREATE OR REPLACE VIEW CamionRevisioneInScadenza AS
528 select Id,DataProssimaRevisione
529 from Camion
530 where DataProssimaRevisione<'2019-09-16';
531
532/*
533 * X OPERAZIONE:Controllare che aggiornando le tuple di auto ci siano categorie solo di auto
534 */
535
536
537DELIMITER |
538
539CREATE OR REPLACE TRIGGER CheckCategoriaAuto BEFORE UPDATE ON Auto
540FOR EACH ROW
541BEGIN
542IF (New.Categoria < 10 OR New.Categoria>17) THEN
543set New.Categoria=Old.Categoria;
544END IF;
545END|
546DELIMITER ;
547
548/*
549 * XI OPERAZIONE: Controllare che nelle tuple di auto vengano inserite solo categorie di auto
550 */
551DELIMITER |
552
553CREATE OR REPLACE TRIGGER CheckCategoriaAutoInsert BEFORE INSERT ON Auto
554FOR EACH ROW
555BEGIN
556IF (New.Categoria < 10 OR New.Categoria>17) THEN
557set New.Categoria=10;
558END IF;
559END|
560DELIMITER ;
561
562/*
563 * XII OPERAZIONE: Controllare che nelle tuple di moto vengano inserite solo categorie di moto
564 */
565
566DELIMITER |
567
568CREATE OR REPLACE TRIGGER CheckCategoriaMotoInsert BEFORE INSERT ON Moto
569FOR EACH ROW
570BEGIN
571IF (New.Categoria < 1 OR New.Categoria>9) THEN
572set New.Categoria=1;
573END IF;
574END|
575DELIMITER ;
576
577/*
578 * XIII OPERAZIONE:Controllare che aggiornando le tuple di moto ci siano categorie solo di moto
579 */
580
581
582DELIMITER |
583
584CREATE OR REPLACE TRIGGER CheckCategoriaMoto BEFORE UPDATE ON Moto
585FOR EACH ROW
586BEGIN
587IF (New.Categoria < 1 OR New.Categoria>9) THEN
588set New.Categoria=Old.Categoria;
589END IF;
590END|
591DELIMITER ;