56 lines
1.9 KiB
SQL
56 lines
1.9 KiB
SQL
-- Restaurant Database Management System
|
|
--
|
|
-- Instructions:
|
|
-- 1. Create all required tables.
|
|
-- 2. Design appropriate PRIMARY KEY and FOREIGN KEY relationships.
|
|
-- 3. Add suitable constraints based on the requirements.
|
|
-- 4. Insert initial mock data.
|
|
-- 5. Insert at least 3 menu items.
|
|
-- 6. The script should be executable from start to finish without errors.
|
|
|
|
CREATE TABLE users (
|
|
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE,
|
|
password_hash VARCHAR(50) NOT NULL,
|
|
email VARCHAR(150)
|
|
);
|
|
|
|
CREATE TABLE menu_items (
|
|
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
name_ VARCHAR(70) NOT NULL,
|
|
description_ TEXT,
|
|
price NUMERIC(7, 2) NOT NULL CHECK(price > 0),
|
|
category VARCHAR(50)
|
|
);
|
|
|
|
CREATE TABLE orders (
|
|
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
user_id INT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
total_price NUMERIC(7, 2) NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE order_details (
|
|
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
order_id INT NOT NULL REFERENCES orders(id),
|
|
menu_item_id INT NOT NULL REFERENCES menu_items(id),
|
|
quantity SMALLINT NOT NULL CHECK(quantity > 0),
|
|
unit_price NUMERIC(7, 2) NOT NULL CHECK(unit_price > 0)
|
|
);
|
|
|
|
|
|
INSERT INTO menu_items(name_, description_, price, category) VALUES ('Bacon Pizza', 'Medium', 15.99, 'Pizza');
|
|
INSERT INTO menu_items(name_, description_, price, category) VALUES ('Vegan Pizza', 'Small', 10.99, 'Pizza');
|
|
INSERT INTO menu_items(name_, description_, price, category) VALUES ('Coke', 'Mini Can', 2.99, 'Drink');
|
|
|
|
|
|
-- =======================================================
|
|
-- VERIFICATION QUERIES
|
|
-- =======================================================
|
|
--
|
|
-- Uncomment these queries to verify your database.
|
|
--
|
|
-- SELECT * FROM ...;
|
|
-- SELECT * FROM ...;
|
|
-- SELECT * FROM ...;
|
|
-- SELECT * FROM ...; |