Databaskonstruktion: Lösningar till tentamen 2006-12-09

Observera att detta är förslag på lösningar. Det kan finnas andra lösningar som också är korrekta, och 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. En del av lösningarna är kanske inte fullständiga, utan hänvisar bara till var man kan läsa svaret.

Uppgift 1 (8 p)

Tabeller, med primärnycklarna är understrukna:

avdelningar(nummer, namn)
personal(nummer, namn, typ)
patienter(nummer, namn, avdelning)
sjukdomar(nummer, namn)
arbetar(nummer, personal, avdelning)
diagnoser(nummer, patient, sjukdom)

Referensattribut:

patienter.avdelning till avdelningar.nummer
arbetar.personal till personal.nummer
arbetar.avdelning till avdelningar.nummer
diagnos.patient till patienter.nummer
diagnos.sjukdom till sjukdomar.nummer

För att förenkla programmeringen av tillämpningsprogrammen har vi infört enkla numeriska primärnycklar som databasinterna "radnummer" i tabellerna arbetar och diagnos. Alternativt kunde man använt kombinationen av personal och avdelning som primärnyckel i tabellen arbetar, och kombinationen av patient och sjukdom som primärnyckel i tabellen diagnoser. I lösningen ovan är de i stället alternativnycklar.

Alla entitetstyperna hade redan enkla numeriska nycklar, så vi inför inga särskilda databasinterna nycklar för dem. (Vi antar då att numren på patienter och personal inte är vanliga svenska personnummer, som normalt måste lagras som textsträngar och inte som tal.)

SQL-kommandon för att provköra:

create unique sequence avdelningsnummer;

create table avdelningar
(nummer integer default next_value of avdelningsnummer not null primary key,
namn char(10) unique);

create unique sequence personalnummer;

create table personal
(nummer integer default next_value of personalnummer not null primary key,
namn char(10),
typ char(10));

create unique sequence arbetsnummer;

create table arbetar
(nummer integer default next_value of arbetsnummer not null primary key,
personal integer references personal(nummer),
avdelning integer references avdelningar(nummer),
unique(personal, avdelning));

create unique sequence patientnummer;

create table patienter
(nummer integer default next_value of patientnummer not null primary key,
namn char(10),
avdelning integer references avdelning(nummer));

create unique sequence sjukdomsnummer;

create table sjukdomar
(nummer integer default next_value of sjukdomsnummer not null primary key,
namn char(10) unique);

create unique sequence diagnosnummer;

create table diagnoser
(nummer integer default next_value of diagnosnummer not null primary key,
patient integer references patienter(nummer),
sjukdom integer references sjukdomar(nummer),
unique(patient, sjukdom));

insert into avdelningar(namn) values('IVA');
insert into avdelningar(namn) values('Medicin');
insert into avdelningar(namn) values('Ortopedi');
insert into avdelningar(namn) values('Kirurgi');

insert into personal(namn, typ) values('Anna Ek', 'läkare');
insert into personal(namn, typ) values('Bo Ek', 'ssk');
insert into personal(namn, typ) values('Cesar Ek', 'tekniker');
insert into personal(namn, typ) values('Donna Ek', 'läkare');
insert into personal(namn, typ) values('Erik Ek', 'usk');
insert into personal(namn, typ) values('Filip Ek', 'städare');
insert into personal(namn, typ) values('Gunilla Ek', 'väktare');
insert into personal(namn, typ) values('Hans Ek', 'läkare');
insert into personal(namn, typ) values('Inga Ek', 'läkare');
insert into personal(namn, typ) values('Jörn Ek', 'läkare');

insert into arbetar(personal, avdelning) values (1, 3);
insert into arbetar(personal, avdelning) values (2, 3);
insert into arbetar(personal, avdelning) values (3, 3);
insert into arbetar(personal, avdelning) values (4, 3);
insert into arbetar(personal, avdelning) values (5, 3);
insert into arbetar(personal, avdelning) values (6, 3);
insert into arbetar(personal, avdelning) values (7, 3);
insert into arbetar(personal, avdelning) values (8, 3);
insert into arbetar(personal, avdelning) values (9, 3);
insert into arbetar(personal, avdelning) values (1, 4);
insert into arbetar(personal, avdelning) values (2, 4);
insert into arbetar(personal, avdelning) values (3, 4);
insert into arbetar(personal, avdelning) values (4, 4);

