广东工业大学广东数据库原理实验报告.doc

1、数据库原理实验报告学 院 计算机 专 业_计算机科学与技术 班 级_ _7 班 学 号 3110006131 姓 名 陈日燊 指导教师 明 俊 峰 （2012 年 11 月）计算机 学院 计科 专业 07 班 学号：3110006131姓名： 陈日燊 协作者：_ 教师评定： 实验_一_题目_ 数据库及基本表的建立 实验_二_题目_ 设计数据完整性_ _ 实验_ 三_题目_ 查询数据库 _ 实验_ 四_题目_创建和使用视图、索引、存储过程 实验平台：实验一 数据库及基本表的建立一、实验目的1、掌握 SQL SERVER 的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和

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

3、tudent _course 表（学生选课成绩表)、teacher_course 表（教师上课课表）等。题目 1、创建数据库：实现代码及截图：查询分析器执行情况：SQL 语句及执行结果显示CREATE DATABASE SCON(NAME=SC_DAT,FILENAME=E:SC.mdf,SIZE=30MB,FILEGROWTH=20%)LOG ON(NAME=SC_LOG,FILENAME=E:SC.ldf,SIZE=2MB,FILEGROWTH=1MB)实验结果截图显示题目 2、创建基本表创建各表的实现代码及截图：(1)创建student表字段名 代码 类型 约束学号 s_no char(

4、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

5、课程学分 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)创建系部表

6、(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 cha

7、r(4)课程编号 cno char(10) 非空,外键学期 semester Char(6)学年 schoolyear Char(10)(8)创建班级表(class) 字段名 代码 类型 约束专业代码 spno char(8) 主键，与 speciality 表中spno 外键关联,班级编码 class_no char(4) 主键,班负责人 header char(8)查询分析器执行情况：SQL 语句及执行结果显示CREATE TABLE department(dno CHAR(6) PRIMARY KEY,dept_name CHAR(20) NOT NULL,header CHAR(8);

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 Smallint,dno CHAR(6) ,FOREIGN KEY(dno) REFE

9、RENCES department(dno),spno CHAR(8) ,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),实验结果截图显示Department 表：Speciality 表：Student 表：Course 表：FOREIGN KEY(spno) REFERENCES speciality(spno),ctno t

10、inyint,lecture tinyint,experiment tinyint,semester tinyint,credit tinyint);执行结果：命令已成功完成。CREATE TABLE student_course(s_no CHAR(8) ,CONSTRAINT HLM FOREIGN KEY(s_no) REFERENCES student(s_no),tcid smallint ,PRIMARY KEY(s_no,tcid),score tinyint);执行结果：命令已成功完成。CREATE TABLE teacher(t_no CHAR(8) PRIMARY KEY,

11、t_name CHAR(8) NOT NULL,t_sex CHAR(2),t_birthday Smallint,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),FOREIGN KEY(t_no) REFERENCES teacher(t_no),spno CHAR(8),student_course 表：Teacher 表：te

12、acher_course 表：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) ,FOREIGN KEY(spno) REFERENCES speciality(spno),class_no CHAR(4),PRIMARY KEY(sp

13、no,class_no),header CHAR(8) );Class 表：题目 3、查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示classspnoclass_noheaderCoursecnocnamespnoctnolectureexperimentsemesterdepartmentdnodept_nameheaderspecialityspnodnospnamestudents_nosnamesexsbirthdaydnospnoclass_nostudent_courses_notcidscoreteachert_not_namet_sext_birthdayd

14、notech_titleteacher_coursetcidt_nospnoclass_nocno题目 4、利用查询分析器修改上述各表。(1)、用 INSERT 语句向各个表 中插入数据录入 5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL 语句及执行结果显示向 DEPARTMENT 表添加 5 条记录USE SC GOINSERT INTO DEPARTMENT VALUES (01,计算机学院 ,小明)INSERT INTO DEPARTMENT VALUES (02,信息工程学院 ,小智)INSERT INTO DEPARTMENT VALUES (03,外

15、国语学院 ,小天)INSERT INTO DEPARTMENT VALUES (04,艺术学院 ,小唯)INSERT INTO DEPARTMENT VALUES (05,数学学院 ,小野)向 TEACHER 表添加 5 条记录INSERT INTO TEACHER VALUES (js01,刘德华,男,1978-02-25,01,NULL);INSERT INTO TEACHER VALUES (js02,黎明,男,1982-05-15,02,NULL);INSERT INTO TEACHER VALUES (js03,梅艳芳,女,1987-04-07,03,NULL);INSERT INT

