软考高级系统分析师案例分析试题+答案！（02）

案例场景

某果蔬电商平台为用户提供果蔬购买服务。为了吸引用户参与，准备推出产品秒杀活动，设计的部分关系模式如下所示:

商品表:PRODUCTS(p_id, p_name, witheld_stock,actual_stock);属性分别是商品编码，商品名称，预扣库存，实际库存。

秒杀记录表:MSRECORDS(o_id, p_id, user_id,status);属性分别是订单编码，商品编码，用户编码，状态(0-预扣，1-已支付，2-取消)。

订单表:ORDERS(o_id,p_id, user_id, pay_time, status,user_name,

product_name);属性分别是订单编码，商品编码，用户编码，支付时间，订单状态(0-未支付，1-已支付，2-取消)，用户名称，商品名称。

秒杀活动的业务逻辑如下所述，

1、用户秒杀时，实际库存减1，预扣库存加1，秒杀记录表增加1条记录，状态为0;订单表增加1条记录，状态为0;

2、当用户支付成功时，修改订单表的状态为1，同时相应商品的预扣库存减1，秒杀记录状态修改为1;

3、用户取消支付时，修改订单表的状态为2，同时相应商品的预扣库存减1，实际库存加1，秒杀记录状态修改为2。

问题内容:

[问题1] (6分)

该应用系统通过下面的存储过程完成秒杀预扣逻辑:商品实际库存足够，则预扣库存加1，实际库存减 1，并插入秒杀记录，否则回滚。请补全下面的存储过程。

CREATE OR REPLACE PROCEDURE MS_PROCESS(

P_o_idININT,

p_ProductID IN INT,

p_UserID ININT,

p_ProductName IN varchar,

p_UserName IN varchar,

AS

v_ActualStock INT;

BEGIN

SELECT actual_stock INTOv_ActualStock FROM PRODUCTS

WHERE p_id=p_ProductID(1)_;

IF_(2){<}=0 THEN

RAISE_APPLICATION_ERROR(-20001,'库存不足);

END IF;

UPDATE PRODUCTS

SET withheld_stock=withheld_stock+1, actual_stock=actual_stock - 1

WHERE(3)_;

INSERT INTO MSRECORDS (p_id, user_id, status, o_id)

VALUES (p_ProductlD,p_UserlD,0,p_o_id);

INSERT INTO ORDERS (p_id, user_id, status, o_id, pay_time, product_name,

user_name)

VALUES (p_ProductID,p_UserlD，0, p_o_id, NULL, p_ProductName,

p_UserName);

(4);

EXCEPTION WHEN(5)_THEN

(6);

RAISE;

END;

[问题2] (6分)

系统创建下面的触发器，在订单表的记录支付成功后，修改商品表和秒杀记录表中的记录。请补全下面的触发器代码。

CREATE OR REPLACE(1)TRG_PAYMENT_SUCC

(2)UPDATE ON ORDERS

(3)

DECLARE

v_RecordIDINT;

BEGIN

UPDATE MSRECORDS SET(4)

WHEREp_id=:NEW.p_id AND user_id=:NEW.userid;

UPDATE PRODUCTS SET withheld stock=withheld stock -1

WHERE p_id=(5)_;

EXCEPTION WHEN(6)THEN

DBMS_OUTPUT.PUT_LINE('预扣记录不存在');

END;

[问题3](13分)

(1)给出 ORDERS 表的函数依赖集合，并判断该表最高满足第几范式

(1NF/2NF/3NF/BCNF)?(5分)

(2)通过模式分解，使得分解后的所有表均满足BCNF，并说明分解后的表结构。(3分)

(3)该分解是否满足函数依赖保持?请说明理由。(5分)

试题答案:

[问题1]

(1) for update

(2) v_ActualStock

(3) p_id=p_ProductID

(4) COMMIT

(5) others

(6) rollback

[问题2]

(1) TRIGGER

(2) AFTER

(3) FOR EACH ROW WHEN (NEW.status=1)

(4) status=1

(5) :NEW.p_id

(6) OTHERS

[问题 3]

完整试题及答案，点击下方蓝字获取！

系统分析师案例分析试题+答案完整