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

Uppgift 1 (5 p)

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)

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)

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)

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';

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';

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'));

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'));

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';

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');

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';

Uppgift 5 (3 p)

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?

• Sjukdomar.Namn
• Symtom.Sjukdom
• Sjukdomar.ID
• Symtom.Symtom

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!

• Patienter.ID -- förekommer överhuvudtaget inte i frågorna

Uppgift 7 (3 p)

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)