Traccia
Una rete di scuole chiede che sia progettato e realizzato un database per l’organizzazione e la gestione del portale di una community di apprendimento sul Web.
L’organizzazione della community prevede che:
• l’accesso sia consentito ai soli utenti registrati;
• gli utenti siano distribuiti in tre gruppi: amministratore, docente, studente tali che:
- un amministratore abbia accesso a tutte le aree protette del portale;
- un docente abbia accesso a tutte le aree protette tranne che all’area di amministrazione;
- uno studente abbia accesso alla propria area protetta e non abbia accesso né all’area di amministrazione né all’area riservata al gruppo docente;
• la registrazione degli utenti consenta:
alla rete di scuole di acquisire informazioni, sotto forma di dati non sensibili, relative agli utenti quali, ad esempio, nome e cognome, scuola o istituzione formativa di appartenenza, collocazione geografica, e-mail, ecc….
agli utenti di scegliere un nome utente, una password e il gruppo di appartenenza tranne quello degli amministratori;
• agli utenti registrati, ciascuno per il proprio gruppo di appartenenza, sia consentito di effettuare l’upload di documenti multimediali archiviando:
il titolo il tipo di documento (testo, audio, ecc.) una descrizione sintetica la data di upload i dati personali che lo riguardano;
• il gruppo degli studenti possa usufruire di moduli formativi ad esso rivolti;
• il gruppo dei docenti possa usufruire sia di propri moduli formativi che di quelli rivolti agli studenti;
• ciascun modulo formativo sia individuato da un titolo, da una breve descrizione e dal tipo di utente cui è rivolto.
Il candidato, fatte eventuali ipotesi aggiuntive fornisca:
1. Lo schema concettuale e lo schema logico del database;
2. La definizione delle relazioni in linguaggio SQL
.
− implementi almeno una delle seguenti query:
1. I docenti che hanno un account presso la community con la rispettiva collocazione geografica ed i moduli formativi scelti
.
2. I dati relativi agli studenti e ai documenti che essi hanno inviato in remoto sul portale della community mediante upload.
− scriva, in un linguaggio lato server, il codice di almeno una delle seguenti pagine del portale:
•con accesso riservato all’amministratore, il report che trae i dati dalla query n. 1
•con accesso riservato agli studenti, il report che trae i dati dalla query n. 2
• registrazione di un nuovo utente con eventuale invio automatico dei dati registrati mediante e-mail diretta all’utente appena registrato e ad un amministratore.
Soluzione
Analisi
Il problema proposto richiede un portale lms per la gestione di moduli didattici. Tale portale è accessibile previa registrazione tramite nome utente e password. Prevede la gestione dei ruoli da parte dell’amministratore e l’upload di materiale da parte di tutti gli utenti.. Un esempio di LMS è MOODLE. Non essendo possibile sviluppare un intero sistema LMS è necessario porre dei limiti al progetto che svilupperemo.
Ipotesi
Strumenti hardware/software da utilizzare
• Database MySql
• Web Server Apache
• Linguaggio lato server Php
• Implementazione pagine Web con HTML,CSS e Java Script
Iscrizione
Per l’iscrizione di nuovi utenti usiamo, la conferma tramite e-mail inviata all’utente che crea un nuovo account.
Se i dati sono corretti l’iscrizione viene registrata come non confermata e viene inviata una mail all’indirizzo di posta specificato contenente un link ad una pagina di conferma della registrazione.
Ogni nuovo utente è di tipo studente e una volta confermata l’email può iscriversi a uno o più moduli. Il passaggio al ruolo di docente o amministratore deve essere effettuato da un amministratore.
Accesso autenticato
Le credenziali per l’accesso sono registrate nella banca dati sotto forma di username e password cifrata con la codifica MD5.
Una volta fatto l’accesso si crea una sessione che sarà mantenuta fino alla chiusura del browser oppure al logout dell’utente.
I moduli
I moduli possono essere creati dai docenti oppure dagli amministratori e sia i studenti che i docenti possono inserire materiali che verrano registrati sulla banca dati tramite un link, il nome dei file viene rinominato con ponendo l’user di che ha postato il file con il nome assegnato alla risorsa.
Modello Concettuale
Entità.
Utente: che viene identificato tramite l’user e deve riportare l’anagrafica degli utenti ,la password per poter accedere al sito, email, il ruolo che può assumere solo i valori (s=studente, d=docente, a=amministratore con valore di default s).
confermato: boolenano che indica se l’iscrizione è confermata
codice di conferma: stringa casuale usata per la verifica della conferma
Residenza: con i campi città provincia e cap (per le città multi cap si assegna il cap primario)
Istituto scolastico: individuato tramite un codice e una descrizione.
Modulo è l’entità che caratterizza i contenuti della community. Identificato univocamente da un codice artificiale e dalla descrizione: testo che descrive la natura del modulo studente_docente: che distingue i moduli per docenti (accessibili ad amministratore e docenti) dai moduli per studenti (accessibili a tutti)
documento: il documento rappresenta una singola unità di contenuto delle risorse è identificato univocamente da un codice e dagli attributi
· titolo: testo che descrive il titolo del documento
· descrizione: testo che riassume il contenuto del documento
· data: data di caricamento del documento
tipo di documento che ne definisce il formato di archiviazione;
Link al file caricato.
Per il tipo possiamo fare un’entità di lookup
tipo Identificato da un codice e da una descrizione contenente tutti i possibili tipi di documenti multimediali.
Associazioni
Associazione parziale Città utente 1:N risiede:
Regola di lettura
ogni utente deve risiedere in una città
In ogni città può risiedere uno o più utente
Associazione parziale istituto utente 1:N appartiene:
Regola di lettura
ogni utente deve appartenere a un istituto
a ogni istituto può appartenere uno o più utente
Associazione parziale totale utente modulo N:M iscritto
Regola di lettura
ogni utente può essere iscritto a uno o più moduli.
A ogni modulo può essere iscritto, uno o più utenti
Associazione parziale modulo documento 1:N contiene:
Regola di lettura
ogni documento deve essere contenuto in un modulo
Ogni modulo può contenere uno o più documenti
Associazione parziale utente documento 1:N carica:
Regole di lettura
ogni documento deve essere caricato ad un utente
Ogni utente può aver caricato uno o più documenti
Associazione parziale tipo documento 1:N corrisponde:
Regole di lettura
ogni documento deve corrispondere un tipo
a ogni tipo può corrispondere uno o più documenti
Schema logico
Si usa il modello relazionale. Ogni entità diventa una relazione.
Ogni associazione 1:N tra due entità viene sostituita con l’esportazione della chiave primaria del lato 1 come chiave esterna nel lato N.
Ogni associazione N:N tra due entità viene sostituita con una nuova relazione ottenuta esportando le due chiavi primarie come chiavi esterne e definendo la loro composizione come chiave primaria della nuova relazione.
Relazioni
utenti(username(PK),password,cognome,nome,email,confermato, codiceConferma,città(FK),istituto(FK),gruppo)
città(idCittà(PK),città,provincia,cap)
istituti(istituto(PK),descrizione)
moduli(idModulo(PK), titolo,studentiDocenti)
iscrizioni(usernameo(PK),idModulo(PK))
tipi(idTipo(PK), descrizione)
documenti(idDocumento(PK),titolo, descrizione, data,id_tipo(FK), idModulo(FK), username(FK))
|
Relazione |
Campo |
Chiave |
Tipo e dim |
Vicoli |
|
utenti |
username |
PK |
varchar(20) |
|
|
password |
char(32) |
NOT NULL |
||
|
cognome |
varchar(20) |
NOT NULL |
||
|
nome |
varchar(20) |
NOT NULL |
||
|
|
varchar(20) |
NOT NULL |
||
|
confermato |
Boolean |
Default false |
||
|
codiceConferma |
char(32) |
NOT NULL |
||
|
città |
FK |
Int |
città(idCittà) |
|
|
istituto |
FK |
Int |
istituti(idIstituto) |
|
|
gruppo |
char(1) |
Check (s,d,a) |
||
|
Città |
idCittà |
PK |
Int |
auto incremento |
|
Città |
varchar(20) |
NOT NULL |
||
|
provincia |
varchar(20) |
NOT NULL |
||
|
cap |
char(5) |
NOT NULL |
||
|
istituti |
istituto |
PK |
Int |
auto incremento |
|
descrizione |
varchar(20) |
NOT NULL |
||
|
moduli |
idModulo |
PK |
Int |
auto incremento |
|
titolo |
varchar(20) |
NOT NULL |
||
|
studentiDocenti |
char(1) |
Check (s,d) |
||
|
iscrizioni |
username |
PK |
Int |
utenti(username) not Null |
|
idModulo |
PK |
Int |
moduli(idModulo) Not NULL |
|
|
tipi |
idTipo |
PK |
INT |
auto incremento |
|
descrizione |
varchar(20) |
NOT NULL |
||
|
documenti |
idDocumento |
(PK) |
INT |
auto incremento |
|
username(FK) |
||||
|
titolo |
varchar(20) |
NOT NULL |
||
|
descrizione K, |
varchar(20) |
NOT NULL |
||
|
data |
Datatime |
Data della creazione |
||
|
username |
(FK) |
INT |
utente(username) |
|
|
idTipo |
(FK) |
INT |
tipi(idTipo) |
|
|
idModulo |
(FK) |
INT |
moduli(idModulo) |
Modello Fisico
CREATE TABLE Città(
idCittà INT AUTO_INCREMENT PRIMARY KEY,
città VARCHAR(20) NOT NULL,
provincia VARCHAR(20) NOT NULL,
cap CHAR(5) NOT NULL
); CREATE TABLE istituti(
idIstituto INT AUTO_INCREMENT PRIMARY KEY,
descrizione VARCHAR(20) NOT NULL
); CREATE TABLE utenti(
username VARCHAR(20) NOT NULL PRIMARY KEY,
PASSWORD CHAR(32) NOT NULL,
cognome VARCHAR(20) NOT NULL,
nome VARCHAR(20) NOT NULL DEFAULT '',
email VARCHAR(255) NOT NULL,
idCittà INT NOT NULL,
IdIstituto INT NOT NULL,
gruppo CHAR(1) NOT NULL DEFAULT 's',
CHECK
(
gruppo = 's' OR gruppo = 'd' OR gruppo = 'a'
),
confermato BOOLEAN NOT NULL DEFAULT FALSE,
codiceConferma VARCHAR(32) NOT NULL,
FOREIGN KEY(idCittà) REFERENCES città(idCittà),
FOREIGN KEY(idIstituto) REFERENCES istituti(idIstituto)
);
CREATE TABLE moduli(
idModulo INT AUTO_INCREMENT,
descrizione VARCHAR(20) NOT NULL DEFAULT '',
docenteStudente CHAR(1) NOT NULL DEFAULT 's',
CHECK
(
docenteStudente = 's' OR docenteStudente = 'd'
),
PRIMARY KEY(idModulo)
);
CREATE TABLE iscrizioni(
username VARCHAR(20) NOT NULL,
idModulo INT NOT NULL,
PRIMARY KEY(username, idModulo),
FOREIGN KEY(username) REFERENCES utenti(username),
FOREIGN KEY(idModulo) REFERENCES moduli(idModulo)
); CREATE TABLE tipi(
idTipo INT AUTO_INCREMENT PRIMARY KEY,
descrizione VARCHAR(30) NOT NULL
); CREATE TABLE documenti(
idDocumento INT AUTO_INCREMENT PRIMARY KEY,
titolo VARCHAR(20) NOT NULL,
descrizione TEXT NOT NULL,
DATA DATETIME DEFAULT CURRENT_TIMESTAMP,
idTipo INT NOT NULL,
idModulo INT NOT NULL,
username VARCHAR(20) NOT NULL,
FOREIGN KEY(idTipo) REFERENCES tipi(idTipo),
FOREIGN KEY(idModulo) REFERENCES moduli(idModulo),
FOREIGN KEY(username) REFERENCES utenti(username)
);
Interrogazioni
n. 1: I docenti che hanno un account presso la community con la rispettiva collocazione geografica ed i moduli formativi scelti.
Si effettua un natural join sulla associazione N:N utente-iscrizione-modulo per estrarre gli utenti iscritti ai moduli. E il natural join con città. Una restrizione sul gruppo estrae gli utenti che appartengono al gruppo ‘d’(docente).
SELECT nome,cognome,città,moduli.descrizione FROM utenti NATURAL JOIN iscrizioni NATURAL JOIN moduli NATURAL JOIN Città WHERE utenti.gruppo='d'
n. 2: I dati relativi agli studenti e ai documenti che essi hanno inviato in remoto sul portale della community mediante upload.
Si effettua un natural join tra utente, documento e tipo.
Una restrizione su gruppo estrae gli utenti che appartengono al gruppo di ‘s(studente).
SELECT
nome,
cognome,
documenti.titolo AS 'documento',
tipi.descrizione AS 'Tipo di documento'
FROM
utenti
NATURAL JOIN documenti NATURAL JOIN tipi WHERE utenti.gruppo = 's'
Pagine php
Si scriva, in un linguaggio lato server, il codice di almeno una delle seguenti pagine del portale:
- con accesso riservato all’amministratore, il report che trae i dati dalla query n. 1
- con accesso riservato agli studenti, il report che trae i dati dalla query n. 2
- registrazione di un nuovo utente con eventuale invio automatico dei dati registrati mediante e-mail diretta all’utente appena registrato e ad un amministratore.
Ambiente operativo
Si ipotizza di usare una piattaforma composta da:
x sistema operativo Windows o Linux
web server Apache
sql server MySQL
script interprete PHP
Connessione al database
Il server MySQL si presenta come un server TCP che risponde sulla porta 3306. L’interpreter PHP dispone di una libreria di funzioni di interfaccia con il server MySQL.
Si può quindi realizzare uno script di connessione da includere in ogni script che necessiti dell’accesso alla banca dati.
connessione.php
<?php
//connette a MySQL server sullo stesso host,
//utente lms_utente, password ‘lms_password’
$host="localhost";
$user="lms_utente";
$pas="lms_password";
$dbname="lms";
$con=mysqli_connect($host,$user,$pas,$dbname);
if(!$con){
echo "Error: Unable to connect to MySQL." . PHP_EOL;
exit;
}
?>
Affinché la connessione abbia successo è necessario che l’amministratore del database garantisca l’accesso all’utente con il seguente DCL:
GRANT SELECT,INSERT,UPDATE,DELETE ON lms.* TO lms_u[email protected] IDENTIFIED BY ‘lems_password’
Per motivi di sicurezza l’accesso alla banca dati è limitato agli script che si trovano sullo stesso host dell’sql server (localhost). Gli script che necessitano di una connessione devono includere lo script di connessione:
<?php
//termina se l’inclusione fallisce
require_once('connessione.php');
?>
Accesso riservato all’amministratore, il report che trae i dati dalla query n. 1. Lo script verifica lo stato di autenticazione tramite la sessione che viene creata quando si effettua l’accesso. Se l’autenticazione ha successo verifica che l’utenteautenticato appartenga al gruppo degli amministratori e solo in questo caso viene effettuata la query. Se la query ha successo viene presentata la pagina.
<?php
session_start();
require 'connessione.php';
//accesso consentito solo agli amministratori
if(isset($_SESSION['username'])){
if ($_SESSION['gruppo']!='a') die('riservato agli amministratori');}
else{
header("Location: login.php");
exit();
}
$sql = "SELECT nome,cognome,città,moduli.descrizione ad 'modulo',studenteDocente"
. "FROM utenti NATURAL JOIN iscrizioni NATURAL JOIN moduli NATURAL JOIN Città"
."WHERE utenti.gruppo='d'";
$res= mysqli_query($con,$sql);
if(!$res) die("errore nella query");
?>
<html>
<head><title>query n.1</title></head>
<body>
<?php //mostra dati utente autenticato
echo $_SESSION['username']."-".$_SESSION['utente']."-"."amministratore"
?>
<table>
<tr> <!-- riga statica di intestazione -->
<td>cognome</td>
<td>nome</td>
<td>comune</td>
<td>modulo</td>
<td>solodocenti </td>
</tr>
<?php while($v =mysqli_fetch_assoc($res)) { //riga dinamica ?>
<tr>
<td><?php echo stripslashes($v['cognome']) ?></td>
<td><?php echo stripslashes($v['nome']) ?></td>
<td><?php echo stripslashes($v['città']) ?></td>
<td><?php echo stripslashes($v['modulo']) ?></td>
<td><?php echo (($v['studenteDocente']!='s')?'X':' ') ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
Con accesso riservato agli studenti, il report che trae i dati dalla query n. 2
Analoga al precedente.
<?php
session_start();
require ('connessione.php');
if(!isset($_SESSION['username'])){
header("Location: login.php");
exit();
}
$sql = "SELECT cognome,"
. "documenti.titolo AS 'documento',"
. "tipi.descrizione AS 'Tipo',data"
."FROM utenti "
."NATURAL JOIN documenti NATURAL JOIN tipi WHERE utenti.gruppo = 's'";
$res= mysqli_query($con,$sql);
if(!$res) die("errore nella query");
?>
<html>
<head><title>query n.1</title></head>
<body>
<?php
echo $_SESSION['username']."-".$_SESSION['utente']."-".$_SESSION['gruppo']
?>
<table>
<tr> <!-- riga statica di intestazione -->
<td>username</td>
<td>cognome</td>
<td>titolo</td>
<td>tipo</td>
<td>data</td>
</tr>
<?php while($v =mysqli_fetch_assoc($res)) { //riga dinamica ?>
<tr>
<td><?php echo $v['username'] ?></td>
<td><?php echo stripslashes($v['cognome']) ?></td>
<td><?php echo stripslashes($v['documento']) ?></td>
<td><?php echo stripslashes($v['Tipo']) ?></td>
<td><?php echo $row_user['data'] ?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
Registrazione di un nuovo utente.
Innanzitutto si deve realizzare una pagina contenente una form per l’immissione dei dati da inviare successivamente al server per l’elaborazione. In questa form dobbiamo recuperare dal server l’elenco delle città e delle scuole in modo da fare inserire allo studente/docente che si registra. Tramite una funzione javascript controlliamo che le due password inserite sono uguali, mentre la correttezza del formato email lo facciamo tramite l’input di tipo email, con l’attributo required invece ci accertiamo che i dati siano inseriti.
registra.php
<?php
require_once ("connessione.php");
$query = "SELECT idCittà,città FROM città";
$res=mysqli_query($con,$query);
$query = "SELECT * FROM istituti";
$res1=mysqli_query($con,$query);
if(!$res || !$res1)
die("errore nelle query");
?>
<!DOCTYPE html>
<html lang="it">
<head>
<meta charset="UTF-8">
<title>registrazione</title>
</head>
<body>
<h1>Nuovo utente</h1>
<form name="utenti" action="nuovoutente.php" method="POST">
<fieldset>
<legend>dati di accesso</legend>
username: <input type="text" name="username" placeholder="username" required><br><br>
Password: <input type="password" name="password" required>   
Conferma Password:<input type="password" name="conferma" required>
</fieldset>
<fieldset >
<legend>dati utente</legend>
Nome: <input type="text" name="nome" placeholder="nome" required><br><br>
Cognome: <input type="text" name="cognome" placeholder="Cognome" required><br><br>
E-mail:<input type="email" name="email" placeholder="indirizzo email" required><br>
<br> Città: <select name="città">
<?php while($v =mysqli_fetch_assoc($res)){ ?>
<option value="<?php echo $v['idCittà'] ?> ">
<?php echo $v['città'] ?> </option>"
<?php } ?>
</select>
   Scuola: <select name="istituto">
<?php while($v =mysqli_fetch_assoc($res1)){
echo "<option value=\"". $v['idIstituto']." \">".$v['descrizione']."</option>";
}?>
</select>
</fieldset>
<input type="submit" name="Registrati" onclick="controllo()">
</form>
<script>
function controllo(){
if(utenti.password.value===utenti.conferma.value) {
document.utenti.submit();
return true;
}
else
alert("le password non coincidono");
return false;
}
</script>
</body>
</html>
Elabora la registrazione
nuovoutente.php
Lo script controlla l’esistenza dei parametri, controlla se un username e email sono già presenti nel database, se la risposta è negativa crea una stringa casuale di conferma, inserisce i dati del nuovo utente in banca dati come utente non confermato, e viene inviata una mail contenente l’URL di conferma della registrazione con l’username ed il codice di conferma passato come parametro di GET. In caso contrario l’utente deve fare una nuova registrazione con un diverso nome utente.
<?php
if (!isset($_POST['username'])) {
echo "<script>alert(\"inserisci i dati\"); window.location.href = 'registra.php';</script>";
exit();
}else {
$username = $_POST['username'];
}
require_once ("connessione.php");
$email=$_POST['email'];
$sql="SELECT * FROM utenti where username='$username' or email='$email'";
$ris=mysqli_query($con,$sql);
if(!$ris)
die("errore nella query");
$righe= mysqli_num_rows($ris);
if($righe!=0){
echo "<script>alert(\"utente esistente \");window.location.href = 'registra.php';</script>";
}
$istituto=$_POST['istituto'];
$cognome=$_POST['cognome'];
$nome=$_POST['nome'];
$password=$_POST['password'];
$città=$_POST['città'];
$codice_conferma=bin2hex(rand(-1E+18,+1E+18)%32);//generea il codice di conferma
$p=md5($password,false);
$msg="INSERT INTO utenti (username,password,cognome,nome,email,idCittà,idIstituto,codiceConferma)"
." VALUES ('$username','$p','$cognome','$nome','$email',$città,$istituto,'$codice_conferma')";
$query=mysqli_query($con,$msg);
if(!$query) {
die("errore inserimento");
}
//invia mail all’indirizzo fornito
$subject="richiesta registrazione community";
$mailmsg= "Caro $nome $cognome,\r\nPer completare la registrazione percorri il seguente"
."link\r\n\https://lms.scuole.it/conferma_registrazione.php?username=$username&codice_conferma=$codice_conferma\r\nCordiali Saluti,\r\nl'amministratore"
."della community\r\n";
$headers="From: [email protected]\r\nReply-To: [email protected]\r\n";
@mail($email,$subject,$mailmsg,$headers);
?>
<html>
<head>
<title>Elabora registrazione</title>
<meta charset=iso-8859-1">
</head>
<body>
risposta registrazione
Grazie per richiesta la registrazione <b><?php echo $nome." ".$cognome
?></b><br>
Una mail contenente le istruzioni per completare la registrazione è stata
mandata all'indirizzo <b><?php echo $email ?></b>.<br>
A presto!
</body>
</html
Lascia un commento