Tesina basi di dati anno accademico 2016/2017

(1)

Tesina basi di dati

anno accademico 2016/2017

Giuseppe D’Alterio N46002736 Antonio Elefante N46003048

Prefazione

Lo scenario definito dall’azienda CinemaWorld prevede di fornire un Sistema Software per l’

Informatizzazione dei processi legati alla gestione dell’Acquisto, o Prenotazione, di biglietti per la visione di film presso cinema locati in territorio svizzero. Tramite il Sistema Informatico progettato Ad-Hoc `e possibile implementare il tutto in completa sicurezza, sia per il cliente che per l’esercente che potr`a cos`ı gestire con maggiore efficienza la propria multinazionale.

(2)

Introduzione L^ATEX Tesina Basi Di Dati

Specifiche di Progetto

Dalle specifiche dettate dal committente, sono emerse le seguenti richieste:

Si vuole fornire una base di dati che permetta di centralizzare una serie di servizi web per la prenotazione di biglietti a cinema collocati sul territorio svizzero (in particolare ogni societ`a gestisce la prenotazione per i cinema di un singolo cantone). Per la gestione dei dati di interesse del sistema, si vuole progettare una base di dati atta a contenere le informazioni relative a:

1. I cinema, con nome, indirizzo, citt`a, nazione, recapiti telefonici e numero di sale.

2. Le sale dei vari cinema contraddistinte da un numero, da una superficie, espressa in metri quadrati, una capienza e dai numerativi dei posti.

3. I film in programmazione, con titolo, recensione, regia, genere, anno, paese, durata, attori, data di uscita e distributore.

4. Gli utenti con username, password, cognome, nome ed e-mail.

5. La programmazione (giorno/orario/prezzo biglietto) delle proiezioni dei film nelle varie sale dei cinema.

6. Le prenotazioni ed acquisto di biglietti da parte di utenti per le varie proiezioni con la scelta dei posti.

Le informazioni relative ai cinema di un dato cantone, la relativa programmazione, gli orari, i prezzi sono di competenza delle singole societ`a locali, ciascuna delle quali ha nel tempo sviluppato una serie di informazioni riguardanti il proprio esercizio commerciale. Mentre le informazioni sui film devono essere recuperate da siti specializzati gestiti da terze parti (e.g.

imdb). Infine, il pagamento con carta di credito deve essere effettuato in modalit`a sicura sfruttando web service offerti da istituti di credito certificati.

A regime si suppone che nell’arco del prossimo anno la base di dati dovr`a gestire la seguente mole di informazioni:

• Circa 50 Cinema.

• Circa 1000 Utenti.

• Circa 1000 Film.

(3)

Introduzione L^ATEX Tesina Basi Di Dati

Specifiche sulle Operazioni

La multinazionale di informatica intende fornire ai propri utenti un servizio di tipo centralizzato, accessibile mediante interfacce web, per:

1. La consultazione della programmazione delle proiezioni di film presso i vari cinema.

2. L’acquisto e la prenotazione On-Line per una specifica proiezione.

Le operazioni previste sulla Base di Dati sono:

• Inserimento delle informazioni riguardanti i cinema di un dato cantone, la relativa programmazione, gli orari ed i prezzi, di competenza delle singole societ`a locali.

• Inserimento delle informazioni relative ai film: da recuperare da siti specializzati gestiti da terze parti.

• Inserimento in programmazione di un film.

• Prenotazione di biglietti On-Line.

• Prenotazione ed acquisto di biglietti per gli spettacoli.

• Acquisto di biglietti tramite subordinati nei botteghini dei vari cinema.

• Visualizzazione informazioni relative a film in programmazione presso i vari cinema.

• Visualizzazione informazioni relative a spettacoli presso i vari cinema.

Sono inoltre applicate le seguenti regole di Business:

• La prenotazione di uno spettacolo sar`a resa vana se non pagata entro 30 minuti dall’inizio dello spettacolo al fine di assicurare l’esercente da finte prenotazioni.

• Un film potr`a essere messo in programmazione senza associarne una sala di riferimento che sar`a poi specificata in un secondo momento.

(4)

Progettazione logica e fisica della base dati L^ATEX Tesina Basi Di Dati

Vincoli Tecnologici e Politiche di Sicurezza

L’architettura prevista per l’applicazione di gestione dei cinema `e strutturata su 3 livelli:

1. Livello presentazione in cui sono presenti le interfacce web offerte all’utente dall’applicazione.

2. Livello applicazione in cui sono presenti gli oggetti software che realizzano la vera e propria logica applicativa.

3. Livello dati in cui si trova il DBMS e le informazioni da esso gestite.

Il DBMS esegue le proprie operazioni su una macchina dotata di:

• 8GB RAM DDR4.

• HDD 10000 RPM 2x1TB (Raid 1)

La scelta di implementare lo spazio dati disponibile secondo un’impostazione Raid 1 `e stata portata avanti dai vantaggi che ne comporta:

1. Affidabilit`a, cio`e resistenza ai guasti, che aumenta linearmente con il numero di copie.

2. Maggior velocit`a di lettura.

Sono previste 2 categorie di utenti che interagiscono con il sistema:

• DBA: Amministratore del DataBase.

• Utente ”Web Application” cui `e offerta la possibilit`a di poter gestire la visione di un relativo spettacolo in completa autonomia.

(5)

Progettazione della Base di Dati L^ATEX Tesina Basi Di Dati

Terminata la fase di raccolta delle regole progettuali, si deve procedere con una loro analisi prima di avviare lo studio e la progettazione concettuale, logica e fisica.

Analisi delle Specifiche e Ristrutturazione dei Requisiti

Al fine di avere una visione pi `u chiara di quelli che sono i requisiti statici richiesti dall’applicazione, si `e scelto di effettuare una prima settorializzazione suddividendoli in categorie sulla base di dati.

Informazioni Generali

Si vuole progettare una base di dati per un sistema centralizzato di cinema collocati sul territorio svizzero che contenga informazioni relative ai vari cinema, ai film in programmazione ed ai biglietti acquistati.

Informazioni sui Cinema

Per ogni cinema devono essere conservate le informazioni quali nome, indirizzo, citt`a, nazione, recapiti telefonici e numero di sale. Ad ogni cinema possono essere associate differenti sale a cui saranno associati differenti spettacoli.

Informazioni sulle Sale

Per ogni sala devono essere conservate le informazioni quali numero sala, superficie occupata, espressa in metri quadrati, capienza e numero dei posti.

Informazioni sui film in programmazione

Per ogni film in programmazione devono essere conservate le informazioni quali titolo, recensione, regia, genere, anno, paese, durata, attori, data di uscita e distributore.

Informazioni sugli Utenti

Per ogni utente devono essere conservate le informazioni quali username, password, cognome, nome ed e-mail. Inoltra dovranno essere conservate le informazioni relative al tipo di account, utente o botteghino del cinema.

Informazioni sugli Spettacoli

Degli spettacoli devono essere conservate le informazioni relative al giorno, orario e prezzo biglietto delle proiezioni dei film nelle varie sale dei cinema.

