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

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

2、nager创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机,Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC,其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表、course表课程信息表、teacher表教师信息表、student _course表学生选课成绩表、teacher_course表教师上课课表等。1、创建数据库：确定数据库名称；数据库用于学生管理,命名为SC确定数据库的位置；要求：数据文件

3、和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB,日志文件的初始大小为3MB。确定数据库的增长；根据实际情况,确定数据文件按20%增长,日志文件按1MB增长。1、利用查询分析器Query Analyzer,使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句CREATEDATABASE SC_3114006159ONPRIMARYLOGON查询分析器执行情况：SQL语句及执行结果截图显示2、创建基本表利用查询分析器,使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。创建student表字段名代码类型约束学号s_nocha

4、r主键姓名snamechar非空性别sexchar出生日期sbirthdaySmalldatetime学生所在院系编号dnochar 外键专业代码spnochar外键班级编码class_nochar创建Course表字段名代码类型约束课程编号cnochar主键课程名称cnamechar非空专业代码spnochar外键课程类型编号ctnotinyint理论学时lecturetinyint实验学时experimenttinyint开课学期semestertinyint课程学分credittinyint3创建student _course表字段名代码类型约束学号s_nochar主键,与student

5、表中s_no 外键关联,级联删除上课编号tcidsmallint主键学生成绩score tinyint 创建teacher表字段名代码类型约束教师编号t_nochar主键教师姓名t_namechar非空性别t_sexchar出生日期t_birthdaysmalldatetime教师所在院系编号dnochar 外键职称tech_titlechar创建系部表字段名代码类型约束院系编号dnochar主键院系名称dept_namechar非空院系负责人headerchar 创建专业信息表字段名代码类型约束专业代码spnochar主键院系编号dnochar外键,非空专业名称spnamechar非空创建t

6、eacher_course表字段名代码类型约束上课编号tcidsmallint主键教师编号t_nochar外键专业代码spnochar外键班级编码class_nochar课程编号cnochar非空,外键学期semesterChar学年schoolyearChar创建班级表字段名代码类型约束专业代码spnochar主键,与speciality 表中spno 外键关联,班级编码class_nochar主键,班负责人headerchar查看各数据表之间的关系,生成数据库关系图。生成数据库关系图截图显示4、创建各表的实现代码及截图：SQL语句USE SC_3114006159GOCREATETABLE

7、 student_3114006159s_no charPRIMARYKEY, sname charNOTNULL, sex char, sbirthday smalldatetime, dno char, spno char, class_no char;CREATETABLE course_3114006159cno charPRIMARYKEY, cname charNOTNULL, spno char, ctno tinyint, lecture tinyint, experiment tinyint, semester tinyint, credit tinyint;CREATETA

8、BLE student_course_3114006159s_no char, tcid smallint, score tinyint,PRIMARYKEY;CREATETABLE teacher_3114006159t_no charPRIMARYKEY, t_name charNOTNULL, t_sex char, t_birthday smalldatetime, dno char, tech_title char;CREATETABLE deparment_3114006159dno charPRIMARYKEY, dept_name charNOTNULL, header cha

9、r;CREATETABLE speciality_3114006159spno charPRIMARYKEY, dno charNOTNULL, spname charNOTNULL;CREATETABLE teacher_course_3114006159tcid smallintPRIMARYKEY, t_no char, spno char, class_no char, cno charNOTNULL, semester char, schoolyear char;CREATETABLE class_3114006159spno char, class_no char, header

10、char,PRIMARYKEY;ALTERTABLE student_3114006159 ADDFOREIGNKEYREFERENCES deparment_3114006159;ALTERTABLE student_3114006159 ADDFOREIGNKEYREFERENCES speciality_3114006159;ALTERTABLE course_3114006159 ADDFOREIGNKEYREFERENCES speciality_3114006159;ALTERTABLE student_course_3114006159 ADDFOREIGNKEYREFERENC

11、ES student_3114006159;ALTERTABLE teacher_3114006159 ADDFOREIGNKEYREFERENCES deparment_3114006159;ALTERTABLE speciality_3114006159ADDFOREIGNKEYREFERENCES deparment_3114006159 ;ALTERTABLE teacher_course_3114006159 ADDFOREIGNKEYREFERENCES teacher_3114006159;ALTERTABLE teacher_course_3114006159 ADDFOREI

12、GNKEYREFERENCES speciality_3114006159;ALTERTABLE teacher_course_3114006159 ADDFOREIGNKEYREFERENCES course_3114006159;ALTERTABLE class_3114006159 ADDFOREIGNKEYREFERENCES speciality_3114006159;查询分析器执行情况：SQL语句及执行结果截图显示5、利用查询分析器修改上述各表。、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示

