Tesina Basi di Dati Catena Cinema Multisala

MAXSIZE UNLIMITED;

I valori di SIZE e AUTOEXTEND sono stati ricavati, rispettivamente, dalla somma di tutte le

dimensioni initial e next calcolate nel paragrafo “Volumi Totali”.

Il valore sono stati volutamente aumentati per includere eventuali arrotondamenti nel

dimensionamento.

PRENOTAZIONE

Sull’entità si effettua un partizionamento orizzontale, cioè una suddivisione

dell’insieme delle occorrenze, in due sottoinsiemi disgiunti. (prenotazione e storico_prenotazione)

Questo perché la frequenza di consultazione delle prenotazioni a corto termine è molto più alta di

quella che riguarda l’intero storico.

È sensato creare uno storico in cui verranno spostate le prenotazioni insieme alle istanze di

afferenze_posto alla fine di ogni anno. Viene stanziato appositamento il tablespace “ts_storico”.

CREATE TABLESPACE ts_storico

DATAFILE ‘D:\CINEMA\ts_storico.dbf’

SIZE 130M

AUTOEXTEND ON NEXT 130M

MAXSIZE UNLIMITED;

Inoltre viene stanziato uno spazio logico dedicato alla memorizzazione dei tipi CLOB. Questo

tablespace ospita gli effettivi contenuti dell’attributo “testo” della tabella RECENSIONI.

Si prevedono 70 film all’anno che moltiplicati per la media di 2000 caratteri di cui è composta una

recensione, comportano una dimensione potenzialmente occupata di 14000 byte.

CREATE TABLESPACE ts_lob

DATAFILE ‘D:\CINEMA\ts_lob.dbf’

SIZE 14K

AUTOEXTEND ON NEXT 14K

MAXSIZE UNLIMITED;

6.3.3 Utenti e ruoli

Considerando le operazioni previste sulla base e le applicazioni che si interfacceranno col DB, si

forniscono i seguenti ruoli interagenti con la base:

Amministratore: gestore/creatore della base a cui si assegnano tutti i privilegi

WebApp: forniti privilegi di lettura e inserimento per effettuare consultazione e prenotazione

DeskApp: si occupa di gestire inserimenti, aggiornamenti, cancellazioni. Tale ruolo è

associato alle applicazioni amministrative. (non implementato)

Amministratore WEB_APP DESK_APP

Recensioni All Privileges Select Select, Insert, Update, Delete

Film_programmazione All Privileges Select Select, Insert, Update, Delete

Cast All Privileges Select Select, Insert, Update, Delete

Partecipazione All Privileges Select Select, Insert, Update, Delete

Calendari All Privileges Select Select, Insert, Update, Delete

Cinema All Privileges Select Select

Recapiti_cinema All Privileges Select Select

Sale All Privileges Select Select

Afferenza_sala All Privileges Select Select

Posti All Privileges Select Select

Prenotazioni All Privileges Select, Insert Select

Utenti All Privileges Select, Insert Select, Delete

Afferenze_posto All Privileges Select, Insert Select

PASSWORD dba_cinema web_app desk_app

CREATE USER Amministratore IDENTIFIED BY dba_cinema

DEFAULT TABLESPACE ts_cinema

TEMPORARY TABLESPACE temp;

GRANT dba TO Amministratore;

ALTER USER "AMMINISTRATORE" QUOTA UNLIMITED ON TS_CINEMA;

ALTER USER "AMMINISTRATORE" QUOTA UNLIMITED ON TS_STORICO;

Si è preferito utilizzare il ruolo di “dba” predefinito per assegnare i privilegi all’Amministratore.

Utilizzando i privilegi da Amministratore, vengono poi ampliate le politiche di sicurezza definendo

gli altri ruoli.

Le seguenti istruzioni si presentano in questo paragrafo per motivi di ordine anche se sono definiti a

valle della creazione delle tabelle, delle viste e dei trigger.

CREATE ROLE WebApp;

GRANT CONNECT TO WebApp;

GRANT SELECT ON Amministratore.CINEMA TO WebApp;

GRANT SELECT ON Amministratore.RECENSIONI TO WebApp;

GRANT SELECT ON Amministratore.FILM_PROGRAMMAZIONE TO WebApp;

GRANT SELECT ON Amministratore.CAST TO WebApp;

GRANT SELECT ON Amministratore.PARTECIPAZIONE TO WebApp;

GRANT SELECT ON Amministratore.CALENDARI TO WebApp;

GRANT SELECT ON Amministratore.RECAPITI_CINEMA TO WebApp;

GRANT SELECT ON Amministratore.SALE TO WebApp;

GRANT SELECT ON Amministratore.AFFERENZE_SALA TO WebApp;

