Second-hand Homeware Marketplace Database (MySQL 8)

Relational database designed and implemented for an e-commerce scenario (Advanced Databases module).
Covers end-to-end: 3NF schema (9 entities, Table-per-Type inheritance), views, stored procedures, trigger, and indexing.

Features

• 3NF schema with 9 entities: user, category, homeware_items, furniture, utensils, decorative_items, shipping, orders, order_items
• TPT inheritance: homeware_items parent; furniture, utensils, decorative_items as subtypes
• Business logic in DB:
  • Views: user_order_summary, item_inventory_report
  • Stored Procedures: create_new_order, update_item_price
  • Trigger: before_order_item_insert (validates stock + stamps unit_price)
• Indexing: example idx_item_name on homeware_items(name) (add as needed)
• Seed data for quick demo

Quickstart (without Docker)

1. Start a local MySQL 8 server.
2. Create a database:

   CREATE DATABASE homeware_market CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
   USE homeware_market;

3. Run scripts in order:

   SOURCE sql/schema.sql;
   SOURCE sql/seed.sql;
   SOURCE sql/views.sql;
   SOURCE sql/procedures.sql;
   SOURCE sql/triggers.sql;

4. Try a few queries:

   SELECT * FROM user_order_summary;
   SELECT * FROM item_inventory_report;
   CALL update_item_price(1, 99.99, 1, @status); SELECT @status;
   SET @order_id = 0; CALL create_new_order(2, 1, 2, 1, @order_id); SELECT @order_id;

Quickstart (with Docker + Adminer)

1. Install Docker Desktop.
2. From the repo root:

   docker compose up -d

3. Open Adminer at http://localhost:8080 and connect:
   • System: MySQL
   • Server: db
   • User: root
   • Password: root
   • Database: homeware_market
4. Import scripts from the sql/ folder (schema, seed, views, procedures, triggers).

ERD

Export your ERD as docs/ERD.png and reference it here:

![ERD](docs/ERD.png)

Example EXPLAIN

See scripts/explain_examples.sql for sample EXPLAIN on common queries.

Notes

• The stored procedures and trigger here are functional reconstructions aligned with the original assignment intent (order creation, price updates, stock validation).
• For production, add: FK indexes, error handlers with rollback, and explicit referential actions (ON DELETE/UPDATE).

License

MIT