电子科技大学 数据库系统及应用实验报告
计算机专业类课程
实验报告
课程名称:数据库系统及应用 学 院:计算机科学与工程学院 专 业:计算机科学与技术 学生姓名:朋施羽 学 号: 指导教师:
日 期: 2014 年 4月21 日
电子科技大学计算机学院实验中心
电 子 科 技 大 学
实 验 报 告
实验一
一、实验名称:创建数据库 二、实验学时:4 三、实验内容和目的: 实验内容:
(1) 启动SQLSERVER
(2) 创建数据库:STUD (3) 创建表
(4) 插入样本数据 (5) 备份数据库 (6) 恢复数据库
“系别代码表“ “教师表” “学生表” “课程表” “选课表”
为每个表准备大约10记录,使用Insert语句将这些数据插入到相应表中数据录入完成后,将数据库备份到磁盘上,在以后的的试验中备用。
表名:dep
表名:teacher 表名:student 表名:course 表名:sc
实验目的:
本实验要求学生掌握创建数据库的方法及相关操作,创建数据库,向数据库中添加样本数据,学习SQLSERVER数据库的恢复和备份。
四、实验原理:
使用数据库管理系统DB、DDL创建数据库及数据库对象。
五、实验器材(设备、元器件)
操作系统:Win7
数据库 : MS SQLSERVER
六、实验步骤:
(1) 创建数据库:STUD
Create database stud;
(2) 创建表,表的详细说明见表格.
“系别代码表“ 表名:dep
其结构如下:(该表的主键为“系代码”)
“教师表”表名:teacher
其结构如下:(该表的主键为“教师号”)
“学生表” 表名:student 其结构如下:(该表的主键为“学生号”)
“课程表”表名:course
电子科技大学计算机学院实验中心
其结构如下:(该表的主键是课程号)
“选课表”表名:sc
其结构如下:(该表的主键是课程号)
(3) 备份数据库
BACKUP DATABASE stud TO DISK = 'd:\stud.bak' WITH INIT; (4) 恢复数据库
RESTORE DATABASE stud FROM DISK = 'd:\stud.bak' WITH REPLACE;
七、实验数据及结果分析:
(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','[email protected]','沙河庙'),('2406010102','张四','男','601','1982/1/1','[email protected]','大美路
电子科技大学计算机学院实验中心
'),('2406010103','张五','女','601','1983/2/2','[email protected]','清水河'),('2406020101','王三','男','602','1984/12/12','[email protected]','保研路'),('2406020102','王四','男','602','1985/6/6','[email protected]','银杏路');
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;
八、实验结论、心得体会和改进建议:
最后成功建数据库、建表,并在表中插入了数据。了解到,在SqlServer中若要在某个数据库中插入
数据,首先要新建该数据库的查询,或者使用ues该条语句,否则可能会自动创建到系统数
据库中。同时,插入数据的时候要注意顺序,如果某表中有外键,则应该先向其参考的被参考表中插入
数据。
电子科技大学计算机学院实验中心
电 子 科 技 大 学
实 验 报 告
实验二
一、实验名称:数据库的完整性 二、实验学时:4 三、实验内容和目的: 实验内容:
设置约束条件
● 设置教师表,学生表中的院系字段(depid)的外键约束 ● 设置选课表的三个外键约束(学号,课程号,教师号) ● 设置选课表中成绩字段的取值范围是0到100
● 设置学生表中性别字段的取值为 “男”或“女” ● 设置学生表电子邮件字段的取值必须包含@符号
实验目的:
通过设置表的检查约束、外键约束体会数据库完整性的含义,约束条件下数据修改操作的限制,以及实现修改操作的技巧。
四、实验原理:
数据库库的完整性、约束条件、结构化查询语言。
五、实验器材(设备、元器件)
操作系统:Win7
数据库 : MS SQLSERVER
六、实验步骤:
(1) 恢复数据库,将数据库备份stud 恢复到DB2中 (2) 执行SQL命令完成实验内容 (3) 备份数据库
七、实验数据及结果分析:
(1) 恢复数据库,将数据库备份stud 恢复到DB2中
BACKUP DATABASE stud TO DISK = 'd:\stud.bak' WITH INIT;
(2) 执行SQL命令完成实验内容
● 设置教师表,学生表中的院系字段(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表的外键约束
● 设置选课表的三个外键约束(学号,课程号,教师号)
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);
● 设置选课表中成绩字段的取值范围是0到100
alter table sc add check(score>=0 and score
电子科技大学计算机学院实验中心
图2-3 sc表的外键约束和check约束
● 设置学生表中性别字段的取值为 “男”或“女”
alter table student add check(sex in('男','女'));
● 设置学生表电子邮件字段的取值必须包含@符号
alter table student add check(semail like'%@%');
图2-4 student表的check约束
(3) 备份数据库
RESTORE DATABASE stud FROM DISK = 'd:\stud.bak' WITH REPLACE;
八、 实验结论、心得体会和改进建议:
成功的为表添加约束,同时应该注意,可以灵活的使用通配符来达到要求。这里提出一点改进建议,在添加约束的时候,可以考虑为其取名,方便下次修改。
电 子 科 技 大 学
实 验 报 告
实验三
一、实验名称:数据查询
二、实验学时:4
三、实验内容和目的:
实验内容:
(1) 查询年龄在20—22之间的学生姓名(通过出生日期和当前日期计算年龄)
year(getdate())-year(birthd)
(2) 查询所有副教授的信息
(3) 查询姓“张”的学生的学号、姓名、邮件地址
(4) 求每门课程的选课人数、最高分、最低分、平均分,要求在查询结果中显示课程号和课程名;
(5) 查询既选修了1号课程,又选修了2号课程的学生学号
(6) 查询既选修了1号课程,又选修了2号课程的学生姓名
(7) 查询选修了全部课程的学生姓名
实验目的:
练习用SELECT查询语句,设置查询条件,实现单表查询。练习使用SELECT语句从多个表中查询数据,表的内连接、左外连接、右外连接的使用以及设置连接条件,理解连接条件和查询条件的在目的和功能上的区别。
四、实验原理:
结构化查询语言、表的连接、关系运算、分组查询
五、实验器材(设备、元器件)
电子科技大学计算机学院实验中心
操作系统:Win7
数据库 : MS SQLSERVER
六、实验步骤:
(1) 恢复数据库,将数据库备份stud 恢复到DB2中
(2) 执行SQL命令完成实验内容
七、实验数据及结果分析:
(1) 恢复数据库,将数据库备份stud 恢复到DB2中
BACKUP DATABASE stud TO DISK = 'd:\stud.bak' WITH INIT;
(2) 执行SQL命令完成实验内容
● 查询年龄在20—22之间的学生姓名(通过出生日期和当前日期计算年龄)
year(getdate())-year(birthd)
select sname from student where year(getdate())-year(birthd) between 20 and 22;
图3-1 20-22岁学生姓名的查询结果
● 查询所有副教授的信息
select * from teacher where title like '副教授
';
图3-2 副教授信息的查询结果
● 查询姓“张”的学生的学号、姓名、邮件地址
select sid 学号,sname 姓名,semail 邮件地址 from student where sname like '张
%';
图3-3 张姓同学的信息查询结果
● 求每门课程的选课人数、最高分、最低分、平均分,要求在查询结果中显示课程号和课程名; 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 各门课程的查询结果
电子科技大学计算机学院实验中心
● 查询既选修了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号课程学生的学号
● 查询既选修了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号课程学生的姓名
● 查询选修了全部课程的学生姓名
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相当于双重否定,得到了结果。查询的时候,适当给列取别名,可以使得查询结果更加清晰明白。
电子科技大学计算机学院实验中心
电 子 科 技 大 学
实 验 报 告
实验四
一、实验名称:数据库建模
二、实验学时:4
三、实验内容和目的:
实验内容:
(1) 使用PDM,以图形化界面方式创建表及确定各表之间的关系,在实验报告中给出模型图;
(2) 通过“生成数据库”功能生成创建数据库的脚本,在实验报告中给出脚本内容; 实验目的:
本实验要求学生学习数据库建模工具PowerDesigner的使用方法,掌握最基本的使用方法。
四、实验原理:
使用图形化CASE工具设计数据库,ER图
五、实验器材(设备、元器件)
操作系统:Win7
应用软件:Power Designer 15.1
六、实验步骤:
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) 选择Database→Generate 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) 选择File→Reverse Engineering→Database”窗口。新窗口如图9所示:
电子科技大学计算机学院实验中心
图 9
(2) 从“Database name”下拉列表框中选择数据库管理系统。如图9的红圈所示,并点击确定。 (3) 单击“Using a script file”单选按钮,并添加一个SQL脚本文件,如图10所示:
图 10
(4) 单击“OK”,新的PDM 出现在窗口中,如图11所示:
这时可按照步骤1介绍的方法进行图形化界面的修改,并生成新的SQL脚本文件。
七、实验数据及结果分析:
(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 o.name = 'FK_SC_REFERENCE_STUDENT') alter table sc
drop constraint FK_SC_REFERENCE_STUDENT go
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 o.name = 'FK_SC_REFERENCE_TEACHER') alter table sc
drop constraint FK_SC_REFERENCE_TEACHER go
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 o.name = 'FK_SC_REFERENCE_COURSE') alter table sc
drop constraint FK_SC_REFERENCE_COURSE go
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('student') and o.name = 'FK_STUDENT_REFERENCE_DEP') alter table student
drop constraint FK_STUDENT_REFERENCE_DEP go
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('teacher') and o.name = 'FK_TEACHER_REFERENCE_DEP') alter table teacher
drop constraint FK_TEACHER_REFERENCE_DEP go
if exists (select 1
from sysobjects
where id = object_id('course') and type = 'U') drop table course go
if exists (select 1
from sysobjects
where id = object_id('dep') and type = 'U') drop table dep go
if exists (select 1
from sysobjects
where id = object_id('sc') and type = 'U') drop table sc go
if exists (select 1
from sysobjects
where id = object_id('student')
电子科技大学计算机学院实验中心
and type = 'U') drop table student go
if exists (select 1
from sysobjects
where id = object_id('teacher') and type = 'U') drop table teacher go
/*==============================================================*/ /* 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 between 0 and 100)), constraint PK_SC primary key (sid, cid) ) go
/*==============================================================*/ /* Table: student */
/*==============================================================*/ create table student (
sid varchar(11) not null, sname varchar(8) null, depid varchar(8) null, sex varchar(2) null, birthd datetime null, semail varchar(20) null, homeaddr varchar(40) null, constraint PK_STUDENT primary key (sid) ) go
/*==============================================================*/ /* Table: teacher */ /*==============================================================*/ create table teacher (
tid varchar(8) not null, tname varchar(8) null, title varchar(8) null, depid varchar(8) null, constraint PK_TEACHER primary key (tid) ) go
alter table sc
add constraint FK_SC_REFERENCE_STUDENT foreign key (sid) references student (sid) go
alter table sc
add constraint FK_SC_REFERENCE_TEACHER foreign key (tid) references teacher (tid) go
alter table sc
add constraint FK_SC_REFERENCE_COURSE foreign key (cid) references course (cid) go
alter table student
add constraint FK_STUDENT_REFERENCE_DEP foreign key (depid) references dep (depid) go
电子科技大学计算机学院实验中心
alter table teacher
add constraint FK_TEACHER_REFERENCE_DEP foreign key (depid) references dep (depid) go
八、实验结论、心得体会和改进建议: PowerDesigner几乎包括了数据库模型设计的全过程。利用
PowerDesigner可以制作数据流程图、
概念数据模型、物理数据模型,可以生成多种客户端开发工具的应用程序,还可为数据仓库制作结构模
型。利用PowerDesigner可以简单的创建数据库模型,导出数据库脚本,并创建数据库,是非常实用的
工具。