GRANT SELECT ON Amministratore.POSTI TO WebApp;

GRANT SELECT, INSERT, UPDATE ON Amministratore.PRENOTAZIONI TO WebApp;

GRANT SELECT, INSERT ON Amministratore.UTENTI TO WebApp;

GRANT SELECT, INSERT ON Amministratore.AFFERENZE_POSTO TO WebApp;

CREATE ROLE DeskApp;

GRANT CONNECT TO DeskApp;

GRANT SELECT, INSERT, UPDATE, DELETE ON Amministratore.RECENSIONI TO

DeskApp;

GRANT SELECT, INSERT, UPDATE, DELETE ON

Amministratore.FILM_PROGRAMMAZIONE TO DeskApp;

GRANT SELECT, INSERT, UPDATE, DELETE ON Amministratore.CAST TO

DeskApp;

GRANT SELECT, INSERT, UPDATE, DELETE ON Amministratore.PARTECIPAZIONE

TO DeskApp;

GRANT SELECT, INSERT, UPDATE, DELETE ON Amministratore.CALENDARI TO

DeskApp;

GRANT SELECT ON Amministratore.CINEMA TO DeskApp;

GRANT SELECT ON Amministratore.RECAPITI_CINEMA TO DeskApp;

GRANT SELECT ON Amministratore.SALE TO DeskApp;

GRANT SELECT ON Amministratore.AFFERENZE_SALA TO DeskApp;

GRANT SELECT ON Amministratore.POSTI TO DeskApp;

GRANT SELECT ON Amministratore.PRENOTAZIONI TO DeskApp;

GRANT SELECT, DELETE ON Amministratore.UTENTI TO DeskApp;

GRANT SELECT ON Amministratore.RECAPITI_CINEMA TO DeskApp;

Dopo la creazione dei ruoli si procede alla creazione dei relativi utenti.

CREATE USER WEB_APP IDENTIFIED BY web_app

DEFAULT TABLESPACE ts_cinema;

GRANT WebApp to WEB_APP;

CREATE USER DESK_APP IDENTIFIED BY desk_app

DEFAULT TABLESPACE ts_cinema

GRANT DeskApp to DESK_APP;

6.3.4 Tabelle

Di seguito sono riportati gli statement SQL per la creazione delle tabelle e dei vincoli di integrità.

Per ogni “CREATE TABLE” sono state specificate le clausole di storage che descrivono come lo

spazio riservato per ognuna di esse viene utilizzato.

INITIAL: specifica la misura in byte dell’estensione iniziale della tabella.

NEXT: indica la misura per l’estensione successiva.

MINEXTENTS: specifica il numero minimo di extents allocati

MAXEXTENTS: specifica il numero massimo di extents allocati.

PCTINCREASE: indica la percentuale di incremento delle dimensioni delle extents dalla terza in poi

relativa all’extets precedente.

I valori di storage sono stati arrondati cifra intera che segue. (1,32 ->2)

Il valore di NEXT deve essere NOT NULL. Dove non previsto è stato assegnato 1Kb.

Il parametro MAXEXTENTS è stato settato a 5.

Per le ipotesi di dimensionamento stabilite verrà allocato un nuovo extents circa ogni anno,

garantendo quindi il funzionamento del sistema per 5 anni.

CREATE TABLE RECENSIONI(

CodRecensione NUMBER(3),

Film NUMBER(3) NOT NULL,

Testo CLOB,

CONSTRAINT PK_RECENSIONI PRIMARY KEY(CodRecensione)

)

LOB(Testo) STORE AS LOB_Recensioni (TABLESPACE ts_lob)

