数据库设计报告-小型餐饮管理系统.doc

第一章 设计任务与要求目的及意义：此小型餐饮业管理系统的设计主要是为了方便管理，对于各个进出账目，支出和收入的管理便于系统化，在每月汇总计算中分析运营趋势和餐馆的发展方向进行一定的规划。通过各个月的盈利进行对比，分析那种方式更适合餐馆的有利运营和更好地服务顾客。实现对餐馆内部各种管理的电子化、自动化，提高各个模块之间的办公效率，为高质量餐馆服务提供保证。任务：1.能够实现对该系统进行管理的人员的权限限制；2.使餐馆能够及时并灵活的对菜品品种其价位等进行管理；3.为餐馆提供从客户点餐到结算等一系列操作的服务，使之能简单易行、方便、 清楚地进行管理第二章 系统功能分析一功能需求：餐饮管理系统中主要包括对以下几种管理：账单管理，财务管理，订餐管理，菜品管理，系统管理。（1）系统管理：系统管理包括用户名和密码，主要用于用户登陆界面登陆和查询。（2）账单管理：账单管理包括账单号和餐台，每一个餐台对应一个一个账单号，通过餐台号的记录来对对应的餐台进行记账管理，即就是记录每一个餐台的消费金额。账单管理是财务管理的一个小分支，是服务于财务管理的。（3）财务管理：包括账单号，时间和账目。账目用于记录账单号的消费金额及时间，以便用于结算和汇总。每日的结算要通过对每个餐台号的消费金额汇总来记录，然后由每日的结算汇总得出每月的结算。帐务系统功能的完整性。一旦该系统正式运行，餐厅每日营业帐和全部往来客户帐务的操作结算都将依靠计算机，该系统面对当前餐饮业各种复杂的结算要求应具有很强的应变能力。（4）订餐管理：订餐管理仅包括单价。订餐管理是便于顾客订餐和账单管理时对各个菜品消费金额的记录，这样便于账单管理和财务汇总。 （5）菜品管理：菜品管理包括菜名，菜品类别和菜品品种。菜品管理便于顾客点菜和记录各个餐台的消费记账，菜品品种也便于餐馆的食材采购。二. 数据需求：账单管理中账单号是主键，每一个餐台对应一个账单号。财务管理中账单号是主键，每一个账单号都有对应的时间和账目记录。订餐管理中单价就是主键。菜品管理中菜名是主键。系统管理中用户名是主键。三. 性能需求：该餐饮系统操作简单方便可适应各类中小型餐馆。第三章 系统功能模块设计一. 在该系统功能分析的基础上，考虑powerbuilder程序编制的特点，得到如下功能模块图。小型餐饮业管理系统系统管理账单管理订餐管理财务管理菜品管理用户管理密码管理账单号管理餐台管理订单查询餐台点餐管理每日账目管理账目时间管理菜名管理菜品管理菜价管理类别管理图1 系统功能模块图二. 数据库设计1. 概念结构设计2. 逻辑结构设计3. 数据库实施 1) 数据库及表结构的创建 设本系统使用的数据库名为小型餐饮业管理系统,根据已设计的关系模式及各模式的完整性要求，现在就可以在sql server 6.x数据库系统中实现这些逻辑结构。下面是创建基本表的sql语句：/* = */* table: caipingl */* = */create table caipingl( cpgl_name char(20) not null, cpgl_danjia integer not null, cpgl_leibie char(8) null , cpgl_pingzhong char(8) null , cpgl_diancai integer null , constraint pk_caipingl primary key (cpgl_name, cpgl_danjia)go/* = */* table: xtgl */* = */create table xtgl( username char(20) not null, password char(8) not null, constraint pk_xtgl primary key (username, password)go/* = */* table: zhangdagl */* = */create table zhangdagl( zhangdan_number numeric not null, zhangdan_cantai numeric null , cai_zhangdan_number numeric null , constraint pk_zhangdagl primary key (zhangdan_number)go/* = */* index: relation_383_fk */* = */create index relation_383_fk on zhangdagl (cai_zhangdan_number)go/* = */* table: caiwugl */* = */create table caiwugl( zhangdan_number numeric not null, caiwu_time timestamp null , caiwu_amount char(10) null , constraint pk_caiwugl primary key (zhangdan_number)go/* = */* table: dingcangl */* = */create table dingcangl( zhangdan_number numeric not null, dcgl_name char(20) not null, dcgl_danjia int not null, constraint pk_dingcangl primary key (zhangdan_number, dcgl_name, dcgl_danjia)go/* = */* index: relation_451_fk */* = */create index relation_451_fk on dingcangl (zhangdan_number)go建立相关触发器，执行级联删除* = */* database name: model_1 */* dbms name: microsoft sql server 6.x */* created on: 2010-1-21 18:15 */* = */* insert trigger ti_caiwugl for table caiwugl */create trigger ti_caiwugl on caiwugl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when inserting a child in caiwugl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30002, errmsg = parent does not exist in zhangdagl. cannot create child in caiwugl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_caiwugl for table caiwugl */create trigger tu_caiwugl on caiwugl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when updating a child in caiwugl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30003, errmsg = zhangdagl does not exist. cannot modify child in caiwugl. goto error end end /* modify parent code of caiwugl for all children in zhangdagl */ if update(zhangdan_number) begin update zhangdagl set cai_zhangdan_number = i1.zhangdan_number from zhangdagl t2, inserted i1, deleted d1 where t2.cai_zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* delete trigger td_caiwugl for table caiwugl */create trigger td_caiwugl on caiwugl for delete asbegin declare numrows int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* delete all children in zhangdagl */ delete zhangdagl from zhangdagl t2, deleted t1 where t2.cai_zhangdan_number = t1.zhangdan_number return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* insert trigger ti_dingcangl for table dingcangl */create trigger ti_dingcangl on dingcangl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when inserting a child in dingcangl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30002, errmsg = parent does not exist in zhangdagl. cannot create child in dingcangl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_dingcangl for table dingcangl */create trigger tu_dingcangl on dingcangl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent zhangdagl must exist when updating a child in dingcangl */ if update(zhangdan_number) begin if (select count(*) from zhangdagl t1, inserted t2 where t1.zhangdan_number = t2.zhangdan_number) != numrows begin select errno = 30003, errmsg = zhangdagl does not exist. cannot modify child in dingcangl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* insert trigger ti_zhangdagl for table zhangdagl */create trigger ti_zhangdagl on zhangdagl for insert asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent caiwugl must exist when inserting a child in zhangdagl */ if update(cai_zhangdan_number) begin select numnull = (select count(*) from inserted where cai_zhangdan_number is null) if numnull != numrows if (select count(*) from caiwugl t1, inserted t2 where t1.zhangdan_number = t2.cai_zhangdan_number) != numrows - numnull begin select errno = 30002, errmsg = parent does not exist in caiwugl. cannot create child in zhangdagl. goto error end end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* update trigger tu_zhangdagl for table zhangdagl */create trigger tu_zhangdagl on zhangdagl for update asbegin declare numrows int, numnull int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* parent caiwugl must exist when updating a child in zhangdagl */ if update(cai_zhangdan_number) begin select numnull = (select count(*) from inserted where cai_zhangdan_number is null) if numnull != numrows if (select count(*) from caiwugl t1, inserted t2 where t1.zhangdan_number = t2.cai_zhangdan_number) != numrows - numnull begin select errno = 30003, errmsg = caiwugl does not exist. cannot modify child in zhangdagl. goto error end end /* modify parent code of zhangdagl for all children in caiwugl */ if update(zhangdan_number) begin update caiwugl set zhangdan_number = i1.zhangdan_number from caiwugl t2, inserted i1, deleted d1 where t2.zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end /* modify parent code of zhangdagl for all children in dingcangl */ if update(zhangdan_number) begin update dingcangl set zhangdan_number = i1.zhangdan_number from dingcangl t2, inserted i1, deleted d1 where t2.zhangdan_number = d1.zhangdan_number and (i1.zhangdan_number != d1.zhangdan_number) end return/* errors handling */error: raiserror errno errmsg rollback transactionendgo/* delete trigger td_zhangdagl for table zhangdagl */create trigger td_zhangdagl on zhangdagl for delete asbegin declare numrows int, errno int, errmsg varchar(255) select numrows = rowcount if numrows = 0 return /* delete all children in caiwugl */ delete caiwugl from caiwugl t2, deleted t1 where t2.zhangdan_number = t1.zhangdan_number /* delete