insert into patienter(namn, avdelning) values('Anna Berg', null);
insert into patienter(namn, avdelning) values('Bo Berg', 1);
insert into patienter(namn, avdelning) values('Cesar Berg', 2);
insert into patienter(namn, avdelning) values('Donna Berg', 3);
insert into patienter(namn, avdelning) values('Erik Berg', null);
insert into patienter(namn, avdelning) values('Filip Berg', 1);
insert into patienter(namn, avdelning) values('Gun Berg', 1);
insert into patienter(namn, avdelning) values('Hans Berg', 1);
insert into patienter(namn, avdelning) values('Inga Berg', 2);
insert into patienter(namn, avdelning) values('Jörn Berg', 3);

insert into sjukdomar(namn) values('värk');
insert into sjukdomar(namn) values('smärtor');
insert into sjukdomar(namn) values('galenskap');
insert into sjukdomar(namn) values('pest');
insert into sjukdomar(namn) values('kolera');

insert into diagnoser(patient, sjukdom) values (1, 5);
insert into diagnoser(patient, sjukdom) values (2, 5);
insert into diagnoser(patient, sjukdom) values (3, 5);
insert into diagnoser(patient, sjukdom) values (4, 5);
insert into diagnoser(patient, sjukdom) values (5, 5);
insert into diagnoser(patient, sjukdom) values (6, 5);
insert into diagnoser(patient, sjukdom) values (7, 5);
insert into diagnoser(patient, sjukdom) values (1, 1);
insert into diagnoser(patient, sjukdom) values (2, 1);
insert into diagnoser(patient, sjukdom) values (3, 1);
insert into diagnoser(patient, sjukdom) values (4, 1);
insert into diagnoser(patient, sjukdom) values (1, 4);
insert into diagnoser(patient, sjukdom) values (2, 4);
insert into diagnoser(patient, sjukdom) values (3, 4);
insert into diagnoser(patient, sjukdom) values (6, 4);
insert into diagnoser(patient, sjukdom) values (7, 4);

select * from avdelningar;
select * from personal;
select * from arbetar;
select * from patienter;
select * from sjukdomar;
select * from diagnoser;

Uppgift 2 (12 p)

a) (1p) Vilka patienter finns på ortopedavdelningen, alltså avdelningen som heter "Ortopedi"?

select patienter.nummer, patienter.namn
from patienter, avdelningar
where patienter.avdelning = avdelningar.nummer
and avdelningar.namn = 'Ortopedi';

En alternativ lösning:

select nummer, namn
from patienter
where avdelning in (select nummer
                    from avdelningar  
                    where namn = 'Ortopedi');

b) (2p) Vilka patienter har pest?

select patienter.nummer, patienter.namn
from patienter, diagnoser, sjukdomar
where patienter.nummer = diagnoser.patient
and diagnoser.sjukdom = sjukdomar.nummer
and sjukdomar.namn = 'pest';

En alternativ lösning:

select nummer, namn
from patienter
where nummer in (select patient
                 from diagnoser  
                 where sjukdom in (select nummer
                                   from sjukdomar
                                   where namn = 'pest'));

c) (1p) Vilka patienter vårdas inte på någon avdelning?

select nummer, namn
from patienter
where avdelning is null;

Notera att följande fråga (som också skulle kunna utläsas "vilka patienter vårdas på en avdelning som inte finns") ger fel svar, eftersom alla jämförelser med null (utom is null) blir falska:

select nummer, namn
from patienter
where avdelning not in (select nummer from avdelning);

d) (1p) Vilka patienter har (såvitt vi vet) inga sjukdomar?

select nummer, namn
from patienter
where nummer not in (select patient from diagnoser);

e) (1p) Hur många personal av varje typ (som till exempel läkare, sjuksköterskor och tekniker) finns det?

select typ, count(*)
from personal
group by typ;

f) (3p) Vilken sjukdom är vanligast?

create view antal_diagnoser as
select sjukdomar.nummer, sjukdomar.namn, count(*) as antal
from sjukdomar, diagnoser
where sjukdomar.nummer = diagnoser.sjukdom
group by sjukdomar.nummer, sjukdomar.namn;

