Databasteknik: Övning 5 - Sverige

Det här är en övning. Man behöver inte redovisa något, eller lämna in någon rapport, men om du har problem eller frågor får du gärna fråga övningsassistenten.

Det finns lösningsförslag. Se länkarna nedan.

SQL-kommandona är provkörda i Mimer 10.0.6J och (inte alla lösningsförslagen än) i SQL Server 2012.

Mål

Förberedelser

Man bör läsa om SQL, till exempel i boken Databasteknik:

Scenario

Vi ska jobba med riktiga data om Sveriges län, kommuner och tätorter. Våra data finns i de tre tabellerna Län, Kommuner och Tätorter:

Län
Kod Bokstav Namn Folkmängd Residensstad
01 AB Stockholms län 2127006 336
03 C Uppsala län 341977 656
18 T Örebro län 283113 6188
.... .... .... .... ....

Kommuner
Kod Namn Folkmängd Län
180 Stockholms kommun 881235 1
1880 Örebro kommun 138952 18
1881 Kumla kommun 20738 18
.... .... .... ....

Tätorter
Kod Namn Kommun Folkmängd Latitud Longitud Elevation
336 Stockholm 180 1372565 59.332580 18.064900 20
656 Uppsala 380 140454 59.858500 17.645430 10
6188 Örebro 1880 107038 59.274120 15.206600 30
5900 Askersby 1880 243 null null null
5924 Ekeby-Almby 1880 1271 59.260000 15.330000 25
6076 Odensbacken 1880 1374 59.166670 15.533330 18
6116 Stora Mellösa 1880 776 59.216670 15.500000 32
6020 Kumla 1881 14062 59.127700 15.143410 40
6164 Åbytorp 1881 755 59.116670 15.066670 60
.... .... .... .... .... .... ....

Utförligare beskrivning

Sverige är uppdelat i 21 län, till exempel Örebro län och Jämtlands län. Varje län har ett unikt namn, men också en unik länskod och en unik länsbokstav (som i en del fall utgörs av två bokstäver!). Varje län har också en folkmängd. Dessutom har varje län en residensstad, där landshövdingen bor. Det är en tätort som ligger i det länet. (Läs mer om Sveriges län i Wikipedia.)

Varje län delas sedan in i flera kommuner, till exempel Örebro kommun och Kumla kommun, som båda finns i Örebro län. Totalt finns det 290 kommuner. Varje kommun har ett unikt namn och en unik kommunkod, och en folkmängd. (Läs mer om Sveriges kommuner i Wikipedia. De har hela listan.)

Varje kommun innehåller en eller flera tätorter. Som exempel finns i Örebro kommun 14 olika tätorter: Örebro, Hovsta, Garphyttan, Odensbacken, Vintrosa, Ekeby-Almby, Stora Mellösa, Glanshammar, Norra Bro, Latorpsbruk, Ölmbrotorp, Hampetorp, Kilsmo och Askersby. Totalt finns det 1956 tätorter. Varje tätort har ett namn och en folkmängd. Det kan inte finnas två tätorter med samma namn i en och samma kommun, men i olika kommuner kan det finnas tätorter som har samma namn. För att tätorterna ska få en enkel nyckel har de en unik tätortskod. De flesta av tätorterna (men inte riktigt alla) har kartkoordinater i form av latitud och longitud, och en höjd över havet ("elevation"). (Läs mer om Sveriges tätorter i Wikipedia. De har hela listan.)

Kod är primärnyckel i samtliga tabeller.

I tabellen Län är Bokstav och Namn två olika alternativnycklar.
I tabellen Kommuner är Namn en alternativnyckel.
I tabellen Tätorter bildar Namn och Kommun en sammansatt alternativnyckel.

Län.Residensstad refererar till Tätorter.Kod
Kommuner.Län refererar till Län.Kod
Tätorter.Kommun refererar till Kommun.Kod

Arbetsgång

Ladda ner och spara filen skapa-sverige-databasen.txt. Den innehåller SQL-kommandon för att skapa tabellerna och fylla dem med data. Om man arbetar med databashanteraren Mimer kan filen läsas med Batch SQL:s kommando read, till exempel så här:
read all input from 'C:\Documents and Settings\tpy\Skrivbord\skapa-sverige-databasen.txt';
Om man arbetar med SQL Server 2012 kan man starta SQL Server Management Studio och skapa en ny databas (till exempel kallad Sverige) med New Database. Därefter kan man skapa en ny fråga med New Query, klistra in hela filens innehåll, och sedan köra frågan.

Det finns också en annan fil där namn på tabeller och kolumner har rensats från svenska tecken: skapa-sverige-databasen-utan-aao.txt. Där heter tabellerna Lan, Kommuner och Tatorter. Välj själv om du vill använda den i stället. Vilket är enklast?

Deluppgift 1: Rita diagram

Rita ett ER- eller EER-diagram för den beskrivna databasen!

Det vanliga när man jobbar med ER-diagram är att man utgår från den del av världen som man ska beskriva, ritar ett konceptuellt schema i form av ett diagram, och sen översätter diagrammet till tabeller. Här ska vi alltså gå åt andra hållet.

[Lösningsförslag]

Deluppgift 2: Enkel SQL med bara en tabell

Formulera följande frågor i SQL. I de här frågorna räcker det med att titta i en enda tabell för att hitta det sökta svaret.

