En ting vi gjerne ønsker å ha lagret i en database, er informasjon om et firmas kunder, prosjekter og ansatte. I denne oppgaven har vi et relasjonsdatabaseskjema for slike data, som ser slik ut:

Kunde(kundenummer, kundenavn, [kundeadresse], [postnr], [poststed])
Prosjekt(prosjektnummer, [prosjektleder], prosjektnavn, [kundenummer], [status])
Ansatt(ansattnr, navn, [fødselsdato], [ansattDato])
AnsattDeltarIProsjekt(ansattnr, prosjektnr)

I relasjonene er det som står før parentesen relasjonsnavnet, de kommaseparerte ordene er relasjonensattributter, mens primærnøklene er som følger:

Kunde(kundenummer)
Prosjekt(prosjektnummer)
Ansatt(ansattnr)
AnsattDeltarIProsjekt(ansattnr, prosjektnr)

Attributter som står i [klammeparentes], er attributter som kan inneholde NULL.

Relasjonene har følgende fremmednøkler:

Prosjekt(kundenummer) → Kunde(kundenummer)
AnsattDeltarIProsjekt(prosjektnr) → Prosjekt(prosjektnummer)
Prosjekt(prosjektleder) → Ansatt(ansattnr)
AnsattDeltarIProsjekt(ansattnr) → Ansatt(ansattnr)

Det er anbefalt å gjøre oppgavene i rekkefølgen som er satt opp.

Oppgave 1 – CREATE TABLE

Skriv SQL-setninger som oppretter tabellene i skjemaet. Finn passende datatyper for attributtene. I tillegg ønsker vi at attributtet status i relasjonen Prosjekt kun skal kunne inneholde verdiene 'planlagt', 'aktiv', eller 'ferdig'.

Løsningsforslag

CREATE TABLE kunde(
    kundenr int PRIMARY KEY,
    kundenavn text NOT NULL,
    kundeadresse text,
    postnr text,
    poststed text
);

CREATE TABLE ansatt(
    ansattnr int PRIMARY KEY,
    navn text NOT NULL,
    fødselsdato date,
    ansattDato date
);

CREATE TABLE prosjekt(
    prosjektnr int PRIMARY KEY,
    prosjektleder int REFERENCES ansatt(ansattnr), 
    prosjektnavn text NOT NULL,
    kundenr int REFERENCES kunde(kundenr),
    status text CHECK (status = 'planlagt' OR status = 'avktiv' OR status = 'ferdig') -- (status IN ('planlagt', 'aktiv', 'ferdig'))
);

CREATE TABLE ansattDeltarIProsjekt(
    ansattnr int REFERENCES ansatt(ansattnr),
    prosjektnr int REFERENCES prosjekt(prosjektnr),
    CONSTRAINT deltar_pk PRIMARY KEY (ansattnr, prosjektnr)
);

Oppgave 2 – Teori

1. Hva er primærnøkkelen i relasjonen Ansatt? Hva med relasjonen AnsattDeltarIProsjekt?
2. Hva er nøkkelattributtene i relasjonen Ansatt? Hva med relasjonen AnsattDeltarIProsjekt?
3. Har relasjonen Ansatt en kandidatnøkkel? I så fall, hva er kandidatnøkkelen?
4. Hva er supernøklene i relasjonen Ansatt?

Løsningsforslag

1. Ansatt: ansattnr AnsattDeltarIProsjekt: {ansattnr, prosjektnr} (én primærnøkkel som består av to attributter)
2. Ansatt: ansattnr AnsattDeltarIProsjekt: ansattnr og prosjektnr
3. ansattnr (kandidatnøkkel = minimal supernøkkel. Primærnøkler er dermed også kandidatnøkler)
4. Alle kombinasjoner av attributter der kombinasjonen kun gir unike tupler, dvs alle kombinasjoner som består av minst én kandidatnøkkel: {ansattnr}, {ansattnr, navn}, {ansattnr, fødselsdato}, {ansattnr, navn, fødselsdato}, {ansattnr, ansattDato}, {ansattnr, fødselsdato, ansattDato}, {ansattnr, navn, ansattdato}, {ansattnr, navn, fødselsdato, ansattdato}

Oppgave 3 – INSERT

Fyll tabellene med data. Skriv INSERT-setninger som gjør det mulig å teste noen av SELECT-setningene som skal skrives i neste oppgave. Prøv også å legge til data i AnsattDeltarIProsjekt for et ansattnr eller prosjektnr som ikke finnes. Dette skal gi deg en feilmelding. Hva er det som hindrer deg i å legge til slike data?

Løsningsforslag

INSERT INTO kunde VALUES (0, 'per', 'gateveien 1', '0001', 'Oslo'),
(1, 'kari', null, null, null);

INSERT INTO ansatt VALUES (0, 'ola', '1998-01-01', '2016-01-05'),
(1, 'nils', null, null);

INSERT INTO prosjekt VALUES (0, 0, 'topp', 1, 'aktiv');

INSERT INTO ansattDeltarIProsjekt VALUES (0,0), (1,0);

Oppgave 4 – SELECT

Skriv SQL-spørringer som henter ut følgende informasjon:

1. En liste over alle kunder. Listen skal inneholde kundenummer, kundenavn og kundeadresse.
2. Navn på alle prosjektledere. Dersom en ansatt er prosjektleder for flere prosjekter skal navnet kun forekomme en gang.
3. Alle ansattnummerene som er knyttet til prosjektet med prosjektnavn 'Ruter app'.
4. En liste over alle ansatte som er knyttet til prosjekter som har kunden med navn 'NSB'

Løsningsforslag

--a)
SELECT kundenr, kundenavn, adresse
FROM kunde;

--b)
SELECT DISTINCT a.navn
FROM ansatt AS a INNER JOIN prosjekt AS p ON (a.ansattnr = p.prosjektleder);

--c)
SELECT a.ansattnr
FROM ansatt AS a
     INNER JOIN ansattDeltarIProsjekt AS ap
       ON (a.ansattnr = ap.ansattnr)
     INNER JOIN prosjekt AS p
       ON (ap.prosjektnr = p.prosjektnr)
WHERE p.prosjektnavn = 'Ruter app';

--d)
SELECT a.ansattnr
FROM ansatt AS a
     INNER JOIN ansattDeltarIProsjekt AS ap
       ON (a.ansattnr = ap.ansattnr)
     INNER JOIN prosjekt AS p
       ON (ap.prosjektnr = p.prosjektnr)
     INNER JOIN kunde AS k
       ON (p.kundenr = k.kundenr)
WHERE k.kundenavn = 'NSB';

Oppgave 5 – CRUD

De siste ukene har vi sett på hvordan vi henter ut informasjon fra en database. Dette er bare én del av helheten – i en database vil vi normalt også legge inn, endre og slette data. Disse 4 grunnleggende operasjonene kalles gjerne CRUD – Create, read, update, delete.

I dette oppgavesettet har du også prøvd deg på create-delen, nemlig INSERT og CREATE. For å fullføre kabalen må vi lære de to siste operasjonene:

1. Finn ut hvordan du kan bruke UPDATE for å endre en rad. Skriv en UPDATE-spørring som endrer en rad du la inn i Oppgave 3.
2. Finn ut hvordan du kan bruke DELETE for å slette en rad. Skriv en DELETE-spørring som sletter én rad du la inn i Oppgave 3 (eller legg til en ny rad som du så sletter).

Løsningsforslag

--a)
UPDATE ansatt
SET ansattdato = '2019-09-20'
WHERE ansattnr = 1;

--b)
DELETE 
FROM ansatt
WHERE ansattnr = 0;