1 ANALISI DELLA REALTA’
Un comune deve realizzare una base di dati per raccogliere le informazioni degli immobili che ricadono sotto la propria competenza (di proprietà del comune o in comproprietà) per due motivi
Effettuare un censimento annuale di tutti gli immobili di proprietà (anche parziale) del Comune
Consentire l’accesso ad un sistema informatico esterno per realizzare una “banca dati unica a livello nazionale” (Diventa un Web service esponendo delle web API)
Si sottolinea che gli immobili possono essere situati sia sullo stesso comune che su un altro comune Per ogni immobile si vogliono memorizzare:
dati identificativi (dati catastali, categoria catastale, localizzazione)
titolo di proprietà ed utilizzo (diritto, percentuale di proprietà, titolo proprietà, destinazione d’uso) caratteristiche dell’immobile (metrature, anno costruzione, valore a bilancio comunale, valore di mercato, altri valore di acquisto, valore di vendita)
Operazioni di acquisizione, alienazione/vendita con relative date.
Numero di inventario Breve descrizione
Si vuole inoltre tenere traccia di eventuali contratti di locazione indicando i dati dei locatari e i dati di inizio fine locazione.
Poiché uno stesso immobile può essere oggetto di più operazioni di alienazione(vendita) e acquisizione da parte del comune, è opportuno realizzare un’entità OPERAZIONE che sarà di tipo acquisto o di tipo vendita, e che avrà come attributi la data dell’operazione, il valore dell’immobile nell’operazione di acquisto o vendita.
Il valore a bilancio comunale e il valore di stima di mercato sono attributi dell’immobile.
Poichè La categoria catastale, la tipologia di diritto sull’immobile, il titolo di proprietà e la destinazione d’uso sono delle categorie, è utile, soprattutto in fase di inserimento dati, che vengano considerate delle entità.
Gli immobili possono essere in comproprietà del comune e di altre persone o enti. La percentuale di
proprietà del comune è un attributo dell’immobile. I dati dei comproprietari non vanno memorizzati perché eventuali alienazioni della quota parte non del comune potrebbe essere alienata da parte del
comproprietario ad insaputa del comune.
Per ridurre il numero di dati che descrivono un immobile, rendendo quindi più efficienti le query, si individua un’entità riferimento catastale in associazione 1 a 1 con l’entità immobile
Per risolvere più semplicemente la query C è utile aggiungere un attributo ridondante “alienato” all’entità immobile, questo attributo booleano indica se attualmente l’immobile è alienato (quindi non più in possesso del comune) oppure no. Il valore di default dell’attributo è 0 E’ necessario che ogni qual volta un immobile sia oggetto di una operazione di alienazione, oltre alla tabella operazioni venga valorizzato ad 1 anche l’attributo “alienato” dell’immobile.
Per facilitare gli inserimenti dei dati si individuano le entità COMUNE, PROVINCIA, NAZIONE E CAP contenente tutti i dati dei comuni Italiani (presumibilmente già a disposizione del sistema informatico del Comune che sta realizzando il database) a cui sarà possibile aggiungere i Comuni di altre nazioni. Con il
2 codice nazione si intende il codice ISO 3166-1 a due cifre che identifica ogni Nazione. Si presume che gli immobili si trovino tutti in comuni italiani, i locatari invece potrebbero essere stranieri. Non essendo a conoscenza della modalità di codifica univoca dei comuni stranieri si trascura per ora il problema
ipotizzando che i comuni e le provincie di Nazioni fuori dall’Italia abbiano codici identificativi univoci diversi da quelli Italiani.
Si individuano dunque le seguenti entità
IMMOBILE: rappresenta un immobile, contiene i dati che descrivono l’immobile, i suoi riferimenti catastali, il suo valore a bilancio, il valore iniziale e il valore di mercato, la quota percentuale di proprietà posseduta dal comune
LOCATARIO: persona o ente che prende in affitto un immobile
OPERAZIONE: rappresenta un’operazione di acquisto o alienazione/vendita dell’immobile, contiene il tipo di operazione, la data in cui essa è avvenuta e il valore assegnato all’immobile nell’operazione. Si ipotizza che un immobile possa essere acquistato e alienato più volte.
CATEGORIA_CATASTALE: codice e descrizione della categoria catastale di appartenenza dell’immobile.
TIPOLOGIA_DIRITTO: identificativo e descrizione (piena proprietà , comproprietà ecc.)
TITOLO_PROPRIETA: contiene un identificativo e una descrizione del titolo di proprietà (indica a che titolo il comune possiede l’immobile: compravendita, esproprio, donazione..)
DESTINAZIONE_USO: identificativo e descrizione della destinazione d’uso dell’immobile.
COMUNE: il codice catastale di un comune è una stringa di 4 caratteri CAP
PROVINCIA NAZIONE
3 PUNTO 2
Diagramma E/R
PUNTO 3
Database schema:
CAP:
NAZIONI: codice, nome.
PROVINCIE: sigla, nome, nazione FK: nazione=Nazione.codice COMUNI: codice, nome, provincia
FK: provincia=provincia.sigla CAP: CAP, comune
FK: comune=comune.codice
CATEGORIE_CATASTALI: codice, descrizione
TIPOLOGIE_DIRITTO: id_tipologia_diritto, descrizione
4 TITOLI_PROPRIETA: id_titolo_proprieta, descrizione
DESTINAZIONE_USO: id_destinazione_uso, descrizione
IMMOBILI (numero_inventario, descrizione, numero_immobile, unita_catastale,partita, foglio, mappale, subalterno, comune, indirizzo, percentuale_proprieta,superficie,
cubatura,numero_vani,anno_costruzione,valore_bilancio,valore_iniziale,valore_mercato, alienato, categoria_catastale, tipologia_diritto, titolo_proprieta, destinazione_uso)
FK: categoria_catastale CATEGORIE_CATASTALI.codice Tipologia_diritto TIPOLOGIE_DIRITTO.id_tipologia_diritto Titolo_proprieta TITOLI_PROPRIETA.id_titolo_proprieta Destinazione_uso DESTINAZIONI_USO.id_destinazione_uso Comune Comuni.codice
OPERAZIONI (id_operazione, tipo, data,valore, numero_immobile) FK: numero_immobile immobile.numero.inventario
LOCATARI:( id_locatario, CF, cognome, nome, ragione, sociale, partita_iva, comune, indirizzo)
AFFITTI: numero_immobile, locatario, data_inizio, data_fine FK: numero_immobile immobile.numero.inventario
locatario LOCATARI.id_locatario comune COMUNI.codice
NAZIONI
NOME TIPO DIMENSIONE CHIAVE NOT NULL ALTRO
Codice CHAR 2 PK X
Nome Varchar 30 X
PROVINCIE
NOME TIPO DIMENSIONE CHIAVE NOT NULL ALTRO
Sigla CHAR 2 PK X
Nome Varchar 30 X
Nazione Char 2 FK X FK:nazioni.codice
COMUNI
NOME TIPO DIMENSIONE CHIAVE NOT NULL ALTRO
5
Codice Char 4 PK X
Nome Varchar 30 X
Provincia Char 2 FK FK:provincie.sigla. Si lascia la
possibilità che sia NULL per comuni stranieri
CAP
NOME TIPO DIMENSIONE CHIAVE NOT NULL ALTRO
CAP Varchar 5 PK X
Comune Char 4 FK X FK:comuni.codice
CATEGORIE_CATASTALI
NOME TIPO DIMENSIONE CHIAVE NOT NULL ALTRO
Codice varchar 4 PK X
Descrizione Varchar 100 X
TIPOLOGIE_DIRITTO
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Id_tipologia_diritto numerico PK X AUTO_INCREMENT
Descrizione Varchar 100 X
TITOLI_PROPRIETA
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Id_titolo_proprieta numerico PK X AUTO_INCREMENT
Descrizione Varchar 100 X
DESTINAZIONE_USO
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Id_destinazione_uso numerico PK X AUTO_INCREMENT
Descrizione Varchar 100 X
IMMOBILI
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Numero_inventario int PK X AUTO_INCREMENT
Descrizione text
Numero_immobile int X
Unita_catastale Int X
partita Int X
foglio Int X
6
mappale Int X
subalterno int
Comune char 4 FK X FK: comuni.codice
Indirizzo Varchar 50 X
Percentuale proprieta
Float X
Superficie Float X
cubatura Float X
Numero_vani Int Null se non è residenziale
Anno_costruzione Int Può non essere noto
Valore_bilancio float Valore_iniziale float Valore_mercato float
Alienato Boolean X Default=0
Categoria_catastale Varchar 4 FK X FK: Categorie_catastali. Codice
Tipologia_diritto Int FK X FK:Tipoogie_diritti.id_tipologi
a_diritto
Titolo_proprieta Int FK X FK:
Titoli_proprieta.id_titolo_prop rieta
Destinazione_uso Int FK X FK:Destinaizoni_uso.id_destin
azione_uso
OPERAZIONI
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Id_operazione INT PK X AUTO_INCREMENT
Tipo Enum X Acqusito o
Alienazione/Vendita
Data date X
Numero_immobile Int FK X Immobili.numero_inventario
LOCATARI
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Id_locatario Int PK X AUTO_INCREMENT
CF varchar 16 X
Cognome Varchar 30 se c’è ragione sociale non ci
deve essere cognome e nome e viceversa (cognome is not null and nome is not null AND (ragione_sociale is null) or (ragione_sociale is not null and (cognome is null and nome is null))
Nome Varchar 30
Ragione_sociale Varchar 30
Partita_iva Char 11
Comune char 4 FK X FK: comuni.codice
Indirizzo Varchar 30 X
7 AFFITTI
NOME TIPO DIMENSIONE CHIAVE NOT
NULL
ALTRO
Numero_immobile Int PK/FK X FK:immobili.numero_inventario
Locatario Int PK,FK X FK:locatari.id_locatario
Data_inizio Date PK X
Data_fine Date
PUNTO 4 Relazioni NAZIONI
create table nazioni (
codice char(2) not null primary key, nome varchar(30) not null
);
PROVINCIE
create TABLE provincie (
sigla char(2) not null primary key, nome varchar(30) not null, nazione varchar(2) not null,
constraint fk_provincia_nazione FOREIGN KEY(nazione) REFERENCES nazioni(codice) );
COMUNI
create table comuni (
codice char(4) not null primary key, nome varchar(3) not null,
provincia char(2),
constraint fk_comuni_provincie foreign key(provincia) REFERENCES provincie(sigla) )
CAP
create table CAP (
cap varchar(5) not null primary key, comune char(4) not null,
CONSTRAINT fk_comuni_CAP FOREIGN key(comune) REFERENCES comuni(codice) )
8 CATEGORIE_CATASTALI
create table categorie_catastali (
codice varchar (4), descrizione varchar (100),
CONSTRAINT pk_categorie_catastali PRIMARY KEY(codice) )
TIPOLOGIE_DIRITTO
create table tipologie_diritto (
id_tipologia_diritto int AUTO_INCREMENT primary key, descrizione varchar(100) not null
)
TITOLI_PROPRIETA
create table titoli_proprieta (
id_titolo_proprieta int AUTO_INCREMENT primary key, descrizione varchar(100) not null
)
DESTINAZIONI_USO
create table destinazioni_uso (
id_destinazione_uso int AUTO_INCREMENT primary key, descrizione varchar(100) not null
)
IMMOBILI
create table immobili (
numero_inventario int AUTO_INCREMENT not null PRIMARY KEY, descrizione text,
numero_immobile int not null, unita_catastale int not null, partita int not null,
foglio int not null, mappale int not null, subalterno int,
comune char(4) not null, indirizzo varchar(50) not null, percentuale_proprieta float not null, superficie float not null,
cubatura float not null,
9 numero_vani int,
anno_costruzione int, valore_bilancio float, valore_iniziale float, valore_mercato float,
alienato boolean default 0 not null, categoria_catastale varchar(4) not null, tipologia_diritto int not null,
titolo_proprieta int not null, destinazione_uso int not null,
CONSTRAINT fk_immobili_categoria_catastale FOREIGN key(categoria_catastale) REFERENCES categorie_catastali(codice),
CONSTRAINT fk_immobili_tipologie_diritto FOREIGN KEY(tipologia_diritto) REFERENCES tipologie_diritto(id_tipologia_diritto),
CONSTRAINT fk_immobili_titoli_proprieta FOREIGN KEY(titolo_proprieta) REFERENCES titoli_proprieta(id_titolo_proprieta),
CONSTRAINT fk_immobili_destinazioni_uso FOREIGN KEY(destinazione_uso) REFERENCES destinazioni_uso(id_destinazione_uso),
CONSTRAINT fk_immobili_comuni FOREIGN KEY(comune) REFERENCES comuni(codice) );
OPERAZIONI
create table operazioni (
id_operazione int AUTO_INCREMENT not null primary key, tipo enum('acquisto','alienazione/vendita') not null, data date not null,
numero_immobile int not null,
CONSTRAINT fk_operazioni_immobili FOREIGN KEY(numero_immobile) REFERENCES immobili(numero_inventario)
);
LOCATARI
create table locatari (
id_locatario int AUTO_INCREMENT not null primary key, cf varchar(16) not null,
partita_iva char(11), cognome varchar(30), nome varchar(30),
ragione_sociale varchar(30), comune char(4) not null, indirizzo varchar(30) not null,
CONSTRAINT fk_locatari_comuni FOREIGN KEY(comune) REFERENCES comuni(codice), CONSTRAINT ragione_sociale_o_cognome_nome CHECK(cognome is not null and nome is not null AND (ragione_sociale is null) or (ragione_sociale is not null and (cognome is null and nome is null)))
);
10 AFFITTI
create table affitti (
numero_immobile int not null, locatario int not null,
data_inizio date not null, data_fine date,
CONSTRAINT pk_affitti PRIMARY key(numero_immobile,locatario,data_inizio), CONSTRAINT fk_affitti_immobili FOREIGN KEY(numero_immobile) REFERENCES immobili(numero_inventario),
CONSTRAINT fk_affitti_locatari FOREIGN KEY(locatario) REFERENCES locatari(id_locatario) )
PUNTO 5
Elenco delle abitazioni di tipo civile alienate nell’anno 2021
SELECT immobili.*
FROM immobili, categorie_catastali, operazioni
WHERE immobili.categoria_catastale=categorie_ca3tastali.codice AND immobili.numero_inventario=operazioni.numero_immobile AND categorie_catastali.descrizione=’Abitazione di tipo civile’
AND operazioni.tipo=”alienazione”
AND YEAR(operazioni.data)=’2021’;
Elenco delle scuole con relativa descrizione e indirizzo
SELECT immobili.numero_inventario, immobili.descrizione, immobili.comune, immobili.indirizzo FROM immobili, destinazioni_uso
WHERE destinazioni_uso.id_destinazione_uso=immobili.destinazione_uso AND destinazioni_uso.descrizione=”scuola”
11 Il totale del valore a bilancio per ogni categoria catastale degli immobili in essere (non alienati)
SELECT categoria_catastale, sum(valore_bilancio) as somma_valore_bilancio_edifici FROM immobili
WHERE alienato=0
GROUP BY categoria_catastale
Il locatario col maggior numero di immobili in affitto nel corso dell’anno 2020.
SELECT locatario.id_locatario, count(distinct immobili.numero_immobile) as numero_immobili_in_affitto FROM locatario, affitti, immobili
WHERE locatario.id-locatario=affitti.id_locatario
AND affitti.numero_immobile=immobili.numero_inventario
AND YEAR(data_inizio)<=2020 AND (YEAR(data_fine)>2020 OR data_fine IS NULL) GROUP BY locatario.id_locatario
HAVING numero_immobili_in_affitto=
(SELECT MAX(numero_immobili_in_affitto) FROM
(SELECT locatario.id_locatario, count(distinct immobili.numero_immobile) as numero_immobili_in_affitto
FROM locatario, affitti, immobili
WHERE locatario.id-locatario=affitti.id_locatario
AND affitti.numero_immobile=immobili.numero_inventario
AND YEAR(data_inizio)<2020 AND (YEAR(data_fine)>2020 OR data_fine IS NULL) GROUP BY locatario.id_locatario) as T)
PUNTO 6
Per realizzare un’applicazione web based che consenta l’interazione con i dati memorizzati nel database sono necessari un web server (Apache) ed un DBMS (MySQL). L’applicazione sarà realizzata con linguaggio HTML e CSS per la realizzazione del layout, linguaggio javascript per l’interazione con l’applicazione lato client, linguaggio PHP lato client per l’interazione con la base di dati lato server.
12 Si mostra il codice essenziale per la visualizzazione dei risultati di cui alla query b) “Elenco delle scuole con relativa descrizione ed indirizzo.
L’aspetto della pagina sarà il seguente
Pagina scuole.php
<html>
<head>
<title>Elenco scuole</title>
</head>
<body>
<?php
$connection=new mysqli("localhost","root","","provaccia");
if ($connection->errno)
die("Impossibile connettersi al database");
else {
$query="SELECT immobili.numero_inventario, immobili.descrizione, immobili.comune, immobili.indirizzo
FROM immobili, destinazioni_uso WHERE
destinazioni_uso.id_destinazione_uso=immobili.destinazione_uso AND destinazioni_uso.descrizione='scuola'";
$result=$connection->query($query);
if ($result->num_rows==0)
echo"Nessuna scuola presente";
else { ?>
<h4>Elenco scuole</h4>
<table border>
<tr>
<th>id immobile</th>
<th>descrizone</th>
13 <th>Comune</th>
<th>Indirizzo</th>
</tr>
<?php
while($row=$result->fetch_array()) {
echo"<tr>";
echo"<td>$row[numero_inventario]</td>";
echo"<td>$row[descrizione]</td>";
echo"<td>$row[comune]</td>";
echo"<td>$row[indirizzo]</td>";
echo"</tr>";
} ?>
</table>
<?php }
$result->free();
$connection->close();
} ?>
</body>
</html>
14 SECONDA PARTE
PUNTO I
Considerata la relazione OPERE (Cod_opera, Cod_Museo, Titolo_Opera, Nome_Museo, Citta_Museo, DataInizioEsposizione, DataFineEsposizione). Si verifichino le proprietà di normalizzazione e si proponga, eventualmente, uno schema equivalente che rispetti la terza forma normale, motivando le scelte effettuate.
Le relazioni di un database sono dette “ben formate” quando rispettano determinate caratteristiche indicate con il termine di “forme normali”. Se una relazione non rispetta le forme normali potrebbero verificarsi nella base di date alcune problematiche indicate con il termine di anomalie.
Le anomalie sono di tre tipologie
anomalia da inserimento: impossibilità di inserire determinati dati senza vìolare i vincoli di integrità
anomalia da cancellazione: l’eliminazione di determinati non può avvenire senza che altri dati vengano eliminati indesideratamente
anomalia da modifica: la modifica di un dato deve essere replicata in più tuple della base di dati.
Se in un datbase vi sono relazioni che vìolano le forme normali, è possibile ristrutturare le tabelle in modo tale che le forme normali non siano più violate e di conseguenza che il database non sia più affetto dalle anomalie.
Le prime prime tre forme normali sono le seguenti:
1. Una relazione deve avere un identificatore univoco e attributi atomici
2. Non devono esserci, in una relazione con chiave candidata composta, attributi non chiave che dipendono parzialmente dalla chiave candidata
3. Non devono esserci attributi non chiave che dipendono da altri attributi non chiave.
Verifichiamo il rispetto delle tre forme normali nella relazione Opere.
Ipotizzando che un’opera possa essere esposta in uno stesso museo per periodi diversi, l’unica chiave candidata nella relazione è la seguente:
(Cod_opera, Cod_museo, dataInizioesposizione) Dipendenze funzionali:
Cod opera Titolo_Opera vìola 2NF
Cod_museo Nome_museo, Citta_museo Vìola 2NF
Normalizzazione
OPERE: (Cod_opera, Titolo_Opera)
MUSEI: (Cod_museo, Nome_museo, citta_museo)
15 ESPOSIZIONI: (Cod_Opera, Cod_Museo, DataInizioEsposizione, DataFineEsposizione)
PUNTO II
In relazione al tema proposto nella prima parte, sviluppi la query SQL per calcolare la percentuale di immobili acquisite nel corso degli ultimi tre anni (sul totale degli immobili attualmente in essere).
select nuovi, totali, nuovi/totali*100 FROM
(SELECT count(*) as Nuovi FROM immobili, operazioni
WHERE immobili.numero_inventario =operazioni.numero_immobile AND immobili.alienato=0
AND operazioni.tipo='acquisto'
AND timestampdiff(YEAR,operazioni.data,current_date)<=3) as T1, (SELECT count(*) as Totali from Immobili where alienato=0) as T2;
OPPURE
SELECT (SELECT COUNT(*) FROM immobili,operazioni WHERE immobili.numero_inventario=operazioni.numero_immobile AND
timestampdiff(YEAR,operazioni.data,CURRENT_DATE)<=3)/(SELECT COUNT(*) FROM immobili WHERE immobili.alienato=0)*100
PUNTO III
SI illustri il concetto di TRANSAZIONE illustrando, nello specifico, il significato dell’ acronimo ACID
Le transazioni sono una sequenza di operazioni che di modifica dei dati di un database che avvengono in maniera atomica. L’utilizzo delle transazioni consente di evitare che la modifica o l’inserimento di nuovi dati nella base di dati renda la stessa inconsistente. Senza l’utilizzo delle transazioni c’è il rischio che, in un database multiutente, eventuali accessi in scrittura da parte di utenti diversi (oppure una sequenza di operazioni su più dati svolti dallo stesso utente) generino incorenza dei dati e quindi inconsistenza della base di dati. Un tipico esempio è quello di molteplici operazioni di accredito/addebito contemporanee su uno steso conto corrente. Senza le transazioni, le singole operazioni non sarebbero atomiche con il rischio che alcuni accrediti o addebiti non risultino effettuate nel saldo finale del conto corrente. La transizione si definisce quindi come una sequenza di operazioni di modifica di un database che viene eseguita in maniera atomica in modo da portare lo stato del database da uno stato inziale consistente ad un altro stato finale consistente. I moderni DBMS devono garantire per le transazioni le proprietà individuate dall’ acronimo ACID (Atomicity, Consistency, Isolation, Durbability).
Atomicity: Un’operazione di modifica del database deve avvenire in maniera atomica, quindi non deve essere svolta parzialmente, o avviene completamente oppure deve essere annullata. L’operazione che
16 annulla una transazione non andata a buon fine è detta “abort”, in seguito ad un “abort” l’operazione che riporta il database nello stato precedente a quello dell’inizio della transazione (BOT Begin OF Transation) è detta Rollback.
Consistency: Al termine dei una transazione un database deve essere in uno stato consistente, vale a dire che i dati non devono violare i vincoli di integrità.
Isolation: ogni transazione deve essere isolata dalle altre transazioni in esecuzione contemporaneamente, ciò significa che l’esito di una transazione non deve influenzare l’esito delle altre transazioni in corso. Il valore dei dati al termine di più transazioni in esecuzioni contemporaneamente deve essere lo stesso che si sarebbe ottenuto se le transazioni fossero state eseguite non contemporaneamente
Durability: al termine di una transazione i dati devono essere memorizzati in maniera permanente nella base di dati.
PUNTO IV
Elencare i possibili tipi join formulabili con SQL evidenziandone, anche attraverso esempi, le caratteristiche peculiari.
L’operazione di JOIN, chiamata CONGIUNZIONE in italiano, è un’operazione che può essere svolta fra due tabelle T1 e T2 fornendo come risultato una terza tabella T3 formata da tutti gli attributi di T1 e tutti gli attributi di T2
Il JOIN può essere definito, prendendo in prestito la terminologia dell’algebra relazionale, come una selezione svolta su un prodotto cartesiano, infatti il risultato di un JOIN è sempre un sottoinsieme del prodotto cartesiano fra le due tabelle T1 e T2. Premettendo che vi sono diverse sintassi per svolgere un JOIN in SQL, il fatto che il JOIN sia una sequenza di prodotto cartesiano seguito da una selezione si nota anche dalla seguente sintassi SQL per l’esecuzione del JOIN
SELECT *
FROM T1, T2 // questo è un prodotto cartesiano
WHERE T1.attributo1=T2.attributo2 // Condizione: questa è una selezione, fa sì che nel risultato del JOIN siano presenti solo le tuple che soddisfano la condizione espressa da questa selezione, vale a dire che i due attributi indicati abbiano lo stesso valore. (gli attributi devono essere dello stesso tipo)
Nell’esempio precedente il risultato del JOIN è dato dalle sole tuple del prodotto cartesiano che soddisfano la condizione. In base a come è espressa la condizione il join viene denominato in modi diversi:
THETA JOIN: qualsiasi condizione espressa come predicato dell’algebra di boole fra gli attributi delle due tabelle
EQUI JOIN: la condizione è una condizione di UGUAGLIANZA fra i due attributi (come nel caso in esempio) NATURAL JOIN: la condizione è una condizione di uguaglianza fra attributi che hanno lo stesso nome. Ad esempio:
condizione
17 WHERE personale.codicedipartimento=dipartimenti.codicedipartimento
Il JOIN così come è stato descritto, ossia i cui il risultato è una tabella che contiene solo le tuple del prodotto cartesiano che SODDISFANO la condizione, è detto INNER JOIN.
Oltre all’INNER JOIN è possibile svolgere anche altre tipologie di JOIN chiamati OUTER JOIN. Gli OUTER JOIN possibili sono: il LEFT JOIN, il RIGHT JOIN e il FULL JOIN.
Il LEFT JOIN
SELECT *
FROM T1 LEFT JOIN T2 ON (T1.attributo1=T2.attributo2)
fornisce come risultato la tabella T3 che contiene, oltre alle tuple del prodotto cartesiano che soddisfano la condizione, anche TUTTE LE TUPLE DI T1 in cui gli attributi NON soddisfano la condizione, il valore degli attributi di T2 in queste tuple è viene posto a NULL
Il RIGHT JOIN
SELECT *
FROM T1 RIGHT JOIN T2 ON (T1.attributo1=T2.attributo2)
fornisce come risultato la tabella T3 che contiene, oltre alle tuple del prodotto cartesiano che soddisfano la condizione, anche TUTTE LE TUPLE DI T2 in cui gli attributi NON soddisfano la condizione, il valore degli attributi di T1 in queste tuple è viene posto a NULL
Esempio
IL FULL JOIN è l’unione delle tuple ottenute da un LEFT JOIN con le tuple ottenute dal RIGHT JOIN
Un esempio che spieghi gli OUTER JOIN:
Si considerino le seguenti tabelle che rappresentano i dipartimenti in cui è suddivisa un’azienda e i dipendenti dell’azienda con il codice di dipartimento in cui ogni dipendente lavora
Dipartimenti
codiceDipartimento nome
d1 primo
d2 secondo
d3 terzo
d4 quarto
Dipendenti
nome_dipendente codiceDipartimento
Luigi d1
Pierone d1
18
Sandrone NULL
Paolo d3
Sandrone non è inquadrato in alcun dipartimento INNER JOIN (in questo caso è un natural join):
SELECT *
FROM dipendenti,dipartimenti
WHERE dipartimenti..codiceDipartimento=dipendenti.codiceDipartimento
nome_dipendente codiceDipartimento nome codice_dipaertimento
Luigi d1 primo d1
Pierone d1 primo d1
Paolo d3 terzo d3
Sandone NON c’è
LEFT JOIN : SELECT *
FROM dipendenti LEFT JOIN dipartimenti on
(dipartimenti..codiceDipartimento=dipendenti.codiceDipartimento)
nome_dipendente codiceDipartimento nome codice_dipaertimento
Luigi d1 primo d1
Pierone d1 primo d1
Sandrone NULL NULL NULL
Paolo d3 terzo d3
C’è anche Sandrone
RIGHT JOIN : SELECT *
FROM dipendenti RIGHT JOIN dipartimenti on
(dipartimenti..codiceDipartimento=dipendenti.codiceDipartimento)
nome_dipendente codiceDipartimento nome codice_dipaertimento
Luigi d1 primo d1
19
Pierone d1 primo d1
NULL NULL secondo d2
Paolo d3 terzo d3
NULL NULL quarto d4
Ci sono anche i dipartimenti d2 e d4 anche se non ci lavora nessuno
FULL JOIN SELECT *
FROM dipendenti LEFT JOIN dipartimenti on
(dipartimenti..codiceDipartimento=dipendenti.codiceDipartimento) UNION
SELECT *
FROM dipendenti RIGHT JOIN dipartimenti on
(dipartimenti..codiceDipartimento=dipendenti.codiceDipartimento)
nome_dipendente codiceDipartimento nome codice_dipaertimento
Luigi d1 primo d1
Pierone d1 primo d1
Sandrone NULL NULL NULL
Paolo d3 terzo d3
NULL NULL secondo d2
NULL NULL quarto d4