資料庫設計與教學 2015/09/30 John.

Presentation on theme: "資料庫設計與教學 2015/09/30 John."— Presentation transcript:

1 資料庫設計與教學 2015/09/30 John

2 大綱 資料表關聯 資料庫正規化 SQL基本介紹 安裝與建立資料庫、資料表 國立清華大學 工業工程與工程管理學系

3 資料表關聯

4 資料庫結構 Data Row Data Table Data Table：表示記憶體中資料的一個資料表
國立清華大學 工業工程與工程管理學系

5 主索引(primary key) 必須為資料表內獨一無二的欄位,值不可重覆 身份證字號 姓名 性別 就讀學校 A1234567 Tina
Female NTHU F Tom Male NCTU A Jane NTU 國立清華大學 工業工程與工程管理學系

6 系統分析-資料表關聯 設計資料表與資料表之間的關聯 關聯：即為存取資料時，可從資料表得知另一張資料表的相關內容
依數量上的基數(Cardinality)關係，分為3種： （1）一對一 （2）一對多 （3）多對多 國立清華大學 工業工程與工程管理學系

7 Example-學校 兩張資料表尚無關聯! 老師(教師代號,教師姓名,教師電話) 學生(學號,學生姓名,學生電話)
國立清華大學 工業工程與工程管理學系

8 一對一 科系(代號,名稱,位置,學生代表學號) 學生(學號,學生姓名,學生電話)
科系由多位學生組成，而各學生有自己的學號，學號也就是學生對應到自己系上專屬的代號，關係為一對一 國立清華大學 工業工程與工程管理學系

9 一對多 老師(教師代號,教師姓名,教師電話) 課程(課程代號,教師代號,課程名稱,上課教室,學分數)
在任何學期當中，每一位老師通常不只開設一門課程，可能同時開設多門學士班或碩士班的課程，關係為一對多 國立清華大學 工業工程與工程管理學系

10 多對多 學生(學號,學生姓名,學生電話) 課程(課程代號,學號,課程名稱,上課教室,學分數)
在任何學期當中，每一位學生通常不只修習一門課程，可能一次修多門課程；而每一門課程又由許多位學生修習，關係為多對多 國立清華大學 工業工程與工程管理學系

11 資料庫正規化

12 資料庫正規化(Normalization)
主要是對表格做分割的動作 沒有正規化會造成： 容易有資料重覆儲存的浪費情形 資料在做新增、刪除或修改動作時產生異常情形 國立清華大學 工業工程與工程管理學系

13 資料表範例 供應商編號 供應商名稱 聯絡人 區域 代號 產品 編號 名稱 單價 0001 松柏 陳雪芬 A01 基隆市 P01 奶茶 15
蘋果汁 12 0002 皎月 劉名山 B01 桃園縣 P03 汽水 0003 星空 沈芯鈺 C01 台北市 P04 綠茶 10 P05 黑咖啡 20 0004 皓雲 林郁芳 B02 新竹市 0005 秋楓 盧松浩 A02 台中市 國立清華大學 工業工程與工程管理學系

14 新增異常 新供應商『荷風』具有供應商的相關資料，唯獨缺少所提供的產品資料→產品欄位會是空值(null) 供應商編號 供應商名稱 聯絡人 區域
代號 產品 編號 名稱 單價 0001 松柏 陳雪芬 A01 基隆市 P01 奶茶 15 P02 蘋果汁 12 0002 皎月 劉名山 B01 桃園縣 P03 汽水 0003 星空 沈芯鈺 C01 台北市 P04 綠茶 10 P05 黑咖啡 20 0004 皓雲 林郁芳 B02 新竹市 0005 秋楓 盧松浩 A02 台中市 0006 荷風 許琦琦 null 國立清華大學 工業工程與工程管理學系

15 刪除異常 供應商『星空』不再提供『綠茶 』與『 黑咖啡』兩項產品→資料表將直接刪除『星空』的資料 供應商編號 供應商名稱 聯絡人 區域 代號
單價 0001 松柏 陳雪芬 A01 基隆市 P01 奶茶 15 P02 蘋果汁 12 0002 皎月 劉名山 B01 桃園縣 P03 汽水 0003 星空 沈芯鈺 C01 台北市 P04 綠茶 10 P05 黑咖啡 20 0004 皓雲 林郁芳 B02 新竹市 0005 秋楓 盧松浩 A02 台中市 國立清華大學 工業工程與工程管理學系

16 修改異常 修改供應商『松柏』的聯絡人為『陳雪芙』 全部欄位都必須修改→易出錯、耗費時間 供應商編號 供應商名稱 聯絡人 區域 代號 產品
單價 0001 松柏 陳雪芙 A01 基隆市 P01 奶茶 15 陳雪芬 P02 蘋果汁 12 0002 皎月 劉名山 B01 桃園縣 P03 汽水 0003 星空 沈芯鈺 C01 台北市 P04 綠茶 10 P05 黑咖啡 20 0004 皓雲 林郁芳 B02 新竹市 0005 秋楓 盧松浩 A02 台中市 國立清華大學 工業工程與工程管理學系

