Untitled

Prelim Exam
Duration 1hr and 30mns
Scenario
You are a Junior Database administrator for a small online store hosted on XAMPP. The shop
sells computer accessories and uses the following simplified database schema.
1. Customer
• Name, Customer ID, product ID, and email
2. Products
• Product ID, product name, category and price
3. Orders
• Product ID, Customer ID, order date and time
4. Order Items
• Item ID, order ID, product ID
Recently, the system has encountered problem:
• Slow queries when customer search product by category
• Stock errors When multiple orders are placed at the same time.
For 50 points
Your task is to evaluate and improve the system. Do the following in XAMPP. Show your SQL
statement and result.
1. Index and Query Optimization
• Create a composite index to speed up product searches by category and price.
• Write an SQL query to display the top 3 best selling product name + total quantity
sold)
2. Transaction Management
Simulate the following order using transaction:
• Customer ID – 2 order, 2 item units
• The transaction must:
o Check if stock is available
o Insert a new order and order items
o Decrease product stock
o Rollback if stock is insufficient.
3. Concurrency Control
• Explain in your own words how different isolation level (read uncommitted, read
committed, repeatable read, and serializable) affect the transaction
• Which isolation level is most suitable for handling stock updates in the system?
Why?
4. Stored Procedure and Trigger (Designer)
• Create a stored producer that increases the stock of the given product.
• Create trigger (designer) tr_after_order_item_insert that after inserting
order_items, logs the stock change into table stock_loh(order_id, prduct_id,
qty_changed logged_at).