A customer can place multiple orders, but each order belongs to one customer.
A book can appear in multiple orders, and an order can contain multiple books.
Each order consists of one or more order details entries, specifying the quantity and price of each book in that order.
The stock quantity of a book should decrease when an order is placed.
A book’s price is stored at the time of the order to maintain historical pricing.
Customer Table
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
address TEXT,
phone_number VARCHAR(15)
);
Book Table
CREATE TABLE book (
book_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author VARCHAR(100),
isbn VARCHAR(20) UNIQUE,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL
);
Order Table
CREATE TABLE order_table (
order_id SERIAL PRIMARY KEY ,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
customer_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
OrderDetails Table
CREATE TABLE order_details (
order_details_id SERIAL PRIMARY KEY ,
order_id INT NOT NULL,
book_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES order_table (order_id),
FOREIGN KEY (book_id) REFERENCES book(book_id)
);
Insert Sample Data
Insert Customers:
INSERT INTO customer (name, email, address, phone_number) VALUES
('Alice Johnson', 'alice.johnson@example.com', '123 Elm Street, City A', '1234567890'),
('Bob Smith', 'bob.smith@example.com', '456 Oak Avenue, City B', '0987654321');
