Documentazione palestra

Esercitazione di laboratorio

Corso di Gestione dell’Informazione

A.A. 2008/09

Docenti: Valtolina, Mesiti

Titolo esercitazione

Palestra srl

Gruppo:

Massimo Santicchi [email protected] [referente]

Andrea Lanza [email protected]

Stefano Manicone [email protected]

Docente responsabile:

Stefano Valtolina

Data consegna:

Aprile 2009

1. Analisi del dominio e assunzioni

1.1 Analisi del dominio

Alla palestra hanno accesso due tipi diversi di persone: gli abbonati e gli utenti occasionali. Gli abbonati sono titolari di un abbonamento alla palestra, mentre gli utenti occasionali accedono alla palestra tramite il pagamento di un'entrata a prezzo fisso(in Euro) e non sono titolari di nessun tipo di abbonamento.

Per ogni persona che ha accesso alla palestra si vogliono memorizzare le seguenti informazioni: nome, cognome, codice fiscale, indirizzo(via, n civ, città), numero di telefono(casa o cellulare), data di nascita, luogo di nascita ed eventuali note relative al cliente considerato. Gli abbonati alla palestra hanno una tessera identificata univocamente da un codice di tessera.

Gli abbonati si suddividono in due categorie: studenti e lavoratori. Gli studenti hanno uno sconto del 20% sul costo di tutti i tipi di abbonamento, mentre i lavoratori di enti convenzionati con la palestra hanno uno sconto variabile a seconda dell'ente di appartenenza. Si noti che tutti i lavoratori appartenenti allo stesso ente hanno lo stesso sconto su tutti i tipi di abbonamento.

Ogni abbonato è titolare di un certo tipo di abbonamento. Ogni tipo di abbonamento è identificato da un codice e dà diritto a frequentare solo i corsi o solo la sala pesi o entrambi. Inoltre, ogni abbonamento ha una durata (che varia fra un anno, sei mesi, tre mesi, un mese)(in mesi), un costo(in Euro), un campo per memorizzare eventuali note relative all'abbonamento e un campo bonus in cui si memorizza un eventuale bonus a cui l'abbonamento dà diritto (lampade solari gratuite, buoni sconto presso negozi di articoli sportivi, ecc.).

Quando una persona si abbona alla palestra si vogliono memorizzare informazioni relative alla data di inizio e di scadenza dell'abbonamento. Queste informazioni devono essere coerenti con quelle relative al tipo di abbonamento scelto. L'accesso di un abbonato alla palestra è concesso solo ed unicamente entro tali date. Un abbonato può iscriversi alla palestra senza portare il certificato medico. Ha tempo un mese a decorrere dalla data di iscrizione per portare il certificato medico. Dopo un mese non viene più ammesso alla palestra se ancora senza certificato medico.

Quando una persona rinnova l'abbonamento le informazioni relative al precedente abbonamento sono cancellate dalla base di dati. Non si è quindi interessati ad informazioni relative alla storia degli abbonamenti di un abbonato.

Nella palestra si tengono dei corsi. I corsi sono caratterizzati da un codice del corso, che li identifica univocamente, un tipo (aerobica, step, kick boxing, ecc.), il giorno della settimana(uno solo) in cui si tiene il corso, un orario (esempio: 9 AM-10 AM)1, nome del preparatore atletico(codice preparatore) che tiene il corso, grado di difficoltà del corso (da 1 a 5 dove 1 indica i corsi per principianti e 5 i corsi avanzati). Si noti che esiste una sola zona della palestra dove si fanno i corsi, quindi non è possibile effettuare due corsi diversi contemporaneamente. Ogni preparatore atletico non può dedicare più di tre ore al giorno ai corsi. Il sabato pomeriggio (13 PM in poi) non si fanno i corsi. Gli orari della palestra sono (9 AM-10 PM) tutti i giorni esclusa la domenica in cui la palestra è chiusa.

