Databasteknik: Lösningar till tentamen 2020-01-14

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 ER-diagram

En kommentar: Det går att tolka scenariot som i ER-diagrammet ovan, att penntyper från olika tillverkare visserligen kan ha samma namn, men det är olika typer. Man kan också tolka det som att flera olika tillverkare kan tillverka samma typ av penna. Penntypernas namn blir då unika, och vi får ett många-till-många-samband mellan tillverkare och penntyper.

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.

Tillverkare(Nummer, Namn)
Penntyper(Nummer, Namn, Tillverkare)
Färger(Nummer, Namn)
Pennor(Nummer, Typ, Färg, Status)

Primärnycklarna är understrukna. I tabellen Tillverkare är även Namn en kandidatnyckel. I tabellen Färger är även Namn en kandidatnyckel. I tabellen Penntyper är även kombinationen av Namn och Tillverkare en sammansatt kandidatnyckel.

Främmande nycklar:

Penntyper.Tillverkare till Tillverkare.Nummer
Pennor.Typ till Penntyper.Nummer
Pennor.Färg till Färger.Nummer

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

DROP TABLE Pennor;
DROP TABLE Färger;
DROP TABLE Penntyper;
DROP TABLE Tillverkare;

CREATE TABLE Tillverkare
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(13) NOT NULL UNIQUE);

CREATE TABLE Penntyper
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(26) NOT NULL,
Tillverkare INTEGER NOT NULL REFERENCES Tillverkare(Nummer),
UNIQUE (Namn, Tillverkare));

CREATE TABLE Färger
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(7) NOT NULL UNIQUE);

CREATE TABLE Pennor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Penntyp INTEGER NOT NULL REFERENCES Penntyper(Nummer),
Färg INTEGER NOT NULL REFERENCES Färger(Nummer),
Status NVARCHAR(10));

INSERT INTO Tillverkare (Nummer, Namn) VALUES (1, 'Faber-Castell');
INSERT INTO Tillverkare (Nummer, Namn) VALUES (2, 'Ballograf');
INSERT INTO Tillverkare (Nummer, Namn) VALUES (3, 'Shachihata');

INSERT INTO Penntyper (Nummer, Namn, Tillverkare)
VALUES (10, 'Winner 152', 1);
INSERT INTO Penntyper (Nummer, Namn, Tillverkare)
VALUES (11, 'Friendly Whiteboard Marker', 2);
INSERT INTO Penntyper (Nummer, Namn, Tillverkare)
VALUES (12, 'Artline 519', 3);
INSERT INTO Penntyper (Nummer, Namn, Tillverkare)
VALUES (13, 'Ultrabultra', 1);
INSERT INTO Penntyper (Nummer, Namn, Tillverkare)
VALUES (14, 'Trultrabultra', 2);

INSERT INTO Färger VALUES (1, 'svart');
INSERT INTO Färger VALUES (2, 'röd');
INSERT INTO Färger VALUES (3, 'blå');
INSERT INTO Färger VALUES (4, 'grön');
INSERT INTO Färger VALUES (5, 'vit');
INSERT INTO Färger VALUES (6, 'osynlig');

INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (1, 11, 2, 'helt slut');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (2, 10, 1, 'helt slut');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (3, 11, 1, 'ganska bra');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (5, 11, 2, 'helt slut');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (6, 10, 3, 'helt slut');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (7, 12, 3, 'ny');
INSERT INTO Pennor (Nummer, Penntyp, Färg, Status) VALUES (11, 12, 4, 'helt slut');

SELECT * FROM Pennor;
SELECT * FROM Färger;
SELECT * FROM Penntyper;
SELECT * FROM Tillverkare;

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) (1p) Hur många olika tillverkare finns det?

SELECT COUNT(*) FROM Tillverkare;

b) (2p) Vilken eller vilka tillverkare tillverkar en penntyp med ett namn som innehåller ultra, till exempel Ultramarker 2000 eller Turbo-ultratronic B-36?

-- Alternativ 1, med implicit join:

SELECT Tillverkare.Namn
FROM Penntyper, Tillverkare
WHERE Penntyper.Tillverkare = Tillverkare.Nummer
AND Penntyper.Namn LIKE '%ultra%';

-- Alternativ 2, med explicit join:

SELECT Tillverkare.Namn
FROM Penntyper JOIN Tillverkare ON Penntyper.Tillverkare = Tillverkare.Nummer
WHERE Penntyper.Namn LIKE '%ultra%';

-- Alternativ 3, med en underfråga:

SELECT Namn
FROM Tillverkare
WHERE Nummer IN (SELECT Tillverkare
                 FROM Penntyper
                 WHERE Namn LIKE '%ultra%');

