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 ¨C 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 ¨C 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 ¨C 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 ¨C 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 navn p? 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 ap.ansattnr
FROM ansattDeltarIProsjekt AS ap
     INNER JOIN prosjekt AS p
       ON (ap.prosjektnr = p.prosjektnr)
WHERE p.prosjektnavn = 'Ruter app';

--d)
SELECT a.navn
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 ¨C CRUD

De siste ukene har vi sett p? hvordan vi henter ut informasjon fra en database. Dette er bare ¨¦n del av helheten ¨C i en database vil vi normalt ogs? legge inn, endre og slette data. Disse 4 grunnleggende operasjonene kalles gjerne CRUD ¨C 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;