Databasteknik: Lösningar till tentamen 2014-03-08

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

Uppgift 2 (5 p)

Tabeller, med primärnyckeln understruken:

Datasalar(namn)
Datortyper(nummer, märke, modell)
Datorer(nummer, typ, placering)
Felanmälningar(nummer, datum, beskrivning, datasal)
FelanmälningarOmDatorer(felanmälning, dator)

Märke och modell bildar en sammansatt alternativnyckel i Datortyper.

Datorer.typ refererar till Datortyper.nummer
Datorer.placering refererar till Datasalar.namn
Felanmälningar.datasal refererar till Datasalar.namn
FelanmälningarOmDatorer.felanmälning refererar till Felanmälningar.nummer
FelanmälningarOmDatorer.dator refererar till Datorer.nummer

SQL-kommandon för att provköra:

drop table FelanmalningarOmDatorer;
drop table Felanmalningar;
drop table Datorer;
drop table Datortyper;
drop table Datasalar;

create table Datasalar
(namn varchar(4) not null unique);

insert into Datasalar (namn) values ('T120');
insert into Datasalar (namn) values ('T122');
insert into Datasalar (namn) values ('T124');
insert into Datasalar (namn) values ('T002');
insert into Datasalar (namn) values ('L114');

create table Datortyper
(nummer integer not null primary key,
marke varchar(10) not null,
modell varchar(10) not null,
unique (marke, modell));

insert into Datortyper (nummer, marke, modell) values (1, 'Dell', 'D-1');
insert into Datortyper (nummer, marke, modell) values (2, 'Dell', 'D-2');
insert into Datortyper (nummer, marke, modell) values (3, 'Dell', 'D-3');
insert into Datortyper (nummer, marke, modell) values (4, 'HP', 'HP-1');
insert into Datortyper (nummer, marke, modell) values (5, 'HP', 'HP-2');

create table Datorer
(nummer integer not null primary key,
typ integer not null references Datortyper(nummer),
placering varchar(4) references Datasalar(namn));

insert into Datorer (nummer, typ, placering) values (1, 1, 'T120');
insert into Datorer (nummer, typ, placering) values (2, 1, 'T120');
insert into Datorer (nummer, typ, placering) values (3, 2, 'T120');
insert into Datorer (nummer, typ, placering) values (4, 2, 'T120');
insert into Datorer (nummer, typ, placering) values (5, 2, 'T120');
insert into Datorer (nummer, typ, placering) values (6, 4, 'T120');
insert into Datorer (nummer, typ, placering) values (7, 4, 'T120');
insert into Datorer (nummer, typ, placering) values (8, 4, null);
insert into Datorer (nummer, typ, placering) values (9, 5, null);
insert into Datorer (nummer, typ, placering) values (10, 5, 'T124');
insert into Datorer (nummer, typ, placering) values (11, 5, 'T124');

create table Felanmalningar
(nummer integer not null primary key,
datum date not null,
beskrivning varchar(10) not null,
datasal varchar(4) references Datasalar(namn));

insert into Felanmalningar (nummer, datum, beskrivning, datasal) values (1, DATE'2014-03-03', 'Full', 'T120');
insert into Felanmalningar (nummer, datum, beskrivning, datasal) values (2, DATE'2014-03-03', 'Tom', 'T122');
insert into Felanmalningar (nummer, datum, beskrivning, datasal) values (3, DATE'2014-03-03', 'Trasig', null);
insert into Felanmalningar (nummer, datum, beskrivning, datasal) values (4, DATE'2014-03-03', 'Trasiga', null);

create table FelanmalningarOmDatorer
(felanmalning integer references Felanmalningar(nummer),
dator integer references Datorer(nummer),
primary key (felanmalning, dator));

insert into FelanmalningarOmDatorer (felanmalning, dator) values (3, 1);
insert into FelanmalningarOmDatorer (felanmalning, dator) values (4, 8);
insert into FelanmalningarOmDatorer (felanmalning, dator) values (4, 9);
insert into FelanmalningarOmDatorer (felanmalning, dator) values (4, 10);
insert into FelanmalningarOmDatorer (felanmalning, dator) values (4, 11);

select * from Datasalar;
select * from Datortyper;
select * from Datorer;
select * from Felanmalningar;
select * from FelanmalningarOmDatorer;

Uppgift 3 (12 p)

Formulera följande frågor i SQL. Använd dina tabeller från uppgift 2. Definiera gärna vyer om det underlättar.

a) Vad heter de datasalar som har namn som börjar med bokstaven T?

select namn
from Datasalar
where namn like 'T%';

b) Vad heter de datasalar som har namn som inte börjar med bokstaven T?

select namn
from Datasalar
where namn not like 'T%';

c) På den här datorn står inventarienumret 1234. Vad heter modellen?

select Datortyper.modell
from Datorer, Datortyper
where Datorer.typ = Datortyper.nummer
and Datorer.nummer = 1234;

En alternativ lösning utan onödiga utskrivna tabellnamn:

select modell
from Datorer, Datortyper
where typ = Datortyper.nummer
and Datorer.nummer = 1234;

En alternativ lösning med en explicit join:

select Datortyper.modell
from Datortyper inner join Datorer on Datorer.typ = Datortyper.nummer
where Datorer.nummer = 1234;

En alternativ lösning med en underfråga:

select modell
from Datortyper
where nummer in (select typ
                 from Datorer
                 where nummer = 1234);

d) Vilka märken har de datorer som står i datorsal T120?

select distinct marke
from Datorer, Datortyper
where Datorer.typ = Datortyper.nummer
and Datorer.placering = 'T120';

e) Nu ska jag gå till en datasal och jobba, men jag vill välja en felfri sal. Vilka datasalar har inga felanmälningar, vare sig om själva salen eller datorer som står i den salen?