16、O TEACHER VALUES (js04,邓丽君,女,1985-07-08,04,NULL);INSERT INTO TEACHER VALUES (js05,张学友,男,1984-11-23,05,NULL);向 SPECIALITY 表添加 5 条记录USE SC GOINSERT INTO SPECIALITY VALUES 实验结果截图显示（插入数据的表格）(zy01,01,计算机科学与技术);INSERT INTO SPECIALITY VALUES (zy02,01,软件工程);INSERT INTO SPECIALITY VALUES (zy03,01,网络工程);INSER

17、T INTO SPECIALITY VALUES (zy04,02,操作系统);INSERT INTO SPECIALITY VALUES (zy05,03,大学英语);INSERT INTO SPECIALITY VALUES (zy06,04,大学物理);INSERT INTO SPECIALITY VALUES (zy07,05,离散数学);INSERT INTO 向 STUDENT 表添加 5 条记录USE SC GOINSERT INTO STUDENT VALUES (xh01,林俊杰 ,男,1991-05-01,01,zy01,0902);INSERT INTO STUDENT

18、VALUES (xh02,陈奕迅 ,女,1991-07-10,04,zy06,NULL);INSERT INTO STUDENT VALUES (xh03,柯南,男,1991-04-08,01,zy03,0901);INSERT INTO STUDENT VALUES (xh04,小纯一郎 ,男,1991-07-15,02,zy04,NULL);INSERT INTO STUDENT VALUES (xh05,娜美,女,1994-03-26,04,zy06,NULL);INSERT INTO STUDENT VALUES (xh06,周杰伦 ,男,1991-07-03,03,zy05,0905

19、);INSERT INTO STUDENT VALUES (xh07,周迅,女,1986-03-11,05,zy07,NULL);INSERT INTO STUDENT VALUES (xh08,小嶋阳菜 ,女,1988-04-19,03,zy05,0902);向 COURSE 表添加 5 条记录USE SC GOINSERT INTO COURSE VALUES (kc01,数据库,zy01,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc02,图形学,zy01,NULL,NULL,NULL,NULL,NULL)INSERT INT

20、O COURSE VALUES (kc03,C+编程,zy02,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc04,计算机网络,zy03,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc05,工程绘图,zy04,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc06,日语基础,zy05,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc07,绘图要领,zy06

21、,NULL,NULL,NULL,NULL,NULL)INSERT INTO COURSE VALUES (kc08,离散数学,zy07,NULL,NULL,NULL,NULL,NULL)向 CLASS 表添加 5 条记录USE SC GOINSERT INTO CLASS VALUES (zy01,0902,小东);INSERT INTO CLASS VALUES (zy02,0901,小莉);INSERT INTO CLASS VALUES (zy03,0901,小红);INSERT INTO CLASS VALUES (zy04,1003,小周);INSERT INTO CLASS VAL

22、UES (zy05,0905,小陈);INSERT INTO CLASS VALUES (zy06,0804,小天);INSERT INTO CLASS VALUES (zy07,0904,小伟);向TEACHER_COURSE 表添加 5 条记录USE SC GOINSERT INTO TEACHER_COURSE VALUES (01,js01,zy01,0902,kc01,NULL,NULL)INSERT INTO TEACHER_COURSE VALUES (02,js02,zy04,1003,kc05,NULL,NULL)INSERT INTO TEACHER_COURSE VALU

