Databaskonstruktion: Lösningar till tentamen 2005-12-10

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 (10 p)

Man kan också skapa en ny nyckel i Tenta, så den slipper vara en svag entitetstyp.

Ett ER-diagram

Uppgift 2 (10 p)

Primärnycklarna är understrukna:

Lärare(Pnr, Namn, TillhörInstitution)
Institution(Id, Namn)
Kurs(Kod, Namn, GesAvInstitution, AnsvarigLärare)
Tenta(Id, TillhörKurs, Datum, AnsvarigLärare)
Student(Pnr, Namn)
Läser(Student, Kurs)
Skriver(Student, Tenta, Betyg)

Referensattribut:

Lärare.TillhörInstitution till Institution.Id
Kurs.GesAvInstitution till Institution.Id
Kurs.AnsvarigLärare till Lärare.Pnr
Tenta.TillhörKurs till Kurs.Kod
Tenta.AnsvarigLärare till Lärare.Pnr
Läser.Student till Student.Pnr
Läser.Kurs till Kurs.Kod
Skriver.Student till Student.Pnr
Skriver.Tenta till Tenta.Id

Vi har infört två databasinterna nycklar för att underlätta arbetet med databasen:

Institution.Id, eftersom tabellen Institution annars får en lång textsträng som nyckel, och den ska refereras från två andra tabeller
Tenta.Id, eftersom tabellen Tenta annars får en sammansatt nyckel, och den ska refereras från en annan tabell

Det är inte fel, och kan till och med vara bra, att införa den sortens databasinterna nycklar i alla tabeller.

SQL-kommandon för att provköra:

create table Institution
(Id integer not null primary key,
Namn char(6) not null unique);

create table Larare
(Pnr char(11) not null primary key,
Namn char(6) not null,
TillhorInstitution integer not null references Institution(Id));

create table Kurs
(Kod char(6) not null primary key,
Namn char(6) not null,
GesAvInstitution integer not null references Institution(Id),
AnsvarigLarare char(11) not null references Larare(Pnr));

create table Tenta
(Id integer not null primary key,
TillhorKurs char(6) not null references Kurs(Kod),
Datum char(10) not null,
AnsvarigLarare char(11) not null references Larare(Pnr),
unique (TillhorKurs, Datum));

create table Student
(Pnr char(11) not null primary key,
Namn char(6) not null);

create table Laser
(Student char(11) not null references Student(Pnr),
Kurs char(6) not null references Kurs(Kod),
primary key (Student, Kurs));

create table Skriver
(Student char(11) not null references Student(Pnr),
Tenta integer not null references Tenta(Id),
Betyg char(1) not null,
primary key (Student, Tenta));

insert into Institution values (1, 'Teknik');
insert into Institution values (2, 'Hum');
insert into Institution values (3, 'Vård');
insert into Institution values (4, 'Idrott');

insert into Larare values ('111111-1111', 'Anna', 1);
insert into Larare values ('222222-2222', 'Bodil', 1);
insert into Larare values ('333333-3333', 'Ceci', 1);
insert into Larare values ('444444-4444', 'Donna', 2);
insert into Larare values ('555555-5555', 'Elin', 3);

insert into Kurs values ('T1', 'Data', 1, '111111-1111');
insert into Kurs values ('T2', 'El', 1, '222222-2222');
insert into Kurs values ('T3', 'Regler', 1, '222222-2222');
insert into Kurs values ('T4', 'Hållf', 1, '333333-3333');
insert into Kurs values ('H1', 'Flum', 2, '444444-4444');

insert into Tenta values (1, 'T1', '2005-01-01', '111111-1111');
insert into Tenta values (2, 'T1', '2005-02-02', '111111-1111');
insert into Tenta values (3, 'T1', '2005-03-03', '222222-2222');
insert into Tenta values (4, 'T2', '2005-01-01', '222222-2222');
insert into Tenta values (5, 'T3', '2005-01-01', '222222-2222');
insert into Tenta values (6, 'T4', '2005-01-01', '333333-3333');
insert into Tenta values (7, 'H1', '2005-01-01', '444444-4444');

insert into Student values ('666666-6666', 'Filip');
insert into Student values ('777777-7777', 'Gunnar');
insert into Student values ('888888-8888', 'Helge');
insert into Student values ('999999-9999', 'Ivar');

insert into Laser values ('666666-6666', 'T1');
insert into Laser values ('666666-6666', 'T2');
insert into Laser values ('777777-7777', 'T1');

insert into Skriver values ('666666-6666', 1, 'U');
insert into Skriver values ('666666-6666', 2, '5');
insert into Skriver values ('666666-6666', 4, '3');
insert into Skriver values ('777777-7777', 1, '4');

Uppgift 3 (14 p)

a (1p)

select Namn
from Kurs
where Namn like '%programmering%';

b (1p)

select Institution.Namn
from Institution, Kurs
where Institution.Id = Kurs.GesAvInstitution
and Kurs.Namn = 'Databasdestruktion';

Alternativt:

select Namn
from Institution
where Id in (select GesAvInstitution
             from Kurs
             where Namn = 'Databasdestruktion');

c (1p)

select Namn
from Institution
where Id not in (select GesAvInstitution
                 from Kurs);

d (1p)

select count(*)
from Kurs, Institution
where Kurs.GesAvInstitution = Institution.Id
and Institution.Namn = 'Institutionen för teknik';

