《数据库原理》课程设计报告
题目:KTV管理系统
就是记录一下小组做的 以后或许有点用

一、简要概述

顾客来到KTV一定会开包房消费,但是包房会有大小之分,不同类型的包房的价格也不同。可以按照顾客的需求进行包房的分配,并且及时知道包房剩余状态,更方便快捷的进行包房的分配。并且可以了解VIP顾客的性别,年龄,电话,可以准确地为客人服务。现在大部分ktv都是将酒水放在房间外一个单独的地方,有需求的顾客可以自行去购买酒水然后结账购买酒水,顾客消费酒水,系统可以对酒水消费的账单进行查询,查询卖出酒水的种类,数量。对服务员可以准确地了解服务员信息,可以输入新招聘的服务员的信息,删除原来辞职的服务员信息,有效地提高服务效率,使顾客体验更好。

二、需求分析

1.现实需求分析(通过与一些去过KTV的顾客以及KTV服务员之间的问答进行分析而得)
KTV管理信息系统主要是完成对KTV的管理(包括预定,开房,结账)和各种相关信息的管理的录入和查询。用户登入系统,输入用户名和密码,系统从数据库的用户信息表进行核对,完全进入系统。房间或会员信息的增加时,系统会把这些信息入到数据库中的房间和会员信息表中。
若客人取消预定,则在预定单表中删除这些信息。当结账时, 通过房间编号从收银单据表中读取消费信息。结账后,就把此行信息删除,写入到收银单据表中。如果是会员,消费的信息会员写入到会员信息中,消费的次数和消费的金额都将累加,如果消费积分达到一定的程度,会员的账单可以拥有一定的折扣。
2.系统最终用户
服务员
3.数据库功能需求分析
本系统是为一般KTV设计的KTV 管理系统,程序要求能录入些基本的资料,需要对顾客完成对房间的预定,开房,结账以及进行酒水消费等基本信息操作,并可以对这些信息进行统计,根据以上的需求,数据库的设计,必需满足以下几点:
●记录服务员的基本信息:工号,姓名,性别,电话。
●记录房间的基本信息:房间号,房间推荐人数,房间类型,房间的价格,房间状态。
●记录会员的基本信息: 会员账号,会员姓名,电话,地址,充值数目,充值时间,账户余额,消费次数,消费积分。
●记录预订单信息:包括预订单编号,顾客名称,房间编号,房间类型,房间价格,顾客电话,开房开始时间,开房结束时间,服务员姓名,服务员工号,下订单日期,备注。
●记录收银单据信息:单据编号,房间编号,房间类型,房间价格,顾客名称,开房时间,关房时间,付款方式,折扣,消费总额,开房日期,会员编号
●记录用户信息,包括用户ID,用户名,用户密码,用户权限类型
●记录酒水账单信息,包括酒水账单编号,酒水种类,酒水价格,酒水售出时间
主要功能包括:
1、权限管理:为指定角色分配权限
(1)根据输入信息(ID、权限信息、功能信息)分配角色对应权限。 (2)保存权限信息。
2、会员管理:查看会员基本信息,更新积分情况,记录消费次数
3、服务员管理:
记录服务员的基本信息并对相关信息进行管理
4、房间管理:
查看包房状态变化、更新包房信息、进行房间信息查询
进行房间预定:顾客打电话预订包间 ,然后KTV前台服务员查询包房房间信息然后安排预订,将预订号反馈给顾客最后更新房间预订状态
开房:在查询到的满足顾客条件的包房中安排一间包房给顾客
5.酒水消费:顾客进行酒水消费,打印酒水消费账单
6.收银:顾客提出结账要求、服务员打印收银小票 、修改包间房间信息
功能结构图

