SQLize | PHPize | SQLtest

/* ----------------------------------------------------------- DOMINI ----------------------------------------------------------- */ CREATE TABLE DB.Lingua ( cod_lingua CHAR(5) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_lingua) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.LivelloTicket ( cod_livello CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_livello) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.StatoTicket ( cod_stato CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_stato) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.AttivitaCodificata ( cod_attivita CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_attivita) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.EsitoIntervento ( cod_esito CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_esito) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.VotoFeedback ( cod_voto CHAR(10) NOT NULL, Descrizione VARCHAR(100), PRIMARY KEY (cod_voto) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /* ----------------------------------------------------------- ANAGRAFICHE ----------------------------------------------------------- */ CREATE TABLE DB.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 DB.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 DB.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 DB.Operatore ( Matricola VARCHAR(20) NOT NULL, Nome VARCHAR(50), Cognome VARCHAR(50), PRIMARY KEY (Matricola) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE DB.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 DB.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 DB.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 DB.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 DB.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 DB.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;