forked from AdvancedProgramming1404/WS-10-Database
142 lines
7.4 KiB
SQL
142 lines
7.4 KiB
SQL
|
|
-- =========================================================
|
|
-- WS 10 - Restaurant Database Management System
|
|
-- database.sql
|
|
-- =========================================================
|
|
|
|
-- Drop tables if they already exist (useful when re-running the script)
|
|
DROP TABLE IF EXISTS order_details;
|
|
DROP TABLE IF EXISTS orders;
|
|
DROP TABLE IF EXISTS menu_items;
|
|
DROP TABLE IF EXISTS users;
|
|
|
|
-- =========================================================
|
|
-- 1. USERS (Customer)
|
|
-- =========================================================
|
|
CREATE TABLE users (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email VARCHAR(100)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 2. MENU_ITEMS
|
|
-- =========================================================
|
|
CREATE TABLE menu_items (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description VARCHAR(255),
|
|
price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
|
|
category VARCHAR(50)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 3. ORDERS
|
|
-- =========================================================
|
|
CREATE TABLE orders (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users (id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
total_price NUMERIC(10, 2) NOT NULL CHECK (total_price >= 0)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 4. ORDER_DETAILS (items inside an order)
|
|
-- =========================================================
|
|
CREATE TABLE order_details (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
order_id BIGINT NOT NULL REFERENCES orders (id),
|
|
menu_item_id BIGINT NOT NULL REFERENCES menu_items (id),
|
|
quantity INT NOT NULL CHECK (quantity > 0),
|
|
unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- Initial mock data
|
|
-- =========================================================
|
|
|
|
-- Sample users
|
|
-- NOTE: these password_hash values are just placeholders.
|
|
-- Your Java app must hash real passwords before inserting (e.g. with BCrypt).
|
|
INSERT INTO users (username, password_hash, email) VALUES
|
|
('john_doe', '$2a$10$placeholderHashValue1234567890', 'john@example.com'),
|
|
('jane_smith', '$2a$10$placeholderHashValue1234567891', 'jane@example.com');
|
|
|
|
-- Sample menu items (at least 3 required)
|
|
INSERT INTO menu_items (name, description, price, category) VALUES
|
|
('Pizza Margherita', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Main'),
|
|
('Cheeseburger', 'Beef patty with cheddar cheese and house sauce', 8.00, 'Main'),
|
|
('Pasta Carbonara', 'Pasta with egg, pancetta and parmesan', 12.00, 'Main'),
|
|
('Tiramisu', 'Traditional Italian coffee-flavored dessert', 6.50, 'Dessert'),
|
|
('Cola', 'Soft drink, 330ml can', 2.50, 'Drink');-- =========================================================
|
|
-- WS 10 - Restaurant Database Management System
|
|
-- database.sql
|
|
-- =========================================================
|
|
|
|
-- Drop tables if they already exist (useful when re-running the script)
|
|
DROP TABLE IF EXISTS order_details;
|
|
DROP TABLE IF EXISTS orders;
|
|
DROP TABLE IF EXISTS menu_items;
|
|
DROP TABLE IF EXISTS users;
|
|
|
|
-- =========================================================
|
|
-- 1. USERS (Customer)
|
|
-- =========================================================
|
|
CREATE TABLE users (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
email VARCHAR(100)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 2. MENU_ITEMS
|
|
-- =========================================================
|
|
CREATE TABLE menu_items (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
description VARCHAR(255),
|
|
price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
|
|
category VARCHAR(50)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 3. ORDERS
|
|
-- =========================================================
|
|
CREATE TABLE orders (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
user_id BIGINT NOT NULL REFERENCES users (id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
total_price NUMERIC(10, 2) NOT NULL CHECK (total_price >= 0)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- 4. ORDER_DETAILS (items inside an order)
|
|
-- =========================================================
|
|
CREATE TABLE order_details (
|
|
id BIGSERIAL NOT NULL PRIMARY KEY,
|
|
order_id BIGINT NOT NULL REFERENCES orders (id),
|
|
menu_item_id BIGINT NOT NULL REFERENCES menu_items (id),
|
|
quantity INT NOT NULL CHECK (quantity > 0),
|
|
unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0)
|
|
);
|
|
|
|
-- =========================================================
|
|
-- Initial mock data
|
|
-- =========================================================
|
|
|
|
-- Sample users
|
|
-- NOTE: these password_hash values are just placeholders.
|
|
-- Your Java app must hash real passwords before inserting (e.g. with BCrypt).
|
|
INSERT INTO users (username, password_hash, email) VALUES
|
|
('john_doe', '$2a$10$placeholderHashValue1234567890', 'john@example.com'),
|
|
('jane_smith', '$2a$10$placeholderHashValue1234567891', 'jane@example.com');
|
|
|
|
-- Sample menu items (at least 3 required)
|
|
INSERT INTO menu_items (name, description, price, category) VALUES
|
|
('Pizza Margherita', 'Classic pizza with tomato, mozzarella and basil', 10.00, 'Main'),
|
|
('Cheeseburger', 'Beef patty with cheddar cheese and house sauce', 8.00, 'Main'),
|
|
('Pasta Carbonara', 'Pasta with egg, pancetta and parmesan', 12.00, 'Main'),
|
|
('Tiramisu', 'Traditional Italian coffee-flavored dessert', 6.50, 'Dessert'),
|
|
('Cola', 'Soft drink, 330ml can', 2.50, 'Drink'); |