Slide 20/05

Laboratorio di Basi di Dati
Tutorato: il database Mondial
Turni 2 e 3
Luca Piovesan
Luca Canensi
Programma e Consigli
• Presentazione della base di dati (Mondial) utilizzata per l’esame di
laboratorio
• Esercizi
• Come passare l’esame di laboratorio:
– Capire bene ciò che si fa
– Saperlo motivare
– La sola query (anche se corretta) non vi garantisce la riuscita
dell’esame, se non sapete spiegare cosa avete fatto
• Portare, per ogni query:
– Testo della query
– Lista di elementi considerati e motivazioni (quali tabelle, quali vincoli,
perché avete considerato questi)
– (eventuali) Query alternative
– (eventuali) Query di controllo
L’utente Mondial
• ATTENZIONE: il database
Mondial è associato a un
utente diverso da quello
della connessione. Quindi
in SQL Developer le tabelle
di Mondial non sono
riportate nella solita
locazione dell’interfaccia
utente, ma occorre
individuare, nella
connessione Test, la voce
Altri utenti e l’utente
Mondial
Connessione a Test
• Connessione a Test:
Nome utente: test
Password:
test
Nome host: laboracle.educ.di.unito.it
Porta:
1521
SID:
ORALAB
Riferimento a tabelle di Mondial
• L’utente Test possiede delle tabelle con nomi uguali a quelli di
Mondial, ma non sono quelle che dovrete usare per l’esame
• Per far riferimento alle tabelle di Mondial, dovete anteporre
«mondial.» al nome delle tabelle.
Ad es. select * from mondial.country where
mondial.country.code='I';
• Dovreste avere pieno accesso in lettura alle tabelle, ma
nessuna possibilità di modifica
Mondial – cosa rappresenta
• Mondial è un database geografico e geopolitico. Rappresenta
(molto teoricamente) lo stato del mondo nell’anno 2009.
Principali elementi:
• COUNTRY
• CONTINENT
• CITY
• ORGANIZATION
• LAKE, RIVER, SEA, ISLAND, MOUNTAIN e DESERT
La Tabella COUNTRY
• Rappresenta informazioni sugli stati (e simili).
• Chiave primaria: CODE
• Vincoli:
– Area >=0
– Population >=0
– Name NOT NULL
– Name UNIQUE
• Referenziata da (attributo COUNTRY che referenzia COUNTRY.CODE):
ECONOMY
POPULATION
POLITICS
LANGUAGE
RELIGION
ETHNICGROUP
BORDERS
Attenzione: BORDERS non è simmetrica!
Perché è importante conoscere le
dipendenze?
• Le tabelle ECONOMY, POPULATION, POLITICS , LANGUAGE, RELIGION,
ETHNICGROUP, BORDERS contengono delle informazioni relative ai singoli
paesi, e devono essere messe in Join con COUNTRY per avere
l’informazione desiderata.
• Esempio: che lingua si parla in Pakistan?
select country.name, language.name
from mondial.country join mondial.language
on country.code = language.COUNTRY
where country.name = 'Pakistan';
• Trovate una lista delle dipendenze completa (con una rappresentazione un
po’ strana) sul sito di mondial
La Tabella CONTINENT
• Rappresenta i continenti.
• Chiave primaria: NAME
• Come si collega una country al suo continente?
– La tabella ENCOMPASSES referenzia sia CONTINENT che COUNTRY
– ENCOMPASSES contiene informazioni sulla percentuale (percentage) di
superficie di una country in un continente
ATTENZIONE: COUNTRY non è unico in ENCOMPASSES.
Le Tabelle CITY e PROVINCE
CITY:
• Chiave primaria: <NAME, COUNTRY, PROVINCE>
• Vincoli: (vedeteli voi)
• Referenzia PROVINCE via <COUNTRY, PROVINCE> (la provincia in cui si
trova la città)
PROVINCE:
• Chiave primaria: <NAME, COUNTRY>
• Vincoli: (vedeteli voi)
• Referenzia CITY via <CAPITAL, COUNTRY, CAPPROV> (per la capitale della
provincia)
Entrambe referenziano COUNTRY.
La tabella ORGANIZATION
• Rappresenta un’organizzazione con una sede (headquarter) situata in una
specifica città.
• Chiave primaria: ABBREVIATION
• Referenzia la CITY dove ha sede via <CITY, COUNTRY, PROVINCE>
• Gli stati possono essere membri di un’organizzazione. La tabella
ISMEMBER modella questo dato, referenziando lo stato (via COUNTRY) e
l’organizzazione (via ORGANIZATION)
Elementi geografici
• Le tabelle LAKE, RIVER, SEA, ISLAND, MOUNTAIN e DESERT rappresentano
elementi geografici.
• Per ognuno, l’attributo NAME è la chiave primaria
• Dove sono locate?
– Le tabelle GEO_??? con ??? = {LAKE, RIVER, SEA, ISLAND, MOUNTAIN e
DESERT} referenziano l’elemento geografico (tramite NAME) e la
provincia (tramite <COUNTRY, PROVINCE>)
• ESTUARY e SOURCE (modellati come attributi di RIVER) hanno una tabella
GEO_???
• Un’isola può essere locata in un LAKE, RIVER o SEA. Modellato tramite
ISLANDIN
• Due mari possono essere uniti. Modellato con la tabella MERGESWITH che
fa riferimento ai due SEA. ATTENZIONE: non è simmetrica!
Dov’è locata una città
• Una CITY può essere sulle sponde di un fiume, di un lago, o di un mare.
Questo aspetto è modellato con la tabella LOCATED. La tabella fa
riferimento ad una CITY e al fiume|mare|lago sul quale si affaccia.
– ATTENZIONE: come si fa riferimento ad una CITY?
– ATTENZIONE2: può esserci più di una tupla per ogni CITY
• Una CITY può essere su un’isola. Questo aspetto è modellato con la tabella
LOCATEDON, che fa riferimento alla CITY e alla ISLAND.
• Anche una MOUNTAIN può essere su un’isola. Questo aspetto è modellato
con la tabella MOUNTAINON che fa riferimento alla MOUNTAIN e alla
ISLAND.
Query di Esercitazione
Provate a fare queste query con la metodologia con la quale affrontereste
quelle dell’esame (analizzandole e motivandole).
1.
2.
3.
4.
5.
Elencare per ogni religione, il numero di paesi in cui si pratica (il risultato
deve contenere il nome della religione e il numero di paesi, ordinato
alfabeticamente per nome della religione)
Elencare le città italiane (nome) che si trovano su un’isola
Elencare tutti i vulcani con altezza superiore a 4500 metri
Elencare le città olandesi il cui nome inizia con ‘A’
Elencare le città che si trovano al mare, su un’isola (marittima), o in
montagna
Query di Esercitazione - II
6.
7.
Elencare i nomi dei mari che confinano con l’oceano Atlantico
Elencare i nomi dei mari che confinano con l’oceano Atlantico con
profondità superiore ai 500 metri, e la loro profondità
8. Calcolare il numero di abitanti di città costiere (sul mare). (Usare SUM)
9. Calcolare il numero di abitanti di città isolane, raggruppati per
continente
10. Elencare le città che hanno una popolazione maggiore del più piccolo dei
paesi
11. Elencare le città (con la loro popolazione) che hanno una popolazione
maggiore rispetto ad 1/3 della media delle popolazioni di tutti i paesi