Databasteknik: Lösningar till tentamen 2024-05-29

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.

Scenario till uppgifterna

IMDB står för Internet Movie Database, och är en webbplats med information om filmer. Nu vill vi skapa en konkurrent, kallad BDMI (Big Deposit of Movie Information). Den ska finnas på webben och lagra informationen om filmerna i en databas.

Det som ska lagras i databasen är följande:

Filmer. Varje film har ett unikt nummer, till exempel 4711, ett utgivningsår, till exempel 1990, och ett land som filmen kommer från, till exempel USA. Filmen har också en eller flera titlar, dvs filmens namn. Exempelvis kallades filmen Reservoir Dogs för De hänsynslösa i Sverige. och Perros de reserva i Mexico. Titeln, eller titlarna, behöver inte vara unika. Exempelvis finns det minst tio olika filmer som heter Napoleon.
Skådespelare. Varje skådespelare har ett unikt nummer, ett namn namn som inte behöver vara unikt, ett födelseår och ett land som skådespelaren kommer från.
Vilka filmer varje skådespelare varit med i. Vi ska också ange vad rollfiguren som skådespelaren spelade i filmen hette. Till exempel var Linda Hamilton med i filmen Terminator 2: Judgment Day där hon spelade Sarah Connor.
Användare. Man ska kunna registrera en användare på webbplatsen, som man sen loggar in som, för att till exempel skriva recensioner. Varje användare har ett unikt nummer, ett unikt användarnamn och ett lösenord.
Recensioner som användarna skriver. Varje recension gäller en viss film och är skriven av en viss användare, den har ett antal stjärnor i betyg (mellan en och fem), och den har en recensionstext.

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 uppgifterna 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 EER-diagram

Några kommentarer om lösningen ovan:

Länderna kan också göras som vanliga attribut på filmerna och skådespelarna, i stället för en entitetstyp.
Rollfigurerna kan också göras som en entitetstyp, med samband till filmer och skådespelare.
Det är inte orimligt att göra filmtitlarna, dvs namnen på filmerna, som en entitetstyp med ett många-till-många-samband till filmerna.
Det är inte orimligt att göra recensionerna som ett många-till-många-samband mellan användarna och filmerna.
I scenariot har recensionerna inga nummer. Tabeller i en relationsdatabas ska alltid ha en primärnyckel, och det kan vara praktiskt att lägga till ett nummer redan i ER-diagrammet.

Uppgift 2 (6 p)

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

Svar:

Tabeller (kallas också relationer):

Länder(Nummer, Namn)
Filmer(Nummer, År, Land)
Titlar(Film, Titel)
Skådespelare(Nummer, Namn, Födelseår, Land)
Spelat(Skådespelare, Film, Rollfigur)
Användare(Nummer, Namn, Lösenord)
Recensioner(Nummer, Användare, Film, Betyg, Text)

Kandidatnycklar är understrukna. I de tabeller där det finns flera kandidatnycklar, är den första primärnyckel.

Referensattribut (kallas också främmande nycklar):

Filmer.Land refererar till Länder.Nummer
Titlar.Film refererar till Filmer.Nummer
Skådespelare.Land refererar till Länder.Nummer
Spelat.Skådespelare refererar till Skådespelare.Nummer
Spelat.Film refererar till Filmer.Nummer
Recensioner.Användare refererar till Användare.Nummer
Recensioner.Film refererar till Filmer.Nummer

Nedan visas create table-kommandon och exempeldata. Det krävs inte som svar, men är med för att underlätta provkörningar.

CREATE TABLE Länder
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE);

INSERT INTO Länder VALUES (1, 'Sverige');
INSERT INTO Länder VALUES (2, 'USA');
INSERT INTO Länder VALUES (3, 'Israel');

CREATE TABLE Filmer
(Nummer INTEGER NOT NULL PRIMARY KEY,
År INTEGER,
Land INTEGER REFERENCES Länder (Nummer));

INSERT INTO Filmer VALUES (1, 1991, 2);
INSERT INTO Filmer VALUES (2, 1992, 2);
INSERT INTO Filmer VALUES (3, 1957, 1);

CREATE TABLE Titlar
(Film INTEGER REFERENCES Filmer (Nummer),
Titel NVARCHAR(30) NOT NULL,
PRIMARY KEY (Film, Titel));

