SUGGERIMENTI

VIAGGI ESTIVI
MERCH D'ISTITUTO
APPUNTI
NEWS SCUOLA

0 risultati disponibili

MODIFICA FILTRO

CHIUDI SEARCH

QUANDO VUOI PARTIRE?

DOVE VUOI ANDARE?

SPAGNA
ALBANIA
GRECIA E ALBANIA

0 risultati disponibili

MODIFICA FILTRO

#Maturità

Soluzione 2° Prova: Informatica 2015 – ITI Informatica

17 giugno 2015

Analisi delle realtà di riferimento e ipotesi aggiuntive

In fase di registrazione verrà inviata all'utente una mail contenente un hash di 8 carattere rappresentante la password generata in maniere automatizzata e salvata a database con codifica MD5 per questioni di sicurezza ed evitare quindi di averle salvate in chiaro;
È stato scelto di gestire gli artisti associati agli eventi in una tabella separata in modo da avere un sistema più flessibile dando quindi, in futuro, la possibilità di utilizzare questa informazioni come filtro di ricerca;
Secondo le specifiche non viene richiesto in fase di registrazione dell'utente il luogo di appartenenza e la preferenza di notifica via newsletter(privacy). Viene ipotizzata una gestione profilo per il completamento delle informazioni mancanti ma necessarie a rispettare le specifiche;

Schema concettuale/logico della base di dati

Definizione in linguaggio SQL

CREATE SCHEMA IF NOT EXISTS `web_community` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `web_community` ; DROP TABLE IF EXISTS `web_community`.`inserzioni` ;

