《数据库原理》课程设计报告

《数据库原理》课程设计报告
题目：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更加合理。