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

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?

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?

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 navn på alle ansatte som er knyttet til prosjekter som har kunden med navn '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).