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

第一章设计任务与要求目的及意义：此小型餐饮业管理系统的设计主要是为了方便管理，对于各个进出账目，支出和收入的管理便于系统化，在每月汇总计算中分析运营趋势和餐馆的发展方向进行一定的规划。

通过各个月的盈利进行对比，分析那种方式更适合餐馆的有利运营和更好地服务顾客。

实现对餐馆内部各种管理的电子化、自动化，提高各个模块之间的办公效率，为高质量餐馆服务提供保证。

任务：1.能够实现对该系统进行管理的人员的权限限制；2.使餐馆能够及时并灵活的对菜品品种其价位等进行管理；3.为餐馆提供从客户点餐到结算等一系列操作的服务，使之能简单易行、方便、清楚地进行管理第二章系统功能分析一．功能需求：餐饮管理系统中主要包括对以下几种管理：账单管理，财务管理，订餐管理，菜品管理，系统管理。

（1）系统管理：系统管理包括用户名和密码，主要用于用户登陆界面登陆和查询。

（2）账单管理：账单管理包括账单号和餐台，每一个餐台对应一个一个账单号，通过餐台号的记录来对对应的餐台进行记账管理，即就是记录每一个餐台的消费金额。

账单管理是财务管理的一个小分支，是服务于财务管理的。

（3）财务管理：包括账单号，时间和账目。

账目用于记录账单号的消费金额及时间，以便用于结算和汇总。

每日的结算要通过对每个餐台号的消费金额汇总来记录，然后由每日的结算汇总得出每月的结算。

帐务系统功能的完整性。

一旦该系统正式运行，餐厅每日营业帐和全部往来客户帐务的操作结算都将依靠计算机，该系统面对当前餐饮业各种复杂的结算要求应具有很强的应变能力。

（4）订餐管理：订餐管理仅包括单价。

订餐管理是便于顾客订餐和账单管理时对各个菜品消费金额的记录，这样便于账单管理和财务汇总。

（5）菜品管理：菜品管理包括菜名，菜品类别和菜品品种。

菜品管理便于顾客点菜和记录各个餐台的消费记账，菜品品种也便于餐馆的食材采购。

二. 数据需求：账单管理中账单号是主键，每一个餐台对应一个账单号。

财务管理中账单号是主键，每一个账单号都有对应的时间和账目记录。

订餐管理中单价就是主键。

菜品管理中菜名是主键。

系统管理中用户名是主键。

三. 性能需求：该餐饮系统操作简单方便可适应各类中小型餐馆。

第三章系统功能模块设计一. 在该系统功能分析的基础上，考虑PowerBuilder程序编制的特点，得到如下功能模块图。

