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:

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

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å: 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å:

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:

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:

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:


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