4.数据字典
用户:
数据名称 数据类型 是否可以为空 默认值 描述 备注
user_id char 否 登录用户id 主键
user_name varchar 否 用户姓名
user_password varchar 否 用户密码
user_type int 否 用户权限类型(0:管理员1:普通用户)
服务员:
数据名称 数据类型 是否可以为空 默认值 描述 备注
s_id char 否 服务员工号 主键
s_name varchar 否 服务员姓名
s_phone char 否 服务员电话
s_sex Char 否 服务员性别 只能男或女
vip顾客:
数据名称 数据类型 是否可以为空 默认值 描述 备注
v_id Char 否 顾客账号 主键
v_name varchar 否 顾客姓名
v_phone varchar 否 顾客电话
v_age Tinyint 否 顾客年龄
v_sex char 否 顾客性别 只能男或女
v_address varchar 否 顾客地址
v_charge double 否 充值数目
v_time datetime 否 充值时间
v_yue float 否 账户余额
v_ci int 否 0 消费次数
v_integral float 否 消费积分
房间:
数据名称 数据类型 是否可以为空 默认值 描述 备注
r_id char 否 房间编号 主键
r_type varchar 否 房间类型 1:小包2:中包3:大包
r_status int 否 0 房间状态 0:空房 1:已预订 2:有人
r_price float 否 房间价格
r_num Char 否 推荐人数(1-2、3-6、7-15)
预订单:
数据名称 数据类型 是否可以为空 默认值 描述 备注
order_id Char 否 预订单编号 主键
c_name varchar 否 顾客姓名
r_id char 否 房间编号
r_type varchar 否 房间类型 1:小包2:中包3:大包
r_price float 否 房间价格
c_phone char 否 顾客电话
openc_start datetime 否 开房开始时间
openc_end datetime 否 开房结束时间
s_id char 否 服务员工号
order_time datetime 否 预订日期
v_id char 是 会员编号
order_remake varchar 是 描述
酒水消费:
数据名称 数据类型 是否可以为空 默认值 描述 备注
w_id Char 否 酒水账单编号 主键
w_category varchar 否 酒水种类
w_name varchar 否 酒水名称
w_price Decimal 否 酒水价格
w_num int 否 购买数量
w_out Datetime 否 售出时间
w_money float 否 酒水消费
收银单据:
数据名称 数据类型 是否可以为空 默认值 描述 备注
b_id char 否 单据编号 主键
r_id char 否 房间编号
r_type varchar 否 房间类型
r_price float 否 房间价格
c_name varchar 否 顾客姓名
openc_start datetime 否 房间开始时间
openc_end datetime 否 结束时间
order_time datetime 否 下单时间
s_id Char 否 服务员工号
v_id char 是 会员账号
f_money double 否 总消费
f_paymode varchar 否 付款方式

三、 概念结构设计

3.1实体和实体间的联系
(1)一个顾客对应一个房间。
(2)一个顾客对应一个收银单据。
(3)多个服务员对应多个包间。
(4)一个顾客对应一个酒水账单。
(5)一个酒水对应一个顾客。
(6)一个房间对应多个顾客。

3.2全局概念模式设计

一、
二、

各实体之间关系E-R图

四、逻辑结构设计

