某某学院 2006～2007 学年 第二学期
《SQL Server 数据库实训》报告
课程代号：__ _____
指导老师：
班 级： ___ _________
学号姓名：_______ _________
成 绩：________________
一、总体设计思路
当我拿到这份指导书，看完用户需求，我想到了以前的一个借书系统（读者表、借书表、图书表）。在整个住宿过程中有两个实体（客房和客户）、他们有一个关系（住宿），所以我认为用三张表就可以实现这个小型客房管理系统。他们是：
|
客户表 |
住房表 |
客房表 |
接着我从网上看了一个《明日腾龙宾馆客房管理系统》，他有登记表、登记预定、挂帐、客房、客房预定、密码、权限设置和调房登记。我就想：还是把住宿表分为登记表和结帐表。但我想他们的主键是相同的，根据经验他们就应该是一张表。
后来，我们细读了客户要求的第三点，我有了新的想法：应该有一个客房类别表。但这与我先前的想法有点出入。你想性别有男女哪里有个性别表，可是性别取值只有两个，而客房类型可以有无数个。还是有这张表合适。进过这重重的思考，我的出了以下的表结构
最后，我发现同一个客户不可以在同一个客房住同一个房间，这与实际不符合。我的设计不全面。只得在住宿表中加了一个流水号，但是在同一个时间内：身份证号和客房号必须是唯一的。
二、表结构
我的系统由四张表组成：
（1）客房表（描述每个客房的信息）
|
字段名 |
类型 |
长度 |
字段说明 |
|
客房号 |
Nvarchar |
3 |
每个客房有唯一的客房号 |
|
客房类别 |
Nvarchar |
9 |
每个客房有唯一的客房类别 |
|
空闲 |
bit |
1 |
标示是否有人居住 |
（2）客户表（描述每个客户的信息）
|
字段名 |
类型 |
长度 |
字段说明 |
|
身份证号 |
Nvarchar |
18 |
每一个客户有唯一的身份证号 |
|
姓名 |
Nvarchar |
8 |
客户的姓名 |
|
性别(男) |
bit |
1 |
客户的性别 |
|
年龄 |
Int |
4 |
客户的年龄 |
|
家庭地址 |
Nvarchar |
50 |
客户的家庭地址 |
|
工作单位 |
Nvarchar |
50 |
客户的工作单位 |
|
来自地的地名 |
Nvarchar |
50 |
客户的来自地的地名 |
（3）住宿表（描述客户住宿信息）
|
字段名 |
类型 |
长度 |
字段说明 |
|
流水号 |
int |
4 |
唯一标示一次住宿 |
|
员工编号 |
Int |
4 |
唯一标识一个员工 |
|
身份证号 |
Nvarchar |
18 |
每一个客户有唯一的身份证号 |
|
客房号 |
Nvarchar |
3 |
每个客房有唯一的客房号 |
|
入住时间 |
Smalldatetime |
4 |
客户的入住时间 |
|
预计入住天数 |
Int |
4 |
客户预计入住天数 |
|
退房时间 |
Smalldatetime |
4 |
客户的退房时间 |
|
缴纳押金 |
Money |
8 |
客户缴纳的押金 |
|
住宿费 |
Money |
8 |
客户的住宿费 |
（4）客房类别表（描述客房类别信息）
|
字段名 |
类型 |
长度 |
字段说明 |
|
客房类别 |
Nvarchar |
9 |
每个客房有唯一的客房类别 |
|
单价 |
Money |
8 |
每种类别都有一个唯一的单价 |
（5）员工表（描述员工的详细信息）
|
字段名 |
类型 |
长度 |
字段说明 |
|
员工编号 |
Int |
4 |
唯一标识一个员工 |
|
姓名 |
Nvarchar |
20 |
员工的姓名 |
|
名 |
Nvarchar |
10 |
员工的名 |
|
职称 |
Nvarchar |
30 |
员工的职称 |
|
称呼 |
Nvarchar |
25 |
员工的称呼 |
|
出生日期 |
Smalldatetime |
4 |
员工的出生日期 |
|
雇用日期 |
Smalldatetime |
4 |
员工的雇用日期 |
|
地址 |
Nvarchar |
60 |
员工的家庭住址 |
|
城市 |
Nvarchar |
15 |
员工所在的城市 |
|
行政区 |
Nvarchar |
15 |
员工所在的行政区 |
|
区号 |
Nvarchar |
10 |
员工所在的区号 |
|
国家地区 |
Nvarchar |
15 |
员工所在的国家地区 |
|
电话号码 |
Nvarchar |
24 |
员工的电话号码 |
|
内部分机号码 |
Nvarchar |
4 |
公司内部分机号码 |
|
相片 |
Image |
16 |
员工的相片 |
|
附注 |
Ntext |
16 |
员工的附注 |
|
主管 |
Int |
4 |
员工所在的主管 |
三、数据完整性设计
1) 参照完整性约束
|
表名 |
外码 |
参照表 |
|
客房表 |
客房号 |
住宿表的客房号 |
|
客户表 |
身份证号 |
住宿表的身份证号 |
|
客房类别表 |
客房类别 |
客房表的客房类别 |
2) 其它约束
1．建立DEFAULT对象，名称为AddressDefault，值为“地址不祥”，并将该对象绑定到客户表的家庭地址列上。
2．任何客户的缴纳押金必须为以下值之一：50、100、150、200、600。
3．在表客房类别表中，客房类别及单价的组合必须是唯一的。
4．在住宿表中，身份证号和客房号必须是唯一的。
5．给住宿表的预计入住天数列设定默认值为0。
3) 索引
1．给住宿表按身份证号和客房号创建索引，升序排序。
四、视图及存储过程
1．存储过程sp1：为客人选指定类型的客房，返回一个状态为空闲的房间号和相应的房价。
/*程序名称：sp1*/
create proc sp1
@kflb nvarchar(9)
as
select a.房间号,b.单价
from 客房表 a inner join 客房类别表 b
on a.客房类别=b.客房类别
where (a.客房类别=@kflb)and (a.空闲=1)
2．存储过程sp2：查某类房间的入住情况及空房情况，显示所有该类客房空房数目和房间号，用以供客人选择。
/*程序名称：sp2*/
create proc sp2
@kflb nvarchar(9)
as
select @kflb as 客房类别 ,count(*) as '空房数目'
from 客房表 a inner join 客房类别表 b
on a.客房类别=b.客房类别
where (a.客房类别=@kflb) and (a.空闲=1)
select @kflb as 客房类别 ,count(*) as '入住数目'
from 客房表 a inner join 客房类别表 b
on a.客房类别=b.客房类别
where (a.客房类别=@kflb) and (a.空闲=0)
select a.房间号 as '空房房间号'
from 客房表 a inner join 客房类别表 b
on a.客房类别=b.客房类别
where (a.客房类别=@kflb) and (a.空闲=1)
group by a.房间号
3．存储过程sp3：根据客人姓名、来自地的地名、工作单位或家庭住址等信息查询客人信息，以上4种参数是“或”的关系。
/*程序名称：sp3*/
create proc sp3
@krxm nvarchar(8),
@lzdm nvarchar(50),
@gzdw nvarchar(50),
@jtzz nvarchar(50)
as
select*
from 客人表
where 姓名=@krxm or 来自地的地名=@lzdm or 工作单位=@gzdw or 家庭住址=@jtzz
4．存储过程sp4：根据客房号查询入住客人的详细信息。
/*程序名称：sp4*/
create proc sp4
@kfh nvarchar
as
select c.*
from 客房表 a inner join 住宿表 b
inner join 客人表 c
on b.身份证号=c.身份证号
on a.房间号=b.客房号
where (a.房间号=@kfh)
5．存储过程sp5：查询某个客人住宿费情况，显示客人实际入住天数、客房价格、实际住宿费、缴纳押金金额、住宿费差额等信息。
/*程序名称：sp5*/
create proc sp5
@sfzh nvarchar(18)
as
select datediff(dd, a.退房时间,a.入住时间) as '实际入住天数',
datediff(dd, a.退房时间,a.入住时间)*c.单价 as '客房价格',
a.缴纳押金,
datediff(dd, a.退房时间,a.入住时间)*c.单价-a.缴纳押金 as '住宿费差额'
from 住宿表 a inner join 客房表 b
inner join 客房类别表 c
on b.客房类别=c.客房类别
on a.客房号=b.房间号
where a.身份证号=@sfzh
6．存储过程sp6：查询所有入住时间达到或超过预计天数的客人信息。
/*程序名称：sp6*/
create proc sp6
as
select a.*
from 客人表 a inner join 住宿表 b
on a.身份证号=b.身份证号
where datediff(dd, b.入住时间,getdate()) >b.预计入住天数
7．存储过程sp7：统计一段时间内各类客房的入住情况。
/*程序名称：sp7*/
create proc sp7
@tfsj1 smalldatetime,
@tfsj2 smalldatetime
as
select b.客房类别,sum(datediff(dd, a.退房时间,a.入住时间)) as '收入金额'
from 住宿表 a inner join 客房表 b
inner join 客房类别表 c
on b.客房类别=c.客房类别
on a.客房号=b.房间号
where a.入住时间 between @tfsj1 and @tfsj2
group by b.客房类别
8．存储过程sp8：统计全年各月份的客房收入。
/*程序名称：sp8*/
create proc sp8
@sfzh nvarchar
as
select month(a.退房时间),sum(datediff(dd, a.退房时间,a.入住时间)*c.单价)
from 住宿表 a inner join 客房表 b
inner join 客房类别表 c
on b.客房类别=c.客房类别
on a.客房号=b.房间号
group by month(a.退房时间)
9．存储过程sp9：统计一段时间内不同类型的客房的收入情况。
/*程序名称：sp9*/
create proc sp9
@tfsj1 smalldatetime,
@tfsj2 smalldatetime
as
select b.客房类别,sum(datediff(dd, a.退房时间,a.入住时间)*c.单价) as '收入情况'
from 住宿表 a inner join 客房表 b
inner join 客房类别表 c
on b.客房类别=c.客房类别
on a.客房号=b.房间号
where a.入住时间 between @tfsj1 and @tfsj2
group by b.客房类别
10．触发器
1）插入触发器：
/*程序名称：tri_1*/
if exists(select name from sysobjects
where type='TR'and name='tri_1')
drop trigger tri_1
go
create trigger tri_1 on 住宿表
for insert
as
declare @状态 bit
select @状态 = a.空闲
from 客房表 a , 住宿表 i
where a.房间号=i.客房号
if ( @状态 =1 )
begin
update 住宿表 set 住宿表.入住时间 = getdate()
update 客房表 set 空闲=0
from 客房表 a , 住宿表 i
where a.房间号=i.客房号
end
2）更新触发器：
/*程序名称：tri_2*/
if exists(select name from sysobjects
where type='TR'and name='tri_2')
drop trigger tri_2
go
create trigger tri_2 on 住宿表
for update
as
declare @状态 bit
select @状态 = a.空闲
from 客房表 a , 住宿表 i
where a.房间号=i.客房号
if ( @状态 =1 )
begin
update 客房表 set 空闲=0
from 客房表 a , 住宿表 i
where a.房间号=i.客房号
end
11．视图
视图1:
/*程序名称：VIEW1*/
SELECT 员工编号 AS Expr1, 名 AS Expr2, 姓名 AS Expr3, 职称 AS Expr4, 称呼 AS Expr5, 出生日期 AS Expr6,
雇用日期 AS Expr7, 地址, 城市, 行政区, 区号, 国家地区, 电话号码, 内部分机号码, 相片, 附注, 主管
FROM dbo.员工表
视图2:
/*程序名称：VIEW2*/
SELECT dbo.员工表.员工编号, dbo.员工表.姓名, COUNT(*) AS Expr1
FROM dbo.员工表 INNER JOIN
dbo.住宿表 ON dbo.员工表.员工编号 = dbo.住宿表.员工编号
GROUP BY dbo.员工表.员工编号, dbo.员工表.姓名
视图3:
/*程序名称：VIEW3*/
SELECT 员工编号, 姓名
FROM dbo.员工表
WHERE (出生日期 = GETDATE())
五、补充功能
在系统中，我们要管理公司，要知道是那个雇员执勤的。如果有问题是谁负责等等。应此我们加入一张员工表。
1．我们通过视图查看员工信息表。
2．我们通过视图查看每个员工的成绩。
3．我们通过视图查看本月哪些员工过生日。
六、实训总结
通过本次实训，我学会了很多东西。以前只是习题式的学习，老师布置什么作业就做什么作业，不用去考虑如何做一个系统，如何去权衡。这次我模拟一个客房管理系统做了一个小型的“客房管理系统”，“麻雀虽小五脏具全”我考虑了很多问题。就拿住宿表中的要不要流水号的问题来说，我和同学有过一番争论，最后觉得要有，但如何实现呢？如果写了，但是不能够实现自动编号，对于自己来说不是自己打自己的嘴巴吗？后来，通过百度查询我知道如果用代码写用identify，如今我们都用“企业管理器”做，如何做如下图：
最后，终于实现了。完成本次实训我要感谢很多人，指导老师，同学还有网络等等。我觉得做一件事时，应该把所有的资源都用上。
在这次实训中，我深深的感觉到需求分析非常重要，如果分析不当，今后会给我们带来很大的麻烦。我没有考虑到月份的问题，这给我做存储过程sp8时带来了很大的问题。也不知道如何去做，在老师的指导下我实现了该功能。
通过对SQL Server一学期的学习，我学到了很多在别的地方学不到的知识。同时也看到了自己的很多不足。我希望温老师在正式教SQL Server前，能让学生连续一天或者两天熟悉SQL Server 环境，做一点能力内的小事情。因为我在一开始学的时候朦朦胧胧，好似雾里看花。后来我花了一个周末的时间去熟悉SQL Server 的环境，上课才有了精神。其他，我觉得都挺好的。
总之：我觉得一切都挺好的，学习是没有止境的。
