广工2014数据库实验报告.doc

数据库原理实验报告学 院 专 业 班 级 学 号 姓 名 指导教师 （ 2014 年 12 月） 计算机 学院 专业 班 学号： 姓名： 协作者：_ 教师评定： 实验 一 题目 数据库及基本表的建立 实验_二_题目_ 设计数据完整性 _ 实验_三_题目 查询数据库 实验平台：SQL Server 2005、MySQL 计算机 学院 专业 4 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 一、 数据库及基本表的建立 一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2003/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库SC_xxxx(XXXX为学号后四位），其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表，表名为student_xxxx)、course表（课程信息表，表名为course_xxxx)、teacher表（教师信息表，表名为teacher_xxxx)、student _course表（学生选课成绩表，表名为student _course_xxxx)、teacher_course表（教师上课课表，表名为teacher_course_xxxx）等。（xxxx均指代实验者本人学号的后四位）1、创建数据库：确定数据库名称；数据库用于学生管理，命名为SC_XXXX（在每个截图中，应当都有此名称）确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句 create DATABASE sc_6298查询分析器执行情况：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、 对SC_XXXX进行备份与还原操作。(1)建立一个备份设备BK_XXXX，对应的物理文件名为.BK_XXXX.bak，路径自己设定。备份设备建立截图（2）为数据库SC_XXXX做完整备份至设备BK_XXXX.SC_XXXX数据库完整备份截图（3）删除SC_XXXX，从备份设备BK_XXXX中还原数据库SC_XXXX.从备份设备BK_XXXX还原SC_XXXX数据库截图 在实验室没看报告忘了截图！4、 查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示5、 创建各表的实现代码及截图：代码及图太多，仅以一个例子表示，以下类似。SQL语句create table Course(cno char(10) PRIMARY KEY,cname char(20) not null,spno char(8),FOREIGN KEY (spno) REFERENCES speciality(spno),otno tinyint,lecture tinyint,experiment tinyint,semester tinyint,credit tinyint)查询分析器执行情况：SQL语句及执行结果截图显示6、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示 插入数据：insert into student(s_no,sname,sex,sbirthday,class_no,dno,spno)values(00000001,刘产,男,1993/01/01,001,001,001);实验结果截图显示（插入数据的表格）共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：UPDATE studentset sname = 刘忙where s_no = 00000001student表更改前的内容截图显示查询分析student表更改后的内容截图显示(3)、用DELETE语句删除student表中数据；实现代码：DELETE FROM studentwhere sname = 刘忙student表更改前的内容截图显示查询分析student表更改后的内容截图显示五、实验中出现的问题及解决方案按照实验册顺序建表并不能全部建立起来，因为主键和外键的关系，必须先建立使外键存在的表，否则会出错。六、思考题1、 说明数据库中的表和数据文件的关系。答：表示按照数据文件逻辑关系所构建的二维标示图。2、 数据库中的日志文件能否单独修改?答：不能。 计算机 学院 专业 班 学号： 姓名： 协作者：_ _ 教师评定： 实验题目 二、 设计数据完整性 一、实验目的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 nchar(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)course表create table Course(cno char(10) PRIMARY KEY,cname char(20) not null,spno char(8),FOREIGN KEY (spno) REFERENCES speciality(spno),otno tinyint,lecture tinyint,experiment tinyint,semester tinyint,credit tinyint)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),foreign key (dno) references department(dno),tech_title char(10)teacher_course表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)Student_course表create table student_course(s_no nchar(8),FOREIGN KEY (s_no) REFERENCES student(s_no) on delete cascade,tcid smallint,primary key(s_no,tcid),score tinyint)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),FOREIGN KEY (dno) REFERENCES department(dno),spname char(20) not null)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) on delete cascade)2、定义以上各表的外键约束，并通过插入新的数据记录来检查外键约束。写出相应的SQL语句，并将检查执行的结果截图显示。外键定义的SQL语句：检查外键违约处理的结果截图：student表FOREIGN KEY (dno) REFERENCES department(dno),FOREIGN KEY (spno) REFERENCES speciality(spno),student表course表FOREIGN KEY (spno) REFERENCES speciality(spno),course表teacher表foreign key (dno) references department(dno),teacher表Speciality表FOREIGN KEY (dno) REFERENCES department(dno),Speciality表Student_course表FOREIGN KEY (s_no) REFERENCES student(s_no) on delete cascade,Student_course表3、定义唯一约束 为专业表Speciality的spname列建立唯一约束，并通过插入新的数据记录对新建的唯一约束进行检查，写出相应的SQL语句，并将检查的执行结果截图显示。定义的SQL语句：ALTER TABLE specialityADD UNIQUE(spname)检查违约处理的结果截图：4、定义检查约束为student_course表的Score列设置检查约束条件，并通过插入新的数据记录来检查约束。写出相应的SQL语句，并将检查执行结果截图显示。定义的SQL语句：create table student_course(s_no nchar(8),FOREIGN KEY (s_no) REFERENCES student(s_no) on delete cascade,tcid smallint,primary key(s_no,tcid),score tinyint CHECK(score = 0)检查违约处理的结果截图：5、查看各表之间的关系，生成数据库关系图。（二）、利用企业管理器，使用约束实现数据的完整性方法1、启动企业管理器，利用表设计器分别定义：student表、course表、student_course表、teacher表、department表、speciality表、teacher_course表、class表的主键。Student:Class表：Student表：Course表：Student_course表：Teacher表：Teacher_course表：Department表：Speciality表：2、 表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“关系”选项卡中定义外键约束。（实验截图）Student:Course表：Speciality：Student_course:Teacher:teacher_course:Class:Department:无外键。3、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“索引/键”选项中定义专业信息表speciality的spname字段唯一约束。（实验截图）4、在表设计器中，单击工具栏中的“表和索引属性”按钮，打开“属性”对话框，在“CHECK约束”选项中，为student_course表中的score列设置检查约束。例如：输入表达式：score=0 and score1983/01/01;（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。实现代码及查询结果截图：SELECT s_no,sname,sex,sbirthdayFROM studentORDER BY sbirthday ASC;连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称实现代码及查询结果截图：SELECT s_no,sname,sex,dept_name,spnameFROM student,department,specialityWHERE student.dno=department.dno and student.spno = speciality.spno；（2）、查询选修了课程1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩实现代码及查询结果截图：SELECT student.s_no,sname,spname,scoreFROM student,student_course,specialityWHERE student.s_no=student_course.s_no and student.spno = speciality.spno;（3）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。实现代码及查询结果截图：SELECT student.s_no,sname,cnameFROM student,student_course,courseWHERE student.s_no=student_course.s_no and student_course.score80)2、选用Northwind数据库进行查询（1）、对NothWind. Products表进行简单查询；在查询分析器在窗口下用SELECT语句完成单表查询：(截图结果太多，仅以一部分为例)查询所有Products的详细记录；实现代码及查询结果截图：Select *From products;查询单价（UnitPrice）小于20的Products；实现代码及查询结果截图：Select *From productsWhere Unitprice20 AND ProductID IN(SELECT ProductIDFROM order detailsWHERE OrderID IN(SELECT OrderIDFROM ordersWHERE CustomerID = AROUT)查询Products中单价（UnitPrice）最高的Products的资料；实现代码及查询结果截图：SELECT *FROM productsWHERE UnitPrice IN(SELECT MAX(UnitPrice)FROM products)五、实验中出现的问题及解决方案查询年龄时语句不知道怎么写，查询相关资料后解决；因为在机房不够时间做完全部实验，回到宿舍在自己电脑装的是MySQL，跟机房的sql service在很多方面有区别，包括数据类型、语法、系统函数等，在选用Northwind数据库做查询时下载的是SQL service导出的文件，导入MySQL时出现问题，找了非常多的解决方案还是没解决，最后下载了一个MySQL导出的数据库就可以做查询了。六、思考题1、 连接查询分哪几类？各有什么特点？ 答：四类。 （1）等值连接与非等值连接查询，当连接运算符为=时为等值连接，其他为非等值连接。 （2）自身连接，特点是表的自身与自身连接。 （3）外连接，可以保存不满足条件的元组并输出结果。 （4）符合连接，特点是在连接查询中，where有多个连接条件。2、 进行连接查询时应注意哪些问题？答：在进行表连接时选择表中相同的属性进行连接。