1、由实体关系图转换关系模式,结果如下:
(1)用户(用户id,用户姓名,用户密码,用户权限类型)
(2)vip顾客(顾客账号,顾客姓名,顾客电话,顾客性别,顾客地址,账号余额
充值数目,充值时间,消费次数,消费积分)
(3)服务员(服务员工号,服务员姓名,服务员性别,服务员电话)
(4)房间(房间编号,房间价格,房间类型,房间状态,推荐人数)
(5)酒水账单(酒水账单编号,名称,种类,价格,购买数量,售出时间,酒水消费)
(6)收银单据(单据编号,服务员工号,顾客姓名,房间编号,房间类型,房间价格,顾客姓名,房间开始时间,结束时间,开房日期,会员编号,总消费,下单时间,付款方式)
(7)预订订单(预订单编号,顾客姓名,房间编号,房间类型,房间价格,顾客电话,开房日期,开房时间,服务员工号,预订日期,会员编号,备注)
2、数据库中表的的逻辑结构
用户(users):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
user_id char 2 否 登录用户id primary key
user_name varchar 8 否 用户姓名
user_password varchar 16 否 用户密码
user_type int 否 用户权限类型(0:管理员1:普通用户)
服务员(server):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
s_id char 6 否 服务员工号 primary key
s_name varchar 10 否 服务员姓名
s_phone char 11 否 服务员电话
s_sex Char 4 否 服务员性别 只能男或女
vip顾客(vipcustomer):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
v_id Char 8 否 顾客账号 primary key
v_name varchar 10 否 顾客姓名
v_phone varchar 11 否 顾客电话
v_age Tinyint 否 顾客年龄
v_sex char 2 否 顾客性别 Check
v_address varchar 30 否 顾客地址
v_charge Float 8 否 充值数目
v_time datetime 否 充值时间
v_yue float 8 否 账户余额
v_ci int 8 否 0 消费次数
v_integral float 8 否 消费积分
房间(room):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
r_id char 4 否 房间编号 primary key
r_type varchar 10 否 房间类型 1:小包2:中包3:大包
r_status int 否 0 房间状态 0:空房 1:已预订 2:有人
r_price float 8 否 房间价格
r_num Char 6 否 推荐人数(1-2、3-6、7-15)
预订单(orders):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
order_id Char 6 否 预订单编号 Primary key
c_name varchar 8 否 顾客姓名
r_id char 4 否 房间编号
r_type varchar 10 否 房间类型 1:小包2:中包3:大包
r_price float 8 否 房间价格
c_phone char 11 否 顾客电话
openc_start datetime 否 开房开始时间
openc_end datetime 8 否 开房结束时间
s_id char 6 否 服务员工号
order_time datetime 否 预订日期
v_id char 8 是 会员编号
order_remake varchar 50 是 备注
酒水消费(wine):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
w_id Char 4 否 酒水账单编号 primary key
w_category varchar 20 否 酒水种类
w_name varchar 20 否 酒水名称
w_price Decimal 10,2 否 酒水价格
w_num int 否 购买数量
w_out Datetime 否 售出时间
w_money float 8 否 酒水消费
收银单据(bill):
列名 数据类型 长度 是否可以为空 默认值 备注 约束
b_id char 10 否 单据编号 Primary key
r_id char 4 否 房间编号
r_type varchar 10 否 房间类型
r_price float 8 否 房间价格
c_name varchar 8 否 顾客姓名
openc_start datetime 否 房间开始时间
openc_end datetime 否 结束时间
order_time datetime 否 下单时间
s_id Char 6 否 服务员工号
v_id char 8 是 会员账号
f_money double 8 否 总消费
f_paymode varchar 10 否 付款方式

五、数据库物理实现

1、数据库物理结构设计
SQL SERVER操作
(1)创建数据库:
createdatabase ktv;
(2) 建表
use ktv;
用户表:
createtable users(–用户
user_idchar(2)primarykey,–用户id
user_namevarchar(8)notnull,–用户姓名
user_type int,–用户权限类型
user_password varchar(16)–用户密码
)
结果如图所示:

Vip顾客表:
createtable vipcustomer(–vip顾客
v_id char(8)primarykey,–vip账号
v_name varchar(10)notnull,–姓名
v_age tinyintnotnull,–年龄
v_phone char(11)notnull,–电话
v_sex char(2)check (v_sex=‘男’or v_sex=‘女’),–性别
v_time datetimenotnull,–充值时间
v_ci intdefault’0’,–消费次数
v_address varchar(30),–顾客地址
v_integral float(8),–消费积分
v_charge float(8),–充值数目
v_yue intdefault’0’notnull–账户余额
)
结果如图所示:

