-- drop stuff DROP TABLE empv PURGE; DROP TABLE deptv PURGE; DROP TABLE jobv PURGE; DROP TABLE emp PURGE; DROP TABLE dept PURGE; DROP TABLE job PURGE; -- DDL Generated by Oracle SQL Developer Data Modeler CREATE TABLE DEPT ( DEPTNO NUMBER (2) NOT NULL ) LOGGING ; ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY ( DEPTNO ) ; CREATE TABLE DEPTV ( DEPTVID NUMBER (10) NOT NULL , DEPTNO NUMBER (2) NOT NULL , DNAME VARCHAR2 (14) , LOC VARCHAR2 (13) , VALID_FROM DATE NOT NULL , VALID_TO DATE NOT NULL ) LOGGING ; CREATE INDEX DEPTV_DEPT_FK_I ON DEPTV ( DEPTNO ASC ) LOGGING ; ALTER TABLE DEPTV ADD CONSTRAINT DEPTV_PK PRIMARY KEY ( DEPTVID ) ; CREATE TABLE EMP ( EMPNO NUMBER (4) NOT NULL ) LOGGING ; ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY ( EMPNO ) ; CREATE TABLE EMPV ( EMPVID NUMBER (10) NOT NULL , EMPNO NUMBER (4) NOT NULL , ENAME VARCHAR2 (14) NOT NULL , JOBNO NUMBER (2) NOT NULL , MGR NUMBER (4) , HIREDATE DATE , SAL NUMBER (7,2) , COMM NUMBER (7,2) , DEPTNO NUMBER (2) NOT NULL , VALID_FROM DATE NOT NULL , VALID_TO DATE NOT NULL ) LOGGING ; CREATE INDEX EMPV_EMP_FK_I ON EMPV ( EMPNO ASC ) LOGGING ; CREATE INDEX EMPV_JOB_FK_I ON EMPV ( JOBNO ASC ) LOGGING ; CREATE INDEX EMPV_DEPT_FK_I ON EMPV ( DEPTNO ASC ) LOGGING ; CREATE INDEX EMPV_EMP_FK2_I ON EMPV ( MGR ASC ) LOGGING ; ALTER TABLE EMPV ADD CONSTRAINT EMPV_PK PRIMARY KEY ( EMPVID ) ; CREATE TABLE JOB ( JOBNO NUMBER (2) NOT NULL ) LOGGING ; ALTER TABLE JOB ADD CONSTRAINT JOB_PK PRIMARY KEY ( JOBNO ) ; CREATE TABLE JOBV ( JOBVID NUMBER (10) NOT NULL , JOBNO NUMBER (2) NOT NULL , JOB VARCHAR2 (9) NOT NULL , VALID_FROM DATE NOT NULL , VALID_TO DATE NOT NULL ) LOGGING ; CREATE INDEX JOBV_JOB_FK_I ON JOBV ( JOBNO ASC ) LOGGING ; ALTER TABLE JOBV ADD CONSTRAINT JOBV_PK PRIMARY KEY ( JOBVID ) ; ALTER TABLE DEPTV ADD CONSTRAINT DEPTV_DEPT_FK FOREIGN KEY ( DEPTNO ) REFERENCES DEPT ( DEPTNO ) NOT DEFERRABLE ; ALTER TABLE EMPV ADD CONSTRAINT EMPV_DEPT_FK FOREIGN KEY ( DEPTNO ) REFERENCES DEPT ( DEPTNO ) NOT DEFERRABLE ; ALTER TABLE EMPV ADD CONSTRAINT EMPV_EMP_FK FOREIGN KEY ( EMPNO ) REFERENCES EMP ( EMPNO ) NOT DEFERRABLE ; ALTER TABLE EMPV ADD CONSTRAINT EMPV_EMP_MGR_FK FOREIGN KEY ( MGR ) REFERENCES EMP ( EMPNO ) NOT DEFERRABLE ; ALTER TABLE EMPV ADD CONSTRAINT EMPV_JOB_FK FOREIGN KEY ( JOBNO ) REFERENCES JOB ( JOBNO ) NOT DEFERRABLE ; ALTER TABLE JOBV ADD CONSTRAINT JOBV_JOB_FK FOREIGN KEY ( JOBNO ) REFERENCES JOB ( JOBNO ) NOT DEFERRABLE ; -- Initial Data as found in schema SCOTT but amended to fit the adapted model INSERT INTO dept (deptno) SELECT 10 FROM dual UNION ALL SELECT 20 FROM dual UNION ALL SELECT 30 FROM dual UNION ALL SELECT 40 FROM dual; COMMIT; INSERT INTO deptv (deptvid, deptno, dname, loc, valid_from, valid_to) SELECT 1, 10, 'ACCOUNTING', 'NEW YORK', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 2, 20, 'RESEARCH', 'DALLAS', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 3, 30, 'SALES', 'CHICAGO', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 4, 40, 'OPERATIONS', 'BOSTON', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL; COMMIT; INSERT INTO job (jobno) SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 5; COMMIT; INSERT INTO jobv (jobvid, jobno, job, valid_from, valid_to) SELECT 1, 1, 'CLERK', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 2, 2, 'SALESMAN', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 3, 3, 'PRESIDENT', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 4, 4, 'MANAGER', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL UNION ALL SELECT 5, 5, 'ANALYST', DATE '1980-01-01', DATE '9999-12-31' FROM DUAL; COMMIT; INSERT INTO emp (empno) select 7369 from dual union all select 7499 from dual union all select 7521 from dual union all select 7566 from dual union all select 7654 from dual union all select 7698 from dual union all select 7782 from dual union all select 7788 from dual union all select 7839 from dual union all select 7844 from dual union all select 7876 from dual union all select 7900 from dual union all select 7902 from dual union all select 7934 from dual; COMMIT; INSERT INTO empv (empvid, empno, ename, jobno, mgr, hiredate, sal, comm, deptno, valid_from, valid_to) select 1, 7369, 'SMITH',1, 7902, DATE '1980-12-17', 800, NULL, 20, DATE '1980-12-17', DATE '9999-12-31' from dual union all select 2, 7499, 'ALLEN',2, 7698, DATE '1981-02-20', 1600, 300, 30, DATE '1981-02-20', DATE '9999-12-31' from dual union all select 3, 7521, 'WARD',2, 7698, DATE '1981-02-22', 1250, 500, 30, DATE '1981-02-22', DATE '9999-12-31' from dual union all select 4, 7566, 'JONES',4, 7839, DATE '1981-04-02', 2975, NULL, 20, DATE '1981-04-02', DATE '9999-12-31' from dual union all select 5, 7654, 'MARTIN',2, 7698, DATE '1981-09-28', 1250, 1400, 30, DATE '1981-09-28', DATE '9999-12-31' from dual union all select 6, 7698, 'BLAKE',4, 7839, DATE '1981-05-01', 2850, NULL, 30, DATE '1981-05-01', DATE '9999-12-31' from dual union all select 7, 7782, 'CLARK',4, 7839, DATE '1981-06-09', 2450, NULL, 10, DATE '1981-06-09', DATE '9999-12-31' from dual union all select 8, 7788, 'SCOTT',5, 7566, DATE '1987-04-19', 3000, NULL, 20, DATE '1987-04-19', DATE '9999-12-31' from dual union all select 9, 7839, 'KING',3, NULL, DATE '1981-11-17', 5000, NULL, 10, DATE '1981-11-17', DATE '9999-12-31' from dual union all select 10, 7844, 'TURNER',2, 7698, DATE '1981-09-08', 1500, 0, 30, DATE '1981-09-08', DATE '9999-12-31' from dual union all select 11, 7876, 'ADAMS',1, 7788, DATE '1987-05-23', 1100, NULL, 20, DATE '1987-05-23', DATE '9999-12-31' from dual union all select 12, 7900, 'JAMES',1, 7698, DATE '1981-12-03', 950, NULL, 30, DATE '1981-12-03', DATE '9999-12-31' from dual union all select 13, 7902, 'FORD',5, 7566, DATE '1981-12-03', 3000, NULL, 20, DATE '1981-12-03', DATE '9999-12-31' from dual union all select 14, 7934, 'MILLER',1, 7782, DATE '1982-01-23', 1300, NULL, 10, DATE '1982-01-23', DATE '9999-12-31' from dual ; COMMIT; -- Event 1: update ename INSERT INTO empv (empvid, empno, ename, jobno, mgr, hiredate, sal, comm, deptno, valid_from, valid_to) SELECT empvid+14, empno, INITCAP(ename), jobno, mgr, hiredate, sal, comm, deptno, DATE '1990-01-01', DATE '9999-12-31' FROM empv; UPDATE empv SET valid_to = DATE '1989-12-31' WHERE empvid <=14; COMMIT; -- Event 2: update job name INSERT INTO jobv (jobvid, jobno, job, valid_from, valid_to) SELECT jobvid+5, jobno, INITCAP(job), DATE '1990-01-21', DATE '9999-12-31' FROM jobv; UPDATE jobv SET valid_to = DATE '1990-01-20' WHERE jobvid <=5; COMMIT; -- Event 3: update dname INSERT INTO deptv (deptvid, deptno, dname, loc, valid_from, valid_to) SELECT deptvid+4, deptno, INITCAP(dname), loc, DATE '1990-03-01', DATE '9999-12-31' FROM deptv; UPDATE deptv SET valid_to = DATE '1990-02-28' WHERE deptvid <=4; COMMIT; -- Event 4: update loc INSERT INTO deptv (deptvid, deptno, dname, loc, valid_from, valid_to) SELECT deptvid+4, deptno, dname, initcap(loc), DATE '1990-04-01', DATE '9999-12-31' FROM deptv WHERE deptvid > 4; UPDATE deptv SET valid_to = DATE '1990-03-31' WHERE deptvid BETWEEN 5 AND 8; COMMIT; -- Event 5: salary increase INSERT INTO empv (empvid, empno, ename, jobno, mgr, hiredate, sal, comm, deptno, valid_from, valid_to) SELECT empvid+14, empno, ename, jobno, mgr, hiredate, sal*1.1, comm, deptno, DATE '1991-04-01', DATE '9999-12-31' FROM empv WHERE empvid>14; UPDATE empv SET valid_to = DATE '1991-03-31' WHERE empvid BETWEEN 15 AND 28; COMMIT; -- Event 6: Scott leaving UPDATE empv SET valid_to = DATE '1991-07-31' WHERE empvid = 36; COMMIT; -- Event 7: Scott comming back with better salary INSERT INTO empv (empvid, empno, ename, jobno, mgr, hiredate, sal, comm, deptno, valid_from, valid_to) VALUES (43, 7788, 'Scott',5, 7566, DATE '1992-01-01', 3500, NULL, 20, DATE '1992-01-01', DATE '9999-12-31'); COMMIT; -- Event 8: Create a gap for Job type "Analyst" (just a single day) UPDATE jobv SET valid_from = DATE '1990-01-22' WHERE jobvid = 10; COMMIT; -- Gather statistics BEGIN FOR l_rec IN (SELECT table_name FROM user_tables WHERE table_name IN ('EMP', 'EMPV', 'DEPT', 'DEPTV', 'JOB', 'JOBV')) LOOP dbms_stats.gather_table_stats(ownname => USER, tabname => l_rec.table_name, method_opt => 'for all columns size 1', estimate_percent => 100); END LOOP; END; /