· 6 years ago · Jan 07, 2020, 04:10 PM
1CREATE DATABASE IF NOT EXISTS AXMARK_WIDENIUS_HOTEL;
2
3SET FOREIGN_KEY_CHECKS=0;
4
5
6DROP TABLE IF EXISTS Tipo_Camera;
7CREATE TABLE Tipo_Camera (
8 ID TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
9 nome VARCHAR(50) NOT NULL,
10 nLetti TINYINT UNSIGNED NOT NULL,
11 descrizione VARCHAR(200)
12) ENGINE=InnoDB;
13
14DROP TABLE IF EXISTS Camera;
15CREATE TABLE Camera (
16 numero SMALLINT UNSIGNED PRIMARY KEY,
17 piano TINYINT UNSIGNED NOT NULL,
18 animaliAmmessi TINYINT(1) UNSIGNED NOT NULL,
19 tipoCamera TINYINT UNSIGNED NOT NULL,
20 FOREIGN KEY (tipoCamera) REFERENCES Tipo_Camera (ID)
21 ON UPDATE CASCADE
22) ENGINE=InnoDB;
23
24DROP TABLE IF EXISTS Inserviente;
25CREATE TABLE Inserviente (
26 CF CHAR(16) PRIMARY KEY,
27 nome VARCHAR(20) NOT NULL,
28 cognome VARCHAR(20) NOT NULL,
29 via VARCHAR(40) NOT NULL,
30 civico VARCHAR(8) NOT NULL,
31 citta VARCHAR(30) NOT NULL,
32 nazione VARCHAR(20) NOT NULL,
33 cellulare VARCHAR(15) NOT NULL
34) ENGINE=InnoDB;
35
36DROP TABLE IF EXISTS Impiegato;
37CREATE TABLE Impiegato (
38 CF CHAR(16) PRIMARY KEY,
39 nome VARCHAR(20) NOT NULL,
40 cognome VARCHAR(20) NOT NULL,
41 via VARCHAR(40) NOT NULL,
42 civico VARCHAR(8) NOT NULL,
43 citta VARCHAR(30) NOT NULL,
44 nazione VARCHAR(20) NOT NULL,
45 cellulare VARCHAR(15) NOT NULL
46) ENGINE=InnoDB;
47
48DROP TABLE IF EXISTS Servizio_In_Camera;
49CREATE TABLE Servizio_In_Camera (
50 ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
51 inserviente CHAR(16) NOT NULL,
52 camera SMALLINT UNSIGNED NOT NULL,
53 dataOra TIMESTAMP NOT NULL,
54 UNIQUE (camera, dataOra),
55 FOREIGN KEY (inserviente) REFERENCES Inserviente (CF)
56 ON UPDATE CASCADE,
57 FOREIGN KEY (camera) REFERENCES Camera (numero)
58 ON UPDATE CASCADE
59) ENGINE=InnoDB;
60
61DROP TABLE IF EXISTS Tipo_Articolo_Minibar;
62CREATE TABLE Tipo_Articolo_Minibar (
63 ID SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
64 nome VARCHAR(50) NOT NULL,
65 prezzoFisso DECIMAL(9,2) NOT NULL,
66 UNIQUE(nome)
67) ENGINE=InnoDB;
68
69DROP TABLE IF EXISTS Guasto;
70CREATE TABLE Guasto (
71 ID INT UNSIGNED PRIMARY KEY,
72 camera SMALLINT UNSIGNED NOT NULL,
73 note VARCHAR(200),
74 segnalatoDa CHAR(16) NOT NULL,
75 FOREIGN KEY (camera) REFERENCES Camera (numero)
76 ON UPDATE CASCADE,
77 FOREIGN KEY (segnalatoDa) REFERENCES Inserviente (CF)
78 ON UPDATE CASCADE
79) ENGINE=InnoDB;
80
81DROP TABLE IF EXISTS Indisponibilita;
82CREATE TABLE Indisponibilita (
83 guasto INT UNSIGNED PRIMARY KEY,
84 inizio TIMESTAMP NOT NULL,
85 termine TIMESTAMP,
86 FOREIGN KEY (guasto) REFERENCES Guasto (ID)
87 ON UPDATE CASCADE
88) ENGINE=InnoDB;
89
90DROP TABLE IF EXISTS Azienda;
91CREATE TABLE Azienda (
92 partitaiva CHAR(11) PRIMARY KEY,
93 ragioneSociale VARCHAR(30) NOT NULL,
94 ambito VARCHAR(30) NOT NULL,
95 formaSocietaria VARCHAR(20) NOT NULL,
96 cellulare VARCHAR(15) NOT NULL
97) ENGINE=InnoDB;
98
99DROP TABLE IF EXISTS Riparazione_Interna;
100CREATE TABLE Riparazione_Interna (
101 guasto INT UNSIGNED NOT NULL,
102 inserviente CHAR(16) NOT NULL,
103 data DATE NOT NULL,
104 PRIMARY KEY (guasto, inserviente),
105 FOREIGN KEY (guasto) REFERENCES Guasto (ID)
106 ON UPDATE CASCADE,
107 FOREIGN KEY (inserviente) REFERENCES Inserviente (CF)
108 ON UPDATE CASCADE
109) ENGINE=InnoDB;
110
111DROP TABLE IF EXISTS Riparazione_Esterna;
112CREATE TABLE Riparazione_Esterna (
113 guasto INT UNSIGNED NOT NULL,
114 azienda CHAR(11) NOT NULL,
115 data DATE NOT NULL,
116 PRIMARY KEY (guasto, azienda),
117 FOREIGN KEY (guasto) REFERENCES Guasto (ID)
118 ON UPDATE CASCADE,
119 FOREIGN KEY (azienda) REFERENCES Azienda (partitaIVA)
120 ON UPDATE CASCADE
121) ENGINE=InnoDB;
122
123DROP TABLE IF EXISTS Prenotazione;
124CREATE TABLE Prenotazione (
125 ID INT UNSIGNED PRIMARY KEY,
126 data DATE NOT NULL,
127 confermata TINYINT(1) DEFAULT 0
128) ENGINE=InnoDB;
129
130DROP TABLE IF EXISTS Ospite;
131CREATE TABLE Ospite (
132
133 CF CHAR(16) PRIMARY KEY,
134 cognome VARCHAR(20) NOT NULL,
135 nome VARCHAR(20) NOT NULL,
136 via VARCHAR(40) NOT NULL,
137 civico VARCHAR(8) NOT NULL,
138 citta VARCHAR(30) NOT NULL,
139 nazione VARCHAR(20) NOT NULL,
140 nazionalita VARCHAR(20) NOT NULL,
141 cellulare VARCHAR(15)
142) ENGINE=InnoDB;
143
144
145DROP TABLE IF EXISTS Fattura;
146CREATE TABLE Fattura (
147 numero INT UNSIGNED PRIMARY KEY,
148 data DATE NOT NULL DEFAULT CURRENT_DATE,
149 registrataDa CHAR(16) NOT NULL,
150 totale DECIMAL(9,2),
151 FOREIGN KEY (registrataDa) REFERENCES Impiegato (CF)
152 ON UPDATE CASCADE
153) ENGINE=InnoDB;
154
155DROP TABLE IF EXISTS Pagamento;
156CREATE TABLE Pagamento (
157 ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
158 fattura INT UNSIGNED NOT NULL,
159 metodo VARCHAR(20) NOT NULL,
160 data DATE NOT NULL,
161 registratoDa CHAR(16) NOT NULL,
162 FOREIGN KEY (registratoDa) REFERENCES Impiegato (CF)
163 ON UPDATE CASCADE,
164 FOREIGN KEY (fattura) REFERENCES Fattura (numero)
165 ON UPDATE CASCADE
166) ENGINE=InnoDB;
167
168
169DROP TABLE IF EXISTS Prenotazione_Ospite;
170CREATE TABLE Prenotazione_Ospite (
171prenotazione INT UNSIGNED NOT NULL PRIMARY KEY,
172ospite CHAR(16) NOT NULL,
173FOREIGN KEY (ospite) REFERENCES ospite(CF)
174ON UPDATE CASCADE,
175FOREIGN KEY (prenotazione) REFERENCES prenotazione(ID)
176ON UPDATE CASCADE
177) ENGINE = InnoDB;
178
179DROP TABLE IF EXISTS Prenotazione_Azienda;
180CREATE TABLE Prenotazione_Azienda (
181prenotazione INT UNSIGNED NOT NULL PRIMARY KEY,
182azienda CHAR(11) NOT NULL,
183FOREIGN KEY (azienda) REFERENCES Azienda (partitaIVA)
184ON UPDATE CASCADE,
185FOREIGN KEY (prenotazione) REFERENCES prenotazione(ID)
186ON UPDATE CASCADE
187) ENGINE=InnoDB;
188
189DROP TABLE IF EXISTS Prenotazione_Camera;
190CREATE TABLE Prenotazione_Camera (
191 ID INT UNSIGNED PRIMARY KEY,
192 prenotazione INT UNSIGNED NOT NULL,
193 camera SMALLINT UNSIGNED NOT NULL,
194 inizio DATE NOT NULL,
195 fine DATE NOT NULL,
196 prezzo DECIMAL(9,2) NOT NULL,
197 FOREIGN KEY (prenotazione) REFERENCES prenotazione(ID)
198 ON UPDATE CASCADE ON DELETE CASCADE,
199 FOREIGN KEY (camera) REFERENCES Camera (numero)
200 ON UPDATE CASCADE,
201 UNIQUE (prenotazione, camera)
202) ENGINE=InnoDB;
203
204
205DROP TABLE IF EXISTS Consumazione_Minibar;
206CREATE TABLE Consumazione_Minibar (
207 tipoArticolo SMALLINT UNSIGNED NOT NULL,
208 servizioInCamera INT UNSIGNED NOT NULL,
209 quantita INT UNSIGNED NOT NULL DEFAULT 1,
210 fattura INT UNSIGNED NOT NULL,
211 PRIMARY KEY (tipoArticolo, servizioInCamera),
212 FOREIGN KEY (tipoArticolo) REFERENCES tipo_articolo_minibar (ID)
213 ON UPDATE CASCADE,
214 FOREIGN KEY (servizioInCamera) REFERENCES Servizio_In_Camera (ID)
215 ON UPDATE CASCADE,
216 FOREIGN KEY (fattura) REFERENCES Fattura (numero)
217 ON UPDATE CASCADE
218) ENGINE=InnoDB;
219
220DROP TABLE IF EXISTS Intestazione_Ospite;
221CREATE TABLE Intestazione_Ospite (
222 ospite CHAR(16) NOT NULL,
223 fattura INT UNSIGNED NOT NULL PRIMARY KEY,
224 FOREIGN KEY (ospite) REFERENCES Ospite (CF)
225 ON UPDATE CASCADE,
226 FOREIGN KEY (fattura) REFERENCES Fattura (numero)
227 ON UPDATE CASCADE
228) ENGINE=InnoDB;
229
230DROP TABLE IF EXISTS Intestazione_Azienda;
231CREATE TABLE Intestazione_Azienda (
232 azienda CHAR(11) NOT NULL,
233 fattura INT UNSIGNED NOT NULL PRIMARY KEY,
234 FOREIGN KEY (azienda) REFERENCES Azienda (partitaIVA)
235 ON UPDATE CASCADE,
236 FOREIGN KEY (fattura) REFERENCES Fattura (numero)
237 ON UPDATE CASCADE
238) ENGINE=InnoDB;
239
240
241DROP TABLE IF EXISTS Soggiorno;
242CREATE TABLE Soggiorno (
243 ospite CHAR(16) NOT NULL,
244 prenotazioneCamera INT UNSIGNED NOT NULL,
245 PRIMARY KEY (ospite, prenotazioneCamera),
246 FOREIGN KEY (ospite) REFERENCES Ospite (CF),
247 ON UPDATE CASCADE,
248 FOREIGN KEY (prenotazioneCamera) REFERENCES Prenotazione_Camera (ID)
249 ON UPDATE CASCADE ON DELETE CASCADE
250 #cancella il soggiorno a seguito della cancellazione della relativa prenotazione camera (probabilmente dovuta a sua volta dall eliminazione della relativa prenotazione)
251) ENGINE=InnoDB;
252
253DROP TABLE IF EXISTS Guasto_Addebitabile;
254CREATE TABLE Guasto_Addebitabile (
255 guasto INT UNSIGNED PRIMARY KEY,
256 fattura INT UNSIGNED NOT NULL,
257 FOREIGN KEY (guasto) REFERENCES Guasto (ID)
258 ON UPDATE CASCADE,
259 FOREIGN KEY (fattura) REFERENCES Fattura (numero)
260 ON UPDATE CASCADE
261) ENGINE=InnoDB;
262
263SET FOREIGN_KEY_CHECKS=1;
264
265
266
267
268
269
270
271
272#Elenco ordinato delle camere, con indicazione della tipologia e del piano a cui appartengono
273SELECT C.numero,TC.nome,C.piano
274FROM Tipo_Camera TC, Camera C
275WHERE C.tipoCamera=TC.ID
276ORDER BY C.numero;
277
278#Elenco ordinato delle camere prenotate dal 1/2/2019 al 8/2/2019
279SELECT DISTINCT PC.camera
280FROM Prenotazione_Camera PC
281WHERE ('2019-02-01'<=PC.inizio AND PC.inizio<'2019-02-08') OR ('2019-02-01'<PC.fine AND PC.fine<'2019-02-08')
282ORDER BY PC.camera;
283
284#Elenco ordinato delle camere indisponibili dal 1/2/2019 al 8/2/2019
285SELECT DISTINCT G.camera
286FROM Guasto G, Indisponibilita I
287WHERE I.guasto=G.ID AND ('2019-02-01'<=I.inizio AND I.inizio<'2019-02-08') OR ('2019-02-01'<I.termine AND I.termine<'2019-02-08')
288ORDER BY G.camera;
289
290#***Elenco ordinato delle camere disponibili in un periodo (per es. dal 1/2/2019 al 8/2/2019) suddivise per tipologia di stanza***
291#con x posti letto e che ammette animali
292SELECT C.numero,TC.nome
293FROM Tipo_Camera TC
294INNER JOIN Camera C
295ON C.tipoCamera=TC.ID
296WHERE C.numero NOT IN
297(SELECT PC.camera
298FROM Prenotazione_Camera PC
299WHERE ('2019-02-01'<=PC.inizio AND PC.inizio<'2019-02-08') OR ('2019-02-01'<PC.fine AND PC.fine<'2019-02-08'))
300
301AND C.numero NOT IN
302(SELECT G.camera
303FROM Guasto G
304 INNER JOIN Indisponibilita I
305 ON I.guasto = G.ID
306WHERE('2019-02-01'<=I.inizio AND I.inizio<'2019-02-08') OR ('2019-02-01'<I.termine AND I.termine<'2019-02-08'))
307WHERE TC.nLetti = 2 AND C.animaliAmmessi = 1
308ORDER BY C.numero;
309
310#Elenco degli articoli di minibar suddivisi per tipo e per quantita consumata da quando l hotel ha aperto
311SELECT TAM.ID,TAM.nome AS prodotto, SUM(CM.quantita) AS pezzi
312FROM Consumazione_Minibar CM, Tipo_Articolo_Minibar TAM
313WHERE CM.tipoArticolo=TAM.ID
314GROUP BY TAM.nome
315ORDER BY ID;
316
317#***Elenco degli articoli di minibar suddivisi per tipo e per quantita consumata in un periodo(es. dal 01/02/2019 al 28/02/2019)***
318SELECT TAM.ID AS ID,TAM.nome AS prodotto, SUM(CM.quantita) AS pezzi
319FROM Consumazione_Minibar CM, Tipo_Articolo_Minibar TAM, Servizio_In_Camera SIC
320WHERE CM.tipoArticolo=TAM.ID AND CM.servizioInCamera=SIC.ID AND SIC.dataOra>'2019-01-31' AND SIC.dataOra<'2019-03-01'
321GROUP BY TAM.ID
322ORDER BY ID;
323
324#***Elenco degli articoli di minibar suddivisi per tipo e per quantita consumata in un periodo(es. dal 01/02/2019 al 28/02/2019)***
325SELECT T.ID AS ID,T.nome AS prodotto, ISNULL(SUM(C.quantita) AS pezzi, 0)
326FROM Tipo_Articolo_Minibar T
327 JOIN (Consumazione_Minibar C
328 INNER JOIN Servizio_In_Camera S
329 ON C.servizioInCamera=S.ID)
330 ON C.tipoArticolo=T.ID
331WHERE S.dataOra>'2019-01-31' AND S.dataOra<'2019-03-01'
332GROUP BY T.ID
333ORDER BY T.ID;
334
335
336#Elenco dei guasti in carico a ditte esterne con i relativi tempi di riparazione ordinati in modo decrescente
337SELECT A.ragioneSociale AS azienda, A.ambito AS settore,G.note AS guasto, DATEDIFF(RE.data,I.inizio) AS giorni_riparazione
338FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
339WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda
340ORDER BY giorni_riparazione DESC;
341
342
343#***Elenco del tempo medio di riparazione dei guasti delle ditte esterne in ordine decrescente***
344SELECT A.ragioneSociale AS azienda, A.ambito AS settore, AVG(DATEDIFF(RE.data,I.inizio)) AS giorni_riparazione
345FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
346WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda
347GROUP BY A.partitaiva
348HAVING COUNT
349ORDER BY giorni_riparazione DESC;
350
351#***Elenco del tempo medio di ripristino dei guasti che comportano indisponibilita delle ditte esterne in ordine decrescente***
352SELECT A.ragioneSociale AS azienda, A.ambito AS settore, AVG(DATEDIFF(RE.data,I.inizio)) AS giorni_riparazione
353FROM Indisponibilita I
354 INNER JOIN (Guasto G
355 INNER JOIN (Riparazione_Esterna R
356 INNER JOIN Azienda A
357 ON R.azienda = A.partitaiva)
358 ON R.guasto=G.ID)
359 ON I.guasto=G.ID
360GROUP BY azienda, settore
361ORDER BY giorni_riparazione DESC;
362
363#tempo medio di riparazione dei guasti delle ditte esterne
364SELECT AVG(DATEDIFF(RE.data,I.inizio)) AS tempo_medio_riparazione
365FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
366WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda;
367
368#Eliminazione delle prenotazioni non confermate dopo 7 giorni
369#DA COMPLETARE
370DELETE
371FROM Prenotazione
372WHERE confermata=0 AND ( NOW()> (data+7) );
373
374#Fattura mensile di un azienda
375#DA COMPLETARE
376
377#Turni di servizio in camera settimanale: da un dato giorno(es.01/01/2019) ai successivi 7 giorni
378
379SELECT I.cognome, I.nome, DATE(S1.dataOra) AS data, TIME(MIN(S1.dataOra)) AS inizioTurno, TIME(MAX(S1.dataOra)) AS fineTurno
380FROM Servizio_In_Camera S1, Inserviente I
381WHERE S1.dataOra>'2018-12-31' AND S1.dataOra<'2019-01-08' AND S1.inserviente=I.CF
382GROUP BY DATE(S1.dataOra), S1.inserviente
383ORDER BY I.cognome, data;