Leaving a Review

Actors: Registered Shopper

1. The user wants to submit a review after for their order.

2. System checks if order is completed, but not canceled.

SELECT CASE 
         WHEN EXISTS (
             SELECT 1
             FROM order
             WHERE order_num = '0022025000001' AND (status='delivered')
         ) THEN 'Yes'
         ELSE 'No'
       END AS has_review_permission;

3.* The application inserts the review into review.

INSERT INTO review (order_num, comment, rating, last_mod_date) VALUES
('00125100001', 'Excellent quality!', 5, CURRENT_TIMESTAMP);