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

Uppgift 1 (5 p)

Uppgift 2 (5 p)

• skåptyp(nummer, namn, pris)
• kök(nummer, datum, köpare)
• innehåller(skåptyp, kök, antal)
• kund(nummer, namn, adress)

• kök.köpare till kund.nummer
• innehåller.skåptyp till skåptyp.nummer
• innehåller.kök till kök.nummer

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)

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';

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)

• kok.kopare
• kok.nummer
• innehaller.kok
• innehaller.skaptyp
• skaptyp.nummer
• skaptyp.namn

Skapa inte index på:

• kok.datum (används inte för att välja ut eller koppla ihop rader)

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)

Enkel lösning:

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

b) (3p)

Tabeller, med primärnycklarna är understrukna:

• sak(nummer, vikt)
• vass_sak(nummer, vasshet)
• rund_sak(nummer, diameter)
• sax(nummer)
• boll(nummer, studskoefficient)

• vass_sak.nummer till sak.nummer
• rund_sak.nummer till sak.nummer
• sax.nummer till vass_sak.nummer
• boll.nummer till rund_sak.nummer

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;

select *
from sak, rund_sak, boll
where sak.nummer = rund_sak.nummer
and rund_sak.nummer= boll.nummer;

Uppgift 6 (5 p)

Dvs:

• Rum -> Yta
• Rum -> Tid
• Dag -> Städare

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)

• Redundans, dvs dubbellagring av data. Exempel: Att det var Lisa som skötte städningen 16 november står en gång för varje rum som hon städade.
• Vissa saker går inte att lagra. Exempel: Att garaget är 24 kvadratmeter stort går inte att lagra innan vi städad garaget för första gången.
• Tabellen blir onödigt svår att förstå. Exempel: Visar kolumnen Yta hur stor del av rummet som faktiskt städades just den dagen, eller hela golvytan?

• Redundans, dvs dubbellagring av data. Exempel: Det står Hallen på tre ställen, eftersom hallen städats tre gånger.

Uppgift 7 (4 p)

Uppgift 8 (3 p)

• Microsoft Access består huvudsakligen av ett lättanvänt grafiskt gränssnitt, som lagrar sina data i en ganska enkel SQL-databashanterare (som heter Microsoft Jet), och som också kan lagra sina data i en helt annan databashanterare, medan MySQL är själva SQL-databashanteraren.
• MySQL är gjord för att klara många samtidiga användare och hög belastning, medan Access är tänkt för få användare och låg belastning.
• MySQL har en mer avancerad SQL-dialekt än Access (läs: Jet), med triggers med mera.
• MySQL har en klient-server-arkitektur, men den serverprocess som är ständigt igång, medan Access (läs: Jet) är ett program som arbetar direkt med en fil ungefär på samma sätt som Microsoft Word arbetar direkt med ett Word-dokument.
• MySQL kan köras på många olika operativsystem, men Microsoft Access finns bara för Windows.
• MySQL är fri programvara och (dock inte i alla versioner) gratis, medan Access kostar pengar.

Uppgift 9 (4 p)

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.