实验D001: 数据库及表的建立和管理实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 数据库、数据表的建立与数据的插入 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．了解和掌握SQL Server系统。 2．掌握数据库建立的几种方法。
3．掌握数据表的建立方法和数据表中数据的插入方法。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
前期要求掌握的知识：
1．SQL Server 2005平台构成： 数据库引擎。
分析服务（Analysis Services）。 集成服务（Integration Services）。 复制技术。
通知服务（Notification Services）。 报表服务（Reporting Services）。 服务代理（Service Broker）。 全文搜索。 2．SQL Server 2005安装成功后，系统会自动创建4个系统数据库：Master、Model、Msdb、Tempdb。
3．SQL Server 2005中最重要的管理工具：“SQL Server Management Studio”(简称“SSMS”)。
五、实验内容
1．创建数据库和查看数据库属性。
2．在“SSMS”中和查询编辑器中创建数据表。 3．“SSMS”中和查询编辑器中数据表中数据的插入。 六、验证性实验
(一) 在“SSMS”中创建数据库、数据表和进行数据插入 1．创建数据库
（1）使用“SSMS”建立数据库的步骤
1）单击“开始”菜单下“所有程序”→“Microsoft SQL Server2005（或Microsoft SQL Server2008）” →“SQL Server Management Studio”，如图3-1所示。
图3-1 SQL Server 2005启动
2) 在连接到服务器窗口中选择服务器名（点击服务器名称右面的下拉列表→选择“浏览更多”→展开“数据库引擎”→选择下面出现的本机号，单击“确定”），单击”连接”按钮, 如图3-2所示.。
3) 进入到“Microsoft SQL Server Management Studio（SSMS）”,如图3-3所示.。
图3-2 SQL Server 2005连接服务器界面 图3-3 “SSMS”界面 4）选中“数据库”文件夹，单击鼠标右键，在弹出菜单上选择“新建数据库”。随后在数据库属性对话框的常规页面中，输入数据库名“学生选课”，如图3-4 所示。
新建数据库属性对话框有3 个页面：常规、选项和文件组。常规选项分别用来义数据库名、数据文件属性和日志文件属性。选项是对数据库中游标\\状态等信息的设置。
5）单击“确定”按钮，关闭对话框。在“SSMS”窗口中出现“学生选课”数据库标志，如图3-5 所示，表明已经成功创建数据库。
图3-4 新建数据库界面 图3-5 数据库建立成功界面
6）在“SSMS”中，右键单击“学生选课”数据库，在弹出菜单中选择“属性”，将弹出一个对话框，显示“学生选课”数据库的属性,如图3-6所示。 2．创建数据库表
（1） 打开刚才创建的“ 学生选课” 数据库， 并在“SSMS”窗口的右边窗口中用鼠标右键点击“表”对象，选择“新建表”命令，打开表编辑器窗口，如图3-7 所示。
图3-6 数据库属性窗口 图3-7 表编辑器界面
（2）根据表3-1 所示的表结构，添加新列。
表3-1 学生表 列名 学号 姓名 性别 出生日期 专业名 所在系 联系电话 数据类型 Varchar Varchar Char Smalldatetime Varchar Varchar Varchar 长度 10 20 2 4 20 20 20 是否允许为空 N N Y Y Y Y Y （3）点击工具栏上的“保存”按钮，在弹出的“选择名称”对话框中输入表名“学生- 表”，然后单击“确定”按钮，完成新表的创建。
（4）同理，根据表3-2 建课程表，根据表3-3 建选课表
表3-2 课程表 列名 课程号 课程名 学时 学分 列名 学号 课程号 成绩 数据类型 Char Char Tinyint Tinyint 表3-3 选课表 数据类型 Char Char Tinyint 长度 6 3 1 是否允许为空 N N Y 长度 6 20 1 1 是否允许为空 N N N N
3．使用“SSMS”向数据库表输入数据、修改数据、删除数据。
（1）打开“SSMS”，选中服务器，展开数据库文件夹，进一步展开“学生选课”数据库。单击表文件夹，找到“学生表”。
（2）用鼠标右键单击“学生表”，选择“打开表”――“返回全部行，（SQL2008中选择“编辑前xx行”）就会出现表更新对话框，如图3-8 所示。
图3-8 记录输入界面
（3）参考表3-4 的内容，为学生表输入数据。
（二）在查询编辑器中创建数据库、数据表和进行数据插入
1．在“SSMS”中，点击工具栏中的第一个按钮“新建查询”,如图3-9所示。
图3-9 查询编辑器的打开
打开查询编辑器的窗口，如图3-10 所示。
图3-10 查询编辑器
2．在查询编辑器的右窗口中输入T-SQL语句： create database student
选中该语句并执行（单击工具栏的“执行”按钮），出现
命令已成功 表示数据库成功建立。
3．用T-SQL语句创建数据表STUD：
USE STUDENT(表示student为当前数据库)
CREATE TABLE STUD(SNO VARCHAR(10) NOT NULL, SNAME VARCHAR(20) NOT NULL, SEX CHAR(2), BIRTHDAY DATETIME)
选中上述语句并执行，出现
命令已成功完成 表示数据表已经建立好。可以在“对象资源管理器”中的STUDENT数据库上右击鼠标，并单击“刷新”，然后展开“用户表”，可以看到STUD数据表。
4．向STUD数据表中插入数据记录。
INSERT INTO STUD VALUES（’200512’,’Josephine’,’F’,’1980-12-20’）
选中上述语句并执行，出现
所影响的行数为1行 表示数据已经插入到数据表中。
七、设计性实验(注意：设计性实验的题目用T-SQL命令完成) 1．实验要求
（1）创建一个XSDA数据库，该数据库的主文件逻辑名称为 xsda_data，物理文件名为D:\\SQL\\xsda.mdf，初始大小为20MB，无上限，增长速度为20%；数据库的日志文件逻辑名称为xsda_log，物理文件名为D:\\SQL\\xsda.ldf，初始大小为3MB，最大尺寸为10MB，增长速度为1MB。
（2）在XSDA数据库中创建三个数据表：
S(SNO VARCHAR (10) NOT NULL ,SNAME VARCHAR(20),DEPA VARCHAR(20), AGE INT, SEX CHAR(4) )
C(CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20),cpno varchar(10),credit int)
SC(SNO VARCHAR(10) NOT NULL, CNAME VARCHAR(10) NOT NULL, GRADE DECIMAL（4,1）)
（3）在3个表中输入如下数据。（参考以下表中内容，每个表至少输入4-6个记录）
表3-4 学生表 学号 200512 200518 200018 200511 200510 200513 200514 200012 200515 姓名 李勇 刘晨 王敏 杨扬 张立 张立 王点点 欧阳雨 刘依依 系部 计算机系 计算机系 数学系 物理系 信息系 物理系 信息系 数学系 数学系 年龄 20 19 18 20 19 19 19 20 23 性别 男 女 女 男 男 男 女 男 女 表3-5课程表 课程号 c01 课程名 数据库原理 前修课程 c03 学分 4
c02 c03 c04 信息系统 数据结构 C01 null co1 4 6 3 DB_设计 表3-6 成绩表 学号 200512 200512 200512 200515 200518 200018 200518 200511 200511 …… 课程号 c02 c03 c04 c01 c03 c01 c02 c02 c01 …… 成绩 70 55 null 80 95 80 45 78 45 ……
（4）修改S表结构，增加一个入校时间，列名为RXTIME，日期型。 （5）删除SC表中成绩不及格的记录。
（6）把S表中学号为“200512”的学生的年龄改为19。 2．思考题
（1）SQL Server 2005/2008 提供了哪些基本数据类型？
（2）数据库创建后怎样修改数据库操纵文件和数据库日志文件分配的空间大小？ （3）用数据的导入、导出实现SQL数据库之间；SQL与EXCEL之间的数据传递。
实验D002: 单表查询实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 单表查询 2 □验证 □综合 ✓设计 ✓必做 □选做
二、实验目的
1．掌握数据的基本检索方法。
2．掌握数据查询的Group by和Order by子句的使用。 3． 掌握聚集函数的使用方法。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
前期要求掌握的知识： 数据检索的语句格式：
SELECT [ALL|DISTINCT] <目标列表达式列表> FROM <表名或视图名列表> [ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]
其中，SELECT后的目标列表达式可以是列名、表达式或函数。
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
HAVING短语：筛选出只有满足指定条件的组
ORDER BY子句：对查询结果表按指定列值的升序或降序排序
注意：T-SQL语句中关键字不区分大小写 五、实验内容
1．对数据表进行简单检索。 2．对数据表进行排序检索。 3．在检索中使用聚集函数。
六、验证性实验（首先把精品课程网站-→上机实验数据库脚本中的代码
复制到新建查询窗口执行（注意，只执行一次。为避免重复，执行后把这段代码删除。）
先选择要操作的数据库，用T-SQL命令：
USE STUDENT
或在工具栏的当前数据库中选择STUDENT， 如图3-11所示。
工作数据库
图3-11 查询编辑器界面
以下查询要求在逐个语句执行，执行后将执行结果记录下来。 1．条件查询全部数据 USE STUDENT SELECT * FROM S
2．在SELECT关键字后指明要检索的列名 （1） 查询S表的学生学号和姓名 SELECT SNO，SNAME FROM S （2） 查询S表中的系部名 SELECT DEPA FROM S
（3）查询S表中不重复的系部名 SELECT DISTINCT DEPA FROM S 3．改变列标题的检索
（1）使用 空格 形式： 列名 新标题
SELECT SNO 学号 , SNAME 姓名 FROM S （2）使用“AS”形式，列名 AS 新标题
SELECT SNO AS 学号, SNAME AS 姓名 FROM S 3．有条件选择的查询
（1）在S表检索“信息系”的学生信息
SELECT * FROM S WHERE DEPA=’信息系’
（2）在S表中检索姓“王”的学生信息
SELECT * FROM S
WHERE sname like ‘王%’
（3）在SC表检索’C01’选修课成绩为空的选课记录
SELECT SNO， CNO FROM SC
WHERE cno=’C01’ and grade is null （4）检索年龄为21，18，22的学生学号、姓名
SELECT SNO，SNAME FROM S WHERE age in （21,18,22）
Age IN {21,18,22}表示某条记录的AGE字段值是否是集合{21,18,22}中的元素，如是，则选择。它等价于下面语句：
SELECT SNO，SNAME FROM S
WHERE age=18 or age=21 or age=22 4．使用聚集函数
（1）查询选课表中最高分、平均分、最低分 SELECT MAX(GRADE), AVG(GRADE),MIN(GRADE) FROM SC
（2）查询“C01”课程的最高分、平均分和最小成绩。 SELECT MAX(GRADE), AVG(GRADE),MIN(GRADE) FROM SC
WHERE CNO=‘C01’ 5．对检索结果进行排序 SELECT * FROM SC WHERE GRADE IS NULL ORDER BY SNO，CNO DESC 6．进行分组统计：
（1）查询各学生的选课数 SELECT SNO，COUNT（*） FROM SC
GROUP BY SNO
（2）使用HAVING 字句：“选课表”中查询选修了3 门以上课程的学生学号。 SELECT SNO ，COUNT（*）FROM SC GROUP BY SNO
HAVING COUNT(*)>=3
（3）“选课表”中按学号分组汇总学生的平均分，并按平均分的降序排列。 SELECT SNO 学号, AVG(GRADE)平均分 FROM SC GROUP BY SNO
ORDER BY 平均分 DESC
（4）分析下面两个SELECT语句执行结果，说明有什么不同？ SELECT SNO FROM SC ORDER BY SNO GO
SELECT SNO FROM SC GROUP BY SNO 七、设计性实验
1．实验要求
（1）查询计算机系学生的学号和姓名。 （2）查询选修了课程的学生学号。
（3）查询选修“C01” 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，
如果成绩相同则按学号的升序排列。 （4）查询每门课程的平均分。 （5）查询学校开设的课程总数。
（6）查询选修两门及两门以上课程的学生学号。（此题除了代码，执行结果，还请写
出代码的执行过程。）
（7）查询年龄在20-22之间的男生的学号、姓名、系部。 （8）查询选修“C01”课程的学生人数。
2．实验报告要求（见精品课程网站上机指导中实验报告参考） 3．思考题
（1）使用ORDER BY子句后SELECT后的数据列有什么限制吗？使用GROUP BY 呢？
（2）执行select max(grade) from sc, 写出执行结果；执行 select sno, max(grade) from sc group by sno,写出执行结果。思考：使用GROUP BY<分组条件>子句后，语句中的统计函数的运行结果有什么不同?
实验D003： 多表查询实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 多表查询 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．掌握多表连接查询方法。 2．掌握IN 子查询的嵌套查询。 3．了解EXISTS嵌套查询方法。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．多表的连接查询（相当于做笛卡儿乘积） SELECT <目标列表达式列表> FROM 表名，表名 2．多表的等值连接查询
SELECT [ALL|DISTINCT] <表名.列名>… FROM 表1，表2
WHERE 表1.列名=表2.列名 注意：多表连接时要注意当某列名在两张表中同时存在时，在列名前要加表名以示区分。 3．表自身的连接
SELECT 别名.列名，…
FROM 表1 AS 别名1， 表1 AS 别名2 WHERE 别名1.列名=别名2.列名
注意：自身连接时因为是对同一张表操作，为区分开来对该表操作的顺序，需要对表取两个别名，以示区别。
4．IN嵌套子查询
SELECT <目标列表达式列表> FROM 表名
WHERE 列名 IN （SELECT 字句）
5． EXISTS嵌套子查询
–带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 ●若内层查询结果非空，则返回真值 ●若内层查询结果为空，则返回假值。
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
所以，EXISTS子查询中一般是相关自查询，即子查询脱离父查询后不能单独执行。 思考：如果EXISTS子查询中是不相关子查询，会有什么结果？
五、实验内容
1．多表等值连接查询； 2．外连接查询； 3．IN子查询嵌套； 4．EXISTS嵌套查询。 六、验证性实验
1．多表的连接查询（相当于做笛卡儿乘积） SELECT * FROM C，SC 2．表的等值连接查询
(1) 查询各学生的选课信息（包括学号、课程名、成绩）
因为学号和成绩在SC表中，而课程名在C表中，因此需要多表查询。 SELECT SNO，CNAME，GRADE FROM C，SC
WHERE C.CNO=SC.CNO
(2) 查询学生的选课记录，显示学生的学号、姓名、课程号、成绩。 SELECT S.SNO，SNAME，CNO，GRADE FROM S，SC
WHERE S.SNO=SC.SNO
(3) 查询学生的选课记录，显示学生的学号、姓名、课程号、课程名、成绩 SELECT S.SNO，SNAME，SC.CNO，CNAME，GRADE FROM S，SC，C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO
3．左外连接查询：当希望左表（第一张表）中所有记录全部显示出来时，需要用左外连接操作。
INSERT INTO S (SNO,SNAME) VALUES（’20000’,’ZXX’） SELECT S.SNO，SNAME，CNO，GRADE FROM S
LEFT OUTER JOIN SC ON S.SNO=SC.SNO
说明与下面等值连接的执行结果有何不同？ SELECT S.SNO，SNAME，CNO，GRADE FROM S, SC WHERE S.SNO=SC.SNO
4．表自身的连接
(1) 查询与‘李勇’同系的学生学号
将学生表S与S本身进行等值连接（系部相等），因为S与S做连接操作时不能区分，所以，对表取一个别名。然后将第二张表S中名字为‘李勇’的记录选择出就可。
SELECT S1.SNO
FROM S AS S1， S AS S2
WHERE S1.DEPA=S2.DEPA AND S2.SNAME=’李勇’
（2）显示每个学生的非最高分成绩（学生自己的选课成绩中，不是最高分的选课记录显示出来）
SELECT SNO, CNO, GRADE FROM SC AS SC1
WHERE GRADE <(SELECT MAX(GRADE) FROM SC AS SC2 WHERE SC2.SNO=SC1.SNO) 5．IN嵌套子查询
（1） 不相关的IN 子查询：子查询可以单独执行，与被嵌套的查询无关。 如，查询与‘李勇’同系的学生学号
可以先查询出‘李勇’所在的系，然后再到S表中查询与上述结果相同的记录。 SELECT SNO FROM S
WHERE DEPA IN
（SELECT DEPA FROM S WHERE SNAME=‘李勇’） 查询‘数据库原理’课程的选课人数 SELECT COUNT（*） FROM SC WHERE CNO IN
（ SELECT CNO FROM C WHERE CNAME=‘数据库原理’）
（2） 相关的子查询：子查询中要用到父查询表的信息，子查询不能独立执行。 如，查询选修课程号为“C01”课程且成绩至少高于选修课程号为“C02”的同学的Cno、Sno和GRADE。
在子查询中，因为要查找该同学‘C02’课程的成绩，所以，需要父查询表中该学生的学号信息。
SELECT CNO，SNO，GRADE FROM SC AS SC1
WHERE CNO=’C01’ AND GRADE >(
SELECT GRADE FROM SC AS SC 2
WHERE SC2.SNO=SC1.SNO AND SC2.CNO=’C02’) 6． EXISTS嵌套子查询
（1） 执行以下语句，观察显示的两个查询结果 SELECT SNO，SNAME FROM S
WHERE EXISTS
（SELECT * FROM SC WHERE CNO=‘C03’） -----请写出这段代码的执行过程。
GO
SELECT SNO，SNAME FROM S
（从结果看，第一段代码有没有实际的意义？） （2）执行以下语句，观察显示的两个查询结果
INSERT INTO C VALUES（‘C06’，‘数据库安全’，NULL，3） SELECT SNO，SNAME FROM S
WHERE EXISTS
（SELECT * FROM SC WHERE CNO=‘C06’） （3）查询‘数据库原理’课程的选课人数 SELECT COUNT（*） FROM SC
WHERE EXISTS
（ SELECT * FROM C
WHERE C.CNO=SC.CNO AND CNAME=’ 数据库原理’) 七、设计性实验
1．实验要求
（1）查询“计算机系”学生所学课程的成绩表。
（2）查询成绩比该课程平均成绩低的同学的成绩表。（提示：用相关子查询。）
（3）查询选修“C01” 课程的学生学号、课程名、成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。
（4）查询选修两门及两门以上课程的学生学号及姓名。 （5）查询年龄在20-22之间的男生的选修的课程号。 （6）查询选修‘数据结构’课程的学生人数。
（7）查询每门选课成绩在80分以上的学生学号、姓名。（提示：每个学生选修的所有课程中如果其最低成绩为>=80即满足条件）
2．思考题
（1）多表的连接查询是否都可以转换成嵌套查询？什么情况下连接查询不能用嵌套查询表示？
（2）多表连接查询和IN嵌套子查询从查询效率上来说，哪种查询的效果更好？为什么?
实验D004： 数据完整性约束实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 数据完整性约束 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．掌握数据完整性约束的类型。
2．掌握SQL SERVER中的相关完整性约束。
三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．SQL SERVER中的完整性约束
（1）Primary key约束:利用表中的一列或多列来唯一标识一行数据.能确保primary key 对应的数据列不为空,且数据不重复.
（2）default约束:处理用户不包含全部数据列的数据插入. （3）check约束通过检查输入数据的值来维护数据的完整性. （4）unique约束确保主键外的列数据的唯一性
（5）Foreign key主要用来维护两个表之间的数据一致性. 2．使用规则 规则的创建:
Create rule 规则名 As
布尔表达式 规则的绑定:
Sp_bindrule rulename, ‘table.column’Sp_unbindrule ‘talbe.column’ 3．创建数据表时指明完整性约束 CREATE TABLE <表名>
（<列名> <数据类型>[ <列级完整性约束条件> ]
[，<列名> <数据类型>[ <列级完整性约束条件>] ] …… [，<表级完整性约束条件> ] ）；
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件 五、实验内容
1．建立新表时增加完整性约束。 2．为已有表添加完整性约束。
3．为两表建立关联，实现参照完整性。 六、验证性实验
每小题语句输入好后执行，观察执行后有什么结果？想想为什么？
1．PRIMARY KEY主键约束的建立 （1）建立表时加主键约束
CREATE TABLE ST（SNO VARCHAR (10) PRIMARY KEY ,SNAME VARCHAR(20) NOT NULL, DEPA VARCHAR(20), AGE INT, SEX CHAR(4)）
GO
INSERT INTO ST （SNO，SNAME，DEPA） VALUES（‘1001’，‘ZXX’，NULL） GO
INSERT INTO ST （SNO，SNAME） VALUES（‘1001’，‘MID’） GO
INSERT INTO ST （SNO，SNAME） VALUES（NULL，‘ZXX’） （2）在已有的表上添加约束
CREATE TABLE ST2（SNO VARCHAR (10) NOT NULL ,SNAME VARCHAR(20)
NOT NULL, DEPA VARCHAR(20), AGE INT, SEX CHAR(4)）
GO
INSERT INTO ST2 SELECT * FROM S GO
ALTER TABLE ST2 ADD CONSTRAINT priKEY PRIMARY KEY（SNO） INSERT INTO ST2 （SNO，SNAME，DEPA） VALUES（‘200512’，‘Beibei’，NULL）
运行后出现什么结果？分析原因。 2．DEFAULT约束
CREATE TABLE CUST（NO VARCHAR（5） PRIMARY KEY， WEIGHT INT DEFAULT（10））
GO
INSERT INTO CUST（NO） VALUES（‘ZY01’） INSERT INTO CUST（NO） VALUES（‘ZY03’） INSERT INTO CUST VALUES（‘ZY02’，20） GO
SELECT * FROM CUST
运行后出现什么结果？分析原因。 3．CHECK 约束
CREATE TABLE CUSTOMER（CUSTNO CHAR（4） NOT NULL CHECK （CUSTNO LIKE ‘[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]’），CUSTNAME VARCHAR（20））
GO
INSERT INTO CUSTOMER VALUES（‘BJ01’，‘Beijing Grid Corp.’） GO
INSERT INTO CUSTOMER VALUES（‘BJ1’，‘Beijing Grid Corp.’） GO
INSERT INTO CUSTOMER VALUES（‘BJ*1’，‘Beijing Grid Corp.’） 运行后出现什么结果？分析原因。 4．UNIQUE 唯一性约束的建立
CREATE TABLE CUST1（CUSTNO CHAR（4）PRIMARY KEY，CUSTNAME VARCHAR（20） UNIQUE， COUNTRY VARCHAR（10））
GO
INSERT INTO CUST1（CUSTNO） VALUES（‘BJ01’） GO
INSERT INTO CUST1（CUSTNO） VALUES（‘BJ02’） GO
INSERT INTO CUST1(CUSTNO, CUSTNAME ) VALUES（‘BJ03’，‘Beijing Grid Corp.’） GO
INSERT INTO CUST1 (CUSTNO, CUSTNAME ) VALUES（‘BJ04’，‘Beijing Grid Corp.’） 运行后出现什么结果？分析原因。 5．FOREIGN KEY外键约束的建立
ALTER TABLE C ADD CONSTRAINT CpriKEY PRIMARY KEY(CNO)
GO
ALTER TABLE SC ADD CONSTRAINT scpriKEY PRIMARY KEY(SNO,CNO) ALTER TABLE SC ADD CONSTRAINT scforKEY1 FOREIGN KEY (SNO) REFERENCES ST2(SNO)
ALTER TABLE SC ADD CONSTRAINT scforKEY2 FOREIGN KEY (CNO) REFERENCES C(CNO)
GO
INSERT INTO SC (SNO,CNO) VALUES(‘890’,’C10’) INSERT INTO SC (SNO,CNO) VALUES(‘200518’,’C01’) INSERT INTO SC (SNO,CNO) VALUES(‘890’,NULL) INSERT INTO SC (SNO,CNO) VALUES(NULL,’C10’) 运行后出现什么结果？分析原因。 七、设计性实验
1．实验要求
（1） 在S表中添加完整性约束：SNO设置为主键，SEX 的默认值为‘女’,AGE 的有效值为16-25。
（2）创建1张与S相同的表S1，在创建的同时将SNO设置为主键，SEX 的默认值为‘女’,AGE 的有效值为16-25 ，并将S表中的数据插入到S1中，插入不同的记录（用INSERT命令）来验证设置的完整性。
（3）创建1张与SC相同的表SC1，将（SNO，CNO）设置为主键，SNO和CNO设置为外键，并将SC表中的数据复制到SC1中，插入不同的记录来验证设置的完整性。
（4）创建1张与C表相同的表C1，将C中数据插入到C1后，在C1上添加完整性约束：将（CNO）设置为主键，CPNO 引用 CNO，CREDIT值为3-6。
2．思考题
SQL Server 中有哪些完整性功能？保证数据完整性还需要注意哪些方面的问题？
实验D005： 数据操作与索引实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 数据操作与索引 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．掌握数据操作--插入、删除、修改。 2．掌握数据表的索引方法。
3．掌握视图的创建机制及其上的操作。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．数据插入的两种方式
单记录插入—将数值列表所表示的记录插入数据表
INSERT INTO 表名[<目标列表>] VALUES（数值列表） 多记录插入—将SELECT语句的查询结果插入到数据表 INSERT INTO表名[<目标列表>] SELECT 语句
2．数据删除的语句格式： DELETE FROM 表名 [WHERE 字句] 3．数据修改格式： UPDATE 表名
SET 字段名=表达式 [WHERE 字句]
4．索引—提高数据效率的有效方法，建立索引的方法有两种：
非聚族索引：根据某些字段按次序建立索引表，但是不修改原来的数据表；1张数据表上可以建立多个非聚族索引。
聚族索引：根据某些字段进行排序建立索引，同时将原表根据该字段排序过。一张表上只能建立一个聚族索引。
5．根据索引看数据表SELECT * FROM 表名 （INDEX=该表上的索引名） 五、实验内容
1．数据的插入操作。 2．数据的修改操作。 3．数据的查询造作。
4．聚族索引、非聚族索引的建立。 5．视图的建立及其上的操作。 六、验证性实验
1．数据的插入 （1）单记录的插入 USE STUDENT GO
SELECT * FROM S
INSERT INTO S（SNO，SNAME） VALUES（’200300’,’Paulwen’） SELECT * FROM S （2）多记录的插入
CREATE TABLE SBAK(SNO VARCHAR (10) NOT NULL ,SNAME VARCHAR(20),DEPA VARCHAR(20), AGE INT, SEX CHAR(4))
GO
SELECT * FROM SBAK
INSERT INTO SBAK SELECT * FROM S SELECT * FROM SBAK 2．数据的删除
CREATE TABLE CBAK（ CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20), CPNO VARCHAR（10），CREDIT INT）
GO
INSERT INTO CBAK SELECT * FROM C DELETE FROM CBAK WHERE CREDIT<4 SELECT * FROM CBAK 3．数据的修改
SELECT * FROM S
UPDATE S SET DEPA=‘CS’ WHERE DEPA=‘计算机系’ GO
SELECT * FROM S 4．索引的建立
（1） 建立非聚族索引 SELECT * FROM C GO
CREATE INDEX ICNAME ON C（CNAME） GO
SELECT * FROM C /*看数据表 */
SELECT * FROM C WITH（INDEX=ICNAME） /*按索引次序看数据表 */ （2）建立聚族索引 SELECT * FROM C GO
CREATE CLUSTERED INDEX CICNO ON C（CNO） GO
SELECT * FROM C
SELECT * FROM C WITH（INDEX=CICNO） （3） 建立唯一索引
CREATE UNIQUE INDEX UISNO ON SC （SNO） 执行后有什么结果？为什么会出现这个结果？
CREATE UNIQUE INDEX UISNO ON SC （SNO， CNO DESC） 执行后有什么结果？为什么？ （4）建立复合索引
SELECT * FROM S GO
CREATE INDEX IAGESNO ON S（AGE DESC，SNO） GO
SELECT * FROM S
SELECT * FROM S WITH（INDEX =IAGESNO） （5）查看表中的索引 SP_HELPINDEX ‘S’ （6）删除表中的索引
DROP INDEX S.IAGESNO GO
SP_HELPINDEX ‘S’ 5．视图及其操作 （1）视图的建立
CREATE VIEW SHOWSNO AS
SELECT S.SNO，SNAME，AVG(GRADE ) AS 平均成绩 FROM S，SC
WHERE S.SNO=SC.SNO GROUP BY S.SNO，SNAME GO
SELECT * FROM SHOWSNO
创建信息系学生的视图
CREATE VIEW VISDEPA AS
SELECT * FROM S WHERE DEPA=’ 信息系’ （2）在视图上修改数据 UPDATE VISDEPA SET DEPA=’IS’ GO
SELECT * FROM VISDEPA 视图中还有数据吗？为什么会这样？ CREATE VIEW S_SUM AS
SELECT SNO, SUM(GRADE) AS TOTALSCORE FROM SC GROUP BY SNO GO
UPDATE S_SUM
SET TOTALSCORE=60
执行语句，出现什么结果？分析为什么？ （3）在视图上删除数据
SELECT * FROM VISDEPA
DELETE FROM VISDEPA WHERE SEX=‘女’ SELECT * FROM VISDEPA SELECT * FROM S 七、设计性实验
1．实验要求
（1）将S表系部为“CS”学生对应的系部改为‘计算机系’。
（2）创建一个与SC表相同新数据表SCNEW，查询SC表中成绩为空的记录并将其复
制到SCNEW表中。
（3）删除SC表中成绩为空的选课记录。
（4）根据S表中姓名字段建立一个降序非簇索引。 （5）建立S表上SNO的聚族索引。
（6）建立SC表上根据SNO升序，CNO降序的复合索引。
（7）建立每门课程的课程号、课程名、选课人数、平均分的视图。
2．思考题
（1）是否可以对任意的视图进行修改？什么样的视图上不能进行修改操作？ （2）如果表中某个字段重复值比较多，要不要在该字段上建立索引？为什么？
实验D006： SQL编程及存储过程实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 SQL编程及存储过程 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．了解T-SQL的基本数据类型、函数； 2．掌握局部变量的定义和赋值； 3．掌握存储过程的定义及调用 三、实验的软硬件环境要求
硬件环境要求： PC机（单机）
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008） 四、知识准备
1．SQL中的基本数据类型： varchar：字符数据。 datetime：（常量使用特定格式的字符日期值来表示，并使用单引号括起来。例如：'1976-05-28'，'May 28, 1976'，'28 May, 1976'，'760528'，'05/28/76'。
Integer：用一串数字来表示，不含小数点，不使用引号。例如，123，1896。
Decimal：用一串数字来表示，可以包含小数点，不使用引号。例如，1893.1209，2.0。 float和real： 使用科学记数法表示。例如，101.5E5，0.5E-2。
money：用一串数字，可以包含或不包含小数点，以一个货币符号（$）作为前缀，不使用等等
2．SQL中的变量：SQL中变量用来存放临时数据，变量使用前一定要先进行定义，变量名必须以@开头。另外，SQL Server中还提供一些系统变量，系统变量是以@@开头的。
变量的定义： declare 变量名 数据类型 如 go
Declare @name varchar(20) set @name='王雨' Print @name Go
3．SQL中的基本函数
(1) 字符串函数:LEN()、UPPER（）、LOWER（）、RIGHT（）、LEFT（）、SUBSTRING
（）、CHARINDEX（）、STR（）、REPLACE（）等
（2） 数学函数：常用的数学函数通常对作为参数提供的输入值执行计算，并返回一个数字值。如ABS（）、POWER（）、SQUARE（）、SQRT（）、ROUND（）、RAND（）等。
（3）日期和时间函数：日期和时间函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。GETDATE（）、DATEADD（）、DATEIFF（）、DATEPART（）、ISDATE（）、DAY（）、MONTH（）、YEAR（）等
（4）数据类型转换函数 CAST函数 CAST( expression AS data_type ) CONVERT函数 CONVERT( data_type[ ( length ) ], expression [ , style ] ) 4．存储过程的创建及调用 CREATE proc 过程名
[{@变两名 数据类型}][, …n] AS Sql语句
调用 ： EXEC过程名 参数列表
DECLARE @变量名 数据类型
注意：SAL中的局部变量名必须以@开头。
五、实验内容
1．基本结构编程。 2．存储过程的建立。 3．存储过程的调用。 六、验证性实验
1．编程实现查询与‘李勇’同系的学生学号 USE STUDENT GO
DECLARE @DEPA VARCHAR（20）
SELECT @DEPA=DEPA FROM S WHERE SNAME=’李勇’ SELECT SNO FROM S WHERE DEPA=@DEPA
2．运行下面的程序，观察运行结果 DECLARE @X INT, @Y INT SET @X=0 SELECT @Y=1 WHILE @Y<20 BEGIN
SET @X=@X+@Y SELECT @Y=@Y+2
PRINT STR(@Y)+’IN THE LOOP’ IF @Y>14 BREAK END
PRINT ‘OUT OF THE LOOP’
3． CASE函数的使用
（1） CASE后带表达式
Select 性别=case sex
when ‘男’ then ‘M’ when ‘女’ then ‘F’ else ‘输入出错’ end From s
（2） CASE后不带参数 SELECT 性别=CASE
WHEN SEX= ‘男’ THEN ‘M’ WHEN SEX=‘女’ THEN ‘F’ ELSE ‘ERROR’ END
FROM S (3) 用CASE 语句进行多条件修改 update s set depa= case depa
when '计算机系' then 'CS' when '信息系' then 'IS' when '数学系' then 'MA' when '物理系' then 'PH' end
4．存储过程
（1） 不带参数的存储过程
CREATE PROC SCLIST AS
SELECT SNO,CNO, GRADE FROM SC WHERE SNO IN (SELECT SNO FROM S WHERE DEPA=’CS’) 执行存储过程： EXEC SCLIST 观察结果
（2） 带参数的存储过程
如：传入一个学生的学号与姓名,显示出这个学生选修的课程号,成绩 CREATE PROC SC_PROC @xh varchar(10) AS
SELECT cno , grade From sc
Where sc.sno=@xh 执行存储过程：
EXEC SC_PROC ‘200512’ 5． 存储过程的相关操作 (1) 查看存储过程:
Sp_help SCLIST (2) 重命名存储过程
Sp_rename ‘SCLIST’， ‘STUDENTSCORE’ 七、设计性实验
1．实验要求
（1） 调用帮助系统来查找系统存储过程或函数来显示SQL SERVER的版本号及当前系
统时间。
（提示：函数 Getdate()为系统当前时间；系统变量 @@version 为当前版本号。）
）
（2） 编写存储过程：完成1！+2！+…+n！的计算。 （提示：传入参数n，根据N用循环控制来求对应的值）
（3）将S表中的DEPA字段内容改为中文：IS-信息系，CS-计算机系，PH-物理系，MA-数学系。
（提示：用UPDATE语句来修改DEPA字段，DEPA的值根据不同英文缩写要修改为不同的中文系名，用CASE函数来完成）
（4） 将C表上增加一个字段seleNUM(数据类型INT,用来统计每门课的选课人数)，先用UPDATE命令填入C表中每门课程的选课人数（根据SC表中的选课情况），再编写一个存储过程：传入学号和课程号后，完成在SC表中插入相应的选课记录，并在C表中对应课程的seleNUM加1。
2．思考题
（1）简单描述存储过程的使用步骤？
（2）带参数的存储过程定义时参数定义语句可以在AS后面吗？
实验D007： 事务处理和触发器实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 事务处理和触发器 2 □验证 □综合 ✓设计 ✓必做 □选做 二、实验目的
1．了解事务的概念和基本特征。 2．掌握事务的提交与回滚操作。 3．掌握触发器的基本原理。 4．掌握触发器的建立语句。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．事务是一段与数据库打交道的程序，它能保持企业状态和数据库状态的一致性。当某一事件影响企业状态时，事务会更新数据库状态以体现这个事件的发生。如银行里的存款
事务，事件是客户向出纳员提供现金和存款单，事务则是更新数据库中客户的帐号信息以体现这次存款事件。
2．事务处理具有原子性(atomicity)：系统必须保证这段程序要么执行到结束，要么就一点效果也没有。如果一个事务成功执行，我们称其已经提交（commit)；一个事务没有正常完成，称其已经中止（abort)，这时候，事务监视器有责任保证事物对数据库造成的部分改变要修改回来，这就叫事务回滚(roll back)
3．触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。q在
使用触发器过程中，SQL Server使用到了两张特殊的临时表，分别是inserted和deleted表。
（1）在deleted表中存放Update和delete影响的旧数据行。在执行Update和delete时先将数据从基本表中删除，然后被转移到deleted表中。
（2）在inserted表中存放Update和insert影响的数据。当用户执行Update和insert时，将数据添加到基本表中，同时，将数据行的备份复制到inserted临时表中。
4．触发器的创建格式
CREATE TRIGGER 触发器名 ON 表名 AFTER INSERT/UPDATE/DELETE AS
T-SQL语句组 五、实验内容
1．事务的提交与回滚操作。 2．触发器的建立。
3．触发器的修改、删除操作。 4．触发器的删除。 六、验证性实验
1．用ROLLBACK TRAN实现事务的回滚操作。 USE STUDENT GO
BEGIN TRAN
SELECT * FROM C
INSERT INTO C VALUES(‘C08’, ‘决策支持系统’, NULL, 3) SELECT * FROM C ROLLBACK TRAN SELECT * FROM C
观察每次显示C表中数据记录条数变化，为什么有这种变化？ 2． 事务回滚点的保存 USE STUDENT GO
BEGIN TRAN Mytran SELECT * FROM C
INSERT INTO C VALUES(‘C09’, ‘信息分析与预测’, NULL,4) SAVE TRAN POINT1
INSERT INTO C VALUES(‘C10’, ‘数据挖掘’, NULL, 3) SELECT * FROM C ROLLBACK TRAN POINT1
SELECT * FROM C
观察最后的C表中的数据结果与前面C表显示结果的不同。 3．触发器的建立
（1） 在S表建立建立如下触发器 CREATE TRIGGER TRIGONS ON S FOR UPDATE AS
SELECT * FROM DELETED SELECT * FROM INSERTED
选中上述触发器创建语句并执行，然后输入下面语句： UPDATE S
SET DEPA =‘计算机系’ WHERE DEPA=‘CP‘ DELETED表 显示结果：
INSERTED表
（2）在S表上建立下面触发器：
CREATE TRIGGER TEMPTABLE ON S FOR INSERT AS
SELECT * FROM INSERTED ROLLBACK TRAN
执行上述触发器后在S表中插入数据，
INSERT INTO S（SNO，SNAME） VALUES（‘10012’，‘ZXX’） 执行插入语句后打开S观察结果。
4．在SC表上插入一条选课记录后，将C表中对应的课程的seleNUM值加1。（认真完成此题，注意每一步的含义）
Alter table c add seleNUM int---- 在C表增加一个字段 go Update c
Set selenum=(select count(sno) from sc where cno=c.cno)------- C表上的SELENUM的值的填入（根据SC表选课的情况) go
select cno,selenum from c-----查看C表上每门课的选课人数 go
CREATE TRIGGER SELECTCOURSE ON SC FOR INSERT AS
IF (SELECT COUNT(*) FROM INSERTED,sc where sc.sno=inserted.sno and sc.cno=inserted.cno)>1 ----如果SC表中某个学生已有这门课的选课记录
BEGIN
PRINT 'YOU CAN INSERT ONE RECORD ONCE. SO THE RECORDS ARE NOT INSERTED INTO THE TABLE' ROLLBACK TRAN END ELSE BEGIN
DECLARE @CNO VARCHAR(10) SELECT @CNO=CNO FROM INSERTED
If ( select seleNum from c where cno=@cno) is null Begin
Update c set seleNum=0 where cno=@cno end UPDATE C
SET seleNUM=selenum+1 WHERE CNO=@CNO-----C表中根据刚插入的选课记录中的课程号，选课人数增加
PRINT 'THE NUMBER OF STUDENTS WHO SELECTED'+@CNO +' HAS BEENODDED' END GO
insert sc
values('200512','c02',80)-----此记录被拒 go
insert sc
values('200912','c02',90)-------此次记录成功插入SC
select * from c------查看C表中的记录，看看C02的选课人数是否加 GO
insert c(cno,cname,credit)
values('c05','操作系统',4) )---C表中插入一条记录（原来没有这门课的） go
select * from c------注意观察C05的选课人数为null go insert sc
values('200912','c05',90) go
select * from c -----再次观察结果
5．触发器的修改、删除
（1） 修改触发器
ALTER TRIGGER 触发器名 ON 表名 AFTER INSERT/UPDATE/DELETE AS
T-SQL语句组
在调试触发器时可以用修改触发器的语句来进行。 如 CREATE TRIGGER DELS ON S FOR DELETE
AS
DECLARE @DEPA VARCHAR（20）
SELECT @DEPA=DEPA FROM INSERTED IF @DEPA=’CS’ BEGIN
PRINT ‘YOU CAN NOT DELETE THE STUDENT FROM CS’ ROLLBACK END
执行上面的触发器，发现错误，则修改触发器。 ALTER TRIGGER DELS ON S FOR DELETE AS
DECLARE @DEPA VARCHAR（20）
SELECT @DEPA=DEPA FROM DELETED IF @DEPA=’CS’ BEGIN
PRINT ‘YOU CAN NOT DELETE THE STUDENT FROM CS’ ROLLBACK END
（2） 删除触发器
DROP TRIGGER 表名.触发器名 DROP TRIGGER S. DELS
七、设计性实验
1．实验要求
（1） 将S表的PRIMARY KEY完整性约束删除，编写一个S表上的触发器来完成在S表上插入记录时实现如下目标：SNO 不能为空，且SNO不能重复。
（2） 在C表修改记录时，若修改的是CREDIT字段，则修改值超过6时提示出错信息并不将结果写入数据表。
2．思考题
（1）什么是触发器？主要功能是什么？
（2）触发器运行过程中，有什么机制来查看修改了哪些记录信息？
实验D008： 游标的操作实验
一、实验名称和性质
所属课程 实验名称 数据库原理 游标的操作
实验学时 实验性质 必做/选做 2 ✓验证 □综合 □设计 □必做 ✓选做 二、实验目的
1．理解游标的基本原理。 2．掌握游标的使用流程。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．数据库的游标是类似于C语言指针一样的数据结构。 2．为什么使用游标？
多数SQL语句同时处理集合内部的所有数据。但是，当用户需要对这些数据集合中的某一行数据进行操作。若不使用游标，则要在数据库前端用高级语言来实现，将导致不必要的数据传输。游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的一行。
3．使用游标的基本步骤
（1）定义游标。在内存中开辟一块特殊的空间—游标，并根据游标定义语句的内容设置游标空间的数据结构。格式：
DECLARE <游标名称> [ INSENSITIVE ] [ SCROLL ] CURSOR FOR SELECT语句
[ FOR { READ ONLY | UPDATE [OF <列名>[ ,...n ] ] } ]
（2）打开游标。执行定义游标中的SELECT语句，并将SELECT语句的查询结果填充到游标空间。
•OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } 游标打开成功后，可以使用函数@@CURSOR_ROWS来获取这个游标中当前存在的行数。函数@@CURSOR_ROWS有四种可能的返回值：
•-m 表明游标被异步填充。返回值（-m）是键集中当前的行数 •-1 表明游标为动态的。因为动态游标可以反映所有更改，所以符合游标的行数不断变化。因而永远不能确定地说所有符合条件的行均已检索到
•0 表示没有被打开的游标，没有符合最后打开的游标的行，或最后打开的游标已被关闭或被释放
•n 游标已完全填充。返回值（n）是在游标中的总行数 （3）通过游标提取数据行。每次从游标中提取一行数据 •FETCH
•[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar } ]
•FROM] [GLOBAL] <游标名称> •[ INTO @<变量名>[ ,...n ] ] （4）关闭游标 （5）释放游标 五、实验内容
1．学习游标语句格式。
2．游标的运用。 六、验证性实验
1．实验要求
希望按照下面格式显示‘200512’的选课成绩，编程完成。 200512 王敏 数学系 选课成绩如下： 课程名 成绩 数据结构 95 信息系统 45
总成绩：150 平均成绩：75
分析：用SELECT多表操作进行查询，则得到的信息会使“200512 王敏 数学系”重复。为避免重复，可以将“200512 王敏 数学系”先显示，再根据学号将该学生的选课记录放在游标中，每次读取游标中一行数据，即一门选课信息，显示出来，再读取下一行数据就可满足要求。
2．源程序代码
declare @sname varchar(20),@depa varchar(2)
select @sname=sname, @depa =depa from s where sno=’200512’
print ‘200512’+ ‘ ’+str(@sname)+ ‘ ’+str(@depa)+ ‘ 选课成绩如下：’
declare 学生成绩单 cursor for
select cname,grade from sc,c where sc.cno=c.cno and sno=‘200512’ declare @cname varchar(20), @grade int, @totalscore int,@coursenum int open 学生成绩单 /* open the course */ set @totalscore=0 set @coursenum=0
print ‘课程名 成绩’
fetch next from 学生成绩单 into @cname,@grade while (@@fetch_status=0) begin
print @cname+ ‘ ’ +str(@grade) set @totalscore=@totalscore+@grade set @courseNum=@coursenum+1
fetch next from 学生成绩单 into @cname,@grade end
Print‘总成绩：’+str(@totalscore)+’ 平均成绩：'+str(@totalscore/@coursenum) close 学生成绩单
deallocate 学生成绩单 六、设计实验
1、实验要求
输入学生学号，可以按照验证性实验中要求的格式输出学生的选课成绩。
实验D009： 数据库安全与数据库恢复实验
一、实验名称和性质
所属课程 数据库原理
实验名称 实验学时 实验性质 必做/选做 数据库安全与数据库恢复 2 ✓验证 □综合 □设计 □必做 ✓选做
二、实验目的
1．了解数据库的安全性控制方法。 2．了解数据库恢复的基本原理。 3．掌握数据库备份和恢复机制 。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．数据库系统的安全控制见图3-12所示。 DBMS 应用程序 用户标识 存取控制 操作系统 密码存储 和鉴定 审计、视图安全保护 图3-12安全性控制层次图
2．数据库的安全性控制机制
（1）用户标识与鉴定：系统提供一定的方式让用户标识自己的名字或身份；系统内部记录着所有合法用户的标识；
（2）l存取控制：用户权限定义和合法权检查机制一起组成了DBMS的安全子系统 （3）定义视图：视图机制把要保密的数据对无权存取这些数据的用户隐藏起来 （4）审计功能启用一个专用的审计日志（Audit Log），系统自动将用户对数据库的所有操作记录在上面
（5）数据加密：防止数据库中数据在存储和传输中失密的有效手段 3．数据库恢复的基本原理
利用存储在系统其他地方的冗余数据来重建数据库中已经被破坏或已经不正确的那部分数据；恢复机制涉及的关键问题：
如何建立冗余数据 •数据转储 •登记日志文件
如何利用这些冗余数据实施数据库恢复
五、实验内容
1．设置SQL Server 的安全认证模式。
安全性控制
OS
DB
2．设置登录帐户。 3．设置数据库用户。 4．设置数据库角色。 5．设置数据库用户权限。 6．数据库备份。 7．数据库恢复 六、验证性实验
1．设置SQL Server 的安全认证模式。
（1）打开SSMS，在“树”窗口中展开一个服务器组，然后选择希望设置身份验证模式的服务器。
（2）在该服务器上单击鼠标右键，在弹出的菜单中选择命令“属性”，打开“属性”对话框。如图3-13 所示。
（3）在属性对话框中选择“安全性”选项卡，在“身份验证”区域中选择下列身份验证模式之一。
SQL Server 和Windows:指定用户可以使用SQL Server 身份验证和Windows 身份验证
连接到SQL Server。
Windows:指定用户只能使用Windows 身份验证连接SQL Server。 （4）单击“确定”按钮，即可完成身份验证模式的选择和设置。 2．设置登陆帐号
（1）打开SSMS，展开希望创建新的登录的服务器。
（2）展开文件夹“安全性”，在登录节点上单击鼠标右键。
（3）弹出的菜单中选择“新建登录”，打开“新建登录”窗口，如图3-14 所示。
图3-13数据库安全认证模式对话框 图3-14新建登录对话框 （4）在“新建登录”窗口的“常规”选项卡中进行如下配置：
在“名称”文本框中输入一个SQL Server 登录的账号名； 选择一种登录模式； 在“默认设置”区选择连接时默认的数据库XSCJ 和语言。
（5）在“新建登录”窗口的“数据库访问”选项卡，选择允许登录账户访问的数据库和分配给登录账户的数据库角色。
（6）单击“确定”按钮，完成登录模式的创建。 3．设置数据库用户。
登陆用户只有成为数据库用户后才能访问数据库。SQL Server 中的任一数据库中都有两个默认用户：dbo(数据库所有者)和guest(客户用户)。
创建新的数据库用户。要在学生选课数据库中创建一个“User”数据库用户，步骤如下： （1）在“SSMS”中扩展SQL 服务器及数据库文件夹。用鼠标右键点击用户文件夹，出现用户弹出对话菜单，在弹出菜单上选择“新建数据库用户”，会出现新建用户对话框。
（2）在对话框的登陆名中选择一个服务器登陆名，并在下面的用户名郎中填入数据库用户名。
（3）单击“确认”按钮。 4．设置数据库角色。
（1）使用“SSMS”创建数据库角色。
打开“SSMS”，在树形目录中展开指定的数据库节点。
选中该数据库节点的下一级节点“角色”，单击鼠标右键，在弹出的菜单中选择“新建数据库角色”。
在弹出的“数据库角色属性-新建角色”对话框中，输入新建数据库角色的名称，选择角色类型（标准角色），单击“添加”按钮向角色中添加用户
单击“确定”按钮，完成数据库角色的创建。 设置该数据库角色的权限。 （2）在数据库角色中增加和移去角色。
（3）在“SSMS”中，在树形目录中展开到“学生选课”数据库节点下的“角色”节点。 用鼠标右键单击右边细节窗口中的已有的角色，在弹出菜单中选择属性，弹出图3-15所示。
单击“添加”按钮后则出现选择该数据库用户的对话框，选择出要加入角色的用户，单击“确定”按钮，关闭选择数据库用户对话框后，会发现新选的用户名出现在数据库角色属性对话框中。
2）如果要在数据库角色中移走一个用户，在用户栏选中他，然后单击“删除”按钮。 5．设置数据库用户权限。
（1）在“SSMS”中，展开树形目录到“学生选课”数据库-“用户”节点，在右 边细节窗口中找到要修改权限的用户名，用鼠标右键单击该用户名，在弹出菜单中选择“属性”，将弹出用户名属性窗口，如图3-16 所示。
（2）单击“权限”按钮，可以在弹出的对话框中设置当前用户的属性。 （3）在数据库角色成员列表框中，可以将当前用户加入到其他的角色中。 （4）单击“属性”按钮，可以设置当前选定的角色的权限。 （5）单击“确定”按钮，完成数据库用户权限的设置。
图3-15 添加角色对话框 图3-16设置权限对话框 6．数据库备份
（1）数据库的备份
在“SSMS”中，选中对应的表格，单击快捷菜单’所有任务’→‘备份数据’，在窗口中选择备份类型（完全备份、差异备份），添加备份设备（操作系统通常对数据的输入/输出操作是将数据从输入设备得来或输出到输出设备。因此，对存放数据的文件也称为文件设备。因此，添加设备实际上就是对备份文件的存储路径和文件名用一个设备名来表示），然后单击“确定”按钮，SQL SERVER将数据库备份到你设置的设备文件中。
（2）数据表的转储（将数据转换成其他格式文件如DBF，TXT、XLS等）
在“SSMS”中，选中对应的表格，单击快捷菜单’所有任务’ →‘导出数据’，设置数据源（数据源提供驱动器和数据库名），单击“下一步”进入到“目标数据”窗口，选择目标数据驱动程序（如SQL SERVER、Visual FoxPro、EXCEL1997-2000等），设置目标数据存放位置，点击“下一步”，然后根据提示进行操作即可。 7．数据库恢复
（1）在“SSMS”中，选中对应的数据库，单击快捷菜单’所有任务’ →‘恢复数据’，根据提示进行操作。
（2）数据导入
在“SSMS”中，选中对应的表格，单击快捷菜单’所有任务’ →‘导入数据’，设置数据源（数据源提供驱动器和数据文件位置及文件名），单击“下一步”进入到“目标数据”窗口，选择目标数据驱动程序（选择SQL SERVER）和数据库名，点击“下一步”，然后根据提示进行操作即可。
思考题
1．SQL Server 有哪些数据安全性功能？保证数据安全性你觉得还需要注意哪些方面的 问题？
2．SQL Server 中有哪些完整性功能？保证数据完整性还需要注意哪些方面的问题？
实验D010： 数据库设计实验
一、实验名称和性质
所属课程 实验名称 实验学时 实验性质 必做/选做 数据库原理 数据库设计 2 □验证 ✓综合 □设计 □必做 ✓选做 二、实验目的
1．了解数据字典的描述方法。
2．掌握由数据字典进行概念设计的方法。
3．掌握数据库中数据表的规范化程度的判断及分解方法。 三、实验的软硬件环境要求
硬件环境要求：PC机（单机）。
使用的软件名称、版本号以及模块：
Windows XP下的SQL Server 2005（或 SQL Server 2008）。 四、知识准备
1．数据字典中包含：数据项、数据结构、数据流、数据存储、数据操作等。在实际运用中数据字典可能用数据结构、数据流图结合数据项说明等来描述。
2．E-R图的组成：实体、属性、实体间联系。
3．根据E-R图进行数据库逻辑结构设计，将实体、实体的属性和实体之间的联系转化为关系模式：
（1） 一个实体型转换为一个关系模式。
（2） 一个m:n联系转换为一个关系模式，该关系模式中。
–关系的属性：与该联系相连的各实体的码以及联系本身的属性 –关系的码：各实体码的组合
（3）一个1:n联系
若联系上有属性，将该联系转换为一个独立的关系模式，方法与2相同 若联系上无属性，与n端对应的关系模式合并（在n端关系中加入1端关系的码和联系本身的属性）
（4）一个1:1联系可转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。
（5）三个或三个以上实体间的一个多元联系转换为一个关系模式。
关系的属性：与该多元联系相连的各实体的码以及联系本身的属性 关系的码：各实体码的组合
4．函数依赖的概念，根据数据表或数据字典写对应关系模式中的函数依赖； 5．1NF、2NF、3NF等概念；
6．模式分解方法，就是将一个大的关系模式分解为小的关系模式（即1张大的数据表分解为几张小的数据表，使数据表对应的关系模式具有更高的规范化程度）
（1）将关系模式分解为3NF且保持函数依赖的分解方法；
（2）将关系模式分解为3NF且保持函数依赖和无损连接的分解方法 五、实验内容
1．E-R图的绘制。
2．由E-R图进行数据库逻辑结构设计。
3．根据数据字典、数据表写关系模式中的函数依赖集。
4．关系模式规范化程度的判断及模式分解。 六、综合性实验
在Northwind数据库中存储的是一个虚拟的从世界各地进出口食品公司的有关销售数据。 此数据库的数据字典定义如下： 1．Territories（城市）：TerritoryID（城市编号）、TerritoryDescription（城市名）、RegionID（地区编号）。
2．Suppliers（供货厂商）：SupplierID （供货厂商编号） 、CompanyName （厂名）、ContactName （联系人名） 、ContactTitle(联系人职位) 、Address（地址） 、City（城市名） 、Region （地区）、PostalCode（邮政编码） 、Country（国家） 、Phone（电话） 、Fax （传真）、HomePage （主页）。
3．Shippers（托运）：ShipperID（托运人编号）CompanyName（厂名）、Phone （电话）. 4．Region（地区）：RegionID（地区编号）RegionDescription（地区描述）。 5．Products（产品）：ProductID （产品编号）、ProductName （品名）、SupplierID（供货厂商编号）、CategoryID（所属种类号）、QuantityPerUnit（单位数量）、UnitPrice （单价）、UnitsInStock（库存）、UnitsOnOrder （定货数）、ReorderLevel （修订量）、Discontinued(是否进行)。
6．Orders（定单）：OrderID（定单编号） 、CustomerID（顾客编号）、EmployeeID（职员编号）、 OrderDate（定货日期）、RequiredDate（交货日期） 、ShippedDate（载运日期）、ShipVia（经由数）、Freight（运费）、ShipName（船名）、ShipAddress(地址)、ShipCity (城市)、ShipRegion(地区)、PostalCode (邮政编码) 、 ShipCountry(国籍)。
7．Order Details（定单详细信息）：OrderID（定单编号）、ProductID（产品编号）、UnitPrice（单价） 、Quantity（数量）、 Discount（折扣）。
8．EmployeeTerritories(职工所在城市)：EmployeeID(职工编号)、TerritoryID(城市编号)。
9．Employees（职工）：EmployeeID（职工编号）、LastName（姓）、FirstName（名）、Title（头衔）、 TitleOfCourtesy（性别）BirthDate（生日）、HireDate（受聘日期）、Address（地址）、 City（城市）、Region（地区）、PostalCode（邮政编码）、Country（国籍）、HomePhone（住宅电话）、Extension（分机号）、Photo（照片）、Notes（备注）、ReportsTo（直接上级号）、Photopath（职工照片路径）。
10．Customers（顾客） :CustomerID（顾客编号）、CompanyName（公司名）、ContactName（联系人名）、ContactTitle（联系人头衔）、Address（地址）、City（城市）、Region（地区）、PostalCode（邮政编码）、Country（国籍）、Phone（电话）、Fax（传真）。
11．CustomerDemographics（顾客统计）：CustomerTypeID（顾客类编号）、CustomerDesc（描绘）。
12．CustomerCustomerDemo（顾客类）：CustomerID（顾客编号）CustomerTypeID（顾客类编号）。
13．Categories (种类)：CategoryID（类编号）、CategoryName（类名）、Description（详细信息）、Picture（图片）。
根据数据字典，完成：
（1）画出E-R图，进行概念数据库设计。
（2）查看数据表，写出各逻辑数据库模式中的函数依赖集，判断逻辑数据库模式中的各个关系（表）是第几范式，如果没有达到第三范式或BC范式，请进行规范化设计。
设计三： 《数据库原理》课程设计实训指导
一、实训名称和性质
所属课程 实训名称 实训学时 实训性质 必做/选做 二、实训目的
课程设计是学生对课程所学知识的综合运用，它与课堂听讲、上机实验、课外练习、自学研究相辅相成，构成一个完整的课程教学体系。《数据库原理》是一门理论性很强的课程，其中的数据库规范化内容比较抽象难懂，为方便学生对数据库原理的理解，课程中用SQL SERVER数据库管理系统实现对数据库的操作和管理，因此，《数据库原理》也是一门实践性强的课程。本次课程设计通过让学生针对某一个具体的信息系统进行分析设计，让学生掌握数据库设计的规范化流程，同时掌握数据字典、数据流图、E-R图、数据库逻辑结构设计等方法。课程设计是一种综合训练，致力于培养学生进行信息系统需求分析、数据库概念设计、数据库逻辑结构设计等的数据库能力，为今后从事信息系统的分析、设计、开发与应用打下基础。新世纪需要具有丰富科学知识、独立解决实际问题、有创造能力的新型人才，这也是该课程设计的最终目的。
三、实训内容和要求
下面是课程设计的内容选题，每个选题围绕一个信息系统。同学们可以根据自己的实际情况进行选题。
1．图书销售管理系统
调查新华书店图书销售业务，设计的图书销售点系统主要包括进货、退货、销售、统计功能。
（1）进货：根据某种书籍的库存量及销售情况确定进货数量，根据供应商报价选择供应商。输出一份进货单并自动修改库存量，把本次进货的信息添加到进货库中。
（2）退货：顾客把已买的书籍退还给书店。输出一份退货单并自动修改库存量，把本次退货的信息添加到退货库中。
（3）统计：根据销售情况输出统计的报表。一般内容为每月的销售总额、销售总量及排行榜。
（4）销售：输入顾客要买书籍的信息，自动显示此书的库存量，如果可以销售，打印销售单并修改库存，同时把此次销售的有关信息添加到日销售库中。
2．通用工资管理系统
数据库原理 《数据库原理》课程设计 1周 □验证 √□综合 □设计 √□必做 □选做
考察某中小型企业，要求设计一套企业工资管理系统，其中应具有一定的人事档案管理功能。工资管理系统是企业进行管理的不可缺少的一部分，它是建立在人事档案系统之上的，其职能部门是财务处和会计室。通过对职工建立人事档案，根据其考勤情况以及相应的工资级别，算出其相应的工资。为了减少输入帐目时的错误，可以根据职工的考勤、职务、部门和各种税费，自动求出工资。
为了便于企业领导掌握本企业的工资信息在系统中应加入各种查询功能，包括个人信息、职工工资、本企业内某一个月或某一部门的工资情况查询，系统应能输出各类统计报表。
3．报刊订阅管理系统
通过对某企业的报刊订阅业务进行分析、调查，设计该企业的报刊订阅管理系统。主要实现以下功能；
（1）录入功能：录入订阅人员信息、报刊基本信息； （2）订阅功能：订阅人员订阅报刊（并计算出金额）；
（3）查询功能：按人员查询、按报刊查询、按部门查询有关订阅信息，对查询结果能进行预览和打印功能；
（4）统计功能：按报刊统计、按人员统计、按部门统计，对统计结果能进行预览和打印功能。
（5）系统维护。 4．医药销售管理系统
调查从事医药产品的零售、批发等工作的企业，根据其具体情况，设计医药销售管理系统。主要功能包括：
基础信息管理：药品信息、员工信息、客户信息、供应商信息等。 进货管理：入库登记、入库登记查询、入库报表等。 库房管理：库存查询、库存盘点、退货处理、库存报表等。 销售管理：销售登记、销售退货、销售报表及相应的查询等。 财务统计：当日统计、当月统计及相应报表等。 系统维护。
5．电话计费管理系统
对邮电局电话计费业务进行调查，设计的系统要求：
（1）能用关系数据库理论建立几个数据库文件来存储用户信息、收费员信息和收费信息等资料。
（2）具有对各种数据文件装入和修改数据的功能。 （3）能在用户交费同时打印发票。
（4）能通过统计制定未来邮局服务计划方案。 （5）有多种查询和统计功能。
6．宾馆客房管理系统
具体考察本市的宾馆，设计客房管理系统，要求：
（1）具有方便的登记、结帐功能，以及预订客房的功能，能够支持团体登记和团体结帐。
（2）能快速、准确地了解宾馆内的客房状态，以便管理者决策。 （3）提供多种手段查询客人的信息。
（4）具备一定的维护手段，具备有一定权利的操作员在密码的支持下才可以更改房价、房间类型、增减客房。
（5）完善的结账报表系统。 7．学生学籍管理系统
调查所在学校学生处、教务处，设计本校学籍管理系统。要求： （1）建立学生档案，设计学生入学、管理及查询界面。
（2）设计学生各学期、学年成绩输入及查询界面，并打印各项报表。
（3）根据各年度总成绩，查询、输出学生学籍管理方案（优秀、合格、试读、退学）。 （4）毕业管理。 （5）系统维护。 8．车站售票管理系统
考察本市长途汽车站、火车站售票业务，设计车站售票管理系统。要求：
（1）具有方便、快速的售票功能，包括车票的预订和退票功能，能够支持团体的预订票和退票。
（2）能准确地了解售票情况，提供多种查询和统计功能。如：车次的查询、时刻表的查询。
（3）能按情况所需实现对车次的更改、票价的变动及调度功能。 （4）完善的报表系统。 （5）具备一定的维护功能。 9．汽车销售管理系统
调查本地从事汽车销售的企业，根据该企业的具体情况，设计用于汽车销售管理系统。主要功能有：
（1）基础信息管理：厂商信息、车型信息和客户信息等。 （2）进货管理：车辆采购、车辆入库。 （3）销售管理：车辆销售、收益统计。
（4）仓库管理：库存车辆、仓库明细、进销存统计。 （5）系统维护：操作员管理、权限设置等。 10．仓储物资管理系统
经过调查，对仓库管理的业务流程进行分析。库存的变化通常是通过入库、出库操作来进行。系统对每个入库操作均要求用户填写入库单，对每个出库操作均要求用户填写出库单。在出入库操作同时可以进行增加、删除和修改等操作。用户可以随时进行各种查询、统计、报表打印、账目核对等工作。另外，也可以用图表形式来反映查询结果。
11．企业人事管理系统
调查本地的企业，根据企业的具体情况，设计企业人事管理系统。主要功能有： （1）人事档案管理：户口状况、政治面貌、生理状况、合同管理等。 （2）考勤加班出差管理。
（3）人事变动：新进员工登记、员工离职登记、人事变更记录。 （4）考核奖惩：奖惩原则、奖惩方案。 （5）员工培训:培训项目、培训考核。 （6）系统维护：操作员管理、权限设置等。 12．选修课管理系统
调查学校教务处，设计用于管理全校学生选修课活动的系统。主要功能有： （1）全校选修计划课程管理； （2）全校选修开课课程管理； （3）全校学生选课管理； （4）全校选修课成绩管理； （5）打印报表。 13．基于WEB的评教系统
调查院教学管理人员，设计一个期中、期末对授课教师进行教学评价活动的网上教学评价系统。要求：
（1） 能对各课程的授课教师进行教学评价； （2） 学生能进行该学期开课课程的教学评价； （3） 对某教师的某课程进行评价统计； （4） 提供教学评价的查询功能； 四、实训的软硬件环境要求
硬件环境要求： PC机（单机）。
使用的软件名称、版本号以及模块：Windows环境下的SQL Server2005等。 五、实训的基本理论
1．需求分析中数据的描述—数据字典； 2．概念设计中E-R图的表示；
3．范式和模式分解等数据库规范化理论；
4．信息系统的数据库规范化设计步骤。 六、实训组织和安排 1．实训组织：
根据任务不同，可以让学生进行自主进行课程设计或与同学1组来完成课题要求。同学选题和分组由学生自行选择。自主同学自己负责，多名同学的分组选择组长并由组长进行内容分配和协调。 2．实训安排：
实训的整个过程可分为三个阶段，第一阶段是为分组、选题、任务分配及查阅资料阶段，所需时间是二天；第二阶段是数据字典、E-R图、数据库逻辑结构设计和数据库实施，所需时间是三天；第三阶段是实训结果的检查阶段，指导教师根据每个学生所完成的情况进行问题的答辩，所需时间是一天。 七、注意事项
在整个实训过程中，每个学生都必须认真按照任务分工和具体要求独立完成，不得抄袭和请人代替完成。每组最后只提交一个程序，程序中设计一个菜单，可选择其中的任一个应用问题执行，这就要求每组最后又要通过团队的合作、协调来完成整个实训内容。 八、实训结果提交方式
在程序编写完成并能得出正确运行结果后，由指导老师师组织学生进行问题的答辩，答辩通过后，最后由学生每人编写一份课程设计实训报告。根据选择的题目不同有不同的要求。报告应包含如下七个方面的内容： 1．问题描述
根据你所选做题目，写出其问题的文字描述。 2．问题分析
根据自身对课程的掌握程度，充分分析和理解问题的设计要求，给出较为明确、简洁的设计思路和解决方法。 3．信息系统的需求分析
根据信息系统的需求分析，分析信息系统中有哪些数据，用数据字典来描述。对信息系统中的数据流程用数据流图来表示。 4．概念设计
根据数据字典进行数据库概念设计，描述信息系统中的数据实体及实体之间有何联系，用E-R图描述。 5．数据库逻辑结构设计
根据概念设计，进行数据库逻辑结构设计，即信息系统中有哪些表，表中的属性及数据类型和表的主码、外码。分析你的数据表是否达到3NF。 6．数据库实施
根据数据库逻辑结构设计，创建数据库和数据表，并根据数据完整性约束进行存储过程
和触发器的设计。并在数据表中输入一定量的数据记录后，通过对数据操作验证数据库设计是否合理。
7．写出使用说明，实习体会心得等；
最后将以上文字材料并做好封面装订成册提交给指导老师。
九、实训考核方式和标准 1．考核方式
本实验课程成绩由两部分构成，一部分是为实验过程成绩（包括实验预习、实验出勤、运行结果和答辩等），占总成绩的60%，另一部分是课程设计实验报告成绩，占总成绩的40%。 2．考试标准 采用五级评分制。 评分等级 评分标准 A B C D E
出全勤，能按时完成各项任务；程序编写达到要求，回答问题正确；实训报告内容符合要求、条理清楚。 出全勤，能按时完成各项任务；程序编写基本达到要求或回答问题基本正确或实训报告内容基本符合要求、条理比较清楚。 出全勤，基本能按时完成各项任务；程序编写基本达到要求，回答问题基本正确或实训报告内容基本符合要求。 出全勤，基本能按时完成各项任务；程序编写基本达到要求，回答问题不够正确；实训报告内容基本符合要求、条理不够清楚。 不能按时完成各项任务；程序编写基本达到要求，回答问题不够正确；实训报告内容书写不符合要求。
因篇幅问题不能全部显示，请点此查看更多更全内容