Databasteknik: Lösningar till tentamen 2019-01-15

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

Ett EER-diagram

Kommentarer:

Ganska många har gjort Köp som en sambandstyp som knyter ihop Vara och Automat. Det är fel. En sambandstyp mellan två entitetstyper kan bara ha en instans mellan samma två entitetsinstanser. (Exempel: Även om medborgarsambandet mellan personer och stater är ett många-till-många-samband, kan jag inte vara medborgare i Norge två gånger.) En "vara" i den här databasen är uppenbarligen en typ av vara (exempelvis Coke Zero, 50 cl) och inte en enskild vara (just den här 50-centilitersflaskan med Coke Zero som står här på den här hyllan). Om Köp vore en sambandstyp, skulle man därför bara kunna sälja samma typ av vara ur en och samma automat en enda gång.

Av dem som gjort Köp som en sambandstyp mellan Vara och Automat har de flesta gjort ett många-till-många-samband, men en del har gjort det som ett ett-till-ett-samband. Gissningsvis har de tänkt på att det står i scenariot att ett köp hör ihop med en automat och en vara, men ett ett-till-ett-samband skulle innebära att man bara kan göra totalt ett köp ur varje automat, och att varje varutyp bara kan säljas en enda gång, i en enda automat. Så om jag köper en Coke Zero, 50 cl ur den här automaten, så får man dels stänga av den automaten, och dels går det aldrig mer att köpa Coke Zero, 50 cl, vare sig ur den här eller ur någon annan automat.

Uppgift 2 (6 p)

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

Du behöver inte skriva create table-kommandon i SQL, men du ska ange vilka relationer som finns och vilka attribut varje relation innehåller. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Implementationen ska vara bra.

Kunder(Nummer, Namn)
Automater(Nummer, Kund)
Varor(Nummer, Namn)
Innehåller(Automat, Vara)
Köp(Nummer, Vara, Automat, Datum, Tid)

Primärnycklarna är understrukna. I tabellen Varor är även Namn en kandidatnyckel. I tabellen Köp kan man tänka sig att även kombinationen av Vara, Automat, Datum och Tid är en kandidatnyckel, men det beror på hur snabbt ett köp kan gå, på upplösningen i tiden, och på om man kan ställa om klockorna i automaterna.

Främmande nycklar:

Automater.Kund till Kunder.Nummer
Innehåller.Automat till Automater.Nummer
Innehåller.Vara till Varor.Nummer
Köp.Vara till Varor.Nummer
Köp.Automat till Automater.Nummer

Nedan visas create table-kommandon och exempeldata, för att underlätta provkörningar.

DROP TABLE Köp;
DROP TABLE Innehåller;
DROP TABLE Varor;
DROP TABLE Automater;
DROP TABLE Kunder;

CREATE TABLE Kunder
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(50) NOT NULL);

CREATE TABLE Automater
(Nummer INTEGER NOT NULL PRIMARY KEY,
Kund INTEGER NOT NULL REFERENCES Kunder(Nummer));

CREATE TABLE Varor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE Innehåller
(Automat INTEGER NOT NULL REFERENCES Automater(Nummer),
Vara INTEGER NOT NULL REFERENCES Varor(Nummer),
PRIMARY KEY (Automat, Vara));

CREATE TABLE Köp
(Nummer INTEGER NOT NULL PRIMARY KEY,
Vara INTEGER NOT NULL REFERENCES Varor(Nummer),
Automat INTEGER NOT NULL REFERENCES Automater(Nummer),
Datum DATE NOT NULL,
Tid TIME NOT NULL);

INSERT INTO Kunder (Nummer, Namn) VALUES (1, 'Örebro universitet');
INSERT INTO Kunder (Nummer, Namn) VALUES (2, 'USÖ');
INSERT INTO Kunder (Nummer, Namn) VALUES (3, 'FRA');

INSERT INTO Automater (Nummer, Kund) VALUES (1, 1);
INSERT INTO Automater (Nummer, Kund) VALUES (2, 1);
INSERT INTO Automater (Nummer, Kund) VALUES (3, 2);
INSERT INTO Automater (Nummer, Kund) VALUES (4, 2);
INSERT INTO Automater (Nummer, Kund) VALUES (5, 2);