CREATE TABLE IF NOT EXISTS `web_community`.`inserzioni` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `testo` TEXT NOT NULL, 
  `link` TEXT NOT NULL, 
  PRIMARY KEY (`id`)) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`province` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`province` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `nome` VARCHAR(80) NOT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE INDEX `nome_UNIQUE` (`nome` ASC)) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`categorie` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`categorie` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `nome` VARCHAR(80) NOT NULL, 
  PRIMARY KEY (`id`)) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`utenti` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`utenti` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `nickname` VARCHAR(80) NOT NULL, 
  `nome` VARCHAR(80) NOT NULL, 
  `cognome` VARCHAR(80) NOT NULL, 
  `email` VARCHAR(254) NOT NULL, 
  `password` VARCHAR(32) NOT NULL, 
  `id_provincia` INT NULL, 
  PRIMARY KEY (`id`), 
  UNIQUE INDEX `nickname_UNIQUE` (`nickname` ASC), 
  INDEX `fk_utenti_province1_idx` (`id_provincia` ASC), 
  CONSTRAINT `fk_utenti_province1` 
    FOREIGN KEY (`id_provincia`) 
    REFERENCES `web_community`.`province` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`eventi` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`eventi` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `titolo` VARCHAR(255) NOT NULL, 
  `id_categoria` INT NOT NULL, 
  `data_inizio` DATETIME NOT NULL, 
  `data_fine` DATETIME NOT NULL, 
  `id_provincia` INT NOT NULL, 
  `id_utente` INT NOT NULL, 
  PRIMARY KEY (`id`), 
  INDEX `fk_eventi_province_idx` (`id_provincia` ASC), 
  INDEX `fk_eventi_categorie1_idx` (`id_categoria` ASC), 
  INDEX `fk_eventi_utenti1_idx` (`id_utente` ASC), 
  CONSTRAINT `fk_eventi_province` 
    FOREIGN KEY (`id_provincia`) 
    REFERENCES `web_community`.`province` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_eventi_categorie1` 
    FOREIGN KEY (`id_categoria`) 
    REFERENCES `web_community`.`categorie` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_eventi_utenti1` 
    FOREIGN KEY (`id_utente`) 
    REFERENCES `web_community`.`utenti` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`posts` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`posts` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `commento` TEXT NOT NULL, 
  `voto` ENUM('1', '2', '3', '4', '5') NOT NULL, 
  `data_creazione` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  `id_utente` INT NOT NULL, 
  `id_evento` INT NOT NULL, 
  PRIMARY KEY (`id`), 
  INDEX `fk_posts_utenti1_idx` (`id_utente` ASC), 
  INDEX `fk_posts_eventi1_idx` (`id_evento` ASC), 
  CONSTRAINT `fk_posts_utenti1` 
    FOREIGN KEY (`id_utente`) 
    REFERENCES `web_community`.`utenti` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_posts_eventi1` 
    FOREIGN KEY (`id_evento`) 
    REFERENCES `web_community`.`eventi` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`artisti` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`artisti` ( 
  `id` INT NOT NULL AUTO_INCREMENT, 
  `nome` VARCHAR(80) NOT NULL, 
  PRIMARY KEY (`id`)) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`utenti_categorie` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`utenti_categorie` ( 
  `id_categoria` INT NOT NULL, 
  `id_utente` INT NOT NULL, 
  PRIMARY KEY (`id_categoria`, `id_utente`), 
  INDEX `fk_utenti_categorie_categorie1_idx` (`id_categoria` ASC), 
  INDEX `fk_utenti_categorie_utenti1_idx` (`id_utente` ASC), 
  CONSTRAINT `fk_utenti_categorie_categorie1` 
    FOREIGN KEY (`id_categoria`) 
    REFERENCES `web_community`.`categorie` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_utenti_categorie_utenti1` 
    FOREIGN KEY (`id_utente`) 
    REFERENCES `web_community`.`utenti` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`artisti_eventi` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`artisti_eventi` ( 
  `id_artista` INT NOT NULL, 
  `id_evento` INT NOT NULL, 
  PRIMARY KEY (`id_artista`, `id_evento`), 
  INDEX `fk_artisti_eventi_artisti1_idx` (`id_artista` ASC), 
  INDEX `fk_artisti_eventi_eventi1_idx` (`id_evento` ASC), 
  CONSTRAINT `fk_artisti_eventi_artisti1` 
    FOREIGN KEY (`id_artista`) 
    REFERENCES `web_community`.`artisti` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_artisti_eventi_eventi1` 
    FOREIGN KEY (`id_evento`) 
    REFERENCES `web_community`.`eventi` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB; 

DROP TABLE IF EXISTS `web_community`.`inserzioni_categorie` ; 

CREATE TABLE IF NOT EXISTS `web_community`.`inserzioni_categorie` ( 
  `id_categoria` INT NOT NULL, 
  `id_inserzione` INT NOT NULL, 
  PRIMARY KEY (`id_categoria`, `id_inserzione`), 
  INDEX `fk_inserzioni_categorie_categorie1_idx` (`id_categoria` ASC), 
  INDEX `fk_inserzioni_categorie_inserzioni1_idx` (`id_inserzione` ASC), 
  CONSTRAINT `fk_inserzioni_categorie_categorie1` 
    FOREIGN KEY (`id_categoria`) 
    REFERENCES `web_community`.`categorie` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION, 
  CONSTRAINT `fk_inserzioni_categorie_inserzioni1` 
    FOREIGN KEY (`id_inserzione`) 
    REFERENCES `web_community`.`inserzioni` (`id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION) 
ENGINE = InnoDB;

Risoluzione punto 5

5.a Elenco degli eventi già svolti in ordine alfabetico di provincia

SELECT 
    * 
FROM 
    eventi AS e 
        INNER JOIN 
    province AS p ON e.id_provincia = p.id 
WHERE 
    e.data_fine < NOW() 
ORDER BY p.nome;

5.b Elenco dei membri che non hanno mai inserito un commento

SELECT 
    * 
FROM 
    utenti 
WHERE 
	id NOT IN ( 
		SELECT 
			id_utente 
		FROM 
			posts 
	);

5.c Per ogni evento il voto medio ottenuto in ordine di categoria e titolo

SELECT 
    e.id, e.titolo, AVG(p.voto) AS media 
FROM 
    eventi AS e 
        INNER JOIN 
    categorie AS c ON c.id = e.id_categoria 
        LEFT JOIN 
    posts AS p ON e.id = p.id_evento 
GROUP BY e.id 
ORDER BY c.nome , e.titolo;

5.d I dati dell'utente che ha registrato il maggior numero di eventi