23、ES (03,js03,zy05,0905,kc06,NULL,NULL)INSERT INTO TEACHER_COURSE VALUES (04,js04,zy06,0804,kc07,NULL,NULL)INSERT INTO TEACHER_COURSE VALUES (05,js05,zy07,0904,kc08,NULL,NULL)向 STUDENT_COURSE 表添加 5 条记录USE SC GOINSERT INTO STUDENT_COURSE VALUES (xh01,1,NULL)INSERT INTO STUDENT_COURSE VALUES (xh02,4,NUL

24、L)INSERT INTO STUDENT_COURSE VALUES (xh03,6,NULL)INSERT INTO STUDENT_COURSE VALUES (xh04,2,NULL)INSERT INTO STUDENT_COURSE VALUES (xh05,4,NULL)INSERT INTO STUDENT_COURSE VALUES (xh06,3,NULL)INSERT INTO STUDENT_COURSE VALUES (xh07,5,NULL)INSERT INTO STUDENT_COURSE VALUES (xh08,3,NULL)共 8 组（共 8 个表）(2)

25、、用 UPDATE 语句更改 student 表中数据；实现代码：查询分析器执行情况：SQL 语句及执行结果显示USE SC GOUPDATE STUDENT SET class_no=0804 WHERE s_no=xh02UPDATE STUDENT SET class_no=1003 WHERE s_no=xh04UPDATE STUDENT SET class_no=0804 WHERE s_no=xh05UPDATE STUDENT SET class_no=0904 WHERE s_no=xh07UPDATE STUDENT SET class_no=0905 WHERE s_no

26、=xh08student 表更改前后的内容截图显示执行前：执行后：(3)、用 DELETE 语句删除 student 表中数据；实现代码：查询分析器执行情况：SQL 语句及执行结果显示事先插入了一条记录如右图所示删除记录代码：DELETE FROM STUDENT WHERE s_no=xh09student 表更改前后的内容截图显示删除后五实验中出现的问题及解决方案插入数据的时候各种约束条件要很仔细的看清楚然后才可以进行数据的插入六、思考题说明数据库中的表和数据文件的关系。答：表（table）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据

27、类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。表作为数据库中的数据存储单位，存储在数据文件中。数据库中的日志文件能否单独修改?答：可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生某种故障，即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。实验二、设计数据完整性一、实验目的1、掌握使用约束实现数据完整性的方法；2、掌握使用触发器实现数据完整性的方法；二、实验内容和要求1、设置主键约束、设置唯一约束、设置外键约束、设置

28、检查约束、设置默认值约束；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

