mini Appunti

Il candidato svolga la prima parte della prova e risponda ad almeno due tra i quesiti proposti nella seconda parte

Prima Parte

Il Consorzio di un Formaggio Tipico riunisce più di 400 produttori, sparsi nella zona di produzione. Il Consiglio del Consorzio decide di operare un forte rinnovamento tecnologico al duplice scopo di monitorare al meglio la raccolta di latte e la produzione del formaggio, nonché avere un sito di riferimento che permetta al pubblico di cercare, identificare e 'visitare' virtualmente i vari caseifici.

A tale scopo intende realizzare un sistema informativo automatizzato che, per ogni caseificio, raccolga giornalmente i dati relativi alla quantità di latte lavorata, a quella impiegata nella produzione di formaggio, alla quantità di forme prodotte e al numero di quelle vendute. Per ciascuna forma venduta interessa conoscere la stagionatura raggiunta (12, 24, 30 o 36 mesi), nome e tipo dell’acquirente (grande distribuzione, grossisti, ecc.) e se è di prima o di seconda scelta (forma con difetti di produzione). Tali informazioni vengono inserite direttamente dai caseifici a fine giornata, mediante accesso ad un’area riservata dell’interfaccia Web del sito del consorzio.

Ciascun caseificio ha un codice numerico di 4 cifre col quale vengono marchiate le forme, sulle quali viene anche apposta la data di produzione (mese ed anno) ed il numero progressivo all’interno del mese.

Il Consorzio è anche interessato a registrare le informazioni relative ai luoghi di produzione, a partire dal nome, indirizzo, dati di geolocalizzazione, nome del titolare, ed una serie di fotografie del caseificio per realizzare un “tour virtuale” .

Consegna

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. lo schema concettuale della base di dati
3. lo 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:
   1. Visualizzare il numero di forme prodotte da ciascun caseificio tra due date fornite
   2. Visualizzare la media del latte lavorato giornalmente nell’anno corrente dai caseifici provincia per provincia
   3. Visualizzare i dati del caseificio che ha venduto il maggior numero di forme di prima scelta in un anno impostato dall’utente
   4. Visualizzare l’elenco dei caseifici che, in un certo periodo individuato da due date fornite dall’utente, hanno venduto meno di 10 forme di seconda scelta
6. il progetto della Home page dell’interfaccia WEB che si intende proporre per la gestione del DataBase e delle gallerie di immagini dei luoghi di produzione.
7. la codifica in un linguaggio a scelta di un segmento significativo dell'applicazione Web che consente l'interazione con la base di dati.

Seconda parte

Il candidato (che potrà eventualmente avvalersi delle conoscenze e competenze maturate attraverso esperienze di alternanza scuola-lavoro, stage o formazione in azienda) risponda ad almeno due quesiti a scelta tra quelli sotto riportati:

1. In relazione al tema proposto nella prima parte, indichi come intende affrontare la gestione degli accessi riservati agli operatori dei caseifici per lo svolgimento delle loro funzioni.
2. In relazione al tema proposto nella prima parte, sviluppi la query SQL per calcolare la percentuale di forme di seconda scelta prodotte annualmente da un certo caseificio (sul totale delle forme da lui prodotte annualmente).
3. Illustri, anche servendosi di esempi, il concetto di “vista” in una base di dati.
4. Descriva le tipologie di linguaggi utilizzate in un DBMS.
   
   su

Si userà il DBMS MYSQL e il linguaggio php per l'implementazione dell'interfaccia grafica (pagina web) e delle interrogazioni al database. Si ipotizza quindi di poter testare il funzionamento del software in un ambiente preinstallato in locale che comprende una piattaforma integrata di sviluppo web (come ad esempio EasyPhp) contenente:

• il server web Apache
• il server di database MySQL
• un interprete di script php
• e l'interfaccia grafica phpMyAdmin per la creazione delle tabelle e il test delle query in SQL.

Ipotesi aggiuntive:

1. si ipotizza che i caseifici siano collocati solo in territorio italiano
2. si ipotizza che i dati di geolocalizzazione vengano ricavati in automatico tramite interfaccia web, a partire da indirizzo e luogo (registrati nel database) e che quindi non sia necessario aggiungere nelle tabelle nessun altro campo oltre quelli citati.

Saranno usati gli stessi strumenti per l'installazione del software dopo la fase di test negli host del cliente. Si prevede in particolare l'uso di un computer server (contenente il server web e il motore MySQL e php) e poi l'uso di un computer client in rete col server per ogni punto di controllo contenente l'interfaccia grafica che interagisce col database situato sul server remoto.

Per trovare altri esempi di analisi della realtà potete dare un'occhiata ad altri svolgimenti della simulazione che trovate alla fine di questo articolo e cioè qui

su

apri nella dimensione originale

Note: Si è usato la parola comune per intendere il luogo indicato nella traccia; qta è la quantità di latte lavorato in una certa data, qtaforme è quella parte impiegata nella produzione del formaggio.
Si è scelto di fare una progettazione semplice che considera solo le tabelle e i campi strettamente necessari all'interfaccia web; in particolare:

per il caseificio:

si userà il codice numerico di quattro cifre assegnato (indicato nel campo idcaseificio) come nome utente per l'accesso all'area riservata del sito del consorzio. Inoltre nel campo titolare si registrerà soltanto una stringa contenente nome e cognome

per l'acquirente:

saranno registrati solo: nome, cognome, codice fiscale e tipo (grande distribuzione, grossista, ecc.)

Una progettazione migliorata dovrebbe comprendere una tabella a parte per i dati del titolare (contenente tutti i dati anagrafici, altri campi più dettagliati per l'acquirente (data di nascita, indirizzo, modalità di pagamento ecc.), una tabella a parte (o dei campi in più) per i dati di registrazione ed accesso (token di accesso, data e ora di ultimo accesso, tempo di collegamento ecc.).

su

La progettazione logica sarà fatta elencando le tabelle e le relazioni tra le tabelle

Tabelle

caseifici(idcaseificio, nome, titolare, indirizzo, password, idcomune) comuni(idcomune, comune, provincia) foto(idfoto, didascalia, link, idcaseificio) forme(idforma, nome, idcaseificio, meseanno, progressivo, stagionatura, scelta)*1 lavorazionelatte(idlavorazione, qta, qtaforme, data, idcaseificio)*2 vendita(idacquirente,idforma, data) acquirente(idacquirente, nome, cognome, codicefiscale, tipo)

Note:

^ *1: per stagionatura si intendono i mesi, nella fattispecie: 12,24,30,36; l'inserimento corretto verrà controllato tramite la clausola check nella CREATE TABLE;
il campo scelta può contenere i valori 1 (prima scelta) o 2 (seconda scelta); anche questo vincolo verrà controllato tramite una condizione in AND con la condizione sulla stagionatura tramite la clausola CHECK (vedi CREATE TABLE) ^ *2: qta e qtaforme sono rispettivamente la quantità totale di latte lavorata in una certa data e la parte relativa alla produzione delle forme; da questo nasce il vincolo: (qtaforme<=qta) che sarà inserito in fase di creazione della relazione "lavorazionelatte" (cioè nella create table)

---

Relazioni tra le tabelle^*

Tabella 1	Tabella 2	Relazione	tramite
caseificio	comuni	M-1	idcomune
foto	caseificio	M-1	idcaseificio
forme	caseificio	M-1	idcaseificio
lavorazionelatte	caseificio	M-1	idcaseificio
acquirente	forme	M-N	vendita

^ * NB: le relazioni tra le tabelle, sono descritte in maniera non grafica poichè la progettazione concettuale descrive già ampiamente le associazioni tra le entità; ciononostante è opportuno descriverle indicando, per ogni relazione, la maniera nella quale sono collegate, e cioè: o tramite una chiave esterna (nel caso di relazioni uno a uno e uno a molti), oppure tramite un'altra tabella (nel caso di relazione molti a molti). Nella tabella quindi il campo tramite assume proprio: il nome del campo chiave esterna (relazioni 1-1, m-1) o il nome della tabella che fa da "ponte" tra le due. Si intende anche specificare che, la chiave esterna che fa da collegamento (tramite) tra le due tabelle, abbia lo stesso nome della chiave primaria alla quale si collega.

su

La definizione delle relazioni è fatta per tre delle tabelle esistenti. In particolare, si fornisce il codice SQL per la creazione della tabella lavorazionelatte e della tabella forme, nonchè della tabella caseifici collegata ad entrambe le tabelle precedenti tramite la chiave esterna idcaseificio come da schema logico.

Tabelle lavorazionelatte e caseifici

create table lavorazionelatte ( idlavorazione int unsigned not null auto_increment, qta DECIMAL(6, 4), qtaforme DECIMAL(6, 4), data DATE, idcaseificio int unsigned, check(qtaforme<=qta), primary key (idlavorazione), foreign key (idcaseificio) references caseifici );

---

create table caseifici ( idcaseificio int unsigned not null, nome varchar(20), titolare varchar(30), indirizzo varchar(30), password varchar(30), idcomune int unsigned, primary key (idcaseificio), foreign key (idcomune) references comuni );

Ipotesi

si suppone che esista già la tabella comuni con chiave primaria idcomune. Il campo idcaseificio non è auto_increment perché contiene il codice numerico del caseificio che per ipotesi deve essere di 4 cifre; definire questo campo chiave primaria (nella riga primary key(idcaseificio)) assicura l'univocità dei valori. Bisogna controllare inoltre che questo campo contenga effettivamente quattro cifre: questo potrà essere fatto ad esempio all'atto della codifica php prima dell'inserimento nel database (e in particolare nel front-end della form di inserimento e quindi addirittura prima di sottomettere la query di inserimento al server).

---

Tabella forme

create table forme ( idforma int unsigned not null auto_increment, nome varchar(30), idcaseificio int unsigned, progressivo int, meseanno TIMESTAMP(4), stagionatura int unsigned, scelta int unsigned, check((scelta IN (1,2)) and (stagionatura IN (12,24,30,36))), primary key (idforma), foreign key (idcaseificio) references caseifici );

su

1. Visualizzare il numero di forme prodotte da ciascun caseificio tra due date fornite;

   SELECT count(*) AS 'N. FORME', idcaseificio, caseificio.nome FROM caseifici, forme WHERE ( DATE_FORMAT(meseanno, '%Y-%m') >= [inserisci data inizio (aaaa/mm)] ) and ( ( DATE_FORMAT(meseanno, '%Y-%m') <= [inserisci data fine (aaaa/mm] ) and (caseificio.idcaseificio=forme.idcaseificio) GROUP BY idcaseificio

   NB:
   si ipotizza che la data sia memorizzata nel formato: yyyy-mm-dd (formato usato in genere in MYSQL che è il DBMS scelto).
   La funzione DATE_FORMAT serve per estrapolare solo la data nel formato necessario (solo anno e mese).

2. Visualizzare la media del latte lavorato giornalmente nell’anno corrente dai caseifici provincia per provincia;

   SELECT AVG(qta) as 'Media latte', provincia FROM lavorazionelatte, caseifici, comuni WHERE (lavorazionelatte.idcaseificio = caseifici.idcaseificio) and (caseifici.idcomune = comuni.idcomune) and DATE_FORMAT(data, '%Y') = YEAR(CURRENT_TIMESTAMP) GROUP BY provincia

   DATE_FORMAT(data, '%Y') serve a estrapolare solo l'anno nel formato aaaa dal campo data; YEAR(CURRENT_TIMESTAMP) invece serve a ricavare l'anno corrente in un database MySql. Per SQL server invece si potrebbe usare DATEPART(yyyy, getdate()) oppure: YEAR(getdate()) (fonte stackoverflow)

   Ancora tre query

3. Visualizzare i dati del caseificio che ha venduto il maggior numero di forme di prima scelta in un anno impostato dall’utente;

   SELECT count(idforma) as numforme, caseificio.nome as 'caseificio', titolare, indirizzo, comune FROM caseifici, forme, vendita, acquirente, comuni WHERE (vendita.iforma=forme.idforma) and (vendita.idacquirente=acquirenti.idacquirente) and (caseificio.idcomune=comuni.idcomune) and (caseificio.idcaseificio = forme.idcaseificio) and (scelta=1) and (DATE_FORMAT(meseanno, '%Y')=[inserisci anno (aaaa)]) GROUP BY idcaseificio ORDER BY numforme DESC LIMIT 1

4. Visualizzare l’elenco dei caseifici che, in un certo periodo individuato da due date fornite dall’utente, hanno venduto meno di 10 forme di seconda scelta ;

   SELECT count(idforma) as numforme, caseifici.nome as 'caseificio', tiolare, indirizzo, comune FROM caseifici,forme, vendita, acquirenti, comuni WHERE(vendita.iforma = forme.idforma) and (vendita.idacquirente = acquirenti.idacquirente) and (caseifici.idcaseificio = forme.idcaseificio) and (caseifici.idcomune=comuni.idcomune) and ((DATE_FORMAT(meseanno, '%Y-%m')>=[inserisci anno e mese di inizio (aaaa/mm)]) and (DATE_FORMAT(meseanno,'%Y-%m')<=[inserisci anno e mese di fine (aaaa/mm)]) ) and(numforme<10) and (scelta=2) GROUP BY idcaseificio

5. calcolare la percentuale di forme di seconda scelta prodotte annualmente da un certo caseificio (sul totale delle forme da lui prodotte annualmente).

   in lavorazione

   NB: le query qui presenti non sono state provate, quindi sono possibili errori od omissioni.
   su

Il progetto della Home Page si può fare semplicemente disegnando i componenti grafici importanti della pagina web e quindi ad esempio può essere fatto come quello presente qui
su

La codifica della parte di php si trova qui
su

I punti 1 e 2 sono in lavorazione

Punto 3

Illustri, anche servendosi di esempi, il concetto di “vista” in una base di dati.

In una base di dati, una vista è una relazione virtuale, nel senso che appare all'utente come ogni altra relazione (tabella), ma in realtà i dati presenti in essa non sono fisicamente memorizzati nella vista, ma sono derivati dalla/e tabella/e reale/i a cui la vista si riferisce. La vista, in generale, può essere usata anche per effettuare un'operazione di aggiornamento (update); tale operazione fa sì che, le modifiche apportate siano poi fisicamente attivate sulla/e tabella/e reale/i sottostante/i.
Le viste vengono create con il comando CREATE VIEW seguito da una clausola SELECT. Un esempio di creazione di una vista che usa le tabelle del nostro schema logico è il seguente:

create view fotocaseificio      select * from foto, caseifici      where foto.idcaseificio=caseifici.idcaseificio and caseificio.nome="zagaroli"

Per concludere, bisogna dire che il meccanismo delle viste risolve il problema di salvataggio di dati in tabelle di appoggio che comporta:

• dati sovrabbondati che richiedono spazio
• dati ridondanti che devono essere mantenuti facendo in modo che ogni cambiamento dei dati sulle tabelle si rifletta anche sulle copie in modo tale da evitare inconsistenze.

NB: altre informazioni sulle viste si possono reperire sul sito html.it

Punto 4

Descriva le tipologie di linguaggi utilizzate in un DBMS.

Un DBMS (Databse Management System) supporta 4 tipi di linguaggi distinti in base alle funzioni svolte sui dati che sono:

• DDL= Data Definition Language.
  E' il linguaggio che serve per la creazione dello schema logico e cioè delle relazioni (tabelle) e quindi successivamente anche per la modifica della struttura di una tabella (aggiunta o rimozione di colonne, cambio del nome e/o del tipo di dati di una colonna) o della sua eliminazione. Del DDL fanno parte le seguenti istruzioni: Create Database, Create Table, Alter Table, Drop Table
• DML= Data Manipulation Language
  Serve per l'inserimento, la cancellazione e la modifica dei dati interni alla tabella (cioè nelle varie righe). Del DML fanno parte le seguenti istruzioni: Insert Into, Delete From, Update
• DQL= Data Query Language
  Di questo linguaggio fa parte l'istruzione SELECT che serve a selezionare, ricercare, estrapolare dati da una o più tabelle collegate tra di loro in maniera diretta (tramite chiave esterna), o indiretta (tramite tabelle intermedie), sulla base di uno o più criteri (specificati tramite la clausola WHERE) eventualmente raggruppati (clausola GROUP BY) od ordinati (clausola ORDER BY)
• DCL= Data Control Language
  E' il linguaggio contenente le istruzioni GRANT e REVOKE utili a:
  • garantire (GRANT) e cioè concedere
  • revocare (REVOKE) ovvero togliere
  privilegi di accesso, modifica, creazione, inserimento ecc. (in generale ad effettuare ogni istruzione SQL) su ogni tabella del database ad ogni singolo utente.

su

Potete trovare nei link seguenti altri due svolgimenti di questa prova. Usatele ad esempio per un confronto con la modalità seguita nella propria classe, oppure per estrapolare una diversa analisi della realtà:

• Svolgimento della simulazione d'esame proposta dal MIUR il 16 aprile 2015.
• Svolgimento proposto dal Prof. Mauro De Berardis ITT "Alessandrini" di Teramo

su