Databasteknik: Lösningar till tentamen 2015-01-13

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. Dessutom har det inträffat i världshistorien att lärare skrivit fel i lösningsförslagen.

Uppgift 1 (3 p)

Enligt ER-diagrammet kan varje student bara ha betyg i en enda kurs.

När man fått betyg i en kurs, till exempel Datavetenskapens grunder, kan man aldrig mer få betyg i någon annan kurs, till exempel kursen Databasteknik, i alla fall inte utan att först ta bort det gamla betyget.

Att varje kurs måste ges av ett universitet. Det får alltså inte finnas "lösa" kurser som inte tillhör något universitet.

Uppgift 2 (7 p)

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

drop table LaserKurs;
drop table Studenter;
drop table Kurser;
drop table Telefonnummer;
drop table Universitet;

create table Universitet
(Nummer integer not null primary key,
Namn varchar(10) not null unique);

insert into Universitet (Nummer, Namn) values (1, 'Atle Uni');
insert into Universitet (Nummer, Namn) values (2, 'Berra U');
insert into Universitet (Nummer, Namn) values (3, 'C-uni');

create table Telefonnummer
(Universitet integer not null references Universitet(Nummer),
Telefon varchar(10) not null,
primary key (Universitet, Telefon));

insert into Telefonnummer (Universitet, Telefon) values (1, 'A-1111');
insert into Telefonnummer (Universitet, Telefon) values (1, 'A-2222');
insert into Telefonnummer (Universitet, Telefon) values (1, 'A-3333');
insert into Telefonnummer (Universitet, Telefon) values (3, 'C-1111');
insert into Telefonnummer (Universitet, Telefon) values (3, 'C-2222');

create table Kurser
(Kod varchar(6) not null primary key,
Namn varchar(10) not null,
GesAv integer references Universitet(Nummer));

insert into Kurser (Kod, Namn, GesAv) values ('AA-AF', 'Atomfysik', 1);
insert into Kurser (Kod, Namn, GesAv) values ('AA-AK', 'Atomkemi', 1);
insert into Kurser (Kod, Namn, GesAv) values ('AA-D', 'Databaser', 1);
insert into Kurser (Kod, Namn, GesAv) values ('BB-D', 'Databaser', 2);
insert into Kurser (Kod, Namn, GesAv) values ('CC-D', 'Databaser', 3);
insert into Kurser (Kod, Namn, GesAv) values ('CC-E', 'Ekonomi', 3);
insert into Kurser (Kod, Namn, GesAv) values ('CC-F', 'Fysik', 3);
insert into Kurser (Kod, Namn, GesAv) values ('CC-G', 'Geografi', 3);

create table Studenter
(Nummer integer not null primary key,
Personnummer varchar(11) not null unique,
Namn varchar(10) not null,
BetygIKurs varchar(6) references Kurser(Kod),
Betygsdatum date,
VilketBetyg varchar(2));

insert into Studenter (Nummer, Personnummer, Namn, BetygIKurs, Betygsdatum, VilketBetyg) values (1, '111111-1111', 'Anna', 'AA-AF', DATE'2014-01-10', 'G');
insert into Studenter (Nummer, Personnummer, Namn, BetygIKurs, Betygsdatum, VilketBetyg) values (2, '222222-2222', 'Bob', 'AA-AF', DATE'2014-02-16', 'G');
insert into Studenter (Nummer, Personnummer, Namn, BetygIKurs, Betygsdatum, VilketBetyg) values (3, '333333-3333', 'Charlie', null, null, null);
insert into Studenter (Nummer, Personnummer, Namn, BetygIKurs, Betygsdatum, VilketBetyg) values (4, '444444-4444', 'Doris', 'CC-D', DATE'2015-01-10', 'G');

create table LaserKurs
(Student integer not null references Studenter(Nummer),
Kurs varchar(6) not null references Kurser(Kod),
primary key (Student, Kurs));

