广工数据库实验报告.doc

数据库应用实验报告学 院 计算机学院 专 业 计算机科学与技术 班 级 _ 学 号 姓 名 指导教师 顾国生 ( 2015 年 1 月) 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验_一_题目_ 数据库及基本表的建立 实验 二 题目 查询数据库 实验 三 题目 创建和使用视图、索引 实验平台：sql server 2005 Win7 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 数据库及基本表的建立 _ 实验项目名称：数据库及基本表的建立实验项目性质：验证型所属课程名称：数据库应用实验计划学时：3一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表)、course表（课程信息表)、teacher表（教师信息表)、student _course表（学生选课成绩表)、teacher_course表（教师上课课表）等。1、创建数据库：确定数据库名称；数据库用于学生管理，命名为SC确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。要求：（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句CRETE DATABASE SCON(NAME = SC_DB;FILENAME = E:DATASC_DATA.mdf,SIZE = 30MB,FILENAME = 20%)LOG ON(NAME = SC_LOG,FILENAME = E:DATASC_DATA.ldf,SIZE = 3MB,FILEGROWTH = 1MB)查询分析器执行情况：SQL语句及执行结果截图显示2、创建基本表利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。(1) 创建student表字段名代码类型约束学号s_nochar(8)主键姓名snamechar(8)非空性别sexchar(2) 出生日期sbirthdaySmalldatetime学生所在院系编号dnochar(6) 外键专业代码spnochar(8)外键班级编码class_nochar(4) (2)创建Course表字段名代码类型约束课程编号cnochar(10)主键课程名称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_sexchar(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主键教师编号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语句CREATE TABLE Department(DNo CHAR(6) PRIMARY KEY,Dept_Name CHAR(20) NOT NULL,Header CHAR(8);CREATE TABLE Speciality(SpNo CHAR(8) PRIMARY KEY,DNo CHAR(6) NOT NULL,FOREIGN KEY (DNo)REFERENCES Department(DNo) ,SpName char(20) NOT NULL);CREATE TABLE Student(S_no CHAR(8) PRIMARY KEY,Sname CHAR(8) NOT NULL,Sex CHAR(2),SBirthday Smalldatetime,DNo CHAR(6),SpNO CHAR(8),FOREIGN KEY (DNo) REFERENCES Department(DNo),FOREIGN KEY (SpNO) REFERENCES Speciality(SpNo),Class_no CHAR(4);CREATE TABLE Course(Cno CHAR(10) PRIMARY KEY,Cname CHAR(20) NOT NULL,SpNo CHAR(8),FOREIGN KEY (SpNo) REFERENCES Speciality(SpNo),CtNO TINYINT,Lecture TINYINT,Experiment TINYINT,Semester TINYINT,Credit TINYINT);CREATE TABLE Student_course(S_no CHAR(8),PRIMARY KEY(S_no, TcId),FOREIGN KEY (S_no) REFERENCES Student(S_no) ON DELETE CASCADE,TcId SMALLINT ,Score TINYINT);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),FOREIGN KEY (DNo) REFERENCES Department(DNo),Tech_Title CHAR(10);CREATE TABLE Teacher_course(TcId SMALLINT PRIMARY KEY,T_No CHAR(8),SpNo CHAR(8),FOREIGN KEY (T_No) REFERENCES Teacher(T_NO),FOREIGN KEY (SpNo) REFERENCES Speciality(SpNo),Class_No CHAR(4),CNo CHAR(10) NOT NULL,FOREIGN KEY (CNo) REFERENCES Course(CNo) ,Semester CHAR(6),Schoolyear CHAR(10);CREATE TABLE Class(SpNo CHAR(8),Class_No CHAR(4) ,PRIMARY KEY(Class_No,SpNo),FOREIGN KEY (SpNo) REFERENCES Speciality(SpNo) ,Header CHAR(8); 查询分析器执行情况：SQL语句及执行结果截图显示5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示insert into department(dno,dept_name,header) values(50012,外国语,sharon)insert into department(dno,dept_name,header) values(50017,机电工程,steve)insert into department(dno,dept_name,header) values(50015,自动化,jackson)insert into department(dno,dept_name,header) values(50025,电子商务,janet)insert into department(dno,dept_name,header) values(50001,计算机,peter);insert into speciality(spno,dno,spname)values(KB001,50001,软件工程) insert into speciality(spno,dno,spname)values(KB011,50025,电子商务) insert into speciality(spno,dno,spname)values(KB079,50012,德语) insert into speciality(spno,dno,spname)values(KB045,50017,微电子工程) insert into speciality(spno,dno,spname)values(KB036,50015,机械与自动化);insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(WG001,Lily,女,1985-11-08,50012,实习讲师)insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(JD015,Smith,男,1970-2-23,50017,高级讲师) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(CS079,Jobs,男,1980-1-20,50001,系主任) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(ZD012,Sam,男,1972-11-04,50015,副教授) insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)values(DZ030,Janet,女,1982-10-18,50025,教授);insert into class(spno ,class_no,header)values(KB079,1307,王红)insert into class(spno ,class_no,header)values(KB045,1205,张强) insert into class(spno ,class_no,header)values(KB036,1103,李彪) insert into class(spno ,class_no,header)values(KB011,1206,曾江) insert into class(spno ,class_no,header)values(KB001,1310,国彬);insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(130721,王红,女,1994-05-10,50012,KB079,1307)insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(120518,张强,男,1993-10-05,50017,KB045,1205) insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(110329,李彪,男,1993-10-05,50015,KB036,1103) insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(120622,曾江,男,1993-10-05,50025,KB011,1206) insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(131025,国彬,男,1993-10-05,50001,KB001,1310);insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(CSC701,JAVA程序设计,KB001,2,50,15,2,4)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(ZDC812,自动控制原理,KB036,2,50,15,2,3)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(WGC011,德语入门,KB079,1,56,10,1,4)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(JDC025,机械设计,KB045,2,30,20,1,3)insert into course(cno,cname,spno,ctno,lecture,experiment,semester,credit)values(DZC701,电子商务概论,KB011,1,30,5,2,5)insert into student_course(s_no,tcid,score)values(130721,1,90)insert into student_course(s_no,tcid,score)values(120518,2,75)insert into student_course(s_no,tcid,score)values(110329,5,80)insert into student_course(s_no,tcid,score)values(120622,3,55)insert into student_course(s_no,tcid,score)values(131025,4,96)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(4,CS079,KB001,1310,CSC701,2,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(5,ZD012,KB036,1103,ZDC812,2,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(1,WG001,KB079,1307,WGC011,1,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(2,JD015,KB045,1205,JDC025,1,2013-2014)insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)values(3,DZ030,KB011,1206,DZC701,2,2013-2014)实验结果截图显示（插入数据的表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：student表更改前的内容截图显示查询分析update student set sbirthday = 1994-10-10 where s_no = 130721student表更改后的内容截图显示修改前：修改后：(3)、用DELETE语句删除student表中数据；实现代码：student表更改前的内容截图显示查询分析delete from student_course where s_no = 130721delete from student where s_no = 130721student表更改后的内容截图显示删除前：student_course表student表删除后：student_course表student表五、实验中出现的问题及解决方案删除student表的时候出现以下情况：消息547，级别16，状态0，第102 行DELETE 语句与REFERENCE 约束FK_student_co_s_no_0EA330E9冲突。该冲突发生于数据库SC，表dbo.student_course, column s_no。语句已终止。因为没有级联删除导致违反了参照完整性。student_course表的s_no充当外码，它是与student的s_no主码相对应的。当用户删除了student表当中某个元组时，应该将student_course里面与被删除学生相关的选课信息也删除掉，否则的话student_course里的外码在student表中找不到与之对应的元组，因而违反参照完整性。修改前的代码：delete from student where s_no = 130721修改后的代码： delete from student_course where s_no = 130721delete from student where s_no = 130721六、思考题1.说明数据库中的表和数据文件的关系。数据文件是数据库的存放位置，数据库是数据表的存放位置，数据文件与数据表之间是需要数据库来做承接的。表为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。 数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。 2.数据库中的日志文件能否单独修改? 可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生某种故障，即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 查询数据库 _ 实验项目名称：使用SQL语言查询管理数据实验项目性质：基础型所属课程名称：数据库应用实验计划学时：2一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询1、掌握基本的SELECT查询及其相关子句的使用；2、掌握复杂的SELECT查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL sever 2005；四、实验方法、步骤及结果测试要求用SELECT完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirthday from student查询结果截图显示（2）、根据学号=“你的学号”查询对应元组的姓名和年龄。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示 select s_no,sname,year(getdate() - year(sbirthday) from student where s_no = 110329查询结果截图显示 （3）、查询院系编号为50017（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirthday from student where dno = 50017查询结果截图显示（4）、查询“计算机科学与技术”专业（专业代码为50001）并且班级代码为1310的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sbirthday from student where spno= 50001 and class_no =1310查询结果截图显示 （5）、查询在1996-11-11以后出生的计算机学院（院系编号为510000）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示为了检验，又插入了一条学生记录到student表insert into student(s_no ,sname,sex,sbirthday,dno,spno,class_no)values(131027,合子,男,1996-11-11,51000,KB001,1310);select s_no,sname,sbirthday from student where dno = 510000 and sbirthday 1996-11-11查询结果截图显示 （6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,sbirthday from student order by sbirthday asc查询结果截图显示连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select s_no,sname,sex,dept_name,spname from student,department,specialitywhere student.dno = department.dno and student.spno = speciality.spno查询结果截图显示（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select student_course.s_no,sname,dept_name,score from student,student_course,departmentwhere student.s_no = student_course.s_no and student.dno = department.dno and tcid = 1查询结果截图显示（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示select student.s_no,sname,cname from student,course,student_course where score 80)查询结果截图显示 五、实验中出现的问题及解决方案答：1.这次实验出现的问题主要是一些题目要求的数据本身在实验一的时候是没有的，所以必须去插入一些测试记录，一边测试。 2.where后面不能有聚集函数，改用group by .having. ，前者是作用于基本表和视图，选出满足条件的元组，后者是作用于分组，用于选择满足条件的组。六、思考题1、 连接查询分哪几类？各有什么特点？一个查询同时涉及多个表，则称为连接查询。连接查询分为等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件查询等。连接查询的where字句中用来连接两个表的条件成为连接条件和连接谓词。格式为： “表1.列1 比较运算符 表2.列2” ，列名就是连接字段（1） 等值与非等值连接查询 当比较运算符为“=”时就是等值连接，使用其他运算符就是非等值连接 （2）自然连接查询 特殊的等值连接，两个关系中进行比较的分量必须是同名的属性组，在结果中把重复的属性列去掉 （3）外连接查询自然连接的时候，两个关系R,S在公共属性上值相等的元组构成新的关系。此时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，被舍弃，同样S中的某些元组也会被舍弃，这些被舍弃的元组就是悬浮元组。把悬浮元组也保存在结果关系中，而在悬浮元组的其他属性上填NULL，就叫做外连接。只保留左边关系R的悬浮元组叫做左外连接，只保留右边关系S的悬浮元组叫做右外连接。 （4）复合条件查询涉及多个表或者多个查询条件的查询2、 进行连接查询时应注意哪些问题？（1）根据问题判断这个查询最少需要几张表，可以根据查询的字段和要筛选的条件是进行判断；（2）判断要连接的表之间是否存在公共字段，如果有就可以连接，如果没有那么就要考虑需要一个中间表了 ； （3）实施连接，要明确2表关联的公共字段是哪个字段 ； （4）连接成功之后的查询结果是一张大表，同样可以完成筛选，排序，分组，嵌入聚合函数等操作。 计算机 学院 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 创建和使用视图、索引 _ 实验项目名称：创建和使用视图实验项目性质：基础型所属课程名称：数据库应用实验计划学时：3一、实验目的1、理解视图、索引的定义、索引的优点与、索引的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引；3、掌握创建视图、索引的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows7；2数据库管理系统：SQL Server 2005；三、实验方法、步骤及结果测试（一）、视图1、建立“计算机学院”的学生基本情况视图ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行ies_student_view视图并观察结果。3、建立课程1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图ies_student_view，使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。7、执行ies_student_view视图并观察结果。8、删除视图ies_student_view。并查询结果。记录上述实验过程并截图说明。创建视图的SQL语句及执行结果显示create view ies_student_viewas select s_no,sname,sex,sbirthday,spname from student,department,specialitywhere student.spno = speciality.spno and student.dno = department.dno and department.dept_name = 计算机with check option查询视图的SQL语句及执行结果截图创建视图的SQL语句及执行结果显示create view sc_2_view as select student.s_no,sname,spname,scorefrom student,speciality,student_coursewhere student.s_no = student_course.s_no and student.spno = speciality.spno and tcid =1with check optioncreate view no_pass_student_cno_viewas select student.s_no,sname,course.cnofrom student,student_course,coursewhere student.s_no = student_course.s_no and score 60 and student.spno = course.spno with check option查询视图的SQL语句及执行结果截图select * from ies_student_view修改视图的SQL语句及执行结果显示alter view ies_student_viewas select student.s_no,sname,sex,sbirthday,spname,dept_namefrom student,speciality,departmentwhere student.spno = speciality.spnoand student.dno = department.dno with check option查询视图的SQL语句及执行结果截图删除视图的SQL语句及执行结果显示drop view ies_student_view查询视图SQL语句及执行结果截图（通过查询语句检查该视图，系统会返回该视图不存在的信息）select * from ies_student_view（二）、索引（1）、在student_course表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index (2)通过SQL语句查看运行结果创建索引的SQL语句及执行结果显示create unique index sc_sno_index on student_course(s_no)SQL语句及运行结果截图Sp_helpindex student_course实验方法：（1）、视图（使用企业管理器）1、 启动企业管理器，选择要创建视图的数据库SC并展开；2、 执行“工具”/“向导”命令；3、 选择数据库SC；4、 选择数据库SC，选择表student、department。5、 选择student表中的sno snamesex sbirthday字段和speciality表中的spname字段；6、 在“定义限制”对话框中输入WHERE子句来限制视图的信息；7、 命名视图：ies_sc_view8、 使用视图ies_sc_view。在查询分析器中运用SQL语句查询视图。（2）、索引（使用企业管理器）1、启动企业管理器，选择要创建索引的数据库SC并展开；2、执行“工具”/“向导”命令；3、展开“数据库”文件夹，双击“创建索引向导”4、选择希望为其建立索引的数据库和对象（如数据库SC中的student表）5、查看有关当前索引的信息。选择一个或多个将被包含在索引中的列6、指定索引选项。四、思考题1、视图更新的优点答：（1）简化用户用户操作（2）使用户能以多种角度看待同一个数据（3）对重构数据库提供了一定程度的逻辑独立性（4）对机密数据提供安全保护（5）适当的利用视图可以更清晰地表达查询，因此，视图更新会比表更新有更多的好处。2、哪些视图是可以更新的？哪些视图是不可以更新的？针对实验举例说明。1）没有指定DISTINCT，即重复元组未从查询结果中消除；2）定义查询的SELECT列表中的每个元素均为列名（而不是常量，表达式或聚合函数），且列名出现不能多于一次；3）FROM子句只能指定一个表，即视图必须有一个源表且用户有请求该表的权限。如果源表本身就是一个视图，那么视图必须满足这些条件。因此，排除了基于连接、并（UNION）、交（INTERSECT）或差（EXCEPT）操作的所有视图；4）where子句不能包含任何引用了FROM子句的表的嵌套SELECT操作。5）定义查询不能有GROUP BY或HAVING子句。举例：本次实验的ies_student_view和no_pass_student_cno_view基于连接，所以无法做更新操作