kinokuniya bookstore database

A database for an online bookstore modelled on Kinokuniya, built entirely in PostgreSQL.

• Schema. 22 tables covering customers and addresses, stores, publishers, authors, memberships, categories, book and non-book products, discounts, inventory, restocks, carts and orders. Enums cover product type, discount type and order status.
• API in the database. The app’s backend is a set of PL/pgSQL functions, split into a customer-facing set and an admin set. Customer functions include add_to_cart, apply_promo_code, search_products_advanced and create_order_from_cart. Admin functions include add_book_product, edit_inventory_threshold and edit_order_status.
• Triggers. One trigger raises a restock alert and creates a restock when inventory drops below its threshold. Another makes sure each customer has exactly one default address.