Databasteknik: Lösningar till tentamen 2017-08-21

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.

Svar:

Ett ER-diagram

Ett alternativ är att byta ut många-till-många-sambanet Ingår mot en entitetstyp Försäljningsrad, som motsvarar kopplingen mellan ett försäljningstillfälle och en vara. Försäljningsrad kan vara en svag entitetstyp.

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.

Svar:

Restauranger(Nummer, Adress, Telefon)
Varor(Nummer, Namn, Pris)
Försäljningstillfällen(Nummer, Restaurang, Tid)
Ingår(ID, Försäljningstillfälle, Vara, Antal);

Primär- och kandidatnycklarna är understrukna. I alla tabellerna den kandidatnyckel som står först primärnyckel. I tabellen Ingår är ID en databasintern nyckel för att underlätta arbetet. Vi kan också klara oss utan den.

Främmande nycklar:

Försäljningstillfälle.Restaurang till Restauranger.Nummer
Ingår.Försäljningstillfälle till Försäljningstillfälle.Nummer
Ingår.Vara till Varor.Nummer

Här finns create table-kommandon och exempeldata, för att underlätta provkörningar:

DROP TABLE Ingår;
DROP TABLE Försäljningstillfällen;
DROP TABLE Varor;
DROP TABLE Restauranger;

CREATE TABLE Restauranger
(Nummer INTEGER NOT NULL PRIMARY KEY,
Adress NVARCHAR(10) NOT NULL UNIQUE, -- Motverkar felet i fråga 3g!
Telefon NVARCHAR(10) NOT NULL UNIQUE );

CREATE TABLE Varor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE,
Pris DECIMAL(5,2) NOT NULL);

CREATE TABLE Försäljningstillfällen
(Nummer INTEGER NOT NULL PRIMARY KEY,
Restaurang INTEGER NOT NULL REFERENCES Restauranger(Nummer),
Tid TIMESTAMP NOT NULL);

CREATE TABLE Ingår
(ID INTEGER NOT NULL PRIMARY KEY,
Försäljningstillfälle INTEGER NOT NULL REFERENCES Försäljningstillfällen(Nummer),
Vara INTEGER NOT NULL REFERENCES Varor(Nummer),
Antal INTEGER,
UNIQUE(Vara, Försäljningstillfälle));

INSERT INTO Restauranger (Nummer, Adress, Telefon) VALUES (1, 'Vägen 1', '012345');
INSERT INTO Restauranger (Nummer, Adress, Telefon) VALUES (2, 'Vägen 2', '728009');
INSERT INTO Restauranger (Nummer, Adress, Telefon) VALUES (3, 'Vägen 3', '019-94639');

INSERT INTO Varor (Nummer, Namn, Pris) VALUES (1, 'Big Mac', 49.00);
INSERT INTO Varor (Nummer, Namn, Pris) VALUES (2, 'McFeast', 49.90);
INSERT INTO Varor (Nummer, Namn, Pris) VALUES (3, 'El Maco', 10.00);
INSERT INTO Varor (Nummer, Namn, Pris) VALUES (4, 'Äppelpaj', 0.90);
INSERT INTO Varor (Nummer, Namn, Pris) VALUES (5, 'Spajk', 10.00);

INSERT INTO Försäljningstillfällen (Nummer, Restaurang, Tid) VALUES (1, 1, TIMESTAMP '2017-08-21 12:10:21');
INSERT INTO Försäljningstillfällen (Nummer, Restaurang, Tid) VALUES (2, 2, TIMESTAMP '2017-08-21 12:10:22');
INSERT INTO Försäljningstillfällen (Nummer, Restaurang, Tid) VALUES (3, 1, TIMESTAMP '2017-08-21 12:10:23');

INSERT INTO Ingår (ID, Försäljningstillfälle, Vara, Antal) VALUES (1, 1, 1, 1);
INSERT INTO Ingår (ID, Försäljningstillfälle, Vara, Antal) VALUES (2, 1, 2, 1);
INSERT INTO Ingår (ID, Försäljningstillfälle, Vara, Antal) VALUES (3, 2, 1, 100);
INSERT INTO Ingår (ID, Försäljningstillfälle, Vara, Antal) VALUES (4, 2, 2, 10);
INSERT INTO Ingår (ID, Försäljningstillfälle, Vara, Antal) VALUES (5, 3, 4, 10);

SELECT * FROM Restauranger;
SELECT * FROM Varor;
SELECT * FROM Försäljningstillfällen;
SELECT * FROM Ingår;

Tabellutskrifter från Mimer med exempelraderna:

SELECT * FROM Restauranger;

     Nummer Adress     Telefon
=========== ========== ==========
          1 Vägen 1    012345
          2 Vägen 2    728009
          3 Vägen 3    019-94639

                  3 rows found

SELECT * FROM Varor;

     Nummer Namn          Pris
