Databasteknik: Teoriövning 9 om SQL i program och program i SQL

Välj själv om du vill göra övningen ensam eller i någon typ av grupp. Det gör inget om en del av frågorna skulle vara svåra att svara på, för de är kanske avsedda att inleda en diskussion snarare än att ha ett enda rätt svar. Det gör inte heller något om man inte hinner med alla frågorna på övningstillfället.

Förberedelser

Innan denna övning bör du ha tittat på videoföreläsning nummer 10, på YouTube, som handlar om SQL i program och program i SQL. Den är uppdelad i två Youtube-videor: del 1, del 2. Man kan också läsa i kursboken eller motsvarande.

Scenario till uppgifterna

Vi använder samma exempeldatabas med onda robotar som i teoriövning 7:

Robotar
Nummer	Färg	Vikt
1	Blå	40
2	Blå	40
3	Svart	1000
4	Stålgrå	800

Deltagare
Robot	Strid	Resultat
1	1	Oavgjort
2	1	Oavgjort
2	2	Vinst
3	2	Förlust
1	3	Oavgjort
2	3	Oavgjort
3	3	Oavgjort

Strider
Nummer	Datum
1	2022-11-23
2	2022-11-23
3	2022-11-24

Uppgift 1

Vi behöver göra en lång och komplicerad sammanställning av statistik i databasen. Hur är det bäst att göra den? Som:

En SQL-fråga?
Programkod i en lagrad procedur?
Programkod i ett klientprogram, som hämtar data ur databasen?

Diskutera!

Uppgift 2

När vi kört SQL-frågor i ett SQL-gränssnitt som Batch SQL eller DbVisualizer behövde vi aldrig använda några loopar, som med for och while som vi känner igen från vanliga programmeringsspråk som C och Python. Men när vi arbetar med en databas med hjälp av SQL-frågor inbäddade i ett programmeringsspråk som Python måste vi ganska ofta använda loopar. Förklara varför!

Uppgift 3

Varje gång en robot förlorar en strid tappar den tio procent av sin vikt, eftersom gripklor och andra delar ramlar av. Skriv en trigger som automatiskt uppdaterar vikten vid en förlust! Du behöver inte skriva riktiga SQL-kommandon, utan du kan använda pseudokod eller vanliga ord för att beskriva den trigger som behövs, och vad den ska göra.

När en robot har förlorat en strid lägger man in (med insert) en rad i tabellen Deltagare, med värdet Förlust i kolumnen Resultat.

Uppgift 4

En uppgift på teoriövning 8 handlade om ifall man kunde skapa index för att få den här SQL-frågan att gå snabbare:

SELECT SUM(Vikt) FROM Robotar;

Svaret var att det inte går, för man behöver alla vikterna för att räkna ut summan, så databashanteraren måste läsa igenom hela tabellen från början till slut, och har ingen nytta av att snabbt hitta individuella rader.

Vi kan skapa en vy av samma fråga:

CREATE VIEW Robotviktsumma AS
SELECT SUM(Vikt) AS Totalvikt FROM Robotar;

Därefter använder man vyn som en vanlig tabell:

SELECT * FROM Robotviktsumma;

a) Går det fortare att gå via vyn än att ställa SELECT-frågan direct?

b) Går det fortare med en materialiserad vy?

c) En del databashanterare (bland annat PostgreSQL) har inbyggda materialiserade vyer, så man kan skriva något i stil med:

CREATE MATERIALIZED VIEW Robotviktsumma AS
SELECT SUM(Vikt) AS Totalvikt FROM Robotar;

I de databashanterare som inte har materialiserade vyer (bland annat Mimer) kan man ändå skapa en materialiserad vy med hjälp av en separat tabell, och triggers som håller den uppdaterad.

Visa hur man kan göra! Du behöver inte skriva riktiga SQL-kommandon, utan du kan använda pseudokod eller vanliga ord för att beskriva vilka triggers som behövs, och vad de ska göra.

d) Vad händer när en robot förlorar en strid, och triggern i förra uppgiften körs så roboten tappar en del av sin vikt? Uppdateras den här materialiserade vyn då?

Uppgift 5

Vi inför en regel att en lättare robot aldrig kan vinna över en tyngre robot. Vi kan alltså inte lägga in en Vinst-rad i tabellen Deltagare för en robot i en strid mot en tyngre robot.

Vi kan inte ha ett statiskt integritetsvillkor, med check eller på annat sätt, som säger att en robot inte kan ha en sådan Vinst-rad. Robotarnas vikt ändras ju med tiden, så om den tyngre roboten senare förlorar strider kan dess vikt sjunka, och plötsligt är det den som är den lättare roboten i striden.

Kan vi lösa det med en trigger?

Uppgift 6

Vi skapar en ny regel:

@
create trigger dumregel after update on Robotviktsumma
    referencing new table as n
for each statement
begin atomic
    update robotar set vikt = vikt + 1 where nummer = 1;
end
@

Vad kommer att hända?

Förslag på lösningar

Det finns lösningsförslag till en del av uppgifterna, men försök lösa dem själv först.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 30 april 2024