DatabaseProject

Database final Project

資料庫實做題目(二) 中華民國109年11月8日

有一個國家之外交部公開徵求廠商或個人來設計及實作一個員工外派資訊系統，功能如下，現在請您依下列需求設計及實作這個系統：

一、資料庫部分，包括建立：

1. 員工基本資料表，用以存放員工基本資料，包括：員工姓名、員工身分證字號、職等、薪資、電話、性別、出生年月日、錄用日期、住址與照片等。其中，

「員工姓名」為十四位元組(byte)之字串， 「員工身分證字號」為十位元組之字串，亦是本資料表之關鍵屬性(key attribute)， 「職等」為十位元組之字串， 「薪資」為八碼之整數， 「電話」為十四位元組之字串(限用0~9之數字，但是以字串呈現)， 「性別」為一位元組之字(character)， 「出生年月日」與「錄用日期」格式為yyyy/mm/dd (以下所有日期均為此格式)， 「住址」為三十位元組之字串， 「照片」請從MS Word美工圖片中的「人物」取出貼上。

例如，(呂溪東，B123456789，簡任十等，80000， 886-2-6666-6666，男，1970/11/02，2012/11/03，台中市台灣大道四段一七二七號， )

2. 國家資料表，用以存放員工派駐國家之國家資料，包括：國家代碼、國家名稱、所屬洲名、該國元首姓名、該國外交部長姓名、該國聯絡人姓名、該國人口數、該國領土面積、連絡電話與是否為邦交國。其中，

「國家代碼」為六位元組之字串，其中前二碼必須是英文大寫字母(即，限AAZZ)，後四碼限用09之數字，本欄位亦是本資料表之關鍵屬性， 「國家名稱」為十四位元組之字串， 「所屬洲名」為六位元組之字串， 「該國元首姓名」，「該國外交部長姓名」與「該國聯絡人姓名」皆為十四位元組之字串， 「該國人口數」為十四碼之整數， 「該國領土面積」為十四碼之整數，單位是平方公里， 「連絡電話」為十四位元組之字串(限用0~9之數字，但是以字串呈現)， 「是否為邦交國」為一布林值(Boolean)。

例如，(AA4321，美麗國，北美洲，John Smith，Mary Jenifer，Tom Wallace，200000000，9630000，019-1-917-123-4567，否)

3. 員工派駐資料表，用以存放員工派駐各國之資料，包括：員工身分證字號、派駐國家代碼、員工姓名、到職日期、與大使(代表)之姓名等。其中，

「派駐國家代碼」為六位元組之字串，其中前二碼必須是英文大寫字母(即，限 AAZZ)，後四碼限用09之數字，本欄位亦是「國家資料表」之「國家代碼」 在本資料表的外來鍵(Foreign key)， 「員工身分證字號」與「派駐國家代碼」一起為本資料表之複合關鍵屬性(Compound key attributes)， 「大使(代表)姓名」為十四位元組之字串。

例如，(B112233445，AB1234，張三豐， 2012/01/25，葉問)

4. 眷屬資料表，用以存放員工之眷屬資料，包括：員工身份證字號、眷屬身分證字號、眷屬姓名、眷屬性別、和員工關係與出生年月日等。其中，

「員工身份證字號」與「眷屬身份證字號」一起為本資料表之複合關鍵屬性， 「眷屬姓名」為十四位元組之字串， 「眷屬性別」為一位元組之字(character)， 「和員工關係」為六位元組之字串。

例如，(B223344556， A123454321，呂東萊，男，夫妻，1980/10/31)

** 請注意：

1. 設計資料表過程中，如有必要可以自行增加屬性，唯須於設計及繪製系統概念模式圖(Conceptual Model)中說明。
2. 每一資料表請至少輸入三筆資料。

二、系統功能

