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

《《数据库原理与SQL Server》教案》由会员分享，可在线阅读，更多相关《《数据库原理与SQL Server》教案（90页珍藏版）》请在装配图网上搜索。

1、第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.

2、11992年 Microsoft、Sybase SQL Server 4.21995年 Microsoft SQL Server 61998年 Microsoft SQL Server 72000年 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用

3、户 MB级 Windows 98/2000 Professional3 安装注：如在不支持的OS上安装将弹出警告信息，提示用户只能安装客户端组件。数据库实例(Instance)即数据库服务器名，默认为服务器名。默认安装位置：C:Promram FilesMicrosoft SQL ServerMSSQL 指定：MSSQL为MSSQL$一台机器上允许安装多个实例。如果使用本地系统帐户不需要设置密码，但没有网络访问权限。如果选择混合模式允许使用Windows身份验证或SQL Server 2000身份验证。使用Windows 98时只能选择混合模式，且只有采用混合模式才能使用“sa”用户登录。二、

4、 SQL Server 2000组件1 服务管理器启动/停止/暂停SQL Server服务。注：启动的作用：允许另外的数据库服务器访问。启动Windows自动启动的设置。连接数据库服务器时自动启动：SQL-EM数据库服务器单击右键编辑SQL Server注册信息。2 企业管理器类似资源管理器形式，SQL Server主要工具。新建服务器组SQL-EMSQL Servers组单击右键新建SQL Server。注册服务器SQL-EM服务器组单击右键新建SQL Server注册。 同网段注册：数据库服务器名。 不同网段注册：IP地址或/。3 查询分析器执行Transact-SQL语句。4 导入导出数

5、据与其它数据库管理系统或其它数据格式之间交换数据。详见第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 Advance

6、d 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 200

7、0标准版。其中设置选项：在本地安装新的SQL Server 2000服务器和客户端工具；使用默认实例名；选择典型安装，并将安装磁盘指定为D，文件夹名称不变；使用域用户账户；使用混合验证模式。(2) 启动SQL Server服务管理器，并设置启动OS时自动启动该项服务。(3) 启动SQL Server企业管理器，并熟悉操作方法。(4) 设置创建与SQL Server服务器连接时，是否自动启动SQL Server服务。(5) 在客户端SQL Server企业管理器中注册服务器端的数据库服务器。(6) 启动SQL Server查询分析器，并熟悉操作方法。第2课 数据库的管理教学目的 掌握数据库的相关

8、知识 掌握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. 天

9、气预报 数据库技术的出现和发展人工管理阶段eg. 十个数排序背景：汇编语言特点：数据与程序不具有独立性。 数据不能共享，冗余度高。文件系统阶段eg. 学生档案管理（学号、姓名、性别、出生日期）、学生成绩（学号、姓名、性别、成绩）管理。背景：高级语言特点：数据与程序具有一定的独立性。 数据可以共享，但仍有较高的冗余度（因很难共享）。倒排文件系统阶段文件系统的改进，采用索引技术，便于数据检索。数据库系统阶段eg. 学生档案管理、学生学籍管理背景：数据库技术特点：数据集中统一管理，与程序独立，通过DBMS操纵。 数据可以共享，具有可控冗余度。 数据库系统构成DB（数据库）：数据的集合。DBMS（数据

10、库管理系统）：数据库管理软件。DBA（数据库管理员）：负责数据库系统的正常运行。DBS（数据库技术）：采用了数据库技术的计算机系统。2 SQL Server数据库类型 数据库由若干表及表间特定“关系”构成。 系统数据库master：系统所有系统级信息，最重要。model：模板数据库，包含用户数据库所有基本信息。msdb：服务信息。tempdb：中间信息。 样本数据库pubs：一个虚拟出版公司数据库。northwind：一个虚拟贸易公司数据库。 用户数据库创建用户数据库时系统自动复制model中的内容。3 数据库中的对象 Table(表)存放数据 View(视图)虚拟表，通过select语句对表

