Seconda prova scritta informatica 2004

TRACCIA

Il Dirigente Scolastico di una Scuola Secondaria Superiore chiede che si realizzi una base di dati per l’archiviazione e la gestione di informazioni riguardanti le attività scolastiche ed extrascolastiche

documentate, nonchÈ i risultati scolastici conseguiti da ciascuno studente al fine di produrre, in itinere e/o al termine del corso di studi, un “portfolio studente”. L’organizzazione scolastica dell’istituto prevede che:

  1. ciascuno studente possa frequentare più corsi di recupero e/o di sostegno e/o  di arricchimento dell’offerta formativa;
  2.  ogni corso abbia un titolo, una descrizione, una data di inizio e di fine, un monte ore definito;
  3. ogni studente possa frequentare più corsi esterni alla scuola;
  4.  i corsi esterni alla scuola hanno un titolo, una descrizione, una data di inizio e di fine, un monte ore definito, un riferimento che indichi l’Ente e/o l’Istituzione che li ha organizzati, un riferimento alla documentazione di accertamento;
  5. ogni classe sia individuata univocamente da un numero ordinale progressivo (I, II, III, IV,V) e da una lettera che ne indica la sezione di appartenenza (A, B, C, D, E, F, G,).

In particolare, il Dirigente Scolastico chiede che si possa procedere all’archiviazione dei:

  • dati anagrafici degli studenti utili alla loro univoca identificazione;
  • dati relativi alla frequenza delle classi del corso di studi (quali classi ciascuno studente ha frequentato in ordine crescente ed in quale anno scolastico);
  • dati relativi agli esiti conclusivi di ciascun anno scolastico (promozione sÏ/no, eventuali debiti formativi ed in quale disciplina) per ciascuno studente;
  • dati relativi ai corsi interni;
  • dati relativi ai corsi esterni alla scuola purché documentati.

Il candidato, fatte le opportune ipotesi aggiuntive, progetti una base di dati utile alla realizzazione del portfolio studente richiesto dal Dirigente Scolastico, fornendo:

  1.  uno schema concettuale della base di dati;
  2.  uno schema logico della base di dati;
  3.  la definizione delle relazioni della base di dati in linguaggio SQL;

ed inoltre:implementi in linguaggio SQL le seguenti interrogazioni:

  1. data una classe, quali studenti di quella classe hanno frequentato corsi e di che tipo;
  2. dato uno studente, quali corsi ha frequentato, di che tipo, per quale monte ore e in quale anno scolastico;
  3. dato un anno scolastico, quali corsi interni sono stati attivati e da quali studenti sono stati seguiti;
  4. dato un corso, quali sono i dati relativi ad esso e per quali anni scolastici è stato attivato;
  5. dato uno studente quali classi ha frequentato, in quali anni scolastici e con quali esiti finali.

Durata massima della prova: 6 ore. è consentito soltanto l’uso di manuali tecnici e di calcolatrici tascabili non programmabili. Non Ë consentito lasciare l’Istituto prima che siano trascorse 3 ore dalla dettatura del tema.

Analisi  e Ipotesi

Dalla testo notiamo che l‘organizzazione scolastica dell’istituto prevede che  ogni  studente può (non è obbligatorio ) frequentare più corsi sia interni/esterni. Se un corso è attivato è frequenta da almeno uno studente.

Ogni studente presente nel database deve essere iscritto almeno in una classe in un determinato anno scolastico. 

Se una classe esiste nel database almeno in un anno scolastico deve essere stata frequentata da studenti.

In un anno scolastico no studente può avere più debiti  e lo stesso tipo di debito può riguardare più studenti. 

Anagrafe studenti: per l’identificazione dello studente si inseriscono i dati Anagrafici e i dati di residenza e a ogni studente viene assegnata una matricola che diventa l’identificatore unico dell’entità studente.

Comune di nascita e di residenza,  Entità contenente la città la provincia e il cap, assumendo di usare il cap principale per quelle città che hanno cap diversi per zone diverse.

Quindi andiamo a modellare queste entità con gli attributi proposti dalla traccia:

