《RFID标准介绍》PPT课件

Chapter 4資 料 庫 規 劃 與 設 計 1 2/102 大 綱 4-1 資 料 庫 設 計 基 礎 4-2 資 料 模 型 4-3 資 料 庫 設 計 -實 體 關 係 模 型 4-4 資 料 庫 設 計 -正 規 化 4-5 實 作 案 例 4-6 SQL 3/102 4-1 資 料 庫 設 計 基 礎 資 料 庫 （ Database） 是 一 組 相 關 資 料 所 形 成 的 集合 ， 它 是 資 訊 系 統 中 用 來 儲 存 資 料 的 地 方 。 資 料 庫 是 以 電 子 化 的 檔 案 型 式 存 在 於 系 統 中 ， 其透 過 特 定 的 資 料 模 型 概 念 ， 將 資 料 集 合 進 行 組 織性 的 儲 存 ， 以 提 供 應 用 系 統 的 使 用 。 4/102 資 料 庫 管 理 系 統 資 料 庫 管 理 系 統 （ Database Management System）簡 稱 為 DBMS， 它 是 一 套 應 用 軟 體 系 統 ， 是 介 於使 用 者 與 作 業 系 統 之 間 ， 用 來 有 效 管 理 資 料 庫 。 常 見 的 DBMS有 SQL Server、 Oracle、 SyBase等 。 資 料 庫 管 理 系 統 的 主 要 功 能 資 料 定 義 資 料 處 理 安 全 性 管 理 資 料 庫 的 維 護 5/102 資 料 庫 系 統 資 料 庫 系 統 （ Database System） 是 一 套 包 含 資料 庫 、 資 料 庫 管 理 系 統 、應 用 程 式 ， 以 及 使 用 者在 內 的 系 統 。 一 般 也 將 資 料 庫 系 統 視為 是 一 個 包 含 資 料 庫 功能 的 資 訊 系 統 。 使 用 者SQL查 詢 / 應 用 程 式資 料 庫 管 理 系 統綱 要( 中 繼 資 料 ) 資 料 庫 6/102 資料庫系統相關的人員角色 資 料 庫 系 統 相 關 的 人 員 角 色 資 料 庫 管 理 員 資 料 庫 設 計 師 終 端 使 用 者 系 統 分 析 師 與 應 用 程 式 設 計 師 使 用 資 料 庫 系 統 的 優 點 減 少 大 量 重 覆 儲 存 的 資 料 、 減 少 不 一 致 的 資 料 、資 料 得 以 共 享 、 達 成 資 料 標 準 化 的 目 的 、 加 快資 料 擷 取 速 度 、 資 料 具 有 整 合 性 、 達 成 資 料 獨立 、 可 利 用 資 料 模 型 來 描 述 複 雜 的 關 係 、 快 速備 份 與 回 復 資 料 庫 、 提 供 不 同 的 視 界 等 。 7/102 4-2 資 料 模 型 資 料 模 型 是 資 料 庫 系 統 的 核 心 一 個 資 料 模 型 的 主 要 組 成 資 料 的 表 示 方 式 表 示 方 式 的 合 法 條 件 (整 合 限 制 條 件 ) 資 料 的 操 作 與 運 算 資 料 模 型 1. 階 層 式 資 料 模 型 2. 網 路 式 資 料 模 型 3. 關 聯 式 資 料 模 型 8/102 1. 階 層 式 資 料 模 型 階 層 式 資 料 模 型 （ Hierarchical Database Model） 其結 構 是 採 用 一 個 有 序 集 合 的 樹 狀 結 構 。 階 層 式 資 料 模 式 的 整 合 限 制 條 件 為 ： 任 何 的 子 節點 （ Child Node） 都 必 須 要 有 父 節 點 （ Parent Node）的 存 在 。 階 層 式 資 料 模 型 上 的 資 料 運 算 主 要 是 依 循 樹 狀 結構 （ Tree Structure） 上 的 運 算 原 則 。 階 層 式 資 料 模 型 只 適 合 用 來 描 述 一 對 多 的 關 係 ，但 不 適 合 多 對 多 關 係 的 描 述 。 9/102 2. 網 路 式 資 料 模 型 網 路 式 資 料 庫 模 型 （ Network Database Model） 在資 料 的 結 構 上 採 用 網 路 圖 形 來 連 結 資 料 。 網 路 式 資 料 模 式 的 整 合 限 制 條 件 為 ： 除 非 其 父 親節 點 已 經 存 在 了 ， 否 則 任 何 的 孩 子 節 點 都 不 能 加入 資 料 庫 中 。 網 路 式 資 料 模 型 上 的 資 料 運 算 主 要 依 循 圖 形（ Graph） 結 構 上 的 運 算 。 由 於 網 路 式 資 料 模 型 在 結 構 上 是 採 用 網 路 (圖 形 )的型 式 ， 因 此 適 合 用 來 描 述 多 對 多 問 題 。 10/102 3. 關 聯 式 資 料 模 型 關 聯 式 資 料 模 型 的 發 展 ， 最 早 是 在 1970 年 代 由IBM 公 司 的 研 究 人 員 Codd博 士 首 先 發 展 出 關 聯 式資 料 模 型 ， 並 於 1985 年 ， 對 外 提 出 關 聯 式 資 料 庫管 理 系 統 的 規 則 建 議 。 使 用 關 聯 式 模 型 為 基 礎 觀 念 所 建 立 的 資 料 庫 管 理系 統 ， 稱 之 為 關 聯 式 資 料 庫 管 理 系 統 關 聯 式 資 料 模 型 的 組 成 元 素 ， 包 含 了 資 料 結 構 、完 整 性 限 制 條 件 ， 以 及 資 料 操 作 或 運 算 等 三 大 元素 。 11/102 關 聯 （ Relation） 關 聯 也 稱 為 關 聯 表 ， 形 式 上 就 如 同 是 一 個 表 格 ，是 關 聯 式 資 料 模 型 所 採 行 的 表 示 結 構 。 關 聯 是 由 許 多 資 料 值 所 組 成 的 表 格 關 聯 可 視 為 是 一 組 橫 列 （ Row） 的 集 合 也 可 以 視 為 是 一 組 直 欄 （ Column） 的 集 合 關 聯 中 的 每 個 直 欄 亦 是 一 個 屬 性 關 聯 的 屬 性 數 目 （ 即 欄 位 數 ） 稱 為 此 關 聯 的 維 度 。 關 聯 可 視 為 是 經 由 許 多 屬 性 所 組 成 的 表 格 。 12/102 關 聯 綱 要 一 個 關 聯 的 實 體 架 構 稱 為 關 聯 綱 要 建 立 關 聯 必 須 要 有 ： 關 聯 名 稱 屬 性 集 合 (及 各 屬 性 的 資 料 型 態 ) 主 鍵 (與 外 來 鍵 ) 13/102 範 例 ： Product 關 聯 表 14/102 關 聯 表 具 備 四 個 性 質 關 聯 表 的 值 組 是 沒 有 順 序 的 關 聯 表 的 屬 性 是 沒 有 順 序 的 關 聯 表 中 的 屬 性 值 是 單 元 值 關 聯 表 中 不 含 重 複 的 值 組 15/102 關 聯 綱 要 的 表 示 法 16/102 限 制 條 件 在 關 聯 式 資 料 模 型 中 的 整 合 限 制 條 件 是 用 來 規 範資 料 在 關 聯 表 中 的 儲 存 、 刪 除 或 更 改 動 作 ， 以 防止 不 一 致 或 錯 誤 的 情 況 發 生 。 關 聯 式 資 料 模 型 的 整 合 規 則 個 體 整 合 限 制 ： 用 來 規 範 關 聯 內 部 的 限 制 條 件 參 考 整 合 限 制 ： 用 來 規 範 關 聯 與 關 聯 之 間 的 限制 條 件 在 關 聯 式 資 料 模 型 之 中 ， 單 一 關 聯 表 格 所 涉 及 到的 限 制 包 含 鍵 值 完 整 性 限 制 、 定 義 域 完 整 性 限 制 ，以 及 實 體 完 整 性 限 制 。 17/102 鍵 值 完 整 性 限 制 ： 關 聯 之 中 ， 鍵 值 具 有 不 可 為 空值 （ Null Value） 的 限 制 。 定 義 域 完 整 性 限 制 ： 對 於 不 符 合 期 待 的 值 ， 必 須被 限 制 輸 入 。 實 體 完 整 性 限 制 ： 關 聯 中 不 會 存 在 兩 筆 完 全 相 同的 值 組 。 參 考 整 合 性 限 制 是 指 定 在 兩 個 關 聯 之 間 ， 用 以 維持 兩 個 關 聯 值 組 的 一 致 性 。 參 考 整 合 性 限 制 ： 外 來 鍵 的 屬 性 值 必 須 在 所 參 考之 關 聯 的 主 鍵 屬 性 中 被 找 到 ， 或 者 為 Null。 18/102 關 聯 式 資 料 庫 範 例 19/102 4-3 資 料 庫 設 計 -實 體 關 係 模 型 資 料 庫 設 計 （ Database Design） 是 指 依 照 一 定 的 程序 、 方 法 和 技 術 ， 使 用 結 構 化 方 式 將 概 念 資 料 模型 轉 換 成 資 料 庫 的 過 程 。 資 料 庫 設 計 的 階 段 概 念 資 料 庫 設 計 :將 資 料 庫 需 求 轉 換 成 概 念 資 料模 型 的 過 程 。 邏 輯 資 料 庫 設 計 :針 對 特 定 的 資 料 庫 模 型 來 建 立邏 輯 資 料 模 型 。 實 體 資 料 庫 設 計 :將 邏 輯 資 料 模 型 轉 換 成 關 聯 式資 料 庫 管 理 系 統 的 一 系 列 SQL 指 令 敘 述 。 20/102 實 體 關 係 模 型 實 體 關 係 圖 經 常 被 使 用 在 概 念 資 料 庫 設 計 活 動 中 ，用 來 建 立 概 念 資 料 模 型 ， 這 種 圖 形 化 工 具 可 以 描述 使 用 者 和 設 計 者 眼 中 的 真 實 世 界 ， 作 為 之 間 的溝 通 橋 樑 。 實 體 關 係 模 型 中 的 實 體 可 以 對 應 關 聯 式 資 料 庫 模型 的 關 聯 表 ， 並 透 過 關 係 來 表 示 外 來 鍵 參 考 的 參考 概 念 ， 所 以 關 聯 式 資 料 庫 的 邏 輯 資 料 庫 設 計 也可 以 使 用 實 體 關 係 圖 建 立 邏 輯 資 料 模 型 。 實 體 關 係 模 型 是 一 套 觀 念 與 圖 形 符 號 ， 主 要 的 組成 元 件 包 含 ： 實 體 、 屬 性 、 關 係 等 。 21/102 範 例 :客 戶 -訂 購 -商 品 的 實 體 關 係 圖 22/102 1.實 體 型 態 與 實 體 實 例 實 體 型 態 (Entity Type)是 同 一 類 實 體 所 形 成 的 集 合 ，其 圖 形 符 號 以 矩 形 表 示 。 例 如 客 戶 實 體 型 態 可 表 示 為 : 商 品 實 體 型 態 可 表 示 為 : 實 體 型 態 的 實 例 (Instances)， 或 簡 稱 為 實 體 或 個 體 23/102 2. 關 係 型 態 與 關 係 實 例 關 係 型 態 (Relationship Types)屬 於 一 種 關 聯 實 體 型態 (Associate Entity Type)， 其 目 的 是 用 來 連 結 一 、二 個 或 多 個 相 關 的 實 體 型 態 ， 圖 形 符 號 是 使 用 菱形 方 式 表 示 。 關 聯 型 態 也 可 以 建 立 實 例 ， 用 來 表 示 一 個 訂 購 的行 為 或 事 件 。 24/102 3. 屬 性 屬 性 （ Attributes） 在 實 體 關 係 圖 中 是 以 橢 圓 型 圖形 符 號 的 表 示 節 點 。 實 體 關 係 圖 中 的 實 體 型 態 與關 係 型 態 可 以 擁 有 許 多 個 屬 性 。 例 如 ： 在 下 圖 中 ， 商 品 實 體 型 態 擁 有 商 品 編 號 、商 品 名 稱 、 顏 色 ， 與 價 格 等 四 個 屬 性 25/102 屬 性 的 類 型 (1) 簡 單 屬 性 與 複 合 屬 性 若 一 屬 性 的 形 成 由 許 多 屬 性 所 共 同 構 成 ， 亦 即 此一 屬 性 可 以 被 往 下 進 行 分 割 ， 切 割 成 數 個 細 微 的屬 性 ， 則 稱 之 為 複 合 屬 性 。 如 下 圖 中 的 姓 名 屬 性 。 簡 單 屬 性 是 不 可 再 分 割 的 屬 性 。 如 下 圖 中 的 員 工編 號 、 生 日 與 薪 資 等 屬 性 。 26/102 屬 性 的 類 型 (2) 單 值 屬 性 與 多 值 屬 性 多 值 屬 性 是 一 個 可 以 有 多 個 值 的 屬 性 (如 下 圖 中 的地 點 屬 性 )， 而 一 般 的 屬 性 都 僅 能 有 一 個 內 容 值 ，這 種 屬 性 我 們 就 稱 之 為 單 值 屬 性 (如 下 圖 中 的 部 門編 號 以 及 名 稱 屬 性 ) 。 27/102 屬 性 的 類 型 (3) 儲 存 屬 性 與 導 出 屬 性 某 些 屬 性 的 資 料 值 ， 在 需 要 時 可 以 動 態 的 經 由 計算 方 式 產 生 ， 因 此 無 須 被 實 際 儲 存 起 來 ， 這 種 屬性 稱 之 為 導 出 屬 性 。 例 如 範 例 中 的 年 紀 屬 性 可 以 經 由 儲 存 屬 性 生 日 換 算 得 到 ， 因 此 在 實 際 的 資 料 庫 中 ， 不 需 要 儲 存年 紀 屬 性 的 資 料 值 。 28/102 例 如 下 圖 中 的 客 戶 的 交 易 次 數 可 以 經 由 訂 購 紀 錄中 計 算 出 來 ， 因 此 交 易 次 數 屬 性 是 屬 於 導 出 屬 性 。 如 果 屬 性 是 實 體 型 態 中 ， 具 有 唯 一 性 ， 可 以 用 來識 別 實 例 的 屬 性 ， 我 們 稱 之 為 鍵 值 屬 性 。 例 如 客戶 與 商 品 的 編 號 屬 性 。 29/102 4. 關 係 型 態 上 的 特 性 關 係 型 態 的 Degree 是 參 與 此 關 係 的 實 體 型 態 數 目 。 關 係 型 態 若 連 接 了 兩 個 實 體 型 態 ， 表 示 此 關 係 型態 的 Degree 為 2， 也 稱 為 二 元 關 係 。 若 關 係 型 態 的Degree 為 3， 則 又 稱 三 元 關 係 。二 元 關 係 三 元 關 係 30/102 5. 關 聯 限 制 條 件 關 係 型 態 連 接 的 實 體 型 態 可 以 指 定 限 制 條 件 ， 稱為 關 聯 限 制 條 件 ， 包 含 ： 基 數 比 與 參 與 。 基 數 比 限 制 條 件 是 指 一 個 實 體 所 能 參 與 的 關 係 案例 數 。一 對 一 關 係 31/102 一 對 多 關 係多 對 多 關 係 32/102 參 與 限 制 條 件 是 指 實 體 集 合 的 實 例 是 全 部 或 部 份參 與 關 係 型 態 。 若 實 體 的 存 在 必 須 依 靠 關 係 型 態 與 另 一 實 體 產 生關 聯 來 決 定 ， 則 我 們 稱 之 為 全 部 參 與 ； 否 則 則 為部 份 參 與 。 33/102 弱 實 體 型 態 （ Weak Entity Types） 本 身 可 以 沒 有 任何 的 鍵 值 屬 性 。 弱 實 體 型 態 透 過 另 一 實 體 型 態 的 一 些 屬 性 所 組 成的 相 關 特 定 實 體 來 辨 認 ， 而 連 結 Weak Entity 與Identifying Owner 的 關 係 稱 為 辨 認 關 係 。 34/102 4-4 資 料 庫 設 計 -正 規 化 一 個 未 經 過 正 規 化 的 資 料 庫 是 一 個 不 良 的 資 料 庫設 計 與 實 作 方 式 。 資 料 庫 正 規 化 是 指 可 以 對 關 聯 進 行 適 當 的 分 割 處理 過 程 。 35/102 1. 為 什 麼 要 做 正 規 化 使 關 聯 有 良 好 的 語 意 避 免 因 為 資 料 重 複 性 造 成 資 料 不 一 致 36/102 避 免 新 增 異 常 要 新 增 一 個 新 的 部 門 (如 ： 9號 ,工 程 部 )， 但 因 為這 個 新 部 門 尚 未 有 所 屬 員 工 ， 導 致 主 鍵 欄 位 必須 為 Null而 無 法 完 成 部 門 的 新 增 。 37/102 避 免 修 改 異 常 將 2號 部 門 (研 發 )的 部 門 主 管 修 改 成 E4， 必 須 保證 範 例 中 的 第 三 筆 與 第 四 筆 值 組 的 DMGR屬 性皆 有 被 正 確 修 正 ， 否 則 將 會 造 成 資 料 不 一 致 的狀 況 。 38/102 避 免 刪 除 異 常 若 員 工 (莊 勝 為 ,E5)離 職 必 須 將 其 資 料 予 以 刪 除 ，但 此 刪 除 動 作 將 導 致 編 號 3號 的 生 產 部 門 被 意 外刪 除 了 。 39/102 2. 資 料 庫 的 正 規 化 做 法 40/102 第一階正規化（1NF） 第 一 階 正 規 化 （ 1NF） 的 要 求 是 ： 關 聯 表 沒 有 重 複出 現 的 欄 位 且 每 一 屬 性 中 只 能 存 放 單 一 的 資 料 值 。 滿 足 1NF 的 關 聯 不 能 有 多 值 屬 性 或 複 合 屬 性 的 存在 ， 因 為 這 一 類 的 屬 性 並 無 法 讓 屬 性 值 滿 足 不 可分 的 特 性 。 41/102 第二階正規化（2NF） 第 二 階 正 規 化 （ 2NF） 的 要 求 是 ： 滿 足 1NF 且 關 聯表 中 的 每 一 個 非 主 要 屬 性 必 須 完 全 功 能 相 依 於 主鍵 。 因 此 在 2NF 的 處 理 過 程 中 ， 主 要 在 於 消 除 非主 要 屬 性 與 主 鍵 之 間 的 部 份 功 能 相 依 性 。2NF 42/102 第三階正規化（3NF） 第 三 階 正 規 化 （ 3NF） 的 要 求 條 件 是 ： 滿 足 2NF 且每 一 個 非 主 要 屬 性 都 不 能 遞 移 相 依 於 主 鍵 。3NF 43/102 4-5 實 作 案 例 (財 產 盤 點 系 統 )1. 個 案 公 司 在 組 織 上 包 含 了 許 多 不 同 的 單 位 ， 這 些 單 位 都 有 其 正式 的 單 位 名 稱 ， 同 時 公 司 也 賦 予 各 單 位 一 個 獨 立 的 單 位 編 號 ，這 個 編 號 在 公 司 中 是 唯 一 的 ， 可 以 用 來 代 表 公 司 的 特 定 單 位 。2. 各 單 位 可 以 擁 用 許 多 人 員 ， 每 個 人 員 的 屬 性 資 料 中 ， 除 了 一 個公 司 所 配 發 的 特 定 編 號 之 外 ， 亦 包 含 人 員 的 姓 名 、 職 稱 、 電 話 ，與 地 址 等 基 本 資 料 屬 性 之 外 ， 為 了 讓 這 些 人 員 能 夠 順 利 登 入 系統 ， 執 行 才 採 盤 點 等 系 統 功 能 ， 因 此 必 須 同 時 紀 錄 每 位 人 員 的帳 號 、 密 碼 ， 以 及 E-mail等 相 關 資 訊 。3. 為 建 立 一 套 完 善 的 財 產 盤 點 系 統 ， 於 資 料 庫 中 必 須 清 楚 描 述 每項 財 產 的 編 號 ， 這 項 編 號 是 公 司 於 現 行 作 業 流 程 中 對 於 財 產 項目 所 作 的 唯 一 性 編 碼 ， 以 及 財 產 名 稱 、 該 財 產 項 目 的 功 能 、 數量 、 相 片 資 訊 ， 以 及 財 產 建 立 的 日 期 等 。 基 於 RFID新 系 統 的 應用 ， 我 們 於 系 統 中 將 會 紀 錄 貼 附 於 每 項 財 產 上 的 RFID Tag標 籤的 卡 號 ， 以 便 讓 RFID盤 點 系 統 能 夠 順 利 運 作 。 除 此 之 外 ， 在 每 項 財 產 項 目 紀 錄 被 建 立 時 ， 系 統 必 須 自 行 賦 予 每 一 財 產 記 錄 值組 一 個 具 有 唯 一 性 的 流 水 編 號 。 44/102 4. 為 了 能 夠 清 楚 表 示 財 產 設 備 設 置 的 地 點 位 置 ， 我 們 將 這 些 可 能設 置 或 存 放 財 產 的 地 點 位 置 進 行 編 碼 ， 對 於 公 司 中 每 一 個 存 放有 財 產 的 特 定 位 置 給 一 個 位 置 名 稱 ， 以 及 一 個 用 來 代 表 這 個 位置 的 編 號 ， 以 便 日 後 在 進 行 盤 點 作 業 或 執 行 其 它 系 統 功 能 時 ，對 於 財 產 的 設 置 位 置 能 有 一 個 共 同 的 標 號 與 稱 呼 名 稱 。5. 對 於 盤 點 作 業 時 可 能 出 現 的 狀 況 ， 我 們 經 過 分 析 後 將 其 歸 納 成正 常 、 送 修 ， 以 及 出 借 等 數 種 可 能 的 盤 點 狀 態 ， 並 賦 予 各 種 狀況 一 個 編 號 以 及 狀 況 名 稱 ， 並 保 留 一 個 備 註 的 屬 性 ， 用 來 對 某一 狀 況 的 細 節 進 行 必 要 的 描 述 或 說 明 。6. 對 於 公 司 的 人 員 而 言 ， 可 能 隸 屬 於 某 一 個 單 位 ， 另 就 部 門 單 位而 言 ， 一 個 單 位 可 能 會 擁 有 一 個 以 上 的 人 員 。 在 財 產 部 份 ， 許多 財 產 項 目 可 能 會 同 樣 被 某 一 個 單 位 所 保 管 ， 亦 即 一 個 單 位 可能 同 時 保 管 多 項 的 財 產 項 目 ， 而 許 多 財 產 項 目 也 可 能 會 位 於 同一 個 地 點 位 置 。7. 每 當 進 行 一 項 財 產 盤 點 作 業 時 ， 系 統 必 須 清 楚 紀 錄 盤 點 的 財 產 項 目 、 盤 點 人 員 、 盤 點 狀 況 、 盤 點 日 期 ， 並 描 述 財 產 的 詳 細 位置 ， 同 時 系 統 必 須 對 每 一 個 財 產 盤 點 作 業 ， 給 予 一 個 獨 立 的 盤點 紀 錄 編 號 ， 以 方 便 日 後 管 理 者 對 於 盤 點 記 錄 資 料 的 查 詢 等 資料 處 理 。 45/102 1. 資 料 庫 設 計 46/102 由 實 體 關 係 模 型 轉 換 成 一 組 關 聯 表 集 合 這 些 關 聯 表 必 須 經 過 正 規 化 處 理 後 並 確 認 綱 要 47/102 依 據 綱 要 產 生 可 以 建 立 關 聯 的 SQL單位資料關聯表Unit屬性意義屬性名稱資料型態鍵別參考關聯單位編號Unit_ID int PK單位名稱Unit_Name char 20Create table unit( Unit_ID int Not Null, Unit_Name char(20), Primary key (Unit_ID)財產資料關聯表Goods屬性意義屬性名稱資料型態鍵別參考關聯 自動編號Goods_ID int PK單位編號Unit_ID int FK unit財產名稱Goods_Name char 100財產編號Goods_Code char 20財產建立日期Goods_Coming_Daydatetime財產功能Goods_Function varchar 255數量Goods_Number int照片Photo varchar 100RFID卡號Goods_Tag varchar 20位置編號Area_ID int FK area Create table goods( Goods_ID int Not Null, Unit_ID int, Goods_Name char(100), Goods_Code char(20), Goods_Coming_Day datetime, Goods_Function varchar(255), Goods_Number int, Photo varchar(100), Goods_Tag varchar(20), Area_ID int, Primary key (Goods_ID), Foreign key (Unit_ID) references unit, Foreign key (Area_ID) references area) 48/102 2. 資 料 庫 實 作 使 用 SQL Server 建 立 (1) 資 料 庫 (2) 關 聯 表 格 (3) 關 聯 表 之 間 的 參 考 (4) 關 聯 表 關 係 圖 49/102 (1)建 立 資 料 庫 新 增 一 個 wealth 資 料 庫 50/102 (2)建 立 關 聯 表 格 新 增 並 定 義 關 聯 表 (Unit關 聯 表 ) 51/102 (2)建 立 關 聯 表 格 定 義 盤 點 狀 況 關 聯 表 (Situ)定 義 盤 點 位 置 關 聯 表 Area)定 義 人 員 資 料 關 聯 表 M mber) 定 義 財 產 資 料 關 聯 表 (Goods) 定 義 盤 點 紀 錄 關 聯 表 (Checkgoods) 52/102 (3)建 立 關 聯 表 之 間 的 參 考 參 考 的 設 定 是 ： 對 於 關 聯 表 中 所 存 在 的 外 來 鍵 ，將 其 指 定 到 所 參 考 關 聯 表 的 主 鍵 屬 性 中 。 例 如 ： 範 例 資 料 庫 中 的 Member 人 員 資 料 關 聯 表 存 在 外 來 鍵 Unit_ID的 member關 聯 表外 來 鍵 所 參 考 的 unit關 聯 表 53/102 Goods關 聯 表 的 Area_ID外 來 鍵 設 定 54/102 Goods關 聯 表 的 Unit_ID外 來 鍵 設 定 55/102 Checkgoods關 聯 表 的 Goods_ID外 來 鍵 設 定 56/102 Checkgoods關 聯 表 的 Member_ID外 來 鍵 設 定 57/102 Checkgoods關 聯 表 的 Situ_ID外 來 鍵 設 定 58/102 (4)建 立 關 聯 表 關 係 圖 wealth 資 料 庫 的 關 聯 表 關 係 59/102 4-6 SQL SQL（ Structured Query Language） 結 構 化 查 詢 語 言 ，是 使 用 者 用 來 操 作 DBMS 的 重 要 橋 樑 。 SQL 語 言 依 指 令 功 能 ， 可 以 分 成 三 類 資 料 定 義 語 言 DDL（ Data Definition Language） ：建 立 資 料 表 、 視 界 和 索 引 等 的 SQL 指 令 。 資 料 處 理 語 言 DML（ Data Manipulation Language） ： 資 料 表 記 錄 插 入 、 刪 除 、 更 新 和 查詢 指 令 。 資 料 控 制 語 言 DCL（ Data Control Language） ：資 料 庫 安 全 管 理 的 權 限 設 定 指 令 。 60/102 內 容 大 綱 1. 資 料 庫 與 關 聯 表 的 建 立 2. 資 料 新 增 3. 資 料 擷 取 4. 資 料 修 改 5. 資 料 刪 除 61/102 1. 資 料 庫 與 關 聯 表 的 建 立 建 立 資 料 庫 的 語 法 如 下Create database 資 料 庫 名 稱 例 如 建 立 一 個 名 為 MyWealth 資 料 庫 ， 則 SQL 的 語法 為 ： Create database MyWealth 62/102 刪 除 資 料 庫 的 語 法 如 下 ： Drop database 資 料 庫 名 稱 例 如 要 刪 除 一 個 名 為 MyWealth 資 料 庫 ， 則 SQL 的語 法 為 ： Drop database MyWealth 63/102 關 聯 表 的 建 立 一 個 關 聯 表 的 建 立 必 須 指 定 關 聯 表 的 名 稱 、 屬性 集 合 、 各 屬 性 的 資 料 型 態 、 主 鍵 ， 有 時 候 也會 包 含 外 來 鍵 等 必 要 成 員 。 以 單 位 資 料 關 聯 表 (Unit)為 例 ， 建 立 該 關 聯 表 的SQL指 令 為 ：Create table unit( Unit_ID int Not Null, Unit_Name char(20), Primary key (Unit_ID) ) 64/102 建 立 盤 點 狀 況 關 聯 表 (Situ) 的 SQL指 令 為 ： 建 立 盤 點 位 置 關 聯 表 (Area)的 SQL指 令 為 ：Create table situ( Situ_ID int Not Null, Situ_Name char(20), Situ_Desc varchar(255), Primary key (Situ_ID)Create table area( Area_ID int Not Null, Area_Txt varchar(255), Primary key (Area_ID) 65/102 建 立 Member關 聯 表 的 SQL指 令 為 ：Create table member( Member_ID int Not Null, Unit_ID int, Member_Name char(20), Member_title char(20), Member_phone char(20), Member_Address char(100), Member_mail char(30), Login_ID char(10), Login_PWD char(10), Primary key (Member_ID), Foreign key (Unit_ID) references unit ) 66/102 建 立 Goods關 聯 表 的 SQL指 令 為 ：Create table goods( Goods_ID int Not Null, Unit_ID int, Goods_Name char(100), Goods_Code char(20), Goods_Coming_Day datetime, Goods_Function varchar(255), Goods_Number int, Photo varchar(100), Goods_Tag varchar(20), Area_ID int, Primary key (Goods_ID), Foreign key (Unit_ID) references unit, Foreign key (Area_ID) references area) 67/102 建 立 Checkgoods關 聯 表 的 SQL指 令 為 ： 刪 除 關 聯 表 的 語 法 如 下 ： Drop table 關 聯 表 名 稱 例 如 要 刪 除 一 個 名 為 Checkgoods關 聯 表 的 SQL為 ： Drop table checkgoodsCreate table checkgoods( Id uniqueidentifier default newid( ) Not Null, Goods_ID int, Member_ID int, Situ_ID int, Check_Day datetime, memo varchar(255), Primary key (id), Foreign key (Goods_ID) references goods, Foreign key (Member_ID) references member, Foreign key (Situ_ID) references situ,) 68/102 2. 資 料 新 增 資 料 新 增 的 SQL命 令 結 構 如 下 ： 範 例 1： 新 增 一 筆 單 位 編 號 為 1號 、 單 位 名 稱 為 資 管 系 辦 的 單 位 資 料 記 錄 。 INSERT INTO 關聯表 (屬性1, 屬性2, ,屬性N)VALUES (資料值1, 資料值2, ,資料值N)INSERT INTO unit (Unit_ID,Unit_Name) VALUES ( 1,資管系辦) 69/102 資 料 新 增 的 SQL命 令 中 ， 若 是 所 給 定 新 增 記 錄 資料 是 依 照 關 聯 表 建 立 時 的 屬 性 順 序 ， 則 在 新 增 敘述 中 可 以 省 略 屬 性 名 稱 的 部 份 敘 述 。 範 例 2： Unit關 聯 表 中 的 屬 性 依 序 是 Unit_ID，Unit_Name， 因 此 原 本 用 來 新 增 2號 單 位 ， 名 稱 為 RFID實 驗 室 的 新 增 命 令 可 以 改 寫 成 ： 範 例 3： 新 增 一 筆 單 位 編 號 為 3號 、 單 位 名 稱 為 電 腦 教 室 的 單 位 資 料 記 錄 ， 其 SQL命 令 可 以被 敘 述 成 下 列 的 型 式 ： INSERT INTO unit VALUES ( 2,RFID實驗室) INSERT INTO unit (Unit_Name,Unit_ID) VALUES (電腦教室,3) 或 INSERT INTO unit (Unit_ID,Unit_Name) VALUES ( 3,電腦教室) 70/102 範 例 4： 新 增 一 筆 單 位 編 號 為 9號 ， 但 單 位 名 稱 目前 並 不 清 楚 的 單 位 資 料 記 錄 。 範 例 5： 新 增 一 筆 盤 點 狀 況 記 錄 ， 將 狀 況 編 號Situ_ID設 定 為 1號 、 狀 況 名 稱 Situ_Name指 定 為 正 常 ， 且 於 備 註 Situ_Desc屬 性 中 註 明 使 用情 況 正 常 。 INSERT INTO situ (Situ_ID,Situ_Name,Situ_Desc) VALUES ( 1,正常,使用情況正常)INSERT INTO unit (Unit_ID) VALUES ( 9) 71/102 範 例 6： 新 增 一 筆 盤 點 位 置 資 料 記 錄 ， 指 定 位 置 編號 Area_ID為 11號 ， 且 位 置 名 稱 為 展 示 架 第 一層 ， 則 此 新 增 於 盤 點 位 置 關 聯 表 Area的 新 增 指令 如 下 ： INSERT INTO area (Area_ID,Area_Txt) VALUES ( 11,展示架第一層) 72/102 範 例 7： 在 人 員 關 聯 表 Member之 中 新 增 一 筆 資 料記 錄 ， 假 設 員 工 編 號 為 1、 單 位 編 號 為 1、 姓 名 為 吳 啟 源 、 職 稱 為 助 理 、 電 話 為 3601、 地址 為 500 台 中 縣 中 山 路 1號 、 E-mail為 user1ctu.edu.tw 、 帳 號 為 user1 、 密 碼 為 passwd1 ， 則 此 新 增 命 令 可 表 示 如 下 ： INSERT INTO member (Member_ID,Unit_ID,Member_Name, Member_title, Member_phone, Member_Address, Member_mail,Login_ID,Login_PWD) VALUES ( 1,1,吳啟源,助理,3601,500 台中縣中山路1號, user1ctu.edu.tw,user1,passwd1) 73/102 範 例 8： 若 有 一 新 的 財 產 項 目 ， 其 系 統 給 定 的 自 動編 號 為 1、 單 位 編 號 為 1、 財 產 名 稱 為 ATA-133介面 硬 碟 機 、 財 產 編 號 為 09509061-1 、 財 產建 立 日 期 為 2006-08-02 00： 00： 00.000 、 財 產功 能 為 HP2410PDA 、 數 量 為 1、 目 前 沒 有 相 關的 照 片 檔 案 資 訊 、 RFID卡 號 為 A6CCCDD1 、位 置 編 號 為 11。 由 於 此 新 增 資 料 目 前 沒 有 照 片 檔案 資 訊 ， 因 此 財 產 資 料 記 錄 的 新 增 僅 須 針 對 已 知的 九 項 屬 性 進 行 設 定 ， 其 SQL： INSERT INTO goods (Goods_ID,Unit_ID,Goods_Name, Goods_Code,Goods_Coming_Day, Goods_Function, Goods_Number,Goods_Tag,Area_ID)VALUES (1,1,ATA-133介面硬碟機,09509061-1, 2006-08-02 00：00：00.000,HP2410PDA, 1,A6CCCDD1,11) 74/102 範 例 9： 財 產 編 號 1號 的 商 品 進 行 盤 點 ， 盤 點 人 員的 編 號 為 1號 員 工 ， 盤 點 結 果 的 狀 態 編 號 為 1號 狀態 ， 盤 點 日 期 為 2009-10-11 00： 00： 00.000 ，並 在 盤 點 記 錄 中 標 示 財 產 位 置 為 右 方 。 INSERT INTO checkgoods (Goods_ID,Member_ID,Situ_ID, Check_Day, memo) VALUES (1,1,1,2009-10-11 00：00：00.000,右方) 75/102 3. 資 料 擷 取 資 料 擷 取 亦 即 一 般 所 謂 的 資 料 查 詢 ， 其 指 令 敘 述結 構 表 示 如 下 ：select all distinct top | | as from where | group by having order by | 76/102 在 一 般 的 資 料 擷 取 應 用 案 例 中 ， 資 料 擷 取 的 動 作多 半 是 結 合 SELECT、 FROM、 WHERE 敘 述 所 組合 而 成 的 擷 取 命 令 ， 如 下 所 示 。 SELECT 是 指 所 要 投 影 的 屬 性 欄 位 FROM 是 資 料 擷 取 所 作 用 的 關 聯 表 格名 稱 WHERE 則 是 表 示 在 關 聯 表 格 中 選 取值 組 的 條 件SELECT FROM WHERE 77/102 範 例 資 料 內 容 78/102 範 例 資 料 內 容 79/102 範 例 10： 擷 取 財 產 資 料 關 聯 表 中 ， 財 產 數 量 超 過30個 以 上 的 財 產 編 號 、 財 產 名 稱 ， 以 及 數 量 。 SELECT Goods_Code, Goods_Name,Goods_NumberFROM goods WHERE Goods_Number30 80/102 範 例 11： 擷 取 人 員 資 料 關 聯 表 中 ， 職 稱 為 助 理 的 人 員 姓 名 、 電 話 ， 以 及 地 址 。 範 例 12： 擷 取 財 產 資 料 關 聯 表 中 ， 數 量 大 於 36以上 且 屬 於 1號 單 位 所 保 管 的 財 產 編 號 、 財 產 名 稱 ，與 數 量 。 SELECT Member_Name, Member_phone, Member_AddressFROM member WHERE Member_title =助理 SELECT Goods_Code, Goods_Name,Goods_NumberFROM goods WHERE Goods_Number36 and Unit_ID=1 81/102 範 例 13： 在 下 面 的 SQL命 令 中 ， 因 為 沒 有 用 來 篩選 值 組 的 WHERE敘 述 ， 因 此 將 會 擷 取 出 所 有 存 在於 Unit 關 聯 表 中 資 料 值 組 的 單 位 編 號 Unit_ID與 單位 名 稱 Unit_Name擷 取 出 來 。 範 例 14： 列 出 所 有 屬 於 2號 單 位 的 所 有 人 員 資 訊 。 SELECT Unit_ID, Unit_NameFROM unitSELECT * FROM memberWHERE Unit_ID=2 82/102 範 例 15： 找 出 所 有 屬 於 RFID實 驗 室 的 人 員 ，列 出 這 些 人 員 的 姓 名 與 職 稱 。 上 述 的 SQL資 料 擷 取 處 理 命 令 亦 可 改 寫 成 下 面 的型 式 ： SELECT Member_Name, Member_titleFROM member,unitWHERE Unit_Name= RFID實驗室 and member.Unit_ID=unit.Unit_IDSELECT Member_Name, Member_title, Unit_Name FROM member join unit on member.Unit_ID= unit.Unit_IDWHERE Unit_Name= RFID實驗室 83/102 範 例 ： 我 們 想 要 列 出 所 有 電 話 號 碼 ， 若 使 用 左 邊的 敘 述 方 式 ， 我 們 會 發 現 這 些 電 話 號 碼 中 許 多 是重 複 出 現 的 (這 是 因 為 有 些 人 員 是 共 用 同 一 分 機 號碼 的 )， 因 此 我 們 可 以 使 用 右 下 的 第 二 種 敘 述 方 式 ，利 用 distinct的 方 式 來 過 濾 掉 重 複 出 現 的 電 話 號 碼 。 SELECT Member_phoneFROM member SELECT DISTINCT Member_phoneFROM member Distinct 84/102 範 例 16： 找 出 所 有 屬 於 RFID實 驗 室 的 人 員 ，列 出 這 些 人 員 的 姓 名 與 職 稱 。 範 例 16的 例 子 在 先 前 我 們 曾 經 以 JOIN的 形 式 處 理 ，然 而 我 們 也 可 以 利 用 下 面 的 巢 狀 形 式 來 達 成 。SELECT Member_Name, Member_titleFROM memberWHERE Unit_ID = ( SELECT Unit_ID FROM unit WHERE Unit_Name= RFID實驗室 ) 85/102 範 例 17： 找 出 所 有 數 量 超 過 20以 上 的 財 產 項 目 ，列 出 保 管 這 些 財 產 的 有 關 單 位 的 名 稱 。SELECT Unit_NameFROM unitWHERE Unit_ID IN ( SELECT DISTINCT Unit_ID FROM goods WHERE Goods_Number20 ) 86/102 聚 合 函 數 (Aggregates) Count ( ) Sum ( ) Avg ( ) Max ( ) Min ( ) 87/102 範 例 18： 求 取 屬 於 1號 單 位 的 人 員 共 有 多 少 人 。 範 例 19： 求 取 由 RFID實 驗 室 所 保 管 的 財 產 數量 總 數 是 多 少 。 SELECT COUNT(*)FROM memberWHERE Unit_ID=1SELECT SUM(Goods_Number)FROM unit,goods WHERE Unit_Name = RFID實驗室 and unit.Unit_ID= goods.Unit_ID 88/102 範 例 20： 求 取 由 RFID實 驗 室 所 保 管 的 財 產 項目 中 ， 財 產 平 均 數 量 是 多 少 。 範 例 21： 求 取 所 有 的 財 產 項 目 中 ， 數 量 最 多 與 最少 的 各 是 多 少 。 SELECT AVG(Goods_Number)FROM unit,goodsWHERE Unit_Name = RFID實驗室 and unit.Unit_ID= goods.Unit_ID SELECT MAX(Goods_Number), MIN(Goods_Number)FROM goods 89/102 範 例 22： 求 取 由 RFID實 驗 室 所 保 管 的 財 產 項目 中 ， 數 量 最 多 的 值 是 多 少 ？ 並 以 最 大 數 量 來 標 示 最 大 數 量 的 屬 性 欄 位 名 稱 。SELECT MAX(Goods_Number) AS 最大數量FROM unit,goodsWHERE Unit_Name = RFID實驗室 and unit.Unit_ID= goods.Unit_ID 90/102 範 例 23： 將 所 有 人 員 依 照 所 屬 的 單 位 分 群 ， 並 計算 各 單 位 所 屬 人 員 的 數 量 各 有 多 少 人 ， 列 出 這 些單 位 的 編 號 與 所 屬 的 人 員 人 數 。 SELECT Unit_ID, COUNT(*) AS 人數FROM memberGROUP BY Unit_IDGroup By 與 HAVING 91/102 範 例 24： 將 所 有 人 員 依 照 所 屬 的 單 位 分 群 ， 並 計算 各 單 位 所 屬 人 員 的 數 量 各 有 多 少 人 ， 列 出 其 中人 數 大 於 1人 以 上 的 單 位 的 編 號 與 所 屬 的 人 員 人 數 。 SELECT Unit_ID, COUNT(*) AS 人數FROM memberGROUP BY Unit_IDHAVING COUNT(*)1 92/102 Like的 敘 述 是 資 料 庫 資 料 擷 取 中 ， 對 於 文 字 型 態之 屬 性 所 採 用 的 萬 用 字 元 表 示 方 式 。 % ： 是 以 百 分 比 符 號 表 示 零 個 或 多 個 字 元 _ ： 是 以 底 線 符 號 表 示 任 一 個 字 元 範 例 25： 找 出 財 產 資 料 關 聯 表 之 中 ， 財 產 名 稱 包含 字 母 A 的 財 產 項 目 ， 列 出 這 些 財 產 的 編 號 與名 稱 。 SELECT Goods_Number, Goods_Name FROM goodsWHERE Goods_Name LIKE %A% 萬用字元 93/102 範 例 26： 找 出 人 員 資 料 關 聯 表 之 中 ， 地 址 是 位 於 彰 化 市 的 人 員 ， 列 出 這 些 人 員 的 姓 名 與 地 址 。 範 例 27： 找 出 財 產 資 料 關 聯 表 之 中 ， 財 產 名 稱 長度 為 3個 字 ， 且 其 中 第 二 個 字 必 須 是 邏 的 財 產項 目 ， 列 出 這 些 財 產 項 目 的 編 號 與 名 稱 。 SELECT Member_Name, Member_AddressFROM memberWHERE Member_Address LIKE %彰化市% SELECT Goods_Number, Goods_NameFROM goodsWHERE Goods_Na