第4章

案例數(shù)據(jù)庫(kù)的創(chuàng)建與客戶端的連接案例數(shù)據(jù)庫(kù)分析與設(shè)計(jì)案例數(shù)據(jù)庫(kù)的創(chuàng)建客戶端與案例數(shù)據(jù)庫(kù)的連接配置4.1案例數(shù)據(jù)庫(kù)分析與設(shè)計(jì)案例數(shù)據(jù)庫(kù)的分析案例數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)案例數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)4.1.1案例數(shù)據(jù)庫(kù)的分析員工管理部門管理職位管理員工職位調(diào)動(dòng)管理企業(yè)所在區(qū)域管理企業(yè)所在國(guó)家管理企業(yè)所在位置管理4.1.2案例數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)4.1.3案例數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)REGIONS字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明region_idNUMBERPRIMARYKEY區(qū)域編號(hào)region_nameVARCHAR225NOTNULL區(qū)域名稱COUNTRIES字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明country_idCHAR2PRIMARKEY國(guó)家編號(hào)country_nameVARCHAR240NOTNULL國(guó)家名稱region_idNUMBERFOREIGNKEY所屬區(qū)域編號(hào)LOCATIONS字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明location_idNUMBER4PRIMARYKEY位置編號(hào)street_addressVARCHAR240街道postal_codeVARCHAR212郵政編碼cityVARCHAR230NOTNULL城市名稱state_provinceVARCHAR225州或省名稱country_idCHAR2FOREIGNKEY所屬國(guó)家編號(hào)DEPARTMENTS字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明department_idNUMBER4PRIMARYKEY部門編號(hào)department_nameVARCHAR230NOTNULL部門名稱manager_idNUMBER6部門管理者編號(hào)location_idNUMBER4FOREIGNKEY部門所屬位置編號(hào)JOBS字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明job_idVARCHAR210PRIMARYKEY職位編號(hào)job_titleVARCHAR235NOTNULL職位名稱min_salaryNUMBER6職位最低工資max_salaryNUMBER6職位最高工資EMPLOYEES字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明employee_idNUMBER6PRIMARYKEYfirst_nameVARCHAR220last_nameVARCHAR225NOTNULLemailVARCHAR225NOTNULLUNIQUEphone_numberVARCHAR220hire_dateDATENOTNULLjob_idVARCHAR210NOTNULLFOREIGNKEYsalaryNUMBER8,2大于0commission_pctNUMBER2,2manager_idNUMBER6FOREIGNKEYdepartment_idNUMBER4FOREIGNKEYJOB_HISTORY字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明employee_idNUMBER6PRIMARYKEYstart_dateDATEPRIMARYKEYend_dateDATENOTNULLend_date>start_datejob_idVARCHAR210NOTNULLdepartment_idNUMBER4FOREIGNKEY字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明gradeNUMBER2PRIMARYKEY工資等級(jí)min_salaryNUMBER6NOTNULL等級(jí)最低工資max_salaryNUMBER6NOTNULL等級(jí)最高工資SAL_GRADESUSERS字段名數(shù)據(jù)類型長(zhǎng)度約束說(shuō)明user_idNUMBER2PRIMARYKEY管理員編號(hào)user_nameCHAR20管理員名passwordVARCHAR220NOTNULL密碼序列的設(shè)計(jì)創(chuàng)建一個(gè)名為“EMPLOYEES_SEQ”的序列，用于產(chǎn)生員工編號(hào)，起始值為100，步長(zhǎng)為1，不緩存，不循環(huán)。創(chuàng)建一個(gè)名為“DEPARTMENTS_SEQ”的序列，用于產(chǎn)生部門編號(hào)，起始值為10，步長(zhǎng)為10，最大值為9990，不緩存，不循環(huán)。創(chuàng)建一個(gè)名為“LOCATIONS_SEQ”的序列，用于產(chǎn)生位置編號(hào)，起始值為1000，步長(zhǎng)為100，最大值為9990，不緩存，不循環(huán)。索引的設(shè)計(jì)在employees表的department_id列上創(chuàng)建名為“EMP_DEPARTMENT_INDX”的平衡樹(shù)索引。在employees表的job_id列上創(chuàng)建名為“EMP_JOB_INDX”的平衡樹(shù)索引。在employees表的manager_id列上創(chuàng)建名為“EMP_MANAGER_INDX”的平衡樹(shù)索引。在employees表的last_name，first_name列上創(chuàng)建名為“EMP_NAME_INDX”的復(fù)合索引。在departments表的location_id列上創(chuàng)建名為“DEPT_LOCATION_INDX”的平衡樹(shù)索引。在job_history表的job_id列上創(chuàng)建名為“JHIST_JOB_INDX”的平衡樹(shù)索引。在job_history表的employee_id列上創(chuàng)建名為“JHIST_EMP_INDX”的平衡樹(shù)索引。在job_history表的department_id列上創(chuàng)建名為“JHIST_DEPT_INDX”的平衡樹(shù)索引。在locations表的city列上創(chuàng)建名為“LOC_CITY_INDX”的平衡樹(shù)索引。在locations表的state_province列上創(chuàng)建名為“LOC_STATE_PROVINCE_INDX”的平衡樹(shù)索引。在locations表的country_id列上創(chuàng)建名為“LOC_COUNTRY_INDX”的平衡樹(shù)索引。視圖的設(shè)計(jì)用于員工信息綜合查詢的視圖名為“EMP_DETAILS_VIEW”，包括員工編號(hào)、員工名、工資、獎(jiǎng)金、職位編號(hào)、職位名稱、部門編號(hào)、部門名稱、部門所在地信息、國(guó)家信息、區(qū)域信息等。創(chuàng)建一個(gè)名為“DEPT_STAT_VIEW”的視圖，包含部門號(hào)、部門人數(shù)、部門平均工資、部門最高工資、部門最低工資以及部門工資總和。存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器的設(shè)計(jì)創(chuàng)建名為“PROC_SHOW_EMP”的存儲(chǔ)過(guò)程，以部門編號(hào)為參數(shù)，查詢并返回該部門平均工資，以及該部門中比該部門平均工資高的員工信息。創(chuàng)建名為“PROC_RETURN_DEPTINFO”的存儲(chǔ)過(guò)程，以部門編號(hào)為參數(shù)返回該部門的人數(shù)和平均工資。創(chuàng)建名為“PROC_SECURE_DML”的存儲(chǔ)過(guò)程，檢查當(dāng)前用戶操作時(shí)間是否為工作時(shí)間，即非周六、周日，時(shí)間為08：00-18：00。創(chuàng)建名為“FUNC_EMP_SALARY”的函數(shù)，以員工編號(hào)為參數(shù)，返回員工的工資。創(chuàng)建名為“FUNC_DEPT_MAXSAL”的函數(shù)，以部門編號(hào)為參數(shù)，返回部門最高工資。創(chuàng)建名為“FUNC_EMP_DEPT_AVGSAL