Entità

Studente classe corsi materie debiti

Le associazioni

Tra città e studente ci suono du associazioni 1 a N  Risiede e Nato 

Tra studente e Classe  Associazione N:M frequenta con attributi AS e esito

tra studente e Materia associazione parziale N:M Debiti con attributo AS 

tra studente e corso associazione parziale M:M con attributo 

Il fatto che la query 4 ci chiede:

dato un corso, quali sono i dati relativi ad esso e per quali anni scolastici è

stato attivato. Per poter rispondere a questa query dobbiamo supporre che abbiamo un elenco di corsi che vengo attivati nei diversi anni scolatici, quindi l’anno scolastico e le date inizio e fine vanno spostate da corsi all’associazione.

Ristrutturazione

Lo Schema e/r presenta una gerarchia che arcorppiamo verso l’alto

Modello relazionale

Per il modello relazionale facciamo riferimento a un DBMS MYSQL

Ogni Entità diventa una relazione, le associazioni molti a molti diventano Relazioni, per le associazioni 1 a N la chiave primaria della relazione che partecipa con uno diventa una chiave esterna della relazione che partecipa con N. AS che è presente on diverse relazioni diventa una relazione a se e indichiamo chiave esterna il campo nella relazione. L’attributo Esito dell’associazione Frequenta diventa una tabella in cui possiamo inserire i vari esiti permessi (promosso, non promosso ecc) 

Città(idCittà (PK),città,provincia,cap);

 Discipline(idDiscilplina(PK),materia);

Studenti(matricola(PK),cognome,nome ,dataNascita,via,civico,telefono, 
nato,residente);

 AnniScolastici(idAnno (PK),ANNOSCOLASTICO );

Corsi (idCorso (PK),titolo,tipo,descrizione,dataInizio,dataFine,monteOre, internoEsterno,EnteIstituzione,documentazione,annoScolastico(FK));

FrequenzeCorsi (studente(PK),corso(PK),annoScolastico(pk),dataInisio, dataFine);

Classi (numero(pk),sezione(pk));


FrequenzeClassi (studente(PK),classe(PK),sezione(pk),annoScolastico(PK), esito(FK));

Debiti (studente(PK),disciplina(PK),annoScolastico(PK))

Esiti(idEsito(pk),Esito);

Relazione

Attributo

Chiave

Tipo dimensione

Vincoli

Città

idCittà

PK

int

AUTO_INCREMENT

città v

 

varchar(10)

NOT NULL

provincia

 

varchar(15)

NOT NULL

cap

 

char(5)

NOT NULL

Discipline

idDiscilplina

PK

int

AUTO_INCREMENT

materia

 

varchar(10)

NOT NULL

Studenti

matricola

PK

CHAR(7)

 

cognome   

 

VARCHAR(20)

NOT NULL,

nome   

 

VARCHAR(20)

NOT NULL,

dataNascita 

 

DATE

NOT NULL,

via 

 

VARCHAR(20)

NOT NULL,

civico

 

varchar(8)

 

telefono

 

VARCHAR(15)

 
   

INT

Città(idCittà)

residente

 

INT

Città(idCittà)

AnniScolastici

idAnno

PK

INT

AUTO_INCREMENT

annoScolastico

 

char(9)

 

Corsi

idCorso    

PK

INT

AUTO_INCREMENT

titolo   

 

VARCHAR(50)

NOT NULL,

tipo           

 

  ENUM

(‘Recupero’, ‘Sostegno’, ‘Arricchimento’)

Descrizione

 

VARCHAR(255)

 

monteOre

 

INT

 

internoEsterno

 

ENUM

(‘Interno’, ‘Esterno’),

EnteIstituzione 

 

VARCHAR(50)

 

documentazione

 

Text

 

FrequenzeCorsi


 

 

 

studente

PK

CHAR(7)

NOT NULL studenti (matricola),

corso   

PK

int

NOT NULL corsi (idCorso)

annoScolastico 

pk

INT

NOT NULL AnniScolatici(idAnno)

dataInizio      

 

   DATE

 