13、insertinto deparment_3114006159values;insertinto deparment_3114006159values;insertinto deparment_3114006159values;insertinto deparment_3114006159values;insertinto deparment_3114006159values;insertinto teacher_3114006159values;insertinto teacher_3114006159values;insertinto teacher_3114006159values;in

14、sertinto teacher_3114006159values;insertinto teacher_3114006159values;insertinto speciality_3114006159values;insertinto speciality_3114006159values;insertinto speciality_3114006159values;insertinto speciality_3114006159values;insertinto speciality_3114006159values;insertinto class_3114006159values;i

15、nsertinto class_3114006159values;insertinto class_3114006159values;insertinto class_3114006159values;insertinto class_3114006159values;insertinto course_3114006159values;insertinto course_3114006159values;insertinto course_3114006159values;insertinto course_3114006159values;insertinto course_3114006

16、159values;insertinto teacher_course_3114006159values;insertinto teacher_course_3114006159values;insertinto teacher_course_3114006159values;insertinto teacher_course_3114006159values;insertinto teacher_course_3114006159values;insertinto student_3114006159values;insertinto student_3114006159values;ins

17、ertinto student_3114006159values;insertinto student_3114006159values;insertinto student_3114006159values;insertinto student_course_3114006159values;insertinto student_course_3114006159values;insertinto student_course_3114006159values;insertinto student_course_3114006159values;insertinto student_cour

18、se_3114006159values;实验结果截图显示插入数据的表格共8组共8个表、用UPDATE语句更改student表中数据；实现代码：use SC_3114006159update student_3114006159set sname=村上春树where sname=莫言student表更改前的内容截图显示查询分析student表更改后的内容截图显示、用DELETE语句删除student表中数据；实现代码：use SC_3114006159deletefrom student_course_3114006159where s_no in;deletefrom student_3114

19、006159 where sname=郭敬明student表更改前的内容截图显示查询分析student表更改后的内容截图显示五、实验中出现的问题及解决方案在插入数据时没注意外键约束,结果导致插入失败,于是根据数据表关系图先插入没有外键约束的表,再根据外键约束关系按照一定顺序插入表。在删除student的元组时,因为其主键被student_course表外键所参照,所以无法删除,于是先删除student_course表中的元组,再删除student中的元组。六、思考题说明数据库中的表和数据文件的关系。答：表存储于数据文件中,一个数据文件可以存储多个表。数据库中的日志文件能否单独修改?不能,SQL

20、 Server数据库都有log日志文件,log日志文件记录用户对数据库修改的操作,只有数据库发生更改时,日志文件才会发生更改,不能单独更改。附加练习题. 将计算机专业所有学生的数据库原理的成绩增加10分代码以及运行结果： 删除计算机专业所有学生的数据库原理的选修信息代码以及运行结果： 为speciality的spname添加唯一约束代码以及运行结果: 为student_course的score设置检查约束代码以及运行结果:实验题目实验二查询数据库一、实验目的熟悉SQL语句的基本使用方法,学习如何编写SQL语句来实现查询1、掌握基本的SELECT查询及其相关子句的使用；2、掌握复杂的SELECT

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

22、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示use SC_3114006159select s_no,sname,sex,sbirthdayfrom student_3114006159查询结果截图显示2、查询全体学生的学号、姓名和年龄实现代码及查询结果截图：use SC_3114006159select s_no,sname,sex,yeargetdate-yearfrom student_31140061593、查询院系编号为510000计算机学院的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：use SC_3114006

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

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

25、 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.dno2、查询选修了课程1上课编号的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：use SC_3114006159select stud

26、ent_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=13、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：use SC_3114006159s

27、elect student_3114006159.s_no,sname,cnamefrom student_3114006159,student_course_3114006159,course_3114006159where student_3114006159.s_no=student_course_3114006159.s_no andstudent_course_3114006159.tcid=course_3114006159.ctno andscore60嵌套查询：1、查询没有选修了课程1的学生,列出学生的学号和姓名。实现代码及查询结果截图：use SC_3114006159sel

28、ectdistinct student_3114006159.s_no,snamefrom student_3114006159,student_course_3114006159where student_3114006159.s_no notin2、查询每门课都是80分以上的学生的学号与姓名。实现代码及查询结果截图：use SC_3114006159selectdistinct student_3114006159.s_no,snamefrom student_3114006159wherenotexistsselect*from student_course_3114006159wher

29、e student_3114006159.s_no=student_course_3114006159.s_no and score2、选用Northwind数据库进行查询1、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句以下部分查询结果截图由于表格过长,所以只截取了部分表格内容,无法完整截图整个表格。完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：use Northwindselect*from Products查询单价UnitPrice小于20的Products；实现代码及查询结果截图：use Northwindsel

