小型餐饮管理系统数据库设计报告Word文件下载.docx
- 文档编号:20650747
- 上传时间:2023-01-24
- 格式:DOCX
- 页数:20
- 大小:239.85KB
小型餐饮管理系统数据库设计报告Word文件下载.docx
《小型餐饮管理系统数据库设计报告Word文件下载.docx》由会员分享,可在线阅读,更多相关《小型餐饮管理系统数据库设计报告Word文件下载.docx(20页珍藏版)》请在冰豆网上搜索。
系统管理中用户名是主键。
三. 性能需求:
该餐饮系统操作简单方便可适应各类中小型餐馆。
第三章系统功能模块设计
一.在该系统功能分析的基础上,考虑PowerBuilder程序编制的特点,得到如下功能模块图。
图1系统功能模块图
二.数据库设计
1.概念结构设计
2.逻辑结构设计
3.数据库实施
1)数据库及表结构的创建
设本系统使用的数据库名为小型餐饮业管理系统,根据已设计的关系模式及各模式的完整性要求,现在就可以在SQLServer6.x数据库系统中实现这些逻辑结构。
下面是创建基本表的SQL语句:
/*============================================================*/
/* Table:
CAIPINGL */
/*============================================================ */
createtableCAIPINGL
(
CPGL_NAME char(20) notnull,
CPGL_DANJIA integer notnull,
CPGL_LEIBIE char(8) null ,
CPGL_PINGZHONG char(8) null ,
CPGL_DIANCAI integer null ,
constraintPK_CAIPINGLprimarykey(CPGL_NAME, CPGL_DANJIA)
)
go
/* ============================================================*/
/* Table:
XTGL */
/*============================================================*/
create tableXTGL
USERNAME char(20) notnull,
PASSWORD char(8) notnull,
constraintPK_XTGLprimary key (USERNAME,PASSWORD)
go
/*============================================================*/
/* Table:
ZHANGDAGL */
/*============================================================ */
create tableZHANGDAGL
ZHANGDAN_NUMBERnumeric notnull,
ZHANGDAN_CANTAI numeric null,
CAI_ZHANGDAN_NUMBERnumeric null ,
constraint PK_ZHANGDAGL primarykey(ZHANGDAN_NUMBER)
/*============================================================ */
/*Index:
RELATION_383_FK */
/*============================================================*/
createindexRELATION_383_FKonZHANGDAGL (CAI_ZHANGDAN_NUMBER)
/*============================================================*/
/* Table:
CAIWUGL */
/*============================================================ */
createtableCAIWUGL
ZHANGDAN_NUMBERnumeric notnull,
CAIWU_TIME timestamp null ,
CAIWU_AMOUNT char(10) null ,
constraintPK_CAIWUGLprimarykey(ZHANGDAN_NUMBER)
/* ============================================================*/
/* Table:
DINGCANGL */
/*============================================================*/
createtableDINGCANGL
ZHANGDAN_NUMBER numeric notnull,
DCGL_NAME char(20) not null,
DCGL_DANJIA int notnull,
constraintPK_DINGCANGLprimarykey(ZHANGDAN_NUMBER,DCGL_NAME, DCGL_DANJIA)
/*============================================================*/
/* Index:
RELATION_451_FK */
/*============================================================ */
createindexRELATION_451_FKon DINGCANGL(ZHANGDAN_NUMBER)
建立相关触发器,执行级联删除
*============================================================ */
/* Databasename:
MODEL_1 */
/* DBMSname:
MicrosoftSQLServer6.x */
/* Createdon:
2010-1-2118:
15 */
/* ============================================================ */
/*Inserttrigger"
ti_caiwugl"
for table"
CAIWUGL" */
createtrigger ti_caiwuglonCAIWUGLforinsertas
begin
declare
@numrows int,
@numnullint,
@errno int,
@errmsgvarchar(255)
select@numrows= @@rowcount
if@numrows=0
return
/* Parent"
ZHANGDAGL"
mustexistwhen insertinga child in"
CAIWUGL" */
ifupdate(ZHANGDAN_NUMBER)
begin
if (selectcount(*)
from ZHANGDAGLt1,inserted t2
wheret1.ZHANGDAN_NUMBER=t2.ZHANGDAN_NUMBER)!
=@numrows
begin
select@errno = 30002,
@errmsg ='Parentdoesnotexistin"
ZHANGDAGL"
. Cannotcreatechild in"
CAIWUGL"
.'
gotoerror
end
end
return
/*Errorshandling */
error:
raiserror @errno@errmsg
rollback transaction
end
/*Updatetrigger"
tu_caiwugl"
fortable"
CAIWUGL"
*/
createtriggertu_caiwuglonCAIWUGLfor updateas
begin
declare
@numrowsint,
@numnull int,
@errno int,
@errmsgvarchar(255)
select@numrows=@@rowcount
if@numrows= 0
return
/*Parent"
ZHANGDAGL"
mustexist when updating achildin"CAIWUGL" */
if update(ZHANGDAN_NUMBER)
begin
if(selectcount(*)
fromZHANGDAGLt1,insertedt2
where t1.ZHANGDAN_NUMBER=t2.ZHANGDAN_NUMBER)!
=@numrows
begin
select@errno = 30003,
@errmsg='
"
ZHANGDAGL"does not exist. Cannot modifychild in "CAIWUGL"
gotoerror
end
end
/* Modifyparentcodeof"CAIWUGL"
forallchildrenin"ZHANGDAGL"
*/
ifupdate(ZHANGDAN_NUMBER)
begin
updateZHANGDAGL
set CAI_ZHANGDAN_NUMBER= i1.ZHANGDAN_NUMBER
from ZHANGDAGLt2,inserted i1,deletedd1
where t2.CAI_ZHANGDAN_NUMBER=d1.ZHANGDAN_NUMBER
and(i1.ZHANGDAN_NUMBER!
=d1.ZHANGDAN_NUMBER)
end
return
/* Errors handling */
error:
raiserror@errno@errmsg
rollbacktransaction
/*Deletetrigger"
td_caiwugl"
fortable"
CAIWUGL" */
createtrigger td_caiwuglon CAIWUGLfordelete as
begin
declare
@numrowsint,
@errnoint,
@errmsg varchar(255)
select @numrows= @@rowcount
if@numrows= 0
return
/*Deleteallchildrenin"ZHANGDAGL"*/
deleteZHANGDAGL
fromZHANGDAGLt2,deleted t1
wheret2.CAI_ZHANGDAN_NUMBER=t1.ZHANGDAN_NUMBER
return
/*Errorshandling */
error:
raiserror@errno@errmsg
rollbacktransaction
end
/*Inserttrigger "
ti_dingcangl"
fortable"
DINGCANGL"
*/
create triggerti_dingcanglon DINGCANGL for insertas
begin
declare
@numrows int,
@numnullint,
@errnoint,
@errmsgvarchar(255)
select@numrows= @@rowcount
if@numrows =0
return
/* Parent"
ZHANGDAGL"mustexistwhen inserting achildin "DINGCANGL"
*/
if update(ZHANGDAN_NUMBER)
begin
if(selectcount(*)
fromZHANGDAGLt1,inserted t2
wheret1.ZHANGDAN_NUMBER =t2.ZHANGDAN_NUMBER)!
= @numrows
begin
select@errno=30002,
@errmsg='
Parentdoesnotexist in"
ZHANGDAGL".Cannotcreatechildin "
DINGCANGL"
.'
gotoerror
end
end
return
/* Errorshandling*/
raiserror@errno @errmsg
rollbacktransaction
/*Update trigger"
tu_dingcangl"
for table"DINGCANGL"*/
createtriggertu_dingcanglonDINGCANGLforupdateas
declare
@numrowsint,
@numnull int,
@errnoint,
@errmsg varchar(255)
select @numrows=@@rowcount
if @numrows= 0
return
/*Parent"ZHANGDAGL"
must existwhenupdating a child in"
DINGCANGL"*/
ifupdate(ZHANGDAN_NUMBER)
if (selectcount(*)
from ZHANGDAGLt1,insertedt2
where t1.ZHANGDAN_NUMBER =t2.ZHANGDAN_NUMBER)!
=@numrows
begin
select @errno=30003,
@errmsg= '
"ZHANGDAGL"
doesnotexist. Cannot modify childin"
DINGCANGL"
gotoerror
end
end
return
/*Errorshandling*/
error:
raiserror@errno @errmsg
rollbacktransaction
end
/*Inserttrigger"ti_zhangdagl"
fortable "ZHANGDAGL"
createtriggerti_zhangdagl onZHANGDAGLforinsertas
begin
declare
@numrows int,
@numnullint,
@errno int,
@errmsg varchar(255)
select@numrows= @@rowcount
if@numrows= 0
return
/* Parent"
CAIWUGL"
mustexistwhen insertingachildin"
ZHANGDAGL"
if update(CAI_ZHANGDAN_NUMBER)
select @numnull=(select count(*)
from inserted
whereCAI_ZHANGDAN_NUMBERisnull)
if@numnull!
=@numrows
if (selectcount(*)
from CAIWUGL t1,insertedt2
wheret1.ZHANGDAN_NUMBER= t2.CAI_ZHANGDAN_NUMBER) !
=@numrows- @numnull
begin
select@errno= 30002,
@errmsg='
Parentdoes notexistin "
CAIWUGL"
.Cannotcreatechild in "
ZHANGDAGL"
goto error
end
end
return
/*Errors handling*/
error:
raiserror@errno@errmsg
rollback transaction
/* Updatetrigger"
tu_zhangdagl"
fortable "
ZHANGDAGL"*/
createtriggertu_zhangdaglonZHANGDAGLforupdateas
declare
@numrows int,
@numnullint,
@errno int,
@errmsgvarchar(255)
select@numrows =@@rowcount
if@numrows =0
return
/* Parent"
CAIWUGL"mustexistwhen updating achildin"
*/
ifupdate(CAI_ZHANGDAN_NUMBER)
begin
select@numnull =(selectcount(*)
from inserted
whereCAI_ZHANGDAN_NUMBERisnull)
if@numnull!
= @numrows
if(select count(*)
fromCAIWUGLt1,insertedt2
wh
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 小型 餐饮 管理 系统 数据库 设计 报告