DROP TABLE fagvalg;
DROP TABLE fag;
DROP TABLE student;
DROP TABLE eksamens_skole;
DROP TABLE sted;
DROP TABLE ny_sted

DROP SEQUENCE stud_teller;
DROP SEQUENCE eksskole_teller;

CREATE TABLE sted (
        postnr CHAR(4) NOT NULL,
        poststed VARCHAR(30));

CREATE TABLE ny_sted (
        post_nr CHAR(4) NOT NULL,
        poststed VARCHAR(30));

CREATE TABLE eksamens_skole (
        eks_skolenr INTEGER NOT NULL,
        eks_navn VARCHAR(30),
        eks_adresse VARCHAR(30),
        eks_telefon CHAR(8),
        eks_faks CHAR(8),
        eks_kontakt VARCHAR(30),
        postnr CHAR(4) NOT NULL );

CREATE TABLE student (
        studnr INTEGER NOT NULL,
        etternavn VARCHAR(30),
        fornavn VARCHAR(25),
        adresse VARCHAR(30),
        telefon CHAR(8),
        fdato DATE,
        epost VARCHAR(30),
       eks_skolenr INTEGER NOT NULL,
        postnr CHAR(4) NOT NULL);

CREATE TABLE fag (
        fagkode CHAR(6)NOT NULL,
        fagnavn VARCHAR(30),
        eks_dato DATE,
        vekttall INTEGER,
        brukernavn CHAR(3),
        passord CHAR(4));

CREATE TABLE fagvalg (
        studnr INTEGER NOT NULL, 
        fagkode CHAR(6) NOT NULL,
        semester CHAR(3),
        betalt VARCHAR(3),
        karakter REAL);


ALTER TABLE sted
        ADD CONSTRAINT sted_pk PRIMARY KEY (postnr);

ALTER TABLE ny_sted
        ADD CONSTRAINT ny_sted_pk PRIMARY KEY (post_nr);

ALTER TABLE student
        ADD CONSTRAINT student_pk PRIMARY KEY (studnr);

ALTER TABLE eksamens_skole
	ADD CONSTRAINT eksamens_skole_pk PRIMARY KEY (eks_skolenr);

ALTER TABLE fag
	ADD CONSTRAINT fag_pk PRIMARY KEY (fagkode);

ALTER TABLE fagvalg
	ADD CONSTRAINT fagvalg_pk PRIMARY KEY (studnr, fagkode);


ALTER TABLE student
        ADD CONSTRAINT student_fk1 FOREIGN KEY(postnr)REFERENCES sted (postnr);
ALTER TABLE student
        ADD CONSTRAINT student_fk2 FOREIGN KEY(eks_skolenr)REFERENCES eksamens_skole (eks_skolenr);

ALTER TABLE eksamens_skole
        ADD CONSTRAINT eksamens_skole_fk FOREIGN KEY(postnr)REFERENCES sted (postnr);

ALTER TABLE fagvalg
        ADD CONSTRAINT fagvalg_fk1 FOREIGN KEY(studnr)REFERENCES student (studnr);

ALTER TABLE fagvalg
        ADD CONSTRAINT fagvalg_fk2 FOREIGN KEY(fagkode)REFERENCES fag (fagkode);


ALTER TABLE fag
        ADD CONSTRAINT fag_unik UNIQUE(fagnavn);
ALTER TABLE fag
	ADD CONSTRAINT vekttall_sjekk CHECK( vekttall BETWEEN 0 AND 7);


CREATE SEQUENCE stud_teller INCREMENT 1 START 101;
CREATE SEQUENCE eksskole_teller INCREMENT 1 START 1;


INSERT INTO sted VALUES('7563','MALVIK');
INSERT INTO sted VALUES('7223','MELHUS');
INSERT INTO sted VALUES('9900','KIRKENES');
INSERT INTO sted VALUES('3490','GRIMSTAD');
INSERT INTO sted VALUES('7005','TRONDHEIM');
INSERT INTO sted VALUES('7078','SAUPSTAD');


INSERT INTO ny_sted VALUES('0313','OSLO');
INSERT INTO ny_sted VALUES('6408','AUREOSEN');
INSERT INTO ny_sted VALUES('7563','MALVIK');
INSERT INTO ny_sted VALUES('7223','MELHUS');

INSERT INTO fag VALUES('LO343D','GRUNNKURS EDB','1997-05-12',3,'GRK','H03R');
INSERT INTO fag VALUES('LO323D','DATABASER','1997-12-14',2,'DBS','K45Y');
INSERT INTO fag VALUES('LO248D','DATAKOMMUNIKASJON','1997-12-08',2,'DKO','P67T');
INSERT INTO fag VALUES('LO370D','WEBTEKNIKKER','1997-12-12',2,'WTR','W21K');
INSERT INTO fag VALUES('LV338D','DATABASEADMINISTRASJON','1997-12-17', 2, 'DBA', 'T32C');

INSERT INTO eksamens_skole
 VALUES(NEXTVAL('eksskole_teller'),'HiST','E.C.DAHLS GATE 2','73559500','73559520','MAY JENSEN','7005');
INSERT INTO eksamens_skole
 VALUES(NEXTVAL('eksskole_teller'),'KIRKENES VGS','V.ULVANGS VEI 10','78235004','78235014','ARILD SIVERTSEN','9900');
INSERT INTO eksamens_skole
 VALUES(NEXTVAL('eksskole_teller'),'HiA','A.HAMSUNS GATE 3','3901764','39017650','BODIL BRENDE','3490');

INSERT INTO student 
VALUES(NEXTVAL('stud_teller'),'HANSEN','OLE','KOMBINERTKROKEN 3','73341200','15.03.74','OLE@ONLINE.NO',1,'7563');
INSERT INTO student
 VALUES(NEXTVAL('stud_teller'),'JENSEN','KAREN','ELGVEIEN 45','78203021','10.11.69','JENSENK@CI2NET.NO',2,'9900');
INSERT INTO student
 VALUES(NEXTVAL('stud_teller'),'TORMODSEN','PER','KONGSVEIEN 13','72760097','23.07.71','PERT@VESTNETT.NO',1,'7223');
INSERT INTO student
 VALUES(NEXTVAL('stud_teller'),'VIK','KARI','PRINSENS GATE 34','39017645','04.06.70','KARI@AGDERNETT.NO',3,'3490');
INSERT INTO student
 VALUES(NEXTVAL('stud_teller'),'HANSEN','NILS','SAUPSTADRINGEN 39A','72594211','05.12.71','NILS.HANSEN@ONLINE.NO',1,'7078');

INSERT INTO fagvalg VALUES(101,'LO343D','H97','JA',2.1);
INSERT INTO fagvalg VALUES(101,'LO323D','H97','JA',2.5);
INSERT INTO fagvalg VALUES(102,'LO370D','H97','JA',1.5);
INSERT INTO fagvalg VALUES(102,'LO323D','V98','JA',2.8);
INSERT INTO fagvalg VALUES(102,'LO248D','V98','JA',1.8);
INSERT INTO fagvalg VALUES(103,'LO323D','H97','NEI',1.3);
INSERT INTO fagvalg VALUES(104,'LO248D','H97','JA',3.3);
INSERT INTO fagvalg VALUES(104,'LO323D','H97','NEI',NULL);
INSERT INTO fagvalg VALUES(105,'LO323D','H97','JA',1.9);