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

,. 数据库系统实验报告 学 院 计算机学院 专 业 计算机科学与技术 班 级 级 班 学 号 姓 名 指导教师 （ 2016 年 12 月） 实验__一__题目 数据库及基本表的建立 实验__二__题目 查询数据库_ 实验__三__题目 创建和使用视图、索引、存储过程 实验__四 _题目 小型数据库规划设计 实验平台：SQL Sever 2005 实验题目 实验一 数据库及基本表的建立 一、实验目的 1、掌握SQL SERVER的查询分析器和企业管理器的使用； 2、掌握创建数据库和表的操作； 二、实验内容和要求 1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库； 2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表； 三、实验主要仪器设备和材料 1．计算机及操作系统：PC机，Windows 2000/xp； 2．数据库管理系统：SQL sever 2000/2003/2005； 四、实验方法、步骤及结果测试 创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。 创建：student表(学生信息表)、course表（课程信息表)、teacher表（教师信息表)、student _course表（学生选课成绩表)、teacher_course表（教师上课课表）等。 1、创建数据库： 确定数据库名称；数据库用于学生管理，命名为SC 确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。 确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。 确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。 （1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库； 实现代码及截图： SQL语句 CREATE DATABASE SC_3114006159 ON PRIMARY (NAME=SC_3114006159_DATA, FILENAME=E:\3114006159\SC_3114006159.mdf, SIZE=30, FILEGROWTH=20%) LOG ON (NAME=SC_3114006159_LOG, FILENAME=E:\3114006159\SC_3114006159.ldf, SIZE=3, FILEGROWTH=1) 查询分析器执行情况： SQL语句及执行结果截图显示 2、创建基本表 利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。 (1) 创建student表 字段名 代码 类型 约束 学号 s_no char(8) 主键 姓名 sname char(8) 非空 性别 sex char(2) 出生日期 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表 字段名 代码 类型 约束 学号 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) 主键 院系名称 dept_name char(20) 非空 院系负责人 header char(8) (6)创建专业信息表(speciality) 字段名 代码 类型 约束 专业代码 spno char(8) 主键 院系编号 dno char(6) 外键,非空 专业名称 spname char(20) 非空 (7)创建teacher_course表 字段名 代码 类型 约束 上课编号 tcid smallint 主键 教师编号 t_no char(8) 外键 专业代码 spno char(8) 外键 班级编码 class_no char(4) 课程编号 cno char(10) 非空,外键 学期 semester Char(6) 学年 schoolyear Char(10) (8)创建班级表(class) 字段名 代码 类型 约束 专业代码 spno char(8) 主键，与 speciality 表中spno 外键关联, 班级编码 class_no char(4) 主键, 班负责人 header char(8) 3、 查看各数据表之间的关系，生成数据库关系图。 生成数据库关系图截图显示 4、创建各表的实现代码及截图： SQL语句 USE SC_3114006159 GO CREATE 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 tinyint, 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 TABLE deparment_3114006159 (dno char(6) PRIMARY KEY, dept_name char(20) NOT NULL, header 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(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 student_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 ADD FOREIGN KEY (dno) REFERENCES deparment_3114006159(dno); ALTER TABLE speciality_3114006159 ADD 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 KEY (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); 查询分析器执行情况： SQL语句及执行结果截图显示 5、利用查询分析器修改上述各表。 (1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。 实现代码及截图： 查询分析器执行情况： SQL语句及执行结果显示 insert into deparment_3114006159 values(659801,计算机,周杰伦); insert into deparment_3114006159 values(659802,机电,陈奕迅); insert into deparment_3114006159 values(659803,外国语,章子怡); insert into deparment_3114006159 values(659804,土木,梁朝伟); insert into deparment_3114006159 values(659805,环境,张国荣); insert into teacher_3114006159 values(65980101,刘德华,男,1975-03-25,659801,高级); insert into teacher_3114006159 values(65980201,周润发,男,1964-09-24,659802,高级); insert into teacher_3114006159 values(65980301,张学友,男,1956-06-15,659803,高级); insert into teacher_3114006159 values(65980401,张曼玉,女,1965-04-21,659804,高级); insert into teacher_3114006159 values(65980501,陈坤,男,1988-03-02,659805,高级); insert into speciality_3114006159 values(65980110,659801,计算机科学与技术); insert into speciality_3114006159 values(65980210,659802,自动化); insert into speciality_3114006159 values(65980310,659803,商务英语); insert into speciality_3114006159 values(65980410,659804,土木工程); insert into speciality_3114006159 values(65980510,659805,环境工程); insert into class_3114006159 values(65980110,1401,刘德华); insert into class_3114006159 values(65980210,1402,周润发); insert into class_3114006159 values(65980310,1403,张学友); insert into class_3114006159 values(65980410,1404,张曼玉); insert into class_3114006159 values(65980510,1405,陈坤); insert into course_3114006159 values(6598011001,数据库,65980110,1,48,12,1,2); insert into course_3114006159 values(6598021001,制图,65980210,2,56,6,2,2); insert into course_3114006159 values(6598031001,口语,65980310,3,48,0,1,4); insert into course_3114006159 values(6598041001,建筑学,65980410,4,48,12,1,2); insert into course_3114006159 values(6598051001,基础化学,65980510,5,48,12,1,2); insert into teacher_course_3114006159 values(1,65980101,65980110,1401,6598011001,一学期,第二学年); insert into teacher_course_3114006159 values(2,65980201,65980210,1402,6598021001,一学期,第二学年); insert into teacher_course_3114006159 values(3,65980301,65980310,1403,6598031001,一学期,第二学年); insert into teacher_course_3114006159 values(4,65980401,65980410,1404,6598041001,一学期,第二学年); insert into teacher_course_3114006159 values(5,65980501,65980510,1405,6598051001,一学期,第二学年); insert into student_3114006159 values(31140001,王小波,男,1997-01-03,659801,65980110,1401); insert into student_3114006159 values(31140002,韩寒,男,1992-03-23,659802,65980210,1402); insert into student_3114006159 values(31140003,莫言,男,1997-06-15,659803,65980310,1403); insert into student_3114006159 values(31140004,三毛,女,1994-05-30,659804,65980410,1404); insert into student_3114006159 values(31140005,郭敬明,男,1995-12-03,659805,65980510,1405); insert into student_course_3114006159 values(31140001,1,96); insert into student_course_3114006159 values(31140002,3,67); insert into student_course_3114006159 values(31140003,2,91); insert into student_course_3114006159 values(31140004,4,89); insert into student_course_3114006159 values(31140005,5,96); 实验结果截图显示（插入数据的表格） 共8组（共8个表） (2)、用UPDATE语句更改student表中数据； 实现代码： use SC_3114006159 update student_3114006159 set sname=村上春树 where sname=莫言 student表更改前的内容截图显示查询分析 student表更改后的内容截图显示 (3)、用DELETE语句删除student表中数据； 实现代码： use SC_3114006159 delete from student_course_3114006159 where s_no in (select s_no from student_3114006159 where sname=郭敬明); delete from student_3114006159 where sname=郭敬明 student表更改前的内容截图显示查询分析 student表更改后的内容截图显示 五、实验中出现的问题及解决方案 在插入数据时没注意外键约束，结果导致插入失败，于是根据数据表关系图先插入没有外键约束的表，再根据外键约束关系按照一定顺序插入表。在删除student的元组时，因为其主键被student_course表外键所参照，所以无法删除，于是先删除student_course表中的元组，再删除student中的元组。 六、思考题 1、 说明数据库中的表和数据文件的关系。 答：表存储于数据文件中，一个数据文件可以存储多个表。 2、 数据库中的日志文件能否单独修改? 不能，SQL Server数据库都有log日志文件，log日志文件记录用户对数据库修改的操作，只有数据库发生更改时，日志文件才会发生更改，不能单独更改。 3、 附加练习题.(代码以及运行结果采用截图显示) (1) 将计算机专业所有学生的数据库原理的成绩增加10分 代码以及运行结果： (2) 删除计算机专业所有学生的数据库原理的选修信息 代码以及运行结果： (3) 为speciality的spname添加唯一约束 代码以及运行结果: (4) 为student_course的score设置检查约束 代码以及运行结果: 实验题目 实验二 查询数据库 一、实验目的 熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询 1、掌握基本的SELECT查询及其相关子句的使用； 2、掌握复杂的SELECT查询，如多表查询、子查询、连接查询和嵌套查询。 二、实验内容和要求 使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。 三、实验主要仪器设备和材料 1．计算机及操作系统：PC机，Windows 2000/xp； 2．数据库管理系统：SQL sever 2000/2003/2005； 四、实验方法、步骤及结果测试 要求用SELECT完成以下查询， 1、对实验一建立的数据库表进行查询 简单查询： （1）、查询全部学生的学号、姓名、性别和出身年月日。 实现代码及查询结果截图：格式如下 查询分析器执行情况： SQL语句及执行结果显示 use SC_3114006159 select s_no,sname,sex,sbirthday from student_3114006159 查询结果截图显示 （2）、查询全体学生的学号、姓名和年龄 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sex,year(getdate())-year(sbirthday) from student_3114006159 （3）、查询院系编号为‘510000’（计算机学院）的全部学生的学号、姓名、性别和出身年月日。 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sex,sbirthday from student_3114006159 where dno=659801 （4）、查询“计算机科学与技术”专业（专业代码为‘080605’）并且班级代码为‘0201’的学生的学号、姓名和出生日期。 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sbirthday from student_3114006159 where spno=65980110 and class_no=1401 （5）、查询在‘1983/01/10’以后出生的计算机学院（院系编号为‘510000’）学生的学号、姓名、性别和出身年月日。 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sex,sbirthday from student_3114006159 where dno=659801and sbirthday>1983-01-10 （6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sex,sbirthday from student_3114006159 order by sbirthday 连接查询： （1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称 实现代码及查询结果截图： use SC_3114006159 select s_no,sname,sex,dept_name,spname from student_3114006159,deparment_3114006159,speciality_3114006159 where student_3114006159.dno=deparment_3114006159.dno and deparment_3114006159.dno=speciality_3114006159.dno （2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩 实现代码及查询结果截图： use SC_3114006159 select student_3114006159.s_no,sname,spname,score from student_3114006159,student_course_3114006159,speciality_3114006159 where student_3114006159.s_no=student_course_3114006159.s_no and student_3114006159.spno=speciality_3114006159.spno and tcid=1 （3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。 实现代码及查询结果截图： use SC_3114006159 select student_3114006159.s_no,sname,cname from student_3114006159,student_course_3114006159,course_3114006159 where student_3114006159.s_no=student_course_3114006159.s_no and student_course_3114006159.tcid=course_3114006159.ctno and score<60 嵌套查询： （1）、查询没有选修了课程1的学生，列出学生的学号和姓名。 实现代码及查询结果截图： use SC_3114006159 select distinct student_3114006159.s_no,sname from student_3114006159,student_course_3114006159 where student_3114006159.s_no not in( select s_no from student_course_3114006159 where tcid=1) （2）、查询每门课都是80分以上的学生的学号与姓名。 实现代码及查询结果截图： use SC_3114006159 select distinct student_3114006159.s_no,sname from student_3114006159 where not exists( select * from student_course_3114006159 where student_3114006159.s_no=student_course_3114006159.s_no and score<80) 2、选用Northwind数据库进行查询 （1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句 以下部分查询结果截图由于表格过长，所以只截取了部分表格内容，无法完整截图整个表格。 完成单表查询： 查询所有Products的详细记录； 实现代码及查询结果截图： use Northwind select * from Products 查询单价（UnitPrice）小于20的Products； 实现代码及查询结果截图： use Northwind select * from Products where UnitPrice<20 查询Products中最高单价（UnitPrice）是多少； 实现代码及查询结果截图： use Northwind select max(UnitPrice) from Products （2）、在查询分析器在窗口下用SELECT语句完成连接（嵌套）查询： 查询所有被订购过得Products的ProductsID和ProductName； 实现代码及查询结果截图： use Northwind select distinct Products.ProductID,Products.ProductName from Products,[Order Details] where Products.ProductID=[Order Details].ProductID 查询所有被CustomerID为AROUT客和订购过的ProductsID和ProductName； 实现代码及查询结果截图： use Northwind select Products.ProductID,Products.ProductName from Products,[Order Details],Orders where Products.ProductID=[Order Details].ProductID and [Order Details].OrderID=Orders.OrderID and CustomerID=AROUT 查询所有被CustomerID为AROUT客和订购过，且单价在20 以上的ProductsID 和ProductName； 实现代码及查询结果截图： use Northwind select Products.ProductID,Products.ProductName from Products,[Order Details],Orders where Products.ProductID=[Order Details].ProductID and [Order Details].OrderID=Orders.OrderID and CustomerID=AROUT and Products.UnitPrice>20 查询Products中单价（UnitPrice）最高的Products的资料； 实现代码及查询结果截图： use Northwind select * from Products where UnitPrice>=all (select UnitPrice from Products); 五、实验中出现的问题及解决方案 六、思考题 1、 连接查询分哪几类？各有什么特点？ 答：连接查询可分为： 1. 等值连接与非等值连接：当where子句中的连接运算符为=时，称为等值连接，使用其它运算符为非等值连接。 2. 自然连接：在等值连接中把目标列中重复的属性列去掉则为自然连接。 3. 自身连接：一个表与自身连接，称为表的自身连接。 4. 外连接：两表连接时不存在公共属性上值相等的元组也保存在结果关系中，而在其他属性上填空值（NULL），那么连接就叫外连接。 5. 多表连接：两个表以上连接为多表连接。 2、 进行连接查询时应注意哪些问题？ 答：连接查询时应注意选择正确的连接谓词。 实验题目 实验三 创建和使用视图、索引、存储过程 一、实验目的 1、理解视图、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理； 2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程； 3、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。 4、掌握使用视图来查询数据。 二、实验主要仪器设备和材料 1．计算机及操作系统：PC机，Windows； 2．数据库管理系统：SQL Server 2005； 三、实验方法、步骤及结果测试 （一）、视图 1、建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。 2、执行ies_student_view视图并观察结果。 3、建立课程1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。 4、建立统计不及格情况的视图，列出不及格学生