北京工业大学 数据库原理 实验报告

计算机学院实验报告
课程名称：数据库原理
实验人学号：******xx 姓名：xxx 实验完成日期：2014年5月20日报告完成日期：2014年5月20日
目录
目录 (2)
实验准备 (3)
系统描述： (3)
ER图： (3)
关系模式: (3)
字段类型设置： (4)
业务需求： (5)
实验一数据库实施：创建数据库及其对象 (5)
一、实验目的 (5)
二、实验内容 (5)
三、实验要求 (6)
四、实验装置 (6)
五、实验步骤 (6)
六、个人实验过程: (6)
七、思考题 (11)
实验二: 数据的增删改 (12)
一、实验目的 (12)
二、实验内容 (12)
三、实验要求 (12)
四、实验装置 (12)
五、实验步骤 (12)
六、思考题 (17)
实验三: 数据库查询 (18)
一、实验目的 (18)
二、实验内容 (18)
三、实验要求 (18)
四、实验装置 (18)
五、实验步骤 (19)
六、思考题 (20)
实验四: 创建和删除视图 (21)
一、实验目的 (21)
二、实验内容 (21)
三、实验要求 (21)
四、实验装置 (21)
五、实验步骤 (21)
六、思考题 (23)
个人总结 (24)
实验准备
系统描述：
在医院服务管理系统中，需要对医生、病人、科室和病房进行管理。

其中，一个科室有多个病房、管理多个医生；一个病房只能隶属一个科室；一个医生只能属于一个科室；一个医生可负责多个病人的诊治；一个病人的诊断也可以由多个医生共同完成；一个病房可以住多个病人，但一个病人只能住在一个病房。

ER 图：
医生
病房
科室
病人
工作证号
姓名
性别
职称年龄
性别
病历号
年龄
姓名
科室名
科室地址
电话
主任姓名
病房号
诊断
管理
住院
隶属
挂号单号
诊断日期
看病花费药品花费
关系模式:
医生（工作证号，科室名，姓名，性别，年龄，职称）
病人（病历号，病房号，姓名，性别，年龄 看病花费，药品花费）
科室（科室名，科室地址，电话，主任姓名）
病房（病房号，科室名）
诊断（工作证号，病历号，挂号单号，诊断日期）字段类型设置：
科室
业务需求：
查询：医生基本信息，病人基本信息，病房基本信息，科室基本信息；
查询：病房的分配信息，科室对医生的管理信息，医生收入信息，科室收入信息，病人住院信息
打印：病人的住院信息及诊断结果。

实验一数据库实施：创建数据库及其对象
一、实验目的
1．学习 MS SQL SERVER2000 的特点。

2．学习并掌握创建一个关系数据库的方法和相关 SQL 语句的使用。

3．学习并掌握在数据库中创建基本表、修改表结构、删除基本表的方法和相关 SQL 语
句的使用。

4．学习并掌握在数据库中创建、删除索引文件的方法和相关 SQL 语句的使用。

5．掌握实体完整性、参照完整性、用户定义完整性约束概念。

二、实验内容
根据数据库设计方案：
1．使用 CREATE DATABASE 语句，在数据库服务器上创建一个关系数据库。

2．使用 CREATE TABLE 语句，以及 ALTER、DROP 语句创建基本表、修改表结构或
删除基本表。

3．分析数据库的应用（可以模拟设定），并使用 CREATE INDEX 语句、DROP 语句
创建、删除索引文件。

三、实验要求
1．使用 QUERY ANALYZER（查询分析器），以交互方式完成创建数据库，创建基
本表、修改基本表和删除基本表，以及创建、删除索引文件。

2．基本表的定义至少包括主码约束、外码约束、唯一性约束、检查约束、“空”约束
和缺省值约束。

四、实验装置
1．服务器端：MS SQL SERVER 2000 Enterprise Edition / Professional Edition.
2. 客户器端：Windows 2000 Server / Windows XP
五、实验步骤
1．根据数据库设计方案，编写创建数据库和基本表，修改、删除基本表，以及创建、删除索引文件的 SQL 语句。

2．上机编辑、调试并执行 SQL 语句。

六、个人实验过程:
（1）创建一个数据库，其数据文件初始大小为6M、日志文件的初始大小为
1M，数据文件最大允许增加到 50M，日志文件最大允许增加到3M。

