Databasteknik: Lösningar till tentamen 2017-01-10

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)

Ett ER-diagram

Ett uppenbart alternativ är att låta Symtom vara en egen entitetstyp, med ett många-till-många-samband mellan entitetstyperna Symtom och Sjukdom.

Man kan också göra Symtom som en svag entitetstyp.

Uppgift 2 (6 p)

Avdelningar(Nummer, Namn)
Patienter(ID, Personnummer, Namn, Avdelning);
Sjukdomar(ID, Namn)
Symtom(ID, Sjukdom, Symtom)
Diagnoser(ID, Patient, Sjukdom)

Primärnycklarna är understrukna. I tabellen Avdelningar är även Namn en kandidatnyckel. I tabellen Patienter är även Personnummer en kandidatnyckel. I tabellen Sjukdomar är även Namn en kandidatnyckel. I tabellen Diagnoser är även kombinationen av Patient och Sjukdom en kandidatnyckel. I de tabeller som har primärnyckeln ID utgörs den av en databasintern nyckel för att underlätta arbetet, men vi kan också klara oss utan den.

Främmande nycklar:

Patienter.Avdelning till Avdelningar.Nummer
Symtom.Sjukdom till Sjukdomar.ID
Diagnoser.Patient till Patienter.ID
Diagnoser.Sjukdom till Sjukdomar.ID

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

DROP TABLE Diagnoser;
DROP TABLE Symtom;
DROP TABLE Sjukdomar;
DROP TABLE Patienter;
DROP TABLE Avdelningar;

CREATE TABLE Avdelningar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE Patienter
(ID INTEGER NOT NULL PRIMARY KEY,
Personnummer CHAR(12) NOT NULL UNIQUE,
Namn NVARCHAR(50) NOT NULL,
Avdelning INTEGER REFERENCES Avdelningar(Nummer));

CREATE TABLE Sjukdomar
(ID INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE Symtom
(ID INTEGER NOT NULL PRIMARY KEY,
Sjukdom INTEGER NOT NULL REFERENCES Sjukdomar(ID),
Symtom NVARCHAR(50) NOT NULL,
UNIQUE(Sjukdom, Symtom));

CREATE TABLE Diagnoser
(ID INTEGER NOT NULL PRIMARY KEY,
Patient INTEGER NOT NULL REFERENCES Patienter(ID),
Sjukdom INTEGER NOT NULL REFERENCES Sjukdomar(ID),
UNIQUE(Patient, Sjukdom));

INSERT INTO Avdelningar (Nummer, Namn) VALUES (1, 'IVA');
INSERT INTO Avdelningar (Nummer, Namn) VALUES (2, 'Medicin');
INSERT INTO Avdelningar (Nummer, Namn) VALUES (3, 'Ortopedi');
INSERT INTO Avdelningar (Nummer, Namn) VALUES (17, 'Kirurgi');

INSERT INTO Patienter (ID, Personnummer, Namn, Avdelning) VALUES (1, '631211-1658', 'Anna Berg', null);
INSERT INTO Patienter (ID, Personnummer, Namn, Avdelning) VALUES (2, '631211-3696', 'Bo Berg', 1);
INSERT INTO Patienter (ID, Personnummer, Namn, Avdelning) VALUES (3, '631211-2672', 'Cesar Berg', 2);
INSERT INTO Patienter (ID, Personnummer, Namn, Avdelning) VALUES (4, '631211-1906', 'Donna Berg', 1);
INSERT INTO Patienter (ID, Personnummer, Namn, Avdelning) VALUES (5, '631211-4751', 'Erik Berg', 2);

INSERT INTO Sjukdomar (ID, Namn) VALUES (1, 'skörbjugg');
INSERT INTO Sjukdomar (ID, Namn) VALUES (2, 'difteri');
INSERT INTO Sjukdomar (ID, Namn) VALUES (3, 'psykos');
INSERT INTO Sjukdomar (ID, Namn) VALUES (4, 'pest');
INSERT INTO Sjukdomar (ID, Namn) VALUES (5, 'kolera');

INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (1, 1, 5);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (2, 2, 5);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (3, 3, 5);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (4, 4, 5);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (5, 5, 5);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (6, 1, 1);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (7, 2, 1);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (8, 3, 1);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (9, 4, 1);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (10, 1, 4);
INSERT INTO Diagnoser (ID, Patient, Sjukdom) VALUES (11, 2, 4);

INSERT INTO Symtom (ID, Sjukdom, Symtom) VALUES (1, 4, 'bölder');
INSERT INTO Symtom (ID, Sjukdom, Symtom) VALUES (3, 4, 'hosta');
INSERT INTO Symtom (ID, Sjukdom, Symtom) VALUES (2, 4, 'feber');
INSERT INTO Symtom (ID, Sjukdom, Symtom) VALUES (4, 5, 'feber');

SELECT * FROM Avdelningar;
SELECT * FROM Patienter;
SELECT * FROM Sjukdomar;
SELECT * FROM Symtom;
SELECT * FROM Diagnoser;

Uppgift 3 (3 p)

a) 1NF

b) En del sökningar, till exempel på vilka sjukdomar som har symtomet feber, blir onödigt långsamma. En del frågor, till exempel vilket som är det vanligaste symtomet, blir mycket besvärliga att formulera i SQL.

Uppgift 4 (10 p)

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

a) (1p) Vad heter de avdelningar som har namn som börjar på bokstaven K?

SELECT Namn FROM Avdelningar WHERE Namn LIKE 'K%';

b) (2p) Vilka symtom drabbas man av om man har pest?

SELECT Symtom.Symtom
FROM Symtom, Sjukdomar
WHERE Symtom.Sjukdom = Sjukdomar.ID
AND Sjukdomar.Namn = 'pest';
Ett par alternativa lösningar:
SELECT Symtom
FROM Symtom
WHERE Sjukdom IN (SELECT ID
                  FROM Sjukdomar
                  WHERE Namn = 'pest');

SELECT Symtom
FROM Symtom JOIN Sjukdomar ON Symtom.Sjukdom = Sjukdomar.ID
WHERE Sjukdomar.Namn = 'pest';

c) (2p) Det kommer in en patient som har feber. Vilka sjukdomar kan hon ha? Vi vill alltså veta namnen på de sjukdomar som har feber bland sina symtom.

SELECT Sjukdomar.Namn
FROM Symtom, Sjukdomar
WHERE Symtom.Sjukdom = Sjukdomar.ID
AND Symtom.Symtom = 'feber';
Ett par alternativa lösningar:
SELECT Namn
FROM Sjukdomar
WHERE ID IN (SELECT Sjukdom
                  FROM Symtom
                  WHERE Symtom = 'feber');

SELECT Sjukdomar.namn
FROM Symtom JOIN Sjukdomar ON Symtom.Sjukdom = Sjukdomar.ID
WHERE Symtom.Symtom = 'feber';

d) (2p) Vad heter den avdelning där det just nu vårdas flest patienter?

CREATE VIEW PatienterPerAvdelning AS
SELECT Avdelningar.Namn, COUNT(*) AS Antal
FROM Patienter, Avdelningar
WHERE Patienter.Avdelning = Avdelningar.Nummer
GROUP BY Avdelningar.Namn;

SELECT Namn
FROM PatienterPerAvdelning
WHERE Antal = (SELECT MAX(Antal) FROM PatienterPerAvdelning);

e) (3p) Vad heter de stackars patienter som samtidigt har både pest och kolera?

SELECT Namn
FROM Patienter
WHERE ID IN (SELECT Patient
             FROM Diagnoser
             WHERE Sjukdom IN (SELECT ID
                               FROM Sjukdomar
                               WHERE Namn = 'pest'))
AND ID IN (SELECT Patient
           FROM Diagnoser
           WHERE Sjukdom IN (SELECT ID
                             FROM Sjukdomar
                             WHERE Namn = 'kolera'));
Några alternativa lösningar:
SELECT stackarna.Namn
FROM Patienter AS stackarna,
     Diagnoser AS pestdiagnos,
     Sjukdomar AS pest,
     Diagnoser AS koleradiagnos,
     Sjukdomar AS kolera
WHERE pest.namn = 'pest'
AND kolera.namn = 'kolera'
AND pestdiagnos.Sjukdom = pest.ID
AND koleradiagnos.Sjukdom = kolera.ID
AND stackarna.ID = pestdiagnos.Patient
AND stackarna.ID = koleradiagnos.Patient;

SELECT Patienter.Namn
FROM Patienter JOIN Diagnoser ON Patienter.ID = Diagnoser.Patient
JOIN Sjukdomar ON Diagnoser.Sjukdom = Sjukdomar.ID
WHERE Sjukdomar.Namn IN ('pest', 'kolera')
GROUP BY Patienter.ID, Patienter.Namn
HAVING COUNT(*) = 2;

SELECT ID, Namn
FROM Patienter
WHERE ID IN (SELECT Patient
             FROM Diagnoser
             WHERE Sjukdom IN (SELECT ID
                               FROM Sjukdomar
                               WHERE Namn = 'pest'))
INTERSECT
SELECT ID, Namn
FROM Patienter
WHERE ID IN (SELECT Patient
             FROM Diagnoser
             WHERE Sjukdom IN (SELECT ID
                               FROM Sjukdomar
                               WHERE Namn = 'kolera'));
Den här SQL-frågan ger fel svar, eftersom de två villkoren Sjukdomar.Namn = 'pest' och Sjukdomar.Namn = 'kolera' aldrig kan vara sanna samtidigt:
SELECT Patienter.Namn
FROM Patienter, Diagnoser, Sjukdomar
WHERE Patienter.ID = Diagnoser.Patient
AND Diagnoser.Sjukdom = Sjukdomar.ID
AND Sjukdomar.Namn = 'pest'
AND Sjukdomar.Namn = 'kolera';
Den här SQL-frågan ger också fel svar, nämligen de patienter som har minst en av pest och kolera, men inte nödvändigtvis båda:
SELECT Patienter.Namn
FROM Patienter, Diagnoser, Sjukdomar
WHERE Patienter.ID = Diagnoser.Patient
AND Diagnoser.Sjukdom = Sjukdomar.ID
AND (Sjukdomar.Namn = 'pest' OR Sjukdomar.Namn = 'kolera');
Och det här blir helt tokigt:
SELECT Patienter.Namn
FROM Patienter, Diagnoser, Sjukdomar
WHERE Patienter.ID = Diagnoser.Patient
AND Diagnoser.Sjukdom = Sjukdomar.ID
AND Sjukdomar.Namn = 'pest'
OR Sjukdomar.Namn = 'kolera';
Övning: Varför?

Uppgift 5 (3 p)

Likhet: Både vyer och tabeller ser ut och fungerar ganska lika när man använder dem i sökningar, och man kan använda dem på samma sätt i SELECT-frågor. Användaren som skriver SQL-frågor behöver (oftast) inte bry sig om ifall det är en vy eller en tabell.

Skillnad: En tabell innehåller data som lagras i databasen, men en vy är egentligen en SQL-fråga som fått ett namn, och där det är SQL-frågan som lagrats i databasen. (Undantag för så kallade materialiserade vyer, som finns som en inbyggd funktioni vissa databashanterare.)

Skillnad: Man kan inte alltid uppdatera en vy med INSERT, DELETE och UPDATE på samma sätt som en tabell.

Uppgift 6 (3 p)

a) Vilka index bör man skapa?

b) Ge ett exempel på ett index som inte skulle snabba upp någon av frågorna i a-c uppgift 4, och förklara varför det inte förbättrar tiderna!

Uppgift 7 (3 p)

ÖSÖ har tusentals anställda, som kommer att behöva jobba med databasen i scenariot. Nu behöver vi välja vilken databashanterare som ska användas.

a) Lämpliga databashanterare som nämnts i kursen: Mimer, MySQL, Microsoft SQL Server

b) En mindre lämplig databashanterare: Microsoft Access. Den klarar kanske datamängderna, men den är inte byggd för de höga belastningarna med tusentals användare och många som arbetar samtidigt.

Uppgift 8 (14 p)

Ange för varje påstående om det är sant eller falskt! Lämna in denna sida tillsammans med resten av svaren. Fel svar ger inte minuspoäng.

Påstående Sant Falskt
Uttrycket null = NULL i ett WHERE-villkor blir falskt. X  
Många databashanterare skapar automatiskt ett index på primärnyckeln i varje tabell. X  
Vi söker ofta efter patienter med ett visst namn. Då bör vi skapa ett sammansatt index på (ID, Namn), eftersom ID är primärnyckel i tabellen.   X
En vy kan användas för att dela upp en komplicerad fråga i flera steg. X  
Flera SQL-satser som hör ihop, så att antingen ska alla utföras eller också ingen, kan grupperas till en enhet i form av en transaktion. X  
Isolering mellan transaktioner betyder att varje transaktion har sina egna tabeller, som andra transaktioner inte kan se.   X
I en trigger kan vi göra mer komplexa kontroller än vad vi kan göra med integritetsvillkor i SQL. X  
SQL injection innebär att en hacker kan komma åt och förändra en databas genom att skriva in särskilt utformade texter till exempel i ett textfält på en webbsida. X  
Man kan använda kommandona LOCK och UNLOCK för att gruppera flera SQL-kommandon till en transaktion.   X
INSERT-kommandot kontrollerar nyckelvillkor, så man inte får dubbletter i primärnyckeln, men det gör inte UPDATE-kommandot, så med UPDATE kan man skapa dubbletter i primärnyckeln.   X
För att ta bort rättigheter till ett objekt i databasen från en användare använder man kommandot REVOKE. X  
"Relationerna" i en relationsdatabas är kopplingarna mellan tabeller med främmande nycklar ("foreign keys").   X
Ett funktionellt beroende är samma sak som en främmande nyckel.   X
Boyce-Codds normalform (BCNF) ställer fler krav på hur en tabell får se ut än andra normalformen, så det finns tabeller som uppfyller andra normalformen men inte BCNF. X  


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 18 januari 2017