INSERT INTO Titlar VALUES (1, 'Terminator 2: Judgment Day');
INSERT INTO Titlar VALUES (2, 'Reservoir Dogs');
INSERT INTO Titlar VALUES (2, 'De hänsynslösa');
INSERT INTO Titlar VALUES (2, 'Perros de reserva');
INSERT INTO Titlar VALUES (3, 'Det sjunde inseglet');
INSERT INTO Titlar VALUES (3, 'The Seventh Seal');

CREATE TABLE Skådespelare
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(30) NOT NULL,
Födelseår INTEGER,
Land INTEGER REFERENCES Länder (Nummer));

INSERT INTO Skådespelare VALUES (1, 'Arnold Schwarzenegger', 1947, 2);
INSERT INTO Skådespelare VALUES (2, 'Max von Sydow', 1929, 1);

CREATE TABLE Spelat
(Skådespelare INTEGER REFERENCES Skådespelare (Nummer),
Film INTEGER REFERENCES Filmer (Nummer),
Rollfigur NVARCHAR(30) NOT NULL,
PRIMARY KEY (Skådespelare, Film));

INSERT INTO Spelat VALUES (1, 1, 'The Terminator');
INSERT INTO Spelat VALUES (2, 3, 'Antonius Block');

CREATE TABLE Användare
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE,
Lösenord NVARCHAR(10) NOT NULL);

INSERT INTO Användare VALUES (1, 'padrone', 'banan');
INSERT INTO Användare VALUES (2, 'joe', 'banan');
INSERT INTO Användare VALUES (3, 'donald', 'banan');

CREATE TABLE Recensioner
(Nummer INTEGER NOT NULL PRIMARY KEY,
Användare INTEGER NOT NULL REFERENCES Användare (Nummer),
Film INTEGER NOT NULL REFERENCES Filmer (Nummer),
UNIQUE (Användare, Film),
Betyg INTEGER NOT NULL,
Text NVARCHAR(10));

INSERT INTO Recensioner VALUES (1, 1, 1, 10, 'Bäst!');
INSERT INTO Recensioner VALUES (2, 1, 2, 9, 'Ok!');
INSERT INTO Recensioner VALUES (3, 1, 3, 8, 'Bra!');
INSERT INTO Recensioner VALUES (4, 2, 1, 10, NULL);
INSERT INTO Recensioner VALUES (5, 3, 1, 2, NULL);

Uppgift 3 (11 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) Från vilket land kommer skådespelaren Arnold Schwarzenegger?

Svar (några alternativ):

SELECT Länder.Namn
FROM Skådespelare, Länder
WHERE Skådespelare.Land = Länder.Nummer
AND Skådespelare.Namn = 'Arnold Schwarzenegger';

SELECT Länder.Namn
FROM Skådespelare JOIN Länder ON Skådespelare.Land = Länder.Nummer
AND Skådespelare.Namn = 'Arnold Schwarzenegger';

SELECT Namn
FROM Länder
WHERE Nummer IN (SELECT Land
                 FROM Skådespelare
                 WHERE Namn = 'Arnold Schwarzenegger');

Om man inte har en tabell med länder, utan det står bara ett namn på ett land direkt i skådespelartabellen, blir frågan enklare:

SELECT Land
FROM Skådespelare
WHERE Namn = 'Arnold Schwarzenegger';

b) (2p) Hur många filmer har han spelat i?

Svar (två alternativ):

SELECT COUNT(*)
FROM Skådespelare, Spelat, Filmer
WHERE Skådespelare.Nummer = Spelat.Skådespelare
AND Spelat.Film = Filmer.Nummer
AND Skådespelare.Namn = 'Arnold Schwarzenegger';

SELECT COUNT(*)
FROM Skådespelare, Spelat
WHERE Skådespelare.Nummer = Spelat.Skådespelare
AND Skådespelare.Namn = 'Arnold Schwarzenegger';

c) (2p) Vad heter rollfiguren som Max von Sydow spelar i filmen Det sjunde inseglet?

Svar:

SELECT Spelat.Rollfigur
FROM Skådespelare, Spelat, Filmer, Titlar
WHERE Skådespelare.Nummer = Spelat.Skådespelare
AND Spelat.Film = Filmer.Nummer
AND Filmer.Nummer = Titlar.Film
AND Skådespelare.Namn = 'Max von Sydow'
AND Titlar.Titel = 'Det sjunde inseglet';

