Databasteknik: Lösningar till tentamen 2021-01-13

Observera att detta är förslag på lösningar. Det kan finnas andra lösningar som också är korrekta. Det kan hända att en del av lösningarna är mer omfattande än vad som krävs för full poäng på uppgiften, eller att de bara hänvisar till var man kan läsa svaren. Dessutom har det inträffat i världshistorien att lärare skrivit fel i lösningsförslagen. Jag har förstås aldrig gjort det, men andra. Är det verkligen någon som läser såna här inledande texter? Jag vet inte. Det kan vara så. Rabarber rabarber rabarber. Det har jag hört att statisterna får säga på filminspelningar när det ska vara bakgrundssorl från en restaurang eller liknande. Här nedan kommer lösningsförslagen till uppgifterna.

Uppgift 1 (5 p)

Rita ett ER- eller EER-diagram för den beskrivna databasen. Använd informationen i scenariot ovan, men tänk också på att det ska gå att svara på frågorna i uppgift 3 nedan.

ER- och EER-diagram kan ritas på flera olika sätt. Om du använder en annan notation än kursboken, måste du förklara den notation som du använder.

ER-diagram

Uppgift 2 (8 p)

Implementera den beskrivna databasen i relationsmodellen, dvs översätt ER-diagrammet till tabeller. Implementationen ska vara bra.

a) Visa en tydlig översikt över dina tabeller. Ange vilka relationer som finns och vilka attribut varje relation innehåller, med väl valda exempeldata. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Personer(Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
Platser(Nummer, Namn, Gatuadress, Ort)
Vaccinationer(ID, Datum, Person, Plats)
Gruppvaccinationer(ID, Datum, Plats)
Gruppvaccinerade(Gruppvaccination, Person)

Kandidatnycklar är understrukna. I de tabeller där det finns flera kandidatnycklar är den första primärnyckel.

Främmande nycklar:

Vaccinationer.Person till Personer.Personnummer
Vaccinationer.Plats till Platser.Nummer
Gruppvaccinationer.Plats till Platser.Nummer
Gruppvaccinerade.Gruppvaccination tlll Gruppvaccinationer.ID
Gruppvaccinerade.Person till Personer.Personnummer

Tabellerna med exempeldata:

Personer
Personnummer Namn Gatuadress Ort Ålder Prioritet Vikt
450222-1122 Anna Alm Vägen 5 Örebro 75 2 100
551112-9917 Bengt Berg Vägen 6 Örebro 65 0 100
650402-7266 Cecilia Ceder Vägen 7 Örebro 55 null 70
720515-4599 Don Duck Vägen 8 Örebro 48 0 100
871031-1914 Erik Ek Vägen 9 Örebro 33 2 200

Platser
Nummer Namn Gatuadress Ort
1 Örebro COVID-camp USÖ Örebro
2 Kumla COVID-camp Vägen 1 Kumla

Vaccinationer
ID Datum Person Plats
1 2021-01-13 450222-1122 1
2 2021-01-13 551112-9917 1
3 2021-01-13 720515-4599 2

Gruppvaccinationer
ID Datum Plats
1 2021-01-13 1
2 2021-01-13 1

Gruppvaccinerade
Gruppvaccination Person
1 450222-1122
1 551112-9917
2 551112-9917
2 720515-4599

Några kommentarer:

b) I delfrågan ovan står det att man ska ange vilka relationer som finns. Vad är en relation?

En relation i en relationsdatabas är samma sak som en tabell. Det är inte kopplingarna mellan tabellerna som är relationerna.

c) Skriv de SQL-kommandon som behövs för att skapa tabellerna, med lämpliga integritetsvillkor. 

CREATE TABLE Personer
(Personnummer CHAR(11) NOT NULL PRIMARY KEY,
Namn VARCHAR(15),
Gatuadress VARCHAR(10),
Ort VARCHAR(10),
Ålder INTEGER,
Prioritet INTEGER,
Vikt INTEGER);

