網路選課系統如何判斷衝堂? 應該是網路選課很重要的一個功能。這個功能應該如何實現呢? 我們使用實際的操作來說明幾個判斷衝堂的解決方式。
首先建立一個資料庫
CREATE DATABASE school
CHARACTER SET utf8
COLLATE utf8_general_ci;
你也可以使用COLLATE為 utf8mb4_general_ci 或是 utf8mb4_unicode_ci
使用unicode與general的差異? general排序速度較快，但是unicode比較不會出錯。
使用utf8mb4與utf8的差異? MySQL 5.5.3之後支援utf8mb4，編碼相容性比utf8好。
參考資料 : https://www.cadch.com/modules/news/article.php?storyid=198
然後記得使用 use school;
假設網路選課系統有以下表單 :
-- 科系基本資料表
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
dname VARCHAR(20)
);
-- dname : department name
如果資料表結構需要更改請參考ALTER指令
https://www.w3schools.com/mysql/mysql_alter.asp
https://dev.mysql.com/doc/refman/8.0/en/alter-database.html
https://www.tutorialspoint.com/mysql/mysql-indexes.htm
https://ithelp.ithome.com.tw/questions/10204532
-- 教師基本資料表
CREATE TABLE teachers (
id INT AUTO_INCREMENT PRIMARY KEY,
department_id INT NOT NULL,
tname VARCHAR(20),
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- tname : teacher name
-- 學生基本資料表
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
sname VARCHAR(255) NOT NULL,
department_id INT NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- sname : student name
-- 課程基本資料表
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
cname VARCHAR(50),
department_id INT NOT NULL,
credit INT NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
-- cname : course name
-- credit : course credit
-- 開課資料表
CREATE TABLE open_courses (
id INT AUTO_INCREMENT PRIMARY KEY,
teacher_id INT NOT NULL,
course_id INT NOT NULL,
classroom VARCHAR(20),
FOREIGN KEY (course_id) REFERENCES courses(id),
FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);
-- 開課時間資料表#1
CREATE TABLE courses_schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
oc_id INT NOT NULL,
day_of_week INT NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
FOREIGN KEY (oc_id) REFERENCES open_courses(id)
);
-- 開課時間資料表#2
CREATE TABLE courses_schedule2 (
id INT AUTO_INCREMENT PRIMARY KEY,
oc_id INT NOT NULL,
slot INT NOT NULL,
FOREIGN KEY (oc_id) REFERENCES open_courses(id)
);
-- 學生選課表
CREATE TABLE registrations (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
oc_id INT NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (oc_id) REFERENCES open_courses(id)
);
-- 插入科系資料
INSERT INTO departments (dname) VALUES
('數學系'),
('資管系'),
('電機系');
-- 插入老師資料
INSERT INTO teachers (tname, department_id) VALUES
('李一', 1),
('王二', 2),
('張三', 3),
('林四', 3);
-- 插入學生資料
INSERT INTO students (sname, department_id) VALUES
('小明', 1),
('小華', 2),
('小美', 3),
('小劉', 3);
-- 插入課程資料
INSERT INTO courses (cname, department_id, credit) VALUES
('數學', 1, 3),
('英文', 2, 2),
('物理', 3, 3),
('離散數學', 1, 2),
('管理學概論', 2, 2),
('電磁學', 3, 3),
('代數', 1, 3),
('統計學', 2, 2),
('電路學', 3, 3),
('電子學', 3, 3);
-- 插入開課課程資料
INSERT INTO open_courses (teacher_id, course_id, classroom) VALUES
(1, 1, 'Room001'),
(2, 2, 'Room002'),
(3, 3, 'Room003'),
(1, 4, 'Room004'),
(2, 5, 'Room005'),
(3, 6, 'Room006'),
(1, 7, 'Room007'),
(2, 8, 'Room008'),
(3, 9, 'Room009');
-- 插入課程時間資料#1
INSERT INTO courses_schedule (oc_id, day_of_week, start_time, end_time) VALUES
(1, 1, '08:00:00', '09:00:00'),
(1, 1, '09:00:00', '10:00:00'),
(1, 3, '08:00:00', '09:00:00'),
(1, 3, '09:00:00', '10:00:00'),
(2, 2, '08:00:00', '09:00:00'),
(2, 2, '09:00:00', '10:00:00'),
(3, 2, '09:00:00', '10:00:00'),
(3, 3, '09:00:00', '10:00:00'),
(4, 4, '09:00:00', '12:00:00'),
(5, 4, '10:00:00', '12:00:00'),
(5, 5, '10:00:00', '12:00:00'),
(6, 2, '15:00:00', '17:00:00'),
(6, 3, '15:00:00', '17:00:00'),
(7, 5, '14:00:00', '15:00:00'),
(7, 5, '15:00:00', '16:00:00'),
(7, 5, '16:00:00', '17:00:00'),
(8, 4, '19:00:00', '22:00:00'),
(9, 3, '16:00:00', '17:00:00'),
(9, 3, '18:00:00', '19:00:00'),
(9, 3, '20:00:00', '21:00:00'),
(9, 4, '20:00:00', '21:00:00');
-- 插入課程時間資料#2
INSERT INTO courses_schedule2 (oc_id, slot) VALUES
(1, 1),
(1, 2),
(1, 25),
(1, 26),
(2, 13),
(2, 14),
(3, 14),
(3, 26),
(4, 38),
(4, 39),
(4, 40),
(5, 39),
(5, 40),
(5, 51),
(5, 52),
(6, 19),
(6, 20),
(6, 31),
(6, 32),
(7, 54),
(7, 55),
(7, 56),
(8, 46),
(8, 47),
(8, 48),
(9, 32),
(9, 33),
(9, 35),
(9, 47);
以上關於課程時間資料有兩種結構
courses_schedule 是以星期跟時間區間來定義課程時間。
courses_schedule2 是以Slot時間區段編號來定義課程時間。
Slot時間區段編號的意思就是把一週的時間劃分為以下 :
週一 : 1, 2, 3, 4 午餐 5, 6, 7, 8 晚餐 9, 10, 11, 12
週二 : 13, 14, 15, 16 午餐 17, 18, 19, 20 晚餐 21, 22, 23, 24
週三 : 25, 26, 27, 28 午餐 29, 30, 31, 32 晚餐 33, 34, 35, 36
週四 : 37, 38, 39, 40 午餐 41, 42, 43, 44 晚餐 45, 46, 47, 48
週五 : 49, 50, 51, 52 午餐 53, 54, 55, 56 晚餐 57, 58, 59, 60
週六 : 61, 62, 63, 64 午餐 65, 66, 67, 68 晚餐 69, 70, 71, 72
週日 : 73, 74, 75, 76 午餐 77, 78, 79, 80 晚餐 81, 82, 83, 84
(以上只是範例的劃分方式，你可以根據你的實際需要修改)
你現在可以抓取資料，找到一些你想看的資料
select tname, dname from departments d, teachers t where t.department_id=d.id;
或
select tname, dname from departments d
inner join teachers t on t.department_id=d.id;
或
select tname, dname from departments d
join teachers t on t.department_id=d.id;
列出科系及老師，執行如下圖
select cname as 課程,oc.id as 開課編號,dname as 科系 from departments d, open_courses oc, courses c where c.department_id=d.id and oc.course_id=c.id;
或
select cname as 課程,oc.id as 開課編號,dname as 科系 from departments d
inner join courses c on c.department_id=d.id
inner join open_courses oc on oc.course_id=c.id;
列出開課課程, 開課編號, 科系，執行如下圖
(依據courses_schedule結構)
select cname as 課程,oc.id as 開課編號,dname as 科系, day_of_week as 星期, start_time, end_time from departments d, open_courses oc, courses c , courses_schedule cs where c.department_id=d.id and oc.course_id=c.id and cs.oc_id=oc.id;
或
select cname as 課程,oc.id as 開課編號,dname as 科系, day_of_week as 星期, start_time, end_time from departments d
inner join courses c on c.department_id=d.id
inner join open_courses oc on oc.course_id=c.id
inner join courses_schedule cs on cs.oc_id=oc.id;
列出開課課程, 開課編號, 科系，以及開課時間，執行如下圖
(依據courses_schedule2結構)
select cname as 課程,oc.id as 開課編號,dname as 科系, slot from departments d, open_courses oc, courses c , courses_schedule2 cs where c.department_id=d.id and oc.course_id=c.id and cs.oc_id=oc.id;
或
select cname as 課程,oc.id as 開課編號,dname as 科系, slot from departments d
inner join courses c on c.department_id=d.id
inner join open_courses oc on oc.course_id=c.id
inner join courses_schedule2 cs on cs.oc_id=oc.id;
列出開課課程, 開課編號, 科系，以及開課時間，執行如下圖
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱 from students s, registrations r, open_courses oc, courses c where r.student_id=s.id and r.oc_id=oc.id and oc.course_id=c.id;
或
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱 from students s
inner join registrations r on r.student_id=s.id
inner join open_courses oc on r.oc_id=oc.id
inner join courses c on oc.course_id=c.id;
列出學生id, 學生姓名, 修習的課程名稱，執行如下圖
(依據courses_schedule結構)
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱, day_of_week, start_time, end_time from students s, registrations r, open_courses oc, courses c, courses_schedule cs where r.student_id=s.id and r.oc_id=oc.id and oc.course_id=c.id and cs.oc_id=oc.id;
或
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱, day_of_week, start_time, end_time from students s
inner join registrations r on r.student_id=s.id
inner join open_courses oc on r.oc_id=oc.id
inner join courses c on oc.course_id=c.id
inner join courses_schedule cs on cs.oc_id=oc.id;
列出學生id, 學生姓名, 修習的課程名稱, 以及開課時間，執行如下圖
(依據courses_schedule2結構)
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱, slot from students s, registrations r, open_courses oc, courses c, courses_schedule2 cs where r.student_id=s.id and r.oc_id=oc.id and oc.course_id=c.id and cs.oc_id=oc.id;
或
select s.id as 學生id, s.sname as 學生姓名, c.cname as 課程名稱, slot from students s
inner join registrations r on r.student_id=s.id
inner join open_courses oc on r.oc_id=oc.id
inner join courses c on oc.course_id=c.id
inner join courses_schedule2 cs on cs.oc_id=oc.id;
列出學生id, 學生姓名, 修習的課程名稱, 以及開課時間，執行如下圖
網路選課判斷衝堂方法的兩種方法
-- Stored Procedure 判斷衝堂方法一 (根據courses_schedule結構) :
DELIMITER //
CREATE PROCEDURE CheckConflict (
IN studentID INT, IN ocID INT, OUT isConflict TINYINT)
BEGIN
SELECT COUNT(*) INTO isConflict
FROM courses_schedule cs1
JOIN registrations r ON r.oc_id = cs1.oc_id
WHERE r.student_id = @studentID
AND EXISTS (
SELECT 1 FROM courses_schedule cs2
WHERE cs2.oc_id = @ocID
AND cs1.day_of_week = cs2.day_of_week
AND (
(cs1.start_time BETWEEN cs2.start_time AND cs2.end_time)
OR (cs1.end_time BETWEEN cs2.start_time AND cs2.end_time)
OR (cs2.start_time BETWEEN cs1.start_time AND cs1.end_time)
OR (cs2.end_time BETWEEN cs1.start_time AND cs1.end_time)
)
);
END //
DELIMITER ;
上面Select 1是什麼作用? 表示下面條件成立的話，就會出現1。
例如
Select 1 from courses where id is not null;
Select 1 from courses where id is null;
執行如下圖
如果符合條件就會出現1，如果不符合條件就不出現任何資料 (就是Empty Set)。
方法一，要顯示該學生的修課時段，如以下的指令
select cs.oc_id, day_of_week, start_time, end_time from registrations r , courses_schedule cs where r.student_id=1 and cs.oc_id=r.oc_id;
方法一，要顯示要修課的時段，如以下的指令
select oc_id, day_of_week, start_time, end_time from open_courses oc, courses_schedule cs where oc.id=1 and cs.oc_id=oc.id;
在方法一中，就是如果時間重疊(符合條件1以及條件2/3/4/5任一個)，就會回覆1。
條件 :
(1) 如果在同一天。
cs1.day_of_week = cs2.day_of_week
(2) 如果既有課程的開始時間在要選的課程當中。
cs1.start_time BETWEEN cs2.start_time AND cs2.end_time
(3) 如果既有課程的結束時間在要選的課程當中。
cs1.end_time BETWEEN cs2.start_time AND cs2.end_time
(4) 如果要選的課程的開始時間在既有課程的當中。
cs2.start_time BETWEEN cs1.start_time AND cs1.end_time
(5) 如果要選的課程的結束時間在既有課程的當中。
cs2.end_time BETWEEN cs1.start_time AND cs1.end_time
如果我們先插入一個修課資料
INSERT INTO registrations (student_id, oc_id) VALUES (1,2);
(方法一)
student_id=1的學生已經修了oc_id=2的課程 (英文課)
星期一 課程時間 08:00:00~11:00:00
-- 假設您要檢查學生ID為 1 的學生選課ID為 2 的課程是否衝堂
SET @studentId = 1;
SET @ocId = 2;
SET @isConflict = 0;
CALL CheckConflict (@studentId, @ocId, @isConflict);
SELECT @isConflict;
如果@isConflict大於 0，則表示衝突。
執行結果如下圖
-- 假設您要檢查學生ID為 1 的學生選課ID為 3 的課程是否衝堂
SET @studentId = 1;
SET @ocId = 3;
SET @isConflict = 0;
CALL CheckConflict (@studentId, @ocId, @isConflict);
SELECT @isConflict;
執行結果如下圖
-- Stored Procedure 判斷衝堂方法二 (根據courses_schedule2結構) :
DELIMITER //
CREATE PROCEDURE CheckConflict2 (
IN studentID INT, IN ocID INT, OUT isConflict TINYINT)
BEGIN
SELECT DISTINCT COUNT(*) INTO isConflict
FROM (
SELECT cs1.slot
FROM registrations r
JOIN courses_schedule2 cs1 ON cs1.oc_id = r.oc_id
WHERE r.student_id = @studentID
) AS a
WHERE EXISTS (
SELECT 1
FROM open_courses oc
JOIN courses_schedule2 cs2 ON cs2.oc_id = oc.id
WHERE oc.id = @ocID AND a.slot = cs2.slot
);
END //
DELIMITER ;
方法二，要顯示該學生的修課時段，如以下的指令
select cs.slot from registrations r , courses_schedule2 cs where r.student_id=1 and cs.oc_id=r.oc_id;
方法二，要顯示要修課的時段，如以下的指令
select cs.slot from open_courses oc, courses_schedule2 cs where oc.id=1 and cs.oc_id=oc.id;
(方法二)
student_id=1的學生已經修了oc_id=2的課程 (英文課)
課程時間編號為 15,16 (週二早上3,4堂課)
-- 假設您要檢查學生ID為 1 的學生選課ID為 2 的課程是否衝堂
SET @studentId = 1;
SET @ocId = 2;
SET @isConflict = 0;
CALL CheckConflict2 (@studentId, @ocId, @isConflict);
SELECT @isConflict;
如果@isConflict大於 0，則表示衝突。如果@isConflict= 0，則表示沒有衝突，可以修課。
執行結果如下圖
-- 假設您要檢查學生ID為 1 的學生選課ID為 4 的課程是否衝堂
SET @studentId = 1;
SET @ocId = 4;
SET @isConflict = 0;
CALL CheckConflict2 (@studentId, @ocId, @isConflict);
SELECT @isConflict;
執行結果如下圖
請比較一下方法一與方法二兩個不同結構，哪個檢查衝堂的效率會好一些呢?
為了比較方法一、二的執行效能，先以下面方式執行
SET @studentId = 1;
SET @ocId = 2;
SET @isConflict = 0;
CALL CheckConflict (@studentId, @ocId, @isConflict);
SELECT @isConflict;
得到如下結果 ~ 查詢用了 0.0006 秒
再來執行以下
SET @studentId = 1;
SET @ocId = 2;
SET @isConflict = 0;
CALL CheckConflict2 (@studentId, @ocId, @isConflict);
SELECT @isConflict;
得到如下結果 ~ 查詢也用了 0.0006 秒
目前看起來兩個似乎沒有太大差異，也可能是資料量太少，不過你可以使用各種不同資料再來測試兩者的查詢效能是否有差異。
更多參考~網路選課的UML分析
https://www.mysql.tw/2021/05/uml-usecase-activity-class.html
更多參考~線上商店的UML分析
https://www.mysql.tw/2021/05/uml-online-shopping.html
0 留言