Informazioni sulle Prenotazioni

Delle prenotazioni devono essere conservate le informazioni relative all’utente che ha effettuato la prenotazione con i relativi posti riservati per la prenotazione.

(6)

Progettazione Concettuale L^ATEX Tesina Basi Di Dati

Schema ER Portante

Come primo passo verrà estratto, dalle specifiche di progetto, lo Schema ER Portante, contenente i concetti fondamentali su cui si basa la Base di Dati. Successivamente vi sarà una fase di raffinamento dello schema in esame, aggiungendo man mano che si procede informazioni specifiche non ancora esaminate. Nello specifico sono state estratte 5 entità fondamentali quali Utente, Cinema e Film, Spettacolo e Sala. Le Entità sono fra loro legate tramite Associazioni con fine di consetire ad un utente finale di procedere con l’acquisto di un biglietto per un film.

*Si tenga presente che `e stata volutamente omessa la partecipazione tra le varie entit`a in quanto non si dispongono ad ora le informazioni necessarie al compimento di tale compito

(7)

Fase di Raffinamento

Esaminando approfonditamente le specifiche dettate dall’azienda, si pu ò osservare che vi sono informazioni non anzcora trattate, che, al fine di fornire una rappresentazione logica quanto pi ù efficace, verranno inserite nello Schema ER Finale coadiuvato da un glossario dei termini utilizzati. Vengono prese in considerazione, al fine di garantire uniformità e coerenza nelle informazioni da gestire, le proprietà logiche che dovranno rispettare le varie entità:

• Atomicit`a

• Coerenza

• Isolamento

• Durabilit`a (o Persistenza)

A seguito di un processo di studio ulteriormente accurato, si osserva che vi sono numerose modifiche da apportare al nostro schema portante. Anzitutto si osserva che siccome i vari Cantoni Svizzeri hanno valenza reale, risulta evidente che esso possa essere evidenziato definendone un’entit`a associata, che a sua volta verr`a collegata a Cinema tramite l’associazione Afferenza.

L’entità Utente verrà da adesso vista come una SuperClasse divisa da 3 entità figlie quali

”Anonimo”, ”Cliente” e ”Botteghino”.

1. La prima, per poter offrire un servizio che abbia come targhet il maggior numero di utenti possibili.

2. La seconda per poter garantire la possibilit`a di modificare il proprio ordine in fase di acquisto. Inoltre sar`a possibile gestire offerte e vantaggi a chi decide di diventare partner dell’azienda.

3. L’ultima sar`a necessaria al corretto funzionamento della Base di Dati, in quanto anche l’operatore fisico dovr`a effettuare prenotazioni tramite applicazione apposita, cos`ı da riportare quanti e quali posti sono occupati tramite la WebAPP disponibile agli utenti web.

UTENTE

FILM CINEMA

CANTONE

(0, N)

Prossimamente

SPETTACOLO

(1, 1) Struttura

(1, N)

(1, 1)

Palinsesto

(0, N) (0, N) (0, N)

Proiezione

(1, 1)

(0, N)

Afferenza (1, 1)

Ticket

(1, N)

Composizione POSTO ^{(1, 1)}

(1, N)

Posti riservati ^{(1, N)} (1, 1)

(0, N)

Ordine

(1, 1)

ANONIMO CLIENTE BOTTEGHINO

SALA

PRENOTAZIONE

Numero Spettacolo

Nome

Indirizzo Citta

Nazione Telefono

(1;N)

Superficie

Capienza Titolo

Recensioni

(0,N) Regista Genere Anno

Paese Durata

Attori (1,N)

Data di uscita Distributore

Data Prezzo Ora

Tipo_3D

Codice Id Username Nome

Cognome Email Password

(8)

Schema ER Trasformato

Al fine di aumentare l’efficienza del servizio offerto, viene scorporata l’entità Sala in Sala + Posto, collegate fra loro tramite l’associazione ’Composizione’, quest’ultima, verrà collegata a Prenotazione tramite l’associazione ’Posti Riservati’. Grazie a tale operazione gli utenti potranno specificare la poltrona che li accompagnerà per tutta la durata dello Spettacolo.

L’entità Prenotazione diventa Superclasse, separando cos`ı l’entità figlia Prevendita, suggerendo cos`ı una possibile via per evitare eventuali frodi, in quanto una prenotazione sarà attiva se e solo se pagata entro 30 minuti dall’inizio dello spettacolo.

UTENTE

FILM CINEMA

CANTONE

(0, N)

Prossimamente

SPETTACOLO

(1, 1) Struttura

(1, N)

(1, 1)

Palinsesto

(0, N) (0, N) (0, N)

Proiezione

(1, 1)

(0, N)

Afferenza

(1, 1)

Ticket

(1, N)

Composizione POSTO ^{(1, 1)}

(1, N)

Posti riservati ^{(1, N)} (1, 1)

(0, N)

Ordine

(1, 1)

SALA

PRENOTAZIONE

ATTORE RECENSIONE

(1, N)

Numero Cast

(1, 1)

Spettacolo

Valutazione

(1, N)

(0, N)

TELEFONO

(1, 1)

Nome

Recapito

(1, N)

Nome Indirizzo Citta

Nazione

Superficie

Capienza Titolo

Regista

Genere Anno

Paese Durata

Data di uscita Distributore

Data Prezzo Ora

Tipo_3D

Numero Fila

Importo Attiva

(0, 1) Codice

Id

Username Nome

Cognome Email Password Tipo

Pagato

Nome Cognome Autore

Id Voto

Testo Codice

Numero

Come si evince dall’info-grafica le Superclassi su citate, Prenotazione ed Utente vengono trasfor- mate in entit`a a se stanti, integrando le figlie tramite un flag di segnalazione. Non vengono eseguite ulteriori modifiche allo schema in quanto non risultano tali da consentirne vantaggi effettivi.

*Si suppone che un Cinema sia tale quando allo stesso viene associata almeno una Sala.

*Si suppone che una sala a momento dell’integrazione con la base dati, sia gi`a stata allestita e dunque corredata da relative poltrone.

(9)

Di seguito vengono corredate allo schema le scelte progettuali effettuate, al fine di fornire un’immagine quanto pi `u chiara del lavoro svolto.

• Un UTENTE pu ò effettuare pi ù ordini con il proprio account (0,N), mentre una PRENO- TAZIONE è relativa ad uno ed un solo utente (1,1). Il rapporto di cardinalità dell’associazione ORDINE è dunque di tipo uno a molti.

• Una data PRENOTAZIONE pu ò riservare uno o pi ù posti riservati (1,N), mentre ogni POSTO RISERVATO pu ò appartenere a pi ù prenotazioni(1,N), mentre per un dato spettacolo risulta essere univoca.

• Una data PRENOTAZIONE pu ò essere associata ad uno ed un solo spettacolo (1,1), mentre ogni SPETTACOLO pu ò essere eventualmente associato a pi ù prenotazioni (0,N). Il rapporto di cardinalità dell’associazione Ticket è dunque di tipo uno a molti.