30、ect*from Productswhere UnitPrice20查询Products中最高单价UnitPrice是多少；实现代码及查询结果截图：use Northwindselectmaxfrom Products2、在查询分析器在窗口下用SELECT语句完成连接嵌套查询： 查询所有被订购过得Products的ProductsID和ProductName；实现代码及查询结果截图：use Northwindselectdistinct Products.ProductID,Products.ProductNamefrom Products,Order Details where Produc

31、ts.ProductID=Order Details.ProductID查询所有被CustomerID为AROUT客和订购过的ProductsID和ProductName；实现代码及查询结果截图：use Northwindselect Products.ProductID,Products.ProductNamefrom Products,Order Details,Orderswhere Products.ProductID=Order Details.ProductID and Order Details.OrderID=Orders.OrderID and CustomerID=AROU

32、T查询所有被CustomerID为AROUT客和订购过,且单价在20 以上的ProductsID和ProductName；实现代码及查询结果截图：use Northwindselect Products.ProductID,Products.ProductNamefrom Products,Order Details,Orderswhere Products.ProductID=Order Details.ProductID and Order Details.OrderID=Orders.OrderID and CustomerID=AROUTand Products.UnitPrice20

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

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

35、法、步骤及结果测试一、视图1、建立计算机学院的学生基本情况视图ies_student_view,该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行ies_student_view视图并观察结果。3、建立课程1上课编号的学生名册的视图,该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视图,列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图ies_student_view,使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。7、执行ies_student_view视图并观察结果。8、

36、删除视图ies_student_view。并查询结果。记录上述实验过程并截图说明。查询分析器执行情况：创建视图的SQL语句及执行结果显示1查询视图SQL语句及执行结果截图2查询分析器执行情况：创建视图的SQL语句及执行结果显示3、4、查询视图SQL语句及执行结果截图5、查询分析器执行情况：修改视图的SQL语句及执行结果显示6、查询视图SQL语句及执行结果截图7、查询分析器执行情况：删除视图的SQL语句及执行结果显示8、查询视图SQL语句及执行结果截图二、索引在student_course表学生选课表的学生学号sno列上创建索引sc_sno_index查询分析器执行情况：创建索引的SQL语句及执

37、行结果显示三、存储过程题目1创建存储过程list_student_department,该存储过程接收学院代码作为输入参数,列出数据库sc中某个院系学生的全部信息。查询分析器执行情况：SQL语句CREATEPROCEDURE list_student_department_3114006159indno charASSELECT*FROM student_3114006159WHERE dno=indno执行结果截图显示题目2使用SQL语句执行存储过程list_student_department查询分析器执行情况：SQL语句EXEC list_student_department_31140

38、06159 indno=659801执行结果截图显示题目3编写存储过程将某班选修某门课程的所有学生的学号和上课编号插入到student_course表中查询分析器执行情况：SQL语句CREATEPROCEDURE insert_student_course_3114006159 cno char,class_no char,tcid integerASINSERTINTO student_course_3114006159SELECT tcid,s_no FROM student_3114006159,course_3114006159 WHEREcno=cno AND class_no=cl

39、ass_no AND s_no notIN执行结果截图显示题目4使用SQL语句执行3创建的存储过程查询分析器执行情况：SQL语句EXEC insert_student_course_3114006159 cno=6598011001,class_no=1401,tcid=1执行结果截图显示题目5使用SQL语句查看3创建的存储过程代码查询分析器执行情况：SQL语句EXECsp_helptext list_student_department_3114006159执行结果截图显示题目6使用SQL语句删除一个前面建立的存储过程查询分析器执行情况：SQL语句DROPPROCEDURE insert_s

40、tudent_course_3114006159执行结果截图显示四、实验中出现的问题及解决方案无五、思考题1、视图更新的优点答：1.视图可以简化用户的操作。2.视图能使用户能以多种角度看待同一数据。3.视图对重构数据提供了一定程度的逻辑独立性。4.视图能够对机密数据提供安全保护。5.适当利用视图可以更加清晰的表达查询。2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。答：一般的,行列子集视图时可更新的,除行列子集视图外,理论上有些视图也是可以更新的,但它们的确切特征还是尚待研究的课题。还有其他的视图从理论上就是不可更新的,比如,视图的属性来自聚集函数、表达式,则该视图肯定是不

41、可更新的。在上述实验中 ies_student_view_3114006159视图是可以更新的,其视图更新既是对student_3114006159表的更新。3、什么是存储过程,什么是触发器,两者有什么区别？答：触发器与存储过程可以说是非常相似,可以说是一种变种的存储过程,触发器和存储过程一样都是SQL语句集,通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器,所以可以用它来强制实施复杂的业务规则,以确保数据的完整性。触发器不同于存储过程,触发器主要是通过事件执行触发而被执行的,而存储过程可以通过存储过程名称名字而直接调用4、teacher_cour

