Databaskonstruktion: Lösningar till tentamen 2007-03-03

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

Uppgift 2 (6 p)

Tabeller, med primärnycklarna är understrukna:

bil(registreringsnummer, märke, modell, årsmodell, ägare)
person(personnummer, namn)
gripande(nummer, datum, tid)
kör(person, bil)
greps(gripande, person)
inblandade(gripande, bil)

Referensattribut:

bil.ägare till person.personnummer
kör.person till person.personnummer
kör.bil till bil.registreringsnummer
greps.gripande till gripande.nummer
greps.person till person.personnummer
inblandade.gripande till gripande.nummer
inblandade.bil till bil.personnummer

Entitetstypen Gripande har ingen nyckel i scenariot (för två separata gripanden kan förmodligen ske samtidigt), så vi inför ett unikt nummer.

Det skulle dessutom kunna vara bra att införa enkla numeriska primärnycklar i tabellerna bil och person, där primärnycklarna nu är textsträngar.

Det skulle också kunna vara bra att införa enkla numeriska primärnycklar som databasinterna "radnummer" i tabellerna kör, greps och inblandade.

SQL-kommandon för att provköra. (Stämmer inte helt med scenariot, till exempel eftersom bil-registreringsnumret här gjorts som ett heltal.)

create table person
(personnummer integer primary key,
namn varchar(10));

create table bil
(registreringsnummer integer primary key,
marke varchar(10),
modell varchar(10),
arsmodell integer,
agare integer references person(personnummer));

create table gripande
(nummer integer primary key,
datum varchar(10),
tid varchar(5));

create table kor
(person integer references person(personnummer),
bil integer references bil(registreringsnummer),
primary key (person, bil));

create table greps
(gripande integer references gripande(nummer),
person integer references person(personnummer),
primary key (gripande, person));

create table inblandade
(gripande integer references gripande(nummer),
bil integer references bil(registreringsnummer),
primary key (gripande, bil));

insert into person(personnummer, namn) values (1, 'Adam');
insert into person(personnummer, namn) values (2, 'Bertil');
insert into person(personnummer, namn) values (3, 'Cesar');
insert into person(personnummer, namn) values (4, 'David');
insert into person(personnummer, namn) values (5, 'Erik');

insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (1, 'Volvo', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (2, 'Volvo', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (3, 'Volvo', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (4, 'Saab', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (5, 'Volvo', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (6, 'Volvo', 'V70', 2005, 1);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (7, 'Volvo', 'V70', 2005, 2);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (8, 'Volvo', 'V70', 2005, 2);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (9, 'Volvo', 'V70', 2005, 2);
insert into bil(registreringsnummer, marke, modell, arsmodell, agare)
    values (10, 'Volvo', 'V70', 2005, 2);

insert into gripande(nummer, datum, tid) values (1, '2007-03-03', '08:30');
insert into gripande(nummer, datum, tid) values (2, '2007-03-03', '08:30');
insert into gripande(nummer, datum, tid) values (3, '2007-03-03', '08:30');

insert into kor(person, bil) values (1, 1);
insert into kor(person, bil) values (1, 2);
insert into kor(person, bil) values (1, 3);
insert into kor(person, bil) values (2, 1);
insert into kor(person, bil) values (2, 2);
insert into kor(person, bil) values (3, 3);
insert into kor(person, bil) values (4, 4);

insert into greps(gripande, person) values (1, 1);
insert into greps(gripande, person) values (1, 2);
insert into greps(gripande, person) values (1, 3);
insert into greps(gripande, person) values (2, 1);
insert into greps(gripande, person) values (2, 2);
insert into greps(gripande, person) values (2, 3);
insert into greps(gripande, person) values (3, 3);

insert into inblandade(gripande, bil) values (1, 1);
insert into inblandade(gripande, bil) values (1, 2);
insert into inblandade(gripande, bil) values (1, 3);
insert into inblandade(gripande, bil) values (2, 1);
insert into inblandade(gripande, bil) values (2, 2);
insert into inblandade(gripande, bil) values (2, 3);
insert into inblandade(gripande, bil) values (3, 3);

select * from person;
select * from bil;
select * from gripande;
select * from kor;
select * from greps;
select * from inblandade;

Uppgift 3 (12 p)

a) (2p) Berra Bengtsson äger flera bilar. Vilka märken har dessa bilar?

select marke
from person, bil
where agare = personnummer
and namn = 'Berra Bengtsson;

En alternativ lösning:

select marke from bil
where agare in (select personnummer
                from person
                where namn = 'Berra Bengtsson');

b) (2p) Vilka personer äger bilar som varit inblandade i gripanden? Vi vill veta dessa personers personnummer och namn.

select distinct personnummer, namn
from person, bil, inblandade
where person.personnummer = bil.agare
and bil.registreringsnummer = inblandade.bil;

En alternativ lösning:

select personnummer, namn from person
where personnummer in (select agare from bil
                       where registreringsnummer in (select bil from inblandade));

c) (2p) Vilka personer har gripits? Vi vill veta deras personnummer och namn.

select distinct personnummer, namn
from person, greps
where person.personnummer = greps.person;

En alternativ lösning:

select personnummer, namn from person
where personnummer in (select person from greps);

d) (3p) Vem kör flest bilar? Vi vill veta den personens personnummer och namn.

create view antalkorda as
select personnummer, namn, count(*) as antal
from person, kor
where person.personnummer = kor.person
group by personnummer, namn;

select personnummer, namn
from antalkorda
where antal = (select max(antal) from antalkorda);

e) (3p) Vi vill ha en lista över alla personer (med personnummer och namn) och hur många bilar de brukar köra. Personer som inte brukar köra några bilar alls ska också vara med, med antalet noll.

select personnummer, namn, count(kor.person) as antal
from person left outer join kor on person.personnummer = kor.person
group by personnummer, namn;

Uppgift 4 (4 p)

I fråga a: bil.agare person.personnummer person.namn I fråga b: person.personnummer bil.agare bil.registreringsnummer inblandade.bil; I fråga c: person.personnummer greps.person Skapa index på:

bil.agare
bil.registreringsnummer
greps.person
inblandade.bil
person.namn
person.personnummer

Dessa används för att välja ut rader och koppla ihop tabeller i frågorna, och alla tabeller är åtminstone ganska stora.

Skapa inte index på (bland annat):

bil.marke -- används inte för att välja ut rader eller koppla ihop tabeller
kor.person -- används inte i fråga a, b eller c

Uppgift 5 (6 p)

a) (3p)

Den enklaste varianten, utan några noteringar om disjunkta delmängder eller fullständigt deltagande:

Ett ER-diagram för bibliotekariens värld

b) (3p)

Tabeller, med primärnycklarna understrukna:

låntagare(nummer, namn, adress)
bok(nummer, titel)
fackbok(nummer)
skönlitterär_bok(nummer)
roman(nummer)
novellsamling(nummer, antal_noveller)

Referensattribut:

fackbok.nummer till bok.nummer
skönlitterär_bok.nummer till bok.nummer
roman.nummer till skönlitterär_bok.nummer
novellsamling.nummer till skönlitterär_bok.nummer

Uppgift 6 (6 p)

Se kurslitteraturen.

Uppgift 7 (3 p)

Se kurslitteraturen.

Uppgift 8 (4 p)

Se kurslitteraturen.

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 25 februari 2007