|
【需求描述】：
（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
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.gid=goods.gid
（3）查询在‘2020-1-1 00:00:00’到‘2020-5-10 23:59:59’期间各种商品销售的总金额；（3分）
select xszje 销售总金额 from xsls where xssj BETWEEN 2020-1-1 00:00:00 and 2020-5-10 23: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（商品编码，商品名称，条形码，产地，生产厂家）
根据自己设计的数据模型完成以下要求
1 在收银员完成收银操作写入销售明细更新商品库存时，要检查该商品的当前库存数量，若当前库存数量小于或等于该商品的库存下限数量，系统自动生成需要采购的商品信息，包含商品编码、当前库存量、生成时间等。请使用SQL语言实现触发器完成采购信息的自动生成。（8分）
CREATE TRIGGER sccg
AFTER UPDATE
ON goods FOR EACH ROW
BEGIN
SELECT gid,curnum,NOW() from goods where curnum |