数据库原理课程设计实验报告.doc

数据库原理实验报告学 院 计算机学院 专 业 计算机科学与技术 班 级 2班 学 号 姓 名 指导教师 （200 9年 6 月） 计算机 学院 计算机科学与技术 专业 2 班 学号： 姓名： 协作者：_ 教师评定： 实验_一_题目 数据库及基本表的建立 实验_二_题目 查询数据库 实验_三_题目 创建和使用视图、索引和存储过程 实验_四_题目 数据库设计 实验平台：1计算机及操作系统：PC机，Windows XP SP32数据库管理系统：SQL Server 2000； 计算机 学院 计算机科学与技术 专业 2 班 学号：3106006475 姓名： 杨振风 协作者：_ 教师评定： 实验题目_数据库及基本表的建立 实验一 数据库及基本表的建立实验项目名称：数据库及基本表的建立实验项目性质：验证型所属课程名称：数据库原理实验计划学时：2一、实验目的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语句指定参数创建数据库；实现代码及截图：CREATE database SC on primary( name=SC_DATA,filename=E:mySC_DATA.mdf,size=30MB,maxsize=unlimited,filegrowth=10%)log on(name=SC_LOG,filename=E:mySC_LOG.df,size=3MB, maxsize=unlimited, filegrowth=1MB)GO查询分析器执行情况：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语句USE SCcreate 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), sbirthday smalldatetime, dno char(6), spno char(8), class char(4), foreign key (dno) references department(dno), foreign key (spno) references speciality(spno);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);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语句及执行结果显示use SCinsert into departmentvalues(dno001,计算机学院,陈向杰 );insert into departmentvalues(dno002,自动化学院,杨佳妮 );insert into departmentvalues(dno003,物理学院,李成才 );insert into departmentvalues(dno004,机电学院,王成军 );insert into departmentvalues(dno005,轻化学院,罗伟民 );insert into departmentvalues(dno006,环境学院,杨小明 );insert into specialityvalues(spno001,dno001,计科);insert into specialityvalues(spno002,dno002,自动化);insert into specialityvalues(spno003,dno003,软件);insert into specialityvalues(spno004,dno001,网络);insert into specialityvalues(spno005,dno006,高分子);insert into studentvalues(31001,杨某,男,1987-08-24,dno001,spno001,0601);insert into studentvalues(31002,李某,女,1988-05-14,dno002,spno004,0602);insert into studentvalues(31003,罗某,男,1987-06-14,dno003,spno002,0602);insert into studentvalues(31004,王某,女,1986-01-02,dno005,spno003,0605);insert into studentvalues(310005,陈某,男,1987-10-30,dno001,spno004,0606);insert into coursevalues(cno001,数据库,spno001,01,20,10,2,1);insert into coursevalues(cno002,离散数学,spno005,02,30,0,2,3);insert into coursevalues(cno003,数值计算,spno002,03,25,15,2,4);insert into coursevalues(cno004,编译原理,spno003,04,50,30,2,2);insert into coursevalues(cno005,数据结构,spno004,05,40,30,2,5);insert into student_coursevalues(31001,10,80);insert into student_coursevalues(31002,20,40);insert into student_coursevalues(31003,30,90);insert into student_coursevalues(31004,40,70);insert into student_coursevalues(31005,60,60);insert into teachervalues(41001,黄某,男,1965-01-10,dno001,教授);insert into teachervalues(41002,刘某,男,1968-01-10,dno001,副教授);insert into teachervalues(41003,何某,男,1967-01-10,dno001,教授);insert into teachervalues(41004,郑某,女,1969-10-10,dno001,副教授);insert into teachervalues(41005,朱某,女,1965-01-10,dno001,教授);insert into teacher_coursevalues(11,41001,spno001,02,cno005,5,3);insert into teacher_coursevalues(21,41002,spno004,05,cno001,3,5);insert into teacher_coursevalues(31,41003,spno002,04,cno005,4,3);insert into teacher_coursevalues(41,41001,spno003,01,cno002,5,1);insert into teacher_coursevalues(51,41004,spno002,07,cno004,1,2);insert into classvalues(spno001,01,向某);insert into classvalues(spno005,05,陈某);insert into classvalues(spno004,04,李某);insert into classvalues(spno003,03,向某);insert into classvalues(spno002,02,杨某);实验结果截图显示（插入数据的表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：use SCupdate studentset sex=女where s_no=31003;student表更改前的内容截图显示查询分析student表更改后的内容截图显示(3)、用DELETE语句删除student表中数据；实现代码：use SCdelete from studentwhere s_no=31004;student表更改前的内容截图显示查询分析student表更改后的内容截图显示五、实验中出现的问题及解决方案六、思考题1、 说明数据库中的表和数据文件的关系。答：一个表空间可以包含若干个数据文件，一个数据文件只能属于一个表空间。 一个表只能存放在一个表空间里，同时可以存放在这个表空间的多个数据文件中。2、 数据库中的日志文件能否单独修改?答：日志文件不能单独修改，日志文件是记录数据库的操作过程以及对数据的备份。 计算机 学院 计算机科学与技术 专业 2 班 学号：3106006475 姓名： 杨振风 协作者：_ 教师评定： 实验题目 查询数据库 实验二 查询数据库实验项目名称：使用SQL语言查询管理数据实验项目性质：基础型所属课程名称：数据库原理实验计划学时：2一、实验目的熟悉SQL语句的基本使用方法，学习如何编写SQL语句来实现查询1、掌握基本的SELECT查询及其相关子句的使用；2、掌握复杂的SELECT查询，如多表查询、子查询、连接查询和嵌套查询。二、实验内容和要求使用SQL查询分析器查询数据，练习查询语句的使用，掌握SELECT 语句的完整结构，包括简单查询、嵌套查询、连接查询等基本实现方法，掌握存储查询结果的方法，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试要求用SELECT完成以下查询， 1、对实验一建立的数据库表进行查询简单查询：（1）、查询全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：格式如下查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sex,sbirthdayfrom student;查询结果截图显示（2）、查询全体学生的学号、姓名和年龄实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,year(getdate()-year(sbirthday)from student;查询结果截图显示（3）、查询院系编号为510000（计算机学院）的全部学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sex,sbirthdayfrom studentwhere dno=510000;查询结果截图显示（4）、查询“计算机科学与技术”专业（专业代码为080605）并且班级代码为0201的学生的学号、姓名和出生日期。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sbirthdayfrom studentwhere spno=080605 AND classM=0201;查询结果截图显示（5）、查询在1983/01/10以后出生的计算机学院（院系编号为510000）学生的学号、姓名、性别和出身年月日。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sex,sbirthdayfrom studentwhere year(sbirthday)-year(1983-01-30)0 AND month(sbirthday)-month(1983-01-30)0 AND day(sbirthday)-day(1983-01-30)0 AND dno=51000;查询结果截图显示（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sex,sbirthdayfrom studentorder by sbirthday asc;查询结果截图显示连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,sname,sex,department.dept_name,speciality.spnamefrom student,department,specialitywhere student.dno=department.dno AND student.spno=speciality.spno;查询结果截图显示（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect student.s_no,sname,speciality.spname,student_course.scorefrom student,student_course,specialitywhere student.spno=speciality.spno AND student.s_no=student_course.s_no AND student_course.score=1;查询结果截图显示（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect student.s_no,student.sname,ame,student_course.scorefrom student,course, student_course,specialitywhere student.s_no=student_course.s_no and course.spno=speciality.spnoand speciality.spno=student.spno and student_course.score60;查询结果截图显示嵌套查询：（1）、查询没有选修了课程1的学生，列出学生的学号和姓名。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,snamefrom studentwhere not exists( select * from student_course where student.s_no=student_course.s_no and tcid=1);查询结果截图显示（2）、查询每门课都是80分以上的学生的学号与姓名。实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use SCselect s_no,snamefrom studentwhere s_no in(select s_nofrom student_course xwhere not exists(select *from student_course ywhere x.s_no=y.s_no and score80);查询结果截图显示2、选用Northwind数据库进行查询（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：查询所有Products的详细记录；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use Northwindselect *from Products;查询结果截图显示查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示use Northwindselect *from Productswhere UnitPrice20;查询结果截图显示查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：查询分析器执行情况：SQL语句及执行结果显示USE Northwindselect *from Productswhere UnitPrice in(select MAX(UnitPrice)from Products);查询结果截图显示五、实验中出现的问题及解决方案六、思考题1、 连接查询分哪几类？各有什么特点？答：1、等值与非等值连接查询，连接条件中的各个连接字段类型必须是可比的，但名字不必相同 2、自身连接，连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接。3、外连接，把舍弃的元祖也保存在结果关系中，而其他属性上填空值。4、复合条件连接，where子句可以有多个连接条件。2、 进行连接查询时应注意哪些问题？答：连接字段必须是一致的。 计算机 学院 计算机科学与技术 专业 2 班 学号：3106006475 姓名： 杨振风 协作者：_ 教师评定： 实验题目 创建和使用视图、索引和存储过程 实验三、创建和使用视图、索引、存储过程实验项目名称：创建和使用视图实验项目性质：基础型所属课程名称：数据库原理实验计划学时：2一、实验目的1、理解视图、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；3、掌握创建视图、索引、存储过程的SQL语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows；2数据库管理系统：SQL Server 2000；三、实验方法、步骤及结果测试（一）、视图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语句及执行结果显示use SCGOcreate view ies_student_viewas select s_no,sname,sex,sbirthday,speciality.spnamefrom student,department,specialitywhere student.spno =speciality.spnoand student.dno =department.dnoanddepartment.dept_name=计算机学院查询视图SQL语句及执行结果截图查询分析器执行情况：创建视图的SQL语句及执行结果显示use SCGOcreate view course2as select student.s_no,sname,speciality.spname,student_course.scorefrom student,student_course,specialitywhere student.s_no=student_course.s_noand student_course.tcid=1and student.spno=speciality.spno查询视图SQL语句及执行结果截图查询分析器执行情况：修改视图的SQL语句及执行结果显示use SCGOcreate view failas select student.s_no,student.sname,ofrom student,course,student_course,specialitywhere student.s_no=student_course.s_noand student.spno=speciality.spnoand speciality.spno=course.spnoand student_course.score60查询视图SQL语句及执行结果截图查询分析器执行情况：删除视图的SQL语句及执行结果显示use SCGOcreate view ies_student_viewas select s_no,sname,sex,sbirthday,department.dept_name,speciality.spnamefrom student,department,specialitywhere student.spno =speciality.spnoand student.dno =department.dnoanddepartment.dept_name=计算机学院查询视图SQL语句及执行结果截图查询分析器执行情况：删除视图的SQL语句及执行结果显示use SCgodrop view ies_student_view查询视图SQL语句及执行结果截图（二）、索引（1）、在student_course表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index(2)通过SQL语句查看运行结果查询分析器执行情况：创建索引的SQL语句及执行结果显示create unique index sc_sno_indexon student_course(s_no);exec sp_helpindex student_coursego查询视图SQL语句及执行结果截图（三）、存储过程（1）、创建存储过程list_student_department,该存储过程接收学院代码作为输入参数，列出数据库student中某个院系学生的全部信息。实验过程:(1) 启动查询分析器，选择SC为当前数据库(2) 在编辑窗口输入SQL语句(3) 、按F5执行该语句查询分析器执行情况：SQL语句create proc list_student_departmentdno char(6)=计算机学院asselect *from studentgo执行结果截图显示（2）、使用SQL语句执行存储过程list_student_department 实验过程:（1）、启动查询分析器，选择SC为当前数据库（2）、在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句exec list_student_department执行结果截图显示（3）、将某班选修某门课程的所有学生的学号和上课编号查入到student_course表中实验过程:（1）、启动查询分析器，选择SC为当前数据库（2）、在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句create procedure inserts_student_course class char(4),cno char(10)As insert into student_course (s_no,tcid)Select student.s_no,tcid from student inner join teacher_courseon student.class=teacher_course.class_no where teacher_course.class_no=classno and cno=c_no;执行结果截图显示（4）、使用SQL语句执行存储过程实验过程:（1）、启动查询分析器，选择SC为当前数据库（2）、在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句use SCexec inserts_student_course0601,cno001执行结果截图显示（5）、使用SQL语句查看存储过程代码实验过程:(4) 启动查询分析器，选择SC为当前数据库(5) 在编辑窗口输入SQL语句(6) 、按F5执行该语句查询分析器执行情况：SQL语句use SCexec sp_helptext inserts_student_course执行结果截图显示（6）、使用SQL语句修改存储过程实验过程:（1）、启动查询分析器，选择SC为当前数据库（2）、在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句alter procedure inserts_student_coursedno char(6)asselect s_no,snamefrom studentwhere dno=dno001;执行结果截图显示（7）、使用SQL语句删除存储过程实验过程:（1）、启动查询分析器，选择SC为当前数据库（2）、在编辑窗口输入SQL语句（3）按F5执行该语句查询分析器执行情况：SQL语句drop procedure inserts_student_course执行结果截图显示实验方法：（1）、视图（使用企业管理器）1、 启动企业管理器，选择要创建视图的数据库SC并展开；2、 执行“工具”/“向导”命令；3、 选择数据库SC；4、 选择数据库SC，选择表student、department。5、 选择student表中的sno sname