En kommentar: En del databashanterare skiljer som default på stora och små bokstäver, och andra gör det inte. Mimer råkar vara en som skiljer, och för att jämförelsen med '%ultra%' ska matcha även till exempel Turbo-ultratronic B-36, behöver man lägga till COLLATE SWEDISH till frågan. Dessa detaljer ligger dock utanför den här kursen.

c) (2p) Hur många typer av pennor tillverkas av Faber-Castell?

SELECT COUNT(*)
FROM Penntyper, Tillverkare
WHERE Penntyper.Tillverkare = Tillverkare.Nummer
AND Tillverkare.Namn = 'Faber-Castell';

d) (2p) Hur många av våra enskilda pennor har tillverkats av Faber-Castell?

SELECT COUNT(*)
FROM Pennor, Penntyper, Tillverkare
WHERE Pennor.Penntyp = Penntyper.Nummer
AND Penntyper.Tillverkare = Tillverkare.Nummer
AND Tillverkare.Namn = 'Faber-Castell';

e) (3p) Vilken färg finns det flest enskilda pennor av?

-- Alternativ 1, med en CTE (funkar av någon anledning inte i Mimer):

WITH AntalPennorPerFärg AS
(SELECT Färger.Namn AS Färg, COUNT(*) AS Antal
FROM Pennor, Färger
WHERE Pennor.Färg = Färger.Nummer
GROUP BY Färger.Namn)
SELECT Färg FROM AntalPennorPerFärg
WHERE Antal IN (SELECT MAX(Antal) FROM AntalPennorPerFärg);

-- Alternativ 2, med en vy (funkar i Mimer):

CREATE VIEW AntalPennorPerFärg AS
SELECT Färger.Namn AS Färg, COUNT(*) AS Antal
FROM Pennor, Färger
WHERE Pennor.Färg = Färger.Nummer
GROUP BY Färger.Namn;

SELECT Färg FROM AntalPennorPerFärg
WHERE Antal IN (SELECT MAX(Antal) FROM AntalPennorPerFärg);

Uppgift 4 (4 p)

Databasen växer, och efter några år har alla tabeller väldigt många rader. Sökningarna i uppgiften ovan tar lång tid att köra. Vi ser att det inte finns några index i databasen, inte ens på nycklar.

a) (1p) Hur skulle man kunna få sökningen i delfråga 3a att gå snabbare att köra?

Många databashanterare håller reda på statistik om vilka data som finns i tabellerna, bland annat antalet rader, och då går denna fråga mycket snabbt att köra. Eftersom frågan dock "tar lång tid att köra", enligt förutsättningarna, fungerar tydligen den här databashanteraren inte så.

Det finns några olika saker man kan prova:

Ett sätt att snabbt hitta antalet tillverkare kan vara att lagra antalet i en separat tabell, och använda triggers för att hålla värdet aktuellt.
Skapa ett index. Visserligen gör vi ingen sökning efter värden i tabellen efter värden, men det kan vara så att statistiken om antal rader finns direkt tillgänglig om det finns ett index, men inte om det inte finns. (MySQL visade sig fungera så, när jag provkörde.)
Man kan förstås också prova med en snabbare dator, till exempel att byta till SSD om den har en mekanisk hårddisk.
En annan databashanterare kanske fungerar annorlunda.

b) (3p) Hur skulle man kunna få sökningen i delfråga 3b att gå snabbare att köra?

Här är det lämpligt att skapa index på de kolumner som används i villkoret:

Penntyper.Tillverkare
Tillverkare.Nummer

Det hjälper förmodligen inte att skapa ett index av traditionell typ på Penntyper.Namn, eftersom vi inte söker på början av texten. (Jämför med ett uppslagsverk i bokform, och att slå upp inte bara ordet "ultra" som förstås står under bokstaven U, utan ord som innehåller "ultra".) Om databashanteraren har möjlighet att skapa någon sorts fritextindex kan det fungera.

Man ska inte skapa ett index på Tillverkare.Namn, eftersom den kolumnen visserligen finns med i resultatet, men den används inte för att söka fram rader.

Uppgift 5 (7 p)

Här är ett försök att lösa uppgift 2 ovan, med en databas som bara består av en enda tabell.

Pennor
Penn-nummer	Färg	Penntyp	Tillverkare	Status
1	röd	Friendly Whiteboard Marker	Ballograf	helt slut
2	svart	Winner 152	Faber-Castell	helt slut
3	svart	Friendly Whiteboard Marker	Ballograf	ganska bra
5	röd	Friendly Whiteboard Marker	Ballograf	helt slut
6	blå	Winner 152	Faber-Castell	helt slut
7	blå	Artline 519	Shachihata	ny
11	grön	Artline 519	Shachihata	helt slut

