Sessione ordinaria 2015
Seconda prova scritta
Ministero dell’Istruzione, dell’ Università e della Ricerca
M963 – ESAME DI STATO DI ISTRUZIONE SECONDARIA SUPERIORE Indirizzo: ITIA - INFORMATICA E TELECOMUNICAZIONI
ARTICOLAZIONE INFORMATICA Tema di: INFORMATICA
Il candidato svolga la prima parte della prova e risponda a due tra i quesiti proposti nella seconda parte.
PRIMA PARTE
Si vuole realizzare una web community per condividere dati e commenti relativi a eventi dal vivo di diverse categorie, ad esempio concerti, spettacoli teatrali, balletti, ecc. che si svolgono in Italia.
Gli eventi vengono inseriti sul sistema direttamente dai membri stessi della community, che si registrano sul sito fornendo un nickname, nome, cognome, indirizzo di e-mail e scegliendo una o più categorie di eventi a cui sono interessati.
Ogni membro utente riceve periodicamente per posta elettronica una newsletter, emessa automaticamente dal sistema, che riporta gli eventi delle categorie da lui scelte, che si svolgeranno nella settimana seguente nel territorio provinciale dell'utente.
I membri registrati possono interagire con la community sia inserendo i dati di un nuovo evento, per il quale occorre specificare categoria, luogo di svolgimento, data, titolo dell’evento e artisti coinvolti, sia scrivendo un post con un commento ed un voto (da 1 a 5) su un evento.
Il sito della community offre a tutti, sia membri registrati sia utenti anonimi, la consultazione dei dati on line, tra cui:
visualizzazione degli eventi di un certo tipo in ordine cronologico, con possibilità di filtro per
territorio di una specifica provincia
visualizzazione di tutti i commenti e voti relativi ad un evento. Il candidato, fatte le opportune ipotesi
aggiuntive, sviluppi
1. un’analisi della realtà di riferimento individuando le possibili soluzioni e scelga quella che a suo motivato giudizio è la più idonea a rispondere alle specifiche indicate
2. uno schema concettuale della base di dati
3. uno schema logico della base di dati
4. la definizione in linguaggio SQL di un sottoinsieme delle relazioni della base di dati in cui siano presenti alcune di quelle che contengono vincoli di integrità referenziale e/o vincoli di dominio, laddove presenti
5. le seguenti interrogazioni espresse in linguaggio SQL:
a. elenco degli eventi già svolti, in ordine alfabetico di provincia b. elenco dei membri che non hanno mai inserito un commento
c. per ogni evento il voto medio ottenuto in ordine di categoria e titolo d. i dati dell'utente che ha registrato il maggior numero di eventi
6. il progetto della pagina dell’interfaccia WEB che permetta ad un utente registrato di svolgere le operazioni specificate
7. la codifica in un linguaggio a scelta di un segmento significativo dell'applicazione Web che consente l'interazione con la base di dati.
Analisi
La traccia risulta chiara e non ambigua e fornisce tutti gli elementi per la progettazione della base di dati.
Viene adottata una soluzione web adatta sia ai PC che ai dispositivi mobili (pagine web responsive) Gli strumenti software necessari sono:
DBMS: MySQL
WEB SERVER: Apache
Linguaggio lato Server: PHP
Linguaggio lato Client: HTML, Java Script.
Si esclude l’utilizzo di un CMS vista la specificità dell’indirizzo di studi.
Modello Concettuale- E/R
Ipotesi di lavoro:si utilizzano solo gli attributi indispensabili così come si evince dalla traccia. Così come per
semplicità non è prevista una entità provincia, tale informazione viene gestita a livello di
ulteriore attributo, non è previsto un registro delle attività dei membri.
Regole di lettura del diagramma E/R
Ogni evento appartiene ad una categoria, ad ogni categoria appartengono N eventi;
Ogni utente può registrare N eventi, ogni evento è registrato da un solo utente;
Ogni utente può scrivere N commenti, ogni commento è scritto da un solo utente;
Ogni commento riguarda un evento, per ogni evento possono esserci N commenti;
Ogni utente preferisce N categorie, ogni categoria è preferita da N utenti;
Ogni artista partecipa a N eventi, ogni evento può avere più artisti;
Per realizzare un database che implementi questo schema bisogna risolvere la relazioni N:N che sono ben due con altrettante entità deboli e le associazioni tra esse e le entità di partenza, per cui:
1. l'associazione “preferisce” diviene l'entità Preferenza con le relative associazioni N:1 con Utente e Categoria;
2. l'associazione “partecipa” diviene l'entità Partecipa con le relative associazioni N:1 con Evento e Artista.
Il modello logico che ne deriva è quindi formato dalle 7 tabelle di seguito elencate che nello specifico avranno gli attributi indicati premesso che le tabelle sono nominate con la forma plurale dell'entità corrispondente, gli attributi chiave primaria vengono sottolineati, quelli chiave esterna sono scritti in corsivo (o racchiusi in parentesi quadre) :
1. EVENTI (id_evento, titolo, data, ora, citta, provincia, id_utente, id_categoria)
2. UTENTI (id_utente, username, password, cognome, nome, provincia, mail)
3. CATEGORIE (id_categoria, denominazione)
4. CATEGORIE (id_commento, post, data, voto, id_evento, id_utente)
5. ARTISTI (id_artista, cognome, nome)
6. PREFERENZA (id_utente, id_categoria) 7. PARTECIPA (id_evento, id_artista) Struttura delle tabelle sintassi MySQL:
TABELLA CAMPI KEY TIPO LUNGHEZZA NOTE
EVENTI Id_evento Primary Int 11 contatore
titolo Varchar 100 Titolo evento
Data Date 8 Data evento
Ora Time 8 orario
Citta Varchar 15
Provincia Char 2
Id_utente Foreign Int 11 Chiave esterna
Id_categoria Foreign Int 11 Chiave esterna
UTENTI Id_utente Primary int 11
Username Varchar 30
Password Varchar 8
Cognome Varchar 15
Nome Varchar 15
Provincia char 2
mail Varchar 40
CATEGORIE Id_categoria Primary int 11
denominazione Varchar 30
COMMENTI Id_commento Primary Int 11
Post Varchar 100
Data Date 8
Voto Int 1
Id_evento Foreign Int 11
Id_utente Foreign int 11
ARTISTI
PREFERENZA Id_utente Foreign int 11
Id_categoria Foreign int 11
PARTECIPA
Query SQL per la creazione del data base e delle tabelle:
create database webeventi;
CREATE TABLE Commenti (
Id_commento INT(11) PRIMARY KEY, auto_increment, * Post VARCHAR(100),
data DATE, Voto INT(1), Utente INT(11), Evento INT(11),
FOREIGN KEY ( Evento ) REFERENCES Eventi( ID_Evento ), FOREIGN KEY ( Utente ) REFERENCES Utenti(ID_Utente ),
CHECK ( Voto>0 AND Voto<6) **
);
*Clausola di default “on delete no action” per vincolo di integrità referenziale (intertabellare)
** Vincolo di dominio sui dati (intratabellare).
CREATE TABLE IF NOT EXIST eventi (
Id_evento INT(11)
PRIMARY KEY, auto_increment, titolo varchar(50),data DATE, ora TIME, citta varchar(30), provincia varchar(2), utente INT(11), categoria INT(11),
FOREIGN KEY ( Utente ) REFERENCES Utenti(ID_Utente ),
FOREIGN KEY ( categoria ) REFERENCES categorie( ID_categoria ), );
Interrogazioni in linguaggio SQL:
a)
SELECT Eventi.Data, Eventi.Titolo, eventi.provincia FROM Eventi
WHERE Data < CURRENT_DATE ORDER BY eventi.provincia b)
SELECT utenti.username FROM utenti
WHERE ID_utente NOT IN ( SELECT DISTINCT id_utente FROM Commenti )
ORDER BY username
c)
SELECT Eventi.Titolo, AVG( Commenti.Voto ) AS Punteggio
FROM Eventi INNER JOIN Commenti ON Commenti.id_Evento = Eventi.ID_Evento GROUP BY Eventi.ID_Evento HAVING Punteggio > 0
ORDER BY Eventi.id_Categoria, Eventi.Titolo
SELECT Eventi.Titolo, AVG( Commenti.Voto ) AS Punteggio, categorie.descrizione FROM eventi, commenti, categorie
where Commenti.id_Evento = Eventi.ID_Evento and categorie.id_categoria=eventi.id_categoria GROUP BY Eventi.ID_Evento HAVING Punteggio > 0
ORDER BY categorie.descrizione, Eventi.Titolo d)
Soluzione con creazione di una vista ossia ulteriore tabella da cancellare successivamente
CREATE VIEW Inserimenti AS
SELECT Eventi.id_Utente AS id_Utente, COUNT(Eventi.ID_Evento) AS Totale FROM Eventi
GROUP BY Eventi.id_Utente Join tra utenti e inserimenti
SELECT Utenti.username, Utenti.mail
FROM Inserimenti INNER JOIN Utenti ON Inserimenti.id_Utente = Utenti.ID_Utente WHERE Inserimenti.Totale = ( SELECT MAX(Totale) FROM Inserimenti )
drop view inserimenti
Soluzione con query annidate
SELECT u.username, u.mail, MAX(Totale) as Eventi_Inseriti
FROM (SELECT Eventi.id_utente AS id_utente, COUNT(Eventi.ID_Evento) AS Totale FROM Eventi GROUP BY Eventi.id_Utente) i, utenti u
where u.id_utente=i.id_utente
6) Interfaccia Web
7)
Visualizzazione di eventi relativamente ad una provincia e ad una data inserite dall'utente SELECT e.titolo, e.ora, e.citta
FROM eventi e
WHERE e.provincia='na' AND e.data='2017-06-30'
Contenuto da importare per costruire la base dati
-- phpMyAdmin SQL Dump-- version 4.6.5.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Creato il: Giu 21, 2017 alle 23:38 -- Versione del server: 10.1.21-MariaDB -- Versione PHP: 5.6.30
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `webeventi`
--
-- --- --
-- Struttura della tabella `artisti`
--
CREATE TABLE `artisti` (
`id_artista` int(11) NOT NULL, `cognome` varchar(15) NOT NULL, `nome` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `artisti`
--
INSERT INTO `artisti` (`id_artista`, `cognome`, `nome`) VALUES (1, 'ferro', 'tiziano'),
(2, 'fracci', 'carla'), (3, 'crispo', 'raffaele'), (4, 'orfei', 'moira'), (5, 'consoli', 'carmen');
-- --- --
-- Struttura della tabella `categorie`
--
CREATE TABLE `categorie` (
`id_categoria` int(11) NOT NULL, `descrizione` varchar(40) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `categorie`
--
INSERT INTO `categorie` (`id_categoria`, `descrizione`) VALUES (1, 'concerto'),
(2, 'teatro'), (3, 'balletto'), (4, 'circense');
-- --- --
-- Struttura della tabella `commenti`
--
CREATE TABLE `commenti` (
`id_commento` int(11) NOT NULL, `post` varchar(80) NOT NULL, `data` date DEFAULT NULL, `voto` int(1) NOT NULL,
`id_evento` int(11) NOT NULL, `id_utente` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `commenti`
--
INSERT INTO `commenti` (`id_commento`, `post`, `data`, `voto`, `id_evento`,
`id_utente`) VALUES
(1, 'molto bello partecipate', '2017-06-29', 5, 1, 1), (2, 'mi associo', '2017-06-30', 4, 1, 2),
(3, 'Insomma', '2017-06-01', 2, 2, 1), (4, 'like', '2017-06-06', 5, 2, 4), (5, 'merda', '2017-06-08', 1, 2, 4);
-- --- --
-- Struttura della tabella `eventi`
--
CREATE TABLE `eventi` (
`id_evento` int(11) NOT NULL, `titolo` varchar(50) NOT NULL, `data` date NOT NULL,
`ora` time DEFAULT NULL, `citta` varchar(30) NOT NULL, `provincia` varchar(2) NOT NULL, `id_utente` int(11) NOT NULL, `id_categoria` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `eventi`
--
INSERT INTO `eventi` (`id_evento`, `titolo`, `data`, `ora`, `citta`,
`provincia`, `id_utente`, `id_categoria`) VALUES
(1, 'concerto tiziano-consoli', '2017-06-30', '18:00:00', 'napoli', 'na', 1, 1),
(2, 'la morte del cigno', '2017-06-22', '20:00:00', 'verona', 'vr', 2, 3), (3, 'clown', '2017-06-01', '13:38:00', 'roma', 'rm', 1, 4),
(4, 'radiokiss', '2017-06-06', '08:18:00', 'napoli', 'na', 3, 1), (5, 'la smorfia', '2017-06-14', '06:10:00', 'moncalieri', 'to', 1, 2), (6, 'pizza in Villa', '2017-06-30', '21:00:00', 'napoli', 'na', 2, 1);
-- --- --
-- Struttura della tabella `partecipa`
--
CREATE TABLE `partecipa` ( `id_evento` int(11) NOT NULL, `id_artista` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `partecipa`
--
INSERT INTO `partecipa` (`id_evento`, `id_artista`) VALUES (1, 1),
(1, 5), (2, 2);
-- --- --
-- Struttura della tabella `preferenza`
--
CREATE TABLE `preferenza` ( `id_utente` int(11) NOT NULL, `id_categoria` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `preferenza`
--
INSERT INTO `preferenza` (`id_utente`, `id_categoria`) VALUES (1, 1),
(1, 3), (2, 2);
-- --- --
-- Struttura della tabella `utenti`
--
CREATE TABLE `utenti` (
`id_utente` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` varchar(8) NOT NULL, `cognome` varchar(15) DEFAULT NULL, `nome` varchar(15) DEFAULT NULL, `provincia` varchar(2) NOT NULL, `mail` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dump dei dati per la tabella `utenti`
--
INSERT INTO `utenti` (`id_utente`, `username`, `password`, `cognome`,
`nome`, `provincia`, `mail`) VALUES
(1, 'paologuarino', '090365', 'guarino', 'paolo', 'na', 'guarino_paolo@gmail.com'),
(2, 'elisamartano', '121267', 'martano', 'elisa', 'vr', 'martanoelisa@libero.it'),
(3, 'pippopelo', '000000', 'pelo', 'pippo', 'Rm', 'pippopelo@libero.it'), (4, 'vittorioguarino', '271298', 'guarino', 'vittorio', 'na',
'vitto@gmail.com');
--
-- Indici per le tabelle scaricate --
--
-- Indici per le tabelle `artisti`
--
ALTER TABLE `artisti`
ADD PRIMARY KEY (`id_artista`);
--
-- Indici per le tabelle `categorie`
--
ALTER TABLE `categorie`
ADD PRIMARY KEY (`id_categoria`);
--
-- Indici per le tabelle `commenti`
--
ALTER TABLE `commenti`
ADD PRIMARY KEY (`id_commento`), ADD KEY `id_evento` (`id_evento`), ADD KEY `id_utente` (`id_utente`);
--
-- Indici per le tabelle `eventi`
--
ALTER TABLE `eventi`
ADD PRIMARY KEY (`id_evento`), ADD KEY `id_evento` (`id_evento`), ADD KEY `id_utente` (`id_utente`),
ADD KEY `id_categoria` (`id_categoria`);
--
-- Indici per le tabelle `partecipa`
--
ALTER TABLE `partecipa`
ADD KEY `id_evento` (`id_evento`), ADD KEY `id_artista` (`id_artista`);
--
-- Indici per le tabelle `preferenza`
--
ALTER TABLE `preferenza`
ADD KEY `id_utente` (`id_utente`),
ADD KEY `id_categoria` (`id_categoria`);
--
-- Indici per le tabelle `utenti`
--
ALTER TABLE `utenti`
ADD PRIMARY KEY (`id_utente`);
--
-- AUTO_INCREMENT per le tabelle scaricate --
--
-- AUTO_INCREMENT per la tabella `artisti`
--
ALTER TABLE `artisti`
MODIFY `id_artista` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT per la tabella `categorie`
--
ALTER TABLE `categorie`
MODIFY `id_categoria` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT per la tabella `commenti`
--
ALTER TABLE `commenti`
MODIFY `id_commento` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT per la tabella `eventi`
--
ALTER TABLE `eventi`
MODIFY `id_evento` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT per la tabella `utenti`
--
ALTER TABLE `utenti`
MODIFY `id_utente` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- Limiti per le tabelle scaricate --
--
-- Limiti per la tabella `commenti`
--
ALTER TABLE `commenti`
ADD CONSTRAINT `commenti_ibfk_1` FOREIGN KEY (`id_evento`) REFERENCES
`eventi` (`id_evento`);
--
-- Limiti per la tabella `eventi`
--
ALTER TABLE `eventi`
ADD CONSTRAINT `eventi_ibfk_1` FOREIGN KEY (`id_utente`) REFERENCES
`utenti` (`id_utente`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `eventi_ibfk_2` FOREIGN KEY (`id_categoria`) REFERENCES
`categorie` (`id_categoria`);
--
-- Limiti per la tabella `partecipa`
--
ALTER TABLE `partecipa`
ADD CONSTRAINT `partecipa_ibfk_1` FOREIGN KEY (`id_artista`) REFERENCES
`artisti` (`id_artista`),
ADD CONSTRAINT `partecipa_ibfk_2` FOREIGN KEY (`id_evento`) REFERENCES
`eventi` (`id_evento`);
--
-- Limiti per la tabella `preferenza`
--
ALTER TABLE `preferenza`
ADD CONSTRAINT `preferenza_ibfk_1` FOREIGN KEY (`id_categoria`) REFERENCES
`categorie` (`id_categoria`),
ADD CONSTRAINT `preferenza_ibfk_2` FOREIGN KEY (`id_utente`) REFERENCES
`utenti` (`id_utente`);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;