INSERT INTO Varor (Nummer, Namn) VALUES (1, 'Coke Zero, 50 cl');
INSERT INTO Varor (Nummer, Namn) VALUES (2, 'Coca-Cola, 50 cl');
INSERT INTO Varor (Nummer, Namn) VALUES (3, 'Snickers');
INSERT INTO Varor (Nummer, Namn) VALUES (4, 'Ahlgrens bilar');

INSERT INTO Innehåller (Automat, Vara) VALUES (1, 1);
INSERT INTO Innehåller (Automat, Vara) VALUES (1, 2);
INSERT INTO Innehåller (Automat, Vara) VALUES (1, 3);
INSERT INTO Innehåller (Automat, Vara) VALUES (1, 4);
INSERT INTO Innehåller (Automat, Vara) VALUES (2, 1);
INSERT INTO Innehåller (Automat, Vara) VALUES (3, 1);
INSERT INTO Innehåller (Automat, Vara) VALUES (4, 1);
INSERT INTO Innehåller (Automat, Vara) VALUES (4, 2);
INSERT INTO Innehåller (Automat, Vara) VALUES (5, 3);

INSERT INTO Köp (Nummer, Vara, Automat, Datum, Tid) VALUES (1, 1, 1, DATE '2019-01-15', TIME '09:10:00');
INSERT INTO Köp (Nummer, Vara, Automat, Datum, Tid) VALUES (2, 1, 1, DATE '2019-01-15', TIME '09:11:00');
INSERT INTO Köp (Nummer, Vara, Automat, Datum, Tid) VALUES (3, 1, 1, DATE '2019-01-15', TIME '09:11:00');
INSERT INTO Köp (Nummer, Vara, Automat, Datum, Tid) VALUES (4, 1, 2, DATE '2019-01-15', TIME '09:11:00');

SELECT * FROM Kunder;
SELECT * FROM Automater;
SELECT * FROM Varor;
SELECT * FROM Innehåller;
SELECT * FROM Köp;

Uppgift 3 (10 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) Vad är numren på de automater som är placerade hos kunden Örebro universitet?

Tre förslag:

SELECT Automater.Nummer
FROM Automater, Kunder
WHERE Automater.Kund = Kunder.Nummer
AND Kunder.Namn = 'Örebro universitet';

SELECT Automater.Nummer
FROM Automater JOIN Kunder ON Automater.Kund = Kunder.Nummer
WHERE Kunder.Namn = 'Örebro universitet';

SELECT Nummer
FROM Automater
WHERE Kund IN (SELECT Nummer
               FROM Kunder
               WHERE Namn = 'Örebro universitet');

b) (2p) Hur många olika varor finns i automaterna som står hos kunden USÖ? Vi vill veta antalet, inte numren.

SELECT COUNT(DISTINCT Varor.Nummer)
FROM Varor, Innehåller, Automater, Kunder
WHERE Varor.Nummer = Innehåller.Vara
AND Innehåller.Automat = Automater.Nummer
AND Automater.Kund = Kunder.Nummer
AND Kunder.Namn = 'USÖ';

Man kan utelämna tabellen Varor:

SELECT COUNT(DISTINCT Innehåller.Vara)
FROM Innehåller, Automater, Kunder
WHERE Innehåller.Automat = Automater.Nummer
AND Automater.Kund = Kunder.Nummer
AND Kunder.Namn = 'USÖ';

En annan lösning:

SELECT COUNT(*)
FROM Varor
WHERE Nummer IN (SELECT Vara
                 FROM Innehåller, Automater, Kunder
                 WHERE Innehåller.Automat = Automater.Nummer
                 AND Automater.Kund = Kunder.Nummer
                 AND Kunder.Namn = 'USÖ');

c) (2p) Hos vilka kunder står det inga automater? För varje sådan kund vill vi veta kundens nummer och namn.

SELECT Nummer, Namn
FROM Kunder
WHERE Nummer NOT IN (SELECT Kund FROM Automater);

d) (3p) Skapa en vy som heter AntalKöp och som för varje kund anger kundens nummer, kundens namn och antalet köp som gjorts ut automater som står hos den kunden. Även kunder där inga köp gjorts ska vara med i vyn, med antalet noll.

CREATE VIEW AntalKöp AS
SELECT Kunder.Nummer AS Kundnummer, Kunder.Namn AS Kundnamn, COUNT(Köp.Nummer) AS Antal
FROM Köp RIGHT JOIN Automater ON Köp.Automat = Automater.Nummer
RIGHT JOIN Kunder ON Automater.Kund = Kunder.Nummer
GROUP BY Kunder.Nummer, Kunder.Namn;

e) (1p) Använd vyn från uppgiften ovan för att söka reda på hos vilken kund det har gjorts flest köp. Vi vill veta namnet på den kunden.

SELECT Kundnamn
FROM AntalKöp
WHERE Antal IN (SELECT MAX(Antal) FROM AntalKöp);

Uppgift 4 (5 p)

Sodexo vill ha en ruta på sin hemsida med namnet på deras bästa kund, alltså den kund där det gjorts flest köp. Det är alltså svaret på SQL-frågan i deluppgift e i uppgiften ovan.

De gör en lösning där den SQL-frågan körs varje gång någon tittar på Sodexos hemsida. Frågan tar lång tid att köra, och allt blir väldigt långsamt.

Beskriv några bra sätt som Sodexo kan använda för att få bättre prestanda!

Svar:

Man kan skapa index för att frågan ska gå fortare att köra. Det kommer förmodligen inte att vara tillräckligt, men de index man borde skapa är på de kolumner som används för att söka fram rader i fråga 3d: Köp.Automat, Automater.Nummer, Automater.Kund, Kunder.Nummer. Eftersom Automater.Nummer och Kunder.Nummer är deklarerade som nycklar, kommer de flesta databashanterare redan att automatiskt ha skapat index på dessa.
Det kommer förmodligen inte heller att vara tillräckligt, men man kan förstås också välja en snabb databashanterare, och snabb hårdvara.
Oavsett vilka index som finns, och vilken hårdvara man har, är det mycket data som behöver gås igenom för att hitta det sökta svaret. Därför kan det vara lämpligt att skapa en materialiserad vy, som hålls uppdaterad med triggers. Varje köp som görs måste uppdatera innehållet i den materialiserade vyn.
Man kan också köra frågan mer sällan, till exempel en gång varje natt, och visa ett cachat svar på hemsidan. Då kommer det inte alltid att vara rätt kund som visas, men det kanske Sodexo kan stå ut med.

Uppgift 5 (5 p)

När man programmerar numera är det populärast med objektorienterad programmering. Samtidigt är det relationsdatabaser som är den vanligaste typen av databaser. Om man har ett objektorienterat program, och vill lagra det programmets objekt i en databas, brukar man därför behöva översätta från programmets objekt till databasens tabeller.

Ett objektorienterat program innehåller följande klasser, alltså typer av objekt:

Fordon. Varje fordon har ett unikt registreringsnummer (till exempel OAU686), en vikt (till exempel 2600 kg) och en färg (till exempel röd).
Varje fordon kan vara antingen ett motorfordon eller ett omotoriserat fordon. Klassen Fordon har därför två subklasser, Motorfordon och (kan vi kalla dem) FordonUtanMotor. Motorfordon har, förutom samma egenskaper som andra fordon, även en motorstyrka, till exempel 800 hästkrafter.
Klassen Motorfordon är i sin tur uppdelad i två subklasser: Bilar och Motorcyklar.
Klassen Bilar är i sin tur uppdelad i tre subklasser: Lastbilar, Personbilar och Bussar. Lastbilar har en maxlast (till exempel 10000 kg), och bussar har ett antal platser (till exempel 20).

a) (2p) Rita ett EER-diagram som innehåller entitetstyperna ovan, med arv och egenskaper.

Ett EER-diagram

b) (2p) Översätt EER-diagrammet till tabeller. Du behöver inte skriva create table-kommandon i SQL, men du ska ange vilka relationer som finns och vilka attribut varje relation innehåller. Ange också vad som är primärnyckel, och vilka referensattribut som finns och vad de refererar till.

Fordon(Regnr, Vikt, Färg)
Motorfordon(Regnr, Motorstyrka)
FordonUtanMotor(Regnr)
Bilar(Regnr)
Motorcyklar(Regnr)
Lastbilar(Regnr, Maxlast)
Personbilar(Regnr)
Bussar(Regnr, Platser)

Regnr är primärnyckel i alla tabellerna. Motorfordon.Regnr och FordonUtanMotor.Regnr refererar båda till Fordon.Regnr. Bilar.Regnr och Motorcyklar.Regnr refererar båda till Motorfordon.Regnr. Lastbilar.Regnr, Personbilar.Regnr och Bussar.Regnr refererar alla till Bilar.Regnr.

Nedan visas create table-kommandon och exempeldata, för att underlätta provkörningar.

DROP TABLE Fordon;
DROP TABLE Motorfordon;
DROP TABLE FordonUtanMotor;
DROP TABLE Bilar;
DROP TABLE Motorcyklar;
DROP TABLE Lastbilar;
DROP TABLE Personbilar;
DROP TABLE Bussar;

CREATE TABLE Fordon
(Regnr INTEGER NOT NULL PRIMARY KEY,
Vikt INTEGER,
Färg VARCHAR(10));

CREATE TABLE Motorfordon
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Fordon(Regnr),
Motorstyrka INTEGER);

CREATE TABLE FordonUtanMotor
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Fordon(Regnr));

CREATE TABLE Bilar
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Motorfordon(Regnr));

CREATE TABLE Motorcyklar
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Motorfordon(Regnr));

CREATE TABLE Lastbilar
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Bilar(Regnr),
Maxlast INTEGER);

CREATE TABLE Personbilar
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Bilar(Regnr));

CREATE TABLE Bussar
(Regnr INTEGER NOT NULL PRIMARY KEY REFERENCES Bilar(Regnr),
Platser INTEGER);

INSERT INTO Fordon VALUES (1, 10, 'Blå');
INSERT INTO Fordon VALUES (2, 20, 'Blå');
INSERT INTO Fordon VALUES (3, 30, 'Blå');
INSERT INTO Fordon VALUES (4, 40, 'Röd');
INSERT INTO Fordon VALUES (5, 50, 'Röd');
INSERT INTO Fordon VALUES (6, 60, 'Röd');

INSERT INTO Motorfordon VALUES (1, 1000);
INSERT INTO Motorfordon VALUES (2, 2000);
INSERT INTO Motorfordon VALUES (4, 4000);
INSERT INTO Motorfordon VALUES (5, 5000);

INSERT INTO Bilar VALUES (1);
INSERT INTO Bilar VALUES (2);
INSERT INTO Bilar VALUES (4);
INSERT INTO Bilar VALUES (5);

INSERT INTO Lastbilar VALUES (1, 100);
INSERT INTO Bussar VALUES (2, 20);
INSERT INTO Lastbilar VALUES (4, 400);
INSERT INTO Bussar VALUES (5, 50);

c) (1p) Skriv en SQL-fråga som tar fram vikten på alla röda bussar.

SELECT Fordon.Vikt
FROM Fordon, Motorfordon, Bilar, Bussar
WHERE Fordon.Regnr = Motorfordon.Regnr
AND Motorfordon.Regnr = Bilar.Regnr
AND Bilar.Regnr = Bussar.Regnr
AND Fordon.Färg = 'Röd';

Eller enklare:

SELECT Fordon.Vikt
FROM Fordon, Bussar
WHERE Fordon.Regnr = Bussar.Regnr
AND Fordon.Färg = 'Röd';

Eller:

SELECT Vikt
FROM Fordon
WHERE Färg = 'Röd'
AND Regnr IN (SELECT Regnr FROM Bussar);

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 2 februari 2019