17 資料庫正規化(Normalization)
正規化的目的： 簡化單一資料表的資料量 加快資料庫存取速度 正規化類型:第一至第三正規化 正規化是在資料庫中組織資料的程序。其中包括建立資料表，以及在這些資料表之間根據規則建立關聯性， 這些規則的設計目的是：透過刪除重複性和不一致的相依性，保護資料並讓資料庫更有彈性。  重複的資料會浪費磁碟空間，並產生維護方面的問題。如果必須變更現有資料，並且該資料的位置超過一個以上，就必須在所有位置上以完全相同的方式進行變更。如果資料只儲存於 [客戶] 資料表中，而不儲存於資料庫中任何其他位置，變更客戶地址就會更容易執行。 國立清華大學 工業工程與工程管理學系

18 第一正規化 定義:資料表一個儲存格只能儲存一個值 去除『多值屬性』 客戶編號 統一編號 公司名稱 聯絡人 1 1234 First Tom
John 2 5678 Second Mary Jane 國立清華大學 工業工程與工程管理學系

19 第一正規化結果 客戶編號 統一編號 公司名稱 聯絡人 1 1234 First John Tom 2 5678 Second Mary
Jane 國立清華大學 工業工程與工程管理學系

20 第二正規化 定義:依據相依性，讓表單的非主key欄位『完全功能相依』所有主索引(primary key) 。
則違反第二正規化 當設計table時，有2個或2個以上的主索引(primary key) 要注意是否有違反第二正規化 如果違反，則分割table 如果主索引(primary key)只有1個欄位 則沒有第二正規化的問題 國立清華大學 工業工程與工程管理學系

21 第二正規化 相依性(一)：{產品編號} → {產品名稱} 相依性(二)：{供應商編號，產品編號} → {單價}
相依性(三)：{供應商編號} → {供應商名稱，聯絡人，區域，區域代號} 因此主key必須設成{供應商編號，產品編號}，但是會有 a non-key column is dependent on a component of the primary key 的問題 此table的主key=?? 主key=「供應商編號」或是主key=「產品編號」→皆會有重複值出現的問題 此table沒有2筆資料﹛供應商編號，產品編號﹜此兩欄位值都一樣，因此主key可設成複合key=﹛供應商編號，產品編號﹜→相依性(二) 相依性(一) →只要知道產品編號(不需知道供應商編號)就知道產品名稱→給定一「產品編號」可以找出唯一一個「產品名稱」 相依性(三) →有非主key的欄位只依賴部分的主key→違反”第二正規化” 國立清華大學 工業工程與工程管理學系

22 第二正規化結果 三個相依性，切割成三個『完全功能相依』 {產品編號} → {產品名稱} {供應商編號，產品編號} → {單價}
{供應商編號} → {供應商名稱，聯絡人，區域，區域代號} 要讓原先設計的資料表單滿足第二正規化，避免non-key column is dependent on a component of the primary key的問題，要依據相依性，將原先設計的資料表單切割成新表單，讓表單的非主key欄位『完全功能相依』所有主key欄位。 國立清華大學 工業工程與工程管理學系

23 第三正規化 定義:已符合第一及第二正規化，所有欄位只與主索引(primary key)相依，使功能相依關係單純化
去除間接相依性與遞移相依性。 訂單(訂單編號,接單日期,下單公司) 客戶(下單公司,聯絡人,電話) 遞移性(Transitivity)：若 A→B，且 B→C 則 A→C。 部份功能相依(Partial Functional Dependence):假設 X→Y，若 H 為 X 的部份屬性 (也就是 H 包含於 X 中) 使得 H→Y 稱之 Y 部份功能相依於 X；否則稱之 Y 完全功能相依於 X。 國立清華大學 工業工程與工程管理學系

24 第三正規化結果 訂單(訂單編號,接單日期,下單公司) 客戶(下單公司,聯絡人編號) 聯絡人(聯絡人編號,電話)
國立清華大學 工業工程與工程管理學系

25 SQL基本介紹

26 SQL介紹 SQL: Structured Query Language SQL 可以從資料庫中擷取、排序和篩選特定想要的資料。
一個用來查詢、更新和管理關聯式資料庫的語言。 SQL 可以從資料庫中擷取、排序和篩選特定想要的資料。 您可以使用 SQL 的 SELECT 陳述式在任何資料表名稱、查詢名稱、或欄位名稱可以被接受的地方 現今市面上所有關聯性資料庫軟體幾乎都支援SQL。(Microsoft Visual Basic / Microsoft Access Jet database engine) 國立清華大學 工業工程與工程管理學系

27 SQL 語言包含四個部分 資料定義語言 (DDL－Data Definition Language)
資料操縱語言 (DML－Data Manipulation Language) 資料查詢語言 (DQL－Data Query Language) 資料控制語言 (DCL－Data Control Language) 國立清華大學 工業工程與工程管理學系