• Ad ogni SPETTACOLO è assegnata una ed una sola sala (1,1), mentre ad ogni SALA possono, eventualmente, essere assegnati pi ù spettacoli (0,N), purché rispettino i vincoli di associazione. Il rapporto di cardinalità dell’associazione PALLINSESTO è dunque di tipo uno a molti.

• Uno SPETTACOLO è collegato ad uno ed un solo film (1,1), mentre ogni FILM pu ò essere assegnato, eventualmente, a pi ù spettacoli (0,N). Il rapporto di cardinalità dell’associazione PALINSESTO è dunque di tipo uno a molti.

• Un FILM pu ò essere, eventualmente, messo in programmazione in pi ù cinema (0,N), ed ogni CINEMA pu ò avere, eventualmente, in programmazione (ovvero i film ancora non assegnati ad uno spettacolo preciso) pi ù film (0,N). Il rapporto di cardinalità dell’associazione PROSSIMAMENTE è dunque di tipo molti a molti.

• Un CINEMA pu ò essere composto da pi ù sale (1,N), mentre ogni SALA appartiene ad uno ed un solo cinema (1,1). Il rapporto di cardinalità dell’associazione STRUTTURA è dunque di tipo uno a molti.

• Una SALA è, eventualmente, allestita con pi ù posti (1,N), mentre ogni POSTO pu ò appartenere ad una ed una sola sala (1,1). Il rapporto di cardinalità dell’associazione ALLES- TIMENTO è dunque di tipo uno a molti.

• Un CANTONE pu ò, eventualmente, avere pi ù cinema (0,N), mentre un CINEMA pu ò appartenere ad uno ed un solo cantone (1,1). Il rapporto di cardinalità dell’associazione AFFERENZA è dunque di tipo uno a molti.

(10)

Dimensionamento della Base di Dati

Di seguito viene riportata una stima dello spazio occupato a fronte di un’anno di lavoro, es- eguendo eventuali supposizioni per informazioni non definite da progetto. Per ogni tabella viene presentato lo spazio occupato a fronte di un’anno di lavoro, all’atto della messa in esercizio della base dati (initial), tenendo conto che per ogni informazione viene calcolato lo spazio in funzione del worst case considerando cos`ı il numero di byte richiesti da ogni singolo campo. Si noti inoltre che le occorrenze delle tabelle:

• Recensioni

• Prossimamente

• Sale

• Posti

• Posti Riservati

• Prenotazioni

• Attori

• Cast

• Telefoni

Non sono state specificate per cui, per scelta di progetto verr`a eseguito un calcolo ad-hoc segnalando il percorso eseguito per giungere al risultato finale.

Dimensione Tabella Cinema Cinema

Attributo Tipo attributo Spazio occupato Initial

Codice Number(3) 3 Byte 150 Byte

Nome Varchar2(25) 25 Byte 1.25 KByte

Via Varchar2(25) 25 Byte 1.25 KByte

Civico Number(2) 3 Byte 150 Byte

CAP Number(5) 4 Byte 200 Byte

Citt`a Varchar2(20) 20 Byte 1 KByte Nazione Varchar2(15) 15 Byte 750 Byte

Cantone Number(2) 3 Byte 150 Byte

Spazio totale occupato a fronte di un anno di lavoro : 5 KByte

(11)

Dimensionamento Tabella Film Film

Codice Number(5) 4 Byte 4 KByte

Titolo Varchar2(180) 180 Byte 180 KByte

Data Uscita Date 7 Byte 7 KByte

Anno char(4) 4 Byte 4 KByte

Genere Varchar2(15) 15 Byte 15 KByte

Paese Varchar2(15) 15 Byte 15 KByte

Distributore Varchar2(30) 30 Byte 30 KByte

Durata Number(3) 3 Byte 3 KByte

Regista Varchar2(20) 20 Byte 20 KByte Spazio totale occupato a fronte di un anno di lavoro : 287 KByte

Dimensionamento Tabella Recensioni Recensioni

Codice Number(6) 5 Byte 250 Byte

Autore Varchar2(25) 25 Byte 1.25 KByte

Film Number(5) 4 Byte 200 Byte

Voto Number 1 Byte 50 Byte

Testo CLOB 4 Byte 200 Byte

Spazio totale occupato a fronte di un anno di lavoro : 2.05 KByte Dimensionamento Tabella Sale

Sale

Codice Number(5) 4 Byte 1.4 KByte

Cinema Number(3) 3 Byte 1.05 KByte

Numero Number(2) 3 Byte 1.05 KByte

Superficie Number(3) 3 Byte 1.05 KByte

Tipo 3D Number(1) 2 Byte 700 Byte

Spazio totale occupato a fronte di un anno di lavoro : 5.25 KByte Dimensionamento Tabella Posti

Posti

Fila Char(1) 1 Byte 35 KByte

Numero Number(2) 3 Byte 105 KByte

Sala Number(5) 4 Byte 140 KByte

(12)

Dimensionamento Tabella Utenti Utenti

ID Number(5) 4 Byte 4 KByte

Username Varchar2(40) 40 Byte 40 KByte

Password Char(32) 32 Byte 32 KByte

Nome Varchar2(25) 25 Byte 25 KByte

Cognome Varchar2(25) 25 Byte 25 KByte

Email Varchar2(40) 40 Byte 40 KByte

Tipo Number(1) 2 Byte 2 KByte

Spazio totale occupato a fronte di un anno di lavoro : 170 KByte Dimensionamento Tabella Attori

Attori

ID Number(6) 5 Byte 40 KByte

Nome Varchar2(25) 25 Byte 200 KByte

Cognome Varchar2(25) 25 Byte 200 KByte Spazio totale occupato a fronte di un anno di lavoro : 440 KByte

Dimensionamento Tabella Cast Cast

Attore Number(6) 5 Byte 50 KByte

Film Number(5) 4 Byte 40 KByte

Spazio totale occupato a fronte di un anno di lavoro : 100 KByte Dimensionamento Tabella Cantoni

Cantoni

ID Number(2) 3 Byte 78 Byte

Nome Varchar2(20) 20 Byte 520 Byte

Spazio totale occupato a fronte di un anno di lavoro : 700 Byte

Dimensionamento Tabella Prossimamente Prossimamente

Cinema Number(3) 3 Byte 150 KByte

(13)

Dimensionamento Tabella Telefoni Telefoni

Numero Number(15) 9 Byte 900 Byte

Cinema Number(3) 3 Byte 300 Byte

Spazio totale occupato a fronte di un anno di lavoro : 1.2 KByte Progettazione Logica Tabelle Persistenti : Chiave Primaria Unique

• Cinema(CODICE,NOME,VIA,CIVICO, CAP, CITTA`, NAZIONE, CANTONE:CANTONI)

• Film(CODICE,TITOLO,DATA USCITA,ANNO,GENERE,PAESE,DISTRIBUTORE,DURATA,REGISTA)

• Recensioni(CODICE,AUTORE,FILM:FILM,VOTO,TESTO)