图1 系统功能模块图二. 数据库设计1. 概念结构设计2. 逻辑结构设计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 asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "ZHANGDAGL" must exist when inserting a child in "CAIWUGL" */if update(ZHANGDAN_NUMBER)beginif (select count(*)from ZHANGDAGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.ZHANGDAN_NUMBER) != @numrowsbeginselect @errno = 30002,@errmsg = 'Parent does not exist in "ZHANGDAGL". Cannot create child in "CAIWUG L".'goto errorendendreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Update trigger "tu_caiwugl" for table "CAIWUGL" */create trigger tu_caiwugl on CAIWUGL for update asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "ZHANGDAGL" must exist when updating a child in "CAIWUGL" */if update(ZHANGDAN_NUMBER)beginif (select count(*)from ZHANGDAGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.ZHANGDAN_NUMBER) != @numrowsbeginselect @errno = 30003,@errmsg = '"ZHANGDAGL" does not exist. Cannot modify child in "CAIWUGL".' goto errorendend/* Modify parent code of "CAIWUGL" for all children in "ZHANGDAGL" */if update(ZHANGDAN_NUMBER)beginupdate ZHANGDAGLset CAI_ZHANGDAN_NUMBER = i1.ZHANGDAN_NUMBERfrom ZHANGDAGL t2, inserted i1, deleted d1where t2.CAI_ZHANGDAN_NUMBER = d1.ZHANGDAN_NUMBERand (i1.ZHANGDAN_NUMBER != d1.ZHANGDAN_NUMBER)endreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Delete trigger "td_caiwugl" for table "CAIWUGL" */create trigger td_caiwugl on CAIWUGL for delete asbegindeclare@numrows int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Delete all children in "ZHANGDAGL" */delete ZHANGDAGLfrom ZHANGDAGL t2, deleted t1where t2.CAI_ZHANGDAN_NUMBER = t1.ZHANGDAN_NUMBERreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Insert trigger "ti_dingcangl" for table "DINGCANGL" */create trigger ti_dingcangl on DINGCANGL for insert asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "ZHANGDAGL" must exist when inserting a child in "DINGCANGL" */ if update(ZHANGDAN_NUMBER)beginif (select count(*)from ZHANGDAGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.ZHANGDAN_NUMBER) != @numrowsbeginselect @errno = 30002,@errmsg = 'Parent does not exist in "ZHANGDAGL". Cannot create child in "DINGCA NGL".'goto errorendendreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Update trigger "tu_dingcangl" for table "DINGCANGL" */create trigger tu_dingcangl on DINGCANGL for update asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "ZHANGDAGL" must exist when updating a child in "DINGCANGL" */if update(ZHANGDAN_NUMBER)beginif (select count(*)from ZHANGDAGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.ZHANGDAN_NUMBER) != @numrowsbeginselect @errno = 30003,@errmsg = '"ZHANGDAGL" does not exist. Cannot modify child in "DINGCANGL".' goto errorendendreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Insert trigger "ti_zhangdagl" for table "ZHANGDAGL" */create trigger ti_zhangdagl on ZHANGDAGL for insert asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "CAIWUGL" must exist when inserting a child in "ZHANGDAGL" */if update(CAI_ZHANGDAN_NUMBER)beginselect @numnull = (select count(*)from insertedwhere CAI_ZHANGDAN_NUMBER is null)if @numnull != @numrowsif (select count(*)from CAIWUGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.CAI_ZHANGDAN_NUMBER) != @numrows - @numnullbeginselect @errno = 30002,@errmsg = 'Parent does not exist in "CAIWUGL". Cannot create child in "ZHANGDAG L".'goto errorendendreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Update trigger "tu_zhangdagl" for table "ZHANGDAGL" */create trigger tu_zhangdagl on ZHANGDAGL for update asbegindeclare@numrows int,@numnull int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Parent "CAIWUGL" must exist when updating a child in "ZHANGDAGL" */if update(CAI_ZHANGDAN_NUMBER)beginselect @numnull = (select count(*)from insertedwhere CAI_ZHANGDAN_NUMBER is null)if @numnull != @numrowsif (select count(*)from CAIWUGL t1, inserted t2where t1.ZHANGDAN_NUMBER = t2.CAI_ZHANGDAN_NUMBER) != @numrows - @numnullbeginselect @errno = 30003,@errmsg = '"CAIWUGL" does not exist. Cannot modify child in "ZHANGDAGL".' goto errorendend/* Modify parent code of "ZHANGDAGL" for all children in "CAIWUGL" */if update(ZHANGDAN_NUMBER)beginupdate CAIWUGLset ZHANGDAN_NUMBER = i1.ZHANGDAN_NUMBERfrom CAIWUGL t2, inserted i1, deleted d1where t2.ZHANGDAN_NUMBER = d1.ZHANGDAN_NUMBERand (i1.ZHANGDAN_NUMBER != d1.ZHANGDAN_NUMBER)end/* Modify parent code of "ZHANGDAGL" for all children in "DINGCANGL" */if update(ZHANGDAN_NUMBER)beginupdate DINGCANGLset ZHANGDAN_NUMBER = i1.ZHANGDAN_NUMBERfrom DINGCANGL t2, inserted i1, deleted d1where t2.ZHANGDAN_NUMBER = d1.ZHANGDAN_NUMBERand (i1.ZHANGDAN_NUMBER != d1.ZHANGDAN_NUMBER)endreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo/* Delete trigger "td_zhangdagl" for table "ZHANGDAGL" */create trigger td_zhangdagl on ZHANGDAGL for delete asbegindeclare@numrows int,@errno int,@errmsg varchar(255)select @numrows = @@rowcountif @numrows = 0return/* Delete all children in "CAIWUGL" */delete CAIWUGLfrom CAIWUGL t2, deleted t1where t2.ZHANGDAN_NUMBER = t1.ZHANGDAN_NUMBER/* Delete all children in "DINGCANGL" */delete DINGCANGLfrom DINGCANGL t2, deleted t1where t2.ZHANGDAN_NUMBER = t1.ZHANGDAN_NUMBERreturn/* Errors handling */error:raiserror @errno @errmsgrollback transactionendgo2) 数据库前台的设计根据数据库的设计，该餐饮业管理系统可以分成四个子模块：▪订餐管理根据客户的点菜单，采用checkbox从已有菜品中选定菜品，并记录。