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

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

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

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

4、,FILENAME=E:SC_.ldf,SIZE=3,FILEGROWTH=1)查询分析器执行情况：SQL语句及执行结果截图显示2、创建基本表利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。(1) 创建student表字段名代码类型约束学号s_nochar(8)主键姓名snamechar(8)非空性别sexchar(2) 出生日期sbirthdaySmalldatetime学生所在院系编号dnochar(6) 外键专业代码spnochar(8)外键班级编码class_nochar(4) (2)创建Course表字段名代码类型约束课程编号cnochar(10)主键课程

5、名称cnamechar(20)非空专业代码spnochar(8)外键课程类型编号ctnotinyint理论学时lecturetinyint实验学时experimenttinyint开课学期semestertinyint课程学分credittinyint（3）创建student _course表字段名代码类型约束学号s_nochar(8)主键,与student表中s_no 外键关联,级联删除上课编号tcidsmallint主键学生成绩score tinyint (4)创建teacher表 字段名代码类型约束教师编号t_nochar(8)主键教师姓名t_namechar(8)非空性别t_sexch

6、ar(2)出生日期t_birthdaysmalldatetime教师所在院系编号dnochar(6) 外键职称tech_titlechar(10)(5)创建系部表(department) 字段名代码类型约束院系编号dnochar(6)主键院系名称dept_namechar(20)非空院系负责人headerchar(8) (6)创建专业信息表(speciality) 字段名代码类型约束专业代码spnochar(8)主键院系编号dnochar(6)外键,非空专业名称spnamechar(20)非空(7)创建teacher_course表字段名代码类型约束上课编号tcidsmallint主键教师编号

7、t_nochar(8)外键专业代码spnochar(8)外键班级编码class_nochar(4)课程编号cnochar(10)非空,外键学期semesterChar(6)学年schoolyear Char(10) (8)创建班级表(class) 字段名代码类型约束专业代码spnochar(8)主键，与 speciality 表中spno 外键关联,班级编码class_nochar(4)主键,班负责人headerchar(8)3、 查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示4、创建各表的实现代码及截图：SQL语句USE SC_GOCREATE TABLE student