• Sale(CODICE,CINEMA:CINEMA,NUMERO,SUPERFICIE,TIPO 3D)

• Posti(CODICE,FILA,NUMERO,SALA:SALE)

• Utenti(ID,USERNAME,PASSWORD,NOME,COGNOME,EMAIL,TIPO)

• Attori(ID,NOME,COGNOME)

• Cast(ATTORE,FILM:FILM)

• Cantoni(ID,NOME)

• Prossimamente(CODICE,FILM:FILM,CINEMA:CINEMA)

• Telefoni(NUMERO,CINEMA:CINEMA)

(14)

Di seguito sono riportate le tabelle relative ad informazioni che saranno poi trasferite mensilmente ad un’altro TableSpace di riferimento, il quale sar`a necessario per formulare richieste alla base dati su base bimestrale, trimestrale, semestrale o annua.

Dimensionamento Tabella Prenotazioni Prenotazioni

Utente Number(5) 4 Byte 2.3 KByte

Spettacolo Number(6) 5 Byte 2.9 KByte Importo Number(5,2) 4 Byte 2.3 KByte

Pagato Number(1) 2 Byte 1.166 KByte

Attiva Number(1) 2 Byte 1.166 KByte

Spazio totale occupato a fronte di un mese di lavoro : 13.332 KByte Dimensionamento Tabella Spettacoli

Spettacoli

Data e Ora Timestamp(0) 11 Byte 343.75 KByte

Prezzo Number(5,2) 4 Byte 125 KByte

3D Number(1) 2 Byte 62.5 KByte

Spazio totale occupato a fronte di un mese di lavoro : 937.5 KByte Dimensionamento Tabella Posti Riservati

Posti Riservati

ID Number(8) 6 Byte 23.622 KByte

Spettacolo Number(6) 5 Byte 19.685 KByte

Posto Number(6) 5 Byte 19.685 KByte

Prenotazione Number(8) 6 Byte 23.622 KByte Spazio totale occupato a fronte di un mese di lavoro : 86.614 KByte Progettazione Logica Tabelle Temporanee : Chiave Primaria Unique

• Prenotazioni(CODICE,UTENTE:UTENTI,SPETTACOLO:SPETTACOLI,IMPORTO,PAGATO,ATTIVA)

• Spettacoli(CODICE,FILM:FILM,SALA:SALE,DATA E ORA,PREZZO,TIPO 3D)

• Posti Riservati(ID,SPETTACOLO:SPETTACOLI, POSTO:POSTI,PRENOTAZIONE:PRENOTAZIONI)

(15)

Di seguito le tabelle di ripiego per le informazioni da salvare nel tablespace dedicato agli storici atte ad alleggerire il lavoro eseguito dal DBMS a seguito di Query di Selezione che agiscono sulle tabelle referenti salvate mensilmente

Dimensionamento Tabella Storico Prenotazioni Storico Prenotazioni

Utente Number(5) 4 Byte 2.3 KByte

Spettacolo Number(6) 5 Byte 2.9 KByte Importo Number(5,2) 4 Byte 2.3 KByte

Pagato Number(1) 2 Byte 1.166 KByte

Attiva Number(1) 2 Byte 1.166 KByte

Spazio totale occupato a fronte di un Anno di lavoro : 450 KByte Dimensionamento Tabella Storico Spettacoli

Storico Spettacoli

Data e Ora Timestamp(0) 11 Byte 343.75 KByte

Prezzo Number(5,2) 4 Byte 125 KByte

3D Number(1) 2 Byte 62.5 KByte

Spazio totale occupato a fronte di un Anno di lavoro : 8.25 MByte Dimensionamento Tabella Storico Posti Riservati

Storico Posti Riservati

ID Number(8) 6 Byte 23.622 KByte

Spettacolo Number(6) 5 Byte 19.685 KByte

Posto Number(6) 5 Byte 19.685 KByte

Prenotazione Number(8) 6 Byte 23.622 KByte Spazio totale occupato a fronte di un anno di lavoro : 693 KByte

(16)

Create TableSpaces e Definizione Ruoli L^ATEX Tesina Basi Di Dati

-- --- -- UTENTE: system

-- --- -- TABLESPACE : system datafile:

CREATE TABLESPACE ts_cinema DATAFILE ’D:\Universita\oracle\cinemadb\system.dbf’ SIZE 20M;

--

--TABLESPACE : LOB TESTO Recensioni, 300 caratteri per 50 recensioni per ogni film (1000)=15MB x 4anni=60MB CREATE TABLESPACE ts_testo_recensioni DATAFILE ’D:\Universita\oracle\cinemadb\lob_recensioni.dbf’ SIZE 60M;

--

--TABLESPACE DI BACKUP PER STORICO SPETTACOLI:

CREATE TABLESPACE ts_storico_spettacoli

DATAFILE ’D:\Universita\oracle\cinemadb\storico_spettacoli.dbf’ SIZE 40M;

-- RUOLO: WEB_APP CREATE ROLE WEBAPP;

GRANT CONNECT TO WEBAPP;

GRANT SELECT on cinema_dba.mv_spettacoli_attivi TO WEBAPP;

GRANT INSERT ON cinema_dba.utenti TO WEBAPP;

GRANT INSERT ON cinema_dba.prenotazioni TO WEBAPP;

GRANT INSERT ON cinema_dba.posti_riservati TO WEBAPP;

GRANT SELECT ON cinema_dba.utenti TO WEBAPP;

GRANT SELECT ON cinema_dba.posti TO WEBAPP;

GRANT SELECT ON cinema_dba.posti_riservati TO WEBAPP;

GRANT SELECT ON cinema_dba.prenotazioni TO WEBAPP;

GRANT UPDATE ON cinema_dba.v_utenti_update TO WEBAPP;

GRANT SELECT ANY SEQUENCE to WEBAPP;

-- UTENTE: cinema_dba

CREATE USER cinema_dba IDENTIFIED BY carlosansone DEFAULT TABLESPACE ts_cinema;

GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO cinema_dba;

GRANT CREATE ANY MATERIALIZED VIEW TO cinema_dba;

-- UTENTE: WEB_APP

CREATE USER web_app IDENTIFIED BY onlinestore DEFAULT TABLESPACE ts_cinema;

GRANT WEBAPP TO web_app;

(17)

Create Table su ts Cinema L^ATEX Tesina Basi Di Dati

-- --- -- UTENTE: cinema_dba

-- --- -- TABLESPACE ts_cinema

CREATE TABLE FILM (

Codice NUMBER(5) PRIMARY KEY,

Titolo VARCHAR2(180) NOT NULL,

Data_uscita DATE NOT NULL,

Anno NUMBER(4) NOT NULL,

Genere VARCHAR2(15),

Paese VARCHAR2(15),

Distributore VARCHAR2(30) NOT NULL,

Durata NUMBER(3) NOT NULL

Regista VARCHAR2(50)

)

TABLESPACE ts_cinema

STORAGE (INITIAL 287K NEXT 287K MINEXTENTS 1 PCTINCREASE 0) ; CREATE TABLE RECENSIONI (

Codice NUMBER(6) PRIMARY KEY, Autore VARCHAR2(25) NOT NULL, Film NUMBER(5) NOT NULL,

Voto NUMBER(1) CHECK(Voto>0 AND Voto<6), -- voto [1,5]

Testo CLOB,

CONSTRAINT UQ_AUTO_FILM UNIQUE (Autore, Film) )

STORAGE (INITIAL 2100K MINEXTENTS 1)

LOB(Testo) STORE AS BASICFILE LOB_post (TABLESPACE ts_testo_recensioni) ; CREATE TABLE ATTORI (

Id NUMBER(6) PRIMARY KEY, Nome VARCHAR2(25) NOT NULL, Cognome VARCHAR2(25) NOT NULL )

STORAGE (INITIAL 440K NEXT 385K MINEXTENTS 1) ; CREATE TABLE CAST (

Attore NUMBER(6),

Film NUMBER(5), PRIMARY KEY(Attore, Film) )

STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 1) ;

(18)

CREATE TABLE CANTONI (

Id NUMBER(2) PRIMARY KEY, Nome VARCHAR2(20) NOT NULL )

STORAGE (INITIAL 700 MINEXTENTS 1 MAXEXTENTS 1) ; CREATE TABLE CINEMA (

Nome VARCHAR2(25) NOT NULL UNIQUE, Via VARCHAR2(25) NOT NULL,

Civico NUMBER(2) NOT NULL,

CAP NUMBER(5) NOT NULL,

Citta VARCHAR2(20) NOT NULL,

Nazione VARCHAR2(15) NOT NULL,

Cantone NUMBER(2) NOT NULL

)

STORAGE (INITIAL 5k NEXT 2500 MINEXTENTS 1) ; CREATE TABLE TELEFONI (

Numero NUMBER(15) PRIMARY KEY, Cinema NUMBER(3) NOT NULL )

STORAGE (INITIAL 1400 NEXT 700 MINEXTENTS 1) ; CREATE TABLE SALE (

Cinema NUMBER(3) NOT NULL,

Numero NUMBER(2) NOT NULL,

Superficie NUMBER(3),

Tipo_3D NUMBER(1) DEFAULT 0 CHECK(Tipo_3D=1 OR Tipo_3D=0), CONSTRAINT UQ_CINE_SALA UNIQUE (Cinema, Numero)

)

STORAGE (INITIAL 8k NEXT 4k MINEXTENTS 1) ; CREATE TABLE PROSSIMAMENTE (

Codice NUMBER(6) PRIMARY KEY, Film NUMBER(5) NOT NULL, Cinema NUMBER(3) NOT NULL,

CONSTRAINT UQ_FILM_CINE UNIQUE (Film, Cinema) )

STORAGE (INITIAL 700K NEXT 1050K MINEXTENTS 1 PCTINCREASE 25) ;

(19)

CREATE TABLE SPETTACOLI (

Film NUMBER(5) NOT NULL, Sala NUMBER(5) NOT NULL, Data_e_ora TIMESTAMP(0) NOT NULL,

Prezzo NUMBER(5,2) NOT NULL,

Tipo_3D NUMBER(1) DEFAULT 0 CHECK(Tipo_3D=1 OR Tipo_3D=0), CONSTRAINT UQ_SPET_SALA UNIQUE (Sala, Data_e_ora)

)

STORAGE (INITIAL 1040K MINEXTENTS 1 MAXEXTENTS 2) ; CREATE TABLE POSTI (

Codice NUMBER(6) PRIMARY KEY, Fila CHAR(1) NOT NULL, Numero NUMBER(2) NOT NULL, Sala NUMBER(5) NOT NULL,

CONSTRAINT UQ_POST_SALA UNIQUE (Fila, Numero, Sala) )

STORAGE (INITIAL 490K NEXT 245K MINEXTENTS 1) ; CREATE TABLE UTENTI (

Id NUMBER(5) PRIMARY KEY,

Username VARCHAR2(40) UNIQUE NOT NULL, Password CHAR(32) NOT NULL,

Nome VARCHAR2(25),

Cognome VARCHAR2(25),

Email VARCHAR2(40) UNIQUE NOT NULL,

Tipo NUMBER(1) DEFAULT 0 CHECK(Tipo=-1 OR Tipo=0 OR Tipo=1) -- -1=GUEST | 0=UTENTE | 1=BOTTEGHINO )

STORAGE (INITIAL 170K NEXT 170K MINEXTENTS 1) ; CREATE TABLE PRENOTAZIONI (

Utente NUMBER(5) NOT NULL,

Spettacolo NUMBER(6) NOT NULL,

Importo NUMBER(5,2) DEFAULT 0 NOT NULL,

Pagato NUMBER(1) DEFAULT 0 CHECK(Pagato=0 OR Pagato=1), Attiva NUMBER(1) DEFAULT 1 CHECK(Attiva=0 OR Attiva=1) )

STORAGE (INITIAL 16K MINEXTENTS 1) ;

(20)

CREATE TABLE POSTI_RISERVATI (

Posto NUMBER(6) NOT NULL,

Prenotazione NUMBER(8) NOT NULL,

CONSTRAINT UQ_POSTI_RIS_SPET_POST UNIQUE (Posto, Spettacolo) )

STORAGE (INITIAL 87K MINEXTENTS 1 MAXEXTENTS 2) ;

(21)

Definizione IC L^ATEX Tesina Basi Di Dati

-- ALTER TABLE

ALTER TABLE cinema_dba.Cast ADD CONSTRAINT FK_CAST_FILM FOREIGN KEY (Film) REFERENCES cinema_dba.Film(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Cinema ADD CONSTRAINT FK_CINE_CANT FOREIGN KEY (Cantone) REFERENCES cinema_dba.Cantoni(Id) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Telefoni ADD CONSTRAINT FK_TELE_CINE FOREIGN KEY (Cinema) REFERENCES cinema_dba.Cinema(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Sale ADD CONSTRAINT FK_SALE_CINE FOREIGN KEY (Cinema) REFERENCES cinema_dba.Cinema(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Spettacoli ADD CONSTRAINT FK_SPET_FILM FOREIGN KEY (Film) REFERENCES cinema_dba.Film(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Spettacoli ADD CONSTRAINT FK_SPET_SALE FOREIGN KEY (Sala) REFERENCES cinema_dba.Sale(Sala) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Posti ADD CONSTRAINT FK_POST_SALE FOREIGN KEY (Sala) REFERENCES cinema_dba.Sale(Sala) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Prenotazioni ADD CONSTRAINT FK_PREN_USER FOREIGN KEY (Utente) REFERENCES cinema_dba.Utenti(Id) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Prenotazioni ADD CONSTRAINT FK_PREN_SPET FOREIGN KEY (Spettacolo) REFERENCES cinema_dba.Spettacoli(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Posti_riservati ADD CONSTRAINT FK_POST_RIS_SPET FOREIGN KEY (Spettacolo) REFERENCES cinema_dba.Spettacoli(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Posti_riservati ADD CONSTRAINT FK_POST_RIS_POST FOREIGN KEY (Posto) REFERENCES cinema_dba.Posti(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Posti_riservati ADD CONSTRAINT FK_POST_RIS_PREN FOREIGN KEY (Prenotazione) REFERENCES cinema_dba.Prenotazioni(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Prossimamente ADD CONSTRAINT FK_PROS_FILM FOREIGN KEY (Film) REFERENCES cinema_dba.Film(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Prossimamente ADD CONSTRAINT FK_PROS_CINE FOREIGN KEY (Cinema) REFERENCES cinema_dba.Cinema(Codice) ON DELETE SET NULL;

ALTER TABLE cinema_dba.Recensioni ADD CONSTRAINT FK_RECE_FILM FOREIGN KEY (Film) REFERENCES cinema_dba.Film(Codice) ON DELETE SET NULL;

(22)

Materialized View L^ATEX Tesina Basi Di Dati

-- Su XE rappresentano Tabelle

CREATE MATERIALIZED VIEW mv_spettacoli_attivi TABLESPACE ts_cinema

STORAGE (INITIAL 3M) AS

SELECT SP.codice, C.nome AS nome_cinema, F.titolo,

F.durata, SA.codice AS codice_sala, SA.numero AS numero_sala, SP.data_e_ora, SP.prezzo, SP.tipo_3d FROM Spettacoli SP, Film F, Cinema C, Sale SA

WHERE SP.film=F.codice AND SP.sala=SA.codice AND C.codice=SA.cinema AND SP.data_e_ora >= SYSDATE;

CREATE INDEX INDEX_TITOLO_FILM ON FILM (TITOLO ASC);

-- fine TABLESPACE ts_cinema

(23)

Create Table su ts Backup L^ATEX Tesina Basi Di Dati

-- TABLESPACE ts_storico_spettacoli CREATE TABLE STORICO_SPETTACOLI (

Film NUMBER(5) NOT NULL, Sala NUMBER(5) NOT NULL, Data_e_ora TIMESTAMP(0) NOT NULL,

Prezzo NUMBER(5,2) NOT NULL,

Tipo_3D NUMBER(1) DEFAULT 0 CHECK(Tipo_3D=1 OR Tipo_3D=0), CONSTRAINT UQ_STORICO_SPET_SALA UNIQUE (Sala, Data_e_ora)

)

TABLESPACE ts_storico_spettacoli

STORAGE (INITIAL 8250K NEXT 12375K MINEXTENTS 1 PCTINCREASE 25) ; CREATE TABLE STORICO_PRENOTAZIONI (

Utente NUMBER(5) NOT NULL,

Importo NUMBER(5,2) NOT NULL,

Pagato NUMBER(1) DEFAULT 0 CHECK(Pagato=0 OR Pagato=1), Attiva NUMBER(1) DEFAULT 1 CHECK(Attiva=0 OR Attiva=1) )

STORAGE (INITIAL 426K NEXT 638K MINEXTENTS 1 PCTINCREASE 50) ; CREATE TABLE STORICO_POSTI_RISERVATI (

Posto NUMBER(6) NOT NULL,

Prenotazione NUMBER(8) NOT NULL,

CONSTRAINT UQ_STORICO_POSTI_RIS_SPET_POST UNIQUE (Posto, Spettacolo) )

STORAGE (INITIAL 693K NEXT 1040K MINEXTENTS 1 PCTINCREASE 50) ; -- fine TABLESPACE ts_storico_spettacoli

(24)

Sequences L^ATEX Tesina Basi Di Dati

-- SEQUENCES

CREATE SEQUENCE recensioni_seq START WITH 1

INCREMENT BY 1 NOCYCLE;

CREATE SEQUENCE prossimamente_seq START WITH 1

CREATE SEQUENCE utenti_seq START WITH 1

CREATE SEQUENCE film_seq START WITH 1

CREATE SEQUENCE attori_seq START WITH 1

CREATE SEQUENCE cantoni_seq START WITH 1

CREATE SEQUENCE cinema_seq START WITH 1

CREATE SEQUENCE sale_seq START WITH 1

CREATE SEQUENCE spettacoli_seq START WITH 1

CREATE SEQUENCE posti_seq START WITH 1

CREATE SEQUENCE prenotazioni_seq START WITH 1

CREATE SEQUENCE posti_riservati_seq START WITH 1

(25)

Livello Dati : Triggers L^ATEX Tesina Basi Di Dati

L’architettura software dell’applicazione definita si posa su tre livelli logico-funzionali:

1. Livello Dati

2. Livello Applicazione 3. Livello Presentazione

Nel caso in esame viene sfruttata la potenza del DBMS Oracle 11g grazie al quale possiamo integrare una serie di procedure o funzioni PL/SQL grazie al quale possiamo rendere l’informazione quanto pi `u indipendente dai livelli superiori. Di seguito sono riportati alcuni fra Trigger&Stored Procedures utilizzati per rendere automatiche le regole di business applicate.