SELECT 
    u.*, 
    (SELECT 
            COUNT(id_utente) 
        FROM 
            eventi AS e 
        WHERE 
            e.id_utente = u.id 
        GROUP BY id_utente 
	) AS eventi_caricati 
FROM 
    utenti AS u 
ORDER BY eventi_caricati DESC 
LIMIT 1

Prima Parte

Soluzione Query di Informatica:

Query 1:

SELECT * FROM evento INNER JOIN luogo ON evento.id_luogo = luogo.id WHERE evento.data_creazione < NOW() ORDER by luogo.provincia ASC

Query 2:

SELECT * FROM utente WHERE NOT EXISTS (SELECT null FROM voto WHERE voto.id_utente = utente.id)

Query 3:

SELECT ROUND(AVG(voto.voto), 1) as 'voto', categoria.nome, evento.nome FROM evento LEFT JOIN voto ON evento.id = voto.id_evento INNER JOIN categoria ON evento.id_categoria =categoria.id GROUP BY evento.nome, categoria.nome

Query 4:

SELECT

count(evento.id_utente) as 'Numero Eventi',

utente.id, utente.nome, utente.email, utente.nickname

FROM evento INNER JOIN utente ON evento.id_utente = utente.id GROUP BY utente.idORDER BY 'Numero Eventi' DESC LIMIT 1

<strong>Soluzione Struttura definitiva di Informatica</strong>:

CREATE TABLE `utente` (

`id` int NOT NULL AUTO_INCREMENT,

`nickname` varchar(50) NOT NULL,

`nome` varchar(50) DEFAULT NULL,

`cognome` varchar(50) DEFAULT NULL,

`email` varchar(255) NOT NULL,

PRIMARY KEY(id)

);

CREATE TABLE `categoria` (

`id` int NOT NULL AUTO_INCREMENT,

`nome` varchar(50) DEFAULT NULL,

PRIMARY KEY(id)

);

CREATE TABLE `utente_categoria` (

`id` int NOT NULL AUTO_INCREMENT,

`id_utente` int NOT NULL,

`id_categoria` int NOT NULL,

PRIMARY KEY(id),

FOREIGN KEY (id_utente) REFERENCES utente(id),

FOREIGN KEY (id_categoria) REFERENCES categoria(id)

);

CREATE TABLE `luogo` (

`id` int NOT NULL AUTO_INCREMENT,

`nome` varchar(255) DEFAULT NULL,

`latitudine` varchar(50) DEFAULT NULL,

`longitudine` varchar(50) DEFAULT NULL,

`provincia` varchar(2),

PRIMARY KEY(id)

);

CREATE TABLE `evento` (

`id` int NOT NULL AUTO_INCREMENT,

`nome` varchar(50) NOT NULL,

`data_creazione` datetime DEFAULT NULL,

`id_luogo` int NOT NULL,

`id_categoria` int NOT NULL,

`id_utente` int NOT NULL,

PRIMARY KEY(id),

FOREIGN KEY (id_luogo) REFERENCES luogo(id),

FOREIGN KEY (id_categoria) REFERENCES categoria(id),

FOREIGN KEY (id_utente) REFERENCES utente(id)

);

CREATE TABLE `artista`(

`id` int NOT NULL AUTO_INCREMENT,

`nome` varchar(50),

`cognome` varchar(50),

`alias` varchar(50),

PRIMARY KEY(id)

);

CREATE TABLE `evento_artista` (

`id` int NOT NULL AUTO_INCREMENT,

`id_evento` int NOT NULL,

`id_artista` int NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (id_evento) REFERENCES evento(id),

FOREIGN KEY (id_artista) REFERENCES artista(id)

);

CREATE TABLE `voto` (

`id` int NOT NULL AUTO_INCREMENT,

`id_utente` int NOT NULL,

`id_evento` int NOT NULL,

`commento` text,

`voto` tinyint(4) DEFAULT NULL,

PRIMARY KEY(id),

FOREIGN KEY (id_utente) REFERENCES utente(id),

FOREIGN KEY (id_evento) REFERENCES evento(id)

);

INDEX.PHP

