Databaskonstruktion: Lösningar till tentamen 2006-03-13

Uppgift 1 (5 p)

För att underlätta det fortsatta arbetet med databasen har jag också skapat id-nummer för de övriga entitetstyperna. Det är bra att göra så, eftersom entitetstyperna Filosof och Skola annars får långa textfält som nyckel, men det går också lika bra att skapa dessa id-nummer i samband med att man gör översättningen till tabeller.

Uppgift 2 (5 p)

Filosof(Id, Namn, Född, Död, Nationalitet, TillhörSkola)
Skola(Id, Namn)
Bok(Id, Namn, Utgivningsår)
Skrivit(Författare, Bok)
Påverkat(PåvrkandeFilosof, PåverkadFilosof)

Referensattribut:

• Filosof.TillhörSkola till Skola.Id
• Skrivit.Författare till Filosof.Id
• Skrivit.Bok till Bok.Id
• Påverkat.PåverkandeFilosof till Filosof.Id
• Påverkat.PåverkadFilosof till Filosof.Id

Det kan vara praktiskt att införa databasinterna id-nummer även i tabellerna Skrivit och Påverkat.

SQL-kommandon för att provköra i Mimer:

create unique sequence SkolIdSekvens;

create table Skola
(Id integer not null primary key default next_value of SkolIdSekvens,
Namn char(6) not null unique);

create unique sequence FilosofIdSekvens;

create table Filosof
(Id integer not null primary key default next_value of FilosofIdSekvens,
Namn char(6) not null unique,
Fodd integer not null,
Dod integer,
TillhorSkola integer not null references Skola(Id));
-- Jag glömde visst nationaliteten.

create unique sequence BokIdSekvens;

create table Bok
(Id integer not null primary key default next_value of BokIdSekvens,
Namn char(6) not null,
Utgivningsar integer not null);

create unique sequence SkrivitIdSekvens;

create table Skrivit
(Id integer not null primary key default next_value of SkrivitIdSekvens,
Forfattare integer not null references Filosof(Id),
Bok integer not null references Bok(Id),
unique (Forfattare, Bok));

create unique sequence PaverkatIdSekvens;

create table Paverkat
(Id integer not null primary key default next_value of PaverkatIdSekvens,
PaverkandeFilosof integer not null references Filosof(Id),
PaverkadFilosof integer not null references Filosof(Id),
unique (PaverkandeFilosof, PaverkadFilosof));

insert into Skola (Namn) values ('Pomo');
insert into Skola (Namn) values ('Rätt');
insert into Skola (Namn) values ('Fel');
insert into Filosof (Namn, Fodd, Dod, TillhorSkola)
values ('Bert', 1872, 1970, 2);
insert into Filosof (Namn, Fodd, TillhorSkola) values ('Leif', 1965, 1);
insert into Filosof (Namn, Fodd, TillhorSkola) values ('Tommy', 1964, 1);
insert into Bok (Namn, Utgivningsar) values ('TLF', 1921);
insert into Bok (Namn, Utgivningsar) values ('KLF', 1986);
insert into Skrivit (Forfattare, Bok) values (1, 1);
insert into Skrivit (Forfattare, Bok) values (2, 1);
insert into Skrivit (Forfattare, Bok) values (2, 2);
insert into Paverkat (PaverkandeFilosof, PaverkadFilosof) values (1, 2);
insert into Paverkat (PaverkandeFilosof, PaverkadFilosof) values (1, 3);

drop table filosof cascade;
drop table skola cascade;  
drop table bok cascade;   
drop table paverkat cascade;
drop table skrivit cascade; 
drop sequence SkolIdSekvens;
drop sequence filosofIdSekvens;
drop sequence bokIdSekvens;    
drop sequence paverkatIdSekvens;
drop sequence skrivitIdSekvens; 

Uppgift 3 (10 p)

select Filosof.Namn
from Filosof, Skola
where Filosof.TillhorSkola = Skola.Id
and Skola.Namn = 'Postmodernism';

b (1p)

select Bok.Id, Bok.Namn
from Filosof, Skrivit, Bok
where Filosof.Namn = 'Bertrand Russell'
and Filosof.Id = Skrivit.Forfattare
and Skrivit.Bok = Bok.Id;

c (1p)

select Namn
from Filosof
where Id in (select PaverkandeFilosof from Paverkat);

d (1p)

select Namn
from Filosof
where Id not in (select PaverkandeFilosof from Paverkat);

e (2p)

select master.Namn, student.Namn
from Filosof as master, Filosof as student, Paverkat
where master.Id = Paverkat.PaverkandeFilosof
and student.Id = Paverkat.PaverkadFilosof
and master.Fodd > student.Dod;

f (2p)