Per quello che riguarda il body building, esistono un insieme di schede predefinite da assegnare agli abbonati che hanno accesso alla sala pesi. Tali schede sono caratterizzate da un codice di scheda che le identifica, un grado di difficoltà (analogo a quello dei corsi), il nome del preparatore atletico(codice preparatore) che ha creato la scheda, se la scheda è adatta solo agli uomini, solo alle donne o ad entrambi, il tipo della scheda (circuito aerobico, potenziamento, tonificazione, ecc.), il tempo medio stimato per effettuare la scheda (un'ora, due ore, ecc.), per quanto tempo deve essere fatta la scheda (una settimana, un mese, due settimane, ecc.)(in settimane), quante volte alla settimana deve essere fatta la scheda. Ad ogni scheda è associato un certo numero di esercizi. Ogni esercizio è caratterizzato dal nome della macchina su cui fare l'esercizio e la descrizione dell'esercizio stesso. Si vuole inoltre tenere traccia del numero di ripetizioni per ogni serie e del numero di serie che devono essere effettuate per un dato esercizio relativamente ad una scheda.

Quando una persona si iscrive alla palestra con accesso alla sala pesi le viene assegnata obbligatoriamente una scheda. Si osservi che le schede hanno dei vincoli sul sesso della persona che svolge la scheda. Si vogliono tenere informazioni relative alla data in cui tale scheda è stata assegnata, la data in cui si presume di cambiare scheda e il preparatore atletico(codice preparatore) che la assegna, che può essere diverso da quello che ha creato la scheda. Una volta raggiunta la data in cui si presume di cambiare scheda il preparatore atletico convoca l'abbonato per effettuare un eventuale cambio di scheda o confermare quella attualmente assegnata. È possibile cambiare la scheda attualmente assegnata solo con una di 1 grado di difficoltà più alto di quella in corso, con una minore o con una di pari grado. Si può eventualmente confermare quella attualmente in uso. Va conservata nella base di dati la storia delle schede assegnate ad una persona fino a quando tale persona è iscritta alla palestra.

Si vogliono gestire, in modo consistente, attraverso la base di dati le entrate delle persone nella palestra. Per ogni entrata si memorizza la data, l'ora(es :10.30 o 22.30) di entrata e di uscita. Nel caso in cui una persona entri per frequentare una o più ore di corsi si vuole memorizzare quali corsi la persona ha seguito. Gli utenti occasionali dopo cinque entrate hanno diritto ad un'entrata gratis. Per gli abbonati le entrate sono vincolate al tipo di abbonamento che una persona possiede. Alla data di scadenza di un abbonamento si vogliono cancellare le entrate relative a quell'abbonamento.

1.2 Assunzioni

• Non è possibile associarsi ad un ente quando è già in corso l’abbonamento

• Un tupla della tabella utente deve necessariamente partecipare o alla tabella cliente o alla tabella dipendente

• I clienti occasionali non possiedono una tessera

• Lo username corrisponderà all’email inserita durane l’iscrizione sia per i clienti che per i dipendenti della palestra

• Non è necessario che un cliente effettui un’entrata

• Non tutti gli abbonamenti devono essere necessariamente associati ad un ente

• Una tessera può rimanere in archivio anche con l’abbonamento scaduto

• Non esistono distinzioni tra le entrate dei clienti

• La data di scadenza di un Abbonamento viene calcolata come somma tra la data in cui

l’abbonamento è stipulato e l’intervallo di giorni di validità di esso;

• L’iscrizione di un cliente alla palestra non può avvenire autonomamente, ma solo tramite u dipendente

• Un cliente abbonato può iscriversi ad un corso tramite l’applicativo php

• Una Scheda può non contenere esercizi

• Un Dipendente può essere sia preparatore atletico che no ma ha accesso a tutte le funzioni

dell’applicativo

• Un esercizio può non far parte di alcuna scheda

• Un Dipendente potrebbe non tenere alcun Corso, o non aver creato/assegnato alcuna Scheda

• In base all’abbonamento sottoscritto dal cliente, egli avrà accesso alla sala pesi e/o ai corsi

• Un Cliente non può effettuare più di una Entrata alla stessa ora nella stessa data

• Si suppone che l’ora di uscita sia successiva all’ora di entrata (supponiamo un entrata con badge)

2. Progettazione Concettuale

2.1 Dizionario dei dati Legenda: _____ = entità _____ = associazioni _____ = attributi

Alla palestra hanno accesso due tipi diversi di persone: gli abbonati e gli utenti occasionali. Gli abbonati sono titolari di un abbonamento alla palestra, mentre gli utenti occasionali accedono alla palestra tramite il pagamento di un'entrata a prezzo fisso(in Euro) e non sono titolari di nessun tipo di abbonamento.

Per ogni persona che ha accesso alla palestra si vogliono memorizzare le seguenti informazioni: nome, cognome, codice fiscale, indirizzo(via, n civ, città), numero di telefono(casa o cellulare), data di nascita, luogo di nascita ed eventuali note relative al cliente considerato. Gli abbonati alla palestra hanno una tessera identificata univocamente da un codice di tessera.

Gli abbonati si suddividono in due categorie: studenti e lavoratori. Gli studenti hanno uno sconto del 20% sul costo di tutti i tipi di abbonamento, mentre i lavoratori di enti convenzionati con la palestra hanno uno sconto variabile a seconda dell'ente di appartenenza. Si noti che tutti i lavoratori appartenenti allo stesso ente hanno lo stesso sconto su tutti i tipi di abbonamento.

Ogni abbonato è titolare di un certo tipo di abbonamento. Ogni tipo di abbonamento è identificato da un codice e dà diritto a frequentare solo i corsi o solo la sala pesi o entrambi. Inoltre, ogni abbonamento ha una durata (che varia fra un anno, sei mesi, tre mesi, un mese)(in mesi), un costo(in Euro), un campo per memorizzare eventuali note relative all'abbonamento e un campo bonus in cui si memorizza un eventuale bonus a cui l'abbonamento dà diritto (lampade solari gratuite, buoni sconto presso negozi di articoli sportivi, ecc.).

Quando una persona si abbona alla palestra si vogliono memorizzare informazioni relative alla data di inizio e di scadenza dell'abbonamento. Queste informazioni devono essere coerenti con quelle relative al tipo di abbonamento scelto. L'accesso di un abbonato alla palestra è concesso solo ed unicamente entro tali date. Un abbonato può iscriversi alla palestra senza portare il certificato medico. Ha tempo un mese a decorrere dalla data di iscrizione per portare il certificato medico. Dopo un mese non viene più ammesso alla palestra se ancora senza certificato medico.

Quando una persona rinnova l'abbonamento le informazioni relative al precedente abbonamento sono cancellate dalla base di dati. Non si è quindi interessati ad informazioni relative alla storia degli abbonamenti di un abbonato.

Nella palestra si tengono dei corsi. I corsi sono caratterizzati da un codice del corso, che li identifica univocamente, un tipo (aerobica, step, kick boxing, ecc.), il giorno della settimana(uno solo) in cui si tiene il corso, un orario (esempio: 9 AM-10 AM)1, nome del preparatore atletico(codice preparatore) che tiene il corso, grado di difficoltà del corso (da 1 a 5 dove 1 indica i corsi per principianti e 5 i corsi avanzati). Si noti che esiste una sola zona della palestra dove si fanno i corsi, quindi non è possibile effettuare due corsi diversi contemporaneamente. Ogni preparatore atletico non può dedicare più di tre ore al giorno ai corsi. Il sabato pomeriggio (13 PM in poi) non si fanno i corsi. Gli orari della palestra sono (9 AM-10 PM) tutti i giorni esclusa la domenica in cui la palestra è chiusa.

Per quello che riguarda il body building, esistono un insieme di schede predefinite da assegnare agli abbonati che hanno accesso alla sala pesi. Tali schede sono caratterizzate da un codice di scheda che le identifica, un grado di difficoltà (analogo a quello dei corsi), il nome del preparatore atletico(codice preparatore) che ha creato la scheda, se la scheda è adatta solo agli uomini, solo alle donne o ad entrambi, il tipo della scheda (circuito aerobico, potenziamento, tonificazione, ecc.), il tempo medio stimato per effettuare la scheda (un'ora, due ore, ecc.), per quanto tempo deve essere fatta la scheda (una settimana, un mese, due settimane, ecc.)(in settimane), quante volte alla settimana deve essere fatta la scheda. Ad ogni scheda è associato un certo numero di esercizi. Ogni esercizio è caratterizzato dal nome della macchina su cui fare l'esercizio e la descrizione dell'esercizio stesso. Si vuole inoltre tenere traccia del numero di ripetizioni per ogni serie e del numero di serie che devono essere effettuate per un dato esercizio relativamente ad una scheda.

Quando una persona si iscrive alla palestra con accesso alla sala pesi le viene assegnata obbligatoriamente una scheda. Si osservi che le schede hanno dei vincoli sul sesso della persona che svolge la scheda. Si vogliono tenere informazioni relative alla data in cui tale scheda è stata assegnata, la data in cui si presume di cambiare scheda e il preparatore atletico(codice preparaotr) che la assegna, che può essere diverso da quello che ha creato la scheda. Una volta raggiunta la data in cui si presume di cambiare scheda il preparatore atletico convoca l'abbonato per effettuare un eventuale cambio di scheda o confermare quella attualmente assegnata. È possibile cambiare la scheda attualmente assegnata solo con una di 1 grado di difficoltà più alto di quella in corso, con una minore o con una di pari grado. Si può eventualmente confermare quella attualmente in uso. Va conservata nella base di dati la storia delle schede assegnate ad una persona fino a quando tale persona è iscritta alla palestra.

Si vogliono gestire, in modo consistente, attraverso la base di dati le entrate delle persone nella palestra. Per ogni entrata si memorizza la data, l'ora(es :10.30 AM) di entrata e di uscita. Nel caso in cui una persona entri per frequentare una o più ore di corsi si vuole memorizzare quali corsi la persona ha seguito. Gli utenti occasionali dopo cinque entrate hanno diritto ad un'entrata gratis. Per gli abbonati le entrate sono vincolate al tipo di abbonamento che una persona possiede. Alla data di scadenza di un abbonamento si vogliono cancellare le entrate relative a quell'abbonamento.

Tabella Entità

Nome Descrizione Attributi Identificatori

Cliente Qualsiasi persona

iscritta alla palestra

• nome • cognome • telefono • datanascita • luogonascita • note • codf • Indirizzo come

via, nciv, cap, città e provincia

• Sesso • Email

• codf

Abbonato Un utente non

occasionale della palestra

• codf

Occasionale Un utente non abbonato

alla palestra • entrate • codf

Dipendente Tutte le persone che

lavorano nella palestra

• codd • nome • cognome • email • telefono • stipendio

• codd

Altro Tutto i dipendenti della palestra non preparatori • codd

Preparatore Una categoria di

dipendente • livelloqualifica • codd

Tessera Ciò che possiede un utente della palestra

• codt • dataiscr • datascad • hacertificato

• codt

Entrata Ciò che effettua un

cliente quando entra in palestra

• oraentrata • data • orauscita • costo

Entità debole che ha come identificatore esterno misto codf, ora entrata, data

Tabella Entità

Nome Descrizione Attributi Identificatori

Corso Attività che si svolge

nella palestra

• codc • tipo • difficolta • ora • durata • giorno

• codc

Abbonamento “Permesso speciale”

che possiede un abbonato alla palestra

• codabb • durataabb • costoabb • bonusabb • noteabb • tipoabb

• coda

Scheda “Guida”

all’allenamento

• cods • tipo • difficolta • ngiorniasett • sesso • tempoesecuzion • ngiornitotali

• cods

Utente Utente dell’applicativo

php

• username • pass • tipo

• username

Ente

Tipicamente una ditta per cui un cliente lavora

(permette di ottenere uno sconto)

• nomepubblico • sconto

• nomepubblico

Esercizio Ciò che è contenuto in

una scheda

• nomees • macchina • descrizione

• nomees

Gerarchie di generalizzazione

Entità Padre Entità Figlie Tipologia

Cliente Abbonato Occasionale

Relazione di sottoinsieme

Dipendente Preparatore Altro

Relazione di sottoinsieme

Tabella Associazioni Nome Descrizione Attributi Entità collegate

relcontienees Una scheda contiene un

esercizio • nserie • nripetizioni

Scheda, Esercizio

relvuoleeseguire In un entrata si partecipa a

un corso Corso, Entrata

reliscrittoaipesi Storico schede

• datainizioscheda

• datafinescheda

• statoscheda

Preparatore, Scheda, Tessera

reliscrittocorso In una tessera si è iscritti a

un corso Tessera, Corso

relassegna Un preparatore assegna una

scheda Preparatore,

Scheda

reltiene Un preparatore tiene un

corso Preparatore, Corso

relhaconvenzionato Una tessera può avere un

ente convenzionato Tessera, Ente

relhatessera Un abbonato ha una tessera Abbonato, Tessera

relutentecliente Un cliente è un utente della

palestra Cliente, Utente

Tabella Associazioni

Nome Descrizione Attributi Entità collegate

relcontieneabb Una tessera può contenere

un abbonamento Tessera, Abbonamento

reldipendenteutente Un dipendente è un

“utente” particolare della palestra

Dipendente, Utente

2.4 Schema ER non ristrutturato

Nome relazioni

R1=releffettua

R2=relutentecliente R3=reldipendenteutente

R4= relcontienees

R5=relassegna

R6=reltiene

R7= relvuoleesguire

R8=relhaconvenzionato

R9=relcontieneabb

R10= reliscrittoaipesi

R11= reliscrittocorso

R12=relhatessera

2.3 Vincoli di dominio

V1

la durata dell’abbonamento è 1 mese, 3 mesi, 6 mesi o un anno

Check

V2

il tipo di abbonamento può assumere i seguenti valori: “pesi”, “corsi” o “entrambi”

Check

V3 il codice fiscale deve essere di 16 caratteri Check

V4 il sesso dei client inseriti deve essere “m”(maschio) o

“f”(femmina) Check

V5 la difficoltà di un corso deve essere compresa tra “1” e

“5” Check

V6 Una scheda può essere in tre stati:“corrente”(in uso),

“abbandonata” o “conclusa” Check

V7 la difficoltà di una scheda deve essere compresa tra “1”

e “5” Check

V8 L’attributo sesso nella tabella scheda può assumere i

seguenti valori: “m”, “f” o “e”(entrambi) Check

V9 L’attributo “tipo” nella tabella “utente” può assumere

solamente i seguenti valori: “abbonato”, occasionale” o “dipendente”

Check

V10 Il sabato la palestra chiude alle 13 (controllo sui corsi che non superino l’orario di chiusura)

Trigger

V11 Due corsi non possono sovrapporsi Trigger

V12 Un preparatore non può tenere più di tre ore di corsi al giorno

Trigger

V13 Un abbonato no può entrare in palestra se il suo abbonamento è scaduto

Trigger

2.4 Schema ER ristrutturato

R1=releffettua

R2=relutentecliente R3=reldipendenteutente

R4= relcontienees

R5=relassegna

R6=reltiene

R7= relvuoleesguire

R8=relhaconvenzionato

R9=relcontieneabb

R10= reliscrittoaipesi

R11= reliscrittocorso

R12=relhatessera

3. Progettazione logica

ABBONAMENTO (codabb, tipoabbnn, durataabbnn, costoabbnn, bonusabb, noteabb) CLIENTE (codf, nomenn, cognomenn, sessonn, indirizzonn, telefononn, emailunn, datanascitann, luogonascitann, note, entrate, abbonato) CORSO (codc, giornonn, tiponn, difficoltànn, preparatore(FK→DIPENDENTE), orann, duratann) DIPENDENTE (codd, nomenn, cognomenn, ruolonn, telefononn, emailunn, preparatorenn, stipendio) ENTE (nome pubblico, scontonn) ENTRATA (codf(FK→CLIENTE), data, oraentrata, orauscita, costo) ESERCIZIO (nomees, descrizionenn, macchinann) RELCONTIENEES (cods(FK→SCHEDA), nomees(FK→ESERCIZIO), nserie, nripetizioni) RELISCRITTOAIPESI (codt (FK→TESSERA), cods (FK→SCHEDA), datainizioscheda, codprep(FK→DIPENDENTE), datafinescheda, statoschedann) RELISCRITTOCORSO (codc(FK→CORSO), codt(FK→TESSERA)) RELVUOLEESEGUIRE (data(FK→ENTRATA), ora entrata(FK→ENTRATA), codc(FK→CORSO), codf(FK→ENTRATA)) SCHEDA (cods, sessonn, tiponn, ngiornitotalinn, tempoesecuzionenn, ngiorniasettnn, preparatore(FK→DIPENDENTE), difficoltann) TESSERA (codt, codf(FK→CLIENTE), hacertificatonn, ente(FK→ENTE), dataiscr, datascad, codabb(FK→ABBONAMENTO)) UTENTE (username, passnn, tipo)

4. Comandi SQL

5.1 Implementazione in sql dei vincoli CHECK

Tabella “abbonamento”

check_durataabb CHECK (durataabb = 30 OR durataabb = 90 OR durataabb = 180 OR durataabb = 365);

controlla che la durata dell’abbonamento sia 1 mese, 3 mesi, 6 mesi o un anno

check_tipoabb CHECK (tipoabb::text = 'pesi'::text OR tipoabb::text = 'corsi'::text OR tipoabb::text = 'entrambi'::text);

controlla che il tipo di abbonamento sia “pesi”, “corsi” o “entrambi”

Tabella “cliente”

check_codf CHECK (char_length(codf::text) = 16);

controlla che il codice fiscale dei clienti sia di 16 caratteri

check_sesso CHECK (sesso = 'm'::bpchar OR sesso = 'f'::bpchar);

controlla che il sesso dei client inseriti sia “m”(maschio) o “f”(femmina)

Tabella “corso”

check_difficolta CHECK (difficolta >= 1 AND difficolta <= 5);

controlla che la difficoltà di un corso sia compresa tra “1” e “5”

Tabella “dipendente”

check_codd CHECK (char_length(codd::text) = 16);

controlla che il codice fiscale dei dipendenti abbia 16 caratteri

Tabella “reliscrittoaipesi”

reliscrittoaipesi_statoscheda_check CHECK (statoscheda::text = ANY (ARRAY['corrente'::character varying, 'abbandonata'::character varying, 'conclusa'::character varying]::text[]));

controlla in che “stato” è una scheda: se è “corrente”(in uso), “abbandonata” o “conclusa”

Tabella “scheda”

check_difficolta CHECK (difficolta >= 1 AND difficolta <= 5);

controlla che la difficoltà di una scheda sia compresa tra “1” e “5”

check_sesso CHECK (sesso = 'm'::bpchar OR sesso = 'f'::bpchar OR sesso = 'e'::bpchar);

controlla che il valore impostato nell’attributo “sesso” nella tabella scheda sia “m”(maschi), “f”(femmine) o “e”(entrambi)

Tabella “utente”

tipo_username CHECK (tipo::bpchar = 'abbonato'::bpchar OR tipo::bpchar = 'occasionale'::bpchar OR tipo::bpchar = 'dipendente'::bpchar);

controlla che l’utente della palestra sia di tipo “abbonato”, occasionale” o “dipendente”

5.2 TRIGGER in sql (implementazione dei vincoli)

I Trigger sono delle procedure che vengono eseguite in maniera automatica in coincidenza di un determinato evento, come ad esempio la cancellazione di un record di una tabella.

Riproponiamo in maniera più ordinata e unita i trigger presenti nella base di dati insieme alle funzioni a cui fanno riferimento:

Tabella “corso”

Trigger:

CREATE TRIGGER trig_checkcorsisab

BEFORE INSERT OR UPDATE

ON corso

FOR EACH ROW

EXECUTE PROCEDURE func_checkcorsisab();

Funzione:

CREATE OR REPLACE FUNCTION func_checkcorsisab()

RETURNS trigger AS

$BODY$

DECLARE

BEGIN

IF (NEW.giorno = 'sabato' AND (NEW.ora+NEW.durata)>'13:00') THEN

RAISE EXCEPTION 'La palestra al sabato chiude alle 13, i dati inseriti non rientrano nelle ore consentite';

ELSE

RETURN NEW;

END IF;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION func_checkcorsisab() OWNER TO postgres;

Questo trigger si attiva prima dell’inserimento o dell’aggiornamento di un corso e attiva la funzione che controlla che, se si inserisce un corso la cui durata supera l’ora di chiusura della palestra il sabato(ora in cui si vuole inserire il corso + durata del corso), ritorna l’errore:

“La palestra al sabato chiude alle 13, i dati inseriti non rientrano nelle ore consentite”

Trigger:

CREATE TRIGGER trig_checkcorsisovrapposti


ON corso

FOR EACH ROW

EXECUTE PROCEDURE funz_checkcorsisovrapposti();

Funzione:

CREATE OR REPLACE FUNCTION funz_checkcorsisovrapposti()

RETURNS trigger AS

$BODY$

DECLARE

tempinterval interval:='00:02:00';

BEGIN

IF EXISTS(SELECT * FROM corso c WHERE giorno = NEW.giorno AND c.codc != NEW.codc AND (NEW.ora BETWEEN c.ora AND (c.ora+(c.durata-tempinterval)))) THEN

RAISE EXCEPTION 'Errore, con questi valori dei corsi si sovrappongono';

END IF;

IF EXISTS(SELECT * FROM corso c WHERE giorno = NEW.giorno AND c.codc != NEW.codc AND (NEW.ora+NEW.durata) BETWEEN c.ora AND ((c.ora+c.durata)-tempinterval)) THEN

RAISE EXCEPTION 'Errore, con questi valori dei corsi si sovrappongono';

END IF;

RETURN NEW;

END;

$BODY$


COST 100;

ALTER FUNCTION funz_checkcorsisovrapposti() OWNER TO postgres;

Questo trigger si attiva prima dell’inserimento o dell’aggiornamento di un corso e attiva la funzione che controlla che due corsi non si sovrappongano (controlla che l’ora di un corso inserito o aggiornato non stia tra l’ora d’inizio un altro corso e la somma tra l’ora di inizio di un altro corso e la sua durata; inoltre controlla che,sommando l’ora di inizio di un corso inserito o aggiornato con la sua durata, esso non vada a sovrapporsi alle ore di un altro corso)

Trigger:

CREATE TRIGGER trig_massimotreore


ON corso

FOR EACH ROW

EXECUTE PROCEDURE func_massimotreore();

Funzione:

CREATE OR REPLACE FUNCTION func_massimotreore()

RETURNS trigger AS

$BODY$

DECLARE

totaleorecorso interval;

BEGIN

SELECT INTO totaleorecorso SUM(durata)

FROM corso

WHERE preparatore=NEW.preparatore AND

giorno=NEW.giorno AND

codc!=NEW.codc

GROUP BY(preparatore);

IF NOT FOUND THEN

totaleorecorso:='00:00:00';

END IF;

IF (totaleorecorso + NEW.durata) >'03:00:00' THEN

RAISE EXCEPTION 'non più di tre ore di corso al giorno, per ogni preparatore';

ELSE

RETURN NEW;

END IF;

END;

$BODY$


COST 100;

ALTER FUNCTION func_massimotreore() OWNER TO postgres;

Questo trigger si attiva prima dell’inserimento o dell’aggiornamento di un corso e attiva la funzione che verifica che per ogni preparatore, in uno stesso giorno, non siano associati più di tre ore di corsi; se si verifica tale condizione si avrà il messaggio:

“non più di tre ore di corso al giorno, per ogni preparatore”

Tabella “entrata”

Trigger:

CREATE TRIGGER trig_abbonamentoscadutoonentrata

BEFORE INSERT

ON entrata

FOR EACH ROW

EXECUTE PROCEDURE func_abbonamentoscadutoonentrata();

Funzione:

CREATE OR REPLACE FUNCTION func_abbonamentoscadutoonentrata()

RETURNS trigger AS

$BODY$

DECLARE

eabbonato boolean;

BEGIN

SELECT INTO eabbonato abbonato

FROM tessera t,cliente c

WHERE t.codf=NEW.codf AND c.codf = t.codf;

IF (eabbonato=TRUE) THEN

PERFORM codt

FROM tessera t

WHERE t.codf=NEW.codf AND t.codabb IS NOT NULL;

IF NOT FOUND THEN

RAISE EXCEPTION 'Abbonamento scaduto per questo utente.';

ELSE

RETURN NEW;

END IF;

ELSE

RETURN NEW;

END IF;

END;

$BODY$


COST 100;

ALTER FUNCTION func_abbonamentoscadutoonentrata() OWNER TO postgres;

questo trigger si attiva prima dell’inserimento di una tupla nella tabella “entrata”; attiva la funzione che controlla all’entrata della palestra che l’abbonamento di un utente non sia scaduto (controlla che il “codabb” non sia nullo)

Trigger:

CREATE TRIGGER trig_checkinsertonentrata

AFTER INSERT OR UPDATE

ON entrata

FOR EACH ROW

EXECUTE PROCEDURE func_checkinsertonentrata();

Funzione:

CREATE OR REPLACE FUNCTION func_checkinsertonentrata()

RETURNS trigger AS

$BODY$

DECLARE

indexgiorno integer;

engiorno character varying;

BEGIN

IF NEW.orauscita IS NOT NULL THEN

SELECT INTO indexgiorno

date_part('dow',NEW.data);

IF indexgiorno=0 THEN engiorno='domenica';

ELSEIF indexgiorno=1 THEN engiorno='lunedì';

ELSEIF indexgiorno=2 THEN engiorno='martedì';

ELSEIF indexgiorno=3 THEN engiorno='mercoledì';

ELSEIF indexgiorno=4 THEN engiorno='giovedì';

ELSEIF indexgiorno=5 THEN engiorno='venerdì';

ELSEIF indexgiorno=6 THEN engiorno='sabato';

END IF;

INSERT INTO relvuoleseguire(data,oraentrata,codf,codc)

SELECT data, oraentrata,codf,codc as codcorso

FROM corso c NATURAL JOIN reliscrittocorso ic NATURAL JOIN entrata e NATURAL JOIN tessera t WHERE (

NEW.oraentrata<(c.ora+c.durata) AND

NEW.orauscita>c.ora AND

lower(c.giorno)=engiorno AND

t.codf = NEW.codf AND NOT EXISTS(SELECT * FROM relvuoleseguire rl WHERE

rl.data=e.data AND rl.codf = t.codf AND rl.codc = c.codc));

END IF;

RETURN NEW;

END;

$BODY$


COST 100;

ALTER FUNCTION func_checkinsertonentrata() OWNER TO postgres;

questo trigger si attiva prima dell’inserimento o dell’aggiornamento di una tupla nella tabella “entrata”; attiva la funzione che inserisce nella relazione “relvuoleseguire” i corsi che un utente ha seguito

Trigger:

CREATE TRIGGER trig_ingressoconcorsi


ON entrata

FOR EACH ROW

EXECUTE PROCEDURE func_ingressoconcorsi();

Funzione:

CREATE OR REPLACE FUNCTION func_ingressoconcorsi()

RETURNS trigger AS

$BODY$

DECLARE

tipoabb character varying;

ngiorno int;

gentrata character varying;

BEGIN

SELECT INTO tipoabb ab.tipoabb

FROM abbonamento ab NATURAL JOIN tessera t

WHERE t.codf=NEW.codf;

IF tipoabb='corsi' THEN

SELECT INTO ngiorno

date_part('dow',NEW.data);

IF ngiorno=0 THEN gentrata='domenica';

ELSEIF ngiorno=1 THEN gentrata='lunedì';

ELSEIF ngiorno=2 THEN gentrata='martedì';

ELSEIF ngiorno=3 THEN gentrata='mercoledì';

ELSEIF ngiorno=4 THEN gentrata='giovedì';

ELSEIF ngiorno=5 THEN gentrata='venerdì';

ELSEIF ngiorno=6 THEN gentrata='sabato';

END IF;

if(true) THEN

PERFORM c.codc

FROM corso c NATURAL JOIN reliscrittocorso ic NATURAL JOIN tessera t

WHERE c.giorno=gentrata AND ic.codc=c.codc AND ic.codt=t.codt AND t.codf=NEW.codf AND (NEW.oraentrata BETWEEN (c.ora-'00:05:00') AND (c.ora+c.durata));

if not found then

RAISE EXCEPTION 'Nessun corso a cui il cliente è iscritto a questo orario.';

end if;

return

new;

END IF;

ELSE

RETURN NEW;

END IF;

END;

$BODY$


COST 100;

ALTER FUNCTION func_ingressoconcorsi() OWNER TO postgres;

questo trigger si attiva prima dell’inserimento o dell’aggiornamento di una tupla nella tabella “entrata”; attiva la funzione che se un cliente ha l’abbonamento solo corsi abbia effettivamente un corso.

Tabella “tessera”

Trigger:

CREATE TRIGGER trig_deleteonentrate

AFTER UPDATE

ON tessera

FOR EACH ROW

EXECUTE PROCEDURE func_deleteonentrate();

Funzione:

CREATE OR REPLACE FUNCTION func_deleteonentrate()

RETURNS trigger AS

$BODY$

DECLARE

tesseradel character varying;

BEGIN

IF(dataiscr IS NULL AND datascad IS NULL AND codabb IS NULL) THEN

SELECT INTO tesseradel t.codt

FROM cliente c NATURAL JOIN tessera t

WHERE t.codt=OLD.codt;

DELETE

FROM entrata

WHERE tessera=tesseradel;

RETURN OLD;

END IF;

END;

$BODY$


COST 100;

ALTER FUNCTION func_deleteonentrate() OWNER TO postgres;

Questo trigger si attiva dopo l’aggiornamento di una tessera; attiva la funzione che cancella le entrate di un cliente quando si cancella la tessera dello stesso

Query richieste

Dato un abbonato, restituire i dati relativi all’abbonamento attualmente in suo pos-sesso, indicando il prezzo dell’abbonamento e l’importo effettivamente pagato dall’abbonato.

Questa query è visibile nell’applicativo php nel pannello di ogni utente abbonato Dato un abbonato, restituire i suoi ingressi alla palestra nell'ultimo mese.

Questa query è visibile nelle statistiche di ogni utente abbonato

Dato un abbonato con accesso alla sala pesi, restituire la storia delle schede a lui assegnate.

Questa query è visibile nelle statistiche di ogni utente abbonato

Restituire tutti i corsi di un determinato tipo. Questa query è visibile nelle statistiche generali del sito Dato un tipo di corso restituire il numero di ore settimanali allocate per quel corso. Questa query è visibile nelle statistiche generali del sito Restituire il codice di corso mediamente più frequentato. Questa query è visibile nelle statistiche generali del sito

Data una settimana restituire il corso che ha registrato il massimo numero di presenze. Restituire, inoltre, tale numero di presenze. Questa query è visibile nelle statistiche generali del sito Restituire le schede associate più frequentemente alle donne comprese fra i 25 e i 30 anni. Questa query è visibile nelle statistiche generali del sito Restituire per ogni tipo di abbonamento la percentuale di lavoratori che lo possiede. Questa query è visibile nelle statistiche generali del sito Restituire le percentuali di abbonati alla palestra suddivisi in fasce di età (21-25, 26-30, ecc.), rispetto al totale di abbonati. Questa query è visibile nelle statistiche generali del sito

Documentazione palestra

Technology

Transcript of Documentazione palestra