KTV-系统的数据库设计

沈 阳 工 学 院数据库应用开发实训报告题 目： KTV 系统的数据库设计 学 院： 信息与控制学院 专 业： 计算机类 学 号： 学生姓名： 李 昊 指导教师： 田丹、董华彪 成 绩： 2018年 7 月 11 日 沈阳工学院实训报告1 系统分析与设计 1.1 需求分析经济的发展在使我们的物质生活水平明显提高的同时，也带来了更为激烈的社会克争各行各业的工作者无不面临着巨大的生活和工作压力。上班一族似乎每天都有干不完的工作，周到周五的工作任务排的满满当当,工作日之余。他们非常渴望通过一定形式的休闲活动释放压力，“给自己的心情放个假”。休闲过节成为一种新时尚当今社会当中，它已经逐渐的容入了年轻人的专利,许许多多的中年人甚至老年人也会在闲暇时间，邀上好友，在KTV一展歌喉。这样不仅可以缓解平日里的压力，还会增进亲友之间的感情。KTV的岀现和发展满足了人们这种需求。在中国，KTV企业逐渐朝着规模化、连锁化、品牌化的经营方式发展，很多KTV企业一般都拥有几家或几十家分店，因而针对不同分店如何统一管理，是KTV企业需要关注的重中之重。KTV管理系统解决方案正是针对此现象而展开。各行业的发展和计算机系统的结合越来越紧密。很多歌厅借助现代高科技所带来的优质的服务，大大提高服务的档次,提升了企业的管理水平,提升了竞争力达到管理的系统化，规范化。KTV管理系统解决方案对于商务交易，保证交易数据的真实性、完整性、一致性，如何防止数据被恶意篡改以及数据破坏后的恢复是至关重要的。KTV管理系统解决方案针对数据库安全，采用双机热备份机制，指定专人对数据库维护，很好地维护电子商务的交易数据。KTV管理系统解决方案通过数据集成组建连接各应用系统，当应用系统或数据库间数据交换规 则、交换方向发生变化时，只需修改交换平台配置，无需源代码开发，是否便于实施和操作。顾客来到KTV一定会开包房消费，但是包房会有大小之分，不同类型的包房的价格也不同。可以按照顾客的需求进行包房的分配，并且及时知道包房剩余状态，更方便快捷的进行包房的分配。并且可以了解所有顾客的性别，年龄，电话，可以准确地为客人服务。进入包房前，顾客会在超市消费，系统可以对超市的账单进行查询，查询营业额，卖出商品的种类，数量以及支付的方式。对服务员可以准确地了解服务员信息，可以输入新招聘的服务员的信息，删除原来辞职的服务员信息，对服务员进行包房分配，有效地提高服务效率，使顾客体验更好。1.2 系统功能设计 KTV管理系统主要设计了顾客信息、服务员信息、路包房信息、超市信息等功能，具体描述如下： （1）顾客信息管理可以实现对顾客即输入任一需要查询的顾客信息，点击查询按钮，反馈结果为该顾客的姓名，性别，电话等信息并且可以添加所有新顾客的信息，删除老顾客的信息。 （2）服务员信息管理功能实现了可以对包房以及服务员信息进行修改更新，可以更新新服务员信息等。 （3）包房信息管理可以进行包房状态查询与更新，在顾客使用和离开包方式可以调整包房状态，管理包房价格与查询包房的对应的负责人。（4）超市信息管理可以查询超市销售情况，了解当天的营业额以及顾客的消费金额的支付方式。2 数据库设计2.1 数据库概念结构设计KTV数据库实体关系图，设计如图2.1所示： 图2.1 KTV管理系统数据库E-R图2.2 数据库逻辑结构设计由实体关系图转换关系模式,结果如下：1顾客（顾客编号，顾客姓名，顾客性别，顾客年龄，顾客电话）2服务员（服务员编号，服务员姓名，服务次数，服务单价）3包房（包房号，包房价格，包房消费，包房类型，包房状态，包房负责人）4超市（物品种类，物品总价，数量，付款方式）2.3 数据库物理结构设计（1）顾客表实现了对于顾客信息的管理，包括顾客编号，顾客姓名，顾客性别，顾客年龄，顾客电话等属性列，其主键为顾客编号。结构如表2.1所示：表2.1 顾客表属性列名数据类型长度约束顾客编号Cnochar10Primary key顾客姓名CnameVarchar8Not null顾客性别CsexChar2Check顾客年龄CageInt4Not null顾客电话ctelVarchar18Unique（2）包房表实现了对于服务员信息的管理，包括服务员编号，服务员姓名。服务次数，服务单价等属性列，其主键为服务员编号。结构如表2.2所示：表2.2 包房表属性列名数据类型长度约束包房号 Rnochar10Primary key包房价格RpriceDecimal10,2Not null包房消费RconsumeMoney8Not null包房类型RtypeVarchar8Not null包房状态RstateChar4default包房负责人rnameVarchar8Not null（3）服务员表实现了对于服务员信息的管理，包括服务员编号，服务员姓名，服务次数，服务单价等属性列，其主键为服务员编号。结构如表2.3所示：表2.3 服务员表属性列名数据类型长度约束服务员编号snoChar8Primary key服务员姓名SnameVarchar10Not null服务次数StimeChar4Not null服务单价Spricemoney8Not null（4）超市表实现了对于超市信息的管理，包括物品种类，物品总价，数量，付款方式等属性列，其主键为物品种类。结构如表2.4所示：表2.4 超市表属性列名数据类型长度约束物品种类MtypeVarchar10Primary key物品总价MpriceDecimal10，2Not null数量MnumberChar10Not null付款方式MwayVarchar10Not null3 数据库实现3.1 创建数据库KTV管理系统创建名称为ktv的数据库，有主文件ktv_data，日志文件ktv_log，辅助文件ktv_data2。创建代码如下： create database ktvon primary(name=ktv_data, size=50,filegrowth=10%,maxsize=500,filename=c:databasektv_data.mdf)log on(name=ktv_log,size=30,filegrowth=10%,maxsize=500,filename=c:databasektv_log.ldf)Alter database ktvADD File(name=ktv_data2,size=30,maxsize=300,filegrowth=5%,filename=c:databasektv_data2.mdf)结果图如图3.1所示：图3.1 数据库结构图3.2 创建表（1）顾客表create table customer(cno char(10) primary key,cname varchar(8) not null,csex char(2),check(csex=男or csex=女),cage int not null,ctel varchar(18)unique)结果如图3.2所示：图3.2 顾客表的创建结果（2）包房表create table room(rno char(10) primary key,rprice decimal(10,2)not null,rconsume money not null,rtype varchar(8)not null,rstate char(4) default空闲,rname varchar(8) not null)结果如图3.3所示 图3.3 包房表的创建结果（3）服务员表create table service(sno char(8)primary key,sname varchar(10)not null,stime char(4)not null,sprice money not null,)结果如图3.4所示图3.4 服务员表的创建结果（4）超市表create table market(mtype varchar(20) primary key,mprice decimal(10,2)not null,mnumber char(10) not null,mway varchar(10) not null,)结果如图3.5所示图3.5 超市表的创建结果3.3 数据操作1.数据录入（1）顾客表信息录入：insert into customer values(01,张三,男,19,)insert into customer values(02,李四,男,20,)insert into customer values(03,张青,女,33,)insert into customer values(04,魏三,男,25,)insert into customer values(05,张显杰,男,22,)（2）包房表信息录入：insert into room values(101,99,200,小包,使用,宋鑫)insert into room values(102,99,0,小包,空闲,李德才)insert into room values(103,99,199,小包,使用,徐一涵)insert into room values(104,99,166,小包,使用,许丹)insert into room values(105,99,99,小包,使用,谢一平)（3）服务员表信息录入：insert into service values(01,宋鑫,16,50)insert into service values(02,李德才,22,86)insert into service values(03,徐一涵,33,90)insert into service values(04,许丹,19,45)insert into service values(05,谢一平,30,68)（4）超市表信息录入：insert into market values(水，面包,5,2,1,微信)insert into market values(香肠,4,2,现金)insert into market values(水，牛奶,6,2,2,支付宝)insert into market values(面包，牛奶,10,3,3,微信)insert into market values(水,5,2,微信)2.数据查询（1）查询顾客表中最年长的顾客年龄。select MAX(cage) from customer结果如图3.6所示：图3.6查询顾客表中最年长的顾客年龄的结果（2）统计男女顾客的人数。select csex,COUNT(cno) from customer group by csex结果如图3.7所示：图3.7统计男女顾客的人数的结果（3） 查询所有姓李的服务员的信息。 select* from service where sname like李% 结果如图3.8所示：图3.8查询所有姓李的服务员的信息（4）查询包房空闲的包房信息。select* from room where rstate like空闲结果如图3.9所示：图3.9 查询包房空闲的包房信息（5）按照销售总价由高到低显示超市的物品种类,物品总价,购买数量及付款方式。select mtype,mprice,mnumber,mway from marker order by mprice desc结果如图3.10所示：图3.10查询第5题的结果（6） 查询服务次数大于30的服务员的信息。select* from service where stime=30结果如图3.11所示：图3.11 查询服务次数大于30的服务员的信息（7）查询年龄在19-30之间的编号,姓名,性别,电话。select cno,cname,csex,ctel from customer where cage=19 and cage=200结果如图3.16所示：图3.16 查询包房消费大于200的包房号价格以及消费（12）按照服务单价由高到低显示服务员的编号，姓名，次数，单价。select sno,sname,stime,sprice from service order by sprice desc结果如图3.17所示：图3.17 按照服务单价由高到低显示服务员的编号，姓名，次数，单价（13）查询服务次数最多的服务员的编号,姓名和服务次数。 select top 1 service.sno,service.sname,service.stime from servicegroup by service.sno,service.sname,service.stime order by service.stime desc结果如图3.18所示： 图3.18查询服务次数最多的服务员的编号,姓名和服务次数（14）查询包房的负责人，服务次数和包房类型。Select service.sname,stime,rtype from room join serviceon(service.sname=room.rname)结果如图3.19所示：图3.19查询包房的负责人，服务次数和包房类型（15）查询包房价格比包房平均价格高的包房的房间号,负责人,价格,包房类型。select rno,rname,rprice,rtype from roomwhere rprice (select AVG(rprice)from room)结果如图3.20所示：图3.20 查询第15题的结果（16）查询超市消费总价低于平均超市消费总价的营业额以及付款方式。select mprice,mway from marketwhere mprice100结果如图3.45所示：图3.45查询视图V5的结果3.5 创建存储过程1. 创建存储过程p1, 查询统计所有男顾客的信息。create proc p1asbegin select * from customer where csex=男 end结果如图3.46示：图3.46查询存储过程p1的结果2.创建存储过程p2. 查询年龄大于25的顾客的姓名,性别和年龄。create proc p2as beginselect cname,csex,cage from customerwhere cage25end结果如图3.47所示：图3.47查询存储过程p2的结果3.创建存储过程p3，统计每种支付方式的收款金额。create proc p3asbeginselect mway ,COUNT(mprice)from marketgroup by mwayend结果如图3.48所示：图3.48查询存储过程p3的结果4.创建存储过程p4，录入顾客刘丽彤信息。create proc p4cno Char(10),cname varchar(8),csex Char(2) ,cage int,ctel varchar(18)asbegin insert into customervalues(cno,cname,csex,cage,ctel)end结果如图3.49所示：图3.49查询存储过程p4的结果5.创建存储过程p5，查询服务员是否存在，存在服务次数加5，不存在显示无该服务员。create proc p5sno char(10)asbeginif exists(select * from service where sno=sno)update serviceset stime=stime+5where sno=snoelseprint无该服务员end结果如图3.50所示：图3.50查询存储过程p5的结果6.创建存储过程p6，查询包房价格最大的包房负责人信息。create proc p6asbeginselect service.*from room join service on(service.sname=room.rname)where rprice=(select MAX(rprice) from room)end结果如图3.51所示：图3.51查询存储过程p6的结果7.创建存储过程p7，查询服务次数在20-50的服务员姓名,编号,服务次数以及房间号和类型。create proc p7asbegin select service.sname,sno,stime,room.rno,rtype from room join service on(service.sname=room.rname) where stime between 20 and 50end结果如图3.52所示：图3.52查询存储过程p7的结果8.创建存储过程p8，查询顾客是否存在年龄小于20，存在查询他的信息，如果不存在显示无。create proc p8asbegin if exists(select * from customer where cage20)select * from customerwhere cage(select AVG(mprice) from market)end结果如图3.54所示：图3.54查询存储过程p9的结果10.创建存储过程p10，查询服务次数大于20次的服务员姓名,编号,服务单价以及负责的包房类型。create proc p10asbeginselect service.sname,sno,sprice,room.rtypefrom room join service on(service.sname=room.rname)where service.stime20end结果如图3.55所示：图3.55查询存储过程p10的结果3.6 创建触发器1. 创建触发器tr1，实现当录入一个服务员名的同时再录入其负责的包房类型以及包房价格。 create trigger tr1 on service for insert as begin declare sname varchar(10) set sname=(select rname from room ) insert into room (rtype ,rprice) values(sname),周宇)end结果如图3.56所示：图3.56触发器tr1的结果2 .创建触发器tr2，实现当录入负责人名的同时再录入该负责人的编号以及服务单价。 create trigger tr2 on room for insert as begin declare rname varchar(8) set rname=(select sname from service ) insert into service (sno ,sprice) values(rname),刘备)end结果如图3.57所示：图3.57触发器tr2的结果3.创建触发器tr3，实现当录入服务员名的同时再录入该服务员负责的包房编号以及使用状态。 create trigger tr3 on service for insert as begin declare sname varchar(10) set