11、中数据定制访问 Index(索引)表的索引，提高查询速度 Stored Procedure(存储过程)一组SQL语句，减轻网络流量 Trigger(触发器)以表动作为前提的一组SQL语句eg. 视图(s、c、sc表的三表查询)及索引、存储过程、触发器的概念。4 文件与文件组 数据库数据文件 主数据文件：1个，mdf 次数据文件：0n个，ndf事务日志文件：1n，ldf 文件组文件的集合，实现数据文件分组管理。默认一个主数据文件组即PRIMARY。 注表可以指定放在文件上，即表中数据分布在不同文件中，加快速度，多磁头读写。通常数据与事务日志分开，优化放置。一个文件只能属于一个文件组，一个文件（组

12、）只能用于一个数据库。二、 Transact-SQL1 SQL概述结构化查询语言，RDBMS的国际标准语言。有“方言”，在SQL Server中即为TransactSQL，简称TSQL。2 Transact-SQL概述数据控制(DCL)：安全管理。GRANY、DENY、ROVOKE数据定义(DDL)：创建数据库及对象。CREATE、ALTER、DROP数据操纵(DML)：操作数据库中的对象。SELECT、INSERT、DELETE、UPDATE附加语言元素：变量、运算符、函数、流程控制等。3 执行方式SQL语句：程序实现，但格式复杂，只给出最一般格式。SQL-EM：操作简便，但只能交互。三、

13、建立数据库1 创建数据库 SQL语句create database 例2-1 在D盘example文件夹下创建一个student数据库，主文件名student_data.mdf，事务日志文件名student_log.ldf。create database studenton(name=student_data,filename=d:examplestudent_data.mdf )log on(name=student_log,filename=d:examplestudent_log.ldf )例2-2 在D盘example文件夹下创建一个customer数据库，包含一个数据文件和一个事务日