Livello Dati

-- Trigger che controlla la sovrapposizione di due spettacoli nella stessa sala di uno stesso cinema CREATE or replace TRIGGER sovrapposizione_spettacoli

BEFORE INSERT ON Spettacoli FOR EACH ROW

BEGIN DECLARE

CURSOR c1 IS SELECT data_e_ora, durata FROM mv_spettacoli_attivi s WHERE s.codice_sala=:NEW.sala;

inizio_spettacolo mv_spettacoli_attivi.data_e_ora%TYPE;

durata_spettacolo mv_spettacoli_attivi.durata%TYPE;

fine_spettacolo mv_spettacoli_attivi.data_e_ora%TYPE;

sovrapposizione EXCEPTION;

BEGIN OPEN c1;

LOOP

FETCH c1 INTO inizio_spettacolo, durata_spettacolo;

EXIT WHEN c1%NOTFOUND;

fine_spettacolo := inizio_spettacolo + (1 / 24 / (60 / durata_spettacolo));

-- DBMS_OUTPUT.put_line (’risultato:’);

-- DBMS_OUTPUT.put_line (’inizio spettacolo=’||inizio_spettacolo||’

-- durata=’||durata_spettacolo||’ fine=’||fine_spettacolo);

IF :NEW.data_e_ora < fine_spettacolo AND :NEW.data_e_ora > inizio_spettacolo THEN -- DBMS_OUTPUT.put_line(’errore’);

