小型超市管理系统系统分析.doc

小型超市管理系统系统分析1.1. 系统目标根据需求分析的描述，现在制定系统实现目标如下：l 界面设计简洁、友好、美观大方l 操作简单、快捷方便l 数据存储安全、可靠l 信息分类清晰、准确l 强大的查询功能、保证数据查询的灵活性l 提供销售排行，为管理员提供真实的数据信息l 提供灵活、方便的权限设置功能，使整个系统的管理分工明确l 对用户的输入的数据，系统进行严格的数据检验，尽可能排人为错误1. 3.小型超市管理系统系统概念设计文档（1） 系统E-R图如下：（2） 系统E-R图说明：a. 超市中用户可以销售多种商品，商品可以被多个用户销售b. 超市中商品可由多个供应商供应，供应商可供应多种商品c. 超市中合格的商品入库，不合格的商品被回收即被退回（3） 视图设计(4)逻辑设计文档a.系统关系模型1）用户登录表（用户编号，用户名称，用户密码，用户类型，用户状态）2）商品信息表（商品编号，供应商编号，商品名称，入库日期，生产日期，进货价格，销售价格，有效期，数量下限，单位）3）供应商信息表（供应商编号，供应商名称，供应商地址，供应商电话）4）销售表（销售编号，销售日期，商品名称，商品数量，商品金额，商品编号，操作员）5）进货入库表（入库编号，入库日期，商品编号，供应商编号，商品数量，操作员）6）退货销售表（退票编号，商品编号，商品数量，商品金额，退货时间，操作员）7）退货商品表（退货编号，商品编号，商品名称，退货时间，供应商编号，操作员，数量）8）库存信息表（库存编号，商品编号，用户编号，统计，总价）d. 系统数据库表结构数据库表索引表名中文名account用户登录表manager用户信息表commodity商品信息表provider供应商信息表returncommodity退货商品表returnsale退货销售表sale销售表storage进货入库表storageinfo库存信息表用户登录表（account）字段名类型长度主/外键字段约束中文名AccountnumberIntegerprimary登录号Accountnamevarchar30Not null登录名Accountpasswordvarchar20Not null登录密码accountTypesmallint6Not null登录类型accountStatesmallint6Not null登录状态managerIDIntegerforeignNot null用户ID 用户信息表(manager)字段名类型长度主/外键字段约束中文名managerIDIntegerprimary用户编号managernamevarchar20Not null用户姓名mobilephonevarchar15手机号码phonevarchar15固定电话manageremailvarchar30电子邮件商品信息表（commodity）字段名类型长度主/外键字段约束中文名commodityIDIntegerprimary商品编号Commoditynamevarchar40Not null商品名称producedatedatatimenot null生产日期Inpricenumeric2Not null进货价格salepricenumeric2Not null销售价格storagetimeintNot null有效期downlimitintNot null数量下限unitvarchar8Not null单位providernumberint外键供应商编号indatedate进货日期供应商表（provider）字段名类型长度P/F约束中文名providernumberintP供应商编号providernamevarchar40Not null供应商名称provideraddressvarchar60Not null供应商地址phonevarchar20Not null供应商电话销售表（sale）字段名类型长度P/F约束中文名saleidintP销售票号commodityidintFNot null商品编号selltimedateNot null销售日期numberintNot null销售数量moneyfloatNot null收入operatorvarchar45Not null销售员commoditynamevarchar45Not null商品名称销售退货（returnsale）字段名类型长度P/F约束中文名RsaleidintP退货票号commodityidintNot null商品编号commoditynamevarchar商品名称numberIntNot null商品数量moenyfolatNot null金额returntimedateNot null退货时间operatorvarchar45Not null操作员入库退货表（returncommodity）字段名类型长度P/F约束中文名RidintP退货编号commoditynamevarcgar45Not null商品名称commodityidintNot 商品编号pricefloatNot null价格outdatedateNot null退货时间providervarchar45Not null供应商名称operatorvarchar45Not null销售员numberintNot null退货数量unitvarchar8Not null单位库存信息表(storage)字段名类型长度P/F约束中文名storageidintP入库编号commoditynamevarchar45Not null商品名称commodityidvarcharNot null商品编号unitvachar45Not null单位pricefloat45Not null单价outdatedateNot null退货时间providervarchar45Not null供应商operatorvarchar45Not null操作员numberintNot null数量6. 物理设计文档/*-创建数据库-*/本系统使用mysql数据库，首先我们在Navicat for MySQL的Host: localhost下创建数据库limitedmarket,选择数据库编码为utf8 - UTF-8 Unicode。或使用sql语句create database limitedmarket创建数据库。/*-创建基本表-*/* 创建account表*/DROP TABLE IF EXISTS account;CREATE TABLE account ( accountNumber Integer NOT NULL auto_increment, accountName varchar(30) NOT NULL, accountPassword varchar(20) NOT NULL, accountType smallint(6) NOT NULL, managerID Integer default NULL, accountState smallint(6) NOT NULL, PRIMARY KEY (accountNumber), KEY FK_manage (managerID), CONSTRAINT FK_manage FOREIGN KEY (managerID) REFERENCES manager (managerID) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建manage表*/DROP TABLE IF EXISTS manager;CREATE TABLE manager ( managerID Integer NOT NULL auto_increment, managerName varchar(20) NOT NULL, managerMobilephone varchar(15) , managerRegionNumber varchar(4) , managerEMail varchar(30) default NULL, managerPhone varchar(10) ,PRIMARY KEY (managerID) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建commodity表*/DROP TABLE IF EXISTS commodity;CREATE TABLE commodity ( commodityID integer NOT NULL auto_increment, providernumber int(11) default NULL, commodityname varchar(45) NOT NULL, inDate datetime NOT NULL, producedate datetime NOT NULL, inprice float NOT NULL, saleprice float NOT NULL, storagetime int(11) NOT NULL, downlimit int(11) NOT NULL, unit varchar(8) default NULL, PRIMARY KEY (commodityID), KEY FK_supply (providernumber), CONSTRAINT FK_supply FOREIGN KEY (providernumber) REFERENCES provider (providernumber) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建returncommodity*/DROP TABLE IF EXISTS returncommodity;CREATE TABLE returncommodity ( RCid int unsigned NOT NULL auto_increment, commodityname varchar(45) NOT NULL, commodityid varchar(45) NOT NULL, unit varchar(45) NOT NULL, price float NOT NULL, outdate datetime NOT NULL, provider varchar(45) NOT NULL, operator varchar(45) NOT NULL, number int unsigned NOT NULL, PRIMARY KEY (RCid) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建sale表*/DROP TABLE IF EXISTS sale;CREATE TABLE sale ( commodityname varchar(45) NOT NULL, commodityid int unsigned NOT NULL, sellTime datetime NOT NULL, number varchar(45) NOT NULL, money double NOT NULL, operator varchar(45) NOT NULL, saleid int NOT NULL auto_increment, PRIMARY KEY (saleid) ENGINE=InnoDB DEFAULT CHARSET=gb2312 PACK_KEYS=1;/*创建returnsale表*/DROP TABLE IF EXISTS returnsale;CREATE TABLE returnsale ( Rsaleid int unsigned NOT NULL auto_increment, commodityid int unsigned NOT NULL, commodityname varchar(45) NOT NULL, number int unsigned NOT NULL, money double NOT NULL, returnTime datetime NOT NULL, operator varchar(45) NOT NULL, PRIMARY KEY (Rsaleid) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建provider表*/DROP TABLE IF EXISTS provider;CREATE TABLE provider ( providernumber int(11) NOT NULL auto_increment, phone varchar(20) NOT NULL, provideraddress varchar(60) NOT NULL, providername varchar(40) NOT NULL, PRIMARY KEY (providernumber) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*创建stroge表*/DROP TABLE IF EXISTS storage;CREATE TABLE storage ( storageid int unsigned NOT NULL auto_increment, commodityname varchar(45) NOT NULL, commodityid int unsigned NOT NULL, unit varchar(45) NOT NULL, price float NOT NULL, indate datetime NOT NULL, provider varchar(45) NOT NULL, operators varchar(45) NOT NULL, number int unsigned NOT NULL, PRIMARY KEY (storageid) ENGINE=InnoDB DEFAULT CHARSET=utf8/* 创建storageinfo表*/DROP TABLE IF EXISTS storageinfo;CREATE TABLE storageinfo ( commoditynumber int unsigned NOT NULL, accountid int unsigned NOT NULL, amount int unsigned NOT NULL, storageid int unsigned NOT NULL auto_increment, money double NOT NULL, PRIMARY KEY (storageid) ENGINE=InnoDB DEFAULT CHARSET=utf8;由于本系统设计较复杂，很多需要用到数据冗余性，所以部复杂功能借助存储功能 和 触发器，本系统需要用到的存储过程和触发器的代码，如下：-存储过程操作数据-/*向commodity插入值*/drop procedure if exists insertcommodity;delimiter /create procedure insertcommodity(IN providernumber int, commodityname varchar(45), inprice float,saleprice float, storagetime int, downlimit int, unit varchar(8) , indate date, producedate date)begininsert into commodity(providernumber, commodityname, inprice, saleprice, storagetime, downlimit, unit,indate, producedate)values(providernumber, commodityname, inprice, saleprice, storagetime, downlimit, unit, indate, producedate);end/delimiter ;/*更新commodity的值*/drop procedure if exists updatecommodity;delimiter /create procedure updatecommodity(IN providernumber int, commodityname varchar(45), inprice float,saleprice float, storagetime int, downlimit int, unit varchar(8) , indate date, producedate date, id int(11)beginupdate commodity set providernumber = providernumber, commodityname=commodityname, inprice = inprice, saleprice=saleprice, storagetime=storagetime, downlimit=downlimit,unit=unit, indate=indate, producedate = producedate where commodityid= id ;end/delimiter ;/* 向表storageinfo插入数据*/drop procedure if exists insertstorageinfo;delimiter /create procedure insertstorageinfo(IN commoditynumber int(10),accountid int(10), amount int(10), money double)begininsert into storageinfo( commoditynumber,accountid, amount,money) values(commoditynumber,accountid , amount, money);end/delimiter ;/*向resale表插入数据*/drop procedure if exists insertreturnsale;delimiter /create procedure insertreturnsale(IN commodityname varchar(45), commodityid int(10),returnTime varchar(45), number int(10) ,money double,operator varchar(45)begininsert into returnsale(commodityname , commodityid, returnTime, number,money, operator) values(commodityname , commodityid, returnTime, number,money,operator);end/delimiter ;/*向storage表插入数据*/drop procedure if exists ruku;delimiter /create procedure ruku(IN commodityname varchar(45), commodityid int, unit varchar(45), price float,indate varchar(45), provider varchar(45), operators varchar(45), number int)begininsert into storage (commodityname , commodityid, unit, price,indate, provider, operators, number) values(commodityname , commodityid, unit, price,indate, provider, operators, number);end/delimiter ;/*向sale插入数据*/drop procedure if exists insertsale;delimiter /create procedure insertsale(IN commodityname varchar(45), commodityid int,sellTime varchar(45), number int ,money double,operator varchar(45)begininsert into sale(commodityname , commodityid, sellTime, number,money, operator) values(commodityname , commodityid, sellTime, number,money,operator);end/delimiter ;/*查询storageinfo的值*/drop procedure if exists storageQuery;delimiter /create procedure storageQuery(IN commodityid int)beginselect * from storageinfo where commoditynumber=commodityid;end/delimiter ;/*更新storageinfo数据*/drop procedure if exists storageupdate;delimiter /create procedure storageupdate(IN a