insert into LaserKurs (Student, Kurs) values (1, 'AA-AF');
insert into LaserKurs (Student, Kurs) values (1, 'AA-AK');
insert into LaserKurs (Student, Kurs) values (1, 'AA-D');
insert into LaserKurs (Student, Kurs) values (1, 'BB-D');
insert into LaserKurs (Student, Kurs) values (1, 'CC-D');
insert into LaserKurs (Student, Kurs) values (1, 'CC-E');
insert into LaserKurs (Student, Kurs) values (1, 'CC-F');
insert into LaserKurs (Student, Kurs) values (1, 'CC-G');
insert into LaserKurs (Student, Kurs) values (2, 'AA-AF');
insert into LaserKurs (Student, Kurs) values (2, 'AA-AK');

select * from LaserKurs;
select * from Studenter;
select * from Kurser;
select * from Telefonnummer;
select * from Universitet;

Uppgift 3 (5 p)

a) Villkor på hur de data som lagras i databasen får se ut. Normalt menar man också att databashanteraren automatiskt ska upprätthålla dem.

b) För att motverka inre motsägelser och andra fel i databasen.

c) Tre olika typer av integritetsvillkor:

Referensintegritetsvillkor: Om det står i tabellen Kurser att en kurs ges av universitet nummer 3, så ska det också finnas ett universitet nummer 3 i tabellen Universitet.

Nyckelvillkor: Varje universitet ska ha ett unikt nummer som man kan identifiera det med, till exempel universitet nummer 3 ovan. Då får det inte finnas några dubbletter i kolumnen Nummer i tabellen Universitet,

Domänvillkor: Man kan inte ha vilka betyg som helst, som X7 eller Super-underkänd. Om vi antar att de enda betyg som är giltiga är G, VG, 3, 4 och 5, så får det inte finnas några andra värden än dessa i kolumnen VilketBetyg i tabellen Studenter.

Uppgift 4 (3 p)

Som exempel tar vi tabellen Studenter, som uppfyller BCNF.

Den är i 1NF eftersom den enbart innehåller enkla, atomära värden, om vi antar att man inte bryr sig om delar av de ingående strängarna, till exempel så att man söker efter vilka kurser som innehåller samma ord.
Den är i BCNF eftersom den dels uppfyller 1NF, och varje determinant är en kandidatnyckel. De fullständiga funktionella beroenden som finns i tabellen är från var och en av de två kandidatnycklarna Nummer och Personnummer till samtliga andra attribut, inklusive till den andra kandidatnyckeln. Det finns inga andra ffb i tabellen, och alltså är alla determinanter kandidatnycklar.

Uppgift 5 (10 p)

a) Vad heter de kurser som ges av Örebro universitet?

select Kurser.Namn
from Kurser, Universitet
where Kurser.GesAv = Universitet.Nummer
and Universitet.Namn = 'Örebro universitet';

select Namn
from Kurser
where GesAv in (select Nummer
                from Universitet
                where Namn = 'Örebro universitet');

select Kurser.Namn
from Kurser join Universitet on Kurser.GesAv = Universitet.Nummer
and Universitet.Namn = 'Örebro universitet';

b) Det finns bara en student i hela Sverige som heter Sven Duva. Vad heter de kurser som han läser just nu?

select Kurser.Namn
from Studenter, LaserKurs, Kurser
where Studenter.Namn = 'Sven Duva'
and Studenter.Nummer = LaserKurs.Student
and LaserKurs.Kurs = Kurser.Kod;

select Namn
from Kurser
where Kod in (select Kurs
              from LaserKurs
              where Student in (select Nummer
                               from Studenter
                               where Namn = 'Sven Duva'));

select Kurser.Namn
from Studenter join LaserKurs on Studenter.Nummer = LaserKurs.Student
    join Kurser on LaserKurs.Kurs = Kurser.Kod
where Studenter.Namn = 'Sven Duva';

