IN2090-ukesoppgaver: Uke 6
Datamanipulering i SQL
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(
int PRIMARY KEY,
kundenr NOT NULL,
kundenavn text
kundeadresse text,
postnr text,
poststed text
);
CREATE TABLE ansatt(
int PRIMARY KEY,
ansattnr NOT NULL,
navn text date,
fødselsdato date
ansattDato
);
CREATE TABLE prosjekt(
int PRIMARY KEY,
prosjektnr int REFERENCES ansatt(ansattnr),
prosjektleder NOT NULL,
prosjektnavn text int REFERENCES kunde(kundenr),
kundenr CHECK (status = 'planlagt' OR status = 'avktiv' OR status = 'ferdig') -- (status IN ('planlagt', 'aktiv', 'ferdig'))
status text
);
CREATE TABLE ansattDeltarIProsjekt(
int REFERENCES ansatt(ansattnr),
ansattnr int REFERENCES prosjekt(prosjektnr),
prosjektnr CONSTRAINT deltar_pk PRIMARY KEY (ansattnr, prosjektnr)
);
Oppgave 2 – Teori
- Hva er primærnøkkelen i relasjonen
Ansatt
? Hva med relasjonenAnsattDeltarIProsjekt
? - Hva er nøkkelattributtene i relasjonen
Ansatt
? Hva med relasjonenAnsattDeltarIProsjekt
? - Har relasjonen Ansatt en kandidatnøkkel? I så fall, hva er kandidatnøkkelen?
- Hva er supernøklene i relasjonen Ansatt?
Løsningsforslag
Ansatt
:ansattnr
AnsattDeltarIProsjekt
:{ansattnr, prosjektnr}
(én primærnøkkel som består av to attributter)Ansatt
:ansattnr
AnsattDeltarIProsjekt
:ansattnr
ogprosjektnr
ansattnr
(kandidatnøkkel = minimal supernøkkel. Primærnøkler er dermed også kandidatnøkler)- 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:
- En liste over alle kunder. Listen skal inneholde kundenummer, kundenavn og kundeadresse.
- Navn på alle prosjektledere. Dersom en ansatt er prosjektleder for flere prosjekter skal navnet kun forekomme en gang.
- Alle ansattnummerene som er knyttet til prosjektet med prosjektnavn
'Ruter app'
. - 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 – 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:
- Finn ut hvordan du kan bruke
UPDATE
for å endre en rad. Skriv enUPDATE
-spørring som endrer en rad du la inn i Oppgave 3. - Finn ut hvordan du kan bruke
DELETE
for å slette en rad. Skriv enDELETE
-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;