Databasteknik: Lösningar till tentamen 2007-12-15

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

ER-diagram över köksdatabasen

Uppgift 2 (5 p)

Tabeller, med primärnycklarna är understrukna: Referensattribut:

För att förenkla programmeringen av tillämpningsprogrammen kan det vara bra att införa en enkel numerisk primärnyckel även i tabellen innehåller.

SQL-kommandon för att provköra:

create table skaptyp
(nummer integer not null primary key,
namn char(10) unique,
pris integer);

create table kund
(nummer integer not null primary key,
namn char(10),
adress char(10));

create table kok
(nummer integer not null primary key,
datum char(10),
kopare integer not null references kund(nummer));

create table innehaller
(skaptyp integer not null references skaptyp(nummer),
kok integer not null references kok(nummer),
antal integer,
primary key (skaptyp, kok));

insert into skaptyp values (1, 'grytskåp', 1000);
insert into skaptyp values (2, 'kryddskåp', 500);
insert into skaptyp values (3, 'skafferi', 2000);
insert into skaptyp values (4, 'städskåp', 1000);
insert into skaptyp values (5, 'kassaskåp', 1000);

insert into kund values (1, 'Kalle', 'Vägen 7');
insert into kund values (2, 'Lisa', 'Gränden 1');
insert into kund values (3, 'Vincent', 'Stigen 2');
insert into kund values (4, 'Öbo', 'Torget 1');

insert into kok values (1, '2007-01-10', 1);
insert into kok values (2, '2007-01-10', 2);
insert into kok values (3, '2007-01-11', 1);
insert into kok values (4, '2007-01-11', 4);
insert into kok values (5, '2007-01-11', 4);
insert into kok values (6, '2007-01-11', 4);
insert into kok values (7, '2007-01-12', 4);

insert into innehaller values (1, 1, 1);
insert into innehaller values (1, 2, 1);
insert into innehaller values (1, 3, 1);
insert into innehaller values (1, 4, 1);
insert into innehaller values (1, 5, 1);
insert into innehaller values (1, 6, 2);
insert into innehaller values (1, 7, 1);
insert into innehaller values (2, 1, 1);
insert into innehaller values (2, 2, 1);
insert into innehaller values (2, 3, 1);
insert into innehaller values (2, 4, 1);
insert into innehaller values (2, 5, 1);
insert into innehaller values (2, 6, 2);
insert into innehaller values (2, 7, 1);
insert into innehaller values (3, 1, 10);

select * from skaptyp;
select * from kund;
select * from kok;
select * from innehaller;

Uppgift 3 (10 p)

a) (1p) Vad är numren på det eller de kök som kund nummer 167 har köpt?

select nummer
from kok
where kopare = 167;

b) (2p) Vilka datum levererades de kök som innehåller skåptypen "grytskåp"?

select kok.datum
from kok, innehaller, skaptyp
where kok.nummer = innehaller.kok
and innehaller.skaptyp = skaptyp.nummer
and skaptyp.namn = 'grytskåp';
En alternativ lösning:
select datum
from kok
where nummer in (select kok
                 from innehaller
                 where skaptyp in (select nummer
                                   from skaptyp
                                   where namn = 'grytskåp'));

c) (1p) Ange namnen på de skåptyper som inte ingår i några kök.

select namn from skaptyp
where nummer not in (select skaptyp from innehaller);

d) (3p) Hur många grytskåp har kunden som heter Öbo sammanlagt köpt i alla sina kök?

select sum(innehaller.antal)
from skaptyp, innehaller, kok, kund
where skaptyp.nummer = innehaller.skaptyp
and innehaller.kok = kok.nummer
and kok.kopare = kund.nummer
and kund.namn = 'Öbo'
and skaptyp.namn = 'grytskåp';

e) (3p) Vad är namnet på den kund som köpt flest kök?

create view kok_per_kund as
select kopare as kundnummer, count(*) as antal
from kok
group by kopare;

select namn from kund
where nummer = (select kundnummer from kok_per_kund
                where antal = (select max(antal) from kok_per_kund));

Uppgift 4 (3 p)

Skapa index på: Dessa är de kolumner som används i de aktuella frågorna för att välja ut rader i en tabell eller för att koppla ihop rader i två tabeller, alltså det som internt i databashanteraren kallas "select"- och "join"-operationer.

Skapa inte index på:

SQL-kommandon för att provköra:

create index kok_kopare_index on kok(kopare);
create index kok_nummer_index on kok(nummer);
create index innehaller_kok_index on innehaller(kok);
create index innehaller_skaptyp_index on innehaller(skaptyp);
create index skaptyp_nummer_index on skaptyp(nummer);
create index skaptyp_namn_index on skaptyp(namn);

Uppgift 5 (7 p)

a) (3p)

Enkel lösning:

EER-diagram över vassa och runda saker

En annan lösning, med en mer fullständig specifikation av hur man tänkt sig databasen (men observera att andra antaganden är möjliga när det gäller fullständig specialisering och överlappning mellan subklasserna):

Ett annat EER-diagram över vassa och runda saker

b) (3p)

Tabeller, med primärnycklarna är understrukna:

Referensattribut:

SQL-kommandon för att provköra:

create table sak
(nummer integer not null primary key,
vikt float);

create table vass_sak
(nummer integer not null primary key references sak(nummer),
vasshet integer);

create table rund_sak
(nummer integer not null primary key references sak(nummer),
diameter integer);

create table sax
(nummer integer not null primary key references vass_sak(nummer));

create table boll
(nummer integer not null primary key references rund_sak(nummer),
studskoefficient float);

insert into sak values (1, 0.1);
insert into sak values (2, 0.2);
insert into sak values (3, 0.3);
insert into sak values (4, 0.4);
insert into sak values (5, 0.5);
insert into sak values (6, 0.6);

insert into rund_sak values (1, 10);    
insert into rund_sak values (2, 20);
insert into rund_sak values (3, 30);

insert into vass_sak values (4, 1); 
insert into vass_sak values (5, 2);
insert into vass_sak values (6, 3);

insert into boll values (2, 0.91); 
insert into boll values (3, 0.97);

insert into sax values (5);        
insert into sax values (6);

c) (1p)

select sak.nummer, sak.vikt, rund_sak.diameter, boll.studskoefficient
from sak, rund_sak, boll
where sak.nummer = rund_sak.nummer
and rund_sak.nummer= boll.nummer;
Eller bara så här:
select *
from sak, rund_sak, boll
where sak.nummer = rund_sak.nummer
and rund_sak.nummer= boll.nummer;

Uppgift 6 (5 p)

a) (1p)

Ffb i tabellen Städning

Dvs:

b) (2p)

Tabellen uppfyller första normalformen. Alla värden är atomära, vilket krävs för första normalformen, men eftersom Rum och Datum är kandidatnyckel, och det finns beroenden på delar av denna, så bryter tabellen mot reglerna för andra normalformen.

c) (2p)

Följande svar är fel:

Uppgift 7 (4 p)

Se kursboken eller webbkursen (http://www.databasteknik.se/webbkursen/transaktioner/index.html).

Uppgift 8 (3 p)

Några exempel:

Uppgift 9 (4 p)

a) (1p) Vad är grant och revoke?

Grant och revoke är två kommandon som används (normalt av databasadministratören) för att ange vad olika användare får göra med de data som finns i databasen. Med grant delar man ut rättigheter, och med revoke tar man bort dem igen.

b) (1p) Visa med exempel hur de kan användas.

grant select on employee to dbtek50;
    Nu får användaren dbtek50 söka i tabellen employee.
grant insert on employee to dbtek51;
    Nu får dbtek51 lägga in nya rader i employee.
revoke select on employee from dbtek50;
    Nu får dbtek50 inte längre söka i employee.
revoke select on employee from dbtek51;
    Ger felmeddelande - rättigheten var aldrig utdelad.
revoke insert on employee from dbtek51;
    Nu får dbtek51 inte längre lägga in nya rader i employee.

c) (1p) Grant och revoke kan kombineras med vyer. Varför gör man det?

Genom att skapa en vy som bara innehåller vissa data från en tabell (eller kombinerat från flera olika tabeller) och sen dela ut rättigheter till den vyn, men inte till de underliggande tabellerna, kan man låta användare komma åt bara just de data från tabellerna som man vill att de ska komma åt.

d) (1p) Visa med ett exempel hur grant kan kombineras med en vy.

create view antal_jobbare
as select count(*) as antal from employee;
    Vyn antal_jobbare visar antalet anställda.
grant select on antal_jobbare to dbtek52;
    Nu kan dbtek52 se antalet anställda, men inga andra data om dem.


Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 29 december 2007