select namn
from Datasalar
where namn not in (select datasal from Felanmalningar where datasal is not null)
and namn not in (select placering from Datorer
                 where nummer in (select dator from FelanmalningarOmDatorer)
                 and placering is not null);

En kommentar: Provkör och se vad som händer om vi inte tar med villkoren is not null! Jag drar inga poäng på den här frågan om man glömt det villkoret, men det kan tjäna som en påminnelse om hur krångligt allt blir så fort man har null-värden i databasen, och varför man vill konstruera sina tabeller så man slipper null-värden!

En alternativ lösning:

create view TrasigaDatasalar as
select Datasalar.namn
from Felanmalningar, Datasalar
where Felanmalningar.datasal = Datasalar.namn;

create view TrasigaDatorersPlacering as
select Datasalar.namn
from Felanmalningar, FelanmalningarOmDatorer, Datorer, Datasalar
where Felanmalningar.nummer = FelanmalningarOmDatorer.felanmalning
and FelanmalningarOmDatorer.dator = Datorer.nummer
and Datorer.placering = Datasalar.namn;

select namn
from Datasalar
where namn not in (select * from TrasigaDatasalar)
and namn not in (select * from TrasigaDatorersPlacering);

f) I vilken datasal står det flest datorer?

create view AntalDatorerPerSal as
select placering as datasal, count(placering) as antal
from Datorer
group by placering;

select * from AntalDatorerPerSal;

select datasal
from AntalDatorerPerSal
where antal = (select max(antal) from AntalDatorerPerSal);

Uppgift 4 (5 p)

Örebro universitet har ett tiotal datasalar och ett par hundra datorer. Det finns flera tusen felanmälningar.

a) Kommer en vanlig databashanterare att kunna hantera dessa datamängder? Motivera svaret!

Ja. Vanliga databashanterare är gjorda för att hantera stora datamängder (miljoner och kanske miljarder rader i en eller flera tabeller), och "flera tusen felanmälningar" är en mycket liten databas.

b) Bör man skapa index, och i så fall vilka? Hur vet man att just dessa index ska skapas?

Det kan vara bra att skapa index. Med en så liten databas kommer det kanske inte att snabba upp frågorna så mycket, men den kanske växer i framtiden, när universitetet ökat i storlek, och felanmälningarna dessutom samlats på hög i årtionden.

Vilka index man bör skapa beror på vilka kolumner som används för att söka fram rader i SQL-frågorna, hur ofta de SQL-frågorna körs, vilka tidskrav man har. Några förslag på index, om vi antar att SQL-frågorna i deluppgift 3a-3d körs ofta:

Datasalar.namn, för den används i sökvillkoren flera frågor. Å andra sidan är den tabellen mycket liten, och eftersom den kolumnen är angiven som nyckel så finns det (normalt) redan ett index på den.
Datorer.nummer, för den används i sökvillkoren flera frågor (men den är primärnyckel)
Datorer.typ
Datortyper.nummer (men den är primärnyckel)
Datorer.placering

c) Vad är egentligen ett index?

En intern datastruktur i en databas, som databashanteraren använder för att snabbare hitta rader i en tabell, baserat på vilka värden som finns i kolumnerna.

d) Och, när man pratar om relationsdatabaser, vad är en relation?

En tabell.

Uppgift 5 (3 p)

Ange tre olika databashanterare. Skriv för var och en åtminstone någon egenskap som skiljer den från de andra.

Här får ni hitta svaren på andra ställen, men här är i alla fall några svar som är fel:

HTML
Java
SQL
Microsoft IBM
DbVisualizer
Batch SQL
FlameRobin
Excel

Uppgift 6 (5 p)

Här är fem påståenden om databaser. Det kan hända att en del av dem är fel.
Ange för varje påstående om det är rätt eller fel. Om det var fel, förklara varför det är fel, och hur det egentligen är.

a) En av fördelarna med att använda databasteknik är att det är lätt att göra ändringar i databasens struktur, till exempel genom att lägga till en ny kolumn i en tabell.

Rätt. Det är förstås bra om man kan slippa göra ändringar, men om omvärlden eller ens egen bild av den förändras, kanske man måste göra det. Då är det lättare att skriva alter table eller create index än att (till exempel) skriva om ett C-program.

b) Den så kallade tre-schema-arkitekturen går ut på att en och samma databas kan beskrivas på tre olika nivåer: den tekniska nivån, med SQL och tabeller, den organisatoriska nivån, och den haptiska nivån.

Fel. Fysisk, logisk och extern nivå, inte teknisk, organisatorisk och haptisk. (Haptik är läran om effekterna av beröring och kroppsrörelser.)

c) På webben är det vanligt att man låter en webbplats lagra data i en databas. Det går att lagra data på andra sätt, till exempel i vanliga filer, men det blir enklare med en databashanterare.

Rätt. Under vissa särskilda förutsättningar kan det vara enklare med till exempel en vanlig fil som man skriver data till, men normalt blir det enklare om man kan använda databashanterare.

d) En databas brukar ha ett explicit schema, som är en beskrivning av vilka data som man kan lagra i databasen. Schemat lagras i databasen som andra data, och kan till exempel innehålla vilka tabeller som finns i databasen.

Rätt.

e) Det finns en SQL-standard som alla databashanterare följer, och därför kommer de SQL-frågor att fungera exakt likadant om man byter från en databashanterare till en annan. Det som skiljer databashanterarna åt är pris och snabbhet, men språket SQL är likadant i alla.

Fel. Det finns en SQL-standard, och de flesta databashanterare har SQL-dialekter som är ganska lika, men detaljerna kan skilja ganska mycket.

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 14 mars 2014