CREATE TABLE Platser
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn VARCHAR(20) NOT NULL UNIQUE,
Gatuadress VARCHAR(10),
Ort VARCHAR(10));

CREATE TABLE Vaccinationer
(ID INTEGER NOT NULL PRIMARY KEY,
Datum DATE,
Person CHAR(11) REFERENCES Personer(Personnummer),
Plats INTEGER REFERENCES Platser(Nummer));

CREATE TABLE Gruppvaccinationer
(ID INTEGER NOT NULL PRIMARY KEY,
Datum DATE,
Plats INTEGER REFERENCES Platser(Nummer));

CREATE TABLE Gruppvaccinerade
(Gruppvaccination INTEGER NOT NULL REFERENCES Gruppvaccinationer(ID),
Person CHAR(11) REFERENCES Personer(Personnummer),
PRIMARY KEY (Gruppvaccination, Person));

För att underlätta provkörningar visas också INSERT-kommandon med exempeldata: 

INSERT INTO Personer (Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
    VALUES ('450222-1122', 'Anna Alm', 'Vägen 5', 'Örebro', 75, 100, 0);
INSERT INTO Personer (Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
    VALUES ('551112-9917', 'Bengt Berg', 'Vägen 6', 'Örebro', 65, 100, 0);
INSERT INTO Personer (Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
    VALUES ('720515-4599', 'Don Duck', 'Vägen 8', 'Örebro', 48, 100, 0);
INSERT INTO Personer (Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
    VALUES ('871031-1914', 'Erik Ek', 'Vägen 9', 'Örebro', 33, 200, 0);

INSERT INTO Platser (Nummer, Namn, Gatuadress, Ort)
    VALUES (1, 'Örebro COVID-camp', 'USÖ', 'Örebro');
INSERT INTO Platser (Nummer, Namn, Gatuadress, Ort)
    VALUES (2, 'Kumla COVID-camp', 'Vägen 1', 'Kumla');

INSERT INTO Vaccinationer (ID, Datum, Person, Plats)
    VALUES (1, DATE '2021-01-13', '450222-1122', 1);
INSERT INTO Vaccinationer (ID, Datum, Person, Plats)
    VALUES (2, DATE '2021-01-13', '551112-9917', 1);
INSERT INTO Vaccinationer (ID, Datum, Person, Plats)
    VALUES (3, DATE '2021-01-13', '720515-4599', 2);

INSERT INTO Gruppvaccinationer (ID, Datum, Plats)
    VALUES (1, DATE '2021-01-13', 1);
INSERT INTO Gruppvaccinationer (ID, Datum, Plats)
    VALUES (2, DATE '2021-01-13', 1);

INSERT INTO Gruppvaccinerade (Gruppvaccination, Person)
    VALUES (1, '450222-1122');
INSERT INTO Gruppvaccinerade (Gruppvaccination, Person)
    VALUES (1, '551112-9917');

INSERT INTO Gruppvaccinerade (Gruppvaccination, Person)
    VALUES (2, '551112-9917');
INSERT INTO Gruppvaccinerade (Gruppvaccination, Person)
    VALUES (2, '720515-4599');

Uppgift 3 (9 p)

Formulera följande frågor i SQL. Definiera gärna vyer eller CTE:er om det underlättar, men skapa inte nya tabeller.

a) (2p) Personen Anna Alm har vaccinerat sig, med en vanlig vaccination (inte en gruppvaccination). Var? Ange adressen till vaccinationsplatsen. 

-- Några olika alternativ:

SELECT Platser.Gatuadress, Platser.Ort
FROM Personer, Vaccinationer, Platser
WHERE Personer.Personnummer = Vaccinationer.Person
AND Vaccinationer.Plats = Platser.Nummer
AND Personer.Namn = 'Anna Alm';

SELECT Platser.Gatuadress, Platser.Ort
FROM Personer JOIN Vaccinationer ON Personer.Personnummer = Vaccinationer.Person
     JOIN Platser ON Vaccinationer.Plats = Platser.Nummer
WHERE Personer.Namn = 'Anna Alm';

SELECT Gatuadress, Ort
FROM Platser
WHERE Nummer IN (SELECT Plats
                 FROM Vaccinationer
                 WHERE Person IN (SELECT Personnummer
                                  FROM Personer
                                  WHERE Namn = 'Anna Alm'));

b) (2p) Personen Bengt Berg har vaccinerat sig med en gruppvaccination. Vad heter de andra personer som vaccinerades vid samma tillfälle? 

SELECT Andra.Namn
FROM Personer AS Bengt, Gruppvaccinerade AS Bengts, Gruppvaccinerade AS Andras, Personer AS Andra
WHERE Bengt.Personnummer = Bengts.Person
AND Bengts.Gruppvaccination = Andras.Gruppvaccination
AND Andras.Person = Andra.Personnummer
AND Bengt.Namn = 'Bengt Berg'
AND Andra.Personnummer != Bengt.Personnummer;

-- Den här frågan ger även Bengt Bergs namn:

SELECT Namn
FROM Personer
WHERE Personnummer IN (SELECT Person
                       FROM Gruppvaccinerade
                       WHERE Gruppvaccination IN (SELECT Gruppvaccination
                                                  FROM Gruppvaccinerade
                                                  WHERE Person IN (SELECT Personnummer
                                                                   FROM Personer
                                                                   WHERE Namn = 'Bengt Berg')));

c) (2p) Hur många personer har vaccinerats med gruppvaccinationer? 

SELECT COUNT(*)
FROM Gruppvaccinerade;

-- Eller om man bara vill räkna unika personer,
-- dvs om samma person gruppvaccineras två gånger så räknas det bara som en person:

SELECT COUNT(DISTINCT Person)
FROM Gruppvaccinerade;

d) (3p) Vad heter den person som har högst prioritet, av de som ännu inte är vaccinerade (vare sig med en vanlig vaccination eller en gruppvaccination)? 

