《数据库原理及应用》实验指导书

1、“数据库原理与应用”实验指导书北京信息科技大学计算机学院数据库原理与应用实验指导书实验一创建数据库及关系表1实验二数据操作语句3实验三 触发器5实验四视图7实验五事务9实验六 安全管理11实验七数据库设计1214实验一创建数据库及关系表一、实验目的1. 掌握SQL Server数据库管理系统的使用，能够在该环境中通过图形化方法创建数据库；2. 掌握建立关系表的SQL语句，掌握声明完整性的定义方法及作用；3. 掌握修改表结构SQL的语句。二、实验要求 1. 了解SQL Server数据库数据库管理系统的使用，会使用图形化工具一个新的创建数据库。2. 编写建立关系表及主键、外键、默认值、唯一值、限

2、制列取值范围约束的T-SQL语句，并执行这些语句，在新建数据库中建立符合要求的关系表。3. 编写并执行修改表结构的T-SQL语句。4. 查看每一步的执行结果。三、实验内容和步骤注：请按顺序执行下述各题，前步执行成功后再执行后续步骤。1创建符合如下要求的数据库：数据库的名字为：学生数据库数据文件的逻辑文件名为：Students_dat，存放在D:Test目录下（若D:盘中无此子目录，可先建立此目录，然后再创建数据库。）；文件的初始大小为：5MB； 增长方式为自动增长，每次增加1MB。日志文件的逻辑文件名字为：Students_log，也存放在D:Test目录下；日志文件的初始大小为：2MB；日志

3、文件的增长方式为自动增长，每次增加10%。2在已建立的“学生数据库”中，写出创建满足下述条件的四张表的SQL语句，将这些语句保存到文件中，并查看语句的执行结果。Student表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Ssex性别普通编码定长字符串，长度为2取值范围：男，女Sage年龄微整型（tinyint）取值范围：15-45Sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期小日期时间默认为系统当前日期Course表结构列名说明数据类

4、型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型SC表结构列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型Teacher表结构列名说明数据类型约束Tno教师号普通编码定长字符串，长度为8非空Tname教师名普通编码定长字符串，长度为10非空Salary工资定点小数，小数点前4位，小数点后2位3 写出实现如下操作的SQL语句，并查看执行结果：（1） 在Te

5、acher表中添加一个新的职称列：列名为：Title，类型为nchar(4)。（2） 为Teacher表的Title列增加取值范围约束，取值范围为：教授，副教授，讲师。（3） 将Course表中Credit列的类型改为：tinyint。（4） 删除Student表的Sid和Sdate列。（5） 为Teacher表添加主键约束，其主键列为：Tno。4 执行下列数据插入语句，查看哪些语句能成功，哪些不能成功？请简单说明理由。对执行成功的语句，请查看表中插入的数据。（1） INSERT INTO Teacher VALUES(T01, 张一, 6000, 教授)（2） INSERT INTO Tea

6、cher VALUES(T01, 张二, 4000, 讲师)（3） INSERT INTO Teacher VALUES(T02, 张二, 4000, 工程师)（4） INSERT INTO Teacher VALUES(T03, 张三, 4000, 讲师)（5） INSERT INTO SC VALUES(S001, C01, 88)（6） INSERT INTO Student VALUES(S001, 王一, 男, 19, 信息系, 1234567890, 2014-9-1)（7） INSERT INTO Student VALUES(S002, 王二, 男, 19, 信息系, 1234

7、567890, 2014-9-1)（8） INSERT INTO Student(Sno, Sname, Ssex, Sage, Sid) VALUES(S003, 王三, 男, 19, 1234567891)（9） INSERT INTO SC VALUES(S001, C01, 88)实验二数据操作语句一、 实验目的理解并掌握数据操作语句的功能。 二、实验要求 1根据实验一建立的数据库和关系表，执行脚本导入实验数据；2编写实现数据操作功能的SQL语句。三、实验内容及步骤0准备工作（1） 在SSMS工具中，选中“学生数据库”，执行下述语句，删除表中已有数据。DELETE FROM SCDEL

