Databasteknik: En del lösningar till tentamen 2025-03-19

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.

Ordet "attribut" böjs så här:
ett attribut, två attribut, det attributet, de attributen
inte t. ex. en attribut, två attributer

Uppgift 1 (7 p)

Skiss över tabellerna:

Skiss över tabellerna

Man behöver inte skriva create table-kommandon i svaret, men de visas här, samt exempeldata, för att underlätta provkörningar.

DROP TABLE Butiker CASCADE;
DROP TABLE Varor CASCADE;
DROP TABLE Pensionärer CASCADE;
DROP TABLE Telefonnummer CASCADE;
DROP TABLE Besök CASCADE;
DROP TABLE Deltog CASCADE;
DROP TABLE Priser CASCADE;

CREATE TABLE Butiker
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(6) NOT NULL,
Ort NVARCHAR(6) NOT NULL,
UNIQUE (Namn, Ort));

INSERT INTO Butiker VALUES (1, 'ICA', 'Örebro');
INSERT INTO Butiker VALUES (2, 'Willys', 'Örebro');
INSERT INTO Butiker VALUES (3, 'K-ICA', 'Kumla');

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

INSERT INTO Varor VALUES (1, 'Kaviar');
INSERT INTO Varor VALUES (2, 'Ost');
INSERT INTO Varor VALUES (3, 'Gurka');

CREATE TABLE Pensionärer
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(6) NOT NULL,
Födelseår INTEGER CHECK (Födelseår > 1800));

INSERT INTO Pensionärer VALUES (1, 'Agda', 1950);
INSERT INTO Pensionärer VALUES (2, 'Bodil', 1940);
INSERT INTO Pensionärer VALUES (3, 'Carl', 1945);
INSERT INTO Pensionärer VALUES (4, 'Doris', 1955);

CREATE TABLE Telefonnummer
(Pensionär INTEGER NOT NULL REFERENCES Pensionärer(Nummer),
Telefon NVARCHAR(6) NOT NULL,
PRIMARY KEY (Pensionär, Telefon));

INSERT INTO Telefonnummer VALUES (1, '123456');
INSERT INTO Telefonnummer VALUES (1, '123457');
INSERT INTO Telefonnummer VALUES (2, '123457');
INSERT INTO Telefonnummer VALUES (3, '123458');
INSERT INTO Telefonnummer VALUES (4, '123459');

CREATE TABLE Besök
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL,
Butik INTEGER REFERENCES Butiker(Nummer));

INSERT INTO Besök VALUES (1, DATE '2025-03-06', 1);
INSERT INTO Besök VALUES (2, DATE '2025-03-07', 1);
INSERT INTO Besök VALUES (3, DATE '2025-03-06', 2);
INSERT INTO Besök VALUES (4, DATE '2025-03-07', 2);

CREATE TABLE Deltog
(Pensionär INTEGER REFERENCES Pensionärer(Nummer),
Besök INTEGER REFERENCES Besök(Nummer),
PRIMARY KEY (Pensionär, Besök));

INSERT INTO Deltog VALUES (1, 1);
INSERT INTO Deltog VALUES (1, 2);
INSERT INTO Deltog VALUES (1, 3);
INSERT INTO Deltog VALUES (2, 1);
INSERT INTO Deltog VALUES (2, 2);
INSERT INTO Deltog VALUES (4, 4);

CREATE TABLE Priser
(Besök INTEGER REFERENCES Besök(Nummer),
Vara INTEGER REFERENCES Varor(Nummer),
Pris FLOAT CHECK (Pris >= 0),
PRIMARY KEY (Besök, Vara));

INSERT INTO Priser VALUES (1, 1, 14.50);
INSERT INTO Priser VALUES (1, 2, 15.50);
INSERT INTO Priser VALUES (1, 3, 16.50);
INSERT INTO Priser VALUES (2, 1, 17.50);
INSERT INTO Priser VALUES (2, 2, 18.50);
INSERT INTO Priser VALUES (2, 3, 199.50);
INSERT INTO Priser VALUES (3, 1, 17.50);
INSERT INTO Priser VALUES (3, 2, 18.50);
INSERT INTO Priser VALUES (4, 2, 17.50);
INSERT INTO Priser VALUES (4, 3, 18.50);

SELECT * FROM Butiker;
SELECT * FROM Varor;
SELECT * FROM Pensionärer;
SELECT * FROM Telefonnummer;
SELECT * FROM Besök;
SELECT * FROM Deltog;
SELECT * FROM Priser;

Uppgift 2 (13 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) (1p) Vad heter de butiker som finns i Örebro?

SELECT Namn FROM Butiker WHERE Ort = 'Örebro';

-- b) (2p) I hur många städer finns det ICA-butiker? Dvs, vad är antalet
-- städer där det finns minst en butik med ett namn som innehåller ICA,
-- till exempel Norra ICA Närköp?

SELECT COUNT(DISTINCT Ort) FROM Butiker WHERE Namn LIKE '%ICA%';

-- c) (2p) Det finns bara en pensionär som heter Agda Svensson och är född
.. 1950. Vilka telefonnummer har hon?

SELECT Telefon
FROM Telefonnummer
WHERE Pensionär IN (SELECT Nummer FROM Pensionärer
                    WHERE Namn = 'Agda Svensson'
                    AND Födelseår = 1950);

