· 6 years ago · Jan 04, 2020, 09:32 PM
1SET FOREIGN_KEY_CHECKS=0;
2
3-- phpMyAdmin SQL Dump
4-- version 4.6.6deb5
5-- https://www.phpmyadmin.net/
6--
7-- Host: localhost:3306
8-- Creato il: Gen 04, 2020 alle 21:41
9-- Versione del server: 10.1.43-MariaDB-0ubuntu0.18.04.1
10-- Versione PHP: 7.2.24-0ubuntu0.18.04.1
11
12SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
13SET time_zone = "+00:00";
14
15
16/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
17/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
18/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
19/*!40101 SET NAMES utf8mb4 */;
20
21DROP TABLE IF EXISTS Artista;
22DROP TABLE IF EXISTS Asta;
23DROP TABLE IF EXISTS Battitore;
24DROP TABLE IF EXISTS Creazione;
25DROP TABLE IF EXISTS Dipendente;
26DROP TABLE IF EXISTS Dipinto;
27DROP TABLE IF EXISTS DocumentoDiAutenticita;
28DROP TABLE IF EXISTS Lotto;
29DROP TABLE IF EXISTS Mediatore;
30DROP TABLE IF EXISTS Mosaico;
31DROP TABLE IF EXISTS Museo;
32DROP TABLE IF EXISTS Offerta;
33DROP TABLE IF EXISTS Opera;
34DROP TABLE IF EXISTS Partecipazione;
35DROP TABLE IF EXISTS Partecipazione_telefonica;
36DROP TABLE IF EXISTS PersonaleInterno;
37DROP TABLE IF EXISTS Privato;
38DROP TABLE IF EXISTS Proposta;
39DROP TABLE IF EXISTS SalaAste;
40DROP TABLE IF EXISTS Scultura;
41DROP TABLE IF EXISTS Ufficio;
42DROP TABLE IF EXISTS Utente;
43DROP TABLE IF EXISTS Valutatore;
44
45CREATE TABLE `Artista` (
46 `IDArtista` int(15) NOT NULL,
47 `DataNascita` date NOT NULL,
48 `Nome` varchar(15) NOT NULL,
49 `Cognome` varchar(15) NOT NULL,
50 `DataMorte` date DEFAULT NULL,
51 PRIMARY KEY(`IDArtista`,`DataNascita`)
52) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
53
54--
55-- Dump dei dati per la tabella `Artista`
56--
57
58INSERT INTO `Artista` (`IDArtista`, `DataNascita`, `Nome`, `Cognome`, `DataMorte`) VALUES
59(1, '1881-10-25', 'Pablo', 'Picasso', '1920-01-06'),
60(2, '1840-11-14', 'Claude', 'Monet', '1921-01-06'),
61(3, '1904-05-11', 'Salvador', 'Dalì', '1930-01-06'),
62(4, '1853-03-30', 'Vincent', 'Van Gogh', '1940-01-06'),
63(5, '1542-04-15', 'Leonardo', 'Da Vinci', '1925-01-06'),
64(6, '1970-01-01', 'Samuel', 'Provetto', NULL),
65(7, '1965-02-03', 'Fabio', 'Tonon', NULL),
66(8, '1475-01-01', 'Michelangelo', 'Buonarroti', '1938-01-06'),
67(9, '1757-11-01', 'Antonio', 'Canova', '1820-01-06'),
68(10, '0000-00-00', 'Sconosciuto', '', NULL);
69
70-- --------------------------------------------------------
71
72--
73-- Struttura della tabella `Asta`
74--
75
76CREATE TABLE `Asta` (
77 `Nome` varchar(15) PRIMARY KEY,
78 `NumeroProposte` int(15) NOT NULL,
79 `Data` date NOT NULL,
80 `Battitore` int(15) NOT NULL,
81 FOREIGN KEY `Battitore` REFERENCES Battitore(`Matricola`)
82) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
83
84--
85-- Dump dei dati per la tabella `Asta`
86--
87
88INSERT INTO `Asta` (`Nome`, `NumeroProposte`, `Data`, `Battitore`) VALUES
89('ACacciaDiArte', 4, '2019-12-04', 1100003),
90('ArteMetaforica', 1, '2019-12-09', 1100000),
91('ArtePerTutti', 2, '2019-12-27', 1100003),
92('LeBelleArti', 2, '2019-05-15', 1100000),
93('LeOperePiuBelle', 1, '2019-12-25', 1100001),
94('OpereUniche', 3, '2020-01-10', 1100000),
95('QuadriPaesaggio', 2, '2020-02-13', 1100001);
96
97-- --------------------------------------------------------
98
99--
100-- Struttura della tabella `Battitore`
101--
102
103CREATE TABLE `Battitore` (
104 `Matricola` int(15) PRIMARY KEY,
105 `AnniDiEsperienza` smallint(15) NOT NULL,
106 FOREIGN KEY (`Matricola`) REFERENCES `PersonaleInterno` (`Matricola`)
107) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
108
109--
110-- Dump dei dati per la tabella `Battitore`
111--
112
113INSERT INTO `Battitore` (`Matricola`, `AnniDiEsperienza`) VALUES
114(1100000, 10),
115(1100001, 20),
116(1100003, 30);
117
118-- --------------------------------------------------------
119
120--
121-- Struttura della tabella `Creazione`
122--
123
124CREATE TABLE `Creazione` (
125 `Opera` int(15) NOT NULL,
126 `ID_Artista` int(15) NOT NULL,
127 PRIMARY KEY(`Opera`,`ID_Artista`),
128 FOREIGN KEY (`Opera`) REFERENCES `Opera` (`IDOpera`),
129 FOREIGN KEY (`ID_Artista`) REFERENCES `Artista` (`IDArtista`)
130) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
131
132--
133-- Dump dei dati per la tabella `Creazione`
134--
135
136INSERT INTO `Creazione` (`Opera`, `ID_Artista`) VALUES
137(1, 2),
138(2, 1),
139(3, 3),
140(4, 4),
141(5, 5),
142(6, 9),
143(7, 8),
144(8, 2),
145(9, 8),
146(10, 9),
147(11, 6),
148(12, 7);
149
150-- --------------------------------------------------------
151
152--
153-- Struttura della tabella `Dipendente`
154--
155
156CREATE TABLE `Dipendente` (
157 `Matricola` int(15) PRIMARY KEY,
158 `Cognome` varchar(15) NOT NULL,
159 `Nome` varchar(15) NOT NULL,
160 `Telefono` int(15) DEFAULT NULL,
161 `Ufficio` int(15) NOT NULL,
162 FOREIGN KEY (`Ufficio`) REFERENCES `Ufficio` (`IDUfficio`)
163) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
164
165--
166-- Dump dei dati per la tabella `Dipendente`
167--
168
169INSERT INTO `Dipendente` (`Matricola`, `Cognome`, `Nome`, `Telefono`, `Ufficio`) VALUES
170(1193290, 'Rossi', 'Mario', 1921293340, 1),
171(1193291, 'Giorgio', 'Verdi', 1291293341, 2),
172(1193292, 'Anna', 'Prova', 1291293342, 3),
173(1193293, 'Gabriella', 'Gialli', 1291293343, 3),
174(1193294, 'Paola', 'Blu', 1291293344, 2),
175(1193295, 'Mara', 'Terzo', 1291293345, 1),
176(1193296, 'Prisco', 'Cesare', 1291293346, 2),
177(1193297, 'Sardi', 'Riccardo', 1291293347, 1),
178(1193298, 'Grigi', 'Marina', 1291293348, 2),
179(1193299, 'Toscani', 'Serena', 1291293349, 3);
180
181-- --------------------------------------------------------
182
183--
184-- Struttura della tabella `Dipinto`
185--
186
187CREATE TABLE `Dipinto` (
188 `IDOpera` int(15) PRIMARY KEY,
189 `TecnicaPittorica` varchar(15) NOT NULL DEFAULT 'Olio su Tela',
190 `Larghezza` decimal(5,2) NOT NULL,
191 `Altezza` decimal(5,2) NOT NULL,
192 FOREIGN KEY (`IDOpera`) REFERENCES `Opera` (`IDOpera`)
193) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
194
195--
196-- Dump dei dati per la tabella `Dipinto`
197--
198
199INSERT INTO `Dipinto` (`IDOpera`, `TecnicaPittorica`, `Larghezza`, `Altezza`) VALUES
200(1, 'Olio su Tela', '100.00', '55.00'),
201(2, 'Olio su tavola', '77.00', '45.00'),
202(3, 'Sfumatura', '45.00', '78.00'),
203(4, 'Acquerello', '89.00', '34.00'),
204(5, 'Olio su tela', '100.00', '55.00'),
205(8, 'Acquerello', '100.00', '50.00');
206
207-- --------------------------------------------------------
208
209--
210-- Struttura della tabella `DocumentoDiAutenticita`
211--
212
213CREATE TABLE `DocumentoDiAutenticita` (
214 `Data` date NOT NULL,
215 `Valutatore` int(15) NOT NULL,
216 `Lotto` int(15) PRIMARY KEY,
217 `Autenticita` tinyint(1) NOT NULL DEFAULT '1',
218 `StatoConservazione` tinyint(1) NOT NULL DEFAULT '3',
219 FOREIGN KEY (`Valutatore`) REFERENCES `Valutatore` (`Matricola`) ON DELETE NO ACTION ON UPDATE NO ACTION,
220 FOREIGN KEY (`Lotto`) REFERENCES `Lotto` (`Progressivo`) ON DELETE NO ACTION ON UPDATE NO ACTION
221) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
222
223--
224-- Dump dei dati per la tabella `DocumentoDiAutenticita`
225--
226
227INSERT INTO `DocumentoDiAutenticita` (`Data`, `Valutatore`, `Lotto`, `Autenticita`, `StatoConservazione`) VALUES
228('2019-12-29', 1193290, 1, 1, 5),
229('2019-12-28', 1193291, 2, 1, 4),
230('2019-12-27', 1193295, 3, 1, 2),
231('2019-12-26', 1193290, 4, 1, 5),
232('2019-12-25', 1193290, 5, 1, 3),
233('2019-12-17', 1193291, 6, 1, 5),
234('2019-12-01', 1193295, 7, 1, 3),
235('2019-11-24', 1193290, 8, 1, 4),
236('2019-12-24', 1193291, 9, 1, 3),
237('2019-12-10', 1193295, 10, 1, 2),
238('2019-11-04', 1193290, 11, 1, 3),
239('2019-11-03', 1193291, 12, 1, 2),
240('2019-12-04', 1193291, 13, 0, 1),
241('2019-12-10', 1193290, 14, 0, 1),
242('2019-11-17', 1193290, 15, 1, 5),
243('2019-12-26', 1193291, 16, 1, 5),
244('2019-12-15', 1193290, 17, 0, 1),
245('2019-12-03', 1193291, 18, 0, 1),
246('2019-12-28', 1193291, 19, 0, 1),
247('2019-11-20', 1193295, 20, 1, 4),
248('2019-11-18', 1193295, 21, 0, 1),
249('2019-12-10', 1193295, 22, 0, 1);
250
251-- --------------------------------------------------------
252
253--
254-- Struttura della tabella `Lotto`
255--
256
257CREATE TABLE `Lotto` (
258 `Progressivo` int(15) PRIMARY KEY,
259 `IDOpera` int(15) NOT NULL,
260 `Privato` varchar(15) DEFAULT NULL,
261 `Museo` int(15) DEFAULT NULL,
262 REFERENCES `Opera` (`IDOpera`) ON DELETE NO ACTION ON UPDATE NO ACTION,
263 REFERENCES `Privato` (`CF`) ON DELETE SET NULL ON UPDATE NO ACTION,
264 REFERENCES `Museo` (`PIVA`) ON DELETE NO ACTION ON UPDATE NO ACTION
265) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
266
267--
268-- Dump dei dati per la tabella `Lotto`
269--
270
271INSERT INTO `Lotto` (`Progressivo`, `IDOpera`, `Privato`, `Museo`) VALUES
272(1, 1, '134WERTOPASDFGH', NULL),
273(2, 2, 'BVCMNBCXZ123ASD', NULL),
274(3, 3, NULL, 2147483638),
275(4, 4, 'VBG345YUIQWE123', NULL),
276(5, 5, '134WERTOPASDFGH', NULL),
277(6, 6, '134WERTOPASUIPO', NULL),
278(7, 7, NULL, 2147483642),
279(8, 8, NULL, 2147483641),
280(9, 9, 'NMBHJKIUY789123', NULL),
281(10, 10, '134WERTOPASDFBD', NULL),
282(11, 11, '134WERTOPASDFBD', NULL),
283(12, 12, NULL, 2147483638),
284(13, 3, 'BVCMNBCXZ123ASD', NULL),
285(14, 5, 'BVCMNBCXZ123ASD', NULL),
286(15, 7, NULL, 2147483640),
287(16, 10, NULL, 2147483640),
288(17, 5, 'NMBHJKIUY789123', NULL),
289(18, 12, 'NMBHJKIUY789123', NULL),
290(19, 2, 'NMBHJKIUY789123', NULL),
291(20, 9, NULL, 2147483641),
292(21, 5, 'VBG345YUIQWE123', NULL),
293(22, 9, 'VBG345YUIQWE123', NULL);
294
295-- --------------------------------------------------------
296
297--
298-- Struttura della tabella `Mediatore`
299--
300
301CREATE TABLE `Mediatore` (
302 `Matricola` int(15) PRIMARY KEY,
303 `MadreLingua` varchar(15) NOT NULL DEFAULT 'Inglese',
304 FOREIGN KEY (`Matricola`) REFERENCES `PersonaleInterno` (`Matricola`)
305) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
306
307--
308-- Dump dei dati per la tabella `Mediatore`
309--
310
311INSERT INTO `Mediatore` (`Matricola`, `MadreLingua`) VALUES
312(1100002, 'Italiano'),
313(1100004, 'Francese'),
314(1100005, 'Tedesco'),
315(1100006, 'Inglese');
316
317-- --------------------------------------------------------
318
319--
320-- Struttura della tabella `Mosaico`
321--
322
323CREATE TABLE `Mosaico` (
324 `IDOpera` int(15) PRIMARY KEY,
325 `Materiale` varchar(15) NOT NULL DEFAULT 'Vetro',
326 `Tecnica` varchar(15) NOT NULL DEFAULT 'MetodoDiretto',
327 FOREIGN KEY (`IDOpera`) REFERENCES `Opera` (`IDOpera`)
328) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
329
330--
331-- Dump dei dati per la tabella `Mosaico`
332--
333
334INSERT INTO `Mosaico` (`IDOpera`, `Materiale`, `Tecnica`) VALUES
335(11, 'Pietre Naturali', 'MetodoDiretto'),
336(12, 'Vetro', 'MetodoIndiretto');
337
338-- --------------------------------------------------------
339
340--
341-- Struttura della tabella `Museo`
342--
343
344CREATE TABLE `Museo` (
345 `PIVA` int(15) PRIMARY KEY,
346 `Nome` varchar(15) NOT NULL,
347 `Direttore` varchar(15) NOT NULL
348) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
349
350--
351-- Dump dei dati per la tabella `Museo`
352--
353
354INSERT INTO `Museo` (`PIVA`, `Nome`, `Direttore`) VALUES
355(2147483638, 'NationalGallery', 'Paul Gogh'),
356(2147483639, 'MuseoEgizio', 'Maria Rossi'),
357(2147483640, 'MuseiVaticani', 'Giacinto Pinto'),
358(2147483641, 'PalazzoPitti', 'Giorgio Verdi'),
359(2147483642, 'VillaDEste', 'Andrea Beghin');
360
361-- --------------------------------------------------------
362
363--
364-- Struttura della tabella `Offerta`
365--
366
367CREATE TABLE `Offerta` (
368 `Orario` time(6) NOT NULL,
369 `Proposta` int(15) NOT NULL,
370 `Valore` int(15) NOT NULL,
371 `Utente` varchar(15) NOT NULL,
372 PRIMARY KEY(`Proposta`,`Valore`),
373 FOREIGN KEY (`Utente`) REFERENCES `Utente` (`CF`),
374 FOREIGN KEY (`Proposta`) REFERENCES `Proposta` (`ID`);
375) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
376
377--
378-- Dump dei dati per la tabella `Offerta`
379--
380
381INSERT INTO `Offerta` (`Orario`, `Proposta`, `Valore`, `Utente`) VALUES
382('06:05:09.243196', 1, 1000, 'AAABBBE55U99999'),
383('07:12:10.126212', 1, 20000, 'AAABBBE55U99999'),
384('11:33:46.664477', 2, 10000, 'APNSTR32P13L895'),
385('10:26:21.571766', 3, 1000, 'APNSTR32P13L895'),
386('10:33:46.664477', 3, 15000, 'GHTIRG9943P5B6T'),
387('07:33:46.664400', 4, 2000, 'GHTIRG9943P5B6T'),
388('07:38:00.000000', 4, 2500, 'GHTIRG9943P5B6T'),
389('07:45:26.173711', 4, 5000, 'RSNRLL67P21L58F'),
390('11:23:56.664477', 5, 6000, 'TSNSTR32P13L895'),
391('16:00:00.000000', 6, 1000, 'AAABBBE55U99999'),
392('16:14:00.000000', 6, 2000, 'GHTIRG9943P5B6T'),
393('16:16:14.000000', 6, 3000, 'RSNRLL67P21L58F'),
394('09:33:44.664488', 6, 3500, 'GHTIRG9943P5B6T'),
395('11:19:46.664477', 7, 20000, 'GHTIRG9943P5B6T'),
396('10:13:00.000000', 10, 1000, 'XXXBBBE77U99999'),
397('10:26:00.000000', 10, 10000, 'TNNSTR56P13L895'),
398('10:30:00.000000', 10, 10500, 'TNNSTR56P13L895'),
399('11:00:00.000000', 11, 700, 'AAABBBE55U99999'),
400('12:00:00.000000', 11, 800, 'AAABBBE55U99999'),
401('12:30:00.000000', 11, 10000, 'XXXBBBE77U99999'),
402('13:00:00.000000', 11, 100000, 'AAABBBE55U99999'),
403('10:30:00.000000', 15, 250, 'APNSTR32P13L895'),
404('10:32:00.000000', 15, 350, 'TSNSTR32P13L895');
405
406-- --------------------------------------------------------
407
408--
409-- Struttura della tabella `Opera`
410--
411
412CREATE TABLE `Opera` (
413 `IDOpera` int(15) PRIMARY KEY,
414 `Anno` int(4) NOT NULL,
415 `Titolo` varchar(15) NOT NULL,
416 `Tipo` enum('Dipinto','Mosaico','Scultura') NOT NULL DEFAULT 'Dipinto'
417) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
418
419--
420-- Dump dei dati per la tabella `Opera`
421--
422
423INSERT INTO `Opera` (`IDOpera`, `Anno`, `Titolo`, `Tipo`) VALUES
424(1, 1883, 'Ninfee', 'Dipinto'),
425(2, 1937, 'Guernica', 'Dipinto'),
426(3, 1948, 'Gli Elefanti', 'Dipinto'),
427(4, 1889, 'Notte Stellata', 'Dipinto'),
428(5, 1503, 'Gioconda', 'Dipinto'),
429(6, 1793, 'Amore e Psiche', 'Scultura'),
430(7, 1504, 'David', 'Scultura'),
431(8, 1873, 'I papaveri', 'Dipinto'),
432(9, 1499, 'Pieta Vaticana', 'Scultura'),
433(10, 1819, 'Venere Italica', 'Scultura'),
434(11, 1900, 'MosaicoAdEfeso', 'Mosaico'),
435(12, 1850, 'I Tre Magi', 'Mosaico');
436
437-- --------------------------------------------------------
438
439--
440-- Struttura della tabella `Partecipazione`
441--
442
443CREATE TABLE `Partecipazione` (
444 `Utente` varchar(15) NOT NULL,
445 `Asta` varchar(15) NOT NULL,
446 `NumeroPosto` int(15) DEFAULT NULL,
447 `Tipo` tinyint(15) NOT NULL,
448 `ID` int(11) PRIMARY KEY,
449 FOREIGN KEY (`Utente`) REFERENCES `Utente` (`CF`),
450 FOREIGN KEY (`Asta`) REFERENCES `Asta` (`Nome`)
451) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
452
453--
454-- Dump dei dati per la tabella `Partecipazione`
455--
456
457INSERT INTO `Partecipazione` (`Utente`, `Asta`, `NumeroPosto`, `Tipo`, `ID`) VALUES
458('AAABBBE55U99999', 'ACacciaDiArte', 20, 0, 0),
459('AAABBBE55U99999', 'ArtePerTutti', 65, 0, 1),
460('AAABBBE55U99999', 'OpereUniche', 20, 0, 2),
461('AAABBBE55U99999', 'QuadriPaesaggio', NULL, 1, 3),
462('AAACCCE55U99999', 'ArteMetaforica', NULL, 1, 4),
463('APNSTR32P13L895', 'LeOperePiuBelle', 23, 0, 5),
464('APNSTR32P13L895', 'QuadriPaesaggio', NULL, 1, 6),
465('GHTIRG9943P5B6T', 'ArteMetaforica', NULL, 1, 7),
466('GHTIRG9943P5B6T', 'LeBelleArti', 27, 0, 8),
467('RSNRLL67P21L58F', 'ArtePerTutti', 34, 0, 9),
468('SPGXXX99P16D442', 'ACacciaDiArte', 89, 0, 10),
469('SPGXXX99P16D442', 'OpereUniche', NULL, 1, 11),
470('TSNSTR32P13L895', 'LeOperePiuBelle', 56, 0, 12),
471('XXXAAAE77U9999', 'ArteMetaforica', 45, 0, 13),
472('XXXBBBE77U99999', 'ACacciaDiArte', 78, 0, 14);
473
474-- --------------------------------------------------------
475
476--
477-- Struttura della tabella `Partecipazione_Telefonica`
478--
479
480CREATE TABLE `Partecipazione_Telefonica` (
481 `Telefono` int(15) NOT NULL,
482 `Lingua` varchar(15) NOT NULL,
483 `ID_Partecipazione` int(11) PRIMARY KEY,
484 `Mediatore` int(11) NOT NULL,
485 FOREIGN KEY (`ID_Partecipazione`) REFERENCES `Partecipazione` (`ID`),
486 FOREIGN KEY (`Mediatore`) REFERENCES `Mediatore` (`Matricola`)
487) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
488
489--
490-- Dump dei dati per la tabella `Partecipazione_Telefonica`
491--
492
493INSERT INTO `Partecipazione_Telefonica` (`Telefono`, `Lingua`, `ID_Partecipazione`, `Mediatore`) VALUES
494(92999333, 'Italiano', 3, 1100002),
495(92999334, 'Tedesco', 4, 1100002),
496(92999335, 'Francese', 6, 1100004),
497(34567879, 'Italiano', 7, 1100004),
498(34567880, 'Francese', 11, 1100005);
499
500-- --------------------------------------------------------
501
502--
503-- Struttura della tabella `PersonaleInterno`
504--
505
506CREATE TABLE `PersonaleInterno` (
507 `Matricola` int(15) PRIMARY KEY,
508 `Sala` int(15) NOT NULL,
509 `Nome` varchar(15) NOT NULL,
510 `Cognome` varchar(15) NOT NULL,
511 `Telefono` int(15) NOT NULL,
512 FOREIGN KEY (`Sala`) REFERENCES `SalaAste` (`IDSala`)
513) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
514
515--
516-- Dump dei dati per la tabella `PersonaleInterno`
517--
518
519INSERT INTO `PersonaleInterno` (`Matricola`, `Sala`, `Nome`, `Cognome`, `Telefono`) VALUES
520(1100000, 1, 'Mario', 'Rossi', 42991796),
521(1100001, 2, 'Maria', 'Bianchi', 42991797),
522(1100002, 3, 'Giorgio', 'Verdi', 42991798),
523(1100003, 2, 'Paolo', 'Gialli', 42991799),
524(1100004, 3, 'Andrea', 'Bruni', 42991800),
525(1100005, 2, 'Francesco', 'Piersanti', 42991801),
526(1100006, 1, 'Riccardo', 'Pavan', 42991802),
527(1100007, 1, 'Manuele', 'Terzo', 42991803);
528
529-- --------------------------------------------------------
530
531--
532-- Struttura della tabella `Privato`
533--
534
535CREATE TABLE `Privato` (
536 `CF` varchar(15) PRIMARY KEY,
537 `Nome` varchar(15) NOT NULL,
538 `Cognome` varchar(15) NOT NULL,
539 `Telefono` int(15) NOT NULL
540) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
541
542--
543-- Dump dei dati per la tabella `Privato`
544--
545
546INSERT INTO `Privato` (`CF`, `Nome`, `Cognome`, `Telefono`) VALUES
547('134WERTOPASDFBD', 'Rosa', 'Tulipano', 34616567),
548('134WERTOPASDFGH', 'Gabriele', 'Giallo', 34590768),
549('134WERTOPASUIPO', 'Primo', 'Quaderno', 34626567),
550('BVCMNBCXZ123ASD', 'Federica', 'Mango', 38675634),
551('CVBNMLOIUYTR456', 'Virginia', 'Verde', 34212345),
552('NMBHJKIUY789123', 'Renzo', 'Rosso', 34889786),
553('VBG345YUIQWE123', 'Michael', 'Blu', 34656567);
554
555-- --------------------------------------------------------
556
557--
558-- Struttura della tabella `Proposta`
559--
560
561CREATE TABLE `Proposta` (
562 `Asta` varchar(15) NOT NULL,
563 `Lotto` int(15) NOT NULL,
564 `RilancioMinimo` int(15) NOT NULL DEFAULT '50',
565 `BaseAsta` int(15) NOT NULL DEFAULT '500',
566 `ID` int(11) PRIMARY KEY,
567 FOREIGN KEY (`Asta`) REFERENCES `Asta` (`Nome`) ON DELETE NO ACTION ON UPDATE NO ACTION,
568 FOREIGN KEY (`Lotto`) REFERENCES `Lotto` (`Progressivo`) ON DELETE NO ACTION ON UPDATE NO ACTION
569) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
570
571--
572-- Dump dei dati per la tabella `Proposta`
573--
574
575INSERT INTO `Proposta` (`Asta`, `Lotto`, `RilancioMinimo`, `BaseAsta`, `ID`) VALUES
576('ACacciaDiArte', 1, 50, 500, 0),
577('ACacciaDiArte', 2, 100, 1000, 1),
578('ACacciaDiArte', 10, 100, 1000, 2),
579('ACacciaDiArte', 11, 150, 1500, 3),
580('ArteMetaforica', 12, 70, 700, 4),
581('ArtePerTutti', 3, 50, 500, 5),
582('ArtePerTutti', 8, 50, 500, 6),
583('LeBelleArti', 4, 75, 1500, 7),
584('LeOperePiuBelle', 6, 50, 500, 8),
585('OpereUniche', 7, 50, 1200, 9),
586('OpereUniche', 9, 50, 500, 10),
587('QuadriPaesaggio', 5, 50, 600, 11),
588('OpereUniche', 6, 50, 400, 13),
589('QuadriPaesaggio', 6, 50, 300, 14),
590('LeBelleArti', 6, 50, 250, 15);
591
592-- --------------------------------------------------------
593
594--
595-- Struttura della tabella `SalaAste`
596--
597
598CREATE TABLE `SalaAste` (
599 `IDSala` int(15) PRIMARY KEY,
600 `Nome` varchar(15) NOT NULL,
601 `Telefono` int(15) NOT NULL,
602 `NumeroPosti` smallint(15) DEFAULT NULL,
603 `Via` varchar(15) NOT NULL,
604 `Civico` int(15) NOT NULL,
605 `Nazione` varchar(15) NOT NULL,
606 `Citta` varchar(15) NOT NULL,
607 `CAP` int(15) NOT NULL
608) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
609
610--
611-- Dump dei dati per la tabella `SalaAste`
612--
613
614INSERT INTO `SalaAste` (`IDSala`, `Nome`, `Telefono`, `NumeroPosti`, `Via`, `Civico`, `Nazione`, `Citta`, `CAP`) VALUES
615(1, 'Sole', 44123678, 150, 'Battisti', 1, 'Italy', 'Roma', 10010),
616(2, 'Fortuna', 34216758, 100, 'Mazzini', 1, 'Italy', 'Padova', 35131),
617(3, 'BattiIlTempo', 34216798, 45, 'Roma', 7, 'Italy', 'Bologna', 40121);
618
619-- --------------------------------------------------------
620
621--
622-- Struttura della tabella `Scultura`
623--
624
625CREATE TABLE `Scultura` (
626 `IDOpera` int(15) PRIMARY KEY,
627 `Peso` int(15) NOT NULL,
628 `Altezza` decimal(5,2) NOT NULL,
629 `Materiale` varchar(15) NOT NULL DEFAULT 'Marmo',
630 FOREIGN KEY (`IDOpera`) REFERENCES `Opera` (`IDOpera`)
631) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
632
633--
634-- Dump dei dati per la tabella `Scultura`
635--
636
637INSERT INTO `Scultura` (`IDOpera`, `Peso`, `Altezza`, `Materiale`) VALUES
638(6, 89, '100.00', 'Granito bianaco'),
639(7, 98, '100.00', 'Marmo '),
640(9, 70, '99.00', 'MarmoCarrara'),
641(10, 170, '99.00', 'MarmoCalacatta');
642
643-- --------------------------------------------------------
644
645--
646-- Struttura della tabella `Ufficio`
647--
648
649CREATE TABLE `Ufficio` (
650 `IDUfficio` int(15) PRIMARY KEY,
651 `Telefono` int(15) NOT NULL,
652 `Civico` int(15) NOT NULL,
653 `Nazione` varchar(15) NOT NULL,
654 `Citta` varchar(15) NOT NULL,
655 `CAP` int(15) NOT NULL,
656 `Via` varchar(15) NOT NULL
657) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
658
659--
660-- Dump dei dati per la tabella `Ufficio`
661--
662
663INSERT INTO `Ufficio` (`IDUfficio`, `Telefono`, `Civico`, `Nazione`, `Citta`, `CAP`, `Via`) VALUES
664(1, 345123478, 1, 'Italy', 'Roma', 78907, 'Del Capitano'),
665(2, 345123479, 2, 'Germany', 'Burgenstraße', 45768, 'Italia'),
666(3, 345123480, 3, 'Francia', 'Parigi', 32415, 'CharlesDeGaulle');
667
668-- --------------------------------------------------------
669
670--
671-- Struttura della tabella `Utente`
672--
673
674CREATE TABLE `Utente` (
675 `CF` varchar(15) PRIMARY KEY,
676 `Nome` varchar(15) NOT NULL,
677 `Cognome` varchar(15) NOT NULL
678) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
679
680--
681-- Dump dei dati per la tabella `Utente`
682--
683
684INSERT INTO `Utente` (`CF`, `Nome`, `Cognome`) VALUES
685('AAABBBE55U99999', 'Giorgio ', 'Verdi'),
686('AAACCCE55U99999', 'Anna', 'Clara'),
687('APNSTR32P13L895', 'Astrid', 'Uber'),
688('GHTIRG9943P5B6T', 'Daniele', 'Spigolon'),
689('RSNRLL67P21L58F', 'Sandra', 'Mattei'),
690('SPGXXX99P16D442', 'Mario', 'Rossi'),
691('TNNSTR56P13L895', 'Ester', 'Magalli'),
692('TSNSTR32P13L895', 'Alberto', 'Morise'),
693('XXXAAAE77U9999', 'Maria', 'Rossi'),
694('XXXBBBE77U99999', 'Barbara', 'Finestra'),
695('XXXJJJE44U9999', 'Libero', 'Bianchi');
696
697-- --------------------------------------------------------
698
699--
700-- Struttura della tabella `Valutatore`
701--
702
703CREATE TABLE `Valutatore` (
704 `Matricola` int(15) PRIMARY KEY,
705 `AnniDiEsperienza` int(15) NOT NULL DEFAULT '3',
706 FOREIGN KEY (`Matricola`) REFERENCES `Dipendente` (`Matricola`)
707) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
708
709--
710-- Dump dei dati per la tabella `Valutatore`
711--
712
713INSERT INTO `Valutatore` (`Matricola`, `AnniDiEsperienza`) VALUES
714(1193290, 20),
715(1193291, 30),
716(1193295, 10);
717
718--Query SQL
719
720DROP VIEW IF EXISTS VincitaProposta; /* Se esiste già elimino la vista */
721
722/*Per ogni utente, ritornare la massima differenza tra il prezzo base e finale tra i lotti da lui vinti.
723Se un utente non ha mai vinto un lotto, ritornare -1. Se ci sono più lotti vinti, con la medesima differenza, riportarli tutti. */
724
725/* Per ogni lotto venduto, si ottiene: proposta, lotto, asta, vincitore e valore offerta finale */
726/* NOTA: In diverse delle query successive viene utilizzata questa vista */
727
728CREATE VIEW VincitaProposta AS
729 SELECT P.Asta, O.Proposta, P.Lotto, O.Utente AS Vincitore, O.Valore
730 FROM Offerta O JOIN Proposta P ON O.Proposta = P.ID
731 WHERE O.Valore = (SELECT MAX(Valore) FROM Offerta WHERE Proposta = O.Proposta);
732
733/* Per ogni utente che ha vinto almeno un lotto, vengono mostrati: il lotto da lui vinto con rialzo massimo */
734SELECT V.Vincitore AS Utente, V.Lotto, V.Valore - P.BaseAsta AS Differenza
735FROM VincitaProposta V JOIN Proposta P ON V.Proposta = P.ID
736WHERE V.Valore - P.BaseAsta >= ALL (
737/*Seleziono tutti i valori Valore-BaseAsta di lotti vinti dal vincitore corrente */
738 SELECT V1.Valore - P1.BaseAsta
739 FROM VincitaProposta V1 JOIN Proposta P1 ON V1.Proposta = P1.ID
740 WHERE V1.Vincitore = V.Vincitore
741 )
742UNION
743/* Vengono selezionati tutti gli utenti che non hanno mai vinto un lotto */
744SELECT CF AS Utente, 0 AS Lotto, -1 AS Differenza
745FROM Utente U
746WHERE NOT EXISTS ( SELECT Lotto FROM VincitaProposta WHERE Vincitore = U.CF) ;
747
748
749/*Query che ritorna i primi 10 utenti ordinati per CF che il mese scorso hanno vinto all’asta solo lotti dello stesso tipo,
750 o che non hanno mai vinto un lotto, il mese scorso*/
751
752DROP VIEW IF EXISTS LAST_MONTH_WINNERS; /* Se la vista era già presente, viene eliminata */
753/* Viene creata una vista per ottenere le informazioni degli utenti che hanno vinto almeno un lotto il mese scorso */
754CREATE VIEW LAST_MONTH_WINNERS AS
755 SELECT DISTINCT VP.Vincitore, VP.Lotto
756 FROM VincitaProposta VP JOIN Asta A ON VP.Asta = A.Nome
757 WHERE EXTRACT(MONTH FROM A.Data) = EXTRACT(MONTH FROM DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND EXTRACT(YEAR FROM A.Data) = EXTRACT(YEAR FROM DATE_SUB(CURDATE(), INTERVAL 1 MONTH));
758
759/* Vengono selezionati gli utenti che il mese scorso non hanno vinto alcun lotto */
760SELECT CF AS Utente, "Nessuna vincita" AS Motivazione
761FROM Utente U
762WHERE U.CF NOT IN ( SELECT DISTINCT Vincitore FROM LAST_MONTH_WINNERS )
763UNION
764/* Vengono selezionati gli utenti che il mese scorso hanno vinto opere di un solo tipo */
765SELECT CF AS Utente, "Unico tipo di vincita"
766FROM Utente U
767WHERE U.CF IN (SELECT DISTINCT Vincitore FROM LAST_MONTH_WINNERS)
768 AND NOT EXISTS ( /* Vengono selezionate tutte le coppie di lotti vinti il mese scorso dall’utente */
769 SELECT * /* considerato che fanno riferimento a opere di tipo diverso */
770 FROM (LAST_MONTH_WINNERS LMW1 JOIN Lotto L1 ON (LMW1.Lotto = L1.Progressivo)
771JOIN Opera O1 ON L1.IDOpera = O1.IDOpera) JOIN (LAST_MONTH_WINNERS LMW2 JOIN Lotto L2 ON (LMW2.Lotto = L2.Progressivo) JOIN Opera O2 ON L2.IDOpera = O2.IDOpera) ON LMW1.Vincitore = LMW2.Vincitore
772 WHERE LMW1.Vincitore = U.CF AND O1.Tipo <> O2.Tipo
773)
774ORDER BY Utente ASC /* Ordinamento in base a CF dell’utente */
775LIMIT 10 ; /* Vengono considerati solo i primi 10 risultati */
776
777
778/*Query che ritorna l’identificativo ( CF) dei privati non fidati. Dove un privato non fidato è un privato che: ha portato 2 o più lotti non autentici o
779che ha portato uno o più lotti a cui corrispondono 4 o più proposte ( e che quindi non sono stati venduti almeno 3 volte).*/
780
781SELECT P.CF
782FROM Privato P
783WHERE P.CF IN (
784 /* Utenti privati che hanno portato 2 o più lotti non autentici */
785 SELECT L1.Privato
786 FROM Lotto L1 JOIN DocumentoDiAutenticita D ON L1.Progressivo = D.Lotto
787 WHERE L1.Privato IS NOT NULL AND D.Autenticita = 0
788 GROUP BY L1.Privato
789 HAVING COUNT(L1.Progressivo) >= 2
790)
791OR EXISTS (
792 /* Lotti portati dall’utente considerato a cui corrispondono 4 o più proposte */
793 SELECT L1.Progressivo
794 FROM Lotto L1 JOIN Proposta P1 ON L1.Progressivo = P1.Lotto
795 WHERE L1.Privato = P.CF
796 GROUP BY L1.Progressivo
797 HAVING COUNT(P1.Asta) >= 4
798)
799
800/*Per ogni mese in cui ci sono state aste, trovare il battitore che negli ultimi due anni
801(anno corrente e anno scorso) ha venduto opere dal valore complessivo più alto.*/
802
803DROP VIEW IF EXISTS SOLD_PER_MONTH; /* se la vista esiste già, viene rimossa */
804/* creo una vista che associa ad ogni battitore la somma venduta ogni mese negli ultimi 2 anni */
805CREATE VIEW SOLD_PER_MONTH AS
806 SELECT A.Battitore, EXTRACT(MONTH FROM A.Data) AS Mese, SUM(VP.Valore) AS TotaleVenduto
807 FROM VincitaProposta VP JOIN Asta A ON (VP.Asta = A.Nome)
808 WHERE EXTRACT(YEAR FROM A.Data) = EXTRACT(YEAR FROM CURDATE()) OR EXTRACT(YEAR FROM A.Data) = EXTRACT(YEAR FROM DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
809 GROUP BY A.Battitore, EXTRACT(MONTH FROM A.Data);
810
811SELECT PI.Nome AS NomeBattitore, PI.Cognome AS CognomeBattitore, SPM.TotaleVenduto, CASE SPM.Mese
812 when 1 then 'GENNAIO' when 2 then 'FEBBRAIO' when 3 then 'MARZO'
813 when 4 then 'APRILE' when 5 then 'MAGGIO' when 6 then 'GIUGNO'
814 when 7 then 'LUGLIO' when 8 then 'AGOSTO' when 9 then 'SETTEMBRE'
815 when 10 then 'OTTOBRE' when 11 then 'NOVEMBRE' when 12 then 'DICEMBRE'
816END AS Mese
817
818FROM SOLD_PER_MONTH AS SPM JOIN PersonaleInterno PI ON SPM.Battitore = PI.Matricola
819WHERE SPM.TotaleVenduto >= ALL (
820/* seleziono il massimo venduto il mese considerato */
821 SELECT TotaleVenduto
822 FROM SOLD_PER_MONTH
823 WHERE Mese = SPM.Mese
824)
825ORDER BY SPM.Mese
826
827/*I primi 5 artisti le cui opere sono state vendute alle aste che l’anno scorso hanno
828avuto in media il numero più alto di partecipanti.*/
829
830DROP VIEW IF EXISTS PARTECIPAZIONE_ASTE;
831/* Creo una vista che per ogni asta indica il numero di partecipanti alle aste dell’anno scorso */
832CREATE VIEW PARTECIPAZIONE_ASTE AS
833 SELECT Asta, COUNT(DISTINCT Utente) AS Partecipanti
834FROM Partecipazione P JOIN Asta A ON P.Asta = A.Nome
835WHERE EXTRACT(YEAR FROM A.Data) = EXTRACT(YEAR FROM DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
836GROUP BY Asta;
837
838DROP VIEW IF EXISTS PARTECIPAZIONE_ARTISTA;
839/* Creo una vista che per ogni artista indica il numero medio di partecipanti alle proprie aste */
840CREATE VIEW PARTECIPAZIONE_ARTISTA AS
841 SELECT C.ID_Artista AS Artista, AVG(PAR.Partecipanti) AS MediaPartecipanti
842 FROM PARTECIPAZIONE_ASTE PAR JOIN Proposta P ON P.Asta = PAR.Asta
843 JOIN Lotto L ON P.Lotto = L.Progressivo
844 JOIN Creazione C ON C.Opera = L.IDOpera
845GROUP BY C.ID_Artista ;
846
847/* Seleziono i primi 5 artisti con numero medio di partecipanti massimo l’anno scorso */
848SELECT AR.Nome, AR.Cognome, PAR.MediaPartecipanti
849FROM PARTECIPAZIONE_ARTISTA PAR JOIN Artista AR ON PAR.Artista = AR.IDArtista
850ORDER BY PAR.MediaPartecipanti DESC
851LIMIT 5;
852
853/*
854Trovare per ogni tipologia di cliente (privato/museo) quello che in media porta i lotti nello stato migliore,
855se per una stessa categoria sono presenti più clienti con la stessa media, riportarli tutti.*/
856
857DROP VIEW IF EXISTS Conservazione;
858/* associo ad ogni cliente la media dello stato di conservazione dei lotti da lui portati */
859CREATE VIEW Conservazione AS
860 SELECT Coalesce(L.Privato, L.Museo) AS Cliente,
861 AVG(DOC.StatoConservazione) AS MediaConservazione,
862 CASE WHEN L.Privato IS NULL THEN "Museo" ELSE "Privato" END AS "TipologiaCliente"
863 FROM DocumentoDiAutenticita DOC JOIN Lotto L ON DOC.Lotto = L.Progressivo
864 GROUP BY Coalesce(L.Privato, L.Museo);
865
866/* miglior cliente per categoria */
867SELECT C.*
868FROM Conservazione C
869WHERE C.MediaConservazione >= ALL
870( SELECT MediaConservazione
871 FROM Conservazione
872 WHERE TipologiaCliente = C.TipologiaCliente );
873
874--INDICIZZAZIONE
875
876CREATE INDEX IndiceAsta ON Asta(Data);
877
878/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
879/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
880/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;