USE master
Create database db_u11070322
ON (
NAME=DB_student_DATA,
FILENAME='D:\DB1107\db0322.mdf',
SIZE= 6,
MAXSIZE= 50,
FILEGROWTH= 1
)
LOG ON (
NAME=DB_student_LOG,
FILENAME='D:\DB1107\db0322.Ldf',
SIZE= 1,
MAXSIZE= 3,
FILEGROWTH= 1
每次增量都为1M。

数据文件和日志文件存放在D:\database 目录之下。

（SQL2012 初始大小设置为 1M 时会提示错误，要求最小为 3M）如图，可在左侧栏见到创建的数据库db_u11070322。

（2）使用 CREATE TABLE 语句创建自己设计的基本表。

--创建表
use db_u11070322;
create table doctor(
工作证号int check(工作证号>= 10000 and工作证号<= 99999),
科室名char(30)not null,
姓名char(30)not null,
性别char check(性别='M'or性别='F'),
年龄int check(年龄>= 0 and年龄<= 120),
职称char(30)not null,
primary key(工作证号),
foreign key(科室名)references科室(科室名)
)
create table科室(
科室名char(30)not null,
科室地址char(30)not null,
主任姓名char(30)not null,
电话int check(电话>= 1000 and电话<= 9999),
primary key(科室名)
create table病房(
病房号int check(病房号>= 100 and病房号<= 999),
科室名char(30)not null,
primary key(病房号),
foreign key(科室名)references科室(科室名)
)
create table病人(
病历号int check(病历号>= 1000 and病历号<= 9999),
姓名char(30)not null,
性别char check(性别='M'or性别='F'),
年龄int check(年龄>= 0 and年龄<= 120),
病房号int check(病房号>= 100 and病房号<= 999),
primary key(病历号),
foreign key(病房号)references病房(病房号)
)
create table诊断(
挂号单号int check(挂号单号>= 1000 and挂号单号<= 9999),工作证号int check(工作证号>= 10000 and工作证号<= 99999),病历号int check(病历号>= 1000 and病历号<= 9999),
foreign key(工作证号)references doctor(工作证号),
foreign key(病历号)references病人(病历号)
)
使用alter语句修改表：
--测试：修改表(添加列，删除列)
use db_u11070322;
create table test(
test int not null,
primary key(test)
)
alter table test add test1int not null alter table test add test2int not null alter table test drop column test1
使用CREATE INDEX 语句、DROP 语句创建、删除索引文件：--测试：使用CREATE INDEX 语句、DROP 语句创建、删除索引文件
create index doctor_num on doctor(
工作证号ASC,
年龄Desc
)
drop index doctor_num on doctor
七、思考题
1．总结基本表的定义方法，以及三种数据完整性约束。

2．理解索引文件的使用目的和它对数据库系统开销的影响。

理解聚簇索引的优缺点。

1、基本表的定义使用create table语句，三种数据完整性约束为主码外码约束， check约束，和非空约束。

2、当查询中包含索引表中的属性时，可利用索引表先查询到该属性满足要求的行，在进行其他属性的查询，以缩小查询范围并提高查询速度。

但是，如果数据量不大，则建立索引反而会增加系统的运行维护开销，影响查询的效率。

实验二: 数据的增删改
Insert , Delete and Update
实验学时：2
实验类型：SQL 语句的使用
一、实验目的
1．学习基本表数据更新（含：增、删、改）的方法及其 SQL 语句的使用。

2．为后续实验准备数据。

二、实验内容
1．使用 INSERT 语句，向数据库插入数据。

2．使用 UPDATE 语句，修改数据库中已有数据的取值。

3．使用 DELETE 语句，从数据库中删去一些数据。

三、实验要求
1．使用 QUERY ANALYZER（查询分析器），编写 SQL 语句，以交互方式完成数据
库数据的更新操作。

2．设计 INSERT、UPDATE 或 DELETE 语句时，要求用户能够观察到当插入或修改操
作符合或者违背主码约束、外码约束、唯一性约束、检查约束、“空”约束或缺省值约束
时，DBMS 对数据约束的维护、监督作用。

3．一个 UPDATE 语句可以对两个以上的属性值，或者对多个元组进行修改。

4．数据库中必须保留足够的数据，以供后续实验使用。

四、实验装置
与实验一相同。

五、实验步骤
1．根据实验一创建数据库及其对象，编写 SQL 语句，用于数据库数据的插入、修改、
删除操作。

2．上机编辑、调试并执行 SQL 语句
数据插入语句(包含违背主码约束的插入)：
use db_u11070322;
insert into科室(科室名,科室地址,主任姓名,电话)values ('内科',' 一号楼','小明', 1234)
insert into科室(科室名,科室地址,主任姓名,电话)values ('外科',' 二号楼','小花', 1235)
insert into科室(科室名,科室地址,主任姓名,电话)values ('妇科',' 三号楼','小华', 1236)
insert into科室(科室名,科室地址,主任姓名,电话)values ('耳鼻喉科',' 四号楼','小一', 1237)
insert into科室(科室名,科室地址,主任姓名,电话)values ('眼科',' 五号楼','小一', 1238)
insert into科室(科室名,科室地址,主任姓名,电话)values ('传染科',' 六号楼','小一', 1239)
insert into科室(科室名,科室地址,主任姓名,电话)values ('消化科',' 七号楼','小一', 1230)
--违背主码约束的测试
insert into科室(科室名,科室地址,主任姓名,电话)values ('消化科',' 七号楼','小一', 1230)
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12344,'内科','齐医生','M', 24,'专家');
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12345,'外科','周医生','F', 23,'普通医师');
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12346,'妇科','彭医生','M', 25,'普通医师');
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12347,'耳鼻喉科','姚医生','M', 21,'专家');
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12348,'眼科','齐医生','M', 28,'普通医师');
insert into doctor(工作证号,科室名,姓名,性别,年龄,职称)values (12349,'传染科', '宋医生','F', 26,'省级专家');
insert into病房(病房号,科室名)values (124,'内科');
insert into病房(病房号,科室名)values (125,'外科');
insert into病房(病房号,科室名)values (126,'妇科');
insert into病房(病房号,科室名)values (127,'耳鼻喉科');
insert into病房(病房号,科室名)values (128,'眼科');
insert into病房(病房号,科室名)values (129,'传染科');
insert into病人(病历号,姓名,性别,年龄,病房号)values (2234,'路人甲','M', 24, 124);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2235,'路人乙','F', 23, 125);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2236,'路人丙','M', 25, 126);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2237,'路人丁','M', 21, 127);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2238,'路人戊','F', 25, 128);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2239,'路人己','M', 29, 129);
insert into病人(病历号,姓名,性别,年龄,病房号)values (2230,'路人庚','M', 45, 129);
insert into诊断(挂号单号,工作证号,病历号)values (3234, 12344, 2234);
insert into诊断(挂号单号,工作证号,病历号)values (3235, 12345, 2235);
insert into诊断(挂号单号,工作证号,病历号)values (3236, 12346, 2236);
insert into诊断(挂号单号,工作证号,病历号)values (3237, 12347, 2237);
insert into诊断(挂号单号,工作证号,病历号)values (3238, 12348, 2238);
insert into诊断(挂号单号,工作证号,病历号)values (3239, 12349, 2239);
insert into诊断(挂号单号,工作证号,病历号)values (3230, 12349, 2230);
Update语句更新表中元组属性：
--update语句测试
update科室set主任姓名='小昕'where科室名='眼科' update科室set主任姓名='小涛'where科室名='传染科' update科室set主任姓名='小飞'where科室名='消化科'
Delete语句：
--delete语句测试
delete from科室where科室名='消化科'
六、思考题
1．在更新数据库数据过程中，RDBMS 如何发挥数据库数据约束作用？
绝大部分关系型数据库管理系统RDBMS都可自动支持关系完整性规则，只要用户在定义（建立）表的结构时，注意选定主键、外键及其参照表，RDBMS可自动实现其完整性约束条件。