RAISE sovrapposizione;

END IF;

END LOOP;

CLOSE c1;

EXCEPTION

WHEN sovrapposizione THEN

-- DBMS_OUTPUT.put_line(’sovrapposizione spettacoli’);

RAISE_APPLICATION_ERROR( -20001,

’Sala occupata da un altro spettacolo’ );

(26)

-- Trigger che cancella il film da "prossimamente" se messo in programmazione per quel cinema CREATE OR REPLACE TRIGGER da_prossimamente_a_spettacolo

AFTER INSERT OR UPDATE ON Spettacoli FOR EACH ROW

BEGIN DECLARE

tmp_spettacolo Prossimamente.codice%TYPE;

tmp_cinema Cinema.codice%TYPE;

BEGIN

SELECT cinema INTO tmp_cinema

FROM Sale WHERE Sale.codice=:NEW.sala;

SELECT codice INTO tmp_spettacolo

FROM Prossimamente WHERE film=:NEW.film AND cinema=tmp_cinema;

DELETE FROM Prossimamente WHERE codice=tmp_spettacolo;

EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;

-- DBMS_OUTPUT.PUT_LINE(’NOT_FOUND’);

END;

-- Trigger UNIQUE CONSTRAINT posti_riservati per prenotazioni attive CREATE OR REPLACE TRIGGER UQ_POST_RISE