Alternativt:

select count(*)
from Kurs
where GesAvInstitution in (select Id
                           from Institution
                           where Namn = 'Institutionen för teknik');

e (2p)

select Kurs.Namn, Tenta.Datum
from Kurs, Tenta
where Kurs.Kod = Tenta.TillhorKurs
and Kurs.AnsvarigLarare <> Tenta.AnsvarigLarare;

f (4p)

create view KurserPerLarare(Pnr, Antal)
as select AnsvarigLarare, count(*)
from Kurs, Larare, Institution
where Kurs.AnsvarigLarare = Larare.Pnr
and Larare.TillhorInstitution = Institution.Id
and Institution.Namn = 'Teknik'
group by AnsvarigLarare;

select Namn
from Larare
where Pnr in (select Pnr
              from KurserPerLarare
              where Antal in (select max(Antal)
                              from KurserPerLarare));

Alternativt:

create view Tekniklarare
as select Pnr, Larare.Namn
from Larare, Institution
where Larare.TillhorInstitution = Institution.Id
and Institution.Namn = 'Teknik';

create view KurserPerTekniklarare(Pnr, Antal)
as select AnsvarigLarare, count(*)
from Kurs, Tekniklarare
where Kurs.AnsvarigLarare = Tekniklarare.Pnr
group by AnsvarigLarare;

select Namn
from Tekniklarare
where Pnr in (select Pnr
              from KurserPerTekniklarare
              where Antal in (select max(Antal)
                              from KurserPerTekniklarare));

g (4p)

create view KurserPerInstitution(Pnr, Antal)
as select Institution.Id, count(Kurs.Kod)
from Institution left outer join Kurs on Institution.Id = Kurs.GesAvInstitution
group by Institution.Id;

select avg(Antal)
from KurserPerInstitution;

Alternativt:

create view AntalKurser
as select count(*) as Antal
from Kurs;                 

create view AntalInstitutioner
as select count(*) as Antal
from Institution;

select AntalKurser.Antal / AntalInstitutioner.Antal
from AntalKurser, AntalInstitutioner;

Alternativt:

select distinct
(select count(*) from Kurs) / (select count(*) from Institution)
from Kurs;

Uppgift 4 (4 p)

Skapa index på:

Kurs.Kod
Kurs.AnsvarigLarare
Tenta.TillhorKurs
Tenta.AnsvarigLarare

Dessa är de kolumner som används i "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.

Man ska inte skapa index på:

Kurs.Namn
Tenta.Datum

Uppgift 5 (5 p)

a) (2p)

Tabellen uppfyller bara första normalformen.

Tabellen innehåller bara enkla, atomära värden, så den uppfyller 1NF. Däremot uppfyller den inte 2NF. Tabellen har en enda kandidatnyckel, nämligen kombinationen Kurskod + Datum + Student. Kolumnen Kursnamn är fullständigt funktionellt beroende av Kurskod, och 2NF säger att ingen kolumn utanför kandidatnycklarna får vara ffb bara på en del av en kandidatnyckel. (Annorlunda uttryckt: Varje icke-nyckel-attribut måste vara fullständigt funktionellt beroende av varje kandidatnyckel.)

b) (3p)

Tre problem:

Redundans: För varje student som skrivit en tenta i en viss kurs, måste man upprepa den kursens namn.
Man kan inte lagra en tenta som gavs, men som ingen student skrev.
Om det här är det enda stället vi lagrar kursnamnen, så kan man inte lagra kursnamnet för en kurs som ingen student har tentat.
Det är också lite otydligt vad tabellen egentligen betyder. Handlar den om tentorna, eller om vilka studenter som skrivit tentorna?

Uppgift 6 (4 p)

Primärnycklarna är understrukna:

Person(Pnr, Namn)
Student(Pnr)
Lärare(Pnr)
VerksamLärare(Pnr, Lön)
PensioneradLärare(Pnr)

Referensattribut:

Student.Pnr till Person.Pnr
Lärare.Pnr till Person.Pnr
VerksamLärare.Pnr till Lärare.Pnr
PensioneradLärare.Pnr till Lärare.Pnr

Uppgift 7 (3 p)

Här kan man förstås göra en mängd olika jämförelser. Exempel på vad man kan svara:

MySQL är ett klient-server-system, med en databasserver som är igång hela tiden och som klientprogrammen kopplar upp sig mot, via ett datornät. Access är bara ett program som arbetar direkt med en fil på disken.
Access har ett lättanvänt grafiskt gränssnitt, med guider som underlättar arbetet. Det finns grafiska program även för MySQL, men inte alls lika avancerade och lättanvända.
MySQL är tänkt för, och effektiv när den arbetar med, stora mängder data. Access är i första hand tänkt för mindre databaser.
Den senaste versionen av MySQL, 5.0, har en mer avancerad SQL-dialekt än Access. Den innehåller till exempel triggers och lagrade procedurer.
MySQL är gratis, men Access kostar pengar.
MySQL är har öppen källkod, men Access har sluten källkod.
MySQL kan användas under en mängd olika operativsystem, men Access enbart under (olika sorters) Windows.
Access är egentligen inte en databashanterare, utan ett grafiskt gränssnitt som ligger som ett skal runt en databashanterare som heter Jet.

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