STORAGE(INITIAL 1K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE FILM_PROGRAMMAZIONE(

CodFilm NUMBER(3),

Titolo VARCHAR2(30) NOT NULL,

Anno CHAR(4),

Produttore VARCHAR2(10),

Genere VARCHAR2(10),

Durata NUMBER(4,2) NOT NULL,

DataUscita DATE NOT NULL,

CONSTRAINT PK_FILM_PROGRAMMAZIONE PRIMARY KEY(CodFilm)

)

STORAGE(INITIAL 5K NEXT 5K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE CAST(

IdCast NUMBER(4),

Tipo VARCHAR2(7) NOT NULL,

Nome VARCHAR2(20) NOT NULL,

Cognome VARCHAR2(20) NOT NULL,

DataNascita DATE,

CONSTRAINT PK_CAST PRIMARY KEY(IdCast),

CONSTRAINT CK_CAST CHECK(Tipo IN ('Attore', 'Regista'))

)

STORAGE(INITIAL 44K NEXT 5K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE PARTECIPAZIONE(

Film NUMBER(3),

Cast NUMBER(4),

Ruolo VARCHAR2(20),

CONSTRAINT PK_PARTECIPAZIONE PRIMARY KEY(Film, Cast)

)

STORAGE(INITIAL 61K NEXT 27K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE CALENDARI(

CodProgramma NUMBER(6),

Ora NUMBER(4,2) NOT NULL,

Data DATE NOT NULL,

Film NUMBER(3) NOT NULL,

Cinema CHAR(2) NOT NULL,

CONSTRAINT PK_CALENDARI PRIMARY KEY(CodProgramma)

)

STORAGE(INITIAL 3367K NEXT 1685K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE

0);

CREATE TABLE CINEMA(

CodCinema CHAR(2),

Nome VARCHAR2(15) NOT NULL,

NumeroSale CHAR(1) NOT NULL,

Citta VARCHAR2(15) NOT NULL,

Indirizzo VARCHAR2(20) NOT NULL,

Civico CHAR(3) NOT NULL,

CONSTRAINT PK_CINEMA PRIMARY KEY(CodCinema)

)

STORAGE(INITIAL 3K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE RECAPITI_CINEMA(

Numero VARCHAR2(10),

Cinema CHAR(2) NOT NULL,

CONSTRAINT PK_RECAPITI_CINEMA PRIMARY KEY(Numero),

CONSTRAINT CK_RECAPITI CHECK(LENGTH(Numero)>=9)

)

STORAGE(INITIAL 2K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE SALE(

NumSala CHAR(1),

Cinema CHAR(2),

Superficie CHAR(4),

Capienza CHAR(3) NOT NULL,

CONSTRAINT PK_SALE PRIMARY KEY(NumSala, Cinema)

)

STORAGE(INITIAL 2K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE AFFERENZE_SALA(

Programma NUMBER(6),

Sala CHAR(1) NOT NULL,

Cinema CHAR(2) NOT NULL,

CONSTRAINT PK_AFFERENZE_SALA PRIMARY KEY(Programma)

)

STORAGE(INITIAL 1284K NEXT 642K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE

0);

CREATE TABLE POSTI(

Numero NUMBER(3),

Fila NUMBER(3),

Sala CHAR(1),

Cinema CHAR(2),

CONSTRAINT PK_POSTI PRIMARY KEY(Numero, Fila, Sala, Cinema)

)

STORAGE(INITIAL 106K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE PRENOTAZIONI(

IdPrenotazione NUMBER(7),

Utente NUMBER(4) NOT NULL,

Programma NUMBER(4) NOT NULL,

Pagato CHAR(1) NOT NULL,

DataPrenotazione DATE NOT NULL DEFAULT SYSDATE,

CONSTRAINT PK_PRENOTAZIONI PRIMARY KEY(IdPrenotazione),

CONSTRAINT CK_PRENOTAZIONI CHECK(Pagato IN ('Y', 'N'))

)

STORAGE(INITIAL 35936K NEXT 1K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE PRENOTAZIONI_STORICO(

IdPrenotazione NUMBER(7),

Utente NUMBER(4) NOT NULL,

Programma NUMBER(4) NOT NULL,

Pagato CHAR(1) NOT NULL,

DataPrenotazione DATE NOT NULL,

CONSTRAINT PK_PRENOTAZIONI_STORICO PRIMARY KEY(IdPrenotazione),

CONSTRAINT CK_PRENOTAZIONI_STORICO CHECK(Pagato IN ('Y', 'N'))

)

TABLESPACE ts_storico

STORAGE(INITIAL 35936K NEXT 35936K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE

0);

CREATE TABLE UTENTI(

CodUtente NUMBER(4),

Username VARCHAR2(10) UNIQUE NOT NULL,

Pwd VARCHAR2(8) NOT NULL,

Cognome VARCHAR2(15) NOT NULL,

Nome VARCHAR2(15) NOT NULL,

Email VARCHAR2(30) UNIQUE NOT NULL,

CONSTRAINT PK_UTENTI PRIMARY KEY(CodUtente),

CONSTRAINT CK_UTENTI1 CHECK(LENGTH(Username)>5),

CONSTRAINT CK_UTENTI2 CHECK(LENGTH(Pwd)>5),

CONSTRAINT CK_UTENTI3 CHECK(Pwd!=Username);

)

STORAGE(INITIAL 81K NEXT 16K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 0);

CREATE TABLE AFFERENZE_POSTO(

Prenotazione NUMBER(7),

NumPosto NUMBER(3),

NumFila NUMBER(3),

Sala CHAR(1),

Cinema CHAR(2),

Prezzo NUMBER(4,2) NOT NULL,

CONSTRAINT P