select * from antal_diagnoser;

select namn
from antal_diagnoser
where antal = (select max(antal)
               from antal_diagnoser);

g) (3p) Pest är smittsamt! Vilka patienter har inte pest (än), men vårdas på samma avdelning som någon patient som har pest?

create view patienter_med_pest as
select diagnoser.patient as nummer
from diagnoser, sjukdomar
where diagnoser.sjukdom = sjukdomar.nummer
and sjukdomar.namn = 'pest';

create view avdelningar_med_pest as
select avdelning as nummer
from patienter
where nummer in (select nummer from patienter_med_pest);

select distinct nummer, namn
from patienter
where nummer not in (select nummer from patienter_med_pest)
and avdelning in (select nummer from avdelningar_med_pest);

Uppgift 3 (5 p)

a) (4p)

Skapa index på:

patienter.avdelning
patienter.nummer
diagnoser.patient
diagnoser.sjukdom
sjukdomar.nummer
sjukdomar.namn

Dessa är de kolumner som används i de aktuella frågorna för det som internt i databashanteraren kallas "select"- och "join"-operationer, alltså för att välja ut rader i en tabell eller för att koppla ihop rader i två tabeller.

Några kolumner som man antagligen inte bör skapa index på:

patienter.namn (används inte för att välja ut eller koppla ihop rader)
avdelningar.namn (tabellen är för liten)
avdelningar.nummer (tabellen är för liten)
personal.nummer (nämns överhuvudtaget inte i dessa frågor)

SQL-kommandon för att provköra:

create index pavdelning on patienter(avdelning);
create index pnummer on patienter(nummer);
create index dpatient on diagnoser(patient);
create index dsjukdom on diagnoser(sjukdom);
create index snummer on sjukdomar(nummer);
create index snamn on sjukdomar(namn);

b) (1p)

Ett index är en datastruktur som gör att det går snabbt att hitta rader i en tabell om man känner till värdet på den indexerade kolumnen.

Uppgift 4 (5 p)

a) (2p)

Tabeller, med primärnycklarna är understrukna:

personaltyp(nummer, namn)
personal(nummer, namn, typ)

Referensattribut:

personal.typ till personaltyp.nummer

SQL-kommandon för att provköra:

create unique sequence personaltypsnummer;

create table personaltyp
(nummer integer default next_value of personaltypsnummer not null primary key,
namn char(10) unique);

create unique sequence personal2nummer;

create table personal2
(nummer integer default next_value of personal2nummer not null primary key,
namn char(10),
typ integer not null references personaltyp(nummer));

insert into personaltyp(namn) values('läkare');
insert into personaltyp(namn) values('ssk');
insert into personaltyp(namn) values('usk');
insert into personaltyp(namn) values('tekniker');
insert into personaltyp(namn) values('städare');
insert into personaltyp(namn) values('väktare');
insert into personaltyp(namn) values('fältskär');
insert into personaltyp(namn) values('homeopat');

insert into personal2(namn, typ) values('Anna Ek', 1);
insert into personal2(namn, typ) values('Bo Ek', 2);
insert into personal2(namn, typ) values('Cesar Ek', 4);
insert into personal2(namn, typ) values('Donna Ek', 1);
insert into personal2(namn, typ) values('Erik Ek', 3);
insert into personal2(namn, typ) values('Filip Ek', 5);
insert into personal2(namn, typ) values('Gunilla Ek', 6);
insert into personal2(namn, typ) values('Hans Ek', 1);
insert into personal2(namn, typ) values('Inga Ek', 1);
insert into personal2(namn, typ) values('Jörn Ek', 1);

b) (3p)

select personaltyp.namn, count(personal2.nummer)
from personaltyp left outer join personal2 on personaltyp.nummer = personal2.typ
group by personaltyp.namn;

Kommentar: coalesce behövs inte. count ger noll och inte null om den inte hittar några rader.

Uppgift 5 (5 p)

a) (2p)

Tabellen uppfyller 1NF och 2NF, men inte 3NF och BCNF.