14、志文件。主数据文件的逻辑文件名为customer，实际文件名为customer.mdf，初始容量为10MB，最大容量为50MB，自动增长时的递增量为2MB。事务日志文件的逻辑文件名为customer_log，实际文件名为customer_log.1df，初始容量为5MB，最大容量为30MB，自动增长时的递增量为1MB。CREATE DATABASE customerON PRIMARY (NAME=customer, FILENAME=d:examplecustomer.mdf , SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=2MB)LOG ON (NAME=cus

15、tomer_log, FILENAME=d:examplecustomer_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和archkl

16、og2.1df。上述文件的初始容量均为5MB，最大容量均为50MB，递增量均为1MB。CREATE DATABASE archive ON PRIMARY (NAME=archl, FILENAME=d:examplearchdatl.mdf , SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB), (NAME=arch2, FILENAME=d:examplearchdat2.ndf , SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB), (NAME=arch3, FILENAME=d:examplearchdat3.ndf , SI

17、ZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB) LOG ON (NAME=archlogl, FILENAME=d:examplearchklog1.ldf , SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB), (NAME=archlog2, FILENAME=d:examplearchklog2.ldf , SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB) SQL-EMSQL-EM数据库右键新建数据库：数据库名。注：文件的存储位置。2 修改数据库 SQL语句alter database 例2-4 将例2

18、-2中的数据库customer的主数据文件customer的大小调整为20MB。ALTER DATABASE customerMODIFY FILE (NAME=customer, SIZE=20MB)例2-5 将例2-2中的数据库customer增加一个次数据文件customer_1.ndf。ALTER DATABASE customerADD FILE (NAME=customer_1, FILENAME=d:examplecustomer_1.ndf , SIZE=10MB, MAXSIZE=20MB, FILEGROWTH=20%)例2-6 首先创建一个名为test的数据库，其主数据文

19、件的逻辑文件名和实际文件名分别为testdatl和tdatl.mdf。然后向该数据库中添加一个次数据文件，其逻辑文件名和实际文件名分别为testdat2和tdat2.ndf。两个数据库文件的初始容量均为5MB，最大容量均为10MB，递增量均为20%。CREATE DATABASE testON (NAME= testdat1, FILENAME=d:exampletdatl.mdf , SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB)ALTER DATABASE testADD FILE (NAME=testdat2, FILENAME=d:exampletdat

20、2.ndf , SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=20%) SQL-EMSQL-EM指定数据库右键属性3 删除数据库 SQL语句drop database 例2-7 删除数据库test。drop database test注：不能删除系统数据库。正在使用的库无法删除。除非使用备份，否则删除无法恢复。 SQL-EMSQL-EM指定数据库右键删除4 重命名数据库sp_renamedb , 例2-8 将数据库archive名修改为arch。Sp_rename archive, arch注：系统数据库和正在使用的库无法重命名。四、 发布和订阅数据库发布服务器分发服务

21、器订阅服务器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 Ser

22、ver 2000数据库的文件类型包括哪几个？扩展名是什么？一个数据库中，各文件分别可以有多少个？6 简述文件组的概念。7 简述发布和订阅数据库的应用场合及简要操作步骤。实训实训二 创建数据库实验名称：创建数据库目的要求：掌握SQL Server 2000中创建数据库的方法操作步骤：(1) 在D盘根目录下创建文件夹“student”。(2) 使用SQL-EM创建数据库student，包括一个主数据文件、一个事务日志文件。(3) 指定student的主数据文件为“student.mdf”，事务日志文件名为“student.ldf”，并存放在D盘student文件夹中。(4) 数据库其它属性设置取默

23、认值。(5) 发布和订阅所建数据库student。第3课 表的管理教学目的 熟悉关系模型及相关概念 掌握表结构建立 掌握表数据维护知识点 理解关系模型技能点 会用SQL语句建立表结构 熟练用SQL-EM建立表结构 熟练维护表数据学时要求2学时重点难点 表建立和维护一、 关系模型1 数据描述数据管理步骤：概念设计逻辑设计物理设计 概念设计实体：客观存在的事物，包括抽象的。实体集：同类实体的集合。属性：实体的特性。实体标识符：能惟一标识实体的属性（集）。eg. “学生”实体集，包括属性“学号”、“姓名”、“性别”，实体标识符为“学号”。 逻辑设计字段：实体属性的命名单位。 属性记录：字段的有序集合

24、。 实体文件：同类记录的集合。 实体集关键字：能惟一标识记录的字段（集）。实体标识符eg. “学生”文件，包括字段“学号”、“姓名”、“性别”，主健为“学号”。 物理设计存储介质：高速缓存、内存、快擦写存储器、磁盘、光盘、磁带。数据描述：Bit、Byte、Word、Block、Bucket、Volume。eg. “学生”文件，d:examplestudent.mdf。eg. 对“学生”的描述2 数据模型 定义表示实体类型及实体间联系的模型称为数据模型。概念设计概念数据模型：实体联系模型。逻辑设计逻辑数据模型：层次模型、网状模型、关系模型。 实体联系模型（ER模型）数据联系联系：实体间的关系。N

25、元联系：与联系相关的实体个数。二元联系的类型：一对一、一对多、多对多。eg. 其它联系：与二元联系类似。ER图实体：矩形框。属性：椭圆形框。联系：菱形框。联系的类型：1、m、n。实体标识符：下划线。例3-2 为“学生选课系统”设计一个ER模型。 结构数据模型层次模型：树形。MN分为M个1N。联系描述复杂。网状模型：有向图。MN分为2个1N。联系描述复杂。关系模型：二维表。例3-3 将例3-2的ER模型转换为关系模型。学生关系模式s（sno，class，sname，sex，birthday，address，telephone，email）课程关系模式c（cno，cname，credit）选课关系

26、模式sc（sno，cno，score）二、 建立表结构1 概述数据库中最主要的数据对象，数据只能存储在表中。eg. 例3-42 数据类型共28种，主要包括：数值数据类型 整型：bigint(8字节)、int(4字节)、smallint(2字节)、tinyint(1字节)。 实型 精确(小数)：decimal(p,s)、numeric(p,s)，两种数据等价。 近似(浮点数)：folat(n) (8字节)、real(4字节)。字符数据类型char(n)：定长字符串类型，n范围为18000。varchar(n)：变长字符串类型，即自动删除字符串尾部空格后存储。n范围为18000。text：文本类型

27、，实际也是变长字符串类型，存储长度超过char(8000)的字符串，最大长度231-1。实际上，text列仅存储一指针，数据本身存储在连接表中。字符串类型常量两端应加单引号。逻辑数据类型bit：1字节，值为0或1。当输入0和1以外的值时自动转换为1。二进制数据类型 binary(n)：定长，n+4个字节。 varbinary(n)：变长。image：将图形以位字符串形式存储，长度2GB。 注：二进制类型常量应用十六进制表示，并以0x作前缀。日期时间数据类型 datetime：1753.1.19999.12.31。 smalldatetime：1900.1.12079.12.31。 注：日期时间

28、类型常量两端应加单引号。如果只指定日期，则时间默认午夜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

29、-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),

