· 6 years ago · Jan 07, 2020, 03:24 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(7,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, Camera C
294WHERE C.tipoCamera=TC.ID AND C.numero NOT IN
295
296(SELECT PC.camera
297FROM Prenotazione_Camera PC
298WHERE ('2019-02-01'<=PC.inizio AND PC.inizio<'2019-02-08') OR ('2019-02-01'<PC.fine AND PC.fine<'2019-02-08'))
299
300AND C.numero NOT IN
301(SELECT G.camera
302FROM Guasto G, Indisponibilita I
303WHERE 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'))
304
305ORDER BY C.numero;
306
307#Elenco degli articoli di minibar suddivisi per tipo e per quantita consumata da quando l hotel ha aperto
308SELECT TAM.ID,TAM.nome AS prodotto, SUM(CM.quantita) AS pezzi
309FROM Consumazione_Minibar CM, Tipo_Articolo_Minibar TAM
310WHERE CM.tipoArticolo=TAM.ID
311GROUP BY TAM.nome
312ORDER BY ID;
313
314#***Elenco degli articoli di minibar suddivisi per tipo e per quantita consumata in un periodo(es. dal 01/02/2019 al 28/02/2019)***
315SELECT TAM.ID,TAM.nome AS prodotto, SUM(CM.quantita) AS pezzi
316FROM Consumazione_Minibar CM, Tipo_Articolo_Minibar TAM, Servizio_In_Camera SIC
317WHERE CM.tipoArticolo=TAM.ID AND CM.servizioInCamera=SIC.ID AND SIC.dataOra>'2019-01-31' AND SIC.dataOra<'2019-03-01'
318GROUP BY TAM.ID
319ORDER BY ID;
320
321#Elenca dei guasti in carico a ditte esterne con i relativi tempi di riparazione ordinati in modo decrescente
322SELECT A.ragioneSociale AS azienda, A.ambito AS settore,G.note AS guasto, DATEDIFF(RE.data,I.inizio) AS giorni_riparazione
323FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
324WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda
325ORDER BY giorni_riparazione DESC;
326
327
328#***Elenco del tempo medio di riparazione dei guasti delle ditte esterne in ordine decrescente***
329SELECT A.ragioneSociale AS azienda, A.ambito AS settore, AVG(DATEDIFF(RE.data,I.inizio)) AS giorni_riparazione
330FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
331WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda
332GROUP BY A.partitaiva
333ORDER BY giorni_riparazione DESC;
334
335#tempo medio di riparazione dei guasti delle ditte esterne
336SELECT AVG(DATEDIFF(RE.data,I.inizio)) AS tempo_medio_riparazione
337FROM Guasto G, Riparazione_Esterna RE,Indisponibilita I, Azienda A
338WHERE G.ID=RE.guasto AND I.guasto=G.ID AND A.partitaiva=RE.azienda;
339
340#Eliminazione delle prenotazioni non confermate dopo 7 giorni
341#DA COMPLETARE
342DELETE
343FROM Prenotazione
344WHERE confermata=0 AND ( NOW()> (data+7) );
345
346#Fattura mensile di un azienda
347#DA COMPLETARE
348
349#Turni di servizio in camera settimanale: da un dato giorno(es.01/01/2019) ai successivi 7 giorni
350
351SELECT I.cognome, I.nome, DATE(S1.dataOra) AS data, TIME(MIN(S1.dataOra)) AS inizioTurno, TIME(MAX(S1.dataOra)) AS fineTurno
352FROM Servizio_In_Camera S1, Inserviente I
353WHERE S1.dataOra>'2018-12-31' AND S1.dataOra<'2019-01-08' AND S1.inserviente=I.CF
354GROUP BY DATE(S1.dataOra), S1.inserviente
355ORDER BY I.cognome, data;