資料庫管理 Homework #2 楊立偉教授 台灣大學工管系 2018.

Presentation on theme: "資料庫管理 Homework #2 楊立偉教授 台灣大學工管系 2018."— Presentation transcript:

1 資料庫管理 Homework #2 楊立偉教授 台灣大學工管系 2018

2 大綱 作業說明 安裝MySQL 使用MySQL 2.1 for Windows 2.2 for Mac 3.1 設計 3.2 建表
3.3 建資料 3.4 備份及還原

3 1. 作業說明 繪製完整的 E-R model diagram 利用 MySQL 建立表格與關聯 建立測試用的範例資料
共有六題，各組分派二題

4 Scenario A 學校委託你整頓圖書館，你依照學校開出的需求（business rules）重新規劃資料庫
以本校之圖書館為限，包含多個分館 需建立完整書目資訊，每一書目有多本藏書 書目資訊至少包含 Dublin Core 的前10個欄位 每一本藏書只會歸屬於一個分館 每一本藏書都有獨立的藏書號與藏書日期 需建立書目來源（出版商資料） 借書人為學生，每人可以借多本書，需建立借書紀錄 其它部份學校沒意見，請自行發揮

5 Scenario B 為發掘人才、促進就業，學校請你開發校園徵才求職媒合資料庫，需求（business rules）如下：
為了嚴格篩選，廠商需先登記基本資料，才能開出職缺。職缺中需描述職稱、工作內容、工作時間、薪資待遇等。廠商及職缺都需指定一到多個產業分類，以方便查找。 有興趣的同學可登記履歷，包括基本資料、經驗、專長、有興趣的產業分類(可多選) 等。 同學可以把廠商加入最愛清單，也可以對職缺投履歷， 其它部份學校沒意見，請自行發揮

6 Scenario C 為愛惜資源，保護地球，學校請你開發校園二手物品交易資料庫，需求（business rules）如下：
以本校同學為限，不論是賣東西或買東西，都需先登記詳細資料 同學可建立二手商品資訊，包括商品名稱、規格等，以及歸屬一或多個分類。同學可對商品舉行拍賣，包括拍賣期間、底價、交易方式、交易地點等。 同學可以參與拍賣出價 歷史出價紀錄需保留，以利未來統計熱門商品之用 其它部份學校沒意見，請自行發揮

7 Scenario D 為造福同學，請你開發新一代交友資料庫，結合交友與活動，需求（business rules）如下：
以本校同學為限，參加前需先登記詳細資料，其中包括興趣主題（每人可有多個興趣主題） 同學可建立活動資訊，包括會展演等，每項活動並可屬於一到多個興趣主題 同學間可進行邀約，請其它同學一起參加活動，參加人可決定是否接受 歷史邀約紀錄需保留，以利未來統計熱門活動之用 其它部份同學沒意見，請自行發揮

8 Scenario E 學校為整頓腳踏車亂象，委託你開發 ntUbike 資料庫，需求（business rules）如下：
為記名租借，以本校同學為限，租借前需先註冊 腳踏車將編號列管，並詳記廠牌、規格等資訊 於校內多個地點建立租借站，需建立租借站資訊 採預約租借制，將每日分成若干時段，訂價不同，按時段來出租 每人可租借多台；並可甲站借乙站還 歷史租借紀錄需保留，以利未來統計腳踏車王之用 其它部份學校沒意見，請自行發揮

9 Scenario F 冬天到了，宿舍同學很不想外出吃飯，請你開發訂便當資料庫，需求（business rules）如下：
以本校同學為限，包含多個宿舍 需建立餐廳與菜單資訊（含價格） ，供同學訂購 每張訂單可訂多樣/多個便當，以滿足大胃需求 於固定地點繳費領便當，可提供多個領取地點；同學下訂時可以選擇領取地點 每日每餐舉辦，或隨時預約下訂，或其它模式均可 歷史訂購紀錄需保留，以利未來統計熱門菜單之用 其它部份宿舍同學沒意見，請自行發揮

10 作業要求 (1) 完整的 E-R model diagram 有幾個Entity
有哪些Relationship? Cardinality為何 標上屬性 檢查Entity、Relationship、屬性的名稱是否適當

11 作業要求 (2) 每張表格建立 30 筆測試資料，越多越好 以MySQL建立表格與關聯 滿足資料合理性，並檢查關聯是否正確
除了Entity，有哪些Relationship要轉成表格 將屬性開成欄位，並訂出 type, length, constraint 檢查所有表格的PK 不同的Relationship / Cardinality要怎麼轉成FK 每張表格建立 30 筆測試資料，越多越好 滿足資料合理性，並檢查關聯是否正確

12 評分標準 分成10個等級 依詳盡用心程度加分 Entity, Attribute 正確合理
Relationship, Degree 正確合理 Cardinality 正確合理 轉成表格後 PK, FK 正確清楚 符合正規化 (表格內無彼此相依欄位) 測試資料 正確合理 Business Rules 均滿足 … ♥ 滿分 依詳盡用心程度加分

13 Deadline 於 2018.10.25 6am 前繳至 ceiba 由組長繳交即可
MySQL請繳交mwb及sql檔(後述)，檔案名稱請取名「組別_題號_hw2.mwb」、「組別_題號_hw2.sql」 E-R model請貼在一個 Word 檔中，並加入文字說明 取名為「組別_題號_hw2.doc」 所有檔案一同壓縮為「組別_hw2.zip」

