数据库系统实验报告广东工业大学.doc

1、1数据库系统实验报告学 院 计算机学院 专 业 计算机科学与技术 班 级 级 班 学 号 姓 名 指导教师 （ 2016 年 12 月） 2实验_一_题目 数据库及基本表的建立 实验_二_题目 查询数据库_ 实验_三_题目 创建和使用视图、索引、存储过程 实验_四 _题目 小型数据库规划设计 实验平台：SQL Sever 20053实验题目 实验一 数据库及基本表的建立 一、实验目的1、掌握 SQL SERVER 的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别 使用 SQL 语句、企业管理器（Enterprise Manager）创建数据库；2、使用

2、SQL 语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库 SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student 表( 学生信息表)、course 表（课程信息表) 、teacher 表（教师信息表)、student _course 表（学生选课成绩表)、teacher_course 表（教师上课课

3、表）等。1、创建数据库：确定数据库名称；数据库用于学生管理，命名为 SC确定数据库的位置；要求：数据文件和日志文件分别存储在 E 盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为 30MB，日志文件的初始大小为 3MB。确定数据库的增长；根据实际情况，确定数据文件按 20%增长，日志文件按 1MB 增长。（1） 、 利用查询分析器 （Query Analyzer ） ，使用 SQL 语句指定参数创建数据库；实现代码及截图：SQL 语句CREATE DATABASE SC_3114006159ON PRIMARY(NAME=SC_3114006159_DATA,FILE

4、NAME=E:3114006159SC_3114006159.mdf,SIZE=30,FILEGROWTH=20%)LOG ON(NAME=SC_3114006159_LOG,FILENAME=E:3114006159SC_3114006159.ldf,SIZE=3,FILEGROWTH=1)查询分析器执行情况：SQL 语句及执行结果截图显示2、创建基本表利用查询分析器，使用 SQL 语句方式创建方式将下面各表建立到教学管理数据库中。(1)创建student表字段名 代码 类型 约束4学号 s_no char(8) 主键姓名 sname char(8) 非空性别 sex char(2) 出生日

5、期 sbirthday Smalldatetime学生所在院系编号 dno char(6) 外键专业代码 spno char(8) 外键班级编码 class_no char(4)(2)创建Course表字段名 代码 类型 约束课程编号 cno char(10) 主键课程名称 cname char(20) 非空专业代码 spno char(8) 外键课程类型编号 ctno tinyint理论学时 lecture tinyint实验学时 experiment tinyint开课学期 semester tinyint课程学分 credit tinyint（3）创建student _course表字段

6、名 代码 类型 约束学号 s_no char(8) 主键,与student表中s_no 外键关联,级联删除上课编号 tcid smallint 主键学生成绩 score tinyint(4)创建teacher表 字段名 代码 类型 约束教师编号 t_no char(8) 主键教师姓名 t_name char(8) 非空性别 t_sex char(2)出生日期 t_birthday smalldatetime教师所在院系编号 dno char(6) 外键职称 tech_title char(10)(5)创建系部表(department) 字段名 代码 类型 约束院系编号 dno char(6)

7、主键院系名称 dept_name char(20) 非空院系负责人 header char(8)(6)创建专业信息表(speciality) 字段名 代码 类型 约束专业代码 spno char(8) 主键院系编号 dno char(6) 外键,非空专业名称 spname char(20) 非空(7)创建teacher_course表字段名 代码 类型 约束上课编号 tcid smallint 主键5教师编号 t_no char(8) 外键专业代码 spno char(8) 外键班级编码 class_no char(4)课程编号 cno char(10) 非空,外键学期 semester Ch

8、ar(6)学年 schoolyear Char(10)(8)创建班级表(class) 3、 查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示字段名 代码 类型 约束专业代码 spno char(8) 主键，与 speciality 表中spno 外键关联,班级编码 class_no char(4) 主键,班负责人 header char(8)6class_3114006159spnoclass_noheadercourse_3114006159cnocnamespnoctnolectureexperimentsemestercreditdeparment_3114006159

9、dnodept_nameheaderspeciality_3114006159spnodnospnamestudent_3114006159s_nosnamesexsbirthdaydnospnoclass_nostudent_course_3114006159s_notcidscoreteacher_3114006159t_not_namet_sext_birthdaydnotech_titleteacher_course_3114006159tcidt_nospnoclass_nocnosemesterschoolyear74、创建各表的实现代码及截图：SQL 语句USE SC_31140

10、06159GOCREATE TABLE student_3114006159(s_no char(8)PRIMARY KEY,sname char(8) NOT NULL,sex char(2),sbirthday smalldatetime,dno char(6),spno char(8),class_no char(4);CREATE TABLE course_3114006159(cno char(10) PRIMARY KEY,cname char(20) NOT NULL,spno char(8),ctno tinyint,lecture tinyint,experiment tin

11、yint,semester tinyint,credit tinyint);CREATE TABLE student_course_3114006159(s_no char(8),tcid smallint,score tinyint,PRIMARY KEY(s_no,tcid);CREATE TABLE teacher_3114006159(t_no char(8) PRIMARY KEY,t_name char(8) NOT NULL,t_sex char(2),t_birthday smalldatetime,dno char(6),tech_title char(10);CREATE

12、TABLE deparment_3114006159(dno char(6) PRIMARY KEY,dept_name char(20)NOT NULL,查询分析器执行情况：SQL 语句及执行结果截图显示9header char(8);CREATE TABLE speciality_3114006159(spno char(8) PRIMARY KEY,dno char(6) NOT NULL,spname char(20) NOT NULL);CREATE TABLE teacher_course_3114006159(tcid smallint PRIMARY KEY,t_no char

13、(8),spno char(8),class_no char(4),cno char(10) NOT NULL,semester char(6),schoolyear char(10);CREATE TABLE class_3114006159(spno char(8),class_no char(4),header char(8),PRIMARY KEY(spno,class_no);ALTER TABLE student_3114006159 ADD FOREIGN KEY (dno) REFERENCES deparment_3114006159(dno);ALTER TABLE stu

14、dent_3114006159 ADD FOREIGN KEY (spno) REFERENCES speciality_3114006159(spno);ALTER TABLE course_3114006159 ADD FOREIGN KEY (spno) REFERENCES speciality_3114006159(spno);ALTER TABLE student_course_3114006159 ADD FOREIGN KEY (s_no) REFERENCES student_3114006159(s_no);ALTER TABLE teacher_3114006159 AD

15、D FOREIGN KEY (dno) REFERENCES 10deparment_3114006159(dno);ALTER TABLE speciality_3114006159ADD FOREIGN KEY (dno) REFERENCES deparment_3114006159 (dno);ALTER TABLE teacher_course_3114006159 ADD FOREIGN KEY (t_no) REFERENCES teacher_3114006159(t_no);ALTER TABLE teacher_course_3114006159 ADD FOREIGN K

16、EY (spno) REFERENCES speciality_3114006159(spno);ALTER TABLE teacher_course_3114006159 ADD FOREIGN KEY (cno) REFERENCES course_3114006159(cno);ALTER TABLE class_3114006159 ADD FOREIGN KEY (spno) REFERENCES speciality_3114006159(spno);5、利用查询分析器修改上述各表。(1)、用 INSERT 语句向各个表 中插入数据录入 5 条记录。录入时注意体会外键约束。实现代码

17、及截图：查询分析器执行情况：SQL 语句及执行结果显示insert into deparment_3114006159values(659801,计算机, 周杰伦 );insert into deparment_3114006159values(659802,机电, 陈奕迅 );insert into deparment_3114006159values(659803,外国语, 章子怡 );insert into deparment_3114006159values(659804,土木, 梁朝伟 );insert into deparment_3114006159values(659805,环

18、境, 张国荣 );insert into teacher_3114006159实验结果截图显示（插入数据的表格）12values(65980101,刘德华, 男,1975-03-25,659801,高级);insert into teacher_3114006159values(65980201,周润发, 男,1964-09-24,659802,高级);insert into teacher_3114006159values(65980301,张学友, 男,1956-06-15,659803,高级);insert into teacher_3114006159values(65980401,张

19、曼玉, 女,1965-04-21,659804,高级);insert into teacher_3114006159values(65980501,陈坤, 男,1988-03-02,659805,高级);insert into speciality_3114006159values(65980110,659801,计算机科学与技术);insert into speciality_3114006159values(65980210,659802,自动化);insert into speciality_3114006159values(65980310,659803,商务英语);insert in

20、to speciality_3114006159values(65980410,659804,土木工程);insert into speciality_3114006159values(65980510,659805,环境工程);insert into class_3114006159values(65980110,1401,刘德华 );insert into class_3114006159values(65980210,1402,周润发 );insert into class_3114006159values(65980310,1403,张学友 );insert into class_31

21、14006159values(65980410,1404,张曼玉 );insert into class_3114006159values(65980510,1405,陈坤 );13insert into course_3114006159values(6598011001,数据库,65980110,1,48,12,1,2);insert into course_3114006159values(6598021001,制图,65980210,2,56,6,2,2);insert into course_3114006159values(6598031001,口语,65980310,3,48,0

22、,1,4);insert into course_3114006159values(6598041001,建筑学,65980410,4,48,12,1,2);insert into course_3114006159values(6598051001,基础化学,65980510,5,48,12,1,2);insert into teacher_course_3114006159values(1,65980101,65980110,1401,6598011001,一学期 ,第二学年);insert into teacher_course_3114006159values(2,65980201,6

23、5980210,1402,6598021001,一学期 ,第二学年);insert into teacher_course_3114006159values(3,65980301,65980310,1403,6598031001,一学期 ,第二学年);insert into teacher_course_3114006159values(4,65980401,65980410,1404,6598041001,一学期 ,第二学年);insert into teacher_course_3114006159values(5,65980501,65980510,1405,6598051001,一学期

24、 ,第二学年);14insert into student_3114006159values(31140001,王小波, 男,1997-01-03,659801,65980110,1401);insert into student_3114006159values(31140002,韩寒, 男,1992-03-23,659802,65980210,1402);insert into student_3114006159values(31140003,莫言, 男,1997-06-15,659803,65980310,1403);insert into student_3114006159valu

25、es(31140004,三毛, 女,1994-05-30,659804,65980410,1404);insert into student_3114006159values(31140005,郭敬明, 男,1995-12-03,659805,65980510,1405);insert into student_course_3114006159values(31140001,1,96);insert into student_course_3114006159values(31140002,3,67);insert into student_course_3114006159values(3

26、1140003,2,91);insert into student_course_3114006159values(31140004,4,89);insert into student_course_3114006159values(31140005,5,96);共 8 组（共 8 个表）(2)、用 UPDATE 语句更改 student 表中数据；实现代码：use SC_3114006159update student_3114006159set sname=村上春树15where sname=莫言student 表更改前的内容截图显示查询分析 student 表更改后的内容截图显示(3)、

27、用 DELETE 语句删除 student 表中数据；实现代码：use SC_3114006159delete from student_course_3114006159where s_no in(select s_nofrom student_3114006159where sname=郭敬明);deletefrom student_3114006159 where sname=郭敬明student 表更改前的内容截图显示查询分析 student 表更改后的内容截图显示五、实验中出现的问题及解决方案16在插入数据时没注意外键约束，结果导致插入失败，于是根据数据表关系图先插入没有外键约束的表

28、，再根据外键约束关系按照一定顺序插入表。在删除 student 的元组时，因为其主键被 student_course 表外键所参照，所以无法删除，于是先删除student_course 表中的元组，再删除 student 中的元组。六、思考题1、 说明数据库中的表和数据文件的关系。答：表存储于数据文件中，一个数据文件可以存储多个表。2、 数据库中的日志文件能否单独修改?不能，SQL Server 数据库都有 log 日志文件，log 日志文件记录用户对数据库修改的操作，只有数据库发生更改时，日志文件才会发生更改，不能单独更改。3、 附加练习题.(代码以及运行结果采用截图显示 )(1) 将计算机

29、专业所有学生的数据库原理的成绩增加 10 分代码以及运行结果：(2) 删除计算机专业所有学生的数据库原理的选修信息代码以及运行结果：17(3) 为 speciality 的 spname 添加唯一约束代码以及运行结果:(4) 为 student_course 的 score 设置检查约束代码以及运行结果:18实验题目 实验二 查询数据库 一、实验目的熟悉 SQL 语句的基本使用方法，学习如何编写 SQL 语句来实现查询1、掌握基本的 SELECT 查询及其相关子句的使用；2、掌握复杂的 SELECT 查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用 SQL 查询分析器查询数

30、据，练习查询语句的使用，掌握 SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试要求用 SELECT 完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL

31、语句及执行结果显示use SC_3114006159select s_no,sname,sex,sbirthdayfrom student_3114006159查询结果截图显示（2）、 查询全体学生的 学号、姓名和年龄实现代码及查询结果截图：use SC_3114006159select s_no,sname,sex,year(getdate()-year(sbirthday)from student_311400615919（3）、查询 院系编号为 510000（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：use SC_3114006159select s_

32、no,sname,sex,sbirthdayfrom student_3114006159where dno=659801（4）、查询 “计算机科学与技术 ”专业（专业代码为080605）并且班级代码为0201的学生的学号、姓名和出生日期。实现代码及查询结果截图：use SC_3114006159select s_no,sname,sbirthdayfrom student_3114006159where spno=65980110 and class_no=1401（5） 、查询在1983/01/10以后出生的计算机学院（院系编号为510000）学生的学号、姓名、性别和出身年月日。实现代码及

33、查询结果截图：use SC_3114006159select s_no,sname,sex,sbirthdayfrom student_3114006159where dno=659801and sbirthday1983-01-10（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：use SC_3114006159select s_no,sname,sex,sbirthdayfrom student_3114006159order by sbirthday连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结

34、果截图：20use SC_3114006159select s_no,sname,sex,dept_name,spnamefrom student_3114006159,deparment_3114006159,speciality_3114006159where student_3114006159.dno=deparment_3114006159.dno anddeparment_3114006159.dno=speciality_3114006159.dno（2）、 查询选修了课程 1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：use SC_311400

35、6159select student_3114006159.s_no,sname,spname,scorefrom student_3114006159,student_course_3114006159,speciality_3114006159where student_3114006159.s_no=student_course_3114006159.s_no and student_3114006159.spno=speciality_3114006159.spno and tcid=1（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：u

36、se SC_3114006159select student_3114006159.s_no,sname,cnamefrom student_3114006159,student_course_3114006159,course_3114006159where student_3114006159.s_no=student_course_3114006159.s_no and student_course_3114006159.tcid=course_3114006159.ctno and score20查询 Products 中单价（UnitPrice）最高的 Products 的资料；实现

37、代码及查询结果截图：use Northwindselect *from Productswhere UnitPrice=all(select UnitPricefrom Products);25五、实验中出现的问题及解决方案六、思考题1、 连接查询分哪几类？各有什么特点？答：连接查询可分为：1. 等值连接与非等值连接：当 where 子句中的连接运算符为 =时，称为等值连接，使用其它运算符为非等值连接。2. 自然连接：在等值连接中把目标列中重复的属性列去掉则为自然连接。3. 自身连接：一个表与自身连接，称为表的自身连接。4. 外连接：两表连接时不存在公共属性上值相等的元组也保存在结果关系中，而

38、在其他属性上填空值（NULL） ，那么连接就叫外连接。5. 多表连接：两个表以上连接为多表连接。2、 进行连接查询时应注意哪些问题？答：连接查询时应注意选择正确的连接谓词。26实验题目 实验三 创建和使用视图、索引、存储过程 一、实验目的1、理解视图 、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；3、掌握创建 视图、索引、存储过程的 SQL 语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows

39、 ；2数据库管理系统：SQL Server 2005；三、实验方法、步骤及结果测试（一）、视图1、建立 “计算机学院 ”的学生基本情况视图 ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行 ies_student_view 视图并观察结果。3、建立课程 1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图 ies_student_view，使该视图包括所有学生的学号、姓名、性别、

40、出身年月、学院名称、专业名称。7、执行 ies_student_view 视图并观察结果。8、删除视图 ies_student_view。并查询结果。记录上述实验过程并截图说明。查询分析器执行情况：创建视图的 SQL 语句及执行结果显示1查询视图 SQL 语句及执行结果截图227查询分析器执行情况：创建视图的 SQL 语句及执行结果显示3、4、查询视图 SQL 语句及执行结果截图5、查询分析器执行情况：修改视图的 SQL 语句及执行结果显示6、查询视图 SQL 语句及执行结果截图7、28查询分析器执行情况：删除视图的 SQL 语句及执行结果显示8、查询视图 SQL 语句及执行结果截图（二）、索

41、引在 student_course 表（学生选课表）的学生学号（sno）列上创建索引 sc_sno_index查询分析器执行情况：创建索引的 SQL 语句及执行结果显示（三）、存储过程题目（1 ）创建存储过程 list_student_department,该存储过程接收学院代码作为输入参数，列出数据库 sc 中某个院系学生的全部信息。查询分析器执行情况：SQL 语句CREATE PROCEDURE list_student_department_3114006159indno char(6)ASSELECT *FROM student_3114006159WHERE dno=indno执行结

42、果截图显示29题目（2 ）使用 SQL 语句执行存储过程 list_student_department查询分析器执行情况：SQL 语句EXEC list_student_department_3114006159 indno=659801执行结果截图显示题目（3 ）编写存储过程将某班选修某门课程的所有学生的学号和上课编号插入到student_course 表中查询分析器执行情况：SQL 语句CREATE PROCEDURE insert_student_course_3114006159 (cno char(8),class_no char(4),tcid integer) AS INSER

43、T INTO student_course_3114006159(tcid,s_no) SELECT tcid,s_no FROM student_3114006159,course_3114006159 WHEREcno=cno AND class_no=class_no AND s_no not IN(SELECT s_no FROM student_course_3114006159 WHERE tcid=tcid)30执行结果截图显示题目（4 ）使用 SQL 语句执行（3）创建的存储过程查询分析器执行情况：SQL 语句EXEC insert_student_course_3114006

44、159 cno=6598011001,class_no=1401,tcid=1执行结果截图显示题目（5 ）使用 SQL 语句查看（3）创建的存储过程代码查询分析器执行情况：SQL 语句EXEC sp_helptext list_student_department_3114006159执行结果截图显示31题目（6 ）使用 SQL 语句删除一个前面建立的存储过程查询分析器执行情况：SQL 语句DROP PROCEDURE insert_student_course_3114006159执行结果截图显示四、实验中出现的问题及解决方案无五、思考题1、视图更新的优点答：1.视图可以简化用户的操作。2.

45、视图能使用户能以多种角度看待同一数据。3.视图对重构数据提供了一定程度的逻辑独立性。4.视图能够对机密数据提供安全保护。5.适当利用视图可以更加清晰的表达查询。2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。答：一般的，行列子集视图时可更新的，除行列子集视图外，理论上有些视图也是可以更新的，但它们的确切特征还是尚待研究的课题。还有其他的视图从理论上就是不可更新的，比如，视图的属性来自聚集函数、表达式，则该视图肯定是不可更新的。在上述实验中 ies_student_view_3114006159 视图是可以更新的，其视图更新既是对student_3114006159 表的更新

46、。323、什么是存储过程，什么是触发器，两者有什么区别？答：触发器与存储过程可以说是非常相似，可以说是一种变种的存储过程，触发器和存储过程一样都是 SQL 语句集，通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用4、teacher_course 表中的 class_no 是否是外码？请说明判断的理由。答：不是外码，因为 class_no 并不是其它表的主键，也不包含外键，所以它不是外码。5、teacher_course 表中的 class_no 应该与 class 表中的 class_no 建立对应关系，即第一、当向 teacher_course 表中插入一条元组时，必须保证 class_no 是 class 表中的class_no；第二、当删除或修改 class 表中的 class_no 时，teacher_course 表中的 class_no 也应该随之变化或不允许变化。请用触发器完成上述要求。第一：第二：