/*
Dette script sletter eksisterende data og løser opgavesæt 1 og 2.

Indholdet kan kopieres direkte ind i en SQL-prompt eller scriptet kan kaldes fra en SQL-prompt med kommandoen "@<sti til fil>opgave_2.txt".

Ved anvendelse af Oracles SQL*Plus kan det give problemer at kopiere indholdet direkte ind i prompten, fordi der kun kan indsættes en relativ begrænset mængde data i SQL*Plus af gangen.
*/


/*
Eksisterende tabeller slettes mht. at de studerende evt. kan have navngivet tabeller anderledes. 
*/

DROP TABLE ordrelinie;
DROP TABLE ordrelinier;
DROP TABLE ordrerlinie;
DROP TABLE ordrerlinier;
DROP TABLE ordrelinje;
DROP TABLE ordrelinjer;
DROP TABLE ordrerlinje;
DROP TABLE ordrerlinjer;
DROP TABLE ordre;
DROP TABLE ordrer;
DROP TABLE medarb;
DROP TABLE medarbejder;
DROP TABLE medarbejdere;
DROP TABLE vare;
DROP TABLE varer;
DROP TABLE kunde;
DROP TABLE kunder;
DROP TABLE postnr;
DROP TABLE postnummer;
DROP TABLE postnumre;
DROP TABLE afdeling;
DROP TABLE afdelinger;

DROP VIEW kunde_vare;
DROP VIEW kunde_varer;
DROP VIEW kunder_vare;
DROP VIEW kunder_varer;

DROP SEQUENCE kunde_seq;
DROP SEQUENCE kunder_seq;
DROP SEQUENCE vare_seq;
DROP SEQUENCE varer_seq;
DROP SEQUENCE ordre_seq;
DROP SEQUENCE ordrer_seq;
DROP SEQUENCE ordrelinie_seq;
DROP SEQUENCE ordrelinier_seq;

PURGE RECYCLEBIN;

-- Nye tabeller oprettes i henhold til opgaveteksten (SQL-opgaver 1 og 2)

CREATE TABLE afdelinger
(
afdeling_id	NUMBER(6) NOT NULL,
afdeling	VARCHAR2(30) NOT NULL,
afdeling_far	NUMBER(6)
);

ALTER TABLE afdelinger
ADD CONSTRAINT afdelinger_pk PRIMARY KEY (afdeling_id);

ALTER TABLE afdelinger
ADD CONSTRAINT afdelinger_afdelinger_fk FOREIGN KEY (afdeling_far)
REFERENCES afdelinger (afdeling_id);

ALTER TABLE afdelinger
ADD CONSTRAINT afdelinger_afdeling_far_check CHECK (afdeling_id != afdeling_far);

INSERT INTO afdelinger VALUES
(10,'Virksomhed',null);

INSERT INTO afdelinger VALUES
(40,'Produktion',10);

INSERT INTO afdelinger VALUES
(30,'Indkøb',10);

INSERT INTO afdelinger VALUES
(20,'Salg',10);

INSERT INTO afdelinger VALUES
(50,'Udvikling',40);

COMMIT;

CREATE TABLE medarbejdere
(
medarbejder_id	NUMBER(6),
fornavn		VARCHAR2(30),
efternavn	VARCHAR2(40) NOT NULL,
loen		NUMBER(8,2),
email		VARCHAR2(30),
afdeling_id	NUMBER(6),
CONSTRAINT medarbejdere_pk PRIMARY KEY (medarbejder_id),
CONSTRAINT medarbejdere_afdelinger_fk FOREIGN KEY (afdeling_id) REFERENCES afdelinger (afdeling_id),
CONSTRAINT email_snabela_cc CHECK (email LIKE '%@%')
);

CREATE TABLE postnummer
(
postnr NUMBER(4),
bynavn VARCHAR2(30) NOT NULL,
CONSTRAINT postnummer_pk PRIMARY KEY (postnr),
CONSTRAINT postnummer_bynavn_uk UNIQUE (bynavn),
CONSTRAINT postnummer_postnr_cc CHECK (LENGTH(postnr)=4)
);

CREATE TABLE kunder
(
kunde_id NUMBER(6),
kunde VARCHAR2(30) NOT NULL,
adresse VARCHAR2(30),
postnr NUMBER(4), 
CONSTRAINT kunder_pk PRIMARY KEY (kunde_id),
CONSTRAINT kunder_kunde_adresse_postnr_uk UNIQUE (kunde,adresse,postnr),
CONSTRAINT kunder_postnummer_fk FOREIGN KEY (postnr) 
REFERENCES postnummer (postnr)
);

CREATE TABLE varer
(
vare_id NUMBER(3),
vare VARCHAR2(30) NOT NULL,
pris NUMBER(8,2),
CONSTRAINT varer_pk PRIMARY KEY (vare_id)
);

CREATE TABLE ordrer
(
ordre_id NUMBER(6),
oprettelsesdato DATE NOT NULL, 
leveringsdato DATE,
betalingsdato DATE,
kunde_id NUMBER(6),
CONSTRAINT ordrer_pk PRIMARY KEY (ordre_id),
CONSTRAINT ordrer_kunder_fk FOREIGN KEY (kunde_id) 
REFERENCES kunder (kunde_id),
CONSTRAINT ordrer_levdato_efter_opdato_cc CHECK (leveringsdato >= oprettelsesdato)
);

CREATE TABLE ordrelinier
(
ordrelinie_id NUMBER(6),
ordre_id NUMBER(6),
vare_id NUMBER(6),
antal NUMBER(6,2),
CONSTRAINT ordrelinier_pk PRIMARY KEY (ordrelinie_id),
CONSTRAINT ordrelinier_ordre_fk FOREIGN KEY (ordre_id) 
REFERENCES ordrer (ordre_id),
CONSTRAINT ordrelinier_varer_fk FOREIGN KEY (vare_id) 
REFERENCES varer (vare_id)
);