# yanfrog/database_implementation architecture

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

- Interactive diagram: https://gitdiagram.com/yanfrog/database_implementation
- Repository: https://github.com/yanfrog/database_implementation

## Overview

##### Project type and purpose
- This is a data‐centric project built around a local SQLite database (“Group_HW.db”) that holds financial/trading data.
- Its primary purpose is to store and manage user, account, stock, trade and dividend records. The CSV files under version control are the original data sources or exports of each table.

##### File structure
- Top-level files:
- USER.csv, ACCOUNT.csv, STOCK.csv, TRADE.csv, DIVIDENT.csv: raw data dumps or seed files for each table.
- Group_HW.db: the SQLite database file.
- README.md: describes the schema and relationships.
- No separate code directories or scripts; the “ETL” or import step is done via DB Browser or similar tooling.

##### README for additional insights
- Defines five relational tables and their columns.
- Lists primary key and foreign-key relationships:
- USER.PID ← ACCOUNT.Cd_account
- ACCOUNT.AccountID ← TRADE.AccountID
- STOCK.StockID ← TRADE.StockID
- STOCK.StockID ← DIVIDENT.StockID

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data Sources
    subgraph "Data Sources"
        USER_CSV["USER.csv"]:::csv
        ACCOUNT_CSV["ACCOUNT.csv"]:::csv
        STOCK_CSV["STOCK.csv"]:::csv
        TRADE_CSV["TRADE.csv"]:::csv
        DIVIDENT_CSV["DIVIDENT.csv"]:::csv
    end

    %% ETL Layer
    subgraph "Import / ETL"
        ETL["DB Browser / Import Script"]:::etl
    end

    %% Database Storage
    subgraph "Relational Storage"
        subgraph "Group_HW.db"
            USER_TABLE["USER\nPK: PID"]:::entity
            ACCOUNT_TABLE["ACCOUNT\nPK: AccountID\nFK: Cd_account→USER.PID"]:::entity
            STOCK_TABLE["STOCK\nPK: StockID"]:::entity
            TRADE_TABLE["TRADE\nPK: TradeID\nFK: AccountID→ACCOUNT.AccountID\nFK: StockID→STOCK.StockID"]:::entity
            DIVIDENT_TABLE["DIVIDENT\nPK: DivID\nFK: StockID→STOCK.StockID"]:::entity
        end
    end

    %% Reporting Layer
    subgraph "Query / Reporting"
        REPORT_TOOL["SQL Client / BI Tool"]:::reporting
        DOC["README.md"]:::doc
    end

    %% Data Flows
    USER_CSV -->|loads| ETL
    ACCOUNT_CSV -->|loads| ETL
    STOCK_CSV -->|loads| ETL
    TRADE_CSV -->|loads| ETL
    DIVIDENT_CSV -->|loads| ETL

    ETL -->|imports| USER_TABLE
    ETL -->|imports| ACCOUNT_TABLE
    ETL -->|imports| STOCK_TABLE
    ETL -->|imports| TRADE_TABLE
    ETL -->|imports| DIVIDENT_TABLE

    %% Foreign Key Relationships
    ACCOUNT_TABLE -->|Cd_account→USER| USER_TABLE
    TRADE_TABLE -->|AccountID→ACCOUNT| ACCOUNT_TABLE
    TRADE_TABLE -->|StockID→STOCK| STOCK_TABLE
    DIVIDENT_TABLE -->|StockID→STOCK| STOCK_TABLE

    %% Reporting Query
    USER_TABLE -->|SELECT queries| REPORT_TOOL
    ACCOUNT_TABLE -->|SELECT queries| REPORT_TOOL
    STOCK_TABLE -->|SELECT queries| REPORT_TOOL
    TRADE_TABLE -->|SELECT queries| REPORT_TOOL
    DIVIDENT_TABLE -->|SELECT queries| REPORT_TOOL
    DOC -->|documents schema| USER_TABLE

    %% Click Events
    click USER_CSV "https://github.com/yanfrog/database_implementation/blob/main/USER.csv"
    click ACCOUNT_CSV "https://github.com/yanfrog/database_implementation/blob/main/ACCOUNT.csv"
    click STOCK_CSV "https://github.com/yanfrog/database_implementation/blob/main/STOCK.csv"
    click TRADE_CSV "https://github.com/yanfrog/database_implementation/blob/main/TRADE.csv"
    click DIVIDENT_CSV "https://github.com/yanfrog/database_implementation/blob/main/DIVIDENT.csv"
    click ETL "https://github.com/yanfrog/database_implementation/blob/main/README.md"
    click USER_TABLE "https://github.com/yanfrog/database_implementation/blob/main/Group_HW.db"
    click ACCOUNT_TABLE "https://github.com/yanfrog/database_implementation/blob/main/Group_HW.db"
    click STOCK_TABLE "https://github.com/yanfrog/database_implementation/blob/main/Group_HW.db"
    click TRADE_TABLE "https://github.com/yanfrog/database_implementation/blob/main/Group_HW.db"
    click DIVIDENT_TABLE "https://github.com/yanfrog/database_implementation/blob/main/Group_HW.db"
    click DOC "https://github.com/yanfrog/database_implementation/blob/main/README.md"

    %% Styles
    classDef csv fill:#a8f0a3,stroke:#333;
    classDef etl fill:#f4a460,stroke:#333;
    classDef db fill:#d3d3d3,stroke:#333;
    classDef reporting fill:#b19cd9,stroke:#333;
    classDef entity fill:#ffffff,stroke:#333;
    classDef doc fill:#f0e68c,stroke:#333;
```
