今天剛好碰到王老闆，他經營了三家飲料店、兩間拉麵餐廳、三間超市。因為工讀生越來越多，每個工讀生都有不同的工作時間。王老闆希望可以開發一套工讀生薪資管理系統，讓會計人員可以登錄工讀生的工作時間來計算薪資，並記錄登錄資料的會計人員，並提供工讀生以電子郵件登入查詢自己的薪資，你覺得這個工讀生薪資系統的DFD與ERD應該怎麼畫？
(1) 工讀生薪資系統的DFD與ERD應該怎麼畫?
根據前述DFD(Data Flow Diagram)的文章，我們先畫Level-0的DFD，大致如下圖 :
上圖為何工讀生跟商店沒有關聯? 而只跟薪水有關連呢? 如果工讀生跟商店關聯，再由商店跟薪水關聯會如何? 如果工讀生跟商店、薪水一起關聯又會如何呢?
這個你可以自己思考看看，我們後續再來討論。
(2) 邏輯資料表及實體資料表應該怎麼規畫?
這個工讀生薪資系統的邏輯資料表及實體資料表應該怎麼設計？這個工讀生薪資系統的主鍵(Primary Key)跟外鍵(Foreign Key)怎麼設計？
如果根據上面的ERD來看，邏輯資料表應該如下 (主鍵為畫底線者) :
stores (id, cate_id)
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end)
因為登入需要email，所以加入stud_email，並且要記得宣告為NOT NULL UNIQUE (為何?)。
如果再修飾一下，邏輯資料表應該變成如下 :
store_category (id, category_name)
stores (id, cate_id) 商店的cate_id關連到store_category的id
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end)
薪水的store_id關連到store的id，stud_id關連到students的id
以上的邏輯資料表是在工讀時薪固定的情況下，如果時薪會變動就必須做些更動如下 :
store_category (id, category_name)
stores (id, cate_id, pay_per_hour)
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end, salary_total)
在store加入pay_per_hour來調整時薪，在salary加入salary_total紀錄時薪，方便每次工作計算當時薪水。因為沒有每次計算的話，pay_per_hour調整時，整個歷史薪水就全亂了。
然後由以下指令來計算工作多久 :
工作多少分鐘 = TIME_TO_SEC(TIMEDIFF(salary_end, salary_start)) / 60
TIMEDIFF語法參考 https://www.w3schools.com/sql/func_mysql_timediff.asp
TIME_TO_SEC語法參考 https://www.w3schools.com/sql/func_mysql_time_to_sec.asp
每分鐘薪水 = (pay_per_hour / 60)
salary_total = ROUND(每分鐘薪水*工作多少分鐘, 0)
以上的0表示四捨五入到整數
ROUND語法參考 https://www.w3schools.com/sql/func_mysql_round.asp
例如 13:00:00 工作到 18:40:00 ，若時薪為$176元 (以每分鐘薪水$2.93計算)，則薪水總額是 :
salary_total = ROUND(2.93*(TIME_TO_SEC(TIMEDIFF('18:40:00', '13:00:00')) / 60), 0) = $996元
工讀生開始工作打卡時，就會寫入 商店id, 工讀生id, 打卡日期, 開始時間
例如
INSERT INTO salary (store_id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00');
工讀生結束工作打卡時，就會寫入 結束時間, 本次薪水
例如 先計算出來本次薪水 @total
@total = ROUND(ROUND(pay_per_hour/60, 2)*(TIME_TO_SEC(TIMEDIFF('18:40:00', '13:00:00')) / 60), 0)
然後
UPDATE salary SET salary_end ='18:40:00', salary_total=@total
WHERE id=1; (這個id就根據開始工作打卡時產生的id為準)
然後實體資料表設計如下 :
-- store_category 資料表
CREATE TABLE store_category (
id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(20)
);
-- stores 資料表
CREATE TABLE stores (
id INT AUTO_INCREMENT PRIMARY KEY,
cate_id INT,
pay_per_hour DECIMAL(10, 0),
FOREIGN KEY (cate_id) REFERENCES store_category(id)
);
-- students 資料表
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
stud_name VARCHAR(50),
stud_password VARCHAR(20),
stud_email VARCHAR(100) NOT NULL UNIQUE
);
-- salary 資料表
CREATE TABLE salary (
id INT AUTO_INCREMENT PRIMARY KEY,
store_id INT,
stud_id INT,
salary_date DATE,
salary_start TIME,
salary_end TIME,
salary_total DECIMAL(10, 0),
FOREIGN KEY (store_id) REFERENCES stores(id),
FOREIGN KEY (stud_id) REFERENCES students(id)
);
現在先來插入範例資料 :
-- 插入 store_category 資料
INSERT INTO store_category (category_name) VALUES ('便利商店'),('咖啡廳'),('書店');
-- 插入 stores 資料
INSERT INTO stores (cate_id, pay_per_hour) VALUES (1, 160),(2, 180),(3, 150);
-- 插入 students 資料
INSERT INTO students (stud_name, stud_password, stud_email) VALUES ('張三', 'password123', '[email protected]'),('李四', 'password456', '[email protected]'),('王五', 'password789', '[email protected]');
-- 插入 salary 資料 (開始工作打卡)
INSERT INTO salary (store_id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00'),(2, 2, '2023-05-18', '14:00:00'),(3, 3, '2023-05-18', '15:00:00');
-- 更新 salary 資料 (結束工作打卡)
UPDATE salary SET salary_end = '18:40:00' WHERE id = 1;
UPDATE salary SET salary_end = '17:30:00' WHERE id = 2;
UPDATE salary SET salary_end = '20:00:00' WHERE id = 3;
然後就開始寫了觸發器 :
(以下是錯誤的版本)
原先設想在工讀生寫入打工的結束時間，以觸發器計算薪水寫入salary表單，後來發現觸發器不管是BEFORE UPDATE或是AFTER UPDATE，都會產生如下的錯誤。
#1442 - Can't update table 'salary' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
意思是無法更新salary表單，因為正在使用中，所以當salary表單在寫入打工的結束時間時，觸發器無法再去更新salary表單。
所以要計算薪水寫入salary表單，不能使用Trigger，需要使用stored procedure來處理了，上面的UPDATE也不能這樣使用了。因此原本UPDATE的欄位salary_end要清空為null (為何要清為null?)
~~~~~~~~~~~~ 後記 ~~~~~~~~~~~~
重寫trigger，發現以下方式可以排除錯誤，在BEFORE UPDATE ON salary時，去更新salary中的資料，但是還沒有加上檢查salary_end是否為null。
DELIMITER //
CREATE TRIGGER update_salary_total
BEFORE UPDATE ON salary
FOR EACH ROW
BEGIN
DECLARE work_minutes DECIMAL(10,2);
DECLARE pay_per_minute DECIMAL(10,2);
-- 計算工作多少分鐘
SET work_minutes = TIME_TO_SEC(TIMEDIFF(NEW.salary_end, NEW.salary_start)) / 60;
-- 取得每分鐘薪水
SELECT (pay_per_hour / 60) INTO pay_per_minute
FROM stores
WHERE id = NEW.store_id;
-- 計算薪水總額並更新到 NEW.salary_total
SET NEW.salary_total = ROUND(pay_per_minute * work_minutes, 0);
END //
DELIMITER ;
~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~
現在為了有效率的寫出stored procedure，我們呼叫ChatGPT-4來幫忙。
當告訴ChatGPT-4所有條件後，他給了以下的stored procedure :
(以下是錯誤的版本)
使用 CALL update_salary_end_and_total(1, '18:40:00'); 來寫入結束時間。
但是問題來了，需要輸入salary_id跟結束時間? 工讀生刷卡時並沒有給stud_id 如何可以找到salary_id呢?
我們這個疑問 「工讀生結束工作時，如何知道p_id? 是否用stud_id較好?」，ChatGPT同意我們的看法，給了以下的修改版本 :
(以下是錯誤的版本)
他的stored procedure這次需要stud_id、salary_date、salary_end，但是問題又來了，我們的疑問是 「工讀生可能一天會工作兩個時段」，這樣透過stud_id、salary_date可能抓到不只一筆資料，當然就無法得到正確的資料。
ChatGPT同意我們的看法，再給了以下的修改版本 :
(以下是錯誤的版本)
這次需要stud_id、salary_date、salary_start、salary_end，我們給他建議「需要p_salary_start 並不好，應該是提供stud_id、salary_end，然後stored procedure根據當天時間，找到salary_end是 null的資料去 update」。
ChatGPT又聽從我們的建議，再改寫如下 :
(以下是錯誤的版本)
可是奇怪的是，原本插入的資料是
INSERT INTO salary (store_id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00');
使用 CALL update_salary_end_and_total(1, '18:40:00'); 當日日期是 5/20，但是資料竟然更新進去2023-05-18的資料了，顯然條件判斷出了問題。
ChatGPT又再改寫如下 :
(以下是錯誤的版本)
以下是最後正確的版本 :
DELIMITER $$
CREATE PROCEDURE update_salary_end_and_total(
IN p_stud_id INT,
IN p_salary_date DATE,
IN p_salary_end TIME
)
BEGIN
DECLARE v_id INT;
DECLARE v_salary_start TIMESTAMP;
DECLARE v_salary_end TIMESTAMP;
DECLARE v_pay_per_hour DECIMAL(10, 0);
DECLARE v_minutes_worked INT;
DECLARE v_total DECIMAL(10, 0);
-- 獲取工資紀錄的 id 和對應的 salary_start
SELECT id, CONCAT(salary_date, ' ', salary_start) AS salary_start
INTO v_id, v_salary_start
FROM salary
WHERE stud_id = p_stud_id AND salary_end IS NULL
ORDER BY salary_date DESC, salary_start DESC
LIMIT 1;
-- 如果找到了對應的工資紀錄，則繼續執行更新操作
IF v_id IS NOT NULL THEN
-- 計算结束時間
SET v_salary_end = TIMESTAMP(CONCAT(p_salary_date, ' ', p_salary_end));
-- 獲取每小時的薪資
SELECT pay_per_hour
INTO v_pay_per_hour
FROM stores
JOIN salary ON stores.id = salary.store_id
WHERE salary.id = v_id;
-- 計算工作分鐘數
SET v_minutes_worked = TIMESTAMPDIFF(MINUTE, v_salary_start, v_salary_end);
-- 計算薪水
SET v_total = ROUND((v_pay_per_hour / 60) * v_minutes_worked, 0);
-- 更新结束時間和薪水
UPDATE salary
SET salary_end = p_salary_end, salary_total = v_total
WHERE id = v_id;
ELSE
-- 如果未找到對應的薪水紀錄，則輸出錯誤訊息
SELECT 'Error: No matching salary record found' AS error_message;
END IF;
END $$
DELIMITER ;
再使用 CALL update_salary_end_and_total(1, '2023-05-19', '02:30:00'); 終於獲得了正確的結果。
以上就是與ChatGPT協同把解答找到的過程，如果你沒能找到關鍵問題讓他修改，你得到的就是一個無法正常運作的東西而已。
我們自己去寫這個stored procedure，可能需要搞很久，或是根本寫不出來，但是搭配ChatGPT的能力，可能產出了「看起來很正常的結果」，如果你沒有質疑，ChatGPT就混過去了。
當你找到關鍵問題之後，ChatGPT就會把尚無法運作的stored procedure 進行逐步修改。
(3) 如何彙整資料給王老闆？
問題 : 如果王老闆想知道某工讀生2023年一月到四月份的薪資？MySQL語法應該如何？
這個需求只牽涉到薪資，因此只跟salary資料表有關。
假設工讀生id=1
SELECT SUM(salary_total)
FROM salary
WHERE
stud_id = 1 AND salary_date BETWEEN '2023-01-01' AND '2023-04-30';
或是
SELECT salary_date, salary_total
FROM salary
WHERE
stud_id = 1 AND salary_date BETWEEN '2023-01-01' AND '2023-04-30';
問題 : 如果王老闆想知道2023年一月到四月份拉麵餐廳中，哪家拉麵餐廳工讀生的薪資總額最高？MySQL語法應該如何？
假設拉麵餐廳類型的 id=1
(以下發現有錯誤)
SELECT stores.id, SUM(salary_total) AS total_salary
FROM stores s
INNER JOIN store_category ON s.cate_id=1
INNER JOIN salary ON stores.id = salary.store_id
WHERE salary_date >= '2023-01-01' AND salary_date <= '2023-04-30'
GROUP BY stores.id
ORDER BY total_salary DESC
LIMIT 1;
(更改為以下)
SELECT st.id, SUM(salary_total) AS total_salary
FROM salary s
INNER JOIN stores st ON s.store_id=st.id
WHERE st.cate_id=1 AND salary_date BETWEEN '2023-01-01' AND '2023-04-30'
GROUP BY st.id
ORDER BY total_salary DESC
LIMIT 1;
問題 : 如果王老闆想知道2022年整年，哪類店家工讀生的薪資總額最高？MySQL語法應該如何？
(以下發現有錯誤)
SELECT sc.category_name, SUM(s.salary_total) AS total_salary FROM stores s INNER JOIN store_category sc ON s.cate_id = sc.id INNER JOIN salary sa ON s.id = sa.store_id WHERE YEAR(sa.salary_date) = 2022 GROUP BY sc.category_name ORDER BY total_salary DESC LIMIT 1;
FROM salary s
INNER JOIN stores st ON s.store_id = st.id
INNER JOIN store_category sc ON st.cate_id = sc.id
WHERE YEAR(s.salary_date) = 2022
GROUP BY sc.category_name
ORDER BY total_salary DESC
LIMIT 1;
後續再來實際驗證，並加入比較詳細的資料。
[後記]
0 留言