8、ETE FROM Student（2） 打开并执行教师提供的“数据插入语句.sql”文件，将数据插入到“学生数据库”的三张表中。写出实现下述操作的SQL语句，执行这些语句，观察并记录下执行结果。1. 查询计算机系学生姓名和年龄。2. 查询没有考试成绩的学生的学号和相应课程号。3. 查询计算机系和信息系学生的姓名。4. 查询成绩在80-90之间的学生学号、课程号和成绩，结果按成绩降序排序。5. 统计每个学生的选课门数，并按选课门数的递增顺序显示结果。6. 查询总成绩超过200的学生学号和总成绩。7. 查询选课人数超过3人的课程，列出课程号和选课人数。8. 查询计算机系年龄最大的三个学生的姓名和年

9、龄。9. 查询计算机系的学生姓名、选的课程号和考试成绩10. 查询成绩80分以上的学生姓名、选的课程名和成绩，并将结果按成绩的降序排列。11. 查询平均成绩大于等于80且选课门数超过2门的学生学号、选的课程门数和平均成绩。12. 列出“VB”考试成绩最高的前三名学生的学号、姓名、所在系和VB考试成绩。13. 查询全体学生的选课情况，包括未选课的学生，列出学生的学号、姓名、课程号和考试成绩。14. 查询没人选的课程，列出课程号和课程名。15. 查询没人选的课程，列出课程名、开课学期和学分。16. 查询考试成绩高于全体学生的总平均成绩的学生的姓名、考试的课程名和考试成绩。17. 查询平均成绩大于8

10、0的课程名和学分。18. 查询平均成绩最高的学生的姓名和所在系。19. 查询VB考试成绩高于VB平均成绩的学生的姓名。20. 统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：如果此门课程的选课人数超过100人，则显示“人多”；如果此门课程的选课人数在40100，则显示“一般”；如果此门课程的选课人数在140，则显示“人少”；如果此门课程没有人选，则显示“无人选”。21. 将计算机系学生的年龄加1。22. 将VB课程所有不及格的成绩改为及格。分别用子查询和多表连接形式实现。23. 将计算机系所有“计算机文化学”的成绩加10分。24. 修改

11、“VB”课程的考试成绩，如果是计算机系的学生，则增加4分；如果是信息系的学生则增加8分，其他系的学生增加10分。25. 删除成绩小于50的学生的修课记录。26. 删除信息系成绩小于50的学生的修课纪录。27. 删除没人选的课程。28. 删除“VB”考试成绩最低学生的VB修课记录。实验三 触发器一、 实验目的理解触发器的含义和作用。 二、实验要求 1. 掌握后触发型触发器的建立和删除方法。2. 理解后触发型触发器对数据完整性约束的作用。3. 理解INSERTED和DELETED逻辑工作表的作用。三、实验内容及步骤本实验全部在实验一建立的学生数据库及Student、Course和SC表上进行。1.

12、 阅读并测试触发器。（1） 阅读并执行下列创建触发器的代码，说明该触发器的作用。CREATE TRIGGER tri_1 ON SC FOR INSERT,UPDATEAS IF EXISTS( SELECT * FROM Inserted WHERE Grade NOT BETWEEN 0 AND 100 )ROLLBACK（2） 按顺序执行下列语句，查看并说明每个操作是否能执行成功。I INSERT INTO SC VALUES(0811101,C004,190)II INSERT INTO SC VALUES(0811101,C004,90)III UPDATE SC SET Grade

