每位員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，並且要能夠紀錄員工的職務名稱，部門要能夠紀錄誰是部門經理、課長、主任等主管的關聯，並且要允許員工可以擔任多個部門的主管，例如某員工是技術部經理，並兼任管理部資訊長。其他未述明的需求，請自行合理化的假設。
(1) 請根據以上需求，畫出這個系統的實體關係圖 (Entity Relationship Diagram)，
請以陳品山博士提出的模式來繪製。
陳品山的實體關係圖模式可以參考這篇"實體關係模型(Entity-relationship model)"
從以上的需求，可以明顯看出有兩個實體 : 員工、部門。
並且因為員工的基本資料要能夠允許多筆地址、多筆電話、多筆電子郵件，員工可以擔任多個部門的主管，所以可以設計如下 :
(2) 請寫出建置資料表結構的MySQL語法。
我們把相關資料給 ChatGPT，如下圖 :
我們來檢查看看 ChatGPT 的結果是否正確。
-- 建立資料庫語法
CREATE DATABASE IF NOT EXISTS company_hr
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
-- 開始使用 company_hr 資料庫
USE company_hr;
-- 員工主檔
CREATE TABLE employees (
employee_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_no VARCHAR(10) NOT NULL,
employee_name VARCHAR(100) NOT NULL,
employment_status ENUM('在職', '留職停薪', '離職') NOT NULL DEFAULT '在職',
hire_date DATE NOT NULL,
resign_date DATE NULL,
UNIQUE KEY uk_employee_no (employee_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 部門主檔
-- parent_department_id 用來表示部門組織架構，例如：資訊部底下有系統課、網路課
CREATE TABLE departments (
department_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
department_code VARCHAR(30) NOT NULL,
department_name VARCHAR(100) NOT NULL,
parent_department_id BIGINT UNSIGNED NULL,
department_status ENUM('啟用', '停用') NOT NULL DEFAULT '啟用',
UNIQUE KEY uk_department_code (department_code),
KEY idx_parent_department_id (parent_department_id),
CONSTRAINT fk_departments_parent
FOREIGN KEY (parent_department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 職務主檔
-- is_supervisor_role 用來判斷該職務是否屬於主管職，例如經理、課長、主任、資訊長
CREATE TABLE job_titles (
job_title_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
job_title_name VARCHAR(100) NOT NULL,
is_supervisor_role TINYINT(1) NOT NULL DEFAULT 0,
job_level INT UNSIGNED NULL,
job_description VARCHAR(255) NULL,
UNIQUE KEY uk_job_title_name (job_title_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工電話
-- 將 ERD 中的多值屬性「電話」拆成獨立資料表
CREATE TABLE employee_phones (
phone_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
phone_type ENUM('公司', '手機', '住家', '其他') NOT NULL DEFAULT '手機',
phone_number VARCHAR(30) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_phone_employee_id (employee_id),
CONSTRAINT fk_phone_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 將 ERD 中的多值屬性「電話」拆成獨立資料表
CREATE TABLE employee_phones (
phone_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
phone_type ENUM('公司', '手機', '住家', '其他') NOT NULL DEFAULT '手機',
phone_number VARCHAR(30) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_phone_employee_id (employee_id),
CONSTRAINT fk_phone_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工電子郵件
-- 將 ERD 中的多值屬性「電子郵件」拆成獨立資料表
CREATE TABLE employee_emails (
email_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
email_type ENUM('公司', '個人', '其他') NOT NULL DEFAULT '公司',
email VARCHAR(255) NOT NULL COMMENT '電子郵件',
is_primary TINYINT(1) NOT NULL DEFAULT 0 ,
UNIQUE KEY uk_email (email),
KEY idx_email_employee_id (employee_id),
CONSTRAINT fk_email_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工地址
-- 將 ERD 中的多值屬性「地址」拆成獨立資料表
CREATE TABLE employee_addresses (
address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
address_type ENUM('戶籍', '通訊', '公司宿舍', '其他') NOT NULL DEFAULT '通訊',
postal_code VARCHAR(10) NULL,
city VARCHAR(50) NULL,
district VARCHAR(50) NULL,
address_line VARCHAR(255) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_address_employee_id (employee_id),
CONSTRAINT fk_address_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 將 ERD 中的多值屬性「電子郵件」拆成獨立資料表
CREATE TABLE employee_emails (
email_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
email_type ENUM('公司', '個人', '其他') NOT NULL DEFAULT '公司',
email VARCHAR(255) NOT NULL COMMENT '電子郵件',
is_primary TINYINT(1) NOT NULL DEFAULT 0 ,
UNIQUE KEY uk_email (email),
KEY idx_email_employee_id (employee_id),
CONSTRAINT fk_email_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工地址
-- 將 ERD 中的多值屬性「地址」拆成獨立資料表
CREATE TABLE employee_addresses (
address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
address_type ENUM('戶籍', '通訊', '公司宿舍', '其他') NOT NULL DEFAULT '通訊',
postal_code VARCHAR(10) NULL,
city VARCHAR(50) NULL,
district VARCHAR(50) NULL,
address_line VARCHAR(255) NOT NULL,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
KEY idx_address_employee_id (employee_id),
CONSTRAINT fk_address_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 員工、部門、職務的任職關係表
-- 這張表對應 ERD 裡面的「任職」菱形
-- 可記錄員工在哪個部門、擔任什麼職務、任職起訖日期
-- 也可支援一位員工同時擔任多個部門主管
CREATE TABLE employee_department_job_history (
assignment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
department_id BIGINT UNSIGNED NOT NULL,
job_title_id BIGINT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NULL,
is_primary_department TINYINT(1) NOT NULL DEFAULT 0,
change_reason VARCHAR(100) NULL,
note TEXT NULL,
KEY idx_employee_history (employee_id, start_date, end_date),
KEY idx_department_current (department_id, end_date),
KEY idx_job_title_current (job_title_id, end_date),
UNIQUE KEY uk_employee_department_job_start (
employee_id,
department_id,
job_title_id,
start_date
),
CONSTRAINT fk_history_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_job_title
FOREIGN KEY (job_title_id)
REFERENCES job_titles(job_title_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 這張表對應 ERD 裡面的「任職」菱形
-- 可記錄員工在哪個部門、擔任什麼職務、任職起訖日期
-- 也可支援一位員工同時擔任多個部門主管
CREATE TABLE employee_department_job_history (
assignment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
employee_id BIGINT UNSIGNED NOT NULL,
department_id BIGINT UNSIGNED NOT NULL,
job_title_id BIGINT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NULL,
is_primary_department TINYINT(1) NOT NULL DEFAULT 0,
change_reason VARCHAR(100) NULL,
note TEXT NULL,
KEY idx_employee_history (employee_id, start_date, end_date),
KEY idx_department_current (department_id, end_date),
KEY idx_job_title_current (job_title_id, end_date),
UNIQUE KEY uk_employee_department_job_start (
employee_id,
department_id,
job_title_id,
start_date
),
CONSTRAINT fk_history_employee
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT fk_history_job_title
FOREIGN KEY (job_title_id)
REFERENCES job_titles(job_title_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
(3) 請寫出插入範例資料的MySQL語法。
USE company_hr;
-- =========================
-- 1. 新增部門資料
-- =========================
-- 第一層部門
INSERT INTO departments
(department_code, department_name, parent_department_id, department_status)
VALUES
('D001', '管理部', NULL, '啟用'),
('D002', '技術部', NULL, '啟用'),
('D003', '業務部', NULL, '啟用');
-- 第二層部門
INSERT INTO departments
(department_code, department_name, parent_department_id, department_status)
VALUES
(
'D101',
'人資課',
(SELECT department_id FROM departments WHERE department_code = 'D001'),
'啟用'
),
(
'D201',
'系統課',
(SELECT department_id FROM departments WHERE department_code = 'D002'),
'啟用'
),
(
'D202',
'網路課',
(SELECT department_id FROM departments WHERE department_code = 'D002'),
'啟用'
);
-- =========================
-- 2. 新增職務資料
-- =========================
INSERT INTO job_titles
(job_title_name, is_supervisor_role, job_level, job_description)
VALUES
('資訊長', 1, 1, '負責公司資訊策略與資訊系統規劃'),
('經理', 1, 2, '負責部門管理與決策'),
('課長', 1, 3, '負責課級單位管理'),
('主任', 1, 4, '負責基層單位管理'),
('工程師', 0, 5, '負責系統開發、維護與技術支援'),
('專員', 0, 5, '負責行政、業務或專案執行'),
('業務代表', 0, 5, '負責客戶開發與業務推廣');
-- =========================
-- 3. 新增員工資料
-- =========================
INSERT INTO employees
(employee_no, employee_name, employment_status, hire_date, resign_date)
VALUES
('E0001', '王大明', '在職', '2022-03-01', NULL),
('E0002', '李小華', '在職', '2023-05-15', NULL),
('E0003', '陳志強', '在職', '2021-08-10', NULL),
('E0004', '林美玲', '在職', '2024-01-08', NULL),
('E0005', '張雅婷', '留職停薪', '2020-11-20', NULL),
('E0006', '黃俊傑', '離職', '2019-06-01', '2024-12-31');
-- =========================
-- 4. 新增員工電話
-- =========================
INSERT INTO employee_phones
(employee_id, phone_type, phone_number, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'手機',
'0912-345-678',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'公司',
'02-2345-1001',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'手機',
'0922-222-222',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'手機',
'0933-333-333',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'公司',
'02-2345-1004',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'手機',
'0955-555-555',
1
);
-- =========================
-- 5. 新增員工電子郵件
-- =========================
INSERT INTO employee_emails
(employee_id, email_type, email, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'個人',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'公司',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0006'),
'公司',
1
);
-- =========================
-- 6. 新增員工地址
-- =========================
INSERT INTO employee_addresses
(employee_id, address_type, postal_code, city, district, address_line, is_primary)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'通訊',
'100',
'台北市',
'中正區',
'忠孝東路一段 1 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
'戶籍',
'220',
'新北市',
'板橋區',
'文化路一段 88 號',
0
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
'通訊',
'403',
'台中市',
'西區',
'公益路 200 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
'通訊',
'700',
'台南市',
'中西區',
'民生路二段 50 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
'通訊',
'802',
'高雄市',
'苓雅區',
'三多二路 99 號',
1
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
'戶籍',
'640',
'雲林縣',
'斗六市',
'中山路 123 號',
1
);
-- =========================
-- 7. 新增員工、部門、職務任職紀錄
-- =========================
-- 王大明：技術部經理，並兼任管理部資訊長
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
(SELECT department_id FROM departments WHERE department_code = 'D002'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '經理'),
'2023-01-01',
NULL,
1,
'升任技術部經理',
'主要負責技術部管理'
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0001'),
(SELECT department_id FROM departments WHERE department_code = 'D001'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '資訊長'),
'2024-01-01',
NULL,
0,
'兼任管理部資訊長',
'跨部門兼任主管職'
);
-- 李小華：人資課課長
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0002'),
(SELECT department_id FROM departments WHERE department_code = 'D101'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '課長'),
'2023-06-01',
NULL,
1,
'任職人資課課長',
'負責人事管理與招募流程'
);
-- 陳志強：曾任系統課工程師，後來升任主任
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
(SELECT department_id FROM departments WHERE department_code = 'D201'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '工程師'),
'2021-08-10',
'2024-02-29',
1,
'初任系統課工程師',
'負責內部系統維護'
),
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0003'),
(SELECT department_id FROM departments WHERE department_code = 'D201'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '主任'),
'2024-03-01',
NULL,
1,
'升任系統課主任',
'負責系統課日常管理'
);
-- 林美玲：網路課工程師
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0004'),
(SELECT department_id FROM departments WHERE department_code = 'D202'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '工程師'),
'2024-01-08',
NULL,
1,
'新進員工任職',
'負責網路設備維護'
);
-- 張雅婷：業務部專員，目前留職停薪
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0005'),
(SELECT department_id FROM departments WHERE department_code = 'D003'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '專員'),
'2020-11-20',
NULL,
1,
'任職業務部專員',
'目前留職停薪'
);
-- 黃俊傑：已離職，保留歷史任職紀錄
INSERT INTO employee_department_job_history
(employee_id, department_id, job_title_id, start_date, end_date, is_primary_department, change_reason, note)
VALUES
(
(SELECT employee_id FROM employees WHERE employee_no = 'E0006'),
(SELECT department_id FROM departments WHERE department_code = 'D003'),
(SELECT job_title_id FROM job_titles WHERE job_title_name = '業務代表'),
'2019-06-01',
'2024-12-31',
1,
'離職',
'保留歷史任職資料'
);
將以上結構建立在 InfinityFree 的資料庫上，如下圖 :
(4) 列出員工範例資料，包含員工姓名、員工編號、員工電子郵件、所屬部門名稱、職稱。
MySQL 語法如下 :
SELECT
e.employee_name AS 員工姓名,
e.employee_no AS 員工編號,
COALESCE(em.email_list, '') AS 員工電子郵件,
COALESCE(ph.phone_list, '') AS 員工電話,
COALESCE(ad.address_list, '') AS 員工地址,
COALESCE(jh.department_job_list, '') AS 所屬部門與職稱
FROM employees e
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(email_type, '：', email)
ORDER BY is_primary DESC, email_type
SEPARATOR '；'
) AS email_list
FROM employee_emails
GROUP BY employee_id
) em
ON e.employee_id = em.employee_id
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(phone_type, '：', phone_number)
ORDER BY is_primary DESC, phone_type
SEPARATOR '；'
) AS phone_list
FROM employee_phones
GROUP BY employee_id
) ph
ON e.employee_id = ph.employee_id
LEFT JOIN (
SELECT
employee_id,
GROUP_CONCAT(
CONCAT(
address_type, '：',
IFNULL(postal_code, ''), ' ',
IFNULL(city, ''),
IFNULL(district, ''),
address_line
)
ORDER BY is_primary DESC, address_type
SEPARATOR '；'
) AS address_list
FROM employee_addresses
GROUP BY employee_id
) ad
ON e.employee_id = ad.employee_id
LEFT JOIN (
SELECT
h.employee_id,
GROUP_CONCAT(
CONCAT(
d.department_name, '／',
j.job_title_name,
'（', h.start_date, ' ~ ', IFNULL(h.end_date, '目前'), '）'
)
ORDER BY h.start_date
SEPARATOR '；'
) AS department_job_list
FROM employee_department_job_history h
JOIN departments d
ON h.department_id = d.department_id
JOIN job_titles j
ON h.job_title_id = j.job_title_id
GROUP BY h.employee_id
) jh
ON e.employee_id = jh.employee_id
ORDER BY e.employee_no;
輸出結果如下圖 :
最後再補個資料結構的 UML Class Diagram，請 ChatGPT 給我 Planttext UML，得到如下圖 :
0 留言