超市销售系统——数据库系统设计
【需求描述】：
（1）超市业务按部门开展，⽬前该超市有采购部、销售部、运营部等，每个员⼯只能在⼀个部门；员⼯在⼊职时需要登记员⼯姓名、性别、出⽣⽇期、⾝份证、等信息。
（2）超市销售的商品由超市运营部门决定，运营部门在确定销售的商品后，录⼊商品的详细信息，包含：商品编码、商品名称、条形码、产地、⽣产⼚家、规格型号、商品进价、商品销售价、当前库存数量、库存下限数量等信息。其中，规格型号直接⽤⽂本表⽰；商品编码⾃⾏设计编码规则，系统⾃动⽣成商品编码。当前库存数量、库存下限数量均要⼤于等于0。商品营运部每天结束后需要统计商品的库存情况，若商品的当前库存数量⼩于等于库存下限时，这些商品不再放⼊货架进⾏销售，当补充了货源后再⾏销售。
（3）销售部的收银员通过商场POS机完成销售，收银员通过扫描商品完成销售并记录销售流⽔相关信息，包含销售时间、销售商品、销售商品单价、销售商品数量、销售商品⾦额、销售总⾦额、收银员等信息。在收银员完成销售收款后，根据销售的商品数量更新销售商品的库存数量，更新算法为：该商品的当前库存数量 – 销售数量；更新后若该商品的当前库存数量⼩于等于该商品的库存下限数量，系统⾃动⽣成需要采购的商品信息，包含商品编码、当前库存量、⽣成时间等。
（4）采购部门的采购员每天统计销售后⽣成的采购商品信息并进⾏货源组织，完成商品的采购；当采购商品到货后，采购员根据到货情况完成商品的⼊库，在⼊库时需要录⼊商品的进货数量，更新商品的当前库存数量，更新算法为：该商品的当前库存数量 + 进货数量。
数据模型设计
1 根据需求描述，设计该系统的概念数据模型，可以⽤E-R图，也可以⽤PowerDesigner⼯具；（10分）
2 将设计的概念数据模型转换为关系数据库的逻辑数据模型（即转换成关系数据库的关系）。（10分）
完整性设计
数据定义操作
1采⽤SQL语⾔完成部门、员⼯和商品的关系模式定义，并完成相关的完整性定义；（5分）
CREATE TABLE dept(
did INT NOT NULL PRIMARY KEY AUTO_INCREMENT,/*部门id 不空且⾃增长*/
dname varchar(10)UNIQUE/*部门名唯⼀*/
);
CREATE TABLE emp(
eid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,/*员⼯id 不空且⾃增长*/
ename varchar(10),
sex varchar(2),
birthday DATE,
sfz varchar(20)UNIQUE,/*⾝份证唯⼀*/
phone varchar(15),
did int,
FOREIGN KEY(did)REFERENCES dept(did)
);
)
CREATE TABLE goods(
gid int NOT NULL PRIMARY KEY AUTO_INCREMENT,/*商品id 不空且⾃增长*/
gname varchar(10),/*商品名称*/
txm varchar(10),/*条形码*/
cd varchar(30),/*产地*/
sccj varchar(20),/*⽣产⼚家*/
ggxh varchar(5),/*规格型号*/
cpjj double,/*商品进价*/
cpssj double,/*商品销售价*/
curnum int,/*当前存库数量*/
xynum int/*存库下限数量*/
)数据库优化sql语句
插⼊的时候要求随机⽣成编码的sql
insert into goods VALUES（uuid()，””,””...);
/*销售流⽔*/
CREATE TABLE xsls(
gid int,/*商品id*/
FOREIGN KEY(gid)REFERENCES goods(gid),/*商品外键*/
xssj DATE,/*销售时间*/
xsspdj double,/*销售商品单价*/
xsspsl int,/*销售商品数量*/
xsspje double,/*销售商品⾦额*/
xsspzje double,/*销售商品总⾦额*/
xszje double,/*销售总⾦额*/
xsyid int,/*销售员编号*/
FOREIGN KEY(xsyid)REFERENCES emp(eid)/*销售员外键*/
)
2因销售流⽔数据量很⼤，现需要根据销售商品查询商品的销售流⽔，请叙述如何提⾼按商品查询销售流⽔的速度，并根据你设计的数据模型，使⽤SQL语⾔完成定义；（3分）
⽅法⼀通过商品的编号创建存储过程，下次直接调⽤存储过程
⽅法⼆给销售流⽔表建⽴索引，可以提升查询速度
⽅法三关联了太多的表，要实现ssql优化
CREATE PROCEDURE findsslsbygid(IN in_gid int)
BEGIN
SELECT*from xsls where gid = in_gid
END$$
3收银员在收银时只关⼼商品的销售价格，不能让销售员看到商品的进价信息，请叙述如何限制收银员查看数据的范围，并根据你设计的数据模型，试⽤SQL语句完成定义。（4分）
定义⼀个给收银员看的视图（商品编号，商品名称，商品销售价）。
CREATE VIEW收银员
AS
SELECT gid 商品编号,gname 商品名称, cpssj 商品销售价
FROM goods
数据操作
根据设计的数据模型，采⽤关系代数完成以下要求
（1）查询个部门的员⼯信息，包含部门名称、姓名、性别、出⽣⽇期；（2分）
（2）查询姓名为“董庆”的收银员的所有销售明细，包含：姓名、商品名称、销售数量、销售时间，销售⾦额；（3分）
（3）查询在‘2020-1-1 00:00:00’到‘2020-5-10 23:59:59’期间各种商品销售的总⾦额；（3分）
（4）查询销售过所有商品的收银员的姓名。（4分）
根据设计的数据模型，采⽤SQL语⾔完成以下要求
（1）查询个部门的员⼯信息，包含部门名称、姓名、性别、出⽣⽇期；（2分）
SELECT eid,ename,sex,birthday from emp,dept where emp.did=dept.did
（2）查询姓名为“董庆”的收银员的所有销售明细，包含：姓名、商品名称、销售数量、销售时间，销售⾦额；（3分）
select gname 商品名称,xsspsl 销售数量,xssj 销售时间,xszje 销售总⾦额from xsls,goods where xsyid =(select eid from emp where ename='董庆')and xsls.gi d=goods.gid
（3）查询在‘2020-1-100:00:00’到‘2020-5-1023:59:59’期间各种商品销售的总⾦额；（3分）
select xszje 销售总⾦额from xsls where xssj BETWEEN2020-1-100:00:00and2020-5-1023:59:59
（4）查询销售过所有商品的收银员的姓名；（4分）
select ename from xsls,emp where xsls.gid
in(select gid from goods)and xsls.eid=emp.eid
（5）为了提⾼当前数据库的查询速度，在数据备份的基础上需要将离职⼈员的销售记录删除。现需将离职⼈员“张丽丽”及她所有的销售记录删除，请使⽤SQL 语句完成删除操作。（3分）
delete from xsls where xsyid=(select eid from emp where name='张丽丽')
某同学根据需求描述，设计了商品销售关系模式如下：
商品销售（商品编码，商品名称，条形码，产地，⽣产⼚家，销售时间、销售数量，销售价，收银员）
对关系“商品销售”，请回答以下问题：
1 给出该关系模式的函数依赖集；（4分）
（商品编码，收银员）->（销售时间，销售数量，销售价）
（商品编码）->（商品名称，条形码，产地，⽣产⼚家）
2 给出该关系模式的候选码；（2分）
候选码：商品编码 + 收银员
3 该关系模式存在哪些问题？（3分）
关系模式存在部分依赖，如条形码只依赖于商品编码，⽽不依赖于该关系模式的候选码
4 判断该关系模式是否达到了3NF，说明理由。若没有达到3NF，请分解使其达到3NF。（4分）
没有到达第三范式，因为关系模式含有部分依赖属于1NF，如条形码只依赖于商品编码，⽽不依赖于该关系模式的候选码。
R1（商品编码，收银员，销售时间，销售数量，销售价）
R2（商品编码，商品名称，条形码，产地，⽣产⼚家）
根据⾃⼰设计的数据模型完成以下要求

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。