SELECT Telefonnummer.Telefon
FROM Telefonnummer, Pensionärer
WHERE Telefonnummer.Pensionär = Pensionärer.Nummer
AND Pensionärer.Namn = 'Agda Svensson'
AND Pensionärer.Födelseår = 1950;

SELECT Telefon
FROM Telefonnummer, Pensionärer
WHERE Pensionär = Nummer
AND Namn = 'Agda Svensson'
AND Födelseår = 1950;

SELECT Telefon
FROM Telefonnummer JOIN Pensionärer ON Pensionär = Nummer
WHERE Namn = 'Agda Svensson'
AND Födelseår = 1950;

-- d) (2p) 12 februari 2025 besökte Agda Svensson, från uppgiften ovan,
-- några olika butiker som ligger i Örebro. Vad heter de butikerna?

SELECT Butiker.Namn
FROM Pensionärer, Deltog, Besök, Butiker
WHERE Pensionärer.Nummer = Deltog.Pensionär
AND Deltog.Besök = Besök.Nummer
AND Besök.Butik = Butiker.Nummer
AND Pensionärer.Namn = 'Agda Svensson'
AND Pensionärer.Födelseår = 1950
AND Butiker.Ort = 'Örebro'
AND Besök.Datum = DATE '2025-02-12';

-- e) (3p) En av butikerna som Agda Svensson besökte 12 februari 2025
-- heter Coop Forum och ligger i Örebro. Hur många varor undersökte hon
-- priset på vid det besöket?

SELECT COUNT(*)
FROM Pensionärer, Deltog, Besök, Butiker, Priser, Varor
WHERE Pensionärer.Namn = 'Agda Svensson'
AND Pensionärer.Födelseår = 1950
AND Pensionärer.Nummer = Deltog.Pensionär
AND Deltog.Besök = Besök.Nummer
AND Besök.Datum = DATE '2025-02-12'
AND Besök.Butik = Butiker.Nummer
AND Butiker.Namn = 'Coop Forum'
AND Butiker.Ort = 'Örebro'
AND Besök.Nummer = Priser.Besök
AND Priser.Vara = Varor.Nummer;

SELECT COUNT(*)
FROM Priser
WHERE Besök IN (SELECT Nummer
                FROM Besök
                WHERE Datum = DATE '2025-02-12'
                AND Nummer IN (SELECT Besök
                               FROM Deltog
                               WHERE Pensionär IN (SELECT Nummer
                                                   FROM Pensionärer
                                                   WHERE Namn = 'Agda Svensson'
                                                   AND Födelseår = 1950))
AND Butik IN (SELECT Nummer
              FROM Butiker
              WHERE Namn = 'Coop Forum'
              AND Ort = 'Örebro'))

-- f) (3p) Vilken pensionär har gjort flest besök? Vi vill veta namn och
-- nummer på den pensionären.

WITH AntalBesökPerPensionär AS
(SELECT Pensionärer.Nummer, Pensionärer.Namn, COUNT(*) AS Antal
FROM Pensionärer, Deltog
WHERE Pensionärer.Nummer = Deltog.Pensionär
GROUP BY Pensionärer.Nummer, Pensionärer.Namn)
SELECT Namn, Nummer
FROM AntalBesökPerPensionär
WHERE Antal = (SELECT MAX(Antal) FROM AntalBesökPerPensionär);

Uppgift 3 (5 p)

-- Ange för var och en av sökningarna hur man kan få dem att gå snabbare:

-- a) Sökningen i deluppgift 2a

CREATE INDEX Butikortsindex ON Butiker(Ort);

-- b) Sökningen i deluppgift 2b

Ett vanligt index på butiksnamnet hjälper inte, på samma sätt som man
inte snabbt kan slå upp i registret på en bok vilka ord som innehåller
"ICA", utan bara de ord som börjar med "ICA".

En del databashanterare har fritextindex, som låter oss indexera
enskilda ord i texter. Man kan också tänka sig att man bygger ett
sådant själv med en mellantabell: Ordindex(EnskiltOrd, Butik).

Som alternativ kan man förstås skaffa snabbare hårdvara, i första hand
lagringen som till exempel kan vara snabba SSD:er, gärna med databasen
uppdelade på flera så man kan läsa parallellt från dem.

-- c) Sökningen i deluppgift 2c

CREATE INDEX Pensionärsnamnsindex ON Pensionärer(Namn);
CREATE INDEX Pensionärsårsindex ON Pensionärer(Födelseår);
CREATE INDEX Telefonpensionärsindex ON Telefonnummer(Pensionär);

Eventuellt också:

CREATE INDEX Pensionärsnummerindex ON Pensionärer(Nummer);

-- d) Sökningen i deluppgift 2d

Skapa index på alla kolumner som används i WHERE-villkoret:

CREATE INDEX i1 ON Pensionärer(Nummer);
CREATE INDEX i2 ON Deltog(Pensionär);
CREATE INDEX i3 ON Deltog(Besök);
CREATE INDEX i4 ON Besök(Nummer);
CREATE INDEX i5 ON Besök(Butik);
CREATE INDEX i6 ON Butiker(Nummer);
CREATE INDEX i7 ON Pensionärer(Namn);
CREATE INDEX i8 ON Pensionärer(Födelseår);
CREATE INDEX i9 ON Butiker(Ort);
CREATE INDEX i10 ON Besök(Datum);

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 7 april 2025