30、 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)GOCREATE TABLE P(

31、PNO CHAR(4) NOT NULL, PNAME CHAR(20) NOT NULL, COLOR CHAR(8), WEIGHT SMALLINT, PRIMARY KEY (PNO)GOCREATE TABLE J(JNO CHAR(4) NOT NULL, JNAME CHAR(20), CITY CHAR(20), BALANCE NUMERIC(7,2), PRIMARY KEY (JNO)GOCREATE TABLE SPJ(SNO CHAR(4) NOT NULL, PNO CHAR(4) NOT NULL, JNO CHAR(4) NOT NULL, PRICE NUME

32、RIC(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-EMSQL-EM指定数据库表右键新建表：字段、完整性约束、表名。例3-7使用SQL-EM创建例3-4中的表sc。注：主键完整性约束：单击“设置主键”。惟一完整性约束：选择“管理关系”“索引/键”“新建”。外键完

33、整性约束：新建关系图或单击“管理关系”图标。非空完整性约束：单击“允许空”。缺省完整性约束：输入默认值。检查完整性约束：选择“管理关系”“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) ref

34、erences s(sno)例3-11 为表sc的列score增加约束。alter table sc with nocheckadd constraint score_checkcheck (score=0 and score=100) SQL-EMSQL-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

35、 table sc SQL-EMSQL-EM指定数据库指定表右键删除注：删除表包括表定义、约束、数据、索引、触发器等。不能删除系统表、外键约束的参考表。三、 编辑数据1 SQL-EMSQL-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 j

36、y41_01_s例3-15 在表s中插入一学生，学号为“1001”，班级为“信息501”，姓名为“黄鹏”，性别为“男”，出生日期为“1981年10月12日”，住址“江苏省常州市”，电话“13905190335”，电子信箱为“hp”。insert into svalues(1001, 信息501, 黄鹏, 男, 1981-10-12, 江苏省常州市, 13905190335, hp)例3-16在表s中插入一学生，学号为“2001”，班级为“计应501”，姓名为“张宇蛟”，性别为“男”，出生日期为“1984年11月6日”，电子信箱“zyj”。insert into s(sno,class,snam

37、e,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不同的记录。 deletedelete from where 例3-18 删除表s_bak中所有男生。delete from s_bak where sex=男 updateupdate se

38、t = from where 例3-19 将表s学号为“2001”的学生的住址改为“北京市”，电话为“13900102329”。update s set address=北京市,telephone=13900102329 where sno=2001eg1.将所有选修了课程号为c1的学生的成绩加10分。update sc set score=score+10 where cno=c1eg2.将所有选修了c+课程的学生的成绩加10分。update sc set score=score+10 from c where cname=c+ and o=o作业1 简述数据模型的概念。2 什么是概念数据模

39、型？什么是逻辑数据模型？列出常用的概念数据模型和逻辑数据模型。3 简述ER模型、层次模型、网状模型、关系模型的主要特点。4 简述关键键、超键、候选键、主键、外键的区别。5 设某商业集团有三个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。同时，供应商与商品之间存在“供应”联系，每个供应商可供应多种商品，每种商品可向多个供应商订购，每个供应商供应每种商品有个月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，每个商店销售每种商品有个月计

40、划数。试画出反映上述问题的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

41、) 修改表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)。其中，s

42、no为主键，要求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语句在学生、课程和选课表中

43、录入本班五名以上学生的真实数据。第4-7课 数据库规范化设计教学目的 熟悉数据库规范化设计知识点 了解函数依赖 了解关系模式的三个范式学时要求 关系规范化、函数依赖：2学时 属性集闭包：2学时 2NF、2NF的判定：2学时 综合实例：2学时重点难点 关系模式范式的判定一、 关系规范化1 关系模式的操作异常数据冗余：同一数据多次出现。问题：操作异常，是一个不好的模式。例3-21 设有“学生选课”关系模式R(sno,sname,sex,cno,cname,score)，其属性分别表示学生学号、姓名、性别、选修课程的课程编号、课程名、成绩。关系模式R实例snosnamesexcnocnamescor

44、e1001黄鹏男c001数据库应用771004刘玉春女c001数据库应用621006王玲女c001数据库应用501006王玲女c002VB程序设计641004刘玉春女c002VB程序设计741007李国男c003计算机网络0数据冗余：修改异常数据的不一致性 插入异常数据无法插入 删除异常数据丢失2 关系模式的规范化泛关系模式：当R(U)中U包含了现实问题的所有属性，称R(U)为泛关系（模式）。问题：泛关系模式通常右数据冗余，需分解为多个模式，其集合称为数据库模式。eg. 对“学生选课”建立关系模式：R(sno,sname,sex,cno,cname,score)显然为泛关系模式。分解为：R1(

45、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为元组，若t1Xt2X，则t1Yt2Y，称X函数决定Y函数或Y依赖与X，记作XY。例3-22对实例3-21“学生选课”关系模式R，如果规定每个学生只能有一个姓名，每个课程号只能对应一门课程，则有以下FD：snosnamecnocname由于每个学生每

46、选修一门课程只能有一个成绩，则有以下FD：(sno,cno)score当然，还有以下FD：snosexsno(sname,sex)例3-23设关系模式R(ABCD)，假设A与B为一对多联系，而C与D为一对一联系，试写出相应的FD。由于A与B为一对多联系，即每个A值有多个B值对应，即B值决定A值，可写出FD：BA。同理，由于C与D为一对一联系，可写出FD：DC和CD。注意一对一、一对多关系（如学号和性别为一对多）的FD。2 FD和关键码的关系定义：R(U)，若XU，则称X是R的超键。如果X任一子集X1，有X1U不成立，则称X是R的候选键。例3-24 由例3-22关系模式R的分析，有以下FD：(s

47、no,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均有XY。eg. “学生选课” 若XY，而X非超键，则R一定有冗余。eg. 例3-21，有cnocname。 函数依赖表示数据的完整性约束。3 FD推理规则定义：若XY，且YX，则称XY是“平凡的FD”，反之称

48、“非平凡的FD”。结论：平凡的FD是不可能不满足的FD（如XX），要研究的是非平凡的FD。规则：自含律 XX。 自反律 若YX，则XY。 增广律 若XY，则XZYZ。 传递律 若XY且WYZ，则XWZ。 分解规则 若XYZ，则XY且XZ。 合并规则 若XY且XZ，则XYZ。 复合规则 若XY且ZW，则XZYW。作业1 名词解释泛关系模式，泛关系，数据库模式，数据库实例，关系模式，关系模型，关系实例，函数依赖，平凡函数依赖，非平凡函数依赖，局部函数依赖，完全函数依赖，函数依赖集，函数依赖集闭包，属性集闭包，1NF，2NF，3NF。2 关系模式R(ABCD)，假设B与D为一对多联系，而A与C为一对

49、一联系，试写出相应的FD，并由此写出R的候选键。三、 属性集的闭包1 函数依赖集与函数依赖集的闭包F：FD的集合，称函数依赖集。F闭包：由F中所有FD可以推导出的所有FD的集合，记为F+。例3-25 对于关系模式R(ABC)，FAB,BC，求F+。根据FD定义，可推出F+=,A,AA,AB,AC,AAB,ABC,AABC,，共有43个FD。其中，表示空属性集。2 属性集闭包定义对F，F+中所有XA的A的集合称X的闭包，记为X+。如上例：A+ABC结论：X+表示所有X可以决定的属性。 如上例：A+ABC表示AABC，以S(sno,sname,sex)(无同名)讲解。若X+包含R的所有属性，则X是

50、超键。当X不可约时则为候选键。 如上例：A+ABC则A为超键，因为A不可约则为候选键(AB)+ABC则AB为超键，因为AB可约则不为候选键以S(sno,sname,sex)讲解。F+是指数级计算，而XY属于F+的必充条件是Y是X+的子集。 即不求F+，但可以判断FD是否属于F+。 如上例：R(ABC)，F=AB,BCF+=,A,AA,AB,AC,AAB,ABC,AABC,例3-26 对例3-25关系模式R，求其候选键并判断AC和BA是否属于F。根据属性集闭包的定义可知：A+=ABC，B+=BC，C+=C由于A+包含R所有属性，则R候选键为：A。由于CA+而AB+，则ACF而BAF。3 属性集闭

51、包算法A+：将A置入A+。对每一FD，若左部属于A+则将右部置入A+。重复至A+不能扩大。例3-27对于关系模式R(ABCD)，FAB,BC,DB，求其候选键。(1) 求A+。 A+A。 由AB，而AA+，则A+AB。 由BC，而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=ABC,BCA，求其候选键。(1) 求属性闭包。按例3-27可得：A+ABC，B+B，C+C。(2) 求属性集闭包。由BCA，则(BC)+ABC，其余属性集闭包

52、为属性闭包的并。(3) 求候选键。显然，R候选键为：A和BC。4 FD集的最小依赖集 定义：对R(U)上的F1、F2，若F1+=F2+，则称F1与F2等价。eg. R(ABC)，F1=AB,ABC,DAC,DE与F2=ABC,DAE等价？ 对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左部无冗余属性（即删除

53、任一属性即不等价），称左部不可约。 结论：要实现一个F，只要实现Fmin即可。 每个F均存在一个Fmin，但不惟一。作业1 设关系模式R(ABCD)，FAB,BC，试写出(1) 属性集BD的闭包(BD)+。(2) 所有左部为B的FD，即形为“B?”的FD。2 设关系模式R(ABC)，FAB,BC，试写出F+中的43个FD。3 设关系模式R(ABCD)，FAB,CB，试写出R的候选键。4 设关系模式R(ABCD)，假设B与D为一对多联系，而A与C为一对一联系，试写出相应的FD，并由此写出R的候选键。5 设关系模式R有n个属性，在R上可能成立的FD有多少个？其中平凡的FD有多少个？非平凡的FD有多

54、少个？四、 关系模式的范式1 概念范式：满足特定要求的模式。第n范式：表示关系模式的某一级别，有：5NF4NFBCNF3NF2NF1NF。规范化：通过模式分解，将低一级范式转换成若干高一级范式。2 第一范式（1NF）定义：若R中每一属性的值域为原子的，称R属于1NF模式，记为R1NF。eg. R(sno,sname,sex,phone) 当phone包含两个电话时R非1NF。注：1NF与关系模式的性质（属性是原子的）一致，即是关系必为1NF。3 第二范式（2NF） 定义若R1NF，且每个非主属性完全依赖于候选键，则称R属于2NF模式。其中，完全函数依赖：左部不可约。 主属性：属于候选键的属性。

55、部分函数依赖：反之。 非主属性：反之例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要求每个非主属性不能由候选键的一部分决定，否则必有冗

56、余。当候选键为单属性时，必为2NF。 分解算法R(WXYZ)，若存在FD：XY，则将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） 定义若R1NF，且每个非主属性都不传递依赖于候选键，则称R属于3NF。其中传递依赖：XY，YA(且Y/ X，AY)，则称XA是传递依赖。等价定义：若R1NF，F中每个非平凡的FD：XY均满足二条件之一 a)X是超键 b)Y中每个属性均为主属性 则称R属于3NF模式。例3-31 对于例3-30关系模式R1、R2，试判断R2是否属于3NF模式。对R2有FD：tnotaddress，而taddress为非主属性且tno非超键，即R23NF。作业1 设关系模式R(ABCD)，FABCD,AD。(1) 试说明R不是2NF的理由。(2) 试将R分解为2NF模式集。 结论3NF要求每个非主属性必须由候选键决定，否则必有数据冗余。2NF只要求每个非主属性不能由候选键的一部分（但可以是非候选键的任意属