Build Restaurant DB: ERD, SQL Tables, Queries for Portfolio
Design a relational restaurant database using supertype-subtype for transactions, enforce integrity with PK/FK/check constraints, query insights via JOINs/GROUP BY, simplify with views, and speed up with indexes—turns operations into queryable data for real efficiency.
Supertype-Subtype ERD Design Handles Transaction Variants Efficiently
Model restaurant operations by identifying core entities—Customer, Staff, Menu, Table, TransactionHeader, TransactionDetail, Reservation, Takeaway—then map relationships with Crow's foot notation: straight lines for connections, crow's feet for 'many', single bars for 'one', dashed for optional. Mandate relationships like 'transaction must have customer' via labels ('must have' vs 'may have').
Key insight: Use supertype-subtype pattern for TransactionHeader as supertype (shared ID, date, customer, staff) with Reservation (adds table ID, reservation date, people count) and Takeaway (adds queue number, pickup notes) as subtypes. This avoids data duplication since all are transactions but with specialized attributes, enabling flexible growth without separate tables.
Translate conceptual ERD to physical schema defining datatypes (VARCHAR2 for names/emails, NUMBER for IDs/prices/quantities, DATE for dates), primary keys (e.g., Customer_ID), foreign keys (e.g., TransactionHeader.Customer_ID REFERENCES Customer(Customer_ID)), and constraints: NOT NULL for essentials (names, gender), CHECK for validation (email contains '@', phone 10-15 numeric digits, price >=0, quantity >0). Separate TransactionHeader (one row per order) from TransactionDetail (multiple items per order) to normalize multi-item transactions.
Table Implementation Ensures Data Integrity and Traceability
Customer table stores ID (PK), first/last name, gender (NOT NULL), optional phone/email/address/DOB; links to transactions/reservations/takeaways for traceability. Staff adds salary/position. Table tracks ID (PK) and availability status. Menu has ID (PK), name/description (NOT NULL), price (>=0). TransactionHeader links staff/customer IDs (FKs), date, payment (Cash/QRIS/Debit, NOT NULL). Reservation/Takeaway reference TransactionHeader_ID (PK/FK), customer/table IDs (FKs), people count/date/queue. TransactionDetail ties Header_ID (PK/FK), Menu_ID (FK), quantity (>0).
Populate via INSERT INTO with sample data (e.g., 5-10 rows per table shown in images), ensuring FK values exist to prevent errors. This setup centralizes data, reduces redundancy via normalization, and supports scalability by rejecting invalid inputs upfront (e.g., no duplicate IDs, valid formats).
Queries, Views, and Indexes Deliver Operational Insights and Speed
JOIN multiple tables for breakdowns: e.g., SELECT th.ID, customer name (concat first||' '||last), menu name, quantity, price, total (quantity*price) FROM TransactionHeader th JOIN Customer c ON th.Customer_ID=c.Customer_ID JOIN TransactionDetail td ON th.ID=td.Header_ID JOIN Menu m ON td.Menu_ID=m.Menu_ID reconstructs bills. Aggregate popularity: SELECT m.Name, SUM(td.Quantity) AS Total_Sold FROM TransactionDetail td JOIN Menu m ON td.Menu_ID=m.Menu_ID GROUP BY m.Name ORDER BY Total_Sold DESC identifies top items (e.g., Nasi Goreng leads).
Simple views (single-table SELECT, updatable) like simple_staff_view (staff_id, name, salary, etc.) act as shortcuts; INSERT into view updates base table if no JOIN/GROUP/agg/DISTINCT. Complex views pre-join for reports: e.g., transaction summary (Header + Staff + Customer) or detailed orders (Header + Detail + Menu + Customer) for analysis without rewriting queries.
Add indexes on frequent filters like Customer_ID or TransactionDate (CREATE INDEX idx_customer ON Customer(Customer_ID)) to accelerate lookups as data grows, like a book's table of contents. Scenarios prove utility: cashier SELECTs customer info; manager views headers or queues (ORDER BY QueueNumber); kitchen sees details—transforming manual ops into efficient, data-driven decisions.