-- Med en CTE:

WITH Ovaccinerade AS
(SELECT Namn, Prioritet
FROM Personer
WHERE Personnummer NOT IN (SELECT Person FROM Vaccinationer)
AND Personnummer NOT IN (SELECT Person FROM Gruppvaccinerade))
SELECT Namn
FROM Ovaccinerade
WHERE Prioritet = (SELECT MAX(Prioritet) FROM Ovaccinerade);

-- Med en vy:

CREATE VIEW Ovaccinerade AS
SELECT Namn, Prioritet
FROM Personer
WHERE Personnummer NOT IN (SELECT Person FROM Vaccinationer)
AND Personnummer NOT IN (SELECT Person FROM Gruppvaccinerade);

SELECT Namn
FROM Ovaccinerade
WHERE Prioritet = (SELECT MAX(Prioritet) FROM Ovaccinerade);

-- Med Mimer-syntax:

SELECT Namn
FROM Personer
WHERE Personnummer NOT IN (SELECT Person FROM Vaccinationer)
AND Personnummer NOT IN (SELECT Person FROM Gruppvaccinerade)
ORDER BY Prioritet DESC
FETCH FIRST 1;

-- Även MySQLs syntax med "LIMIT" och SQL Servers syntax med "TOP" är godkända.

Uppgift 4 (4 p)

Formulera följande som kommandon i SQL:

a) Lägg in personen Cecilia Ceder, med personnummer 650402-7266, adressen Vägen 7, Örebro, ålder 55 år och vikt 70 kilo. 

INSERT INTO Personer (Personnummer, Namn, Gatuadress, Ort, Ålder, Vikt, Prioritet)
    VALUES ('650402-7266', 'Cecilia Ceder', 'Vägen 7', 'Örebro', 55, 70, NULL);

b) Riskgrupper ska vaccineras först. Låt alla personer som är 70 år eller äldre, och alla personer som väger mer än 130 kilo, få prioriteten 2. 

