What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)

• Section 1: Logical Model ERD design and writeup
• Section 2: Advanced SQL queries (COALESCE, Windowed Functions, String Manipulations, UNION)
• Section 3: SQL operations (CROSS JOIN, INSERT, DELETE, UPDATE)

What did you learn from the changes you have made?

Learned how to create logical models, manage changing data, and apply advanced SQL concepts like window functions and string manipulation.

Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?

No other major approaches considered.

Were there any challenges? If so, what issue(s) did you face? How did you overcome it?

Faced small challenges handling CROSS JOIN explosion and UPDATE queries, especially referencing correct latest quantities, but managed to solve them.

How were these changes tested?

Tested all queries in DB Browser for SQLite. Execution finished successfully.

Execution finished without errors.
Result: query executed successfully. Took 2ms
At line 227:
-- Update current_quantity from vendor_inventory
UPDATE product_units
SET current_quantity = (
SELECT COALESCE(vi.quantity, 0)
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
ORDER BY vi.market_date DESC
LIMIT 1
)
WHERE EXISTS (
SELECT 1
FROM vendor_inventory vi
WHERE vi.product_id = product_units.product_id
);

A reference to a related issue in your repository (if applicable)

Checklist

• [ x] I can confirm that my changes are working as intended