/* Dette script sletter eksisterende data og løser opgavesæt 1 til 4. Indholdet kan kopieres direkte ind i en SQL-prompt eller scriptet kan kaldes fra en SQL-prompt med kommandoen "@opgave_4.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) ); /* Der foretages ændringer i tabelstrukturerne i henhold til opgaveteksten i SQL-opgaver 3. */ -- opg. 1 RENAME postnummer TO postnumre; -- opg. 2 ALTER TABLE ordrer ADD status VARCHAR2(10) NOT NULL; -- opg. 3 ALTER TABLE ordrer ADD CONSTRAINT ordrer_status_cc CHECK (status IN ('oprettet', 'leveret', 'betalt', 'annulleret')); -- opg. 4 ALTER TABLE kunder ADD ( telefonnr NUMBER(8), email VARCHAR2(40) ); -- opg. 5 ALTER TABLE kunder ADD CONSTRAINT kunder_telefonnr_8_cc CHECK (LENGTH(telefonnr) = 8); -- opg. 6 ALTER TABLE kunder MODIFY kunde VARCHAR2(40); -- opg. 7 SELECT * FROM user_cons_columns NATURAL JOIN user_constraints; ALTER TABLE postnumre DROP CONSTRAINT postnummer_bynavn_uk; -- opg. 8 ALTER TABLE ordrer ADD ansvarlig_id NUMBER(6); -- opg. 9 ALTER TABLE ordrer ADD CONSTRAINT ordrer_medarbejdere_fk FOREIGN KEY (ansvarlig_id) REFERENCES medarbejdere (medarbejder_id); /* Data indsættes i tabellerne i henhold til opgaveteksten i SQL-opgaver 4. Først slettes evt. eksisterende data. */ 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; DELETE ordrelinie; DELETE ordrelinier; DELETE ordre; DELETE ordrer; DELETE medarbejder; DELETE medarbejdere; DELETE vare; DELETE varer; DELETE kunde; DELETE kunder; DELETE postnummer; DELETE postnumre; DELETE postnumrer; CREATE SEQUENCE kunde_seq INCREMENT BY 1 START WITH 1001; CREATE SEQUENCE vare_seq INCREMENT BY 1 START WITH 101; CREATE SEQUENCE ordre_seq INCREMENT BY 1 START WITH 1; CREATE SEQUENCE ordrelinie_seq INCREMENT BY 1 START WITH 1; INSERT INTO postnumre (postnr, bynavn) VALUES (9200, 'Aalborg SV'); INSERT INTO postnumre VALUES (9000, 'Aalborg'); INSERT INTO postnumre VALUES (8200, 'Århus N'); INSERT INTO kunder VALUES (kunde_seq.nextval, 'IDEmøbler', 'Nålemagervej 6', 9000, 98138744, 'aalborg@ide.dk'); INSERT INTO kunder (kunde_id, kunde, adresse, postnr) VALUES (kunde_seq.nextval, 'IKEA', 'Graham Bells vej 9-11', 8200); INSERT INTO varer VALUES (vare_seq.nextval, 'Sofabord', 799.50); INSERT INTO varer VALUES (vare_seq.nextval, 'TV-bord', 349); INSERT INTO varer VALUES (vare_seq.nextval, '2-personers sofa', 2199); SELECT * FROM afdelinger; INSERT INTO medarbejdere (medarbejder_id, fornavn, efternavn, loen, email, afdeling_id) VALUES (1030, 'Bent Møller', 'Madsen', 25000, 'bmm@business.aau.dk', 20); INSERT INTO ordrer (ordre_id, oprettelsesdato, leveringsdato, kunde_id, status, ansvarlig_id) VALUES (ordre_seq.nextval, SYSDATE, SYSDATE + 14, (SELECT kunde_id FROM kunder WHERE kunde = 'IKEA'), 'oprettet', 1030); INSERT INTO ordrelinier (ordrelinie_id, ordre_id, vare_id, antal) VALUES (ordrelinie_seq.nextval, 1, 101, 150); INSERT INTO ordrelinier VALUES (ordrelinie_seq.nextval, (select max(ordre_id) from ordrer), 102, 50); INSERT INTO ordrelinier VALUES (ordrelinie_seq.nextval, 1, (SELECT vare_id FROM varer WHERE vare = '2-personers sofa'), 80); ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'; UPDATE ordrer SET status = 'leveret', betalingsdato = '30-09-2006' WHERE ordre_id = 1; COMMIT; SELECT * FROM kunder NATURAL JOIN ordrer NATURAL JOIN ordrelinier NATURAL JOIN varer;