UPDATE Personer
SET Prioritet = 2
WHERE Ålder >= 70 OR Vikt > 130;

c) Det visar sig att vaccinet som levererades till vaccinationsplatsen Örebro COVID-camp var verkningslöst. Endast vanliga vaccinationer genomfördes där, inga gruppvaccinationer. Ta bort alla vaccinationer som gjordes på den vaccinationsplatsen. 

DELETE
FROM Vaccinationer
WHERE Plats IN (SELECT Nummer
                FROM Platser
                WHERE Namn = 'Örebro COVID-camp');

Uppgift 5 (3 p)

Det är många personer som ska vaccineras, och alla tabellerna i databasen blir snabbt mycket stora. Vi märker att fråga 3a, om adressen till platsen där Anna Alm vaccinerade sig, körs ofta, men vi kanske söker efter andra personer. Det går för långsamt. Vi märker att det inte finns några index i databasen, inte ens på nycklar.

a) Vilka index bör man skapa för att få den sökningen att gå snabbare? Skriv CREATE INDEX-kommandon. 

CREATE INDEX PersonensPersonnummer ON Personer(Personnummer);
CREATE INDEX VaccineradPerson ON Vaccinationer(Person);
CREATE INDEX Vaccinationsplats ON Vaccinationer(Plats);
CREATE INDEX Platsnummer ON Platser(Nummer);
CREATE INDEX Personnamn ON Personer(Namn);

b) Ange ett index som inte skulle gjort att sökningen går snabbare, och förklara varför det indexet inte hjälper. 

-- Några förslag:

CREATE INDEX DåligtIndex1 ON Gruppvaccinationer(ID);

-- Tabellen Gruppvaccinationer är inte med alls i frågan.

CREATE INDEX DåligtIndex2 ON Vaccinationer(Datum);

-- Kulumnen Datum i tabellen Vaccinationer är inte med alls i frågan.

CREATE INDEX DåligtIndex3 ON Platser(Gatuadress);

-- Kulumnen Gatuadress i tabellen Platser är med i svaret från
-- sökningen, men används inte för att hitta data.

CREATE INDEX DåligtIndex4 ON Vaccinationer(ID, Datum, Person, Plats);

-- Ett sammansatt index kan bara användas för sökning på ett prefix av
-- de indexerade kolumnerna, alltså ID, ID+Datum, ID+Datum+Person eller
-- alla fyra kolumnerna ID+Datum+Person+Plats. Eftersom kolumnen ID
-- inte är med alls i frågan, kan det här indexet inte användas vid
-- körningen av frågan.

CREATE INDEX DåligtIndex5 ON Personer(Prioritet);

-- Vi kan gissa från uppgifterna ovan att det blir ganska få olika värden
-- på prioriteterna, kanske bara värdena 0, 1 och 2. Ett index på prioritet
-- skulle i så fall ge mycket dålig selektivitet. En sökning på en viss
-- prioritet kommer att ge väldigt många rader som svar, och i så fall är
-- det förmodligen snabbare att bara läsa igenom tabellen sekventiellt.
-- Och dessutom används ju inte den här kolumnen i frågan.

Uppgift 6 (6 p)

Transaktionshanteringen i databaser är till för att upprätthålla de fyra "ACID-egenskaperna", atomicitet (A), konsistensbevarande (C), isolering (I) och hållbarhet (D).

Visa med tydliga exempel från databasen som du skapat i uppgifterna ovan vad som skulle kunna hända som gör att var och en av dessa egenskaper bryts, om man inte använder transaktioner.

Uppgift 7 (3 p)

Vad skulle de olika nivåerna i tre-schema-arkitekturen kunna innehålla i vår databas? Skriv inte bara vad nivåerna normalt innehåller, utan ge specifika exempel utgående från scenariot.

Uppgift 8 (3 p)

Vilken databashanterare bör man välja för vaccinationsdatabasen? Nämn tre förslag på databashanterare, och tala om varför, eller varför inte, de passar för den här tillämpningen!

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 9 februari 2021