8、_ (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_ (cno char(10) PRIMARY KEY, cname char(20) NOT NULL, spno char(8), ctno tinyint, lecture tinyint, experiment tinyint, semester tinyint, credit tin

9、yint);CREATE TABLE student_course_ (s_no char(8), tcid smallint, score tinyint, PRIMARY KEY(s_no,tcid);CREATE TABLE teacher_ (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 TABLE deparment_ (dno char(6) PRIMARY KEY,

10、 dept_name char(20)NOT NULL, header char(8);CREATE TABLE speciality_ (spno char(8) PRIMARY KEY, dno char(6) NOT NULL, spname char(20) NOT NULL);CREATE TABLE teacher_course_ (tcid smallint PRIMARY KEY, t_no char(8), spno char(8), class_no char(4), cno char(10) NOT NULL, semester char(6), schoolyear c

11、har(10);CREATE TABLE class_ (spno char(8), class_no char(4), header char(8), PRIMARY KEY(spno,class_no);ALTER TABLE student_ ADD FOREIGN KEY (dno) REFERENCES deparment_(dno);ALTER TABLE student_ ADD FOREIGN KEY (spno) REFERENCES speciality_(spno);ALTER TABLE course_ ADD FOREIGN KEY (spno) REFERENCES

12、 speciality_(spno);ALTER TABLE student_course_ ADD FOREIGN KEY (s_no) REFERENCES student_(s_no);ALTER TABLE teacher_ ADD FOREIGN KEY (dno) REFERENCES deparment_(dno);ALTER TABLE speciality_ ADD FOREIGN KEY (dno) REFERENCES deparment_ (dno);ALTER TABLE teacher_course_ ADD FOREIGN KEY (t_no) REFERENCE

13、S teacher_(t_no);ALTER TABLE teacher_course_ ADD FOREIGN KEY (spno) REFERENCES speciality_(spno);ALTER TABLE teacher_course_ ADD FOREIGN KEY (cno) REFERENCES course_(cno);ALTER TABLE class_ ADD FOREIGN KEY (spno) REFERENCES speciality_(spno);查询分析器执行情况：SQL语句及执行结果截图显示5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中

14、插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示insert into deparment_values(,计算机,周杰伦);insert into deparment_values(,机电,陈奕迅);insert into deparment_values(,外国语,章子怡);insert into deparment_values(,土木,梁朝伟);insert into deparment_values(,环境,张国荣);insert into teacher_values(,刘德华,男,1975-03-25,高级);inser

15、t into teacher_values(,周润发,男,1964-09-24,高级);insert into teacher_values(,张学友,男,1956-06-15,高级);insert into teacher_values(,张曼玉,女,1965-04-21,高级);insert into teacher_values(,陈坤,男,1988-03-02,高级);insert into speciality_values(,计算机科学与技术);insert into speciality_values(,自动化);insert into speciality_values(,商务

16、英语);insert into speciality_values(,土木工程);insert into speciality_values(,环境工程);insert into class_values(,1401,刘德华);insert into class_values(,1402,周润发);insert into class_values(,1403,张学友);insert into class_values(,1404,张曼玉);insert into class_values(,1405,陈坤);insert into course_values(,数据库,1,48,12,1,2)

17、;insert into course_values(,制图,2,56,6,2,2);insert into course_values(,口语,3,48,0,1,4);insert into course_values(,建筑学,4,48,12,1,2);insert into course_values(,基础化学,5,48,12,1,2);insert into teacher_course_values(1,1401,一学期,第二学年);insert into teacher_course_values(2,1402,一学期,第二学年);insert into teacher_cour

18、se_values(3,1403,一学期,第二学年);insert into teacher_course_values(4,1404,一学期,第二学年);insert into teacher_course_values(5,1405,一学期,第二学年);insert into student_values(,王小波,男,1997-01-03,1401);insert into student_values(,韩寒,男,1992-03-23,1402);insert into student_values(,莫言,男,1997-06-15,1403);insert into student_

19、values(,三毛,女,1994-05-30,1404);insert into student_values(,郭敬明,男,1995-12-03,1405);insert into student_course_values(,1,96);insert into student_course_values(,3,67);insert into student_course_values(,2,91);insert into student_course_values(,4,89);insert into student_course_values(,5,96);实验结果截图显示（插入数据的

20、表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：use SC_update student_set sname=村上春树where sname=莫言student表更改前的内容截图显示查询分析student表更改后的内容截图显示(3)、用DELETE语句删除student表中数据；实现代码：use SC_delete from student_course_where s_no in(select s_nofrom student_where sname=郭敬明);deletefrom student_ where sname=郭敬明student表更改

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

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

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

24、全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示use SC_select s_no,sname,sex,sbirthdayfrom student_查询结果截图显示（2）、查询全体学生的学号、姓名和年龄实现代码及查询结果截图：use SC_select s_no,sname,sex,year(getdate()-year(sbirthday)from student_（3）、查询院系编号为（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：use SC_select s_no,sname,sex,

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

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

27、 anddeparment_.dno=speciality_.dno（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：use SC_select student_.s_no,sname,spname,scorefrom student_,student_course_,speciality_where student_.s_no=student_course_.s_no and student_.spno=speciality_.spno and tcid=1（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及

28、查询结果截图：use SC_select student_.s_no,sname,cnamefrom student_,student_course_,course_where student_.s_no=student_course_.s_no and student_course_.tcid=course_.ctno and score60嵌套查询：（1）、查询没有选修了课程1的学生，列出学生的学号和姓名。实现代码及查询结果截图：use SC_select distinct student_.s_no,snamefrom student_,student_course_where stud

29、ent_.s_no not in( select s_no from student_course_ where tcid=1)（2）、查询每门课都是80分以上的学生的学号与姓名。实现代码及查询结果截图：use SC_select distinct student_.s_no,snamefrom student_where not exists( select * from student_course_ where student_.s_no=student_course_.s_no and score80)2、选用Northwind数据库进行查询（1）、对NothWind. Product

30、s表进行简单查询；在查询分析器在窗口下用SELECT语句以下部分查询结果截图由于表格过长，所以只截取了部分表格内容，无法完整截图整个表格。完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：use Northwindselect *from Products查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：use Northwindselect *from Productswhere UnitPrice20查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：use Northwindsele

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

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

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

34、视图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 查询分析器执行情况：创建索引的SQ

35、L语句及执行结果显示（三）、存储过程题目（1）创建存储过程list_student_department,该存储过程接收学院代码作为输入参数，列出数据库sc中某个院系学生的全部信息。查询分析器执行情况：SQL语句CREATE PROCEDURE list_student_department_indno char(6)ASSELECT *FROM student_WHERE dno=indno执行结果截图显示题目（2）使用SQL语句执行存储过程list_student_department查询分析器执行情况：SQL语句EXEC list_student_department_ indno=执行

36、结果截图显示题目（3）编写存储过程将某班选修某门课程的所有学生的学号和上课编号插入到student_course表中查询分析器执行情况：SQL语句CREATE PROCEDURE insert_student_course_ (cno char(8),class_no char(4),tcid integer) AS INSERT INTO student_course_(tcid,s_no) SELECT tcid,s_no FROM student_,course_ WHEREcno=cno AND class_no=class_no AND s_no not IN(SELECT s_no

37、 FROM student_course_ WHERE tcid=tcid)执行结果截图显示题目（4）使用SQL语句执行（3）创建的存储过程查询分析器执行情况：SQL语句EXEC insert_student_course_ cno=,class_no=1401,tcid=1执行结果截图显示题目（5）使用SQL语句查看（3）创建的存储过程代码查询分析器执行情况：SQL语句EXEC sp_helptext list_student_department_执行结果截图显示题目（6）使用SQL语句删除一个前面建立的存储过程查询分析器执行情况：SQL语句DROP PROCEDURE insert_st

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

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

40、是外码，因为class_no并不是其它表的主键，也不包含外键，所以它不是外码。5、teacher_course表中的class_no应该与class表中的class_no建立对应关系，即第一、当向teacher_course表中插入一条元组时，必须保证class_no是class表中的class_no；第二、当删除或修改class表中的class_no时，teacher_course表中的class_no也应该随之变化或不允许变化。请用触发器完成上述要求。第一：第二：实验题目 实验四 小型数据库规划设计 一、 实验目的学习数据库原理及应用目的就是能创建和管理一个真实的数据库，通过规划设计一个小

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

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

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

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

45、外键supplier表：字段名代码类型约束供应商名称snamechar(10)非空供应商编号snochar(8)主键goods表：字段名代码类型约束商品名称gnamechar(20)非空商品编号gnochar(8)主键商品类型编号gtype_nochar(10)外键供应商编号snochar(8)外键价格gpriceint大于0order表：字段名代码类型约束订单号order_nochar（10）主键客户编号cnochar(6)外键order_goods表：字段名代码类型约束订单号order_nochar(10)主键，外键商品编号gnochar(8)主键，外键商品数量goods_numint大于

46、0总金额amountint等于数量乘单价creditgrade表：字段名代码类型约束等级名称grade_namechar(8)非空信誉等级cgradeint主键goodstype表：字段名代码类型约束商品类型名gtype_namechar(10)非空类型编号gtype_nochar(10)主键2、 生成数据库关系图3、 查询customer_guangzhou视图，获得广州客户信息。（截图显示）5、查询ordersum_goods视图，获得每种商品的名称和每种商品的订单金额的总和。（截图显示）6、执行存储过程并观察结果。（截图显示）7、代码附录（列出所有的SQL语句，并标注每段代码的功能，宋体五号字，分栏打印）/创建数据库CREATE DATABASE SALES_ON PRIMARY(NAME=SALES_DATA,FILENAME=G:SALES_.mdf,SIZE=30,FILEGROWTH=20%)LOG ON(NAME=SC_LOG,FILENAME=G:SALES_.ldf,SIZE=3,FILEGROWTH=1)/创建表USE SALES_GOcreate table customer(cname char(8) not null, cno char(6) primary ke