BEFORE INSERT OR UPDATE ON Posti_riservati FOR EACH ROW

BEGIN DECLARE

n_posti NUMBER;

posto_occupato EXCEPTION;

BEGIN

SELECT count(*) INTO n_posti

FROM Posti_riservati PR JOIN Prenotazioni PRE ON PR.prenotazione=PRE.codice WHERE PR.posto=:NEW.posto AND PR.spettacolo=:NEW.spettacolo AND PRE.attiva=1;

IF n_posti>0 THEN RAISE POSTO_OCCUPATO;

END IF;

EXCEPTION

WHEN POSTO_OCCUPATO THEN

RAISE_APPLICATION_ERROR( -20002,

’Posto occupato da un altro spettatore’ );

END;

(27)

-- Triger che calcola il prezzo totale della prenotazione in base al numero di posti prenotati CREATE OR REPLACE TRIGGER calcola_importo_prenotazione

AFTER INSERT ON posti_riservati FOR EACH ROW

BEGIN DECLARE

prezzo_posto cinema_dba.spettacoli.prezzo%TYPE;

BEGIN

SELECT prezzo INTO prezzo_posto FROM cinema_dba.spettacoli WHERE codice=:NEW.spettacolo;

UPDATE cinema_dba.prenotazioni SET importo=importo+prezzo_posto WHERE codice=:NEW.prenotazione;

END;

(28)

Livello Dati : Stored Procedures L^ATEX Tesina Basi Di Dati

-- Procedura per l’archiviazione degli spettacoli con data antecedente a qu-ella odierna

-- La procedura sposta gli spettacoli nello storico, assieme alle prenotazioni ed ai posti riservati CREATE OR REPLACE PROCEDURE archivia_spettacoli

AS BEGIN DECLARE

CURSOR c_spettacoli IS SELECT codice FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;

codice_spettacolo cinema_dba.spettacoli.codice%TYPE;

BEGIN

OPEN c_spettacoli; -- apro il cursore degli spettacoli da spostare

-- inserisco tutti gli spettacoli non pi`u attivi (a partire da ieri) nella tabella storico INSERT INTO cinema_dba.storico_spettacoli

SELECT *

FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;

LOOP

FETCH c_spettacoli INTO codice_spettacolo; --leggo il codice_spettacolo i-esimo spettacolo EXIT WHEN c_spettacoli%NOTFOUND;

-- --- -- INSERT PHASE

-- inserisco nello storico le prenotazioni della i-esimo spettacolo INSERT INTO cinema_dba.storico_prenotazioni

SELECT *

FROM cinema_dba.prenotazioni WHERE spettacolo=codice_spettacolo;

--

-- inserisco nello storico i posti riservati dell’i-esimo spettacolo INSERT INTO cinema_dba.storico_posti_riservati

SELECT *

FROM cinema_dba.posti_riservati WHERE spettacolo=codice_spettacolo;

-- --- -- DELETE PHASE

DELETE FROM cinema_dba.prenotazioni WHERE spettacolo=codice_spettacolo;

DELETE FROM cinema_dba.posti_riservati WHERE spettacolo=codice_spettacolo;

END LOOP;

CLOSE c_spettacoli;

-- Cancello gli spettacoli non pi`u attivi

DELETE FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;

commit;

END;

END archivia_spettacoli;

(29)

Livello Dati : Stored Procedures L^ATEX Tesina Basi Di Dati

-- Procedura che controlla lo stato del pagamento

CREATE OR REPLACE PROCEDURE checkPagamento(idPrenotazione IN Prenotazioni.codice%TYPE) IS

BEGIN DECLARE

pagamento_success BOOLEAN;

BEGIN

pagamento_success := TRUE;

IF(pagamento_success=TRUE) THEN

UPDATE Prenotazioni SET pagato=1 WHERE codice=idPrenotazione;

END IF;

END;

END checkPagamento;

-- Procedura per l’inserimento di uno spettacolo con conseguente aggiornamento della vista materializzata CREATE PROCEDURE inserisci_spettacolo (codFilm IN spettacoli.film%TYPE,

codSala IN spettacoli.sala%TYPE, dataeora IN spettacoli.data_e_ora%TYPE, pr IN spettacoli.prezzo%TYPE, tipo IN

spettacoli.tipo_3d%TYPE) IS

BEGIN

INSERT INTO Spettacoli(codice, film, sala, data_e_ora, prezzo, tipo_3d) VALUES(spettacoli_seq.NEXTVAL, codFilm, codSala, dataeora, pr, tipo);

-- aggiorno la vista materializzata

DBMS_SNAPSHOT.REFRESH(’mv_spettacoli_attivi’);

END inserisci_spettacolo;

-- esempio di utilizzo:

-- ALTER SESSION SET nls_TIMESTAMP_format=’dd/mm/yyyy hh24:mi’;

-- begin

-- inserisci_spettacolo(550, 49, ’07/01/2017 12:25’, 5.00, 0);

-- end;

-- select * from mv_spettacoli_attivi;

-- select f.titolo from film f join spettacoli s on s.film=f.codice where f.codice=550;

(30)

Scheduler L^ATEX Tesina Basi Di Dati

-- SCHEDULE: Il 1o di ogni mese alle ore 06:00 BEGIN

DBMS_SCHEDULER.CREATE_SCHEDULE(

’inizio_mese’,

REPEAT_INTERVAL => ’FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=6;BYMINUTE=0;BYSECOND=0;’

);

END;

-- Il 1o di ogni mese alle 06:00 viene eseguita la stored procedure di archiviazione degli spettacoli BEGIN

DBMS_SCHEDULER.CREATE_JOB ( job_name => ’archivio_mensile’, schedule_name => ’inizio_mese’, job_type => ’STORED_PROCEDURE’, job_action => ’archivia_spettacoli’, enabled => TRUE

);

END;

ALTER SESSION SET nls_TIMESTAMP_format=’dd/mm/yyyy hh24:mi’;

(31)

Livello Applicazione L^ATEX Tesina Basi Di Dati

Livello Applicazione

