REM user: HXHWB 建立用戶
create user HXHWB 
identified by ok
default tablespace USER_DATA
temporary tablespace temp_USER_DATA
quota unlimited on USER_DATA
quota unlimited on Temp_USER_DATA
quota unlimited on Index_USER_DATA;

REM 授給用戶HXHWB 的權限
grant connect,resource,DBA to HXHWB ;

REM 3.3.1 創建員工基本狀況表格EMPLOYEE_BASIC
CREATE  TABLE  "HXHWB".EMPLOYEE_BASIC 
(
EMP_NO  NUMBER(6)  NOT NULL, 
EMP_NAME  VARCHAR2(10)  NULL, 
DEPT_ID  NUMBER(3)  NULL, 
EMP_GENDER  VARCHAR2(2)  NULL, 
EMP_BIRTHDAY  DATE  NULL, 
EMP_HOMETOWN  VARCHAR2(8)  NULL, 
EMP_COUNTRY  VARCHAR2(10)  NULL, 
EMP_NATION  VARCHAR2(10)  NULL, 
EMP_ID  NUMBER(20)  NULL, 
EMP_MARRIAGE  VARCHAR2(2)  NULL, 
EMP_HEALTH  VARCHAR2(20)  NULL, 
EMP_ZZMM  VARCHAR2(4)  NULL, 
EMP_ZZMM_DATE  DATE  NULL, 
EMP_BLOOD  VARCHAR2(2)  NULL, 
EMP_STARTWORK  DATE  NULL, 
EMP_STATE  VARCHAR2(10)  NULL, 
EMP_STATE_DATE  DATE  NULL, 
EMP_HOMEADRESS  VARCHAR2(10)  NULL, 
EMP_TELENO  NUMBER(10)  NULL, 
EMP_EMAIL  VARCHAR2(20)  NULL, 
JOB_ID  NUMBER(3 )  NULL,  
CONSTRAINT  EMP_NO_PK  PRIMARY KEY (EMP_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.2 創建員工婚姻狀況的表格EMPLOYEE_MARRIAGE
CREATE  TABLE  "HXHWB".EMPLOYEE_MARRIAGE 
(
EMP_NO  NUMBER(6)  NOT NULL, 
LOVER_NO  NUMBER(6)  NOT NULL, 
LOVER_NAME  VARCHAR2(10)  NULL, 
LOVER_BIRTHDAY  DATE  NULL, 
MARRIAGE_DATE  DATE  NULL, 
LOVER_COMPANY  VARCHAR2(20)  NULL, 
LOVER_ZZMM  VARCHAR2(4)  NULL, 
LOVER_JOB  VARCHAR2(10)  NULL,  
CONSTRAINT  EMP_NO_FK  FOREIGN KEY (EMP_NO) 
REFERENCES  "HXHWB"."EMPLOYEE_BASIC"(EMP_NO),  
CONSTRAINT  LOVER_NO_UNQ  UNIQUE  (LOVER_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.3 創建員工學歷狀況表格EMPLOYEE_ SCHOOL
CREATE  TABLE  "HXHWB".EMPLOYEE_SCHOOL 
(
EMP_NO  NUMBER(6)  NOT NULL,
EMP_XL  VARCHAR2(10)  NULL, 
EMP_MAJOR  VARCHAR2(10)  NULL, 
BY_DATE  DATE  NULL, 
BY_SCHOOL  VARCHAR2(20)  NULL, 
SCHOOL_CLASS  VARCHAR2(6)  NULL, 
FOREIGN_LANGUAGE1  VARCHAR2(6)  NULL, 
FOREIGN_LANGUAGE1_CLASS  VARCHAR2(6)  NULL, 
FOREIGN_LANGUAGE2  VARCHAR2(6)  NULL,  
FOREIGN_LANGUAGE2_CLASS  VARCHAR2(6)  NULL, 
CONSTRAINT  SCHOOL_EMP_NO_FK  FOREIGN KEY (EMP_NO) 
REFERENCES  "HXHWB"."EMPLOYEE_BASIC"(EMP_NO)
)  
TABLESPACE "USER_DATA";
REM 3.3.4 創建工作崗位情況表JOB
CREATE  TABLE  "HXHWB". JOB
(
JOB_ID  NUMBER(3)  NOT NULL,
JOB_NAME  VARCHAR2(10)  NULL, 
JOB_POWER  VARCHAR2(20)  NULL, 
JOB_TASK  VARCHAR2(20)  NULL,
CONSTRAINT  JOB_ID_PK  PRIMARY KEY (JOB_ID)
)  
TABLESPACE "USER_DATA";
REM 3.3.5 創建部門信息表DEPT
CREATE  TABLE  "HXHWB".DEPT
(
DEPT_ID  NUMBER(3)  NOT NULL,
DEPT_NAME  VARCHAR2(10)  NULL, 
DEPT_DESC   VARCHAR2(30)  NULL,
DEPT_MANAGER  NUMBER(6)  NULL, 
DEPT_VICEMANAGER  NUMBER(6)  NULL,
CONSTRAINT  DEPT_ID_PK  PRIMARY KEY (DEPT_ID),
CONSTRAINT  DEPT_MANAGER_FK  FOREIGN KEY (DEPT_MANAGER) 
REFERENCES  "HXHWB"."EMPLOYEE_BASIC"(EMP_NO),
CONSTRAINT  DEPT_VICEMANAGER_FK FOREIGN KEY 
               (DEPT_VICEMANAGER) 
REFERENCES  "HXHWB"."EMPLOYEE_BASIC"(EMP_NO)
)  
TABLESPACE "USER_DATA";
REM 3.3.6 增加EMPLOYEE_BASIC表格的外部鍵
ALTER  TABLE  "HXHWB"."EMPLOYEE_BASIC" ADD
( 
CONSTRAINT  DEPT_ID_FK  FOREIGN KEY (DEPT_ID) 
REFERENCES  "HXHWB"."DEPT"(DEPT_ID),  
CONSTRAINT  JOB_ID_FK  FOREIGN KEY (JOB_ID) 
REFERENCES "HXHWB"."JOB"(JOB_ID)
);