Restaurant DB: ERD to SQL with Supertype-Subtype
Use supertype-subtype pattern in ERD for flexible transactions (headers + reservation/takeaway subtypes); implement with PK/FK constraints, JOIN queries for ops, views/indexes/sequences/synonyms for scale—builds production-ready SQL portfolio.
Supertype-Subtype ERD Cuts Duplication in Transaction Modeling
Model restaurant operations by identifying core entities—Customer, Staff, Menu, Table, TransactionHeader (supertype), TransactionDetail, Reservation/Takeaway (subtypes)—and their relationships via conceptual ERD. Straight lines link entities; crow's foot denotes 'many'; single bars 'one'; dashed lines optional ties like staff-to-transaction. Mandatory links (e.g., transaction must have customer) ensure integrity.
Supertype-subtype groups shared transaction attributes (ID, date, customer, staff) in TransactionHeader while subtypes add specifics: Reservation gets table ID/people/reservation date; Takeaway adds queue/people. This avoids redundant tables, supports growth (e.g., add delivery subtype), and normalizes data—one header links to multiple details for multi-item orders.
Convert to physical schema with datatypes (VARCHAR2 names/emails, NUMBER IDs/prices/quantities, DATE timestamps), PKs (e.g., customer_id NUMBER PRIMARY KEY), FKs (e.g., TransactionHeader.customer_id REFERENCES Customer(customer_id)), NOT NULL on essentials (names, gender), CHECK constraints (phone 10-15 digits numeric, email contains '@', price/quantity >=0). Result: 8 tables (Customer, Staffs, TableInfos, Menus, TransactionHeader, Reservations, Takeaways, TransactionDetails) enforcing one-to-many (customer→transactions), many-to-many via details (transactions→menus).
Populate via INSERTs matching constraints—e.g., Customers get 5 sample rows with validated phones/emails; Menus enforce price >=0; TransactionHeaders link existing staff/customer IDs.
JOINs and Aggregations Deliver Real-Time Ops Insights
Query across tables to simulate restaurant needs: Simple SELECTs pull Customer (ID, name, phone) for cashier lookup or TransactionHeader (ID, date, payment) for manager review—tracks volume/methods.
Full breakdowns use multi-JOINs: Start TransactionHeader, JOIN Customer on customer_id for names, TransactionDetail on header_id for items/quantity, Menu on menu_id for name/price; compute line total (quantity * price). Outputs per-transaction customer, items, subtotals—like dynamic billing.
Analytics: JOIN TransactionDetails→Menus, GROUP BY menu name, SUM(quantity) DESC for top seller (e.g., reveals most-ordered item). Takeaway queue: SELECT * FROM Takeaway ORDER BY queue_number—prioritizes pickup. These reconstruct operations without manual tracking, spotting trends like popular dishes from sold totals.
Views, Indexes, Sequences, Synonyms Speed Scale and Access
Simple views (single-table SELECT, no JOIN/GROUP) like simple_staff_view (staff_id, name, salary, gender, position) act as updatable shortcuts—INSERT into view updates base Staffs table.
Complex views pre-join for reports: transaction_summary (headers + staff/customer names via JOINs on IDs) or customer_menu_tx (headers + details/menu/customer + price)—query once for analysis, no repeated JOINs.
Index menu name (CREATE INDEX idx_menu_name ON menus(name)) skips full scans on WHERE name='Paket Nasi Timbel', vital for large menus/frequent searches.
Sequences auto-generate unique IDs: menu_seq START WITH 4 INCREMENT BY 1; INSERT menu_id='M' || menu_seq.NEXTVAL—formats M004+, prevents dupes/manual errors.
Synonyms alias tables (CREATE SYNONYM menu_eks FOR menus)—shortens queries (SELECT * FROM menu_eks) for readability in big schemas.
Together, these make the system efficient: Views simplify ops, indexes cut query time, sequences ensure ID consistency, synonyms clean code—transforms scattered ops into queryable, scalable data driving reports/reservations/revenue.