c) Hur många studenter läser nu kursen Databaser som ges av Uppsala universitet?

select count(*)
from Studenter, LaserKurs, Kurser, Universitet
where Studenter.Nummer = LaserKurs.Student
and LaserKurs.Kurs = Kurser.Kod
and Kurser.GesAv = Universitet.Nummer
and Kurser.Namn = 'Databaser'
and Universitet.Namn = 'Uppsala universitet';

d) Vilka studenter läser just nu ingenting?

select * from Studenter where Nummer not in (select Student from LaserKurs);

select Studenter.*
from Studenter left join LaserKurs on Studenter.Nummer = LaserKurs.Student
where LaserKurs.Student is null;

select *
from Studenter
where (select count(*)
       from LaserKurs
       where LaserKurs.Student = Studenter.Nummer) = 0;

e) Vad är det populäraste kursnamnet? Dvs, vilket namn är det flest kurser som har?

create view AntalKurserMedSammaNamn as
select Namn, count(*) as Antal
from Kurser
group by Namn;

select Namn
from AntalKurserMedSammaNamn
where Antal = (select max(Antal) from AntalKurserMedSammaNamn);

Uppgift 6 (4 p)

a) Lämpliga databashanterare kan vara till exempel Oracle, DB2, Microsoft SQL Server, MySQL eller Mimer, men kanske inte Microsoft Access eller SQLite. Microsoft Access och SQLite är gjorda för mindre mängder data, och inte så många (samtidiga) användare.

b) Index som kan vara lämpliga:

create index Universitetsnummer on Universitet(Nummer); -- men liten tabell, så kanske inte
create index Universitetsnamn on Universitet(Namn); -- men liten tabell, så kanske inte

create index KursGesAv on Kurser(GesAv);

create index Studentnummer on Studenter(Nummer);
create index Studentnamn on Studenter(Namn);

create index StudentLaser on LaserKurs(Student);
create index KursLaser on LaserKurs(Kurs);

create index Kurskod on Kurser(Kod);

Uppgift 7 (4 p)

Atomicitet, dvs att antingen görs alla transaktionens ändringar i databasen, eller inga.

Annars skulle halvfärdiga förändringar kunna ligga kvar i databasen, till exempel om en student klagat på rättningen av en tenta och fått betyget ändrat från 3 till 5. Vi tar bort betyget 3 ur databasen, men sen går strömmen och vi hinner aldrig lägga in betyget 5. Nu är studenten inte alls godkänd på kursen. Det skulle många studenter uppleva som dåligt.

Consistency preserving, dvs att om databasen var i ett konsistent tillstånd (utan motsägelser, eller utan brott mot integritetsvillkoren) före transaktionen, så ska den också vara i ett konsistent tillstånd efter transaktionen.

Annars kan databasen (förstås!) bli inkonsistent, till exempel genom att man tar bort universitet nummer 3, trots att det finns kurser i databasen som det står ges av just universitet nummer 3.

Isolering, att två eller flera samtidiga transaktioner inte får "störa" varandra, genom att till exempel läsa varandras halvfärdiga data, eller skriva över varandras pågående ändringar. Idealfallet, med bäst isolering, är att varje transaktion ska köras på ett sätt som är likvärdigt med att den är den enda samtidigt pågående transaktionen i databasen.

Annars kan många olämpliga saker hända, till exempel att man gör ändringen från atomicitetsexemplet ovan, men precis när man tagit bort betyget 3 på kursen så gör någon annan ett betygsutdrag för den studenten, och i det betygsutdraget står det då inget om att studenten alls är godkänd på kursen. Det skulle många studenter uppleva som dåligt.

Durability, att när transaktionen committats så får de ändringar den gjort i databasen inte försvinna.

Annars skulle man kunna göra en förändring i databasen, till exempel lägga in ett godkänt betyg i en kurs, och sen blir det strömavbrott och det betyget försvinner. Det skulle många studenter uppleva som dåligt.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 15 januari 2015