2 ．DELETE 语句与DROP TABLE 语句的区别是什么？UPDATE 语句与ALTER TABLE 语句的区别什么？
Delete语句的操作对象是元组，Drop table语句删掉的是整个的表；
UPDATE 语句更新的是表中元组对应列的取值，alter table调整的是表的列。

实验三: 数据库查询
Query
实验学时：3
实验类型：SQL 语句的使用
一、实验目的
1．学习 SELECT 语句的使用。

二、实验内容
1．在实验一、二的基础上，使用 QUERY ANALYZER，从数据库中查询并显示数据。

三、实验要求
1．所设计的 SELECT 语句应当具有以下语句成分：
（1）SELECT 子句包含*，表达式，DISTINCT,或者具体指定的属性名，并引入聚集
函数（SUM、COUNT、AVG、MAX、MIN）运算。

（2）FROM 子句包含单个或多个表。

（3）WHERE 子句的条件表达式应包含字符串、日期等类型变量或常量，可以实现多
表的自然连接和一般条件连接。

（4）使用 GROUP BY HAVING 子句。

（5）使用 ORDER BY 子句。

2．设计具有以下结构的 SELECT 语句：
（1）嵌套查询。

包括引入 IN、ALL、ANY （some）和 EXISTS 用法。

（2）集合查询。

四、实验装置
与实验一相同。

五、实验步骤
1．根据实验内容和要求，设计并编写 SQL 查询语句。