dataFine     

 

   DATE

DataInizio<dataFine

Classi

Numero   

 

VARCHAR(2)

NOT NULL

(‘I’, ‘II’, ‘III’, ‘IV’, ‘V’)

Sezione

 

CHAR(1)

NOT NULL

FrequenzeClassi

studente

PK

CHAR(7)

NOT NULL studenti (matricola),

Classe

PK

varchar(2)

NOT NULL Classi (numero)

Sezione

PK

CHAR(1)

NOT NULL Classi(sezione)

annoScolastico

PK

INT

NOT NULL AnniScolatici(idAnno)

esito

FK

Int

Esiti(idEsito)

Debiti

studente

PK

CHAR(7)

NOT NULL studenti (matricola),

disciplina

PK

INT

NOT NULL Discipline(idDisciplina)

annoScolastico

PK

INT

NOT NULL AnniScolatici(idAnno)

Esiti

idEsito

PK

INT

AUTO_INCREMENT

 

esito

 

varchar(10)

NOT NULL

CREATE DATABASE studenti;

CREATE TABLE città (
    idCittà INT AUTO_INCREMENT PRIMARY KEY,
    città VARCHAR(10) NOT NULL,
    provincia VARCHAR(15) NOT NULL,
    cap CHAR(5) NOT NULL
);

 CREATE TABLE Discipline (
    idDisciplina INT AUTO_INCREMENT PRIMARY KEY,
    materia VARCHAR(10) NOT NULL
); 
 
CREATE TABLE Studenti (
    matricola CHAR(7) PRIMARY KEY,
    cognome VARCHAR(20) NOT NULL,
    nome VARCHAR(20) NOT NULL,
    dataNascita DATE NOT NULL,
    via VARCHAR(20) NOT NULL,
    civico VARCHAR(8),
    telefono VARCHAR(15) NOT NULL,
    nato INT NOT NULL,
    residente INT NOT NULL,
    FOREIGN KEY (nato)
        REFERENCES Città (idCittà),
    FOREIGN KEY (residente)
        REFERENCES Città (idCittà)
);

 CREATE TABLE AnniScolastici (
    idAnno INT AUTO_INCREMENT PRIMARY KEY,
    annoScolastico CHAR(9)
); 

 CREATE TABLE Corsi (
    idCorso INT AUTO_INCREMENT PRIMARY KEY,
    titolo VARCHAR(50) NOT NULL,
    tipo ENUM('Recupero', 'Sostegno', 'Arricchimento') NOT NULL,
    descrizione VARCHAR(255),
    nonteOre INT,
    internoEsterno ENUM('Interno', 'Esterno'),
    enteIstituzione VARCHAR(50),
    documentazione TEXT
   ); 

 CREATE TABLE FrequenzeCorsi (
    studente CHAR(7) NOT NULL,
    corso INT NOT NULL,
    annoScolastico INT NOT NULL,
    dataInizio DATE NOT NULL,
    dataFine DATE NOT NULL,
    PRIMARY KEY (studente , corso , annoScolastico),
    FOREIGN KEY (studente)
        REFERENCES studenti (matricola),
    FOREIGN KEY (corso)
        REFERENCES corsi (idCorso),
    FOREIGN KEY (annoScolastico)
        REFERENCES AnniScolastici (idAnno),
         FOREIGN KEY (annoScolastico)
        REFERENCES AnniScolastici (idAnno),
    CHECK (dataFine > dataInizio)
);

 CREATE TABLE Classi (
    numero VARCHAR(2) NOT NULL,
    sezione CHAR(1) NOT NULL,
    PRIMARY KEY (numero , sezione),
    CHECK (Numero IN ('I' , 'II', 'III', 'IV', 'V'))
); 