=========== ========== =======
          1 Big Mac      49.00
          2 McFeast      49.90
          3 El Maco      10.00
          4 Äppelpaj      0.90
          5 Spajk        10.00

                  5 rows found

SELECT * FROM Försäljningstillfällen;

     Nummer  Restaurang Tid
=========== =========== ==========================
          1           1 2017-08-21 12:10:21.000000
          2           2 2017-08-21 12:10:22.000000
          3           1 2017-08-21 12:10:23.000000

                  3 rows found

SELECT * FROM Ingår;

         ID Försäljningstillfälle        Vara       Antal
=========== ===================== =========== ===========
          1                     1           1           1
          2                     1           2           1
          3                     2           1         100
          4                     2           2          10
          5                     3           4          10

                  5 rows found

Uppgift 3 (13 p)

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

a) (1p) Vad kostar en Big Mac?

SELECT Pris FROM Varor WHERE Namn = 'Big Mac';

b) (1p) Vilka olika pajer (alltså varor som innehåller "paj" i namnet) finns det?

SELECT Namn FROM Varor WHERE Namn LIKE '%paj%';

c) (2p) Hur många Big Mac har sålts, sammanlagt?

SELECT Sum(Ingår.Antal)
FROM Ingår, Varor
WHERE Ingår.Vara = Varor.Nummer
AND Varor.Namn = 'Big Mac';

d) (2p) På vilka restauranger har det sålts äppelpajer? Ge restaurangernas nummer, adress och telefonnummer.

SELECT DISTINCT Restauranger.Nummer, Restauranger.Adress, Restauranger.Telefon
FROM Restauranger, Försäljningstillfällen, Ingår, Varor
WHERE Restauranger.Nummer = Försäljningstillfällen.Restaurang
AND Försäljningstillfällen.Nummer = Ingår.Försäljningstillfälle
AND Ingår.Vara = Varor.Nummer
AND Varor.Namn = 'Äppelpaj';

Det blir kortare med tabellalias, och därför kan det faktiskt vara lättare att läsa:

SELECT DISTINCT R.Nummer, R.Adress, R.Telefon
FROM Restauranger AS R, Försäljningstillfällen AS F, Ingår AS I, Varor AS V
WHERE R.Nummer = F.Restaurang
AND F.Nummer = I.Försäljningstillfälle
AND I.Vara = V.Nummer
AND V.Namn = 'Äppelpaj';

Man behöver inte ange tabellen för de kolumner som bara finns i en av de använda tabellerna, men jag rekommenderar inte att man gör så här, för då måste man hålla reda på vilka kolumner det är:

SELECT DISTINCT Restauranger.Nummer, Adress, Telefon
FROM Restauranger, Försäljningstillfällen, Ingår, Varor
WHERE Restauranger.Nummer = Restaurang
AND Försäljningstillfällen.Nummer = Försäljningstillfälle
AND Vara = Varor.Nummer
AND Namn = 'Äppelpaj';

Ett alternativ med nästlade frågor:

SELECT Nummer, Adress, Telefon
FROM Restauranger
WHERE Nummer IN (SELECT Restaurang
                 FROM Försäljningstillfällen
                 WHERE Nummer IN (SELECT Försäljningstillfälle
                                  FROM Ingår
                                  WHERE Vara IN (SELECT Nummer
                                                 FROM Varor
                                                 WHERE Namn = 'Äppelpaj')));

Ytterligare ett alternativ, med explicita joinar:

SELECT DISTINCT Restauranger.Nummer, Restauranger.Adress, Restauranger.Telefon
FROM Restauranger JOIN Försäljningstillfällen ON Restauranger.Nummer = Försäljningstillfällen.Restaurang
    JOIN Ingår ON Försäljningstillfällen.Nummer = Ingår.Försäljningstillfälle
    JOIN Varor ON Ingår.Vara = Varor.Nummer
WHERE Varor.Namn = 'Äppelpaj';

e) (2p) På vilka restauranger har det INTE sålts några äppelpajer? Ge restaurangernas nummer, adress och telefonnummer.

SELECT Nummer, Adress, Telefon
FROM Restauranger
WHERE Nummer NOT IN (SELECT Restaurang
                     FROM Försäljningstillfällen
                     WHERE Nummer IN (SELECT Försäljningstillfälle
                                      FROM Ingår
                                      WHERE Vara IN (SELECT Nummer
                                                     FROM Varor
                                                     WHERE Namn = 'Äppelpaj')));

f) (3p) Hur mycket har varje restaurang sålt för? Vi vill ha en listning med restaurangens nummer, adress, och den restaurangens sammanlagda försäljningssumma. Listningen ska sorteras i ordning efter försäljningssumman, så restaurangen som sålt mest kommer först. De restauranger som inte sålt något alls, behöver inte vara med i listan.

