GESTIONE “ROMANZI-FILM”
S i v o g l i o n o g e s t i r e , c o n u n a b a s e d i d a t i , l e i n f o r m a z i o n i r e l a t i v e a i f i l m , a i r o m a n z i d a c u i s o n o s t a t i e v e n t u a l m e n t e t r a t t i e d i r e l a t i v i a u t o r i .
G l i e l e m e n t i c a r a t t e r i z z a n t i i l p r o b l e m a s o n o : i r o m a n z i , g l i a u t o r i e d i f i l m .
S i c o n s i d e r i i l c a s o i n c u i u n a u t o r e p u ò s c r i v e r e p i ù r o m a n z i m a u n r o m a n z o d e v e e s s e r e s c r i t t o d a u n s o l o a u t o r e .
N e l d a t a b a s e , p e r t a n t o , s i d e v o n o g e s t i r e l e s e g u e n t i t a b e l l e :
• a u t o r i c o n d a t i a n a g r a f i c i ( n o m e , c o g n o m e , d a t a n a s c i t a , d a t a m o r t e , n a z i o n a l i t à ) ;
• r o m a n z i c o n t i t o l o , a u t o r e , a n n o p u b b l i c a z i o n e ;
• f i l m c o n t i t o l o , t r a m a , r e g i s t a , p r o d u t t o r e , a n n o p r o d u z i o n e ;
L ' e s e r c i z i o è u n a r i e l a b o r a z i o n e d i u n e s e r c i z i o p r o p o s t o i n
http://www.di.unipi.it/~leoni/BDeSI/E2.Esercizi%20di%20SQL.pdf
Considerato il problema proposto si realizzi:
uno schema concettuale della base di dati;
uno schema logico della base di dati;
la definizione delle relazioni della base di dati in linguaggio SQL.
Analisi dei dati
Le entità che possono essere individuate per risolvere il problema sono :
ENTITA’
Autori contiene l’elenco degli autori dei romanzi;
Romanzi contiene l’elenco dei romanzi;
Film contiene l’elenco dei film;
Nazionalita ontiene l'elenco degli stati e le descrizioni della nazionalità;
Gli attributi, per ciascuna entità, individuati per risolvere il problema sono :
ATTRIBUTI
Sistema informativo “Gestione Romanzi-Film”
Tipo di entità Attributi
Autore idautore, nome, cognome, anno_nascita,anno_morte, nazionalita;
Romanzo idromanzo,titolo,autore,anno pubblicazione;
Film idfilm,titolo,trama,regista,produttore, annoproduzione, romanzo;
Nazionalita idnazionalita,stato,nazionalita;
Tra l’entità Autore e l’entità Romanzo esiste un’associazione uno-a-molti in quanto un autore può scrivere uno o più romanzi mentre un romanzo, come richiesto dalla traccia, deve essere scritto da un solo autore.
Tra l’entità Romanzo e l’entità Film esiste un’associazione uno-a-molti in quanto da un romanzo possono essere tratti uno o più film mentre un film può essere tratto da un solo romanzo.
Tra l’entità Nazionalita l’entità Autore esiste un’associazione uno-a-molti in quanto ad un autore deve essere attribuita una nazionalità mentre una nazionalità può essere attribuita ad uno o più autori.
Schema concettuale della base di dati
Le relazioni tra i tipi di entità nel modello dei dati sono individuate nel seguente Modello Entità/Associazioni (E/R) :
Nel modello E/R, inoltre, sono stati indicati, oltre ai nomi delle entità, l’opzionalità od obbligatorietà delle associazioni (indicate rispettivamente con linea tratteggiata o continua).
Il modello viene verificato utilizzando le regole di lettura :
ogni autore deve essere attribuito ad uno o più romanzi, ogni romanzo deve essere attribuito ad un solo autore.
un autore deve essere associato ad una nazionalità, una nazionalità può essere associata ad un autore.
da un romanzo possono essere tratti uno o più film, un film può essere tratto da un romanzo.
NAZIONALITA NAZIONALITA ROMANZO
ROMANZO AUTORE AUTORE
FILM FILM
Schema logico della base di dati
Come tipo di schema logico, dovendo poi realizzarlo nello standard SQL, si sceglie di utilizzare quello relazionale.
Applicando le regole di corrispondenza tra il modello E/R ed il modello Relazionale si passa dal precedente schema concettuale al modello logico che viene descritto sia nella forma testuale che in quella grafica.
Rappresentazione testuale dello schema logico :
Nella rappresentazione testuale si elencano le tabelle (indicando per ciascuna i campi) che rappresentano le entità dello schema concettuale e le tabelle che rappresentano le eventuali relazioni molti-a-molti, introducendo, inoltre, le chiave esterne per rappresentare le associazioni (le chiavi primarie sono sottolineate, le chiavi esterne sono in corsivo).
Nello schema E/R precedente si può osservare che non esiste alcuna relazione molti-a-molti (N:N) e, quindi, non si devono inserire entità ausiliarie.
Si definiscono, innanzitutto, le tabelle che rappresentano le entità dello schema concettuale e successivamente si definiscono le tabelle che rappresentano le associazioni che in questo caso non si devono definire.
Le seguenti tabelle rappresentano le entità :
t a b n a z i o n a l i t a ( i d n a z i o n a l i t a , s t a t o , n a z i o n a l i t a )
t a b a u t o r i ( i d a u t o r e , n o m e , c o g n o m e , a n n o _ n a s c i t a , a n n o _ m o r t e , k s n a z i o n a l i t a )
t a b r o m a n z i ( i d r o m a n z o , t i t o l o _ r o m a n z o , k s a u t o r e , a n n o _ p u b b l i c a z i o n e )
t a b f i l m ( i d f i l m , t i t o l o _ f i l m , t r a m a , r e g i s t a , p r o d u t t o r e , a n n o _ p r o d u z i o n e , k s r o m a n z o )
Per realizzare l’associazione uno-a-molti fra l’entità Nazionalita e l’entità Autore si introduce, fra gli attributi dell’entità a molti (Autore), la chiave esterna ksnazionalita associata alla chiave primaria idnazionalita dell’entità a uno (Nazionalita .
Per realizzare l’associazione uno-a-molti fra l’entità Autore e l’entità Romanzo si introduce, fra gli attributi dell’entità a molti (Romanzo), la chiave esterna ksautore associata alla chiave primaria idautore dell’entità a uno (Autore).
Per realizzare l’associazione uno-a-molti fra l’entità Romanzo e l’entità Film si introduce, fra gli attributi dell’entità a molti (Film), la chiave esterna ksromanzo associata alla chiave primaria idromanzo dell’entità a uno (Romanzo).
Rappresentazione grafica dello schema logico :
La rappresentazione grafica dello schema logico relativo al sistema informativo per la gestione delle Romanzi-Film è il seguente :
Definizione delle Tabelle
Nel definire le tabelle e la struttura delle stesse, è consigliabile elencarle partendo dalle tabelle che non presentano chiavi esterne.
In successione si definiscono le tabelle le cui chiavi primarie sono utilizzate da altre tabelle come chiavi esterne.
Nel prospetto seguente, per es., si definiscono prima la tabella tabnazionalita, la tabella tabautori (che ha fra gli attributi un campo definito come chiave esterna alla tabella tabnazionalita), tabromanzi (che ha fra gli attributi un campo definito come chiave esterna alla tabella tabautori) e,quindi, la tabella tabfilm (che ha fra gli attributi un campo definito come chiave esterna alla tabella tabromanzi).
Tabella Nome campo Chiave Tipo dati Dim. Dec. Null Descrizione tabnazionalita idnazionalita Primaria Numerico 5 Autoincremento
stato Carattere 70
nazionalita Carattere 30
tabautori idautore Primaria Numerico 10 Autoincremento
nome Carattere 20
cognome Carattere 20
sesso Carattere 1
anno_nascita Carattere 4
anno_morte Carattere 4
ksnazionalita Esterna Numerico 10
tabromanzi idromanzo Primaria Numerico 10 Autoincremento
titolo_romanzo Carattere 830 Vincoli: valori unici
ksautore Esterna Numerico 10 Integrità referenziale con idautore della tabella tabautori
anno_pubblicazione Carattere 4
Tabella Nome campo Chiave Tipo dati Dim. Dec. Null Descrizione
tabfilm idfilm Primaria Numerico 10 Autoincremento
titolo_film Carattere 80
trama Testo
regista Carattere 30
produttore Carattere 30
anno_produzione carattere 4
cilindrata Carattere 5
potenza Carattere 5
alimentazione Carattere 3
ksromanzo Esterna Numerico 10
Definizione delle relazioni della base di dati in SQL
Lo schema logico relazionale precedente (tabelle, relazione ed applicazione dei vincoli di integrità dei dati e referenziale) può essere creato con le seguenti istruzioni SQL (proposte in versione per MySQL).
MySQL
CREATE TABLE tabnazionalita (
idnazionalita smallint(5) unsigned NOT NULL AUTO_INCREMENT,
stato char(70) NOT NULL DEFAULT '', nazionalita char(30) NOT NULL DEFAULT '', PRIMARY KEY (idnazionalita)
) ENGINE=MyISAM;
CREATE TABLE tabautori (
idautore int(10) NOT NULL AUTO_INCREMENT, nome char(20) NOT NULL DEFAULT '',
cognome char(20) NOT NULL DEFAULT '', anno_nascita char(4) DEFAULT '',
anno_morte char(4) DEFAULT '',
ksnazionalita smallint(5) unsigned NOT NULL, PRIMARY KEY (idautore)
) ENGINE=MyISAM CREATE TABLE tabromanzi (
idromanzo int(10) unsigned NOT NULL AUTO_INCREMENT,
titolo_romanzo char(80) NOT NULL DEFAULT '',
ksautore int(10) unsigned NOT NULL, anno_pubblicazione char(4) DEFAULT '', PRIMARY KEY (idromanzo)
) ENGINE=MyISAM
CREATE TABLE tabfilm (
idfilm int(10) unsigned NOT NULL AUTO_INCREMENT,
titolo_film char(80) NOT NULL DEFAULT '', trama text ,
regista char(30) DEFAULT NULL, produttore char(30) DEFAULT '', anno_produzione char(4) DEFAULT '', ksromanzo int(10) unsigned DEFAULT NULL, PRIMARY KEY (idfilm)
) ENGINE=MyISAM
Nota
: le istruzioni proposte per MySQL sono relative all’uso di tabelle tipo MyISAM che non supportano l'integrità referenziale definita con FOREIGN KEY…. Il tipo di tabella InnoDB, invece, supporta la FOREIGN KEY similmente ad ACCESS.Per la differenza tra le tabelle di tipo MyISAM e InnoDB consultare :
https://www.mrwebmaster.it/mysql/differenza-tabelle-tipo-myisam-innodb_7088.html