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

数据库原理实验报告学 院 计算机 专 业_计算机科学与技术 班 级_ _7班 学 号 3110006131 姓 名 陈日燊 指导教师 明 俊 峰 （2012年 11月） 计算机 学院 计科 专业 07 班 学号：3110006131姓名： 陈日燊 协作者：_ 教师评定： 实验_一_题目_ 数据库及基本表的建立 实验_二_题目_ 设计数据完整性_ _ 实验_ 三_题目_ 查询数据库 _ 实验_ 四_题目_创建和使用视图、索引、存储过程 实验平台：实验一 数据库及基本表的建立一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表)、course表（课程信息表)、teacher表（教师信息表)、student _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_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)查询分析器执行情况：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 Smallint, dno CHAR(6) , FOREIGN KEY(dno) REFERENCES 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), FOREIGN KEY(spno) REFERENCES speciality(spno), ctno tinyint, 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, 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), 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(spno,class_no), header CHAR(8) );实验结果截图显示Department 表：Speciality 表：Student 表：Course 表：student_course 表：Teacher 表：teacher_course 表：Class 表：题目3、查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示题目4、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示向DEPARTMENT表添加5条记录USE SC GOINSERT INTO DEPARTMENT VALUES (01,计算机学院,小明)INSERT INTO DEPARTMENT VALUES (02,信息工程学院,小智)INSERT INTO DEPARTMENT VALUES (03,外国语学院,小天)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 INTO 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,网络工程);INSERT 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 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);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 INTO 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,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 VALUES (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 VALUES (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,NULL)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)、用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=xh08student表更改前后的内容截图显示执行前：执行后：(3)、用DELETE语句删除student表中数据；实现代码：查询分析器执行情况：SQL语句及执行结果显示事先插入了一条记录如右图所示删除记录代码：DELETE FROM STUDENT WHERE s_no=xh09student表更改前后的内容截图显示删除后五实验中出现的问题及解决方案插入数据的时候各种约束条件要很仔细的看清楚然后才可以进行数据的插入六、思考题说明数据库中的表和数据文件的关系。答：表（table）为数据库中数据存储的基本单位，其数据按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库中存储的数据，且一个数据库只有一个数据文件。表作为数据库中的数据存储单位，存储在数据文件中。数据库中的日志文件能否单独修改?答：可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生某种故障，即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。实验二、设计数据完整性一、实验目的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), 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 , credit 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), 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表create 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表create 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), foreign key(spno) references speciality(spno)student表course表create table course( cno char(10), cname char(20) not null, spno 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_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 table 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( 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 smallint , 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表、teacher_course表、class表的主键。2、表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“关系”选项卡中定义外键约束。（实验截图）3、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“索引/键”选项中定义专业信息表speciality的spname字段唯一约束。（实验截图）4、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“CHECK约束”选项中，为student_course表中的score列设置检查约束。例如：输入表达式：score=0 and score=1983-01-10查询结果截图显示:题目（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.spname 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_no 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 join student_course on (student.s_no=student_course.s_no ) join course on (student.spno=course.spno)where score 80 )查询结果截图显示:2、选用Northwin