1. 員工基本資料表：須能新增資料、刪除資料、修改資料、查詢資料及列印資料，其中 新增資料：新增員工資料。 刪除資料：係將資料之狀態(需增加一欄位)由「正常」改成「刪除」，而不是真的刪除。 修改資料：當員工資料與事實不符時，例如，住址或電話變更，用以修改。 查詢資料：必須至少能以「員工身分證字號」查詢員工資料。 列印資料：能依查詢要求列印查詢結果資料。 ** 員工基本資料一經建立不得刪除，須永久保存於員工基本資料表中。

2. 國家資料表：須能新增資料、查詢資料及列印資料，其中 新增資料：係新增國家資料；如欲刪除某一筆資料，只能以自行增加之欄位「國家存在狀態」而以「亡國」為其值之方式記錄之，該欄位其他值為「正常」。 查詢資料：必須至少能以國家代碼查詢國家資料。 列印資料：能依查詢要求列印查詢結果資料。 ** 本表格資料均不得刪除，須永久保存。

3. 員工派駐資料表：須能新增資料、查詢資料及列印資料 ，其中 新增資料：當新增一位員工而派駐到某一國家時所紀錄之資料。如欲刪除資料，亦 須自行增加一欄位「派駐狀態」，而以「調離原職」或「離職」為其值。 查詢資料：本系統管理人員可以直接查詢本資料表。 列印資料：能依查詢要求列印查詢結果資料。 ** 本表格資料均不得刪除，須永久保存。

4. 眷屬資料表：須能新增資料、刪除資料、修改資料、查詢資料及列印資料，其中 新增資料：新增眷屬資料，例如，新增剛出生之員工子女。 刪除資料：係將資料之狀態由「正常」改成「離婚」或「被人收養」，而不是真 的刪除。 修改資料：當眷屬資料與事實不符時，例如，和員工關係變更時(例如，離婚)。 查詢資料：必須至少能以「員工身分證字號」或「眷屬身分證字號」查詢眷屬資料。 列印資料：能依查詢要求列印查詢結果資料。 ** 本表格資料均不得刪除，須永久保存。

三、使用者介面 除了資料庫系統本身所提供的操作介面外，須另外包括下列使用者介面：

1. 員工基本資料介面： (1) 須符合員工基本資料表之系統功能需求。 (2) 可以統計員工人數、員工平均年齡與薪資等資料。 (3) 可以統計依職等分群之資料查詢，例如，十職等之員工人數，九職等以下之總員工人數。 (4) 可以統計所有員工全年，每月與每周之總薪資。

2. 國家之記錄介面： (1) 須符合國家資料表之系統功能需求。 (2) 可以統計共有多少邦交國? 多少非邦交國? (3) 可以統計某一洲(例如，南美洲)共有多少邦交國? 多少非邦交國? (4) 可以查詢某一邦交國或統計所有邦交國之國民人數，查詢某一非邦交國或統計所有非邦交國之國民人數。

3. 員工派駐資料介面： (1) 須符合員工派駐資料表之系統功能需求。 (2) 可以查詢每一國家派駐之總員工人數。 (3) 可以查詢每一國家每單位面積之派駐員工數 (公式: 該國總派駐員工人數/該國領土面積)。

4. 眷屬資料介面： (1) 須符合眷屬資料表之系統功能需求。 (2) 可以統計平均眷屬年齡，男眷屬平均年齡，與女眷屬平均年齡。 (3) 可以統計眷屬是男生之人數，與眷屬是女生之人數。

5. 跨資料表整合介面: (1) 查詢派駐某一國家(例如，美國) 30歲以上之員工人數。 (2) 可以查詢派駐某一洲(例如，歐洲)共多少位員工? (3) 可以查詢30歲以上員工之平均眷屬年齡。 (4) 可以查詢30歲以上員工之平均眷屬人數。

四、資料庫限制: (1) 一個員工可以同時派駐到若干國，例如，一位大使可以同時擔任若干國之大使， 尤其是小國家。而一個國家會同時派駐若干外交人員。 (2) 夫妻皆是員工時，只能以其中一位為其眷屬所附屬之員工，例如，以先生為其子 女之附屬對象。

五、概念模式圖 請繪製本系統之ER Model(Entity Relationship Model)。 (建議在Access DBMS中繪製)