电子科技大学 数据库系统及应用实验报告.doc

计算机专业类课程实验报告课程名称：数据库系统及应用学院：计算机科学与工程学院专业：计算机科学与技术学生姓名：朋施羽学号：指导教师：日期：2014 年 4月21日电 子 科 技 大 学实 验 报 告实验一1、 实验名称：创建数据库2、 实验学时：43、 实验内容和目的：实验内容：(1) 启动SQLSERVER(2) 创建数据库：STUD(3) 创建表(4) 插入样本数据(5) 备份数据库(6) 恢复数据库“系别代码表“表名：dep“教师表”表名：teacher“学生表”表名：student“课程表”表名：course“选课表”表名：sc为每个表准备大约10记录，使用Insert语句将这些数据插入到相应表中数据录入完成后，将数据库备份到磁盘上，在以后的的试验中备用。实验目的:本实验要求学生掌握创建数据库的方法及相关操作，创建数据库，向数据库中添加样本数据，学习SQLSERVER数据库的恢复和备份。4、 实验原理：使用数据库管理系统DB、DDL创建数据库及数据库对象。5、 实验器材（设备、元器件）操作系统：Win7数据库 ： MS SQLSERVER6、 实验步骤：(1) 创建数据库：STUDCreate database stud;(2) 创建表，表的详细说明见表格.“系别代码表“表名：dep其结构如下：（该表的主键为“系代码”）字段名称字段类型字段大小/格式是否可为空约束条件系代码depidVARCHAR8否PK系名depnameVARCHAR20否Not null “教师表”表名：teacher其结构如下：（该表的主键为“教师号”）字段名称字段类型字段大小/格式是否可为空约束条件教师号tidVARCHAR8否PK教师名tnameVARCHAR8否Not null职称titleVARCHAR8是所属院系编号depidVARCHAR8是“学生表”表名：student其结构如下：（该表的主键为“学生号”）字段名称字段类型字段大小/格式是否可为空约束条件学号sidVARCHAR11否PK学生名snameVARCHAR8否Not null院系编号depidVARCHAR8是性别sexVARCHAR2否出生年月birthdDATETIME是邮箱semailVARCHAR20是家庭地址homeaddrVARCHAR40是“课程表”表名：course其结构如下：（该表的主键是课程号）字段名称字段类型字段大小/格式是否可为空约束条件课程号cidVARCHAR8否PK课程名cnameVARCHAR30否Not null学分creditsDECIMAL3,1(小数位数1)否Not null“选课表”表名：sc其结构如下：（该表的主键是课程号）字段名称字段类型字段大小/格式是否可为空约束条件学号sidVARCHAR11否Not null, PK课程号cidVARCHAR8否Not null, PK教师号tidVARCHAR8否Not null成绩scoreDECIMAL3,1(小数位数1)是(3) 备份数据库BACKUP DATABASE stud TO DISK = d:stud.bak WITH INIT;(4) 恢复数据库RESTORE DATABASE stud FROM DISK = d:stud.bak WITH REPLACE;7、 实验数据及结果分析：(1) 启动SQLSERVER(2) 创建数据库：STUD create database stud;(3) 创建表代码：use stud;create table dep(depid varchar(8) primary key,depname varchar(20) not null);create table teacher(tid varchar(8) primary key,tname varchar(8) not null,title varchar(8),depid varchar(8);create table student(sid varchar(11) primary key,sname varchar(8) not null,depid varchar(8),sex varchar(2) not null,birthd datetime,semail varchar(20),homeaddr varchar(40);create table course(cid varchar(8) primary key,cname varchar(30) not null,credits decimal(3,1) not null);create table sc(sid varchar(11) not null,cid varchar(8) not null,tid varchar(8) not null,score decimal(3,1),primary key(sid,cid);截图：图1-1 创建的数据库和表(4) 插入样本数据代码：insert into teacher values(6012,熊教授,教授,601),(6021,刘教授,教授,602),(6032,盖茨,副教授,603),(6074,汤米,讲师,604),(6059,王淑娟,教授,609),(6033,蒋亚辉,讲师,610),(6097,任慧磊,讲师,607),(6014,徐叉叉,副教授,608),(6082,段梅梅,讲师,606),(6053,杨晓,副教授,605);insert into student values(2406010101,张三,男,601,1981/8/8,23534,沙河庙),(2406010102,张四,男,601,1982/1/1,23234,大美路),(2406010103,张五,女,601,1983/2/2,,清水河),(2406020101,王三,男,602,1984/12/12,,保研路),(2406020102,王四,男,602,1985/6/6,,银杏路);insert into student(sid,sname,sex,depid,birthd) values(2406030101,李六,男,603,1986/9/9),(2406040101,王二,女,604,1981/3/9),(2406050101,蒋二,女,605,1982/4/9),(2406090101,段三,男,609,1986/9/23),(2406080101,秦四,男,608,1982/8/14);insert into course values(1,计算机组成原理,4),(2,数据结构,4),(3,数据库,2),(4,微积分,6),(5,数学实验,2),(6,大学英语,4),(7,计算机操作系统,4.5),(8,计算机系统结构,2.5),(9,嵌入式系统及应用,3),(10,编译原理,4);insert into sc(sid,cid,tid) values(2406010101,1,6012),(2406010102,1,6012),(2406010103,1,6012),(2406020101,1,6012),(2406020102,1,6012),(2406030101,1,6012),(2406010101,2,6021),(2406010102,2,6021),(2406010103,2,6021),(2406020101,2,6021),(2406020102,2,6021),(2406030101,2,6021),(2406010101,3,6032),(2406010102,3,6032),(2406010103,3,6032),(2406020101,3,6032),(2406020102,3,6032),(2406030101,3,6032),(2406010103,4,6074),(2406010103,5,6033),(2406010103,6,6057),(2406010103,7,6053),(2406010103,8,6014),(2406010103,9,6097),(2406010103,10,6059);截图：图1-2 插入数据后的teacher表图1-3 插入数据后的dep表图1-4 插入数据后的course表图1-5 插入数据后的sc表图1-6 插入数据后的student表(5) 备份数据库backup database stud to disk=d:stud.bak with init;图1-7 备份数据库后产生的文件(6) 恢复数据库 restore database stud from disk=e:stud.bak with replace;8、 实验结论、心得体会和改进建议： 最后成功建数据库、建表，并在表中插入了数据。了解到，在SqlServer中若要在某个数据库中插入数据，首先要新建该数据库的查询，或者使用ues该条语句，否则可能会自动创建到系统数据库中。同时，插入数据的时候要注意顺序，如果某表中有外键，则应该先向其参考的被参考表中插入数据。电 子 科 技 大 学实 验 报 告实验二1、 实验名称：数据库的完整性2、 实验学时：43、 实验内容和目的：实验内容：设置约束条件l 设置教师表，学生表中的院系字段(depid)的外键约束l 设置选课表的三个外键约束（学号，课程号，教师号）l 设置选课表中成绩字段的取值范围是0到100l 设置学生表中性别字段的取值为“男”或“女”l 设置学生表电子邮件字段的取值必须包含符号实验目的：通过设置表的检查约束、外键约束体会数据库完整性的含义，约束条件下数据修改操作的限制，以及实现修改操作的技巧。4、 实验原理：数据库库的完整性、约束条件、结构化查询语言。5、 实验器材（设备、元器件）操作系统：Win7数据库 ： MS SQLSERVER6、 实验步骤：(1) 恢复数据库，将数据库备份stud 恢复到DB2中(2) 执行SQL命令完成实验内容(3) 备份数据库7、 实验数据及结果分析：(1) 恢复数据库，将数据库备份stud 恢复到DB2中BACKUP DATABASE stud TO DISK = d:stud.bak WITH INIT;(2) 执行SQL命令完成实验内容l 设置教师表，学生表中的院系字段(depid)的外键约束alter table teacher add foreign key(depid) references dep(depid);alter table student add foreign key(depid) references dep(depid);图2-1 teacher表的外键约束图2-2 student表的外键约束l 设置选课表的三个外键约束（学号，课程号，教师号）alter table sc add foreign key(sid) references student(sid);alter table sc add foreign key(cid) references course(cid);alter table sc add foreign key(tid) references teacher(tid);l 设置选课表中成绩字段的取值范围是0到100alter table sc add check(score=0 and score=100);图2-3 sc表的外键约束和check约束l 设置学生表中性别字段的取值为“男”或“女”alter table student add check(sex in(男,女);l 设置学生表电子邮件字段的取值必须包含符号alter table student add check(semail like%);图2-4 student表的check约束(3) 备份数据库RESTORE DATABASE stud FROM DISK = d:stud.bak WITH REPLACE;八、 实验结论、心得体会和改进建议： 成功的为表添加约束，同时应该注意，可以灵活的使用通配符来达到要求。这里提出一点改进建议，在添加约束的时候，可以考虑为其取名，方便下次修改。电 子 科 技 大 学实 验 报 告实验三1、 实验名称：数据查询2、 实验学时：43、 实验内容和目的：实验内容：(1) 查询年龄在2022之间的学生姓名（通过出生日期和当前日期计算年龄）year(getdate()-year(birthd)(2) 查询所有副教授的信息(3) 查询姓“张”的学生的学号、姓名、邮件地址(4) 求每门课程的选课人数、最高分、最低分、平均分，要求在查询结果中显示课程号和课程名；(5) 查询既选修了1号课程，又选修了2号课程的学生学号(6) 查询既选修了1号课程，又选修了2号课程的学生姓名(7) 查询选修了全部课程的学生姓名实验目的：练习用SELECT查询语句，设置查询条件，实现单表查询。练习使用SELECT语句从多个表中查询数据，表的内连接、左外连接、右外连接的使用以及设置连接条件，理解连接条件和查询条件的在目的和功能上的区别。4、 实验原理：结构化查询语言、表的连接、关系运算、分组查询5、 实验器材（设备、元器件）操作系统：Win7数据库 ： MS SQLSERVER6、 实验步骤：(1) 恢复数据库，将数据库备份stud 恢复到DB2中(2) 执行SQL命令完成实验内容七、实验数据及结果分析：(1) 恢复数据库，将数据库备份stud 恢复到DB2中BACKUP DATABASE stud TO DISK = d:stud.bak WITH INIT;(2) 执行SQL命令完成实验内容l 查询年龄在2022之间的学生姓名（通过出生日期和当前日期计算年龄）year(getdate()-year(birthd)select sname from student where year(getdate()-year(birthd) between 20 and 22;图3-1 20-22岁学生姓名的查询结果l 查询所有副教授的信息 select * from teacher where title like 副教授;图3-2 副教授信息的查询结果l 查询姓“张”的学生的学号、姓名、邮件地址select sid 学号,sname 姓名,semail 邮件地址 from student where sname like 张%;图3-3 张姓同学的信息查询结果l 求每门课程的选课人数、最高分、最低分、平均分，要求在查询结果中显示课程号和课程名；select course.cid 课程号,cname 课程名,count(*) 选课人数,max(score) 最高分,min(score) 最低分,avg(score) 平均分 from course,sc where course.cid=sc.cid group by course.cid,cname;图3-4 各门课程的查询结果l 查询既选修了1号课程，又选修了2号课程的学生学号select a.sid from sc a,sc b where a.sid=b.sid and a.cid=1 and b.cid=2;图3-5 选修了1号课程和2号课程学生的学号l 查询既选修了1号课程，又选修了2号课程的学生姓名select sname from student,sc a,sc b where a.cid=1 and b.cid=2 and a.sid=b.sid and a.sid=student.sid;图3-6 选修了1号课程和2号课程学生的姓名l 查询选修了全部课程的学生姓名select sname from student where not exists(select * from course where not exists(select * from sc where sc.cid=course.cid and student.sid=sc.sid);图3-7 选修了全部课程的学生姓名八、实验结论、心得体会和改进建议：成功查找出结果，在查找的过程中灵活的运用连接非常方便。并且在能用连接的时候尽量不要用子查询，因为连接的速度通常要比子查询快。在最后一个查询选修了全部课程的学生名单的时候，运用了两个not exists相当于双重否定，得到了结果。查询的时候，适当给列取别名，可以使得查询结果更加清晰明白。电 子 科 技 大 学实 验 报 告实验四1、 实验名称：数据库建模2、 实验学时：43、 实验内容和目的：实验内容：(1) 使用PDM，以图形化界面方式创建表及确定各表之间的关系，在实验报告中给出模型图；(2) 通过“生成数据库”功能生成创建数据库的脚本，在实验报告中给出脚本内容；实验目的：本实验要求学生学习数据库建模工具PowerDesigner的使用方法，掌握最基本的使用方法。4、 实验原理：使用图形化CASE工具设计数据库，ER图5、 实验器材（设备、元器件）操作系统：Win7应用软件：Power Designer 15.16、 实验步骤：1.通过生成Physical Data Model（PDM）以图形化界面创建表及确定各表之间的关系。(1)打开了PD以后，选择New，并在Model type里面选择Physical Data Model，同时选择可使用的DBMS，如 DB2或My SQL 4.0，然后点击确定，如图1所示。图 1(2)从Palette工具面板中单击创建Table 的工具图标，并在图表窗口中点击一下便可产生一个新的Table（默认名称为Table_1，可修改该名称），如图2所示：图2 (3)选中该表，右击选择”Properties”对该表进行编辑（修改名称、增加列等）如图3所示：图 3(4)选择”Columns”标签，如图3中的红色矩形所示。便可以给student这个表添加列了。在添中列的时候应该指明列的类型，并指是否允许为空，是否为主键等各种属性，如图4所示：图 4(5)按生成student表同样的办法生成department表，只是具体属性不一样，如图5所示：图 5(6)学生表中的学生所在系是department表中的一个外键，这时我们需要建立一个表的引用。如图6所示：图 6(7)这时候可以发现student表中多了一个dno字段，并且有这种外键标识，这样表也就建好了。2.根据第一步生成的表导出数据库的脚本要产生数据库生成脚本，进行如下操作：(1)选择DatabaseGenerate Database，打开“Parameters for Sybase SQL Anywhere”窗口(图7)。(2)在File name 框中接受缺省的文件名crebas.sql，在Directory 框中接受缺省的目录名。(3)接受其它缺省的生成参数和选项。图 7(4)单击“Generate script”按钮，打开如图 8所示的确认框。图8(5)单击“是（Y）”按钮。(6)打开数据库生成脚本文件，并浏览这个文件。这个脚本文件就是我们通过图形化的界面创建的表及其之间的关系的一个SQL描述，我们可以在应用程序中或者相应的DBMS导入这个脚本就可以完成数据库的创建了（在例子中的这个脚本只有表相关的创建语句）。3.利用PD的逆向工程生成PDM并进行修改逆向工程的实验我们以从数据库生成脚本生成PDM为例。从数据库生成脚本生成PDM，按下列步骤进行：(1)选择FileReverse EngineeringDatabase”窗口。新窗口如图9所示：图 9(2)从“Database name”下拉列表框中选择数据库管理系统。如图9的红圈所示，并点击确定。(3)单击“Using a script file”单选按钮，并添加一个SQL脚本文件，如图10所示：图 10(4)单击“OK”，新的PDM 出现在窗口中，如图11所示：这时可按照步骤1介绍的方法进行图形化界面的修改，并生成新的SQL脚本文件。7、 实验数据及结果分析：(1) 通过生成Physical Data Model（PDM）以图形化界面创建表及确定各表之间的关系。图4-1 用PDM得到的模型图(2) 根据第一步生成的表导出数据库的脚本图4-2 得到的数据库脚本，并且用SqlServer打开得到的脚本内容如下：/*=*/* DBMS name: Microsoft SQL Server 2008 */* Created on: 2014/4/28 13:02:44 */*=*/if exists (select 1 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(sc) and = FK_SC_REFERENCE_STUDENT)alter table sc drop constraint FK_SC_REFERENCE_STUDENTgoif exists (select 1 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(sc) and = FK_SC_REFERENCE_TEACHER)alter table sc drop constraint FK_SC_REFERENCE_TEACHERgoif exists (select 1 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(sc) and = FK_SC_REFERENCE_COURSE)alter table sc drop constraint FK_SC_REFERENCE_COURSEgoif exists (select 1 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(student) and = FK_STUDENT_REFERENCE_DEP)alter table student drop constraint FK_STUDENT_REFERENCE_DEPgoif exists (select 1 from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = F) where r.fkeyid = object_id(teacher) and = FK_TEACHER_REFERENCE_DEP)alter table teacher drop constraint FK_TEACHER_REFERENCE_DEPgoif exists (select 1 from sysobjects where id = object_id(course) and type = U) drop table coursegoif exists (select 1 from sysobjects where id = object_id(dep) and type = U) drop table depgoif exists (select 1 from sysobjects where id = object_id(sc) and type = U) drop table scgoif exists (select 1 from sysobjects where id = object_id(student) and type = U) drop table studentgoif exists (select 1 from sysobjects where id = object_id(teacher) and type = U) drop table teachergo/*=*/* Table: course */*=*/create table course ( cid varchar(8) not null, cname varchar(30) null, credits decimal(3,1) null, constraint PK_COURSE primary key (cid)go/*=*/* Table: dep */*=*/create table dep ( depid varchar(8) not null, depname varchar(20) null, constraint PK_DEP primary key (depid)go/*=*/* Table: sc */*=*/create table sc ( sid varchar(11) not null, cid varchar(8) not null, tid varchar(8) null, score decimal(3,1) null constraint CKC_SCORE_SC check (score is null or (score b