/*
Oppgave 5a)

Skriv create-setninger som oppretter tabellene fra skjemaet du laget 
i forrige oppgave.

Student(__Studentid__, _navn, adresse_)
Gruppel?rer(_Studentid_, [l?nn])
Epostkonto(_Epostkontonavn_, studentid)
* Kurs(_Kurskode_)
Deltagelse(_Kurskode, Studentid_)
Resultat(_Kurskode, Studentid_, Karakter)

Gruppel?rer(Studentid) -> Student(Studentid)
Epostkonto(Studentid) -> Student(Studentid)
Deltagelse(Kurskode) -> Kurs(Kurskode)
Deltagelse(Studentid) -> Student(Studentid)
Resultat(Kurskode) -> Kurs(Kurskode)
Resultat(Studentid) -> Student(Studentid)
*/

CREATE TABLE Student (
	studentid VARCHAR(20),
	navn VARCHAR(20) NOT NULL,
	adresse VARCHAR(30) NOT NULL,

	PRIMARY KEY (studentid),
	UNIQUE(navn, adresse)
);

CREATE TABLE Gruppel?rer (
	studentid VARCHAR(20),
	lonn INTEGER,

	PRIMARY KEY (studentid),
	FOREIGN KEY (studentid) REFERENCES Student (studentid)
);

CREATE TABLE Epostkonto (
	epostkontonavn VARCHAR(20),
	studentid VARCHAR(20),

	PRIMARY KEY (epostkontonavn),
	FOREIGN KEY (studentid) REFERENCES Student (studentid)
);


CREATE TABLE Deltagelse (
	studentid VARCHAR(20),
	kurskode VARCHAR(10),

	PRIMARY KEY (studentid, kurskode),
	FOREIGN KEY (studentid) REFERENCES Student (studentid)
);


CREATE TABLE Resultat (
	studentid VARCHAR(20),
	kurskode VARCHAR(10),
	karakter VARCHAR(1),

	PRIMARY KEY (studentid, kurskode),
	FOREIGN KEY (studentid) REFERENCES Student (studentid)
	FOREIGN KEY (studentid, kurskode) REFERENCES Deltagelse (studentid, kurskode)

);

/*
Oppgave 5b)

Skriv en select-setnign som sjekker om det finnes tupler som bryter 
delmengdeskranken i modellen i oppgave 4.
*/

SELECT r.studentid, r.kurskode 
FROM Resultat r
WHERE NOT EXISTS
(
	SELECT studentid, kurskode 
	FROM Deltagelse 
	WHERE r.studentid = studentid
	AND r.kurskode = kurskode
);


/*
Oppgave 6a)

Skriv ned alle kandidatn?kler i relasjonen Person.
*/

-- PID, (Navn, Kallenavn), Tlfnr


/*
Oppgave 6b)

Hvilke av disse attributtmengdene er ikke en supern?kkel i Person?

1 (navn, tlfnr)
2 (navn, postadresse)
3 (pid, navn, kallenavn, tlfnr, postadresse) 
4 (navn, kallenavn, postadresse)
5 (kallenavn, postadresse)
6 (tlfnr, postadresse)
*/

-- 2
-- 5


/*
Oppgave 6c & d)

Finnes det funksjonelle avhengigheter (FD) i Person som 
bryter med BCNF? Nevn i s?fall hvilke.
*/

 pid -> navn, kallenavn  tlfnr, postadresse (BCNF)
 navn, kallenavn -> pid, tlfnr, postadresse (BCNF)
 tlfnr  -> navn, kallenavn, pid, postadresse (BCNF)

-- Relasjonen oppfyller BCNF.

/*
Oppgave 7a)

Et view med navn MusikereITroms med kallenavn og telefonnummer 
for personer med en adresse som inneholder ordet 'Troms' eller 
inneholder en tekststreng med 4 tegn som starter med tegnet '9'.
*/

CREATE VIEW MusikereITroms AS
SELECT kallenavn, tlfnr
FROM Person 
WHERE postadresse LIKE '%Troms%'
	OR postadresse LIKE '9___';

/*
Oppgave 7b)

Navn p? musikere og hvilke intstrumenter de spilte p? konserter 
i Oslo (sted) i November 2014. Ta ogs? med steder som f.eks. 
'Oslo spektrum' eller 'Rockefeller Oslo'.
*/

SELECT navn, instr
FROM Konsert k NATURAL JOIN KonsertInfo ki
	NATURAL JOIN Person p
WHERE sted LIKE '%Oslo%'
	AND dato LIKE '201411__';

/*
Oppgave 7c)

Pid, navn og kallenavn for alle personer som ikke har et 
unikt navn.
*/

CREATE VIEW OppgC
SELECT navn
FROM Person p
GROUP BY navn
HAVING COUNT(navn) > 1;

SELECT pid, navn, kallenavn
FROM Person p NATURAL JOIN OppgC;

/*
Oppgave 7d)

Antall musikere som spiller bare ett instrument.
*/