42、se表中的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也应该随之变化或不允许变化。请用触发器完成上述要求。第一：第二：实验题目实验四小型数据库规划设计 实验目的学习数据库原理及应用目的

43、就是能创建和管理一个真实的数据库,通过规划设计一个小型数据库系统。让学生掌握数据库规划设计的基本技术,熟悉数据库的设计的基本方法和步骤,明白数据库设计各阶段的任务,加深对数据库系统概念和特点的理解。实验内容和要求根据你熟悉的实际情况,选一个小型的数据库应用项目,销售管理系统,进行系统分析和数据库设计。要求：1、规划设计一个小型系统的数据库,按照给定要求,对各设计阶段进行描述,要求给出数据库设计的E-R图,并将E-R图转换成相应的模型,编制SQL命令脚本。2、要求：数据库结构严谨、基本表及属性合理、各种约束条件正确。3、涉及的内容或知识点：数据库设计的需求分析、数据模型、关系规范化理论。4、采用

44、的方法和手段：设计数据模型、进行关系规范化处理和用软件实现数据库的构建。实验主要仪器设备和材料1计算机及操作系统：PC机,Windows XP；2数据库管理系统：SQL sever 2000/2003/2005实验方法、步骤及结构测试一、数据库设计1、销售管理系统数据库包括客户数据、商品数据和订单数据。2、确定销售管理系统中的实体及其属性3、建立销售管理系统的E-R图4、设计销售管理系统数据库Sales5、设计customer表客户表、supplier表供应商表、goods表商品信息表、orader表里订单表、creditgrade表信誉等级表和goodstype表商品类型表的结构。6、确定以

45、上各表的关键字及字段数据类型。二、创建数据库和表1、创建Sales数据库2、创建customer表客户表、supplier表供应商表、goods表商品信息表、orader表里订单表、creditgrade表信誉等级表和goodstype表商品类型表3、分别向以上各表输入入数据三、设计数据完整性1、定义customer表客户表、supplier表供应商表、goods表商品信息表、order表里订单表、creditgrade表信誉等级表和goodstype表商品类型表的主键2、定义以上各表的外键约束3、定义唯一性约束四、创建和使用视图1、创建一个包含XX客户信息的视图customer_guangz

46、hou2、创建一个视图ordersum_goods,其中的内容是每种商品的名称和每种商品的订单金额的总和。五、创建和执行存储过程1、创建一个存储过程proc_hub,用来查看hub商品的所有订货情况2、创建一个带参数的存储过程proc_gooods,参数为商品名称,通过该存储过程可查看任何指定商品订单信息。五、实验报告要求建立系统的E-R图2、列出设计的数据库表customer表：字段名代码类型约束姓名cnamechar非空客户编号cnochar主键地区cregionchar非空信誉等级cgradeint外键supplier表：字段名代码类型约束供应商名称snamechar非空供应商编号sno

47、char主键goods表：字段名代码类型约束商品名称gnamechar非空商品编号gnochar主键商品类型编号gtype_nochar外键供应商编号snochar外键价格gpriceint大于0order表：字段名代码类型约束订单号order_nochar10主键客户编号cnochar外键order_goods表：字段名代码类型约束订单号order_nochar主键,外键商品编号gnochar主键,外键商品数量goods_numint大于0总金额amountint等于数量乘单价creditgrade表：字段名代码类型约束等级名称grade_namechar非空信誉等级cgradeint主键g

48、oodstype表：字段名代码类型约束商品类型名gtype_namechar非空类型编号gtype_nochar主键生成数据库关系图查询customer_guangzhou视图,获得XX客户信息。截图显示5、查询ordersum_goods视图,获得每种商品的名称和每种商品的订单金额的总和。截图显示6、执行存储过程并观察结果。截图显示7、代码附录列出所有的SQL语句,并标注每段代码的功能,宋体五号字,分栏打印/创建数据库.CREATE DATABASE SALES_3114006159ON PRIMARYLOG ON/创建表USE SALES_3114006159GOcreate table

49、customercname char not null, cno char primary key, cregion char not null, cgrade int;create table suppliersname char, sno char primary keycreate table goodsgname char not null, gno char primary key, gtype_no char, sno char, gprice int ;create table orderorder_no char primary key, cno char;create tab

50、le order_goodsorder_no char, gno char, goods_num int, amount int, primary key;create table creditgradegrade_name char not null, cgrade int primary key;create table goodstypegtype_name char not null, gtype_no char primary key;ALTER TABLE customer ADD FOREIGN KEY REFERENCES creditgrade; ALTER TABLE goods ADD FOREIGN KEY REFERENCES goodstype;ALTER TABLE goods ADD FOREIGN KEY REFERENCES supplier;ALTER TABLE order ADD FOREIGN KEY REFERENCES customer;ALTER TABLE order_goods ADD FOREIGN KEY