a) (1p) Vilka kandidatnycklar finns i tabellen?

Svar: Penn-nummer

b) (1p) Vilka fullständiga funktionella beroenden finns i tabellen?

Svar: Från Penn-nummer till vart och ett av de fyra andra attributen

c) (1p) Vilka av de fyra normalformerna 1NF, 2NF, 3NF och BCNF uppfyller tabellen?

Svar: Alla.

d) (2p) Är det här ett bra sätt att lösa uppgift 2? Motivera svaret!

Svar: Inte särskilt bra. Tabellen uppfyller visserligen normalformerna, men det finns ändå flera problem. Vi kan till exempel inte lagra en penntyp om vi inte har pennor av den typen, och vi kan inte lagra en tillverkare om denna inte tillverkar pennor, eller om vi inte har några pennor som den tillverkaren tillverkat.

Ett annat men antagligen inte lika allvarligt problem är att namnen, som är textsträngar, upprepas många gånger. Informationen, till exempel att penna nummer 5 är en Friendly Whiteboard Marker som tillverkats av Ballograf, upprepas inte, men man kunde sparat en del plats i databasen (och förmodligen gjort den snabbare) om man i stället gjort som i lösningsförslaget till uppgift 2 och lagrat att penna nummer 5 är av typ nummer 11 som tillverkats av tillverkare nummer 2.

e) (2p) Användarna trivs med en databas som ser ut som den här enda tabellen. Skapa en vy som motsvarar denna tabell, baserad på dina egna tabeller i din egen lösning på uppgift 2.

-- Förslag 1

CREATE VIEW Pennvy AS
SELECT P.Nummer AS Pennummer, F.Namn AS Färg, Typ.Namn AS Penntyp, Tv.Namn AS Tillverkare, P.Status AS Status
FROM Pennor AS P JOIN Penntyper AS Typ ON P.Penntyp = Typ.Nummer
    JOIN Tillverkare AS Tv ON Typ.Tillverkare = Tv.Nummer
    JOIN Färger AS F ON P.Färg = F.Nummer;

-- Förslag 2

CREATE VIEW Pennvy AS
SELECT P.Nummer AS Pennummer, F.Namn AS Färg, Typ.Namn AS Penntyp, Tv.Namn AS Tillverkare, P.Status AS Status
FROM Pennor AS P, Penntyper AS Typ, Tillverkare AS Tv, Färger AS F
WHERE P.Penntyp = Typ.Nummer
AND Typ.Tillverkare = Tv.Nummer
AND P.Färg = F.Nummer
AND P.Penntyp = Typ.Nummer;

Uppgift 6 (7 p)

Förklara, eventuellt med korta exempel om det behövs, skillnaden mellan

a) SQL-kommandona update och alter table

Svar: Update ändrar på tabellens data, dvs innehållet på raderna, medan alter table ändrar på schemat, dvs kolumnerna, deras domäner, integritetsvillkor och så vidare.

b) SQL-kommandona drop och delete

Svar: Även här handlar det om att ändra data respektive schema. Drop tar bort en hel tabell, vy eller annat objekt i schemat, medan delete tar bort en eller flera rader ur en tabell.

c) SQL-kommandona revoke och rollback

Svar: Revoke tar bort åtkomsträttigheter som man delat ut tillen användare, medan rollback avbryter en transaktion och ändrar tillbaka alla ändringar som den transaktionen gjort.

d) group by och order by i SQL

Svar: Order by sorterar raderna i resultatet, medan group by används tillsammans med aggregatfunktioner för att dela upp raderna som ska aggregeras i olika grupper, så varje grupp får sitt aggregeringsvärde.

e) en vy och en tabell

Svar: En tabell är en samling data som lagras i databasen, medan en vy är en sökning, där man lagrat sökkommandot i databasen, och sökningen körs på nytt när man behöver datat i vyn. (En del databashanterare har också så kallade materialiserade vyer, där vyresultatet förberäknas och lagras i databasen. Man kan också göra egna materialiserade vyer med en separat tabell och triggers.)

f) en databashanterare och en databasadministratör

Svar: En databashanterare är ett program, eller en samling program, som används för att arbeta med och söka i databasen, medan en databasadministratör är en person, eller en hel grupp personer, som arbetar med databasens dagliga drift och administration.

g) atomicitet och isolering (i samband med transaktioner)

Svar: Atomicitet innebär att transaktionen ska genomföras som en helhet, så att antingen genomförs alla dess ändringar eller inga av dem, medan isolering innebär att olika transaktioner som körs samtidigt inte ska påverka varandra, till exempel genom att skriva över varandras resultat på ett sätt som leder till en inkonsistent databas.

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