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