案例場景

某果蔬電商平臺為用戶提供果蔬購買服務。為了吸引用戶參與，準備推出產品秒殺活動，設計的部分關系模式如下所示:

商品表: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]

完整試題及答案，點擊下方藍字獲取！

系統分析師案例分析試題+答案完整