enforced by constraint jhistdateinterval COMMENT ON COLUMN JOBHISTORYJOBID IS

Enforced by constraint jhistdateinterval comment on

This preview shows page 5 - 9 out of 11 pages.

(enforced by constraint jhist_date_interval)' ; COMMENT ON COLUMN JOB_HISTORY.JOB_ID IS 'Job role in which the employee worked in the past; foreign key to job_id column in the jobs table. A not null column.' ; COMMENT ON COLUMN JOB_HISTORY.DEPARTMENT_ID IS 'Department id in which the employee worked in the past; foreign key to deparment_id column in the departments table' ; CREATE INDEX JHIST_JOB_IX ON JOB_HISTORY ( JOB_ID ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE INDEX JHIST_EMP_IX ON JOB_HISTORY ( EMPLOYEE_ID ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE INDEX JHIST_DEPT_IX ON JOB_HISTORY ( DEPARTMENT_ID ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE UNIQUE INDEX JHIST_ID_DATE_PKX ON JOB_HISTORY
Image of page 5
( EMPLOYEE_ID ASC , START_DATE ASC ) ; ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_ID_DATE_PKz PRIMARY KEY ( EMPLOYEE_ID, START_DATE ) ; drop table locations cascade constraints; CREATE TABLE LOCATIONS ( LOCATION_ID NUMBER (4) NOT NULL , STREET_ADDRESS VARCHAR2 (40 BYTE) , POSTAL_CODE VARCHAR2 (12 BYTE) , CITY VARCHAR2 (30 BYTE) NOT NULL , STATE_PROVINCE VARCHAR2 (25 BYTE) , COUNTRY_ID CHAR (2 BYTE) ) LOGGING ; COMMENT ON COLUMN LOCATIONS.LOCATION_ID IS 'Primary key of locations table' ; COMMENT ON COLUMN LOCATIONS.STREET_ADDRESS IS 'Street address of an office, warehouse, or production site of a company. Contains building number and street name' ; COMMENT ON COLUMN LOCATIONS.POSTAL_CODE IS 'Postal code of the location of an office, warehouse, or production site of a company. ' ; COMMENT ON COLUMN LOCATIONS.CITY IS 'A not null column that shows city where an office, warehouse, or production site of a company is located. ' ; COMMENT ON COLUMN LOCATIONS.STATE_PROVINCE IS 'State or Province where an office, warehouse, or production site of a company is located.' ; COMMENT ON COLUMN LOCATIONS.COUNTRY_ID IS 'Country where an office, warehouse, or production site of a company is located. Foreign key to country_id column of the countries table.' ; CREATE INDEX LOC_CITY_IX ON LOCATIONS ( CITY ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE INDEX LOC_STATE_PROV_IX ON LOCATIONS (
Image of page 6
STATE_PROVINCE ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE INDEX LOC_COUNTRY_IX ON LOCATIONS ( COUNTRY_ID ASC ) LOGGING NOCOMPRESS NOPARALLEL ; CREATE UNIQUE INDEX LOC_ID_PKX ON LOCATIONS ( LOCATION_ID ASC ) ; ALTER TABLE LOCATIONS ADD CONSTRAINT LOC_ID_PKz PRIMARY KEY ( LOCATION_ID ) ; drop table regions cascade constraints; CREATE TABLE REGIONS ( REGION_ID NUMBER NOT NULL , REGION_NAME VARCHAR2 (25 BYTE) ) LOGGING ; CREATE UNIQUE INDEX REG_ID_PKX ON REGIONS ( REGION_ID ASC ) ; ALTER TABLE REGIONS ADD CONSTRAINT REG_ID_PKz PRIMARY KEY ( REGION_ID ) ; ALTER TABLE COUNTRIES ADD CONSTRAINT COUNTR_REG_FKz FOREIGN KEY ( REGION_ID ) REFERENCES REGIONS ( REGION_ID ) NOT DEFERRABLE ; ALTER TABLE DEPARTMENTS
Image of page 7
ADD CONSTRAINT DEPT_LOC_FKz FOREIGN KEY ( LOCATION_ID ) REFERENCES LOCATIONS ( LOCATION_ID ) NOT DEFERRABLE ; ALTER TABLE DEPARTMENTS ADD CONSTRAINT DEPT_MGR_FKz FOREIGN KEY ( MANAGER_ID ) REFERENCES EMPLOYEES (
Image of page 8
Image of page 9

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture