翻轉電子書系列：程資料庫系統概論(含邏輯設計)
翻轉工作室：粘添壽
第三章 資料庫範例 - course_db
本書在 AppServ 系統上建構一套網頁資料庫，以供本書編寫範例使用，資料庫名為course_db。它是用來開發學校的『課程管理系統』使用，如圖 2-15，我們期望能製作出下列應用程式 ：
登錄/管理學生資料。
登錄/管理教師資料。
處理學生轉系、轉班之功能。
系所變更名稱、主任。
教師開課。
學生選課。
教師登錄成績。
….
圖 3-1 課程管理系統的功能
如圖 3-2，在 course_db 資料庫內，我們建立了 7 個資料表，以及 3 個視界，其功能如下：(個資料表內容隨後說明)
Students：供登錄全校學生資料使用。
Teachers：供登錄全校教師資料使用。
Classes：供登錄全校班級資料使用。
Department：供登錄全校系所資料使用。
All_course：全校各系所全年級所規劃的課程皆登錄在資料表內。
Open_course：供本學期開課使用。每學期各系由 All_course 表內選擇所欲開的課程，並登錄那一位老師、在哪一個班級開課。
Select_course：供登錄全校學生選課使用。每學期開始學生由 Open_course 表內選課，其中並登錄該生修課成績。
Simple_students 視界：簡化學生資料。
Simple_teachers 視界：簡化教師資料。
Public_course 視界：通用型記錄教師、系所開課之狀況。
圖 3-2 course_db 內的資料表
(1) students 資料表
全校所有學生都登錄此資料表內，如圖 3-3。資料表 students 包含有 7 個欄位：{student_ID, name, class_ID, sex, tel, mailbox, address}，並以 student_ID 為主鍵(Primary key)、class_ID 為外來鍵，連結到 classes 資料表的主鍵 class_ID，作為登錄該學生所屬班級。
圖 3-3 students 資料表的屬性
其中，比較特殊的是學號(student_ID)，我們用 8 個數字表示，由左至右排列是：
學制(一個數字)：日四技(4)、日二技(2)、日五專(5)、日二專(3)、進四技(9)、進二技(7)…。
入學年度(兩個數字)：100(00)、101(01)、102(02)、103(03)、104(04)、、。
系別(兩個數字)：管系(11)、企管系(10)、資工系(09)、電子系(08)、、、。
班級(一個數字)：甲班(1)、乙班(2)、丙班(3)、、、。
座號(兩個數字)：01 ~99。
譬如，學號 = 40311101，表示該生是日間四技、103 年度入學、資管系、甲班、座號為 1。
(2) teachers 資料表
全校教師皆登錄於此資料表內，如圖 3-4。資料表 teachers 係登錄教師資料，包含 8 個欄位，以 teacher_ID(員工代號)作為主鍵，另外 Dep_ID 表示該教師目前在哪一系工作，它參考到 department(系別資料表)的主鍵 Dep_ID。欄位 title 表示該教師的職務，譬如是教師或系主任。
圖 3-4 teachers 資料表的屬性
(3) classes 資料表
全校所有班級皆登錄於 classes 資料表內，如圖 3-5。各班級的資料包含有 4 個欄位，其中 class_ID 為主鍵，Dep_ID 表示該班是屬於哪一個系所、year 試入學年度。另外， class_ID 包含 6 個數字，表示方法如同 student_ID(學號)的前 6 個數字一樣。
圖 3-5 classes 資料表的屬性
(4)department 資料表
全校所有系所皆登錄於 department 資料表內，如圖 3-6。它包含有兩個欄位：系別代碼 dep_ID 與系別名稱 Dep_name，以 dep_ID 為主鍵。
圖 3-6 department 資料表的屬性
(5)all_course 資料表
全校各系所全學年所規劃的課程皆登錄於 all_course 資料表內，如圖 3-7，它包含有 5 個欄位：課程代碼 course_ID、課程名稱 course_name、系所代碼 dep_ID 與必選修 required 與學分數 credit。各欄位說明如下：
圖 3-7 all_course 資料表的屬性
Course_ID：課程代碼。由 7 個字母編制而成，由左至右說明如下：
學制(一個字母)：日四技(4)、日二技(2)、日五專(5)、日二專(3)、進四技(9)、、。
系別(兩個字母)：資管系(0B)、企管系(0A)、資工系(09)、電子系(05)、、、。
課程屬性(一個字母)：共同科(A)、通識(B)、專業必修(C)、專業選修(D)、、、。
課程編號(三個字母)：000 ~ 999
Course_name：課程名稱。各系可能開相同名稱的課程。
Dep_ID：表示此課程是哪一系所開的。
Required：必修或選修。
Credit：學分數。
(6)open_course 資料表
全校這學期所開的課程皆登錄於 open_course 資料表內，這就比較複雜，同一課程可能由多位老師開在不同班級上，每位老師可能開若干個課程。它包含 4 個欄位，說明如下：
圖 3-8 open_course 資料表的屬性
Open_course_ID：開課代碼。該表格的主鍵，我們採用數字，並請系統自動產生一個遞增不可重複的數字(自動編號)。
Course_ID：課程代碼。是參考到 all_course 的主鍵 course_ID，表示開哪一個課程。
Class_ID：班級代碼。表示該課程是開在哪一班級。
Teacher_ID：教師代碼。表示此課程是哪一位老師授課。
(7)select_course 資料表
如圖 3-9，資料表 select_course 是學生的選課記錄，並可登錄成績。它包含 6 個欄位，並且是雙主鍵(student_ID, open_course_ID)，表示哪一位學生、修讀哪一門課。其它是登錄平時成績(normal)、期中考成績(midTerm)、期末考成績(finalTerm)、與學期成績(final)。
圖 3-9 select_course 資料表屬性
(8)simple_students 視界
如圖 3-10，視界(view) simple_students 是簡化 students 資料表的欄位，一般選課作業並不需要那麼詳細的個人資料。它包含 4 個欄位，如下：
圖 3-10 simple_students 視界的屬性
(9)simple_teachers 視界
圖3-11，同樣，簡化 teachers 資料表欄位，如下：
圖 3-11 simple_teachers 視界的屬性
(10)public_course 視界
我們為了資料表的正規化問題(請參考拙著『資料庫管理系統』講義)，將許多資料拆到各個資料表內，如此會造成查詢資料的困難，因此，我們建立 public_course 視界來操作會比較容易。至於，製作技巧容後再說明。它包含 7 個欄位，如圖 3-12 所示。
圖 3-12 public_course 視界的屬性
當我們將資料分散儲存於各個資料表之後，是否可以從中搜尋到所要的資訊？這就是考驗資料庫設計的效益如何，我們用幾個範例來測試 course_db 是否真的可以使用。
(1)查詢『進四資管二丙』的學生名冊：
全校的學生都登錄於 students 資料表內，並由 class_ID 表示該學生是屬於哪一班，但它是班級代碼並非班級名稱，因此，我們必須先由 classes 資料表內，查詢出『四資管二丙』的班級代碼(class_ID)，再利用 class_ID 由 students 表內查出該班的學生。因此，欲找出此訊息須查詢 classes 與 students 兩資料表，將其關係獨立繪出如下：
(2)查詢『資訊工程系』教師名冊。
全校教師皆登錄於 teachers 資料表內，利用 Dep_ID 欄位來註明該教師是屬於哪一系，但它僅登錄系別代碼 (Dep_ID)。吾人要查『資訊工程系』教師，則需先由 department 表內查出它的 Dep_ID，再利用此 Dep_ID 由 teachers 表內查詢出該系教師名冊。查詢動作如下圖：
(3)查詢『電機工程系』的老師這學期開了那些課程：
這個學期全校所開的課程都記錄於 Open_course 資料表內，其中以 teacher_ID 註明是哪位老師所開的課程。要知道電機工程系的老師所開課程，必須先由 department 表內查出該系的 dep_ID，再利用此 dep_ID 由 teachers 表內查出電機系所有老師的 teacher_ID。再利用這些 teacher_ID 由 open_course 表中查詢出他們所開的課程的 course_ID，接著，再利用這些 course_ID 由 all_course 表中查詢，該系老師所開課程的名稱。因此，欲查詢某一系所有老師所開的所有課程，必須動用到四個資料表，如下圖所示。
(4)查詢『粘添壽』老師開課課程，以及所開課程的班級之名稱。
必須先由 teachers 表中查詢出粘添壽老師的 teacher_ID，再利用它由 open_course 表中查詢出，這位老師這學期所開的課程為何。但 opene_course 僅登錄 course_ID 與 class_ID，必須分別由 all_course 表與 classes 表中查詢出它們的名稱。
(5)查詢學號 4011203 修讀那些課程及其成績。
全校學生修課及成績皆登錄於 select_course 表內，由學號可以查詢出學生選讀開課代碼，以及成績，再利用開課代碼，由 open_course 表內查出其課程代碼(course_ID)，再利用 course_ID 由 all_course 表中查詢出它的課程名稱、必選修與學分數，查詢動作如下圖所示。
(6)請查詢『進四資管二丙』這學期開了那些課程
此問題牽涉到三個資料表，如下圖所示。先由班級名稱由 classes 表中查詢出它的 class_ID，再利用 class_ID 由 open_course 表中查詢出該班這學期開了那些課程的 course_ID，再利用 course_ID 由 all_course 查詢該課程的課程名稱、必選修與學分數。
(7)查詢粘添壽老師這學期所授課資料庫管理系統的學生名冊：
如欲查詢某位老師的某一課程的學生名冊，此牽涉到六個資料表，查詢動作如下圖所示。
