数据库系统原理及应用

数据库系统原理及应用关 系 规 范 化 理 论 SQL SERVER 2005数 据 库 与 技 术 数 据 库 设 计 的 三 个 主 要 步 骤概 念 设 计 逻 辑 设 计 物 理 设 计现实世界层次网状关系概念模型数据模型物理模型文件、索引等关 系 数 据 库 规 范 化 理 论 是 关 系 数 据 库 逻 辑 设 计 的 理 论 基 础 解 决 的 中 心 问 题 ： 如 何 构 造 一 个 好 的 数 据 模 式 SQL SERVER 2005数 据 库 与 技 术 前 导 问 题 ：1、 为 什 么 进 行 关 系 规 范 化 ？2、 什 么 是 关 系 规 范 化 ？核 心 问 题 ：怎 样 进 行 关 系 规 范 化 ？关 系 规 范 化 SQL SERVER 2005数 据 库 与 技 术 问 题 一 ：1、 为 什 么 进 行 关 系 规 范 化 ？ SQL SERVER 2005数 据 库 与 技 术 例 如 ， 要 求 设 计 教 学 管 理 数 据 库 ， 其 关 系 模 式 SCD如 下 ：n SCD(SNO,SN,AGE,DEPT,MN,CNO,SCORE) 其 中 ， SNO表 示 学 生 学 号 ， SN表 示 学 生 姓 名 ， AGE表示 学 生 年 龄 ， DEPT表 示 学 生 所 在 的 系 别 ， MN表 示 系主 任 姓 名 ， CNO表 示 课 程 号 ， SCORE表 示 成 绩 。 在 此 关 系 模 式 中 填 入 一 部 分 具 体 的 数 据 ， 则 可 得 到SCD关 系 模 式 的 实 例 ， 即 一 个 教 学 管 理 数 据 库 。实 例 ： 教 学 管 理 数 据 库 SQL SERVER 2005数 据 库 与 技 术 SNO SN AGE DEPT MN CNO SCORES1 赵 亦 17 计 算 机 刘 伟 C1 90S1 赵 亦 17 计 算 机 刘 伟 C2 85S2 钱 尔 18 信 息 王 平 C5 57S2 钱 尔 18 信 息 王 平 C6 80S2 钱 尔 18 信 息 王 平 C7 70S2 钱 尔 18 信 息 王 平 C5 70S3 孙 珊 20 信 息 王 平 C1 0S3 孙 珊 20 信 息 王 平 C2 70S3 孙 珊 20 信 息 王 平 C4 85 S4 李 思 男 自 动 化 刘 伟 C1 93 一 个 教 学 管 理 数 据 库 实 例 SQL SERVER 2005数 据 库 与 技 术 1.数 据 冗 余 浪 费 大 量 的 存 储 空 间 每 个 系 名 和 系 主 任 的 名 字 存 储 的 次 数 等 于该 系 的 学 生 人 数 乘 以 每 个 学 生 选 修 的 课 程 门 数 ，同 时 学 生 的 姓 名 、 年 龄 也 都 要 重 复 存 储 多 次 ，数 据 的 冗 余 度 很 大 ， 浪 费 了 存 储 空 间 。存 在 问 题 SQL SERVER 2005数 据 库 与 技 术 2.插 入 异 常 该 有 的 数 据 无 法 插 入 如 果 某 个 新 系 没 有 招 生 ， 尚 无 学 生 时 ， 则系 名 和 系 主 任 的 信 息 无 法 插 入 到 数 据 库 中 。 因为 在 这 个 关 系 模 式 中 ， (SNO,CNO)是 主 码 。根 据 关 系 的 实 体 完 整 性 约 束 ， 主 码 的 值 不 能 为空 ， 而 这 时 没 有 学 生 ， SNO和 CNO均 无 值 ，因 此 不 能 进 行 插 入 操 作 。存 在 问 题 SQL SERVER 2005数 据 库 与 技 术 3. 删 除 异 常 不 应 该 删 除 的 数 据 被 删 除 了 某 系 学 生 全 部 毕 业 而 没 有 招 生 时 ， 删 除 全 部学 生 的 记 录 则 系 名 、 系 主 任 也 随 之 删 除 ， 而 这个 系 依 然 存 在 ， 在 数 据 库 中 却 无 法 找 到 该 系 的信 息 。 存 在 问 题 （ 续 ） SQL SERVER 2005数 据 库 与 技 术 4. 更 新 异 常 更 新 数 据 时 ， 维 护 完 整 性 的 代价 太 大 如 某 系 更 换 系 主 任 ， 则 属 于 该 系 的 学 生 记 录都 要 修 改 MN的 内 容 ， 稍 有 不 慎 ， 就 有 可 能 漏改 某 些 记 录 ， 这 就 会 造 成 数 据 的 不 一 致 性 ， 破坏 了 数 据 的 完 整 性 。存 在 问 题 （ 续 ） SQL SERVER 2005数 据 库 与 技 术 由 于 存 在 以 上 问 题 ， 我 们 说 ， SCD是 一 个 不 好 的关 系 模 式 。 产 生 上 述 问 题 的 原 因 ， 直 观 地 说 ， 是 因为 关 系 中 “ 包 罗 万 象 ” ， 内 容 太 杂 了 。 那 么 ， 怎 样 才 能 得 到 一 个 好 的 关 系 模 式 呢 ？作 业 1： 将 关 系 模 式 SCD规 范 成 好 的 关 系 模 式 。存 在 问 题 （ 续 ） SQL SERVER 2005数 据 库 与 技 术 问 题 二 ：1、 什 么 是 关 系 规 范 化 ？ SQL SERVER 2005数 据 库 与 技 术 规 范 化 问 题 的 提 出 关 系 数 据 库 的 规 范 化 理 论 主 要 包 括 三 个 方 面 的 内 容 ：n 范 式 （ Normal Form）n 函 数 依 赖n 模 式 分 解 其 中 ， 函 数 依 赖 起 着 核 心 的 作 用 ， 是 模 式 分 解 和 模 式 设计 的 基 础 ， 范 式 是 模 式 分 解 的 标 准 。 SQL SERVER 2005数 据 库 与 技 术 范 式 （ Normal form） 范 式 ： 建 立 关 系 时 需 要 满 足 的 约 束 条 件 划 分 成 若干 标 准 ， 这 些 标 准 称 为 范 式 ， 简 写 为 NF。 范 式 的 级别 越 高 ， 发 生 操 作 异 常 的 可 能 性 越 小 ， 数 据 冗 余 越小 ， 但 由 于 关 联 多 ， 读 取 数 据 时 花 费 时 间 也 会 相 应增 加 。 SQL SERVER 2005数 据 库 与 技 术 范 式 规 范 化 的 基 本 思 想 是 消 除 关 系 模 式 中 的 数 据 冗 余 ， 消 除 数据 依 赖 中 的 不 合 适 的 部 分 ， 解 决 数 据 插 入 、 删 除 时 发 生 异常 现 象 。 这 就 要 求 关 系 数 据 库 设 计 出 来 的 关 系 模 式 要 满 足 一 定 的 条件 。 我 们 把 关 系 数 据 库 的 规 范 化 过 程 中 为 不 同 程 度 的 规 范 化 要求 设 立 的 不 同 标 准 称 为 范 式 （ Normal Form） 。 由 于 规 范 化 的 程 度 不 同 ， 就 产 生 了 不 同 的 范 式 。 满 足 最 基 本 规 范 化 要 求 的 关 系 模 式 叫 第 一 范 式 ， 在 第 一 范 式 中 进 一 步 满 足 一 些 要 求 为 第 二 范 式 ， 以 此 类 推 就 产 生 了 第 三 范 式 等 概 念 。 每 种 范 式 都 规 定 了 一 些 限 制 约 束 条 件 。 SQL SERVER 2005数 据 库 与 技 术各 种 范 式 之 间 的 关 系 SQL SERVER 2005数 据 库 与 技 术 函 数 依 赖 函 数 依 赖 （ Functional Dependency）是 关 系 模 式 中 属 性 之 间 的 一 种 逻 辑 依 赖 关 系 。 例 如 在 关 系 模 式 SCD中 ， SNO与 SN、 AGE、DEPT之 间 都 有 一 种 依 赖 关 系 。 SQL SERVER 2005数 据 库 与 技 术 SNO SN AGE DEPT MN CNO SCORES1 赵 亦 17 计 算 机 刘 伟 C1 90S1 赵 亦 17 计 算 机 刘 伟 C2 85S2 钱 尔 18 信 息 王 平 C5 57S2 钱 尔 18 信 息 王 平 C6 80S2 钱 尔 18 信 息 王 平 C7 70S2 钱 尔 18 信 息 王 平 C5 70S3 孙 珊 20 信 息 王 平 C1 0S3 孙 珊 20 信 息 王 平 C2 70S3 孙 珊 20 信 息 王 平 C4 85 S4 李 思 男 自 动 化 刘 伟 C1 93 一 个 教 学 管 理 数 据 库 实 例n由 于 一 个 SNO只 对 应 一 个 学 生 ， 而 一 个 学 生 只 能 属于 一 个 系 ， 所 以 当 SNO的 值 确 定 之 后 ， SN， AGE，DEPT的 值 也 随 之 被 唯 一 的 确 定 了 。 SQL SERVER 2005数 据 库 与 技 术 核 心 问 题 ：1、 怎 样 进 行 关 系 规 范 化 ？ SQL SERVER 2005数 据 库 与 技 术 范 式 化 的 基 本 步 骤 1NF关 系 消 除 非 主 属 性 对 码 的 部 分 函 数 依 赖 2NF关 系 消 除 非 主 属 性 对 码 的 传 递 函 数 依 赖 3NF关 系 消 除 主 属 性 对 非 主 属 性 的 函 数 依 赖 和 对 码 的 部 分 、 传 递 函 数 依 赖 BCNF关 系 消 除 非 平 凡 且 非 函 数 依 赖 的 多 值 依赖 4NF关 系 消 除 非 候 选 码 所 蕴 涵 的 连 接 依 赖 5NF关 系并 非 规 范化 程 度 越 高的 关 系 模 式就 越 好 SQL SERVER 2005数 据 库 与 技 术 学 生 、 课 程 及 学 生 与 课 程 联 系 的 实 体 联 系 图 （ E-R图 ） 学 生 课 程学生-课程学号姓名班级选课密码选课人数课程编号 上课时间课程名称课程类别系部名称系部编号教师学分限选人数学 号姓名课程名称志愿号选课状态成绩 SQL SERVER 2005数 据 库 与 技 术 SQL SERVER 2005数 据 库 与 技 术 范 式 一 个 关 系 的 每 一 个 属 性 都 是 不 可 再 分的 基 本 数 据 项 ， 则 该 关 系 是 范 式 。 SQL SERVER 2005数 据 库 与 技 术 课 程编 号 课 程名 称 课 程类 别 学 分 教 师 系 部编 号 系 部 名 称 上 课时 间 限 选人 数001 SQL 信 息技 术 3 徐 仁 丰 01 计 算 机 周 二 20002 JAVA 信 息技 术 2 程 伟 斌 01 计 算 机 周 二 40011 建 筑 人 文 2 林 泉 02 建 筑 周 二 20012 智 能建 筑 工 程技 术 2 王 娜 02 建 筑 周 二 20 表1 课程表 SQL SERVER 2005数 据 库 与 技 术 学 号 姓 名 班 级 选 课 密 码00000001 林 斌 00电 子 商 务 12344600000002 彭 少 帆 00电 子 商 务 5656534000000011 朱 川 00多 媒 体 5646131表2 学生表表3 学生选课表学 号 姓 名 课 程 名 称 选 课 状 态 志 愿 号 成 绩00000001 林 斌 SQL 报 名 1 8500000002 彭 少 帆 ASP 报 名 3 90 SQL SERVER 2005数 据 库 与 技 术 表 1、 表 2和 表 3满 足 范 式 的 条 件 ， 所 以 它 们 是 范 式 。 范 式 是 关 系 模 型 必 须 达 到 的 最 低 要 求 ， 不 满 足 该 条件 的 关 系 模 型 称 为 非 规 范 化 关 系 ， 即 非 I范 式 。 SQL SERVER 2005数 据 库 与 技 术 范 式 范 式 首 先 是 范 式 ， 关 系 中 的 每 一个 非 主 属 性 完 全 函 数 依 赖 于 主 关 键 字 ， 则该 关 系 是 范 式 。 SQL SERVER 2005数 据 库 与 技 术 课 程编 号 课 程名 称 课 程类 别 学 分 教 师 系 部编 号 系 部名 称 上 课时 间 限 选人 数001 SQL 信 息技 术 3 徐 仁丰 01 计 算机 周 二 20002 JAVA 信 息技 术 2 程 伟斌 01 计 算机 周 二 40011 建 筑 人 文 2 林 泉 02 建 筑 周 二 20012 智 能建 筑 工 程技 术 2 王 娜 02 建 筑 周 二 20 表1 课程表注：表中黄色背景显示的是主关键字 SQL SERVER 2005数 据 库 与 技 术学 号 姓 名 班 级 选 课 密 码00000001 林 斌 00电 子 商 务 12344600000002 彭 少 帆 00电 子 商 务 5656534000000011 朱 川 00多 媒 体 5646131 表2 学生表 SQL SERVER 2005数 据 库 与 技 术 表3 学生选课表学 号 姓 名 课 程 名 称 选 课 状态 志 愿 号 成 绩00000001 林 斌 SQL 报 名 1 8500000002 彭 少 帆 ASP 报 名 3 90 SQL SERVER 2005数 据 库 与 技 术 将非范式规范为范式的方法是： 将部分函数依赖关系中的主属性(决定方)和非主属性从关系中提取出来，单独构成一个关系模式，将关系模式中余下的属性，加上主关键字，构成另一个关系 。 SQL SERVER 2005数 据 库 与 技 术表4 学生选课表学 号 课 程 名 称 选 课 状 态 志 愿 号 成 绩00000001 SQL 报 名 1 8500000002 ASP 报 名 3 90 学 号 姓 名 课 程 名 称 选 课 状态 志 愿 号 成 绩00000001 林 斌 SQL 报 名 1 8500000002 彭 少 帆 ASP 报 名 3 90 表3 学生选课表 SQL SERVER 2005数 据 库 与 技 术 2NF的 缺 点2NF的 关 系 模 式 解 决 了 1NF中 存 在 的 一 些 问 题 ， 2NF规范 化 的 程 度 比 1NF前 进 了 一 步 ， 但 2NF的 关 系 模 式 在 进行 数 据 操 作 时 ， 仍 然 存 在 着 一 些 问 题 ：n1 数 据 冗 余 。n2 插 入 异 常 。n3 删 除 异 常 。n4 更 新 异 常 。 SQL SERVER 2005数 据 库 与 技 术 表 1 课 程 表表 2 学 生 表表 4 选 课 表 SQL SERVER 2005数 据 库 与 技 术 范 式 范 式 首 先 是 范 式 ， 且 关 系 中 的 任 何 一 个 非 主 属 性都 不 函 数 传 递 依 赖 于 任 何 主 关 键 字 ， 则 关 系 是 范 式 。 首 先 需 要 找 出 关 系 中 的 所 有 主 关 键 字 ， 然 后 确 定 任何 一 个 非 主 属 性 和 任 何 主 关 键 字 之 间 是 否 存 在 函 数 传递 依 赖 关 系 ， 如 果 有 则 删 除 函 数 传 递 依 赖 关 系 。 SQL SERVER 2005数 据 库 与 技 术 课 程编 号 课 程名 称 课 程类 别 学 分 教 师 系 部编 号 系 部名 称 上 课时 间 限 选人 数001 SQL 信 息技 术 3 徐 仁 丰 01 计 算机 周 二 20002 JAVA 信 息技 术 2 程 伟 斌 01 计 算机 周 二 40011 建 筑 人 文 2 林 泉 02 建 筑 周 二 20012 智 能建 筑 工 程技 术 2 王 娜 02 建 筑 周 二 20 表1 课程表 SQL SERVER 2005数 据 库 与 技 术 课 程编 号 课 程名 称 课 程类 别 学 分 教 师 系 部编 号 上 课时 间 限 选 人数001 SQL 信 息技 术 3 徐 仁 丰 01 周 二 20002 JAVA 信 息技 术 2 程 伟 斌 01 周 二 40011 建 筑 人 文 2 林 泉 02 周 二 20系 部 编 号 系 部 名 称01 计 算 机02 建 筑03 旅 游 表5 课程表 表6 系部表 SQL SERVER 2005数 据 库 与 技 术 表 1 课 程 表表 2 学 生 表表 4 选 课 表 SQL SERVER 2005数 据 库 与 技 术属 于 3NF的 关 系 模 式 一 定 是 好 模 式 ？ SQL SERVER 2005数 据 库 与 技 术 学 号 姓 名 班 级 选 课 密 码00000001 林 斌 00电 子 商 务 12344600000002 彭 少 帆 00电 子 商 务 565653400000011 朱 川 00多 媒 体 5646131表2 学生表表7 班级表班 级 编 号 班 级 名 称 系 部 编 号200000001 00电 子 商 务 01200000002 00多 媒 体 01学 号 姓 名 班 级 编 号 选 课 密 码00000001 林 斌 20000001 12344600000002 彭 少 帆 20000001 565653400000011 朱 川 20000002 5646131表8 学生表 SQL SERVER 2005数 据 库 与 技 术 表4 学生选课表学 号 课 程 名 称 选 课 状 态 志 愿 号 成 绩00000001 SQL 报 名 100000002 ASP 报 名 3学 号 课 程 编 号 选 课 状 态 志 愿 号 成 绩00000001 001 报 名 100000002 008 报 名 3表9 学生选课表 SQL SERVER 2005数 据 库 与 技 术 表1 课程表表 5 课 程 表表 6 系 部 表 SQL SERVER 2005数 据 库 与 技 术 表 2 学 生 表表 7班 级 表表 8 选 课 表表 学 生 表 SQL SERVER 2005数 据 库 与 技 术 表 3 选 课 表表 4 选 课 表表 9选 课 表 SQL SERVER 2005数 据 库 与 技 术 关 系 模 式 的 规 范 化 小 结 规 范 化 一 个 低 一 级 范 式 的 关 系 模 式 ， 通 过 模 式 分 解 转换 为 若 干 个 高 一 级 范 式 的 关 系 模 式 集 合 的 过 程 。 规 范 化 目 的 消 除 关 系 上 的 数 据 冗 余 、 操 作 异 常 的 现 象 。 规 范 化 基 本 思 想 逐 步 消 除 数 据 依 赖 中 不 合 适 的 部 分 ， 使 模 式 中 的各 关 系 模 式 达 到 某 种 程 度 的 分 解 。 规 范 化 实 质 概 念 的 单 一 化 SQL SERVER 2005数 据 库 与 技 术 数据完整性 数 据 的 完 整 性 就 是 数 据 的 正 确 性 和 一 致 性 ， 它 反 映 了 现 实世 界 中 实 体 的 本 来 面 貌 。 数 据 的 完 整 性 分 为 列 的 完 整 性 、 表 的 完 整 性 和 参 照 完 整 性 。 SQL SERVER 2005数 据 库 与 技 术 1.列 完 整 性 列 的 完 整 性 也 称 域 完 整 性 或 用 户 定 义 完 整 性 。 列 完 整 性 是 指 表 中 任 意 列 的 数 据 类 型 必 须 符 合 用 户 的 定 义 ，或 者 数 据 必 须 在 规 定 的 有 效 范 围 之 内 。 表6 系部表系 部 编 号 系 部 名 称01 计 算 机 02 建 筑03 旅 游 SQL SERVER 2005数 据 库 与 技 术 2 .表 完 整 性 表 完 整 性 也 可 称 实 体 完 整 性 。 所 谓 实 体 完 整 性 ， 是 指 表 中 必 须 有 一 个 主 关 键 字 ， 且 主键 值 不 能 为 空 。 表6 系部表系 部 编 号 系 部 名 称01 计 算 机 02 建 筑03 旅 游 SQL SERVER 2005数 据 库 与 技 术 3 .参 照 完 整 性 参 照 完 整 性 也 称 为 引 用 完 整 性 ， 它 指 对 外 键 值进 行 插 入 或 修 改 时 一 定 要 参 照 主 键 的 值 是 否 存 在 。对 主 键 进 行 修 改 或 删 除 时 ， 也 必 须 要 参 照 外 键 的值 是 否 存 在 。 SQL SERVER 2005数 据 库 与 技 术 4 .保 证 参 照 完 整 性 规 则 （ 1） 对 外 键 所 在 的 表 执 行 插 入 操 作 时 ， 要 保 证 外 键 的 值 一 定要 在 主 表 中 存 在 。 班 级 编 号 班 级 名 称 系 部 编 号200000001 00电 子 商 务 01200000002 00多 媒 体 01表7 班级表学 号 姓 名 班 级 编 号 选 课 密 码 00000001 林 斌 20000001 12344600000002 彭 少 帆 20000001 565653400000011 朱 川 20000002 5646131表8 学生表 SQL SERVER 2005数 据 库 与 技 术 （ 2） 修 改 从 表 的 外 关 键 字 时 ， 要 保 证 修 改 后 的 外 键 值 在 主表 中 已 存 在 ， 就 是 说 要 参 照 主 表 的 主 关 键 字 的 值 。 班 级 编 号 班 级 名 称 系 部 编 号200000001 00电 子 商 务 01200000002 00多 媒 体 01表7 班级表表8 学生表学 号 姓 名 班 级 编 号 选 课 密 码00000001 林 斌 20000001 123446 00000002 彭 少 帆 20000001 565653400000011 朱 川 20000002 5646131 SQL SERVER 2005数 据 库 与 技 术 （ 3） 修 改 主 表 的 主 关 键 字 时 ， 一 地 你 更 要 注 意 从 表 中 是 否 存 在 该 主 关 键 字 ，如 果 有 ， 则 禁 止 所 做 的 修 改 或 级 联 修 改 所 有 从 表 中 与 之 相 应 的 外 关 键 字 的 值 。 系 部 编 号 系 部 名 称01 计 算 机02 建 筑03 旅 游 表6 系部表课 程编 号 课 程 名 称 课 程 类 别 学 分 教 师 系 部 编号 上 课时 间 限 选 人 数001 SQL 信 息 技 术 3 徐 仁 丰 01 周 二 20 002 JAVA 信 息 技 术 2 程 伟 斌 01 周 二 40011 建 筑 人 文 2 林 泉 02 周 二 20 表5 课程表 SQL SERVER 2005数 据 库 与 技 术 （ 4） 删 除 主 表 的 数 据 行 时 ， 一 定 要 注 意 要 删 除 的 那 些 行 的 主 关 键 字 是 否 在 从表 中 存 在 ， 如 果 存 在 ， 则 禁 止 删 除 或 级 联 删 除 所 有 从 表 中 与 之 相 应 的 外 关 键字 所 在 的 那 些 行 。 系 部 编 号 系 部 名 称01 计 算 机02 建 筑03 旅 游 表6 系部表课 程 编号 课 程 名 称 课 程 类 别 学 分 教 师 系 部 编号 上 课 时间 限 选 人 数001 SQL 信 息 技 术 3 徐 仁 丰 01 周 二 20 002 JAVA 信 息 技 术 2 程 伟 斌 01 周 二 40011 建 筑 人 文 2 林 泉 02 周 二 20 表5 课程表