d) (3p) Vilket genomsnittsbetyg har filmen Terminator 2: Judgment Day? (Tips: Man kan använda vanliga matematiska operationer, som division, i SQL.)

Svar (två alternativ):

SELECT AVG(Recensioner.Betyg)
FROM Recensioner, Filmer, Titlar
WHERE Recensioner.Film = Filmer.Nummer
AND Filmer.Nummer = Titlar.Film
AND Titlar.Titel = 'Terminator 2: Judgment Day';

SELECT AVG(1.0 * Recensioner.Betyg)
FROM Recensioner, Filmer, Titlar
WHERE Recensioner.Film = Filmer.Nummer
AND Filmer.Nummer = Titlar.Film
AND Titlar.Titel = 'Terminator 2: Judgment Day';

SELECT 1.0 * SUM(Recensioner.Betyg) / COUNT(Recensioner.Betyg)
FROM Recensioner, Filmer, Titlar
WHERE Recensioner.Film = Filmer.Nummer
AND Filmer.Nummer = Titlar.Film
AND Titlar.Titel = 'Terminator 2: Judgment Day';

En kommentar: Beroende på vilken databashanterare man använder kan man behöva multiplicera med 1.0 om man vill ha med decimaler i svaret. Annars blir det heltalsdivision, och exempelvis 2/3 blir noll.

e) (3p) Från vilket land kommer flest filmer?

Svar (ett alternativ med en vy och etyt med en CTE):

CREATE VIEW AntalFilmerPerLand AS
SELECT Länder.Namn AS Land, COUNT(*) AS Antal
FROM Filmer, Länder
WHERE Filmer.Land = Länder.Nummer
GROUP BY Länder.Namn; 

SELECT Land
FROM AntalFilmerPerLand
WHERE Antal IN (SELECT MAX(Antal) FROM AntalFilmerPerLand);

WITH AntalFilmerPerLand AS
(SELECT Länder.Namn AS Land, COUNT(*) AS Antal
FROM Filmer, Länder
WHERE Filmer.Land = Länder.Nummer
GROUP BY Länder.Namn)
SELECT Land
FROM AntalFilmerPerLand
WHERE Antal IN (SELECT MAX(Antal) FROM AntalFilmerPerLand);

Uppgift 4 (3 p)

Alla tabeller i filmdatabasen blir mycket stora, utom tabellen med länder ifall vi har en sådan. Vi märker att fråga c, om Max von Sydows rollfigur, körs ofta, men kanske med andra konstanter. Till exempel vill vi kanske veta vad Arnold Schwarzeneggers rollfigur i filmen The Terminator hette. Dessa frågor tar för lång tid att köra. Vi ser att det inte finns några index i databasen, inte ens på nycklar.

Vilka index bör man skapa för att denna sökning ska bli snabbare?

Svar:

Filmer.Nummer
Skådespelare.Namn
Skådespelare.Nummer
Spelat.Film
Spelat.Skådespelare
Titlar.Film
Titlar.Titel

Inte Spelat.Rollfigur! Poängavdrag för andra index, inklusive för felaktiga sammansatta index.

Uppgift 5 (3 p)

ACID-transaktioner kan vara viktiga när man arbetar med databaser. Välj en av de fyra egenskaperna och visa hur det skulle kunna uppstå problem i filmdatabasen om egenskapen saknades. Ge specifika exempel utgående från scenariot och din lösning.

Svar:

Om atomicitet (A) saknas: Man kanske vill lägga in en ny film i databasen. Då måste man lägga in rader inte bara i tabellen Filmer utan också i tabellerna Titlar, Skådespelare (om de inte redan fanns där) och Spelat. Man lägger in den i tabellen Filmer och i tabellen Titlar men innan man hinner lägga till rader i de andra tabellerna för att ange vilka skådespelare som var med, avbryts transaktionen av någon anledning. Då kommer det att se ut som att filmen inte hade några skådespelare alls.

