Primo Compitino di Basi di Dati I (II anno)

Primo compitino: Modelli dei Dati di Nuova
Generazione
14 Aprile 2005
NOME:
COGNOME:
Esercizio
1
2
Totale
Punti
previsti
20
13
33
Punti
assegnati
1
Esercizio 2
Si consideri il seguente schema relazionale, relativo ai programmi dei teatri genovesi:
TEATRI(CodT, NomeTeatro, Indirizzo, Tel)
SPETTACOLI(CodS, CodT, Titolo, Autore, Regista, DataI, DataF)
ATTORI_E_REGISTI(CodA, Nome,Cognome, CodC)
COMPAGNIE(CodC, Nome, Citta_di_riferimento)
RECITA_In(CodS, CodA)
Il campo Regista in SPETTACOLI e’ chiave esterna su ATTORI_E_REGISTI. Le altre chiavi esterne sono
quelle ovvie.
Il campo indirizzo e’ di fatto un campo composto da (Via, Numero, Cap).
I campi (Nome,Cognome) in ATTORI_E_REGISTI sono chiave secondaria.
Il campo NomeTeatro e’ chiave secondaria in TEATRI.
I campi (CodT,Titolo,DataI) sono chiave secondaria in SPETTACOLI .
Il campo Nome e’ chiave secondaria in COMPAGNIE.
Non ci sono altre chiavi secondarie.
Si richiede di:
1. Progettare uno schema concettuale ad oggetti equivalente allo schema relazionale precedente,
motivando le scelte effettuate. Lo schema deve contenere almeno un’aggregazione complessa (di
tipo SET). Si stabilisca quali chiavi, primarie e secondarie, e’ ragionevole mantenere nel nuovo
schema.
2. Tradurre lo schema concettuale in uno schema logico relazionale ad oggetti per Oracle, motivando le
scelte effettuate.
3. Presentare gli statement di creazione della parte di schema logico relativa all’entita’ che contiene un
campo che rappresenta un’aggregazione complessa utilizzando il sistema dei tipi di Oracle (si
presentino solo le definizioni relative alla definizione dell’entita’ principale e dei tipi dei suoi
attributi se tabelle annidate).
4. Supponendo che le tabelle contengano un certo numero di tuple, presentare gli statement per inserire
nella base di dati le seguenti informazioni:
o
o
e’ in scena un nuovo spettacolo con codice 12, in scena presso il Teatro della Corte dal 6 Aprile
2005 al 24 Aprile 2005, dal titolo “L’Illusione Comica”, di Pierre Corbeille, con regista Marco
Sciaccaluga,;
l’attore principale (si vuole inserire solo questo) e’ Eros Pagni.
5. Presentare gli statement SQL per Oracle per le seguenti operazioni:
o
Determinare gli spettacoli in scena presso i teatri periferici (cap > 16100) dal 3 Maggio al 3
Giugno. Restituire il nome del teatro, insieme al suo indirizzo nella forma Via, Numero, Cap, e il
titolo dello spettacolo.
o
Determinare nome e cognome degli attori/registi che hanno partecipato allo spettacolo con titolo
“L’Avaro”, in scena presso il Teatro della Corte, a partire dal 10/12/04.
o
Determinare per ogni teatro il numero di attori che hanno preso parte a spettacoli in scena presso
quel teatro nel 2004.
2
o
Inserire un nuovo attore L’attore si chiama Andrea Nicolini, ha codice 20 e lavora per la
compagnia del “Teatro Stabile di Genova”. Associare quindi tale attore allo spettacolo
inserito al punto 4.
3
Tipi in Oracle
CREATE TYPE Nome AS OBJECT
( Dichiarazioni_attributi)
[FINAL | NOT FINAL];
CREATE TYPE Nome UNDER Nome_superclasse
( Dichiarazioni_attributi)
[FINAL | NOT FINAL];
Default: NOT FINAL
CREATE TYPE Nome AS TABLE OF Tipo;
CREATE TYPE Nome AS VARRAY(Numero) OF Tipo;
CREATE TABLE Nome OF Nome_Tipo
[({Vincoli chiave primaria, chiave esterna, not null, su campi nel tipo |
SCOPE FOR NomeCampoREF is NomeTabella})]
[NESTED TABLE NomeCampoNested STORE AS NomeTabella];
CREATE TABLE Nome
(Dichiarazioni_attributi)
[NESTED TABLE NomeCampoNested STORE AS NomeTabellaStorage];
con



NomeCampoNested: nome del campo della tabella di tipo TABLE.
NomeTabellaStorage: nome tabella utilizzata per la memorizzazione della tabella nested.
I tipi utilizzati in Dichiarazioni_attributi possono essere:

Tipi atomici

Tipi definiti dall’utente

REF Nome [SCOPE IS Nome_tabella]
La clausola SCOPE IS puo’ essere inserita solo negli statement di CREATE TABLE.
La clausola puo’ anche essere aggiunta ai campi delle tabelle tipate (SCOPE FOR).
Funzioni OR in Oracle




ref( ): dato un oggetto di un certo tipo, restituisce l’identificatore per quell’oggetto
deref( ): dato un identificatore, restituisce l’oggetto puntato
value( ): prende un alias di relazione e restituisce l’oggetto tupla associato (utilizzando il costruttore opportuno)
TABLE( ): data una valori di tipo collezione, restituisce la tabella corrispondente al contenuto di tale valore.
Trigger SQL-99
CREATE TRIGGER Nome
{BEFORE|AFTER} Evento ON Relazione
[REFERENCING {OLD AS Var | NEW AS Var | OLD AS Var | NEW AS Var}]
[FOR EACH {ROW | STATEMENT}]
[WHEN Condizione ]
Comandi SQL
con



Evento: INSERT, DELETE, UPDATE [OF Lista Attributi]
Condizione: clausola WHERE SQL
Se l’azione comprende piu’ di un comando: BEGIN ATOMIC … END;
4