SQLize | PHPize | SQLtest

/* ----------------------------------------------------------- DOMINI ----------------------------------------------------------- */ CREATE TABLE Lingua ( cod_lingua CHAR(5) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_lingua) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE LivelloTicket ( cod_livello CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_livello) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE StatoTicket ( cod_stato CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_stato) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE AttivitaCodificata ( cod_attivita CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_attivita) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE EsitoIntervento ( cod_esito CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_esito) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE VotoFeedback ( cod_voto CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_voto) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* ----------------------------------------------------------- ANAGRAFICHE ----------------------------------------------------------- */ CREATE TABLE Cliente ( id_cliente BIGINT NOT NULL AUTO_INCREMENT, Nome VARCHAR(50), Cognome VARCHAR(50), DataDiNascita DATE, Email VARCHAR(100), Via VARCHAR(100), Civico VARCHAR(10), CAP VARCHAR(10), Citta VARCHAR(50), PRIMARY KEY (id_cliente) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE TelefonoCliente ( id_cliente BIGINT NOT NULL, Telefono VARCHAR(20) NOT NULL, PRIMARY KEY (id_cliente, Telefono), CONSTRAINT fk_tel_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DocumentoCliente ( id_cliente BIGINT NOT NULL, Tipo VARCHAR(20) NOT NULL, Numero VARCHAR(30) NOT NULL, EnteRilascio VARCHAR(50), DataRilascio DATE, DataScadenza DATE, PRIMARY KEY (id_cliente, Tipo, Numero), CONSTRAINT fk_doc_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE Operatore ( Matricola VARCHAR(20) NOT NULL, Nome VARCHAR(50), Cognome VARCHAR(50), PRIMARY KEY (Matricola) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE OperatoreLingua ( Matricola VARCHAR(20) NOT NULL, cod_lingua CHAR(5) NOT NULL, LivelloCompetenza VARCHAR(30), PRIMARY KEY (Matricola, cod_lingua), CONSTRAINT fk_opling_operatore FOREIGN KEY (Matricola) REFERENCES Operatore(Matricola) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_opling_lingua FOREIGN KEY (cod_lingua) REFERENCES Lingua(cod_lingua) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* ----------------------------------------------------------- TICKET & INTERVENTI ----------------------------------------------------------- */ CREATE TABLE Ticket ( numero_ticket BIGINT NOT NULL AUTO_INCREMENT, ReservationNumber VARCHAR(30), FlightNumber VARCHAR(20), DataOraApertura DATETIME NOT NULL, Oggetto VARCHAR(150), id_cliente BIGINT NOT NULL, cod_lingua CHAR(5) NOT NULL, cod_livello CHAR(10) NOT NULL, cod_stato CHAR(10) NOT NULL, PRIMARY KEY (numero_ticket), CONSTRAINT fk_ticket_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_ticket_lingua FOREIGN KEY (cod_lingua) REFERENCES Lingua(cod_lingua) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_ticket_livello FOREIGN KEY (cod_livello) REFERENCES LivelloTicket(cod_livello) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_ticket_stato FOREIGN KEY (cod_stato) REFERENCES StatoTicket(cod_stato) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE Intervento ( id_intervento BIGINT NOT NULL AUTO_INCREMENT, numero_ticket BIGINT NOT NULL, Matricola VARCHAR(20) NOT NULL, cod_attivita CHAR(10) NOT NULL, DataOraInizio DATETIME NOT NULL, DataOraFine DATETIME, Nota TEXT, cod_esito CHAR(10), PRIMARY KEY (id_intervento), CONSTRAINT fk_intrv_ticket FOREIGN KEY (numero_ticket) REFERENCES Ticket(numero_ticket) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_intrv_operatore FOREIGN KEY (Matricola) REFERENCES Operatore(Matricola) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_intrv_attivita FOREIGN KEY (cod_attivita) REFERENCES AttivitaCodificata(cod_attivita) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_intrv_esito FOREIGN KEY (cod_esito) REFERENCES EsitoIntervento(cod_esito) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* ----------------------------------------------------------- COMUNICAZIONI, FEEDBACK, GIUDIZI ----------------------------------------------------------- */ CREATE TABLE Comunicazione ( id_comm BIGINT NOT NULL AUTO_INCREMENT, numero_ticket BIGINT NOT NULL, Matricola VARCHAR(20), -- mittente operatore (NULL se cliente) id_cliente BIGINT, -- mittente cliente (NULL se operatore) Tipo VARCHAR(30), Timestamp DATETIME NOT NULL, ContenutoURL VARCHAR(255), PRIMARY KEY (id_comm), CONSTRAINT fk_comm_ticket FOREIGN KEY (numero_ticket) REFERENCES Ticket(numero_ticket) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_comm_operatore FOREIGN KEY (Matricola) REFERENCES Operatore(Matricola) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_comm_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE FeedbackCliente ( id_feedback BIGINT NOT NULL AUTO_INCREMENT, numero_ticket BIGINT NOT NULL, id_cliente BIGINT NOT NULL, cod_voto CHAR(10) NOT NULL, VotoSoddisfazione TINYINT, DataFeedback DATE, NoteMotivazione TEXT, PRIMARY KEY (id_feedback), UNIQUE KEY uq_fb_ticket (numero_ticket), CONSTRAINT fk_fb_ticket FOREIGN KEY (numero_ticket) REFERENCES Ticket(numero_ticket) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_fb_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_fb_voto FOREIGN KEY (cod_voto) REFERENCES VotoFeedback(cod_voto) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE GiudizioCliente ( id_giudizio BIGINT NOT NULL AUTO_INCREMENT, id_cliente BIGINT NOT NULL, Matricola VARCHAR(20) NOT NULL, DataGiudizio DATE NOT NULL, Giudizio TEXT, PRIMARY KEY (id_giudizio), CONSTRAINT fk_giud_cliente FOREIGN KEY (id_cliente) REFERENCES Cliente(id_cliente) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_giud_operatore FOREIGN KEY (Matricola) REFERENCES Operatore(Matricola) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* ----------------------------------------------------------- DISABILITA VINCOLI PER INSERIMENTI MASSIVI ----------------------------------------------------------- */ SET FOREIGN_KEY_CHECKS = 0; /* ----------------------------------------------------------- DOMINI ----------------------------------------------------------- */ INSERT INTO Lingua (cod_lingua, Descrizione) VALUES ('IT', 'Italiano'), ('FR', 'Francese'), ('EN', 'Inglese'); INSERT INTO LivelloTicket (cod_livello, Descrizione) VALUES ('L1', 'Basso'), ('L2', 'Medio'), ('L3', 'Alto'); INSERT INTO StatoTicket (cod_stato, Descrizione) VALUES ('AP', 'Aperto'), ('IN', 'In lavorazione'), ('CH', 'Chiuso'); INSERT INTO AttivitaCodificata (cod_attivita, Descrizione) VALUES ('INFO', 'Informazioni'), ('REBK', 'Ribooking'), ('RIMB', 'Rimborso'); INSERT INTO EsitoIntervento (cod_esito, Descrizione) VALUES ('OK', 'Risolto'), ('KO', 'Non risolto'), ('ESC', 'Escalato'); INSERT INTO VotoFeedback (cod_voto, Descrizione) VALUES ('POS', 'Positivo'), ('NEG', 'Negativo'), ('NEU', 'Neutro'); /* ----------------------------------------------------------- ANAGRAFICHE CLIENTI ----------------------------------------------------------- */ INSERT INTO Cliente (id_cliente, Nome, Cognome, DataDiNascita, Email, Via, Civico, CAP, Citta) VALUES (1, 'Giulia', 'Rossi', '1985-03-14', 'giulia.rossi@email.it', 'Via Roma', '10', '00100', 'Roma'), (2, 'Pierre', 'Dubois', '1990-07-02', 'pierre.dubois@mail.fr', 'Rue Lafayette', '25', '75009', 'Paris'); INSERT INTO TelefonoCliente (id_cliente, Telefono) VALUES (1, '+39‑331‑1111111'), (1, '+39‑06‑55555555'), (2, '+33‑6‑22222222'); INSERT INTO DocumentoCliente (id_cliente, Tipo, Numero, EnteRilascio, DataRilascio, DataScadenza) VALUES (1, 'PASSAPORTO', 'YA123456', 'Questura di Roma', '2020-05-10', '2030-05-09'), (2, 'CNI', '123456789', 'Mairie de Paris', '2019-09-15', '2029-09-14'); /* ----------------------------------------------------------- OPERATORI E LINGUE ----------------------------------------------------------- */ INSERT INTO Operatore (Matricola, Nome, Cognome) VALUES ('OP001', 'Marco', 'Bianchi'), ('OP002', 'Sara', 'Verdi'), ('OP003', 'Jean', 'Martin'); INSERT INTO OperatoreLingua (Matricola, cod_lingua, LivelloCompetenza) VALUES ('OP001', 'IT', 'buono'), ('OP001', 'FR', 'buono'), ('OP002', 'IT', 'buono'), ('OP002', 'EN', 'buono'), ('OP003', 'FR', 'buono'); /* ----------------------------------------------------------- TICKET ----------------------------------------------------------- */ INSERT INTO Ticket (numero_ticket, ReservationNumber, FlightNumber, DataOraApertura, Oggetto, id_cliente, cod_lingua, cod_livello, cod_stato) VALUES (1, 'RSV123', 'AZ123', '2024-12-15 09:30:00', 'Cambio posto a sedere', 1, 'IT', 'L2', 'AP'), (2, 'RSV234', 'AZ456', '2025-01-05 14:10:00', 'Bagaglio in ritardo', 2, 'FR', 'L3', 'IN'), (3, 'RSV345', 'AZ789', '2025-02-20 08:45:00', 'Richiesta rimborso', 1, 'IT', 'L1', 'CH'); /* ----------------------------------------------------------- INTERVENTI ----------------------------------------------------------- */ INSERT INTO Intervento (id_intervento, numero_ticket, Matricola, cod_attivita, DataOraInizio, DataOraFine, Nota, cod_esito) VALUES (1, 1, 'OP001', 'INFO', '2024-12-15 09:35:00', '2024-12-15 09:50:00', 'Fornite informazioni sui posti disponibili', 'OK'), (2, 2, 'OP003', 'REBK', '2025-01-05 14:20:00', '2025-01-05 15:00:00', 'Aperta pratica bagaglio', 'ESC'), (3, 3, 'OP002', 'RIMB', '2025-02-20 09:00:00', '2025-02-20 09:25:00', 'Avviata procedura di rimborso', 'OK'); /* ----------------------------------------------------------- COMUNICAZIONI ----------------------------------------------------------- */ INSERT INTO Comunicazione (id_comm, numero_ticket, Matricola, id_cliente, Tipo, Timestamp, ContenutoURL) VALUES (1, 1, 'OP001', NULL, 'chat', '2024-12-15 09:36:00', 'https://chat.example.com/conv/1'), (2, 1, NULL, 1, 'chat', '2024-12-15 09:37:00', 'https://chat.example.com/conv/1#msg2'), (3, 2, 'OP003', NULL, 'telefono', '2025-01-05 14:25:00', NULL), (4, 3, 'OP002', NULL, 'email', '2025-02-20 09:05:00', 'mailto:customer@example.com'); /* ----------------------------------------------------------- FEEDBACK & GIUDIZI ----------------------------------------------------------- */ INSERT INTO FeedbackCliente (id_feedback, numero_ticket, id_cliente, cod_voto, VotoSoddisfazione, DataFeedback, NoteMotivazione) VALUES (1, 1, 1, 'POS', 5, '2024-12-16', 'Servizio rapido e cortese'), (2, 3, 1, 'NEG', 2, '2025-02-22', 'Rimborso parziale non soddisfacente'); INSERT INTO GiudizioCliente (id_giudizio, id_cliente, Matricola, DataGiudizio, Giudizio) VALUES (1, 1, 'OP001', '2024-12-16', 'Cliente molto collaborativo'), (2, 2, 'OP003', '2025-01-05', 'Cliente frustrato per il ritardo bagagli'); /* ----------------------------------------------------------- RIABILITA VINCOLI ----------------------------------------------------------- */ SET FOREIGN_KEY_CHECKS = 1;