<?php $servername = 'localhost'; $username = 'root'; $password = '';// Creo la connessione $conn = new mysqli($servername, $username, $password); // Verifico la connessione if ($conn->connect_error) { die("Connection failed: $conn->connect_error"); } $conn->select_db('web_community') or die($conn->error); $province = $conn->query('SELECT * FROM province'); $eventi = null; if (isset($_GET['provincia']) && is_numeric($_GET['provincia'])) { $eventi = $conn->query(sprintf('SELECT * FROM eventi WHERE data_fine > NOW() AND id_provincia = %d', $_GET['provincia'])); } else { $eventi = $conn->query('SELECT * FROM eventi WHERE data_fine > NOW()'); } ?> <!DOCTYPE html> <html lang="it"> <head> <meta charset="UTF-8"> <title>Eventi</title> </head> <body> <h1>Eventi</h1> <div> <?php if ($province && $province->num_rows): ?> <form action="" method="GET"> <select name="provincia"> <option value="">Provincia</option> <?php while ($provincia = $province->fetch_object()): ?> <option <?php if (isset($_GET['provincia']) && $_GET['provincia'] == $provincia->id): ?> selected <?php endif ?> value="<?php echo $provincia->id ?>" > <?php echo $provincia->nome ?> </option> <?php endwhile ?> </select> <button type="submit">Filtra</button> </form> <?php endif ?> <?php if ($eventi && $eventi->num_rows): ?> <ul> <?php while ($evento = $eventi->fetch_object()): ?> <li> <h2><a href="/evento.php?id=<?php echo $evento->id ?>"><?php echo $evento->titolo ?></a></h2> <div><?php echo $evento->data_inizio ?></div> <div><?php echo $evento->data_fine ?></div> </li> <?php endwhile ?> </ul> <?php else: ?> <div>Nessun evento</div> <?php endif ?> </div> </body> </html>

EVENTO.PHP

<?php $servername = 'localhost'; $username = 'root'; $password = '';// Creo la connessione $conn = new mysqli($servername, $username, $password); // Verifico la connessione if ($conn->connect_error) { die("Connection failed: $conn->connect_error"); } $conn->select_db('web_community') or die($conn->error); if (!isset($_GET['id']) || !is_numeric($_GET['id'])) { header('HTTP/1.0 404 Not Found'); exit('404 Not Found'); } $evento = $conn->query(sprintf('SELECT * FROM eventi WHERE id = %d LIMIT 1', $_GET['id'])); if (!$evento || $evento->num_rows == 0) { header('HTTP/1.0 404 Not Found'); exit('404 Not Found'); } $evento = $evento->fetch_object(); $artisti = $conn->query(sprintf('SELECT * FROM artisti_eventi INNER JOIN artisti ON artisti_eventi.id_artista = artisti.id WHERE id_evento = %d', $_GET['id'])); $posts = $conn->query(sprintf('SELECT * FROM posts INNER JOIN utenti ON utenti.id = posts.id_utente WHERE id_evento = %d ORDER BY data_creazione DESC', $_GET['id'])); ?> <!DOCTYPE html> <html lang="it"> <head> <meta charset="UTF-8"> <title><?php echo $evento->titolo ?></title> </head> <body> <div> <h1><?php echo $evento->titolo ?></h1> <div><?php echo $evento->data_inizio ?></div> <div><?php echo $evento->data_fine ?></div> <?php if ($artisti && $artisti->num_rows): ?> <h3>Artisti:</h3> <?php while ($artista = $artisti->fetch_object()): ?> <div><?php echo $artista->nome ?></div> <?php endwhile ?> <?php endif ?> <?php if ($posts && $posts->num_rows): ?> <h3>Commenti:</h3> <div> <?php while ($post = $posts->fetch_object()): ?> <div>Autore: <?php echo $post->nickname ?></div> <div>Data: <?php echo $post->data_creazione ?></div> <div>Voto: <?php echo $post->voto ?></div> <div><?php echo $post->testo ?></div> <?php endwhile ?> </div> <?php endif ?> </div> </body> </html>

Traccia svolta dai tutor di Docsity

PAGA CON

Ciao, benvenuto su ScuolaZoo! Come possiamo aiutarti?