# 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.

<figure><img src="/files/YtQZNgc7zLGGeArU1WTt" alt=""><figcaption></figcaption></figure>

**Customer Table**

```sql
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**

```sql
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**

```sql
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**

```sql
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:

```sql
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');

```

Insert Books:

```sql
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:

```sql
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:

```sql
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:

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

Get total stock of Physics and Chemistry books:

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

Find the total sales for Physics books:

```sql
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%';
```

2. 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.

<figure><img src="/files/ZpOwK2fyhIKaTHkpgqZN" alt=""><figcaption></figcaption></figure>

3. 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**.

4. 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**.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://courses.parottasalna.com/database-engineering/entity-relationship-diagram/db-task-003.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.