2．上机编辑、调试并执行交互式 SQL 语句，获取查询结果
(1)查询所有医生的的名字(使用distinct)
--select distinct 查询举例
select distinct姓名as医生名单
from doctor
（2）查询各科室的净收入
--查询各科室的净收入(用到了聚集函数sum，from多个表，where实现了一般的条件连接，使用了group by having
--order by SUM(病人.看病花费)
select病房.科室名as科室名,SUM(病人.看病花费+病人.药品花费)as净收入
from病房,病人
where病房.病房号=病人.病房号
group by病房.科室名
having SUM(病人.看病花费+病人.药品花费)> 100
order by SUM(病人.看病花费+病人.药品花费)asc
（3）嵌套查询及集合查询：
嵌套查询：
--嵌套查询--查询收入小于200的医生的工作证号
select工作证号
from doctor
where工作证号not in(
select工作证号
from医生收入状况
where总收入> 200
)
集合查询：
--集合查询--查询除了眼科医生以外的医生名单--可以看到自动删除了重复元组齐医生
(select姓名from doctor)except (select姓名from doctor where科室名='眼科')
六、思考题
1．聚合函数的作用是什么？使用聚合函数应注意什么问题？
count()求个数sum() 求和avg()求平均数min()最小值max()最大值。

聚合函数一般与group by 语句使用，select 后面不在聚集函数中的属性必须出现在group by 子句中。

2．总结EXISTS 的用法特点。

Exists为相关子查询，EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False，EXISTS 指定一个子查询，检测元组的存在。

实验四: 创建和删除视图
Create and Drop Views
实验学时：1
实验类型：SQL 语句的使用
一、实验目的
1．学习创建、删除视图的方法和相关 SQL 语句的使用。

2．学习查询视图的方法和相关SQL 语句的使用，理解查询视图的执行过程。

3．理解使用视图的意义。

二、实验内容
1．在实验一、二的基础上，使用 CREATE VIEW 语句和 DROP VIEW 语句，创建、
删除视图表。

2．使用 SELECT 语句，从视图表和基本表中查询并显示数据。

三、实验要求
1．CREATE VIEW 语句中的查询子句必须包含聚集函数。

2．使用 QUERY ANALYZER，创建、删除、查询视图表。

四、实验装置
与实验一相同。

五、实验步骤
1．根据你模拟设定的数据库应用，为一些特定用户设计视图，并编写相应的 SQL 语句。

2．上机编辑、调试并执行 SQL 语句，实现视图的创建、删除和查询
创建和删除视图：
use db_u11070322;
--测试：创建视图和删除视图(creat and drop views)
create view病人诊断日期as (
select诊断日期
from诊断
)
drop view病人诊断日期
CREATE VIEW 语句中的查询子句包含聚集函数示例：
--创建视图与视图查询2
create view医生收入状况as (
select doctor.姓名as医生姓名,doctor.工作证号as工作证号,sum(病人.看病花费)as总收入
from doctor,病人,诊断
where病人.病历号=诊断.病历号and doctor.工作证号=诊断.工作证号
group by doctor.工作证号,doctor.姓名
)
创建视图结果：
从视图和基本表中对比查询：
--在视图中查询
select医生姓名,总收入
from医生收入状况
where总收入> 200
--在基本表中查询(对比)
select doctor.姓名as医生姓名,doctor.工作证号as工作证号,sum(病人.看病花费)as总收入
from doctor,病人,诊断
where病人.病历号=诊断.病历号and doctor.工作证号=诊断.工作证号
group by doctor.姓名,doctor.工作证号
having sum(病人.看病花费)> 200
六、思考题
1．试比较视图与基本表的异同点。

相同点：视图与表在基本查询方面的使用方法是一样的；
不同点：
（1）、视图是已经编译好的sql语句。

而表不是
（2）、视图没有实际的物理记录。

而表有。

（3）、表是内容，视图是窗口
（4）、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
（5）、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。

从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。

（6）、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

（7）、视图的建立和删除只影响视图本身，不影响对应的基本表。

2．你做过修改一个视图数据的试验吗？其结果是什么？
做过。

结果如下：
个人总结
通过这次的数据库实验，我实践了各类SQl语句的使用，在实验的过程中，遇到了许多问题，而最终得以顺利解决，也使我加深了对于数据库使用的理解。

在最初的实验准备阶段，我选择了“医院服务管理系统”作为我的选题，在设计数据库的ER图的时候，一开始我的表达与前面描述的需求的表达不一致，主要是在一对多的联系上箭头全部写反了。

在ER图转换成关系模式时，我把所有的联系集都转换成关系模式了。

后来经过老师的指导，我重新复习了课件，最终正确的修改了选题阶段的各种错误。

到了实践阶段，一开始的时候我发现数据库对于语句的执行各种限制颇多，导致我经常性的执行失败，后来随着使用，我设计SQL语句的时候变得更加严谨，在执行顺序上也做到了保持头脑清醒，使得后面的数据库使用还是相当愉快的。

SQL语句是我接触过的很接近于日常语言的一门语言，而它的强大之处也是可见一斑，这次实验我们通过纯语句的方式实现了从数据库创建到修改到查询使用的全部过程，比可视化编辑更加严谨。

这次数据库的实验为我今后的使用打下了
扎实的基础，相信之后我能使用的更好。

最后，感谢老师对我整个实验的耐心指导！。