《数据库原理与SQL Server》教案.doc

第第 1 课课 SQL Server 2000 简介简介 教学目的教学目的 掌握 SQL Server 2000 的定制安装 掌握 SQL Server 2000 主要组件的基本操作 掌握 SQL Server 2000 数据库服务器的注册 知识点知识点 了解 SQL Server 2000 版本 理解 SQL Server 2000 软硬件平台 技能点技能点 会操作 SQL Server 2000 主要组件 熟练安装 SQL Server 2000 学时要求学时要求 2 学时 重点难点重点难点 SQL Server 2000 安装与配置 一 SQL Server 2000 概述概述 1 发展史 1990 年 Microsoft Sybase Ashton Tate SQL Server 1 1 1992 年 Microsoft Sybase SQL Server 4 2 1995 年 Microsoft SQL Server 6 1998 年 Microsoft SQL Server 7 2000 年 Microsoft SQL Server 2000 三个重要版本 1996 年 SQL Server 6 5 1998 年 SQL Server 7 0 和 2000 年 SQL Server 2000 2 平台 硬件 P166 64MB 180MB 软件 企业版 1000 用户 TB 级 Windows NT 2000 Server 标准版 100 用户 GB 级 Windows NT 2000 Server 个人版 10 用户 MB 级 Windows 98 2000 Professional 3 安装 注 如在不支持的 OS 上安装将弹出警告信息 提示用户只能安装客户端组件 数据库实例 Instance 即数据库服务器名 默认为服务器名 默认安装位置 C Promram Files Microsoft SQL Server MSSQL 指定 MSSQL 为 MSSQL 一台机器上允许安装多个实例 如果使用本地系统帐户不需要设置密码 但没有网络访问权限 如果选择混合模式允许使用 Windows 身份验证或 SQL Server 2000 身份验证 使用 Windows 98 时只能选择混合模式 且只有采用混合模式才能使用 sa 用户 登录 二 SQL Server 2000 组件组件 1 服务管理器 启动 停止 暂停 SQL Server 服务 注 启动的作用 允许另外的数据库服务器访问 启动 Windows 自动启动的设置 连接数据库服务器时自动启动 SQL EM 数据库服务器 单击右键 编辑 SQL Server 注册信息 2 企业管理器 类似资源管理器形式 SQL Server 主要工具 新建服务器组 SQL EM SQL Servers 组 单击右键 新建 SQL Server 注册服务器 SQL EM 服务器组 单击右键 新建 SQL Server 注册 同网段注册 数据库服务器名 不同网段注册 IP 地址或 3 查询分析器 执行 Transact SQL 语句 4 导入导出数据 与其它数据库管理系统或其它数据格式之间交换数据 详见第 7 章 注 可实现不同的 SQL Server 服务器之间传递数据 5 服务器网络实用工具 用于设置服务器支持的网络库及其参数 使客户机能连接到本服务器 6 客户端网络实用工具 允许用户创建到指定服务器的网络协议连接和更改默认的网络协议 7 事件探查器 使系统管理员监视 SQL Server 2000 中的事件 8 在 IIS 中配置 SQL XML 支持 可以利用 HTTP 协议以 URL 方式访问 SQL Server 2000 数据库 9 联机丛书 查找所需要的帮助信息 作业作业 1 SQL Server 2000 包括哪些版本 其中能够在 Windows 2000 Advanced Server 上安 装的可以有哪几个版本 2 在安装 SQL Server 2000 时如果使用默认实例名 系统的默认安装位置是什么 如 果使用指定实例名 系统的默认安装位置又是什么 3 安装 SQL Server 2000 时 可供选择的身份验证模式有哪两种 有什么区别 4 简述 sa 的相关概念 5 简述 SQL Server 2000 的四个主要组件及其用途 实训实训 实训一 SQL Server 2000 安装与启动 实训名称 SQL Server 2000 安装与启动 目的要求 掌握 SQL Server 2000 安装方法与启动 SQL Server 2000 的步骤 操作步骤 1 安装 SQL Server 2000 标准版 其中设置选项 在本地安装新的 SQL Server 2000 服务器和客户端工具 使用默认实例 名 选择典型安装 并将安装磁盘指定为 D 文件夹名称不变 使用域用户账户 使用混 合验证模式 2 启动 SQL Server 服务管理器 并设置启动 OS 时自动启动该项服务 3 启动 SQL Server 企业管理器 并熟悉操作方法 4 设置创建与 SQL Server 服务器连接时 是否自动启动 SQL Server 服务 5 在客户端 SQL Server 企业管理器中注册服务器端的数据库服务器 6 启动 SQL Server 查询分析器 并熟悉操作方法 第第 2 课课 数据库的管理数据库的管理 教学目的教学目的 掌握数据库的相关知识 掌握 SQL Server 2000 数据库的构成 掌握 SQL Server 2000 数据库的创建 熟悉 SQL Server 2000 数据库的订阅和发布 知识点知识点 了解数据库的相关知识 了解 Transact SQL 语言 理解 SQL Server 2000 数据库的构成 技能点技能点 熟练 SQL Server 2000 数据库的创建 会发布和订阅 SQL Server 2000 数据库 学时要求学时要求 2 学时 重点难点重点难点 SQL Server 2000 数据库的创建 一 一 数据库构成数据库构成 1 数据库基础知识 相关概念 数据与信息 数据是用于载荷信息的物理符号 数据管理和数据处理 收集 加工 发布 管理 处理 eg 天气预报 数据库技术的出现和发展 人工管理阶段 eg 十个数排序 背景 汇编语言 特点 数据与程序不具有独立性 数据不能共享 冗余度高 文件系统阶段 eg 学生档案管理 学号 姓名 性别 出生日期 学生成绩 学号 姓名 性 别 成绩 管理 背景 高级语言 特点 数据与程序具有一定的独立性 数据可以共享 但仍有较高的冗余度 因很难共享 倒排文件系统阶段 文件系统的改进 采用索引技术 便于数据检索 数据库系统阶段 eg 学生档案管理 学生学籍管理 背景 数据库技术 特点 数据集中统一管理 与程序独立 通过 DBMS 操纵 数据可以共享 具有可控冗余度 数据库系统构成 DB 数据库 数据的集合 DBMS 数据库管理系统 数据库管理软件 DBA 数据库管理员 负责数据库系统的正常运行 DBS 数据库技术 采用了数据库技术的计算机系统 2 SQL Server 数据库类型 数据库 由若干表及表间特定 关系 构成 系统数据库 master 系统所有系统级信息 最重要 model 模板数据库 包含用户数据库所有基本信息 msdb 服务信息 tempdb 中间信息 样本数据库 pubs 一个虚拟出版公司数据库 northwind 一个虚拟贸易公司数据库 用户数据库 创建用户数据库时系统自动复制 model 中的内容 3 数据库中的对象 Table 表 存放数据 View 视图 虚拟表 通过 select 语句对表中数据定制访问 Index 索引 表的索引 提高查询速度 Stored Procedure 存储过程 一组 SQL 语句 减轻网络流量 Trigger 触发器 以表动作为前提的一组 SQL 语句 eg 视图 s c sc 表的三表查询 及索引 存储过程 触发器的概念 4 文件与文件组 数据库 数据文件 主数据文件 1 个 mdf 次数据文件 0 n 个 ndf 事务日志文件 1 n ldf 文件组 文件的集合 实现数据文件分组管理 默认一个主数据文件组即 PRIMARY 注 表可以指定放在文件上 即表中数据分布在不同文件中 加快速度 多磁头读 写 通常数据与事务日志分开 优化放置 一个文件只能属于一个文件组 一个文件 组 只能用于一个数据库 二 二 Transact SQL 1 SQL 概述 结构化查询语言 RDBMS 的国际标准语言 有 方言 在 SQL Server 中即为 Transact SQL 简称 T SQL 2 Transact SQL 概述 数据控制 DCL 安全管理 GRANY DENY ROVOKE 数据定义 DDL 创建数据库及对象 CREATE ALTER DROP 数据操纵 DML 操作数据库中的对象 SELECT INSERT DELETE UPDATE 附加语言元素 变量 运算符 函数 流程控制等 3 执行方式 SQL 语句 程序实现 但格式复杂 只给出最一般格式 SQL EM 操作简便 但只能交互 三 三 建立数据库建立数据库 1 创建数据库 SQL 语句 create database 例 2 1 在 D 盘 example 文件夹下创建一个 student 数据库 主文件名 student data mdf 事务日志文件名 student log ldf create database student on name student data filename d example student data mdf log on name student log filename d example student log ldf 例 2 2 在 D 盘 example 文件夹下创建一个 customer 数据库 包含一个数据文件和 一个事务日志文件 主数据文件的逻辑文件名为 customer 实际文件名为 customer mdf 初始容量为 10MB 最大容量为 50MB 自动增长时的递增量为 2MB 事务日志文件的逻辑文件名为 customer log 实际文件名为 customer log 1df 初始容量为 5MB 最大容量为 30MB 自动增长时的递增量为 1MB CREATE DATABASE customer ON PRIMARY NAME customer FILENAME d example customer mdf SIZE 10MB MAXSIZE 50MB FILEGROWTH 2MB LOG ON NAME customer log FILENAME d example customer log ldf SIZE 5MB MAXSIZE 30MB FILEGROWTH 1MB 例 2 3 在 D 盘 example 文件夹下创建一个 archive 数据库 包含三个数据文件和 两个事务日志文件 主数据文件的逻辑文件名为 archl 实际文件名为 archdatl mdf 两个次数据文件的逻辑文件名分别为 arch2 和 arch3 实际文件名分 别为 archdat2 ndf 和 archdat3 ndf 两个事务日志文件的逻辑文件名分别为 archlogl 和 archlog2 实际文件名分别为 archklogl 1df 和 archklog2 1df 上述文件的初始容 量均为 5MB 最大容量均为 50MB 递增量均为 1MB CREATE DATABASE archive ON PRIMARY NAME archl FILENAME d example archdatl mdf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB NAME arch2 FILENAME d example archdat2 ndf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB NAME arch3 FILENAME d example archdat3 ndf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB LOG ON NAME archlogl FILENAME d example archklog1 ldf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB NAME archlog2 FILENAME d example archklog2 ldf SIZE 5MB MAXSIZE 50MB FILEGROWTH 1MB SQL EM SQL EM 数据库 右键 新建数据库 数据库名 注 文件的存储位置 2 修改数据库 SQL 语句 alter database 例 2 4 将例 2 2 中的数据库 customer 的主数据文件 customer 的大小调整为 20MB ALTER DATABASE customer MODIFY FILE NAME customer SIZE 20MB 例 2 5 将例 2 2 中的数据库 customer 增加一个次数据文件 customer 1 ndf ALTER DATABASE customer ADD FILE NAME customer 1 FILENAME d example customer 1 ndf SIZE 10MB MAXSIZE 20MB FILEGROWTH 20 例 2 6 首先创建一个名为 test 的数据库 其主数据文件的逻辑文件名和实际文件 名分别为 testdatl 和 tdatl mdf 然后向该数据库中添加一个次数据文件 其逻辑文 件名和实际文件名分别为 testdat2 和 tdat2 ndf 两个数据库文件的初始容量均为 5MB 最大容量均为 10MB 递增量均为 20 CREATE DATABASE test ON NAME testdat1 FILENAME d example tdatl mdf SIZE 5MB MAXSIZE 10MB FILEGROWTH 1MB ALTER DATABASE test ADD FILE NAME testdat2 FILENAME d example tdat2 ndf SIZE 5MB MAXSIZE 10MB FILEGROWTH 20 SQL EM SQL EM 指定数据库 右键 属性 3 删除数据库 SQL 语句 drop database 例 2 7 删除数据库 test drop database test 注 不能删除系统数据库 正在使用的库无法删除 除非使用备份 否则删除无法恢复 SQL EM SQL EM 指定数据库 右键 删除 4 重命名数据库 sp renamedb 例 2 8 将数据库 archive 名修改为 arch Sp rename archive arch 注 系统数据库和正在使用的库无法重命名 四 四 发布和订阅数据库发布和订阅数据库 发布服务器 分发服务器 订阅服务器 1 配置发布和分发 SQL EM 指定数据库服务器 右侧复制图标 右键 配置发布 订阅服务器和 分发 分发服务器 快照文件夹 2 创建发布 SQL EM 指定数据库服务器复制文件夹 右侧发布内容图标 右键 新建发布 发布数据库 类型 项目 3 订阅发布 SQL EM 指定数据库服务器复制文件夹 右侧订阅图标 右键 新建请求订阅 发布数据库 目的数据库 作业作业 1 数据管理技术经历了哪三个阶段 各阶段的主要特点是什么 2 简述 DB DBA DBS DBMS 的概念及其关系 3 SQL Server 2000 数据库包括哪几种类型 4 SQL Server 2000 系统数据库包括哪几个 分别有什么作用 5 组成 SQL Server 2000 数据库的文件类型包括哪几个 扩展名是什么 一个数据库 中 各文件分别可以有多少个 6 简述文件组的概念 7 简述发布和订阅数据库的应用场合及简要操作步骤 实训实训 实训二 创建数据库 实验名称 创建数据库 目的要求 掌握 SQL Server 2000 中创建数据库的方法 操作步骤 1 在 D 盘根目录下创建文件夹 student 2 使用 SQL EM 创建数据库 student 包括一个主数据文件 一个事务日志文件 3 指定 student 的主数据文件为 student mdf 事务日志文件名为 student ldf 并 存放在 D 盘 student 文件夹中 4 数据库其它属性设置取默认值 5 发布和订阅所建数据库 student 第第 3 课课 表的管理表的管理 教学目的教学目的 熟悉关系模型及相关概念 掌握表结构建立 掌握表数据维护 知识点知识点 理解关系模型 技能点技能点 会用 SQL 语句建立表结构 熟练用 SQL EM 建立表结构 熟练维护表数据 学时要求学时要求 2 学时 重点难点重点难点 表建立和维护 一 一 关系模型关系模型 1 数据描述 数据管理步骤 概念设计 逻辑设计 物理设计 概念设计 实体 客观存在的事物 包括抽象的 实体集 同类实体的集合 属性 实体的特性 实体标识符 能惟一标识实体的属性 集 eg 学生 实体集 包括属性 学号 姓名 性别 实体标识符为 学号 逻辑设计 字段 实体属性的命名单位 属性 记录 字段的有序集合 实体 文件 同类记录的集合 实体集 关键字 能惟一标识记录的字段 集 实体标识符 eg 学生 文件 包括字段 学号 姓名 性别 主健为 学号 物理设计 存储介质 高速缓存 内存 快擦写存储器 磁盘 光盘 磁带 数据描述 Bit Byte Word Block Bucket Volume eg 学生 文件 d example student mdf eg 对 学生 的描述 2 数据模型 定义 表示实体类型及实体间联系的模型称为数据模型 概念设计 概念数据模型 实体联系模型 逻辑设计 逻辑数据模型 层次模型 网状模型 关系模型 实体联系模型 ER 模型 数据联系 联系 实体间的关系 N 元联系 与联系相关的实体个数 二元联系的类型 一对一 一对多 多对多 eg 其它联系 与二元联系类似 ER 图 实体 矩形框 属性 椭圆形框 联系 菱形框 联系的类型 1 m n 实体标识符 下划线 例 3 2 为 学生选课系统 设计一个 ER 模型 结构数据模型 层次模型 树形 M N 分为 M 个 1 N 联系描述复杂 网状模型 有向图 M N 分为 2 个 1 N 联系描述复杂 关系模型 二维表 例 3 3 将例 3 2 的 ER 模型转换为关系模型 学生关系模式s sno class sname sex birthday address telephone em ail 课程关系模式c cno cname credit 选课关系模式sc sno cno score 二 二 建立表结构建立表结构 1 概述 数据库中最主要的数据对象 数据只能存储在表中 eg 例 3 4 2 数据类型 共 28 种 主要包括 数值数据类型 整型 bigint 8 字节 int 4 字节 smallint 2 字节 tinyint 1 字节 实型 精确 小数 decimal p s numeric p s 两种数据等价 近似 浮点数 folat n 8 字节 real 4 字节 字符数据类型 char n 定长字符串类型 n 范围为 1 8000 varchar n 变长字符串类型 即自动删除字符串尾部空格后存储 n 范围为 1 8000 text 文本类型 实际也是变长字符串类型 存储长度超过 char 8000 的字符串 最大长度 231 1 实际上 text 列仅存储一指针 数据本身存储在连接表中 字符串类型常量两端应加单引号 逻辑数据类型 bit 1 字节 值为 0 或 1 当输入 0 和 1 以外的值时自动转换为 1 二进制数据类型 binary n 定长 n 4 个字节 varbinary n 变长 image 将图形以位字符串形式存储 长度 2GB 注 二进制类型常量应用十六进制表示 并以 0 x 作前缀 日期时间数据类型 datetime 1753 1 1 9999 12 31 smalldatetime 1900 1 1 2079 12 31 注 日期时间类型常量两端应加单引号 如果只指定日期 则时间默认午夜 12 点 0 分 0 秒 如果只指定时间 则日期默认 1900 年 1 月 1 日 如果省略世纪时 则 50 视为 19 货币数值类型 money 8 字节 4 位小数 smallmoney 4 字节 注 货币类型常量应以美元符号 或所定义的金融单位符号作前缀 用户自定义数据类型 3 创建表 SQL 语句 create table 完整性约束 主键完整性约束 primary 惟一完整性约束 unique 外键完整性约束 foreign 非空完整性约束 not null 缺省完整性约束 default 检查完整性约束 check 例 3 5 在 student 数据库中 为例 3 4 的三个关系模式 s c sc 创建表 s c sc CREATE TABLE s sno char 4 PRIMARY key class char 20 sname char 8 sex char 2 birthday datetime address varchar 50 telephone char 20 email char 40 CREATE TABLE c cno char 4 PRIMARY key cname char 20 credit tinyint CREATE TABLE sc sno char 4 cno char 4 score smallint PRIMARY key sno cno 例 3 6 在有关零件 供应商 工程项目的数据库中 有四个关系 其结构如下 供应商关系 S SNO SNAME STATUS ADDR 零件关系 P PNO PNAME COLOR WEIGHT 工程项目关系 J JNO JNAME CITY BALANCE 供应情况关系 CREATE TABLE S SNO CHAR 4 NOT NULL SNAME CHAR 20 NOT NULL STATUS CHAR 10 ADDR CHAR 20 PRIMARY KEY SNO GO CREATE TABLE P PNO CHAR 4 NOT NULL PNAME CHAR 20 NOT NULL COLOR CHAR 8 WEIGHT SMALLINT PRIMARY KEY PNO GO CREATE TABLE J JNO CHAR 4 NOT NULL JNAME CHAR 20 CITY CHAR 20 BALANCE NUMERIC 7 2 PRIMARY KEY JNO GO CREATE TABLE SPJ SNO CHAR 4 NOT NULL PNO CHAR 4 NOT NULL JNO CHAR 4 NOT NULL PRICE NUMERIC 7 2 QTY SMALLINT PRIMARY KEY SNO PNO JNO FOREIGN KEY SNO REFERENCES S SNO FOREIGN KEY PNO REFERENCES P PNO FOREIGN KEY JNO REFERENCES J JNO CHECK QTY BETWEEN 0 AND 10000 GO SQL EM SQL EM 指定数据库 表 右键 新建表 字段 完整性约束 表名 例 3 7 使用 SQL EM 创建例 3 4 中的表 sc 注 主键完整性约束 单击 设置主键 惟一完整性约束 选择 管理关系 索引 键 新建 外键完整性约束 新建关系图或单击 管理关系 图标 非空完整性约束 单击 允许空 缺省完整性约束 输入默认值 检查完整性约束 选择 管理关系 CHECK 新建 4 修改表 SQL 语句 alter table 例 3 8 在表 s 中增加新的列 postcode alter table s add postcode char 6 null 例 3 9 删除表 s 中的列 postcode alter table s drop colum postcode 例 3 10 设置表 sc 中的列 sno 为外键 alter table sc add constraint fk sc s foreign key sno references s sno 例 3 11 为表 sc 的列 score 增加约束 alter table sc with nocheck add constraint score check check score 0 and score 100 SQL EM SQL EM 指定数据库 指定表 右键 设计表 例 3 12 对表 s 定义 sname 非空完整性约束 sex 缺省完整性约束 缺省值 男 email 惟一完整性约束 例 3 13 对表 sc 定义 sno 为外键 参照表 s 的 sno 定义 cno 为外键 参照表 c 的 cno 5 删除表 SQL 语句 drop table 例 3 14 删除 student 数据库中的 sc 表 drop table sc SQL EM SQL EM 指定数据库 指定表 右键 删除 注 删除表包括表定义 约束 数据 索引 触发器等 不能删除系统表 外键约束的参考表 三 三 编辑数据编辑数据 1 SQL EM SQL EM 指定表 右键 打开表 返回所有行 2 SQL 语句 insert 格式一 insert into values eg1 insert into s sno sname values 0001 张三 格式二 insert into select from eg2 将 jy41 01 s 的记录插入 jy41 02 s 表结构相同 insert into jy41 02 s select from jy41 01 s 例 3 15 在表 s 中插入一学生 学号为 1001 班级为 信息 501 姓名为 黄 鹏 性别为 男 出生日期为 1981 年 10 月 12 日 住址 江苏省常州市 电话电子信箱为 hp insert into s values 1001 信息 501 黄鹏 男 1981 10 12 江苏省常州市hp 例 3 16 在表 s 中插入一学生 学号为 2001 班级为 计应 501 姓名为 张 宇蛟 性别为 男 出生日期为 1984 年 11 月 6 日 电子信箱 zyj insert into s sno class sname sex birthday email values 2001 计应 501 张宇蛟 男 1984 11 6 zyj 例 3 17 将表 s 的男生记录插入表 s bak 中 假设表 s bak 已存在 且结构与表 s 相同 insert into s bak 注 事务的概念 当插入违背了完整性约束时 则事务回滚 如若 jy41 01 s 已存 在 jy41 02 s 中同 sno 的记录 则一条记录都不会插入 不会仅插入 sno 不同的记 录 delete delete from where 例 3 18 删除表 s bak 中所有男生 delete from s bak where sex 男 update update set from where 例 3 19 将表 s 学号为 2001 的学生的住址改为 北京市 电话为update s set address 北京市 telephonewhere sno 2001 eg1 将所有选修了课程号为 c1 的学生的成绩加 10 分 update sc set score score 10 where cno c1 eg2 将所有选修了 c 课程的学生的成绩加 10 分 update sc set score score 10 from c where cname c and o o 作业作业 1 简述数据模型的概念 2 什么是概念数据模型 什么是逻辑数据模型 列出常用的概念数据模型和逻辑数 据模型 3 简述 ER 模型 层次模型 网状模型 关系模型的主要特点 4 简述关键键 超键 候选键 主键 外键的区别 5 设某商业集团有三个实体集 一是 商品 实体集 属性有商品号 商品名 规 格 单价等 二是 商店 实体集 属性有商店号 商店名 地址等 三是 供应商 实 体集 属性有供应商编号 供应商名 地址等 同时 供应商与商品之间存在 供应 联 系 每个供应商可供应多种商品 每种商品可向多个供应商订购 每个供应商供应每种商 品有个月供应量 商店与商品间存在 销售 联系 每个商店可销售多种商品 每种商品 可在多个商店销售 每个商店销售每种商品有个月计划数 试画出反映上述问题的 ER 图 并将其转换成关系模型 6 什么是数据完整性规则 在 SQL Server 2000 中 是如何实现数据完整性规则的 7 试用 SQL 语句描述对数据库 student 中三个基本表 s c sc 的操作 1 向表 s 中插入一个学生 属性自定 2 将表 s 中每一门课程成绩都大于等于 80 分的学生学号 姓名和性别插入另一 个已存在的表 student sno sname sex 中 3 删除表 sc 中尚无成绩的选课元组 4 把学号为 0001 的学生的选课和成绩数据全部删除 5 把选修了数据库应用课程的不及格的学生成绩全改为 0 6 把低于总平均成绩的女同学成绩提高 5 7 修改表 sc 中课程编号为 c001 的成绩 若成绩小于等于 75 分时提高 5 若 成绩大于 75 分时提高 4 用两个 UPDATE 语句实现 实训实训 实训三 创建表 实验名称 创建表结构并录入记录 目的要求 掌握 SQL Server 2000 中创建表及录入记录的方法 操作步骤 1 使用 SQL EM 在数据库 student 中创建学生表 表名要求为 s 包含列 sno char 4 class char 20 sname char 8 sex char 2 birthday datatime address varchar 50 telephone char 20 email char 40 其中 sno 为主键 要求 class sname 非空 并指定 sex 默认值为 男 2 使用 SQL EM 在数据库 student 中创建课程表 表名要求为 c 包含列 cno char 4 cname char 20 credit tinyint 其中 cno 为主键 指定 cname 为惟一性字段 3 使用 SQL EM 在数据库 student 中创建选课表 表名要求为 sc 包含列 sno char 4 cno char 4 score smallint 其中 sno cno 为主键 指定 sno 为外键参照表 s 的 sno 指定 cno 为外键参照表 c 的 cno 4 使用 SQL 语句在学生 课程和选课表中录入本班五名以上学生的真实数据 第第 4 7 课课 数据库规范化设计数据库规范化设计 教学目的教学目的 熟悉数据库规范化设计 知识点知识点 了解函数依赖 了解关系模式的三个范式 学时要求学时要求 关系规范化 函数依赖 2 学时 属性集闭包 2 学时 2NF 2NF 的判定 2 学时 综合实例 2 学时 重点难点重点难点 关系模式范式的判定 一 一 关系规范化关系规范化 1 关系模式的操作异常 数据冗余 同一数据多次出现 问题 操作异常 是一个不好的模式 例 3 21 设有 学生选课 关系模式 R sno sname sex cno cname score 其属性分 别表示学生学号 姓名 性别 选修课程的课程编号 课程名 成绩 关系模式 R 实例 snosnamesexcnocnamescore 1001黄鹏男c001数据库应用77 1004刘玉春女c001数据库应用62 1006王玲女c001数据库应用50 1006王玲女c002VB 程序设计64 1004刘玉春女c002VB 程序设计74 1007李国男c003计算机网络0 数据冗余 修改异常 数据的不一致性 插入异常 数据无法插入 删除异常 数据丢失 2 关系模式的规范化 泛关系模式 当 R U 中 U 包含了现实问题的所有属性 称 R U 为泛关系 模式 问题 泛关系模式通常右数据冗余 需分解为多个模式 其集合称为数据库模式 eg 对 学生选课 建立关系模式 R sno sname sex cno cname score 显然为泛关系模式 分解为 R1 sno sname sex R2 cno cname R3 sno cno score 3 符号规定 A B C 表示单个属性 U V W X Y Z 表示属性集 R S 表示关系 R A B C R ABC ABC A B C 二 二 函数依赖 函数依赖 FD 数据依赖 数据间的联系 最基本的是函数依赖 1 FD 的定义 定义 R U X Y 为属性集 t1 t2 为元组 若 t1 X t2 X 则 t1 Y t2 Y 称 X 函数决定 Y 函数或 Y 依赖与 X 记作 X Y 例 3 22 对实例 3 21 学生选课 关系模式 R 如果规定每个学生只能有一个姓名 每个课程号只能对应一门课程 则有以下 FD sno sname cno cname 由于每个学生每选修一门课程只能有一个成绩 则有以下 FD sno cno score 当然 还有以下 FD sno sex sno sname sex 例 3 23 设关系模式 R ABCD 假设 A 与 B 为一对多联系 而 C 与 D 为一对一联 系 试写出相应的 FD 由于 A 与 B 为一对多联系 即每个 A 值有多个 B 值对应 即 B 值决定 A 值 可 写出 FD B A 同理 由于 C 与 D 为一对一联系 可写出 FD D C 和 C D 注意一对一 一对多关系 如学号和性别为一对多 的 FD 2 FD 和关键码的关系 定义 R U 若 X U 则称 X 是 R 的超键 如果 X 任一子集 X1 有 X1 U 不 成立 则称 X 是 R 的候选键 例 3 24 由例 3 22 关系模式 R 的分析 有以下 FD sno cno sno sname sex cno cname score 即 sno cno 为关系模式 R U 的候选键 虽然有以下 FD sno sname cno sno sname sex cno cname score 但 sno sname cno 只是关系模式 R U 的超键而非候选键 因为 sno sname cno 含多 余属性 sname 结论 若 X 是 R 的候选键 则任意 Y 均有 X Y eg 学生选课 若 X Y 而 X 非超键 则 R 一定有冗余 eg 例 3 21 有 cno cname 函数依赖表示数据的完整性约束 3 FD 推理规则 定义 若 X Y 且 YX 则称 X Y 是 平凡的 FD 反之称 非平凡的 FD 结论 平凡的 FD 是不可能不满足的 FD 如 X X 要研究的是非平凡的 FD 规则 自含律 X X 自反律 若 YX 则 X Y 增广律 若 X Y 则 XZ YZ 传递律 若 X Y 且 WY Z 则 XW Z 分解规则 若 X YZ 则 X Y 且 X Z 合并规则 若 X Y 且 X Z 则 X YZ 复合规则 若 X Y 且 Z W 则 XZ YW 作业作业 1 名词解释 泛关系模式 泛关系 数据库模式 数据库实例 关系模式 关系模型 关系实例 函数依赖 平凡函数依赖 非平凡函数依赖 局部函数依赖 完全函数依赖 函数依赖集 函数依赖集闭包 属性集闭包 1NF 2NF 3NF 2 关系模式 R ABCD 假设 B 与 D 为一对多联系 而 A 与 C 为一对一联系 试写 出相应的 FD 并由此写出 R 的候选键 三 三 属性集的闭包属性集的闭包 1 函数依赖集与函数依赖集的闭包 F FD 的集合 称函数依赖集 F 闭包 由 F 中所有 FD 可以推导出的所有 FD 的集合 记为 F 例 3 25 对于关系模式 R ABC F A B B C 求 F 根据 FD 定义 可推出 F A A A A B A C A AB A BC A ABC 共有 43 个 FD 其中 表示空属性集 2 属性集闭包定义 对 F F 中所有 X A 的 A 的集合称 X 的闭包 记为 X 如上例 A ABC 结论 X 表示所有 X 可以决定的属性 如上例 A ABC 表示 A ABC 以 S sno sname sex 无同名 讲解 若 X 包含 R 的所有属性 则 X 是超键 当 X 不可约时则为候选键 如上例 A ABC 则 A 为超键 因为 A 不可约则为候选键 AB ABC 则 AB 为超键 因为 AB 可约则不为候选键 以 S sno sname sex 讲解 F 是指数级计算 而 X Y 属于 F 的必充条件是 Y 是 X 的子集 即不求 F 但可以判断 FD 是否属于 F 如上例 R ABC F A B B C F A A A A B A C A AB A BC A ABC 例 3 26 对例 3 25 关系模式 R 求其候选键并判断 A C 和 B A 是否属于 F 根据属性集闭包的定义可知 A ABC B BC C C 由于 A 包含 R 所有属性 则 R 候选键为 A 由于 CA 而 AB 则 A CF 而 B AF 3 属性集闭包算法 A 将 A 置入 A 对每一 FD 若左部属于 A 则将右部置入 A 重复至 A 不能扩大 例 3 27 对于关系模式 R ABCD F A B B C D B 求其候选键 1 求 A A A 由 A B 而 AA 则 A AB 由 B C 而 BA 则 A ABC A 封闭 即 A ABC 2 求 B C D 按步骤 1 可得 B BC C C D BCD 3 求其候选键 显然 R 候选键为 AD 例 3 28 对于关系模式 R ABC F A BC BC A 求其候选键 1 求属性闭包 按例 3 27 可得 A ABC B B C C 2 求属性集闭包 由 BC A 则 BC ABC 其余属性集闭包为属性闭包的并 3 求候选键 显然 R 候选键为 A 和 BC 4 FD 集的最小依赖集 定义 对 R U 上的 F1 F2 若 F1 F2 则称 F1与 F2等价 eg R ABC F1 A B AB C D AC D E 与 F2 A BC D AE 等价 对 F1 A ABC B B C C D ABCDE E E AB ABC 对 F2 A ABC B B C C D ABCDE E E AB ABC 故 F1与 F2等价 定义 Fmin 是 F 的最小依赖集的必充条件为 Fmin F 重点 每个 FD 的右部是单属性 Fmin中没有冗余的 FD 即删除任何一个 FD 即不等价 每个 FD 左部无冗余属性 即删除任一属性即不等价 称左部不可 约 结论 要实现一个 F 只要实现 Fmin 即可 每个 F 均存在一个 Fmin 但不惟一 作业作业 1 设关系模式 R ABCD F A B B C 试写出 1 属性集 BD 的闭包 BD 2 所有左部为 B 的 FD 即形为 B 的 FD 2 设关系模式 R ABC F A B B C 试写出 F 中的 43 个 FD 3 设关系模式 R ABCD F A B C B 试写出 R 的候选键 4 设关系模式 R ABCD 假设 B 与 D 为一对多联系 而 A 与 C 为一对一联系 试 写出相应的 FD 并由此写出 R 的候选键 5 设关系模式 R 有 n 个属性 在 R 上可能成立的 FD 有多少个 其中平凡的 FD 有 多少个 非平凡的 FD 有多少个 四 四 关系模式的范式关系模式的范式 1 概念 范式 满足特定要求的模式 第 n 范式 表示关系模式的某一级别 有 5NF4NFBCNF3NF2NF1NF 规范化 通过模式分解 将低一级范式转换成若干高一级范式 2 第一范式 1NF 定义 若 R 中每一属性的值域为原子的 称 R 属于 1NF 模式 记为 R 1NF eg R sno sname sex phone 当 phone 包含两个电话时 R 非 1NF 注 1NF 与关系模式的性质 属性是原子的 一致 即是关系必为 1NF 3 第二范式 2NF 定义 若 R 1NF 且每个非主属性完全依赖于候选键 则称 R 属于 2NF 模式 其中 完全函数依赖 左部不可约 主属性 属于候选键的属性 部分函数依赖 反之 非主属性 反之 例 3 29 对于关系模式 R sno cno score tno taddress 的属性分别表示学生编号 选 修课程的课程号 成绩 任课教师编号 教师地址 约定 一门课程只能由一个 教师带 试判断 R 是否属于 2NF 模式 由 一门课程只能由一个教师带 则候选键为 sno cno 对非主属性 tno taddress 有 cno tno taddress 则 sno cno tno taddress 为 局部依赖 非主属性 tno taddress 局部依赖于候选键 sno cno 因此 R2NF 结论 2NF 要求每个非主属性不能由候选键的一部分决定 否则必有冗余 当候选键为单属性时 必为 2NF 分解算法 R WXYZ 若存在 FD X Y 则将 R 分解为 R1 WXZ 且 X 为外键 R2 XY 例 3 30 对于例 3 29 关系模式 R R2NF 按算法 3 3 可以将 R 分解为 和 R2 cno tno taddress 消除了局部依赖 sno cno tno taddress 且 R1 R22NF 4 第三范式 3NF 定义 若 R 1NF 且每个非主属性都不传递依赖于候选键 则称 R 属于 3NF 其中传递依赖 X Y Y A 且 Y X AY 则称 X A 是传递依赖 等价定义 若 R 1NF F 中每个非平凡的 FD X Y 均满足二条件之一 a X 是超键 b Y 中每个属性均为主属性 则称 R 属于 3NF 模式 例 3 31 对于例 3 30 关系模式 R1 R2 试判断 R2 是否属于 3NF 模式 对 R2 有 FD tno taddress 而 taddress 为非主属性且 tno 非超键 即 R23NF 作业作业 1 设关系模式 R ABCD F AB CD A D 1 试说明 R 不是 2NF 的理由 2 试将 R 分解为 2NF 模式集 结论 3N