SELECT COUNT(instr)
FROM Spiller
GROUP BY pid
HAVING COUNT(instr) = 1;


/*
Oppgave 7e)

Oversikt over alle musikere (navn) som spiller minst 5 instrumenter 
og som aldri har deltatt p? konsert i desember m?ned.
*/

CREATE VIEW under5instrumenter AS
SELECT pid
FROM Spiller
GROUP BY pid
HAVING COUNT(instr) < 5;

CREATE VIEW spiltIDesember AS
SELECT pid
FROM Konsert NATURAL JOIN KonsertInfo
WHERE dato LIKE '____12__';

SELECT navn
FROM Person p
WHERE NOT EXISTS 
(
	SELECT pid
	FROM under5instrumenter
	WHERE p.pid = pid
)
AND NOT EXISTS 
(
	SELECT pid
	FROM spiltIDesember
	WHERE p.pid = pid
);


/*
Oppgave 7f)

Et view med antall instrumenter en musiker (pid) spiller, 
inklusive evt. fremmedinstrumenter spilt p? konserter.
*/

CREATE VIEW FremmedInstrumenter AS
SELECT pid, COUNT(DISTINCT instr) AS anta_f_instr
FROM Konsert k
WHERE NOT EXISTS
(
	SELECT instr
	FROM Spiller
	WHERE instr = k.instr
)
GROUP BY pid;

CREATE VIEW SpillerInstrumenter AS
SELECT pid, COUNT(DISTINCT instr) AS anta_instr
FROM Spiller
GROUP BY pid;

CREATE VIEW Oppg7F
SELECT pid, anta_f_instr + anta_instr AS tot_instr
FROM FremmedInstrumenter NATURAL JOIN SpillerInstrumenter;

/*
Oppgave 7g)

Navn og kallenavn p? alle musikere som spiller alle instrumenter 
(ikke regn med evt. fremmedinstrumenter).
*/


SELECT navn, kallenavn
FROM Person p NATURAL JOIN SpillerInstrumenter 
WHERE ant_instr =
(
	SELECT COUNT(DISTINCT instr)
	FROM Spiller
);

/*
Oppgave 7h)

Navn p? musiker som spilte p? mer enn ett fremmedinstrument p? en 
konsert. Ta med navn p? fremmedinstrumentene samt 
dato og sted for konserten.
*/

CREATE VIEW KonsFrInstr
SELECT k.pnr, k.kid, COUNT(DISTINCT instr) AS antFInstr
FROM Konsert k
WHERE NOT EXISTS
(
	SELECT instr
	FROM Spiller 
	WHERE k.pid = pid
)
GROUP BY k.pnr, k.kid;

SELECT navn, instr, dato, sted
FROM Konsert NATURAL JOIN KonsertInfo 
	NATURAL JOIN Person
	NATURAL JOIN KonsFrInstr
WHERE antFInstr > 1;



/*
Oppgave 7i)

Navn og fremmedinstrument for personer som ikke spiller noe 
instrument og som har deltatt p? mer enn 10 konserter med 
dette fremmedinstrumentet.
*/

SELECT navn, instr
FROM Person p NATURAL JOIN Konsert k
WHERE NOT EXISTS 
(
	SELECT pnr
	FROM Spiller
	WHERE pnr = p.pnr
)
GROUP BY pid, instr, navn
HAVING COUNT(kid) > 10;


/*
Oppgave 7j)

Navn p? musikere som har spilt p? alle konserter i Oslo i perioden 
20140710 - 20140719 
(fra og med 10. juli til og med 19. juli 2014).
*/

SELECT navn
FROM Person p NATURAL JOIN Konsert k NATURAL JOIN KonsertInfo ki
WHERE sted LIKE '%Oslo%'
	AND dato LIKE '2014071_';

/*
Oppgave 7k)

Et view med pid og navn for personer som har opptr?dt med f?rre 
enn 4 instrumenter p? minst ¨Śn konsert. Kall dette viewet Max3instr.
*/

CREATE VIEW Max3instr AS
SELECT pid, navn
FROM Konsert k NATURAL JOIN Person p
GROUP BY pid, kid, navn
HAVING COUNT(instr) < 4;


/*
Oppgave 7l)

Et view med informasjon om personer som har spilt (minst) et 
fremmedinstrument p? en konsert. Kall dette viewet FremmedSpiller.
*/

CREATE VIEW FremmedSpiller AS
SELECT DISTINCT pid
FROM Konsert k
WHERE k.instr NOT IN
(
	SELECT isntr
	FROM Spiller s
	WHERE k.pid = s.pid
);
--GROUP BY pid;

/*
Oppgave 7m)

Navn p? alle musikere som spiller minst fire instrumenter 
(fra tabellen Spiller) og som har spilt p? samtlige av disse 
og ingen fremmedinstrumenter p? alle konserter de har 
v?rt medvirkende.
*/

SELECT navn
FROM Person p
WHERE pid NOT IN (SELECT pid FROM FremmedSpiller)
AND pid NOT IN (SELECT pid FROM Max3instr);