14 題目分派 每組分派二題 期中考前後將每題挑選優秀組別上台簡報 組別 分派 #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

15 附錄 : 製圖工具 以MySQL Workbench製作後截圖下來 以雲端服務draw.io繪製後匯出圖檔
以Microsoft Visio繪製 以Microsoft Excel或PowerPoint製作 (不建議)

16 2. 安裝MySQL

17 概念圖 MySQL Community Server是目前最受歡迎的關聯式資料庫管理系統之一 採GPL授權(開放免費) 啟動 啟動
MySQL Workbench 連接 MySQL Server 管理及設計 工具 資料庫系統 DBMS

18 2.1 for Windows 先在本機安裝MySQL Server及MySQL Workbench
Windows請下載MySQL Installer for Windows完整包

19 點此下載，之後選線上安裝版或下載安裝版均可

20 註冊登入或 直接下載均可

21 選Custom自訂安裝，選擇這三項 MySQL Server MySQL Workbench Connector/ODBC (最後這項Ch8會教) (以64位元版為例，請選擇適合自己電腦作業系統的版本)

22 部分Windows機器需先安裝Microsoft Visual C++ 2013/2015 redistributable可轉發套件，請至微軟網站下載

23 安裝完成後，進行MySQL Server設定
選擇Standalone MySQL Server (單機伺服器) 選擇Development Computer (開發機)，預設連接埠號為3306，並開啟防火牆以供網路存取 最高權限帳號為root，設定密碼；必要時，也可在下方增加使用者帳號 勾選Configure MySQL Server as a Windows Service (安裝成系統服務) 勾選Start the MySQL Server at System Startup (開機即執行) 其他預設不變

24 設定完成畫面

25 測試連通 啟動MySQL Workbench，選擇localhost(本機)連線，或是按加號以新增一個連線，取一個名字，連至localhost或 (均為本機之意)，輸入帳號密碼，測試連線成功即可

26 進入此畫面表示安裝及連接成功

27 2.2 for Mac 先在本機安裝MySQL Server及MySQL Workbench
Mac請分開安裝。其中MySQL Server如下圖

28 Mac安裝過程會產生最高權限帳號root的暫存密碼，請牢記 (或是新版會提示請輸入自訂密碼)

29 啟動完mysql，開啟MySQL Workbench，按加號以新增一個連線，取一個名字，連至127
啟動完mysql，開啟MySQL Workbench，按加號以新增一個連線，取一個名字，連至 (本機之意)，輸入帳號root，以及暫存密碼，之後會被提示需更新密碼 測試連線成功即可

30 如果未能連接成功，可能是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 關閉

31 3. 使用MySQL

32 3.1 設計 啟動MySQL Workbench 選擇File > New Model 開始設計、建表、建資料 線上教學

33 建立E-R diagram add Diagram > 開啟空白畫布 放置新表格 entity 放置關係 relationship
編輯表格名稱 編輯欄位、欄位名稱 欄位型別: 常用 int, varchar(n), decimal(p,s), datetime 相關設定: pk, not null, unique, auto incremental 預設值: default 放置關係 relationship 選擇cardinality；有自動修正及完成機制

34 Model > Object Notation > Classic,
Relationship Notation > Crow's Foot

35 建立表格及欄位

36 選取1:n圖例，先選n-side entity，再選1-side entity
自動建立1:n relationship 及foreign key欄位 (可事後編輯)

37 透過not null之勾選， 決定是否為optional

38 3.2 建表 File > Save Model Database > Forward Engineer…
連接至本地資料庫系統, 預設帳號密碼

39 補充說明 Forward Engineer動作執行後即建立表格，為一次性動作；ER圖並不會與Database Schema同步連動

40 3.3 建資料 完成後，Database > Connect to Database… 切換頁籤，即可看到建立的表格 (或按一下更新)
點選表格旁符號可出現Result Grid資料編輯表格 逐筆資料編輯後，按下apply可執行寫入動作 (更改及刪除時亦同) 註: Workbench之動作均轉為SQL指令執行，是很好的自學參考

41 更新 出現編輯表格 進行資料編輯 (增刪修) 套用

42 3.4 備份及還原 E-R Model 請存成 .mwb 檔案；資料庫請用Data Export備份成 .sql 檔案；兩者一同壓縮打包後繳交 選取資料庫後 輸入備份檔名

43 選擇左邊MANAGEMENT>Data Import，選擇Import from Self-contained File，指向
選擇左邊MANAGEMENT>Data Import，選擇Import from Self-contained File，指向.sql檔案，點選Start Import 即可還原資料庫 (如下圖) 註: MySQL的備份與還原均是透過SQL指令完成。若欲還原的資料庫已存在，則還原動作會失敗 (無法覆寫)

44

45 補充 (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));

46 補充 (2) 亦可使用SQL指令來建立範例資料 INSERT INTO
INSERT INTO publisher (pid,name,addr,tel) VALUES ('P01','台大出版社','台北市',' '); INSERT INTO book (ISBN,title,creator,subject,description,pid) VALUES ('001','資料庫管理','楊大毛','電腦','這是一本好書','P01');

47 補充 (3) A部份範例 還有其它Entity與Relationship要補上

48 補充 (4) 注意有的關係上有屬性 若是使用其他繪圖工具如draw.io，可以比照課本轉畫成 Associative Entity，或直接將屬性標在Relationship旁邊亦可

49 Good LUCK !