Prova scritta informatica anno 2006

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:

  1. un amministratore abbia accesso a tutte le aree protette del portale;
  2. un docente abbia accesso a tutte le aree protette tranne che all’area di amministrazione;
  3. 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

email

 

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 [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':'&nbsp;') ?></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>  &emsp;&emsp;
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> &emsp;&emsp; 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

 

Please follow and like us:

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

Commenti recenti

Working Hours

  • Monday9am - 6pm
  • Tuesday9am - 6pm
  • Wednesday9am - 6pm
  • Thursday9am - 6pm
  • Friday9am - 6pm
  • SaturdayClosed
  • SundayClosed
Latest Posts

Teachers

Facebook
Twitter
LinkedIn
Contatti

Commenti recenti

Wordpress Social Share Plugin powered by Ultimatelysocial
error

Enjoy this blog? Please spread the word :)

%d blogger hanno fatto clic su Mi Piace per questo: