MIDTERM EXAM
Name: Đỗ Phạm Hà Chi
Id_student: 11245851
Class: DSEB 66B
Part A – Modeling & Constraints (20 points)
1. Draw an ERD showing entities, relationships (1–n, n–1), and self-reference on
[Link]. Annotate PK/FK. (8 pts)
[Link] five integrity constraints relevant to the schema (state them clearly). (6 pts)
PK constraint – CustomerID, EmployeeID, ProductID, OrderID, and SupplierID
must each be unique and NOT NULL across their respective tables.
FK constraint – [Link] must reference an existing value in
[Link]; similarly, all other foreign keys must reference valid rows in
their parent tables.
UNIQUE constraint – [Link] must be unique across the entire table; no
two customers may share the same email address.
CHECK constraint – [Link] must be within the range [0, 1];
[Link] must be greater than 0; [Link] and
[Link] must be greater than or equal to 0.
Self-referencing FK – [Link] references [Link] in
the same table and allows NULL, meaning a top-level employee (e.g. CEO) has no
manager assigned.
3. Propose two indexes to optimize two queries in Part C and justify each choice. (6
pts)
Index 1:
CREATE INDEX idx_orders_orderdate ON Orders(OrderDate);
Justification: This index optimizes queries that filter rows by date range on the
Orders table. Without it, MySQL performs a full table scan for every date-based
condition. With it, the engine can seek directly to the relevant date range, significantly
reducing I/O. This benefits queries C1 (last 90 days), C2 (last 60 days), and C3
(current quarter).
Index 2:
CREATE INDEX idx_orderitems_productid ON OrderItems(ProductID);
Justification: This index optimizes JOIN operations between OrderItems and
Products on the ProductID column. Since ProductID is a foreign key in
OrderItems and is frequently used as a join condition, indexing it allows the engine
to perform an index lookup instead of a full scan of OrderItems for every matching
product. This benefits queries C2 (stock vs. sold comparison) and C5 (revenue ranking
by product).
Part B – DDL to Create Tables (15 points)
CREATE TABLE Customers (
CustomerID INT NOT NULL AUTO_INCREMENT,
FullName VARCHAR(100) NOT NULL,
Email VARCHAR(150) NOT NULL,
Phone VARCHAR(20),
City VARCHAR(50),
CreatedAt DATETIME DEFAULT NOW(),
PRIMARY KEY (CustomerID),
UNIQUE KEY uq_customer_email (Email)
);
CREATE TABLE Employees (
EmployeeID INT NOT NULL AUTO_INCREMENT,
FullName VARCHAR(100) NOT NULL,
Title VARCHAR(50),
HiredDate DATE,
ManagerID INT DEFAULT NULL,
PRIMARY KEY (EmployeeID),
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
ON DELETE SET NULL
);
CREATE TABLE Suppliers (
SupplierID INT NOT NULL AUTO_INCREMENT,
SupplierName VARCHAR(100) NOT NULL,
ContactEmail VARCHAR(150),
City VARCHAR(50),
PRIMARY KEY (SupplierID)
);
CREATE TABLE Products (
ProductID INT NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(150) NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UnitsInStock INT NOT NULL DEFAULT 0,
SupplierID INT,
PRIMARY KEY (ProductID),
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
CHECK (UnitPrice >= 0),
CHECK (UnitsInStock >= 0)
);
CREATE TABLE Orders (
OrderID INT NOT NULL AUTO_INCREMENT,
CustomerID INT NOT NULL,
EmployeeID INT,
OrderDate DATETIME DEFAULT NOW(),
ShipperName VARCHAR(100),
Freight DECIMAL(10,2) DEFAULT 0.00,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
CHECK (Freight >= 0)
);
CREATE TABLE OrderItems (
OrderID INT NOT NULL,
ProductID INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
Quantity INT NOT NULL,
Discount DECIMAL(4,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
CHECK (Quantity > 0),
CHECK (UnitPrice >= 0),
CHECK (Discount BETWEEN 0 AND 1)
);
Part C – SQL Queries (30 points)
1. Return the Top 5 customers by total revenue in the last 90 days. Revenue =
Σ([Link] * Quantity * (1 - Discount)). (6 pts)
SELECT
[Link],
[Link],
SUM([Link] * [Link] * (1 - [Link])) AS TotalRevenue
FROM Customers c
JOIN Orders o ON [Link] = [Link]
JOIN OrderItems oi ON [Link] = [Link]
WHERE [Link] >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY [Link], [Link]
ORDER BY TotalRevenue DESC
LIMIT 5;
2. List products where UnitsInStock is less than the total quantity sold in the last 60
days. (6 pts)
SELECT
[Link],
[Link],
[Link],
SUM([Link]) AS TotalSold
FROM Products p
JOIN OrderItems oi ON [Link] = [Link]
JOIN Orders o ON [Link] = [Link]
WHERE [Link] >= DATE_SUB(NOW(), INTERVAL 60 DAY)
GROUP BY [Link], [Link], [Link]
HAVING [Link] < SUM([Link]);
3. Find the employee with the highest average revenue per order in the current
quarter. (6 pts)
SELECT
[Link],
[Link],
AVG(order_rev.Revenue) AS AvgRevenuePerOrder
FROM Employees e
JOIN Orders o ON [Link] = [Link]
JOIN (
SELECT
[Link],
SUM([Link] * [Link] * (1 - [Link])) AS Revenue
FROM OrderItems oi
GROUP BY [Link]
) AS order_rev ON [Link] = order_rev.OrderID
WHERE QUARTER([Link]) = QUARTER(NOW())
AND YEAR([Link]) = YEAR(NOW())
GROUP BY [Link], [Link]
ORDER BY AvgRevenuePerOrder DESC
LIMIT 1;
4. For each customer city, compute the percentage of orders with Freight = 0, and
sort descending by this percentage. (6 pts)
SELECT
[Link],
COUNT(*) AS TotalOrders,
SUM(CASE WHEN [Link] = 0 THEN 1 ELSE 0 END) AS FreeFreightOrders,
ROUND(
100.0 * SUM(CASE WHEN [Link] = 0 THEN 1 ELSE 0 END) / COUNT(*),
2
) AS FreeFreightPct
FROM Customers c
JOIN Orders o ON [Link] = [Link]
GROUP BY [Link]
ORDER BY FreeFreightPct DESC;
5. Use a CTE and a window function to rank products by revenue in the current
month; return Top 10 with product identifier/name, revenue, and rank. (6 pts)
WITH monthly_revenue AS (
SELECT
[Link],
[Link],
SUM([Link] * [Link] * (1 - [Link])) AS Revenue
FROM Products p
JOIN OrderItems oi ON [Link] = [Link]
JOIN Orders o ON [Link] = [Link]
WHERE MONTH([Link]) = MONTH(NOW())
AND YEAR([Link]) = YEAR(NOW())
GROUP BY [Link], [Link]
)
SELECT
ProductID,
ProductName,
Revenue,
RANK() OVER (ORDER BY Revenue DESC) AS RevenueRank
FROM monthly_revenue
ORDER BY RevenueRank
LIMIT 10;
Part D – Reporting VIEW (10 points)
Create VIEW v_order_summary with columns: OrderID, OrderDate, CustomerName,
EmployeeName, TotalAmount (use the revenue formula from Part C.4).
Requirements: proper JOINs, correct GROUP BY, and SQL SECURITY DEFINER.
Briefly justify the choice of security mode inside a comment.
CREATE DEFINER = CURRENT_USER
SQL SECURITY DEFINER
VIEW v_order_summary AS
SELECT
[Link],
[Link],
[Link] AS CustomerName,
[Link] AS EmployeeName,
SUM([Link] * [Link] * (1 - [Link])) AS TotalAmount
FROM Orders o
JOIN Customers c ON [Link] = [Link]
LEFT JOIN Employees e ON [Link] = [Link]
JOIN OrderItems oi ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link];
Part E – Stored Procedure & Trigger (15 points)
E.1 Stored Procedure sp_create_order (9 pts)
Inputs: in_p_customer_id INT, in_p_employee_id INT, in_p_shipper
VARCHAR(100), in_p_items JSON (array of items with fields product_id, qty,
unit_price, discount).
Processing:
o Create a new row in Orders (OrderDate = NOW(), Freight allowed to be
0).
o Insert corresponding rows into OrderItems parsed from in_p_items.
o Validate: qty > 0, unit_price >= 0, discount ∈ [0,1], product exists.
o Decrease [Link] by ordered quantity.
o If stock would be negative, raise an error.
Output: Return the new OrderID (by SELECT or OUT parameter).
DELIMITER $$
CREATE PROCEDURE sp_create_order(
IN in_p_customer_id INT,
IN in_p_employee_id INT,
IN in_p_shipper VARCHAR(100),
IN in_p_items JSON,
OUT out_order_id INT
)
BEGIN
DECLARE v_new_order_id INT;
DECLARE v_i INT DEFAULT 0;
DECLARE v_total INT;
DECLARE v_product_id INT;
DECLARE v_qty INT;
DECLARE v_unit_price DECIMAL(10,2);
DECLARE v_discount DECIMAL(4,2);
DECLARE v_stock INT;
-- Bắt đầu transaction
START TRANSACTION;
-- Tạo đơn hàng mới
INSERT INTO Orders(CustomerID, EmployeeID, OrderDate, ShipperName,
Freight)
VALUES (in_p_customer_id, in_p_employee_id, NOW(), in_p_shipper, 0);
SET v_new_order_id = LAST_INSERT_ID();
SET v_total = JSON_LENGTH(in_p_items);
-- Lặp qua từng item trong JSON
WHILE v_i < v_total DO
SET v_product_id = JSON_UNQUOTE(JSON_EXTRACT(in_p_items,
CONCAT('$[', v_i, '].product_id')));
SET v_qty = JSON_UNQUOTE(JSON_EXTRACT(in_p_items,
CONCAT('$[', v_i, '].qty')));
SET v_unit_price = JSON_UNQUOTE(JSON_EXTRACT(in_p_items,
CONCAT('$[', v_i, '].unit_price')));
SET v_discount = JSON_UNQUOTE(JSON_EXTRACT(in_p_items,
CONCAT('$[', v_i, '].discount')));
-- Validate từng trường
IF v_qty <= 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Quantity must be greater than 0';
END IF;
IF v_unit_price < 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Unit price cannot be negative';
END IF;
IF v_discount < 0 OR v_discount > 1 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Discount must be between 0 and 1';
END IF;
-- Kiểm tra product tồn tại và lấy stock
SELECT UnitsInStock INTO v_stock
FROM Products
WHERE ProductID = v_product_id
FOR UPDATE; -- khóa hàng để tránh race condition
IF v_stock IS NULL THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Product does not exist';
END IF;
-- Kiểm tra stock đủ không
IF v_stock - v_qty < 0 THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Insufficient stock for product';
END IF;
-- Chèn vào OrderItems
INSERT INTO OrderItems(OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (v_new_order_id, v_product_id, v_unit_price, v_qty, v_discount);
-- Giảm tồn kho
UPDATE Products
SET UnitsInStock = UnitsInStock - v_qty
WHERE ProductID = v_product_id;
SET v_i = v_i + 1;
END WHILE;
COMMIT;
SET out_order_id = v_new_order_id;
END$$
DELIMITER ;
E.2 Trigger trg_products_nonnegative (6 pts)
Requirement: BEFORE UPDATE on Products, if [Link] < 0, raise an
error with a clear message.
DELIMITER $$
CREATE TRIGGER trg_products_nonnegative
BEFORE UPDATE ON Products
FOR EACH ROW
BEGIN
IF [Link] < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'UnitsInStock cannot be negative after update';
END IF;
END$$
DELIMITER ;
Part F – Call Procedure from Python (10 points)
import [Link]
import json
DB_CONFIG = {
"host": "localhost",
"user": "root",
"password": "1234",
"database": "minishop",
}
SAMPLE_ITEMS = [Link]([
{"product_id": 1, "qty": 2, "unit_price": 15.99, "discount": 0.1},
{"product_id": 3, "qty": 1, "unit_price": 49.99, "discount": 0.0},
{"product_id": 5, "qty": 3, "unit_price": 8.50, "discount": 0.05},
])
def main():
conn = None
cursor = None
try:
conn = [Link](**DB_CONFIG)
cursor = [Link]()
args = (1, 2, "FastShip", SAMPLE_ITEMS, 0) # 0 = placeholder cho OUT
result_args = [Link]("sp_create_order", args)
# Lấy OrderID trả về (tham số OUT ở vị trí index 4)
new_order_id = result_args[4]
print(f"[OK] New OrderID created: {new_order_id}")
# --- Truy vấn view để hiển thị đơn hàng vừa tạo ---
[Link](
"SELECT * FROM v_order_summary WHERE OrderID = %s",
(new_order_id,)
)
row = [Link]()
if row:
cols = [d[0] for d in [Link]]
print("\n--- Order Summary ---")
for col, val in zip(cols, row):
print(f" {col}: {val}")
except [Link] as db_err:
print(f"[DB ERROR] {db_err.errno}: {db_err.msg}")
except Exception as e:
print(f"[ERROR] Unexpected error: {e}")
finally:
if cursor:
[Link]()
if conn and conn.is_connected():
[Link]()
print("\n[INFO] Connection closed.")
if __name__ == "__main__":
main()