# paras4902/onlinebookstore_db architecture

> Architecture diagram of the GitHub repository paras4902/onlinebookstore_db, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-01). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/paras4902/onlinebookstore_db
- Repository: https://github.com/paras4902/onlinebookstore_db

## Overview

##### Project type and purpose
- This repository is a database‐centric project: it provides the schema definition, data‐loading scripts, and sample data for an Online Book Store.
- Its goal is to create an Oracle (or other relational) database: define tables, sequences, tablespaces/users, and then populate them with realistic data via SQL scripts (and helper Python scripts).

##### File structure
- Top level:
- Creation of tables, user n tablespace/… – SQL scripts to set up your tablespace, user, grants, sequences, and tables.
- Insert Scripts/1_*.txt through 9_*.txt – pure SQL INSERT statements for each table.
- Multiple Excel files – source data for authors, books, customers, etc.
- python script for generating dates.py, repeating existing numbers.py, repeating existing shipping types.py – data‐generation utilities.
- LICENSE, README.md, OnlineBookStore_ProblemStatement.pdf – documentation and licensing.
- Patterns: a layered approach where you first set up the environment (tablespace & user), then schema (sequences/tables), then data (INSERT scripts, aided by Python).

##### README for additional insights
- The README confirms this is “Database for Online book store with database design(ERD).”

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data Sources
    subgraph "Data Sources"
        EX1["Creation of insert script for books table.xlsx"]:::source
        EX2["Customers Data with insert script.xlsx"]:::source
        EX3["Data for shopping cart table with insert script.xlsx"]:::source
        EX4["Order Details Table data with insert script.xlsx"]:::source
        EX5["Purchase history data with insert script.xlsx"]:::source
    end

    %% Data Preparation
    subgraph "Data Preparation"
        PY1["python script for generating dates.py"]:::processing
        PY2["python script for repeating existing numbers.py"]:::processing
        PY3["python script for repeating existing shipping types.py"]:::processing
    end

    %% Schema Provisioning
    subgraph "Schema Provisioning"
        SC1["Task_1__script_for_create_tablespace_and_user_and_granting_create_session_and_create_table_privilege.txt"]:::processing
        SC2["Task_2__script_for_create_sequence.txt"]:::processing
        SC3["Task_3__script_for_create_tables.txt"]:::processing
    end

    %% Data Loading
    subgraph "Data Load"
        DL1["1_AUTHORS.txt"]:::processing
        DL2["2_PUBLISHERS.txt"]:::processing
        DL3["3_BOOKS.txt"]:::processing
        DL4["4_CREDIT_CARD_DETAILS.txt"]:::processing
        DL5["5_CUSTOMERS.txt"]:::processing
        DL6["6_SHIPPING_TYPE.txt"]:::processing
        DL7["7_SHOPPING_CART.txt"]:::processing
        DL8["8_ORDER_DETAILS.txt"]:::processing
        DL9["9_PURCHASE_HISTORY.txt"]:::processing
    end

    %% Database
    subgraph "Database Server" 
        subgraph "OnlineBookStore_DB" 
            AUTHORS["AUTHORS\nPK: author_id"]:::storage
            PUBLISHERS["PUBLISHERS\nPK: publisher_id"]:::storage
            BOOKS["BOOKS\nPK: book_id\nFK: author_id → AUTHORS.author_id\nFK: publisher_id → PUBLISHERS.publisher_id"]:::storage
            CUSTOMERS["CUSTOMERS\nPK: customer_id"]:::storage
            SHIPPING_TYPE["SHIPPING_TYPE\nPK: ship_type_id"]:::storage
            SHOPPING_CART["SHOPPING_CART\nPK: cart_id\nFK: customer_id → CUSTOMERS.customer_id\nFK: ship_type_id → SHIPPING_TYPE.ship_type_id"]:::storage
            ORDER_DETAILS["ORDER_DETAILS\nPK: order_detail_id\nFK: cart_id → SHOPPING_CART.cart_id\nFK: book_id → BOOKS.book_id"]:::storage
            CREDIT_CARD_DETAILS["CREDIT_CARD_DETAILS\nPK: card_id"]:::storage
            PURCHASE_HISTORY["PURCHASE_HISTORY\nPK: purchase_id\nFK: customer_id → CUSTOMERS.customer_id\nFK: order_detail_id → ORDER_DETAILS.order_detail_id\nFK: card_id → CREDIT_CARD_DETAILS.card_id"]:::storage
        end
    end

    %% Relationships Data Flow
    EX1 --> PY1
    EX2 --> PY1
    EX3 --> PY3
    EX4 --> PY2
    EX5 --> PY2
    PY1 --> DL3
    PY2 --> DL8
    PY3 --> DL7
    DL1 --> AUTHORS
    DL2 --> PUBLISHERS
    DL3 --> BOOKS
    DL4 --> CREDIT_CARD_DETAILS
    DL5 --> CUSTOMERS
    DL6 --> SHIPPING_TYPE
    DL7 --> SHOPPING_CART
    DL8 --> ORDER_DETAILS
    DL9 --> PURCHASE_HISTORY

    SC1 --> SC2 --> SC3
    SC3 --> AUTHORS
    SC3 --> PUBLISHERS
    SC3 --> BOOKS
    SC3 --> CUSTOMERS
    SC3 --> SHIPPING_TYPE
    SC3 --> SHOPPING_CART
    SC3 --> ORDER_DETAILS
    SC3 --> CREDIT_CARD_DETAILS
    SC3 --> PURCHASE_HISTORY

    %% Click Events for Data Sources
    click EX1 "https://github.com/paras4902/onlinebookstore_db/blob/main/Creation of insert script for books table.xlsx"
    click EX2 "https://github.com/paras4902/onlinebookstore_db/blob/main/Customers Data with insert script.xlsx"
    click EX3 "https://github.com/paras4902/onlinebookstore_db/blob/main/Data for shopping cart table with insert script.xlsx"
    click EX4 "https://github.com/paras4902/onlinebookstore_db/blob/main/Order Details Table data with insert script.xlsx"
    click EX5 "https://github.com/paras4902/onlinebookstore_db/blob/main/Purchase history data with insert script.xlsx"

    %% Click Events for Data Preparation
    click PY1 "https://github.com/paras4902/onlinebookstore_db/blob/main/python script for generating dates.py"
    click PY2 "https://github.com/paras4902/onlinebookstore_db/blob/main/python script for repeating existing numbers.py"
    click PY3 "https://github.com/paras4902/onlinebookstore_db/blob/main/python script for repeating existing shipping types.py"

    %% Click Events for Schema Provisioning
    click SC1 "https://github.com/paras4902/onlinebookstore_db/blob/main/Creation of tables, user n tablespace/Task_1__script_for_create_tablespace_and_user_and_granting_create_session_and_create_table_privilege.txt"
    click SC2 "https://github.com/paras4902/onlinebookstore_db/blob/main/Creation of tables, user n tablespace/Task_2__script_for_create_sequence.txt"
    click SC3 "https://github.com/paras4902/onlinebookstore_db/blob/main/Creation of tables, user n tablespace/Task_3__script_for_create_tables.txt"

    %% Click Events for Data Loading
    click DL1 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/1_AUTHORS.txt"
    click DL2 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/2_PUBLISHERS.txt"
    click DL3 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/3_BOOKS.txt"
    click DL4 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/4_CREDIT_CARD_DETAILS.txt"
    click DL5 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/5_CUSTOMERS.txt"
    click DL6 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/6_SHIPPING_TYPE.txt"
    click DL7 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/7_SHOPPING_CART.txt"
    click DL8 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/8_ORDER_DETAILS.txt"
    click DL9 "https://github.com/paras4902/onlinebookstore_db/blob/main/Insert Scripts/9_PURCHASE_HISTORY.txt"

    %% Styles
    classDef source fill:#ADD8E6,stroke:#333,stroke-width:1px
    classDef processing fill:#90EE90,stroke:#333,stroke-width:1px
    classDef storage fill:#FFA500,stroke:#333,stroke-width:1px
```