2a) Vad är länsbokstaven för Skåne län?

2b) Hur många människor bor det i Åre kommun?

2c) Vad är länsbokstaven och folkmängden för Örebro län?

2d) Vilka kommuner har mindre än 3000 invånare?

2e) Vilka kommuner har namn som börjar på bokstaven Å?

2f) Vilka tätorter har namn som slutar på stad?

2g) Vilka tätorter med namn som slutar på stad har mindre än 1000 invånare? (Ledtråd: En annan formulering av samma fråga: Vilka tätorter har namn som slutar på stad och samtidigt mindre än 1000 invånare?)

2h) Vilka tätorter har antingen ett namn som slutar på stad, eller mindre än 1000 invånare (eller både och)? Sortera dem i bokstavsordning.

2i) Vilka tätorter har mellan 8000 och 9000 invånare (inklusive gränserna)?

2j) Vilka tätorter saknar vi koordinater (latitud och longitud) för? Ledtråd: is null

2k) Vilka tätorter har vi koordinaterna för? Ledtråd: is not null

2l) Vilka tätorter har vi latitud och longitud för, men inte höjden över havet?

[Lösningsförslag]

Deluppgift 3: Enkel SQL med flera tabeller

I här frågorna behöver man använda data från flera tabeller, men skriv varje sökning som en enda SQL-fråga (eventuellt med underfrågor i where-villkoret).

3a) Vad heter tätorterna som finns i Pajala kommun?

3b) Det finns en tätort som heter Flurkmark. Vad heter kommunen som den ligger i?

3c) Vad heter länet som Flurkmark ligger i?

3d) Vilka tätorter ligger i kommuner som har mindre än 3000 invånare?

3e) Vilka län innehåller minst en tätort som har mer än 100000 invånare?

3f) Vilka tätorter med mer än 1000 invånare ligger i kommuner som har mindre än 3000 invånare?

3g) Vilka tätorter i Västerbottens län har mer än 1000 invånare och ligger i kommuner som har mindre än 4000 invånare?

3h) Vad heter residensstaden i Kalmar län, och hur många personer bor det i den?

3i) I vilket län är Karlstad residensstad?

3j) Det finns en tätort som heter Löwenströmska lasarettet. Vad heter kommunen och länet som den ligger i?

3k) Finns det några kommuner som inte innehåller några tätorter? Vad heter de? (Kan det verkligen finnas kommuner utan tätorter? Vad skulle det kunna bero på?)

[Lösningsförslag]

Deluppgift 4: Lite mer avancerad SQL

(Det ska bli fler uppgifter här senare.)

4a) Namn på tätorter är som nämnts bara unika inom en kommun, så två eller flera tätorter i Sverige kan ha samma namn. Vilka namn gäller det? Ledtråd: Skapa två alias för tabellen Tätorter med hjälp av nyckelordet as.

[Lösningsförslag]

Deluppgift 5: SQL, med aggregatfunktioner

Formulera följande frågor i SQL. Definiera gärna vyer om det underlättar.

5a) Hur många kommuner finns det?

5b) Vad är den sammanlagda folkmängden av alla län?

5c) Hur många tätorter har mer än 100000 invånare?

5d) Vad är den genomsnittliga folkmängden för alla tätorter?

5e) Hur många kommuner finns det i Örebro län?

5f) Vad är den genomsnittliga folkmängden för tätorterna i Örebro kommun?

5g) Hur många tätorter ligger i kommuner som har mindre än 3000 invånare?

5h) Vad heter Sveriges minsta kommun (i folkmängd räknat)?

5i) Vad heter den tätort som ligger på högst höjd över havet (elevation)?

5j) Vad heter den sydligaste tätorten?

5k) Vad heter den östligaste tätorten?

5l) Hur många tätorter ligger öster om Stockholm?

5m) Vad heter den största tätorten i Stockholms län?

Nu börjar frågorna bli lite krångliga...

5n) Vad heter det nordligaste länet? (Använd residensstadens koordinater!)

5o) Vilka kommuner innehåller bara en enda tätort? Vad heter kommunerna, och tätorterna? (Tips: Man kan börja med att skapa en vy som anger antalet tätorter i de olika kommunerna.)

5p) Vilken kommun innehåller flest tätorter?

5q) I vilken kommun bor det flest personer i tätorterna?

5r) I vilket län bor det flest personer i tätorterna?

5s) Finns det någon kommun där befolkningen i kommunen är större än summan av befolkningen i kommunens tätorter? (Vad skulle det kunna bero på om det gör det?)

5t) Finns det någon kommun där befolkningen i kommunen är mindre än summan av befolkningen i kommunens tätorter? (Vad skulle det kunna bero på om det gör det?)

5u) Finns det något län där befolkningen i länet inte är lika med summan av befolkningen i länets kommuner? (Vad skulle det kunna bero på om det gör det?)

5v) I vilken kommun bor det flest personer utanför tätorterna? (Vilken kommun är det? Vad skulle det kunna bero på att det blev just den kommunen?)

5w) I vilket län bor det flest personer utanför tätorterna?

[Lösningsförslag]

Deluppgift 6 (överkurs): Ännu roligare SQL (Är det verkligen möjligt?!)

Det är inte omöjligt att det finns fel i databasen, till exempel felaktiga koordinater. Försök formulera några SQL-frågor som kan hjälpa oss att hitta felen!

[Lösningsförslag]


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 19 november 2013