96 lines
4.0 KiB
SQL
96 lines
4.0 KiB
SQL
-- Restaurant Database Management System
|
|
--
|
|
-- This script creates the whole schema from scratch and inserts some
|
|
-- initial data. It can be run from start to finish without errors.
|
|
--
|
|
-- Run with: psql -U postgres -d restaurant_db -f database.sql
|
|
|
|
|
|
-- Drop old tables first so the script is re-runnable.
|
|
-- Order matters because of the foreign keys (drop children before parents).
|
|
DROP TABLE IF EXISTS order_details CASCADE;
|
|
DROP TABLE IF EXISTS orders CASCADE;
|
|
DROP TABLE IF EXISTS menu_items CASCADE;
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
|
|
|
|
-- =======================================================
|
|
-- USER TABLE
|
|
-- =======================================================
|
|
-- Represents customers using the system.
|
|
-- Passwords are stored as a hash (never plain text) by the Java app.
|
|
CREATE TABLE users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL, -- SHA-256 hex hash
|
|
email VARCHAR(255) -- optional
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- MENU ITEM TABLE
|
|
-- =======================================================
|
|
-- Represents available food and drink items.
|
|
CREATE TABLE menu_items (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT, -- optional
|
|
price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
|
|
category VARCHAR(50) -- optional
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- ORDER TABLE ("order" is a reserved word, so we use "orders")
|
|
-- =======================================================
|
|
-- Each order belongs to exactly one user (One-to-Many: user -> orders).
|
|
CREATE TABLE orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
total_price NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (total_price >= 0)
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- ORDER DETAIL TABLE
|
|
-- =======================================================
|
|
-- One line inside an order (One-to-Many: order -> order_details).
|
|
-- "price" is the item price at the moment of purchase, so history stays
|
|
-- correct even if the menu price changes later.
|
|
CREATE TABLE order_details (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
menu_item_id INT NOT NULL REFERENCES menu_items(id),
|
|
quantity INT NOT NULL CHECK (quantity > 0),
|
|
price NUMERIC(10, 2) NOT NULL CHECK (price > 0)
|
|
);
|
|
|
|
|
|
-- =======================================================
|
|
-- INITIAL MENU DATA (at least 3 items)
|
|
-- =======================================================
|
|
INSERT INTO menu_items (name, description, price, category) VALUES
|
|
('Margherita Pizza', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Pizza'),
|
|
('Cheeseburger', 'Beef patty with cheddar, lettuce and tomato', 8.00, 'Burger'),
|
|
('Pasta Bolognese', 'Spaghetti with rich beef and tomato sauce', 12.00, 'Pasta'),
|
|
('Caesar Salad', 'Romaine, croutons, parmesan and Caesar dressing', 6.50, 'Salad'),
|
|
('Coca-Cola', 'Chilled 330ml can', 2.50, 'Drink');
|
|
|
|
|
|
-- =======================================================
|
|
-- OPTIONAL TEST DATA
|
|
-- =======================================================
|
|
-- Sample user. The password below is the SHA-256 hash of the text "1234"
|
|
-- so you can log in with username "admin" / password "1234" for testing.
|
|
INSERT INTO users (username, password, email) VALUES
|
|
('admin', '03ac674216f3e15c761ee1a5e255f067953623c8b388b4459e13f978d7c846f4', 'admin@pizzeria.com');
|
|
|
|
|
|
-- =======================================================
|
|
-- VERIFICATION QUERIES (optional, uncomment to check)
|
|
-- =======================================================
|
|
-- SELECT * FROM users;
|
|
-- SELECT * FROM menu_items;
|
|
-- SELECT * FROM orders;
|
|
-- SELECT * FROM order_details; |