13、 = Grade + 20 WHERE Sno = 0811101 AND Cno = C0042. 编写限制学分在1-20的后触发型触发器，并编写相应数据操作语句测试所实现触发器的作用。3. 阅读并测试触发器。（1） 阅读并执行下列创建触发器的代码，说明该触发器的作用，并体会Inserted和Deleted表的作用。CREATE TRIGGER tri_3 ON SC FOR UPDATEAS IF EXISTS( SELECT * FROM Inserted i JOIN Deleted d ON i.Sno = d.Sno AND i.Cno = d.Cno WHERE i.Grade

14、= 60 AND d.Grade 60 )ROLLBACK（2） 按顺序执行下列语句。I SELECT * FROM SC WHERE Sno = 0821103 AND Cno = C001记下该学生的Grade值。II UPDATE SC SET Grade = 60 WHERE Sno = 0821103 AND Cno = C001执行是否成功？为什么？III SELECT * FROM SC WHERE Sno = 0821103 AND Cno = C001这次执行该学生的Grade值是多少？（3） 按顺序执行下列语句。I SELECT * FROM SC WHERE Sno =

15、0831101 AND Cno = C001记下该学生的Grade值。II UPDATE SC SET Grade = 45 WHERE Sno = 0831101 AND Cno = C001执行是否成功？为什么？III SELECT * FROM SC WHERE Sno = 0831101 AND Cno = C001这次执行该学生的Grade值是多少？4. 阅读并测试触发器。（1） 阅读并执行下列创建触发器的代码，说明该触发器的作用，并说明该触发器中为何使用的是Course表而不是Inserted表或Deleted表。CREATE TRIGGER tri_4 ON Course FOR

16、 INSERT,DELETEAS IF ( SELECT COUNT(*) FROM Course ) NOT BETWEEN 8 AND 9 ROLLBACK（2） 按顺序执行下列语句。I SELECT COUNT(*) FROM Course记下课程门数值。II DELETE FROM Course WHERE Cno = C008执行是否成功？为什么？III INSERT INTO Course VALUES(C009, 新课程1, 3, 3 )执行是否成功？为什么？IV INSERT INTO Course VALUES(C010, 新课程2, 2, 5 )执行是否成功？为什么？5.

17、编写限制每个学生的修课总学分在5-15之间的后触发器型触发器，并编写相应数据操作语句测试所实现触发器的作用。（注：只考虑选课，不考虑考试成绩是否及格，即只要学生选了课，就认为他有这个学分）6删除tri_1和tri_3触发器。实验四视图一、 实验目的理解视图的含义和作用。 二、实验要求 1. 掌握建立和修改视图的方法。2. 掌握通过视图进行数据查询的操作。3. 理解通过视图修改数据的限制。三、实验内容及步骤本实验全部在实验一建立的学生数据库及Student、Course和SC表上进行。1. 建立视图。写出建立满足如下要求的视图的SQL语句，并执行之。（1） 视图V1：查询计算机系学生的学号、姓名

18、和年龄。（2） 视图V2：查询学生的学号、姓名、所在系、选修的课程名和考试成绩。（3） 视图V3：统计每个系的学生人数，列出系名和人数。（4） 视图V4：统计每个学生的选课门数，列出学号和选课门数，视图的列名改为中文。2. 利用视图查询数据。利用第1题建立的视图及Student、Course、SC表，完成如下查询，观察并记录下执行结果。（1） 查询计算机系考试成绩大于等于80的学生的姓名、课程名和成绩。（2） 查询学生人数超过3人的系，列出系名和该系学生人数。（3） 查询选课门数超过3门的学生的学号和选课门数。（4） 查询计算机系选课门数超过3门的学生的学号和选课门数。（5） 查询计算机系选课

19、门数超过3门的学生的姓名、选的课程名和选课门数。3. 通过视图修改数据。（1） 按顺序执行下列语句，并在最后说明通过该例子你得出了哪些结论。I INSERT INTO V1 VALUES(S101, 新学生1,18)SELECT * FROM Student是否有新插入的学生？为什么？若有，该学生的所在系、性别列的值分别是什么？ II SELECT * FROM V1是否有新插入的学生？为什么？III UPDATE Student SET Sdept = 计算机系 WHERE Sno = S101SELECT * FROM V1是否有新插入的学生？为什么？IV UPDATE Student S