服务员表:
createtableserver(–服务员
s_id char(8)primarykey,–服务员工号
s_name varchar(10)notnull,–服务员姓名
s_sex char(2)check(s_sex='男’or s_sex=‘女’),–服务员性别
s_phone varchar(11)notnull,–服务员电话
)
结果如图所示:

房间表:
createtable room(–房间
r_id char(4)primarykey,–房间编号
r_type char(10)notnull,–房间类型
r_status intdefault’0’,–房间状态(0:空房,1:已预订,2:已开房)
r_price intnotnull,–房间价格
r_num char(6)notnull–推荐人数
)
结果如图所示:

酒水消费表:
createtable wine(–酒水消费
s_id intprimarykey,–酒水账单编号
w_name varchar(20)notnull,–酒水名称
w_category varchar(20)notnull,–酒水种类
w_price decimal(10,2)notnull,–酒水价格
w_out datetimenotnull,–售出时间
w_num intnotnull,–购买数量
w_money float(8)–酒水消费
)
结果如图所示:
收银单据表:
createtable bill(–收银单据
b_id char(10)primarykey,–单据编号
s_id char(8)notnull,–服务员工号
r_id char(4)notnull,–房间编号
r_type char(10)notnull,–房间类型
r_price intnotnull,–房间价格
openc_start datetimenotnull,–房间开始时间
openc_end datetimenotnull,–结束时间
openc_date datetimenotnull,–开房日期
v_id char(8),–会员账号
c_name char(8)notnull,–顾客姓名
f_money intnotnull,–总消费
f_paymode varchar(10)notnull,–付款方式
f_time datetimenotnull–下单时间
constraint fk_sid foreignkey(s_id)referencesserver(s_id)ondeletecascadeonupdatecascade,
constraint fk_vid foreignkey(v_id)references vipcustomer(v_id)ondeletecascadeonupdatecascade,
constraint fk_rid foreignkey(r_id)references room(r_id)ondeletecascadeonupdatecascade
)
结果如图所示:

预订单表:
createtable orders(–预订单
order_id char(10)primarykey,–单据编号
s_id char(8)notnull,–服务员工号
r_id char(4)notnull,–房间编号
r_type char(10)notnull,–房间类型
r_price intnotnull,–房间价格
openc_time datetimenotnull,–开房开始时间
openc_end datetimenotnull,–开房结束时间
order_date datetimenotnull,–预订日期
v_id char(8),–会员账号
c_name char(8)notnull,–顾客姓名
c_phone char(11)notnull,–顾客电话
order_remake varchar(50),–备注
constraint fk_s2id foreignkey(s_id)referencesserver(s_id)ondeletecascadeonupdatecascade,
constraint fk_v2id foreignkey(v_id)references vipcustomer(v_id)ondeletecascadeonupdatecascade,
constraint fk_r2id foreignkey(r_id)references room(r_id)ondeletecascadeonupdatecascade
)
结果如图所示:

2、数据操作
2.1数据录入(部分)
用户表:
insertinto users(user_id,user_name,user_password,user_type)values (‘01’,‘mary’,‘200010’,1)
insertinto users(user_id,user_name,user_password,user_type)values (‘02’,‘haha’,‘200011’,1)
insertinto users(user_id,user_name,user_password,user_type)values (‘03’,‘zhangsam’,‘200012’,1)
insertinto users(user_id,user_name,user_password,user_type)values (‘04’,‘lila’,‘200013’,1)
服务员表:
insertintoserver(s_id,s_name,s_phone,s_sex)values (‘000001’,‘mary’,‘15873322471’,‘女’)
insertintoserver(s_id,s_name,s_phone,s_sex)values (‘000002’,‘haha’,‘15873122471’,‘男’)
insertintoserver(s_id,s_name,s_phone,s_sex)values (‘000003’,‘zhangsam’,‘15373322471’,‘男’)
insertintoserver(s_id,s_name,s_phone,s_sex)values (‘000004’,‘lila’,‘15873232471’,‘女’)

Vip顾客表:
insertinto vipcustomer(v_id,v_name,v_phone,v_age,v_sex,v_address,v_charge,v_time,v_yue,v_ci,v_integral)values (‘19069001’,‘张三’,‘15833354471’,21,‘男’,‘湖南’,100,2017-12-12,100,2,10)
insertinto vipcustomer(v_id,v_name,v_phone,v_age,v_sex,v_address,v_charge,v_time,v_yue,v_ci,v_integral)values (‘19069002’,‘丽丽’,‘15834554471’,21,‘男’,‘河南’,50,2017-10-12,100,2,10)
insertinto vipcustomer(v_id,v_name,v_phone,v_age,v_sex,v_address,v_charge,v_time,v_yue,v_ci,v_integral)values (‘19069003’,‘小飞’,‘15245335447’,21,‘女’,‘福建’,200,2018-04-02,100,2,10)
insertinto vipcustomer(v_id,v_name,v_phone,v_age,v_sex,v_address,v_charge,v_time,v_yue,v_ci,v_integral)values (‘19069004’,‘菲菲’,‘15833435466’,21,‘男’,‘江西’,200,2018-11-02,100,2,10)

房间表:
insertinto room(r_id,r_type,r_status,r_price,r_num)values (‘1001’,‘小包’,0,20,‘1-2’)
insertinto room(r_id,r_type,r_status,r_price,r_num)values (‘1002’,‘中包’,1,60,‘2-6’)
insertinto room(r_id,r_type,r_status,r_price,r_num)values (‘1003’,‘大包’,0,88,‘7-15’)
insertinto room(r_id,r_type,r_status,r_price,r_num)values (‘1004’,‘中包’,2,60,‘2-6’)
4.2数据查询
–(1)查询vip顾客表中最年长的vip顾客年龄
selectmax(v_age)from vipcustomer

–(2)统计男女vip顾客的人数
select v_sex,count(v_id)from vipcustomer groupby v_sex

–(3)查询所有姓李的服务员信息
select*fromserverwhere s_name like’李%’

–(4)查询房间空闲的房间信息
select*from room where r_status=0

–(5)查询vip顾客消费次数大于4的顾客信息
select*from vipcustomer where v_ci>10

–(6)查询年龄在19-23之间的vip顾客信息
select*from vipcustomer where v_age>=19 and v_age<=23

–(7)查询房间类型和数量
select r_type,count(r_type)from room groupby r_type

–(8)查询服务员名字第二个字是’天’的个人信息
select*fromserverwhere s_name like’_天%’

–(9)查询各种包房的单价和
select r_type,sum(r_price)from room groupby r_type

–(10)将电话为’15833354471’的vip顾客姓名改为胡汉三
update vipcustomer set v_name='胡汉三’where v_phone=‘15833354471’

–(11)将包房的使用情况更新成使用。
update room set r_status=1 where r_id=10

–(12)将服务员李红的信息删除
deletefromserverwhere s_name=‘李红’

–(13)查询酒水账单编号为’9003’的信息
select*from wine where w_id=‘9003’

–(14)查询每种酒的单价
selectdistinct w_category,sum(w_price)from wine groupby w_category

–(15)查询房间总消费低于平均房间消费的支付方式
select f_money,f_paymode from bill wheref_money<(selectavg(f_money)from bill)

4.3视图
–1.视图V1,查询年龄是21岁的VIP顾客信息
GO
createview V1
as
select*from vipcustomer where v_age=21
withcheckoption

GO
–2.视图V2,查询所有服务员的信息
createview V2
as
select*fromserver

GO
–3.视图V3,按总消费查询收银单据前5单信息
create view V3 
as  
select
top 5 * 
from bill orderby bill.f_money desc

视图图示:

六、总结

1、小组总结
在这次项目设计中,我们小组所选择的是设计一个KTV管理系统,这对我
们来说是一次尝试与创新的过程,也可以说是一个挑战的过程。由于我们刚刚学数据库,因此我们还是缺少经验。现在我们利用自己学到的知识设计并制作一
个KTV管理系统,这本身就是一个知识转化为生产力的过程,所以大家都很兴奋,
都不同程度的投入了很高的热情与努力。哪怕我们因为思路上有些差池导致最后结果有些不尽人意,但是我们已经在尽我们最大努力了。交上了一份还算满意的答卷。从各种文档的阅读到需求分析、概要设计、数据库总体设计、代码编写与调试,我们都准备了好长时间。总的来说,在数据库的规范性方面我们系统比较符合范式,表与表之间存在着比较合理的依赖关系;在实用性方面,我们通过KTV系统服务员可以为顾客开房、预定房间、收银等等,顾客还可以通过服务员进行房间消费查询,并且拥有收银单据,服务员可以通过每日收银单据进行单天收入记录,总的来说整个服务系统还是较为完善的。有着很大的实用性。系统结构符合实际的要求,能够适用于现在许多的KTV;在系统功能方面我们能够满足服务员进行管理的功能、权限管理、房间管理、信息查询等等功能,使得系统正常运行。
一个数据库的设计首先最重要的是要能够满足需求,并且要尽量使其与程序较为符合使编码的难度有所降低,而且就像数据结构决定代码的效率,数据库的结构同样如此,好的表结构对于以后的数据库结构有着莫大的作用。毕竟打好基础才可以稳固的进行下一步。然后数据库的设计,要尽量符合范式,减少数据冗余,表与表间的联系要准确,逻辑要正确从而提高效率和维护数据一致性,但是根据实际经验,适当的冗余可以有利于数据库的查询速度,所以这中间的取舍是我们主要讨论的一个点。数据库设计的步骤先找到所有实体,找到实体间的关系,对已有E-R图进行删减多余信息,最后根据三大基本范式对表进行重新检查。
在此次设计过程中,我们遇到了很多的问题,比如约束设置不合理,最终我们在实践过程中才实现了更好的相关约束;还有字段设置不合理,比如时间,我们设计的是datetime,但是在处理数据过程我们才发现应该使用date更加合理。