Una volta giunti al livello applicazione, si abbandona in parte la sintassi dettata dal DBMS Oracle e si giunge alla programmazione ad alto livello sfruttando i driver messi a disposizione dalle varie case software tramite i quali, l’applicazione web, scritta in Java, eseguirà operazioni di lettura e scrittura per le varie tabelle. In particolare grazie allo standard ODBC l’accesso remoto è garantito dai servizi di accesso alla base dati tramite i servizi offerti da JDBC, impartendo ordini tramite SQL, tenendo fede per ò ai tipi di dato manipolabili tramite il linguaggio utilizzato. Come già detto si è scelto di utilizzare il linguaggio Java ad alto livello per interagire con il DBMS Oracle, in quanto Java grazie alle API JDBC riesce ad istanziare una connessione sinergica con la base dati. Sono state progettate classi utili alla gestione della connessione attraverso il DBMS ORACLE.

Nello specifico sono state utilizzate le seguenti classi messe a disposizione dallo standard JDBC per la comunicazione tra Applicazione e Database, integrate ad una classe progettata ad-hoc.

• E’ stata progettata la classeDataBaseche fornisce in sintonia con lo standard ODBC i metodi classici per gestire la connessione :

– Apertura connessione.

– Invio Query di selezione per estrapolare i dati.

– Invio Query di manipolazione dati.

– Chiusura connessione.

• Statementclasse definita nello standard JDBC, rende possibile all’applicazione di porre richieste al DBMS.

– Essa rende possibile l’esecuzione di Query Statiche cio`e che eseguono richieste in- dipendenti dall’utente che le invia, come ad esempio la lista dei Film in Program- mazione.

• Prepared Statementclasse definita nello standard JDBC, estende la classe Statement eredi- tandone tutte le funzioni, assieme a metodi aggiuntivi.

– Essa rende possibile l’esecuzione di statement SQL parametrizzati

• ConnectionSettingper connessione a server remoti (utile per la gestione da remoto delle informazioni)

• RSManagerper la conversione esplicita degli oggetti di tipo ResultSet a classi manipolabili in Java

E’ stata inoltre progettata una classe Md5Encodergrazie alla quale `e possibile applicare la seguente politica di sicurezza :

– All’atto della registrazione, tale classe avvia un metodo che dato in input una stringa (Campo Password) esegue la conversione della stessa in Md5 salvando cos`ı solo l’informazione crittografata, eliminando cos`ı la possibilit`a, anche al DBA, di risalire alla Password del cliente.

(32)

Livello Presentazione L^ATEX Tesina Basi Di Dati

Livello Presentazione

Per l’ultimo stadio della progettazione della base dati, cioè il Livello Presentazione si è scelto di utilizzare un’interfaccia Web adattabile tramite CSS ai vari dispositivi che andranno a stabilire una connessione con il sito web. Tale applicazione web è definita da pi ù pagine collegate fra loro le quali permettono ad un cliente associato e non di visualizzare la programmazione per un dato cinema nelle sue vicinanze, oltre che eseguire prenotazioni e/o acquisti in completa sicurezza tramite collegamenti esterni ai vari circuiti bancari (es. MasterCard, Paypal, ecc).

Proprio grazie a tale livello si osserva che la gestione delle informazioni tramite PC Desktop (o Laptop), Smartphone o Tablet rimane pressappoco la stessa, variando semplicemente l’interfaccia grafica utilizzata per approcciare con la base dati.

(33)

Comandi per area Manageriale

Tale Query ci consente di conoscere quanti, utenti registrati non hanno effettuato alcuna prenotazione divise per Mese corrente e Storico

SELECT sum(conta) AS utenti_senza_prenotazioni_ FROM ( SELECT count(U.id) AS conta

FROM Utenti U LEFT JOIN Prenotazioni P ON P.utente = U.ID WHERE P.codice IS NULL OR P.attiva = 0

UNION

SELECT count(U.id) AS conta

FROM Utenti U LEFT JOIN Storico_prenotazioni P ON P.utente = U.ID WHERE P.codice IS NULL OR P.attiva = 0

);

La prossima Query ci permette di conoscere il numero di biglietti venduti dalla messa in opera del sistema informatico.

SELECT id, username, sum(biglietti_acquistati) AS biglietti FROM ( SELECT U.id, U.username, COUNT(PR.id) AS biglietti_acquistati FROM Utenti U, Prenotazioni P, Posti_riservati PR

WHERE P.utente=U.id AND PR.prenotazione=P.codice AND P.attiva=1 GROUP BY U.id, U.username

UNION

SELECT U.id, U.username, COUNT(PR.id) AS biglietti_acquistati FROM Utenti U, Storico_prenotazioni P, Storico_posti_riservati PR WHERE P.utente=U.id AND PR.prenotazione=P.codice AND P.attiva=1 GROUP BY U.id, U.username

) GROUP BY id, username;

Query che ci permette di conoscere l’incasso totale dovuto alle prenotazioni.

SELECT SUM(importo) AS Cassa_totale FROM ( SELECT SUM(importo) AS importo

FROM Prenotazioni

WHERE attiva=1 AND pagato=1 UNION

SELECT SUM(importo) AS importo FROM Storico_prenotazioni WHERE attiva=1 AND pagato=1 );

Query che ci permette di conoscere l’incasso a partire dal primo giorno del mese dovuto alle prenotazioni.

SELECT SUM(importo) AS cassa_mensile FROM Prenotazioni

WHERE attiva=1 AND pagato=1;

(34)

Glossario dei Termini

Termine Descrizione Associato

Utente Individuo registrato e non all’interno del database al quale è possibile associare un eventuale Biglietto acquistato o prenotato. Il campo Password è definito CHAR(32) poichè la password sarà convertita e registrata dall’applicazione in formato Md5.

Prenotazioni

Prenotazione Esso rappresenta il fulcro economico dell’azienda, grazie al quale permette ad un determinato utente di prendere visione di uno spettacolo, ad ogni prenotazione risulta cos`ı associato un posto definito posto prenotato.

Utente & Spettacolo & Posto

Posto Poltrona fisica associata univocamente ad una sala

Sala & Prenotazione Spettacolo La messa in visione di un determinato film in

data sala.

Prenotazione & Sala & Film Sala Sezione di un cinema adibita al trattamento di

determinati spettacoli a seguito della messa in proiezione di un Film

Spettacolo & Cinema

Film Pellicola Cinematografica rappresentante oltre che una forma d’arte, anche una princi- pale fonte d’intrattenimento. A seconda dell’interesse portato avanti dal pubblico, nella medesima sala, sono disponibili pi `u vi- sioni al giorno che possono protrarsi fino a qualche mese.

Spettacolo & Cinema & Attore

Cinema Locale attrezzato alla messa in visione di spettacoli cinematografici

Film & Sala Recensione Ogni utente potr`a una volta presa visione di

un film consigliarlo (o sconsigliarlo) al grande pubblico presentando un proprio pensiero riguardo al film appena visionato.

Film

Attore Individuo facente parte del Cast del film. Il successo di un Film viene decretato oltre che dalla trama del suddetto, anche per la qualit`a interpretativa degli attori interessati.

Film

Telefono Insieme di recapiti telefonici associati ad un determinato cinema.

Cinema Cantone Stati che compongono la confederazione el-

vetica, cio`e lo Stato federale svizzero. Ogni cantone ha una sua costituzione, un suo par- lamento, un suo governo e suoi organi giuris- dizionali.

Cinema