Laboratorio di Basi di DatiTutorato: 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):

Attenzione: BORDERS non è simmetrica!

ECONOMYPOPULATIONPOLITICS LANGUAGE

RELIGIONETHNICGROUPBORDERS

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. 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)

2. Elencare le città italiane (nome) che si trovano su un’isola

3. Elencare tutti i vulcani con altezza superiore a 4500 metri

4. Elencare le città olandesi il cui nome inizia con ‘A’

5. Elencare le città che si trovano al mare, su un’isola (marittima), o in montagna

Query di Esercitazione - II

6. Elencare i nomi dei mari che confinano con l’oceano Atlantico

7. 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