DB-TASK-003

1. Online Book Store

• 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', '[email protected]', '123 Elm Street, City A', '1234567890'),
('Bob Smith', '[email protected]', '456 Oak Avenue, City B', '0987654321');

Insert Books:

INSERT INTO book (title, author, isbn, price, stock_quantity) VALUES
('Physics Fundamentals', 'John Doe', '978-1234567890', 50.00, 10),
('Advanced Physics', 'Jane Roe', '978-0987654321', 60.00, 5),
('Organic Chemistry', 'Emily Stone', '978-1122334455', 45.00, 8),
('Inorganic Chemistry', 'Michael Green', '978-6677889900', 55.00, 12);

Insert Orders:

INSERT INTO order_table (order_date, customer_id, total_amount) VALUES
('2024-12-01 10:00:00', 1, 155.00), 
('2024-12-02 15:30:00', 2, 105.00); 

Insert Order Details:

INSERT INTO order_details (order_id, book_id, quantity, price) VALUES
(1, 1, 2, 100.00),
(1, 3, 1, 45.00), 
(2, 2, 1, 60.00), 
(2, 4, 1, 55.00);

List all Physics and Chemistry books:

SELECT * 
FROM book 
WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%';

Get total stock of Physics and Chemistry books:

SELECT title, stock_quantity 
FROM book 
WHERE title LIKE '%Physics%' OR title LIKE '%Chemistry%';

Find the total sales for Physics books:

SELECT SUM(price * quantity) AS total_sales
FROM order_details od
JOIN book b ON od.book_id = b.book_id
WHERE b.title LIKE '%Physics%';

1. Library Management System

• A member can borrow multiple books, but each loan belongs to one member.

• A book can be loaned to multiple members over time, but only to one member at a time.

• The return date should be recorded when the book is returned.

1. School Management System

• A student can enroll in multiple classrooms, and each classroom can have multiple students.

• Each classroom is assigned one teacher.

• A teacher can teach multiple classrooms.

1. E-Commerce System

• A customer can place multiple orders, and each order can have multiple products via OrderDetails.

• A product can belong to multiple categories via ProductCategory.

• Each order must be associated with a payment.