select Skola.Namn, count(*)
from Skola, Filosof
where Skola.Id = Filosof.TillhorSkola
group by Skola.Namn;

Det stod inget om att skolor som inte innehåller några filosofer alls måste vara med. I så fall skulle man behöva använda en yttre join:

select Skola.Namn, count(Filosof.Id) as AntalFilosofer
from Skola left outer join Filosof on Skola.Id = Filosof.TillhorSkola
group by Skola.Namn;

g (4p)

create view AntalPaverkade(PaverkandeFilosof, Antal)
as select PaverkandeFilosof, count(*)
from Paverkat
group by PaverkandeFilosof;

select Namn
from Filosof
where Id in (select PaverkandeFilosof
             from AntalPaverkade
             where Antal = (select max(Antal)
                           from AntalPaverkade));

Vi förutsätter att åtminstone någon filosof påverkat någon annan, så att det största antalet påverkningar inte är noll. Annars hade det behövts en yttre join i vyn för att få med någon filosof i svaret.

Uppgift 4 (5 p)

Skapa index på:

• Filosof.TillhorSkola
• Skola.Id
• Skola.Namn
• Filosof.Namn
• Filosof.Id
• Skrivit.Forfattare
• Skrivit.Bok
• Bok.Id
• Paverkat.PaverkandeFilosof

Fel svar:

• Bok.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 5 (5 p)

Fråga 2:

Fråga 3:

Fråga 4:

Fråga 5:

Uppgift 6 (5 p)

• Översta, eller yttersta nivån: Det externa schemat. Egentligen flera olika schema. Beskriver de vyer som de olika användarna ser databasen igenom.
• Mellannivån: Det logiska schemat. Beskriver databasens logiska uppbyggnad. Om det är en relationsdatabas, så beskriver det logiska schemat alla de tabeller (men inte vyer) som databasen innehåller.
• Nedersta, eller innersta nivån: Det interna eller fysiska schemat. Beskriver hur databasen lagras, med lagringsstrukturer, sorteringsordningar och index.

b) 2p)

För att separera nivåerna från varandra, så att man kan göra ändringar på en nivå utan att de andra nivåerna påverkas:

• Att den innersta, fysiska nivån har ett eget schema som är separerat från det logiska schemat, betyder att man kan ändra lagringsstrukturer och index utan att det påverkar hur tabellerna ser ut. Detta kallas fysiskt dataoberoende.
• Att den logiska nivån har ett eget schema, som är separerat från det externa schemat, betyder att man kan ändra vilka tabeller som finns, och deras kolumner, utan att det påverkar vad användarna (egentligen: applikationsprogrammen) ser av databasen. Detta kallas logiskt dataoberoende.

Uppgift 7 (5 p)

Första normalformen föreskriver enkla, odelbara värden, eller annorlunda uttryckt "högst ett värde i varje ruta". Här har jag stoppat in en hel lista med värden i samma ruta.

b) (1p)

Ingen av dessa. De höge normalformerna lägger var och en på ytterligare krav, som tabellen måste uppfylla. 2NF förutsätter 1NF, och lägger sen på ytterligare krav. 3NF förutsätter 2NF, och lägger sen på ytterligare krav. BCNF förutsätter 3NF, och lägger sen på ytterligare krav.

c) (2p)

Primärnycklarna är understrukna:

• Program(Id, Kanal, Namn, Viktighet)
• Visning(Program, Tid)

Referensattribut:

• Visning.Program refererar till Program.Id

Med exempeldata

d) (1p)

Båda tabellerna innehåller nu endast enkla, atomära värden. Alla fullständiga funktionella beroenden går från en kandidatnyckel (Id -> Kanal, Id -> Namn, Id -> Viktighet).

Uppgift 8 (5 p)

grant select, insert on Filosof to olle;

grant update on Filosof to olle;

revoke insert on Filosof from olle;

b) (2p)

create view DodaFilosofer
as select *
from Filosof
where Dod is not null;

grant select on DodaFilosofer to olle;

grant select on DodaFilosofer to olle;

Uppgift 9 (5 p)

1. Dåligt. Konsulten vet inte vad loggfilen är till för. Det stämmer att loggningen kan minska prestandan, men den används inte (huvudsakligen) för säkerhet utan för transaktionshanteringen, för att "backa tillbaka" transaktioner (rollback) och för att garantera atomicitet och hållbarhet vid krascher.
2. Dåligt. Provkörningar av frågorna är bra, men för att veta databasens prestanda när den används av många användare måste man provköra med många (verkliga eller simulerade) användare, eller åtminstone göra en belastningsanalys.
3. Dåligt. Microsoft Access har ett lättanvänt grafiskt gränssnitt, och är bra för att bygga (ganska) enkla applikationer för en eller några få samtidiga användare, men den är inte lämplig för stora databaser med många samtidiga användare.