Databasteknik: Lösningar till övning 5, deluppgift 6

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!

Det här är några förslag på lösningar. Det finns förstås andra lösningar som också är korrekta,

Om vi råkat blanda ihop koordinaterna för tätorterna, kanske flera tätorter fått samma koordinater. Finns det två olika tätorter med samma koordinater?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.latitud = t2.latitud
AND t1.longitud = t2.longitud
AND t1.kod <> t2.kod;

Övning: Den frågan tar lång tid att köra (på en viss databashanterare som vi provade med). Vad kan det bero på?

Övning: På en annan databashanterare som vi provade med, gick frågan snabbt att köra. Vad kan det bero på?

Tätorternas namn är inte unika, så ett troligt scenario där det kan ha blivit fel är om vi tagit fel på vilken tätort som är vilken när vi la in koordinaterna. Finns det två olika tätorter med samma namn och samma koordinater?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.latitud = t2.latitud
AND t1.longitud = t2.longitud
AND t1.namn = t2.namn
AND t1.kod <> t2.kod;

Övning: Den frågan går mycket fortare att köra än den föregående, trots att vi lagt på ett villkor till i WHERE-delen. Vad kan det bero på?

Om en tätort fått fel koordinater kanske den enligt databasen har hamnat långt från de andra orterna i kommunen. Vilka två tätorter som ligger i samma kommun har (enligt databasen) längst avstånd mellan sig?

SELECT *
FROM "Tätorter" AS t1, "Tätorter" AS t2
WHERE t1.kommun = t2.kommun
AND t1.kod <> t2.kod
AND t1.latitud IS NOT NULL
AND t2.latitud IS NOT NULL
AND t1.longitud IS NOT NULL
AND t2.longitud IS NOT NULL
ORDER BY (t1.latitud-t2.latitud)*(t1.latitud-t2.latitud) + (t1.longitud-t2.longitud)*(t1.longitud-t2.longitud) DESC;

Formeln för avstånd ovan är fel, men ger en uppskattning av de relativa avstånden som stämmer åtminstone inom en faktor 10 eller så. Dessutom ger SQL-frågan inte riktigt det sökta svaret. Så här kan vi få fram rätt svar:

CREATE VIEW "Avstånd Inom Kommunen" AS
SELECT t1.kod AS "tätortskod1", t1.namn AS "tätortsnamn1", t2.kod AS "tätortskod2", t2.namn AS "tätortsnamn2", Kommuner.kod, Kommuner.namn AS kommunnamn,
       (t1.latitud-t2.latitud)*(t1.latitud-t2.latitud) + (t1.longitud-t2.longitud)*(t1.longitud-t2.longitud) AS "avstånd"
FROM "Tätorter" AS t1, "Tätorter" AS t2, Kommuner
WHERE t1.kommun = t2.kommun
AND t2.kommun = Kommuner.kod
AND t1.kod < t2.kod
AND t1.latitud IS NOT NULL
AND t2.latitud IS NOT NULL
AND t1.longitud IS NOT NULL
AND t2.longitud IS NOT NULL;

SELECT * FROM "Avstånd Inom Kommunen"
WHERE "avstånd" = (SELECT MAX("avstånd") FROM "Avstånd Inom Kommunen");

Finns det någon kommun utan tätorter?

SELECT *
FROM Kommuner
WHERE NOT EXISTS (SELECT * FROM "Tätorter" WHERE kommun = Kommuner.kod);

Ja, det finns det, men det beror inte på fel i databasen, utan på hur tätorter definieras! Exempelvis innehåller Solna kommun centralorten Solna, men den har växt ihop med Stockholm och räknas inte som en egen tätort, utan som en del av tätorten Stockholm.

Finns det något län utan kommuner?

SELECT *
FROM "Län"
WHERE NOT EXISTS (SELECT * FROM Kommuner WHERE "län" = "Län".kod);
Fler exempel:


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 17 oktober 2013