Answer to Question #260489 in Databases | SQL | Oracle | MS Access for Agy

Question

Implemen the following business rules in SQL database:

a) The purchase order value must never exceed M500 000. In instances where the purchase order surpasses M500

000 , a message should be displayed

b) The selling price must be greater than the unit buying price

c) Clothing with the same ID cannot be sold to two customers

d) A message should be displayed when stock levels are less than 10 for each product in stock

Answer 1

a) 
ALTER TABLE purchase 
      
   CHECK (purchase_order< 500000);

b) ALTER TABLE transaction
      
   CHECK (selling_price>buying_price);

c)  ALTER TABLE clothing 
      clothing_id VARCHAR(255) NOT NULL UNIQUE,
d) ALTER TABLE purchase 
      
   CHECK (stock_levels < 10);