Tabellen innehåller bara enkla, atomära värden, så den uppfyller 1NF.
Den har inga sammansatta kandidatnycklar, och måste därför uppfylla 2NF.
Det finns ett transitivt (fullständigt funktionellt) beroende från primärnyckeln Personalnummer till icke-nyckel-attributet Typnummer och sen vidare till icke-nyckel-attributet Typnamn, och den sortens transitiva beroenden är inte tillåtna i 3NF.
BCNF är ett starkare krav än 3NF, och eftersom 3NF inte är uppfylld så är inte heller BCNF uppfylld.

b) (2p)

Tre problem:

Redundans: För varje person som tillhör en personaltyp, måste man upprepa all information om personaltypen.
Det blir svårt att lagra personaltyper som det inte finna någon personal som tillhör. Notera att det står null i nyckelkolumnen Personalnummer, och det får man inte ha i en relationsdatabas.
Det är också lite otydligt vad tabellen egentligen betyder. Handlar den om personal eller personaltyper? Beskriver kolumnen Antal hur många Anna Ek som det finns, eller hur många läkare?

c) (1p)

Ett fullständigt funktionellt beroende är ett funktionellt beroende där determinanten är minimal, dvs där det inte går att ta bort några kolumner ur determinanten om det fortfarande ska vara ett funktionellt beroende. Exempel:

Typnummer bestämmer Typnamn, och detta funktionella beroende är ett fullständigt funktionellt beroende, för man kan inte ta bort några kolumner ur determinanten (Typnummer). (Den har ju bara en kolumn, så det går förstås inte!)
Typnummer och Personalnamn bestämmer tillsammans Typnamn, och detta funktionella beroende är inte ett fullständigt funktionellt beroende, för man kan ju ta bort Personalnamn ur determinanten. (Det räckte ju, sa vi nyss, med Typnummer för att bestämma Typnamn.)

Uppgift 6 (5 p)

a) XML är ett sätt att göra databasen mer aktiv, genom att man skriver XML-kod som sen körs automatiskt när vissa villkor är uppfyllda i databasen.

Fel. Det där handlar nog om triggers, snarare än XML. XML är ett sätt att skapa HTML-liknande språk för att lagra och överföra data.

b) Microsoft Access är egentligen ingen riktig databashanterare, utan ett grafiskt verktyg byggt runt Microsofts riktiga databashanterare, som heter SQL Server.

(Delvis) fel. Microsoft Access är ett grafiskt verktyg byggt runt en "riktig" databashanterar, men även om man kan använda Microsoft Access för att jobba mot en SQL Server-databas, så lagras vanliga Access-databaser med en databashanterare som heter Microsoft Jet.

c) På webben är det vanligt att man låter en webbplats lagra data i en databas. Ofta används MySQL som databashanterare.

Sant.

d) ACID-transaktioner är ett sätt att skydda Internet-banker mot bedrägeriförsök.

Fel. ACID-transaktioner är ett sätt att gruppera flera relaterade operationer (till exempel select- och update-kommandon i SQL) till en logisk enhet, som databashanteraren sen ser till att den bland annat blir atomär, vilket betyder att antingen genomförs samtliga operationer i transaktionen eller också ingen.

e) Databaser används sällan i tekniska tillämpningar som styrsystem och programmering, utan nästan enbart för administrativa ändamål som bokföring, personalregister och liknande.

Fel. Databaser används i alla möjliga tillämpningar, även tekniska, även om det kanske är vanligast med administrativa tillämpningar.

Uppgift 7 (5 p)

Schema är en beskrivning av strukturen på en databas, medan data är de uppgifter som man lagrat i databasen. Schemat beskriver alltså vilka data som går att lagra i databasen, hur de får se ut, och hur de ska struktureras och hänga ihop.

Grant och revoke är två kommandon som används (normalt av databasadministratören) för att ange vad olika användare får göra med de data som finns i databasen. Med grant delar man ut rättigheter, och med revoke tar man bort dem igen.

Man kan beskriva databasen på en fysisk och en logisk nivå, till exempel med ett fysiskt och ett logiskt schema. Det fysiska schemat beskriver hur databasen lagras, med lagringsstrukturer (kanske ända ner på bit- och byte-nivå), medan det logiska schemat beskriver databasen på en högre nivå, till exempel med vilka tabeller och kolumner som finns.

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 27 december 2006