SELECT Restauranger.Nummer, Restauranger.Adress, SUM(Varor.Pris * Ingår.Antal) AS Summan
FROM Restauranger, Försäljningstillfällen, Ingår, Varor
WHERE Restauranger.Nummer = Försäljningstillfällen.Restaurang
AND Försäljningstillfällen.Nummer = Ingår.Försäljningstillfälle
AND Ingår.Vara = Varor.Nummer
GROUP BY Restauranger.Nummer, Restauranger.Adress
ORDER BY Summan DESC;

g) (2p) Det är förmodligen fel i databasen om det finns två restauranger på samma adress. Finns det några sådana restauranger i databasen, och vad har de i så fall för nummer och adress?

SELECT *
FROM Restauranger AS ena, Restauranger AS andra
WHERE ena.Adress = andra.Adress
AND ena.Nummer <> andra.Nummer;

Uppgift 4 (6 p)

McDonald's vill att antalet sålda Big Mac hela tiden ska visas på en gigantisk ljustavla på taket till huvudkontoret. För att få ett aktuellt värde, hämtas antalet ur databasen, genom att köra SQL-fråga c från uppgiften ovan. Frågan kommer att köras flera gånger i sekunden. Från början finns det inga index alls i databasen, inte ens på primärnycklar.

a) (2p) Vi lägger in några testrader i databasen och provkör frågan. Den går tillräckligt snabbt. Då är väl allt bra, och vi behöver inte göra något mer? Förklara hur det är!

Svar:

Nej, allt är inte bra än! Om man testkör med några få rader kan det gå snabbt, men när databasen kommer i drift på riktigt ska hela McDonald's försäljning lagras, och de säljer miljoner varor varje dag. Alltså kommer databasen att innehålla många miljoner rader i åtminstone Ingår-tabellen. Om vi antar att frågan har en tidskomplexitet på O(n), som vi ser nedan, kommer den att gå miljoner gånger långsammare.

b) (2p) Vi vill (oavsett svaret på fråga a) snabba upp körningen av frågan. Vilka index bör man skapa för att just den frågan ska gå snabbt att köra? Motivera valet.

Svar:

Ingår.Vara
Varor.Nummer (eventuellt inte, eftersom tabellen är liten)
Varor.Namn (eventuellt inte, eftersom tabellen är liten)

Det är de kolumner som används i frågan för sökningar eller i joinvillkor.

c) (2p) När databasen varit i drift några månader ringer McDonald's och berättar att de har skapat alla index som behövs, och visst går frågan fortare att köra än utan indexen, men den tar ändå alldeles för lång tid. Förklara varför, och berätta vad de kan göra åt saken!

Svar:

Oavsett vilka index man har, måste man läsa igenom hela tabellen Ingår. Man ska ju summera kolumnen Antal från samtliga rader. Frågan har alltså tidskomplexiteten O(n), där n är antalet rader i tabellen Ingår, och den tabellen innehåller miljoner rader. Enkla sökningar i en tabell med trädindex brukar ha tidskomplexiteten O(log n), men det är inte det vi gör här.

En möjlig lösning är att skapa en materialiserad vy med hjälp av triggers. En särskild tabell innehåller antalet sålda Big Mac, och vi skapar en trigger after insert on Ingår, som lägger till antalet Big Mac från Ingår-raden till antalet i tabellen med antalet sålda Big Mac. På det viset hålls summan hela tiden aktuell, och uppslagning av summan sker i konstant tid, dvs med tidskomplexiteten O(1).

Uppgift 5 (6 p)

McDonald's vill kunna dela in sina varor i flera olika kategorier, till exempel Efterrätter, Hamburgare och Drycker.

a) (2p) Man kan göra ändringen i ER-diagrammet, genom att skapa under-entitetstyper till entitetstypen Vara. Diagrammet blir då ett så kallat EER-diagram. Visa hur EER-diagrammet kommer att se ut. (Glöm inte kopplingarna till de andra entitetstyperna.)

Svar:

Ett EER-diagram

b) (2p) Översätt entitetstypen Vara och dess under-entitetstyper 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.

Svar:

Varor(Nummer, Namn, Pris)
Efterrätter(Nummer)
Hamburgare(Nummer)
Drycker(Nummer)

Nummer är primärnyckel i samtliga dessa tabeller, och i underentitetstypstabellerna är Nummer dessutom en främmande nyckel som refererar till Nummer i Varor. Notera att tabellen Varor är oförändrad jämfört med uppgift 2, och kolumnen Vara i tabellen Ingår refererar fortfarande till Nummer i Varor.

c) (2p) En nackdel med att skapa under-entitetstyper i EER-diagrammet är att databasen blir oflexibel. Det är svårt att lägga till en ny kategori. Visa en alternativ lösning, där det är lättare att lägga till kategorier. Rita upp ett ER- eller EER-diagram, och översätt också till tabeller!

Svar:

Ett EER-diagram

Tabeller:

Varor(Nummer, Namn, Pris, Kategori)
Kategorier(Namn)

Kategori i Varor refererar till Namn i Kategorier.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 23 augusti 2017