20、ET Sdept = 其他系 WHERE Sno = S101SELECT * FROM V1是否还有新插入的学生？为什么？（2） 执行下列语句，能否成功？为什么？INSERT INTO V2 VALUES(S102, 新学生2, 计算机系, 高等数学,90)（3） 按顺序执行下列语句，并在最后说明通过该例子你得出了哪些结论。I SELECT * FROM V1 SELECT * FROM Student WHERE Sdept = 计算机系记下两个查询的结果及行数。II DELETE FROM V1 WHERE Sno = 0811103能否删除成功？III SELECT * FROM V1

21、该查询结果中还有0811103号学生吗？为什么？ SELECT * FROM Student WHERE Sdept = 计算机系该查询结果中有0811103号学生吗？为什么？实验五事务一、实验目的1. 理解事务的概念。2. 理解事务的原子性和隔离性。二、实验要求 1 了解事务的定义方法。2 通过验证实验理解事务的原子性和隔离性。三、实验内容及步骤连接到数据库服务器，并选用实验一建立的“学生数据库”及Student、Course、SC表。1. 按顺序执行如下语句，每次执行完成后，观察执行结果：（1） SELECT * FROM SC WHERE Grade 60（2） SELECT * FRO

22、M Student WHERE Sdept = 计算机系（3） 逐行执行下列语句，观察每个查询语句的执行结果。BEGIN TRAN MyTran1 DELETE SC WHERE Grade 60 SELECT * FROM SC WHERE Grade 60UPDATE Student SET Sage = Sage + 100 WHERE Sdept = 计算机系 SELECT * FROM Student WHERE Sdept = 计算机系ROLLBACK MyTran1（4） SELECT * FROM SC WHERE Grade 60 （5） SELECT * FROM Stud

23、ent WHERE Sdept = 计算机系 问： 三个关于Student表和SC表的SELECT语句的结果一样吗？请说明理由？ 该实验体现了事务的哪个特性？2. 将第1题步骤（3）中的ROLLBACK改为COMMIT呢？重新执行步骤（3）（5），结果又是怎样？3. 按顺序执行如下语句。（1） 在SSMS的某个查询窗口中执行下列语句：I SELECT * FROM SC 观察结果数据，记下数据行数II 批量执行下列语句，观察最终查询语句的执行结果。BEGIN TRAN MyTran2 DELETE SC WHERE Grade IS NULL SELECT * FROM SC （2） 建立一个

24、新的查询窗口，并在此窗口中执行如下语句： SELECT * FROM SC是否能够执行成功？为什么？（3） 返回到（1）的查询窗口，执行如下语句： COMMIT MyTran2（4） 返回到步骤（2）的查询窗口，查看其SELECT语句是否已执行成功，若成功，查看执行结果。问：该实验体现了事务的哪个特性？实验六 安全管理一、实验目的1. 掌握数据库管理系统中安全管理的作用。2. 掌握在SQL Server环境中实现安全管理的操作。二、实验要求 1. 能够在SQL Server环境中建立SQL Server身份验证的登录账户。2. 能够建立数据库用户。3. 掌握授权的SQL语句。4. 准确理解安全

25、管理的含义。三、实验内容及步骤1. 在SQL Server的SSMS工具中，以系统管理员身份建立SQL Server认证的登录帐户：u1、u2、u3，并将u1、u2、u3映射为实验一建立的“学生数据库”的用户。2. 用u1建立一个新的连接，选中“学生数据库”并执行下述语句，能否成功？为什么？SELECT * FROM Course3. 用系统管理员建立一个连接，编写实现如下功能的SQL语句。（1） 授予u1具有对course和SC的查询权。（2） 授予u2具有对course的插入权限。4. 用u2建立一个新的连接，选中“学生数据库”，完成下列操作。（1） 执行下述语句，能否成功？为什么？INSERT INTO course VALUES(C011，数据仓库技术，2，7)（2） 执行下述语句，能否成功？为什么？SELECT * FROM Course5. 回到u1建立的连接中，完成下列操作。（1） 执行下述语句，能否成功？为什么？SELECT * FROM course（2） 执行下述语句，能否成功？为什么？INSERT INTO course VALUES(C012，软件工程，4，6)6.* 按顺序执行下列