28 安裝資料庫 安裝網址：SQL Server with Tools
注意：在Windows XP上必須先安裝下列軟體後才能安裝SQL Server 2008 R2： 步驟 1：下載並安裝 Microsoft .Net Framework 3.5 SP1 步驟 2：下載並安裝 Windows Installer 4.5 步驟 3：下載並安裝 Windows PowerShell 1.0 以上元件都安裝完成後，再來安裝Express系統。 國立清華大學 工業工程與工程管理學系

29 點擊【新的安裝或將功能加入到現有安裝】 國立清華大學 工業工程與工程管理學系

30 全選後按下一步 國立清華大學 工業工程與工程管理學系

31 選具名執行個體SQL Express後按下一步
國立清華大學 工業工程與工程管理學系

32 點擊所有SQL服務都使用相同帳戶，並指定帳戶名稱為NT_AUTHORITY\SYSTEM
國立清華大學 工業工程與工程管理學系

33 驗證模式請選用混合模式，並輸入密碼。 國立清華大學 工業工程與工程管理學系

34 安裝好系統後要再做設定，讓Client程式能連線。
設定資料庫連線 安裝好系統後要再做設定，讓Client程式能連線。 由開始功能表→【所有程式】→【SQL Server 2008 R2】→【組態工具】→點選【SQL Server組態管理員】。  國立清華大學 工業工程與工程管理學系

35 在右方的【TCP/IP】的右鍵功能表裡選【內容】。
點擊【SQL Server網路組態】→【SQLEXPRESS的通訊協定】，將右方的【具名管道】與【TCP/IP】用右鍵功能表由【已停用】變更為【已啟用】。 在右方的【TCP/IP】的右鍵功能表裡選【內容】。  國立清華大學 工業工程與工程管理學系

36 點擊【IP位址】分頁，將最下方的【IPAll】→【TCP通訊埠】設定為1433後按確定。
國立清華大學 工業工程與工程管理學系

37 再點擊SQL Server服務，選右方第一個【SQL Server (SQLEXPRESS)】後，右上方的重新啟動，讓SQL Server重新啟動使剛才的修改生效。 
國立清華大學 工業工程與工程管理學系

38 建立資料庫、資料表

39 SQL 建立資料庫 資料庫→右鍵→新增資料庫

40 輸入資料庫名稱→確定

41 選擇剛新增的資料庫底下的資料表→右鍵→新增資料表
SQL 建立資料表 選擇剛新增的資料庫底下的資料表→右鍵→新增資料表

42 輸入資料行名稱 (欄位)、類型、是否允許Null
若輸入為字串，資料類型一律可改為nvarchar(MAX)，但主key該欄位請設為nvarchar(50) 輸入資料行名稱 (欄位)、類型、是否允許Null 國立清華大學 工業工程與工程管理學系

43 資料型態 數值資料 (Numeric Data)
SQL 的數值型態有 integer, float, money等，使用數值資料有一個好處就是你能搭配內建的數值函數來做資料處理，例如 SUM() 函數即可直接獲得該數值欄位的總合 字串(元)資料 (Character & Strings Data) 儲存字元或符號之資料型別。 日期/時間資料 (Date Data) 用來記錄日期/時間的資料型別，有 date, time, timestamp等。 國立清華大學 工業工程與工程管理學系

44 空值，沒有資料存在於欄位。通常在建立資料表時，你可以設定欄位是否允許空值：
布林值 (Boolean Data) true/false, Yes/No, 1/0。 空值 (NULL Data) 空值，沒有資料存在於欄位。通常在建立資料表時，你可以設定欄位是否允許空值： 布林值，就是真假值，True 與False，或是1 與0，1 代表True，0 代表False。 國立清華大學 工業工程與工程管理學系

45 資料表標籤欄位點選右鍵→儲存資料表 國立清華大學 工業工程與工程管理學系

46 輸入資料表名稱→確定，即建立資料表成功 國立清華大學 工業工程與工程管理學系

47 新增資料庫圖表 國立清華大學 工業工程與工程管理學系

48 國立清華大學 工業工程與工程管理學系

49 選擇需要的資料表→加入，即加入資料表成功
國立清華大學 工業工程與工程管理學系

50 國立清華大學 工業工程與工程管理學系

51 選取購物車明細資料表的書名欄位，按左鍵，拖曳至書本明細資料表的書名欄位。
國立清華大學 工業工程與工程管理學系

52 國立清華大學 工業工程與工程管理學系

53 國立清華大學 工業工程與工程管理學系

54 國立清華大學 工業工程與工程管理學系

55 國立清華大學 工業工程與工程管理學系

56 國立清華大學 工業工程與工程管理學系

57 國立清華大學 工業工程與工程管理學系

58 ER Model 國立清華大學 工業工程與工程管理學系

59 Q&A Time