29、 table student(s_no char(8),sname char(8) not null,sex char(2),sbirthday smalldatetime,dno char(6),spno char(8),class_no char(4),primary key(s_no)course 表create table course(cno char(10) ,cname char(20) not null,spno char(8) ,ctno tinyint ,lecture tinyint ,experiment tinyint ,semester tinyint ,credi

30、t tinyint ,primary key(cno)teacher 表create table teacher(t_no char(8) ,t_name char(8) not null,t_sex char(2),t_birthday smalldatetime,dno char(6) ,tech_title char(10),primary key(t_no)teacher_course 表create table teacher_course(tcid smallint ,t_no char(8) ,spno char(8) ,class_no char(4) ,cno char(10

31、),semester char(6) not null,shcoolyear char(10),primary key(tcid)Student_course 表create table student_course(s_no char(8) ,tcid smallint ,score tinyint ,primary key(s_no,tcid),)Department 表create table department(dno char(6) ,dept_name char(20) not null,header char(8)primary key(dno)Speciality 表crea

32、te table speciality(spno char(8) ,dno char(6) not null ,spname char(20) not null,primary key(spno)Class 表create table class(spno char(8) ,class_no char(4) ,header char(8) , primary key (spno,class_no)2、 定义以上各表的外键约束，并通过插入新的数据记录来检查外键约束。写出相应的SQL 语句，并将检查执行的结果截图显示。外键定义的 SQL 语句： 检查外键违约处理的结果截图：student 表cre

33、ate table student( s_no char(8) ,sname char(8) not null,sex char(2),sbirthday smalldatetime,dno char(6),spno char(8),class_no char(4),foreign key(dno) references department(dno), student 表foreign key(spno) references speciality(spno)course 表create table course(cno char(10),cname char(20) not null,sp

34、no char(8) ,ctno tinyint ,lecture tinyint ,experiment tinyint ,semester tinyint ,credit tinyint ,constraint fk_course_spno foreign key(spno) references speciality(spno)course 表teacher 表create table teacher(t_no char(8) ,t_name char(8) not null,t_sex char(2),t_birthday smalldatetime,dno char(6) ,tech

35、_title char(10),constraint fk_teacher_dno foreign key(dno) references department(dno)teacher 表Speciality 表create table speciality(spno char(8) ,dno char(6) not null ,spname char(20) not null,constraint fk_speciality_dno foreign key(dno) references department(dno)Speciality 表Student_course 表create ta

36、ble student_course(s_no char(8) ,tcid smallint ,score tinyint ,constraint fk_student_course_s_no foreign key(s_no) references student(s_no) on delete cascade)Student_course 表3、定义唯一约束为专业表 Speciality 的 spname 列建立唯一约束，并通过插入新的数据记录对新建的唯一约束进行检查，写出相应的 SQL 语句，并将检查的执行结果截图显示。定义的 SQL 语句：create table speciality

37、(spno char(8) ,dno char(6) not null ,spname char(20) not null,unique,constraint fk_speciality_dno foreign key(dno) references department(dno)检查违约处理的结果截图：4、定义检查约束为 student_course 表的 Score 列设置检查约束条件，并通过插入新的数据记录来检查约束。写出相应的 SQL 语句，并将检查执行结果截图显示。定义的 SQL 语句：create table student_course(s_no char(8) ,tcid sm

38、allint ,score tinyint check(score between 0 and 100),constraint fk_student_course_s_no foreign key(s_no) references student(s_no) on delete cascade)检查违约处理的结果截图：5、查看各表之间的关系，生成数据库关系图。（二） 、利用企业管理器，使用约束实现数据的完整性方法1、启动企业管理器，利用表设计器分别定义：student 表、course 表、student_course 表、teacher 表、department 表、speciality 表

39、、teacher_course 表、class 表的主键。2、表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“关系”选项卡中定义外键约束。 （实验截图）3、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“索引/键”选项中定义专业信息表 speciality 的 spname 字段唯一约束。 （实验截图）4、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“CHECK 约束”选项中，为 student_course 表中的 score 列设置检查约束。例如：输入表达式：score=0 and score=1983-01-1

40、0查询结果截图显示:题目（6） 、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：SQL 语句 :USE SCGOSELECT s_no,sname,sex,sbirthdayFROM studentORDER BY sbirthday ASC查询结果截图显示:连接查询：题目（1） 、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：SQL 语句 :USE SCGOSelect student.s_no,student.sname,student.sex,department.dept_name,speciality.sp

41、name from student,department,speciality where student.dno = department.dno and student.spno=speciality.spno查询结果截图显示:题目（2 ）、查询选修了课程1 （上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：SQL 语句 :USE SCGOSelect student.s_no,sname,class_no,tcid,score from student,student_course where student.s_no = student_course.s_n

42、o AND student_course.tcid=1查询结果截图显示:题目（3 ）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：SQL 语句 :USE SCGOselect student.s_no,sname, cname,scorefrom student join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course.spno)select student.s_no,sname, cnamefrom student

43、join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course.spno)where score 80 )查询结果截图显示:2、选用 Northwind 数据库进行查询题目（1 ） 、对 NothWind. Products 表进行简单查询；在查询分析器在窗口下用SELECT 语句完成单表查询：查询所有 Products 的详细记录；实现代码及查询结果截图：SQL 语句 :USE NorthwindGOSELECT * FROM Products查询结果截图显示:查询单

44、价（UnitPrice）小于 20 的 Products；实现代码及查询结果截图：SQL 语句 :USE NorthwindGOSELECT * FROM ProductsWHERE UnitPrice20查询结果截图显示:查询结果均小于 20查询 Products 中最高单价（UnitPrice）是多少；实现代码及查询结果截图：SQL 语句 :USE NorthwindGOselect max(unitprice) from products查询结果截图显示:题目（2 ） 、在查询分析器在窗口下用 SELECT 语句完成连接（嵌套）查询：查询所有被订购过得 Products 的 ProductsID 和 ProductName；实现代码及查询结果截图：SQL 语句 :USE NorthwindGO