資料庫管理 Homework #2 楊立偉教授 台灣大學工管系 2018
大綱 作業說明 安裝MySQL 使用MySQL 2.1 for Windows 2.2 for Mac 3.1 設計 3.2 建表 3.3 建資料 3.4 備份及還原
1. 作業說明 繪製完整的 E-R model diagram 利用 MySQL 建立表格與關聯 建立測試用的範例資料 共有六題,各組分派二題
Scenario A 學校委託你整頓圖書館,你依照學校開出的需求(business rules)重新規劃資料庫 以本校之圖書館為限,包含多個分館 需建立完整書目資訊,每一書目有多本藏書 書目資訊至少包含 Dublin Core 的前10個欄位 每一本藏書只會歸屬於一個分館 每一本藏書都有獨立的藏書號與藏書日期 需建立書目來源(出版商資料) 借書人為學生,每人可以借多本書,需建立借書紀錄 其它部份學校沒意見,請自行發揮
Scenario B 為發掘人才、促進就業,學校請你開發校園徵才求職媒合資料庫,需求(business rules)如下: 為了嚴格篩選,廠商需先登記基本資料,才能開出職缺。職缺中需描述職稱、工作內容、工作時間、薪資待遇等。廠商及職缺都需指定一到多個產業分類,以方便查找。 有興趣的同學可登記履歷,包括基本資料、經驗、專長、有興趣的產業分類(可多選) 等。 同學可以把廠商加入最愛清單,也可以對職缺投履歷, 其它部份學校沒意見,請自行發揮
Scenario C 為愛惜資源,保護地球,學校請你開發校園二手物品交易資料庫,需求(business rules)如下: 以本校同學為限,不論是賣東西或買東西,都需先登記詳細資料 同學可建立二手商品資訊,包括商品名稱、規格等,以及歸屬一或多個分類。同學可對商品舉行拍賣,包括拍賣期間、底價、交易方式、交易地點等。 同學可以參與拍賣出價 歷史出價紀錄需保留,以利未來統計熱門商品之用 其它部份學校沒意見,請自行發揮
Scenario D 為造福同學,請你開發新一代交友資料庫,結合交友與活動,需求(business rules)如下: 以本校同學為限,參加前需先登記詳細資料,其中包括興趣主題(每人可有多個興趣主題) 同學可建立活動資訊,包括會展演等,每項活動並可屬於一到多個興趣主題 同學間可進行邀約,請其它同學一起參加活動,參加人可決定是否接受 歷史邀約紀錄需保留,以利未來統計熱門活動之用 其它部份同學沒意見,請自行發揮
Scenario E 學校為整頓腳踏車亂象,委託你開發 ntUbike 資料庫,需求(business rules)如下: 為記名租借,以本校同學為限,租借前需先註冊 腳踏車將編號列管,並詳記廠牌、規格等資訊 於校內多個地點建立租借站,需建立租借站資訊 採預約租借制,將每日分成若干時段,訂價不同,按時段來出租 每人可租借多台;並可甲站借乙站還 歷史租借紀錄需保留,以利未來統計腳踏車王之用 其它部份學校沒意見,請自行發揮
Scenario F 冬天到了,宿舍同學很不想外出吃飯,請你開發訂便當資料庫,需求(business rules)如下: 以本校同學為限,包含多個宿舍 需建立餐廳與菜單資訊(含價格) ,供同學訂購 每張訂單可訂多樣/多個便當,以滿足大胃需求 於固定地點繳費領便當,可提供多個領取地點;同學下訂時可以選擇領取地點 每日每餐舉辦,或隨時預約下訂,或其它模式均可 歷史訂購紀錄需保留,以利未來統計熱門菜單之用 其它部份宿舍同學沒意見,請自行發揮
作業要求 (1) 完整的 E-R model diagram 有幾個Entity 有哪些Relationship? Cardinality為何 標上屬性 檢查Entity、Relationship、屬性的名稱是否適當
作業要求 (2) 每張表格建立 30 筆測試資料,越多越好 以MySQL建立表格與關聯 滿足資料合理性,並檢查關聯是否正確 除了Entity,有哪些Relationship要轉成表格 將屬性開成欄位,並訂出 type, length, constraint 檢查所有表格的PK 不同的Relationship / Cardinality要怎麼轉成FK 每張表格建立 30 筆測試資料,越多越好 滿足資料合理性,並檢查關聯是否正確
評分標準 分成10個等級 依詳盡用心程度加分 Entity, Attribute 正確合理 Relationship, Degree 正確合理 Cardinality 正確合理 轉成表格後 PK, FK 正確清楚 符合正規化 (表格內無彼此相依欄位) 測試資料 正確合理 Business Rules 均滿足 … ♥ 滿分 依詳盡用心程度加分
Deadline 於 2018.10.25 6am 前繳至 ceiba 由組長繳交即可 MySQL請繳交mwb及sql檔(後述),檔案名稱請取名「組別_題號_hw2.mwb」、「組別_題號_hw2.sql」 E-R model請貼在一個 Word 檔中,並加入文字說明 取名為「組別_題號_hw2.doc」 所有檔案一同壓縮為「組別_hw2.zip」
題目分派 每組分派二題 期中考前後將每題挑選優秀組別上台簡報 組別 分派 #1 分派 #2 1 E F 2 D 3 4 C 5 6 7 B 8 9 10 11 A 12 13 14 15 16 17
附錄 : 製圖工具 以MySQL Workbench製作後截圖下來 以雲端服務draw.io繪製後匯出圖檔 https://www.draw.io 以Microsoft Visio繪製 以Microsoft Excel或PowerPoint製作 (不建議)
2. 安裝MySQL
概念圖 MySQL Community Server是目前最受歡迎的關聯式資料庫管理系統之一 採GPL授權(開放免費) 啟動 啟動 MySQL Workbench 連接 MySQL Server 管理及設計 工具 資料庫系統 DBMS
2.1 for Windows 先在本機安裝MySQL Server及MySQL Workbench https://dev.mysql.com/downloads/mysql/ Windows請下載MySQL Installer for Windows完整包
點此下載,之後選線上安裝版或下載安裝版均可
註冊登入或 直接下載均可
選Custom自訂安裝,選擇這三項 MySQL Server MySQL Workbench Connector/ODBC (最後這項Ch8會教) (以64位元版為例,請選擇適合自己電腦作業系統的版本)
部分Windows機器需先安裝Microsoft Visual C++ 2013/2015 redistributable可轉發套件,請至微軟網站下載 https://support.microsoft.com/zh-tw/help/2977003/the-latest-supported-visual-c-downloads
安裝完成後,進行MySQL Server設定 選擇Standalone MySQL Server (單機伺服器) 選擇Development Computer (開發機),預設連接埠號為3306,並開啟防火牆以供網路存取 最高權限帳號為root,設定密碼;必要時,也可在下方增加使用者帳號 勾選Configure MySQL Server as a Windows Service (安裝成系統服務) 勾選Start the MySQL Server at System Startup (開機即執行) 其他預設不變
設定完成畫面
測試連通 啟動MySQL Workbench,選擇localhost(本機)連線,或是按加號以新增一個連線,取一個名字,連至localhost或127.0.0.1(均為本機之意),輸入帳號密碼,測試連線成功即可
進入此畫面表示安裝及連接成功
2.2 for Mac 先在本機安裝MySQL Server及MySQL Workbench https://dev.mysql.com/downloads/mysql/ Mac請分開安裝。其中MySQL Server如下圖
Mac安裝過程會產生最高權限帳號root的暫存密碼,請牢記 (或是新版會提示請輸入自訂密碼)
啟動完mysql,開啟MySQL Workbench,按加號以新增一個連線,取一個名字,連至127 啟動完mysql,開啟MySQL Workbench,按加號以新增一個連線,取一個名字,連至127.0.0.1(本機之意),輸入帳號root,以及暫存密碼,之後會被提示需更新密碼 測試連線成功即可
如果未能連接成功,可能是MySQL Server未自動啟動,請按F4搜尋terminal,開啟終端機,輸入以下指令來啟動或關閉mysql,然後再用MySQL Workbench連接測試一次 sudo /usr/local/mysql/support-files/mysql.server start 啟動 sudo /usr/local/mysql/support-files/mysql.server stop 關閉
3. 使用MySQL
3.1 設計 啟動MySQL Workbench 選擇File > New Model 開始設計、建表、建資料 線上教學 https://dev.mysql.com/doc/workbench/en/wb-data-modeling.html
建立E-R diagram add Diagram > 開啟空白畫布 放置新表格 entity 放置關係 relationship 編輯表格名稱 編輯欄位、欄位名稱 欄位型別: 常用 int, varchar(n), decimal(p,s), datetime 相關設定: pk, not null, unique, auto incremental 預設值: default 放置關係 relationship 選擇cardinality;有自動修正及完成機制
Model > Object Notation > Classic, Relationship Notation > Crow's Foot
建立表格及欄位
選取1:n圖例,先選n-side entity,再選1-side entity 自動建立1:n relationship 及foreign key欄位 (可事後編輯)
透過not null之勾選, 決定是否為optional
3.2 建表 File > Save Model Database > Forward Engineer… 連接至本地資料庫系統, 預設帳號密碼
補充說明 Forward Engineer動作執行後即建立表格,為一次性動作;ER圖並不會與Database Schema同步連動
3.3 建資料 完成後,Database > Connect to Database… 切換頁籤,即可看到建立的表格 (或按一下更新) 點選表格旁符號可出現Result Grid資料編輯表格 逐筆資料編輯後,按下apply可執行寫入動作 (更改及刪除時亦同) 註: Workbench之動作均轉為SQL指令執行,是很好的自學參考
更新 出現編輯表格 進行資料編輯 (增刪修) 套用
3.4 備份及還原 E-R Model 請存成 .mwb 檔案;資料庫請用Data Export備份成 .sql 檔案;兩者一同壓縮打包後繳交 選取資料庫後 輸入備份檔名
選擇左邊MANAGEMENT>Data Import,選擇Import from Self-contained File,指向 選擇左邊MANAGEMENT>Data Import,選擇Import from Self-contained File,指向.sql檔案,點選Start Import 即可還原資料庫 (如下圖) 註: MySQL的備份與還原均是透過SQL指令完成。若欲還原的資料庫已存在,則還原動作會失敗 (無法覆寫)
補充 (1) 亦可使用SQL指令建立表格與關聯 CREATE TABLE CREATE TABLE publisher ( pid char(50), name char(50), addr char(50), tel char(50), primary key (pid)); CREATE TABLE book ( ISBN char(50), title char(50), creator char(50), subject char(50), description char(50), pid char(50), primary key (bid), foreign key (pid) references publisher(pid));
補充 (2) 亦可使用SQL指令來建立範例資料 INSERT INTO INSERT INTO publisher (pid,name,addr,tel) VALUES ('P01','台大出版社','台北市','23630231'); INSERT INTO book (ISBN,title,creator,subject,description,pid) VALUES ('001','資料庫管理','楊大毛','電腦','這是一本好書','P01');
補充 (3) A部份範例 還有其它Entity與Relationship要補上
補充 (4) 注意有的關係上有屬性 若是使用其他繪圖工具如draw.io,可以比照課本轉畫成 Associative Entity,或直接將屬性標在Relationship旁邊亦可
Good LUCK !