CREATE TABLE Esiti (
    idEsito INT AUTO_INCREMENT PRIMARY KEY,
    esito VARCHAR(50)
);

 CREATE TABLE FrequenzeClassi( 
 studente char(7) NOT NULL, 
 classe VARCHAR(2) NOT NULL,
 sezione CHAR(1) NOT NULL,
 annoScolastico int(11) NOT NULL, 
 esito int(11) DEFAULT NULL, 
 PRIMARY KEY (studente,classe,sezione,annoScolastico), 
 FOREIGN KEY (studente) REFERENCES Studenti (matricola),
 FOREIGN KEY (classe,sezione) REFERENCES Classi (numero,sezione),
 FOREIGN KEY (esito) REFERENCES Esiti (idEsito),
 FOREIGN KEY (annoScolastico) REFERENCES AnniScolastici (idAnno)
) ; 

CREATE TABLE Debiti (
    studente char(7),
    disciplina INT,
    annoScolastico INT,
    PRIMARY KEY (studente , disciplina , annoScolastico),
    FOREIGN KEY (studente)
        REFERENCES Studenti (matricola),
    FOREIGN KEY (disciplina)
        REFERENCES Discipline (idDisciplina),
    FOREIGN KEY (annoScolastico)
        REFERENCES AnniScolastici (idAnno)
);

Query

  1. data una classe, quali studenti di quella classe hanno frequentato corsi e di che tipo;
SELECT DISTINCT Studenti.nome, Studenti.cognome, Corsi.titolo, Corsi.tipo FROM  FrequenzeClassi 
join  Studenti ON matricola=studente JOIN FrequenzeCorsi on Studenti.matricola=FrequenzeCorsi.studente JOIN Corsi ON idCorso=FrequenzeCorsi.corso 
WHERE FrequenzeClassi.classe ='I' AND sezione='A'
oppure 


 

  1. dato uno studente, quali corsi ha frequentato, di che tipo, per quale monte ore e in quale anno scolastico;

Supponendo di conosce la matricola di uno studente

SELECT Corsi.Titolo, Corsi.Tipo, Corsi.nonteOre,
AnniScolastici.annoScolastico
FROM Corsi INNER JOIN FrequenzeCorsi
ON Corsi.idCorso = FrequenzeCorsi.corso join AnniScolastici on AnniScolastici.idAnno=FrequenzeCorsi.annoScolastico
WHERE FrequenzeCorsi.studente = [matricola]

 

  1. dato un anno scolastico, quali corsi interni sono stati attivati e da quali studenti sono stati seguiti;
SELECT
     cognome,
     nome,
    Corsi.titolo,
    Corsi.Tipo,
    AnniScolastici.annoScolastico
FROM
    Corsi
JOIN FrequenzeCorsi ON Corsi.idCorso = FrequenzeCorsi.corso
JOIN Studenti ON FrequenzeCorsi.studente = Studenti.matricola
JOIN AnniScolastici ON AnniScolastici.idAnno = FrequenzeCorsi.annoScolastico
WHERE
    AnniScolastici.annoScolastico = '2000/2001' AND Corsi.internoEsterno='Interno'

 

  1. dato un corso, quali sono i dati relativi ad esso e per quali anni scolastici è stato attivato;
SELECT DISTINCT
    Corsi.tipo,
    Corsi.titolo,
    Corsi.descrizione,
    Corsi.nonteOre AS 'Monte Ore',
    Corsi.internoEsterno AS 'Interno/Esterno',
    AnniScolastici.annoScolastico
FROM
    Corsi
JOIN FrequenzeCorsi ON corsi.idCorso = FrequenzeCorsi.corso
JOIN AnniScolastici ON AnniScolastici.idAnno = FrequenzeCorsi.annoScolastico
WHERE
    Corsi.idCorso = 2
  1. dato uno studente quali classi ha frequentato, in quali anni scolastici e con quali esiti finali.
SELECT
    cognome,
    nome,
    classe,
    sezione,
    Esiti.esito,
    AnniScolastici.annoScolastico
FROM
    Studenti
JOIN FrequenzeClassi ON Studenti.matricola = FrequenzeClassi.studente
JOIN Esiti ON FrequenzeClassi.esito = Esiti.idEsito
JOIN AnniScolastici ON FrequenzeClassi.annoScolastico = AnniScolastici.idAnno
WHERE
    Studenti.matricola = '000001'

 

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: