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:

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:

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