Om hållbarhet (D, "durability") saknas: Om användarna lägger in recensioner och information om filmer i databasen, och får bekräftelser på att dessa data är inlagda, vill de förstås inte att deras data ska försvinna igen. Att data försvinner ur filmdatabasen BDMI är kanske inte lika illa som om de försvinner ur en bankdatabas, där en försvunnen ändring kan innebära att kundernas pengar försvinner, men om det händer för ofta tappar användarna förtroende för webbplatsen, och det kan bli svårt för BDMI att konkurrera med IMDB.

Uppgift 6 (2 p)

En fruktodlare har en databas med alla sina träd och alla sina äpplen. Det är normala äppelträd och normala äpplen. Varje äpple växer på ett träd.

Fruktodlaren har förstås många äppelträd och många äpplen. Sambandet att äpplena växer på träden, är det ett många-till-många-samband? Förklara!

Svar:

Se teoriövning 1!

Nej, det är ett många-till-ett-samband!

"Många till många", "ett till många" och så vidare handlar inte om hur många äpplen och träd det finns, utan det handlar om hur många äpplen ett träd kan kopplas ihop med, och hur många träd ett äpple kan kopplas ihop med.

Varje träd kan kopplas ihop med många äpplen, för det kan växa många äpplen på varje träd. Alltså ska det vara många åt det hållet i sambandet.

Varje äpple kan kopplas ihop med ett enda träd, för ett äpple växer bara på ett enda träd, inte flera. Alltså ska det vara ett åt det hållet i sambandet.

Uppgift 7 (2 p)

Fruktodlaren i uppgiften ovan är upptagen med att odla äpplen, och har förstås inte tid att fördjupa sig i databaser. Hon försöker ändå skapa en databas för sina träd och äpplen, och åstadkommer denna tabell:

Äpplen
Trädnummer	Äppelnummer
1	1, 2, 3, 4, 9
2	5, 7, 8, 10, 11
3	6, 12, 13
4	14, 15, 16, 17, 18, 19
5	20, 22, 24
6	21, 23

Vilka normalformer, av 1NF, 2NF, 3NF och BCNF, uppfyller tabellen? Motivera svaret!

Svar:

Den uppfyller inga av de normalformerna. Kolumnen Äppelnummer innehåller en lista med äppelnummer, så det är alltså inte ett atomärt attribut. Därför bryter den mot första normalformen. Eftersom den andra och tredje normalformen, och Boyce-Codds normalform, är strängare och strängare krav, dvs de lägger på fler och fler krav på tabellen, uppfyller tabellen inte heller dessa normalformer.

Uppgift 8 (8 p)

a) Vad är det för skillnad på DBA och DBMS?

Svar:

En DBA (databasadministratör) är en person, eller en hel grupp av personer, som ansvarar för databasens drift. Ett DBMS (database management system, eller på svenska databashanterare) är det program, eller system av program, som hanterar databasen.

b) Vad är det för skillnad på ett index och en primärnyckel?

Svar:

En primärnyckel är en logisk egenskap hos en tabell, medan ett index är en datastruktur som används internt av databashanteraren. En primärnyckel (i den vanliga, logiska betydelsen) är en kolumn eller kombination av kolumner i en tabell som är garanterad att vara unik, dvs det kan inte finnas två eller flera rader i tabellen med samma värden i den kolumnen eller kombinationen av kolumner. Ett index är en datastruktur som databashanteraren internt använder för att söka efter rader i en tabell, snabbare än genom att bara läsa igenom hela tabellen. Index måste inte vara unika, dvs de kan, men måste inte, indexera en unik kolumn eller kombination av kolumner.

c) Vad är det för skillnad på ALTER och UPDATE?

Svar:

ALTER används för att ändra en tabells schema, till exempel för att lägga till en ny kolumn. UPDATE används för att ändra innehållet på en eller flera rader i tabellen.

d) Vad är det för skillnad, särskilt med tanke på webbplatser som använder databaser, på serverkällkod och klientkällkod?

Svar:

Serverkällkod är den källkod till (i första hand) en webbsida som lagras på servern. Den kan innehålla kommandon och andra element som servern först utför, för att till exempel hämta data ur en databas som ska visas på webbsidan. Klientkällkod är den HTML-kod som sedan skickas till webbläsaren, och den skiljer sig från serverkällkoden till exempel genom att kommandona för att hämta data ur en databas har ersatts med de data som hämtades.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 16 juni 2024