[工学]2012数据库原理实验.doc

计算机 学院 计科 专业 5 班 学号：3110006058 姓名： 王翊剑 协作者：_ 教师评定： 实验题目 一、 数据库及基本表的建立 一、实验目的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、创建数据库：确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句CREATE DATABASE SCON(name=SC,filename=E:DMSsc.mdf,size=30MB,filegrowth=20%)LOG ON(name=SC_log,filename=E:DMSSCLOG.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 ,spname char(20) not null,foreign key (dno) references department(dno);create table student(s_no char(8) primary key,sname char(8) not null,sex char(2),dno char(6),spno char(8),class_no char(4),sbirthday Smalldatetime,foreign key(spno) references speciality(spno),foreign key (dno)references department(dno);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 tinyint,foreign key(spno) references speciality(spno);create table student_course(s_no char(8) ,tcid smallint ,score tinyint,primary key(s_no,tcid),foreign key(s_no) references student(s_no) on delete cascade );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),foreign key(dno) references department(dno);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 char(10),foreign key (t_no) references teacher(t_no),foreign key (spno) references speciality(spno),foreign key (cno) references Course(cno);create table class(spno char(8),class_no char(4) ,header char(8),primary key(spno,class_no),foreign key(spno)references speciality(spno);查询分析器执行情况：SQL语句及执行结果截图显示5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示insert into department values(1,计算机系,唐一); insert into department values (2,自动化系,唐二); insert into department values(3,信工系,唐三); insert into department values(4,机电系,唐四); insert into department values(5,通信系,唐五);insert into speciality values(10,1,计科); insert into speciality values(11,2,软件); insert into speciality values(12,3,化工); insert into speciality values (13,4,自动化); insert into speciality values(14,5,通信);insert into student values(1011,王一,男,1,10,1,1992-1-1);insert into student values (1012,王二,男,1,11,1,1992-1-2); insert into student values (1013,王三,女,2,13,2,1992-1-3); insert into student values (1014,王四,女,3,12,1,1992-1-4); insert into student values (1015,王五,男,5,14,1,1992-1-5)insert into Course values(01,数据结构,10,1,5,4,1,2); insert into Course values (02,编译原理,11,2,5,4,1,2); insert into Course values (03,操作系统,12,3,5,4,1,2); insert into Course values (04,嵌入式,13,4,5,4,1,2); insert into Course values (05,数据库,14,5,5,4,1,2);insert into student_course values(1011,100,60); insert into student_course values (1012,101,60); insert into student_course values (1013,102,60); insert into student_course values (1014,103,60); insert into student_course values(1015,104,60);insert into teacher values(001,张一,男,19850101,1,教师); insert into teacher values (002,张二,男,19850102,2,教授); insert into teacher values (003,张三,女,19850103,3,教师); insert into teacher values (004,张四,女,19850104,4,副教授); insert into teacher values(005,张五,男,19850105,5,教师);insert into teacher_course values(100,001,10,1,01,1,1); insert into teacher_course values(101,002,10,1,02,1,1); insert into teacher_course values(102,003,12,1,03,1,1); insert into teacher_course values(103,004,12,1,04,1,1); insert into teacher_course values(104,005,14,1,05,1,1);insert into class values(10,1,苏一); insert into class values (11,2,苏二); insert into class values(12,3,苏三); insert into class values (13,4,苏四); insert into class values (14,5,苏五)实验结果截图显示（插入数据的表格）因为长度太长，所以截图两半同上截图两半 因为太长，同样截成两半同上截两半共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：select s_no,sbirthdayfrom studentwhere s_no=1011;update studentset sbirthday=1993/1/1where s_no=1011;student表更改前的内容截图显示查询分析student表更改后的内容截图显示(3)、用DELETE语句删除student表中数据；实现代码：delete from studentwhere s_no=1011;student表更改前的内容截图显示查询分析student表更改后的内容截图显示五、实验中出现的问题及解决方案1.按照实验指导书的顺序写好代码执行，发现一大堆错误。原因及解决：因为主键与外键的关系，因而某些表必须先于外键表先构建，不然数据出错，按照主外键原则的顺序构建即可2.在进行插入语句时，发现不能插入数据原因及解决：一条insert语句只能插入一个元组，不能批插入。在每个元组前加入insert语句。3.在student表的插入数据时，发现系统总是提醒字符截断原因及解决：其实是疏忽大意，因为列属性的值是按照代码的顺序生成的，而我是按着实验指导书的顺序插入数据，因而出错。只要按代码顺序插入即可。4.执行提醒student表与student-course表插入冲突原因及解决：这是因为软件在执行两个相关联的代码时并不一定是一前一后地顺序构建成功，因为当前表没有构建成功时，而后表在构建时将因为无关联数据而出错。解决方法是按照表的关联顺序一个一个插入数据。六、思考题1、 说明数据库中的表和数据文件的关系。答：表是按照数据文件的逻辑关系所构建的二维的标示图。2、 数据库中的日志文件能否单独修改?答。不能。33 计算机 学院 计科 专业 5 班 学号： 3110006058 姓名： 王翊剑 协作者：_ 教师评定： 实验题目 二、 设计数据完整性 一、实验目的1、掌握使用约束实现数据完整性的方法；2、掌握使用触发器实现数据完整性的方法；二、实验内容和要求1、设置主键约束、设置唯一约束、设置外键约束、设置检查约束、设置默认值约束；2、使用企业管理器创建触发器、使用SQL语音创建触发器；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试（一）、使用约束实现数据的完整性（针对实验一中的所建的基本表）要求：1、 定义：student表、course表、student_course表、teacher表、department表、speciality表、teacher_course表、class表的主键。写出相应的SQL语句。student表create table student (s_no char(8) primary key, sname char(8) not null, sex char(2), dno char(6), spno char(8), class_no char(4), sbirthday Smalldatetime, foreign key(spno) references speciality(spno), foreign key (dno)references department(dno) );course表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 tinyint, foreign key(spno) references speciality(spno);teacher表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), foreign key(dno) references department(dno);teacher_course表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 char(10), foreign key (t_no) references teacher(t_no), foreign key (spno) references speciality(spno), foreign key (cno) references Course(cno);Student_course表create table student_course (s_no char(8) , tcid smallint , score tinyint, primary key(s_no,tcid), foreign key(s_no) references student(s_no) on delete cascade );Department表create table department (dno char(6) primary key, dept_name char(20) not null, header char(8);Speciality表create table speciality (spno char(8) primary key, dno char(6) not null , spname char(20) not null, foreign key (dno) references department(dno) );Class表create table class (spno char(8), class_no char(4) , header char(8), primary key(spno,class_no), foreign key(spno)references speciality(spno) );2、定义以上各表的外键约束，并通过插入新的数据记录来检查外键约束。写出相应的SQL语句，并将检查执行的结果截图显示。外键定义的SQL语句：检查外键违约处理的结果截图：student表foreign key(spno) references speciality(spno), foreign key (dno)references department(dno)student表insert into student values(1016,王六,女,7,13,3,1993-2-2);insert into student values(1017,王七,女,3,17,3,1993-2-2);course表foreign key(spno) references speciality(spno)course表use SCinsert into Course values(06,西方哲学史,16,5,5,4,1,2);teacher表foreign key(dno) references department(dno)teacher表use SCinsert into teacher values(006,张六,男,1993-2-2,6,教授);Speciality表foreign key (dno) references department(dno)Speciality表use SCinsert into speciality values(15,6,外国语);Student_course表foreign key(s_no) references student(s_no) on delete cascade )Student_course表use SCinsert into student_course values(1016,102,70);3、定义唯一约束 为专业表Speciality的spname列建立唯一约束，并通过插入新的数据记录对新建的唯一约束进行检查，写出相应的SQL语句，并将检查的执行结果截图显示。定义的SQL语句：create table speciality (spno char(8) primary key, dno char(6) not null , spname char(20) unique, foreign key (dno) references department(dno)）；检查违约处理的结果截图：insert into speciality values(15,1,计科);4、定义检查约束为student_course表的Score列设置检查约束条件，并通过插入新的数据记录来检查约束。写出相应的SQL语句，并将检查执行结果截图显示。定义的SQL语句：create table student_course (s_no char(8) , tcid smallint , score tinyint check(score=0 and score=0 and score1983/1/1;截图：（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：use scselect s_no,sname,sex,sbirthdayfrom studentorder by sbirthday asc;截图：连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：use scselect s_no,sname,sex,dept_name,spnamefrom student,department,specialitywhere student.dno=department.dno and student.spno=speciality.spno ;截图：（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：use scselect student.s_no,sname,spname,scorefrom student,student_course,specialitywhere student.s_no=student_course.s_no and student.spno=speciality.spno ;截图：（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：use scselect student.s_no,sname,cnofrom student,student_course,coursewhere student.s_no=student_course.s_no and student.s_no=student_course.s_no and score80);截图：2、选用Northwind数据库进行查询（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图： use Northwindselect *from products;截图： 查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：use Northwindselect *from productswhere UnitPrice20 and ProductID in (select ProductID from Order Details where OrderID in (select OrderID from Orders where CustomerID=AROUT);截图：查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：use Northwindselect *from productswhere UnitPrice in(select max(UnitPrice) from products);截图：五、实验中出现的问题及解决方案1.无法绑定有多个部分组成的标示符原因及解决：之所以无法绑定是因为标示符的表示出现错误，改正即可。2.在进行嵌套查询时出现重复列原因及解决：因为嵌套查询会重复扫描，因而出现重复元组，解决方法是在要查询的属性列前加上DISTINCT就可以消除重复列的显示了3在进行嵌套查询的时候，常常出现无效ID原因及解决：主要有两个原因，一是因为疏忽大意对属性列的拼写错误，二是因为在嵌套查询时层次间链接的ID并非是所要求的链接，因而查询出错，原因在于疏忽与理解。仔细检查以及清晰理解嵌套层次。六、思考题1、 连接查询分哪几类？各有什么特点？答：分四类。一 等值与非等值连接查询 特点是以连接运算符作为区分，若运算符是=则是等值连接，其他运算符是非等值连接二 自身连接 特点是一个表与其自身连接三 外连接 特点是可保存不满足连接的条件的元组并输出结果四 符合连接 特点是在连接查询中，where有等多个连接条件2、 进行连接查询时应注意哪些问题？答：连接查询是指在一个查询的同时涉及多个表，因而注意的问题是一 涉及表的个数二 涉及表的属性列三 在表与表的连接注意其共同属性。 计算机 学院 计科 专业 5 班 学号：3110006058 姓名： 王翊剑 协作者：_ 教师评定： 实验题目 四、 创建和使用视图、索引、存储过程 一、实验目的1、理解视图、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；3、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL Server 2000；三、实验方法、步骤及结果测试（一）、视图1、建立“计算机学院”的学生基本情况视图ies_student