Changes between Version 1 and Version 2 of Relational


Ignore:
Timestamp:
09/24/26 13:16:25 (3 days ago)
Author:
231285
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Relational

    v1 v2  
    1 = Relational Design
    2 
    3 == Descriptive representation of the relational schema
    4 
    5 Notation: **bold** = primary key, *italic* = foreign key.
    6 
    7 - **Users**(<u>**id**</u>, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at)
    8   - Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`.
    9 - **Crypto**(<u>**id**</u>, symbol, name, created_at)
    10   - Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
    11 - **Markets**(<u>**id**</u>, *crypto_id*, quote_currency, is_active, created_at)
    12   - Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`.
    13 - **Holdings**(<u>**id**</u>, *user_id*, *crypto_id*, quantity, avg_price, created_at, updated_at)
    14   - Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and
    15     `{user_id, crypto_id}` — the latter is the relationship's own key and is
    16     enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for
    17     consistency with the other relations.
    18   - `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
    19 - **Orders**(<u>**id**</u>, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
    20   - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
    21 - **Transactions**(<u>**id**</u>, *user_id*, type, amount, currency, *related_order*, created_at, description)
    22   - `type ∈ {deposit, buy, sell, fee}`.
    23 - **MarketTrades**(<u>**id**</u>, *market_id*, executed_at, price, quantity, side, source)
    24 - **MarketCandles**(<u>**id**</u>, *market_id*, timeframe, open, high, low, close, volume, candle_time)
    25   - `UNIQUE(market_id, timeframe, candle_time)`.
    26 - **Watchlists**(<u>**id**</u>, *user_id*, name, created_at)
    27 - **WatchlistItems**(<u>**id**</u>, *watchlist_id*, *crypto_id*, added_at)
    28   - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}`
    29     and `{watchlist_id, crypto_id}`, the latter enforced with
    30     `UNIQUE(watchlist_id, crypto_id)`.
    31 
    32 ==== Transformation method used
    33 
    34 **Partial transformation.** Applied as follows:
    35 
    36 - Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping
    37   its UUID (or serial) primary key.
    38 - Each **1:N relationship without attributes** is transformed by adding the
    39   parent's primary key as a foreign-key column on the child table — the "N"
    40   side. This is where every foreign key in the schema comes from, and it is why
    41   no foreign keys appear in the ER diagram itself:
    42   `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`,
    43   `Places` → `orders.user_id`, `Records` → `transactions.user_id`,
    44   `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`,
    45   `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`.
    46 - Each **M:N relationship** becomes its own table holding the two foreign keys
    47   plus the relationship's own attributes: `Holds` → `holdings`,
    48   `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's
    49   key and is enforced as a `UNIQUE` constraint in both tables.
    50 - **Total participation** in the ER model becomes `NOT NULL` on the
    51   corresponding foreign key; partial participation stays nullable. `Settles` is
    52   partial on both sides, which is exactly why `transactions.related_order` is
    53   the one nullable foreign key in the schema — a deposit has no originating
    54   order.
    55 
    56 ==== Normalisation
    57 
    58 All relations are in **3NF**:
    59 
    60 - Every attribute is atomic (no repeating groups, no composite fields).
    61 - No partial dependency exists because every primary key is a single UUID column.
    62 - No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate.
    63 - `avg_price` in `Holdings` is a **derived value** cached for performance (it is
    64   the weighted-average entry price across all `buy` transactions for that
    65   `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram.
    66   We accept the denormalisation: it is recomputed by the database inside the same
    67   transaction as each buy, in the same statement that changes the quantity
    68   (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average
    69   and the stored quantity can never disagree.
    70 - `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the
    71   P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL`
    72   yields `NULL`, so a nullable average would have silently blanked the
    73   unrealised-P/L column for an existing position instead of failing loudly.
    74 
    75 === DDL script
    76 
    77 The script that creates the entire schema is [`../server/db/schema_creation.sql`](../../server/db/schema_creation.sql). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema.
     1= Relational Design =
     2
     3== Descriptive representation of the relational schema ==
     4
     5Notation: '''bold''' = primary key, ''italic'' = foreign key.
     6
     7 * '''Users'''(__'''id'''__, username, email, full_name, password_hash, available_balance, invested_balance, created_at, updated_at)
     8   * Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`.
     9 * '''Crypto'''(__'''id'''__, symbol, name, created_at)
     10   * Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
     11 * '''Markets'''(__'''id'''__, ''crypto_id'', quote_currency, is_active, created_at)
     12   * Candidate keys: `{id}`, `{crypto_id, quote_currency}`. `UNIQUE(crypto_id, quote_currency)`.
     13 * '''Holdings'''(__'''id'''__, ''user_id'', ''crypto_id'', quantity, reserved_quantity, avg_price, created_at, updated_at)
     14   * Transformation of the M:N relationship `Holds`. Candidate keys: `{id}` and
     15     `{user_id, crypto_id}` — the latter is the relationship's own key and is
     16     enforced with `UNIQUE(user_id, crypto_id)`. `id` was chosen as PK for
     17     consistency with the other relations.
     18   * `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
     19   * `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` — the amount already committed to the
     20     user's own open sell orders. `quantity - reserved_quantity` (the amount
     21     actually free to sell) is not a stored column; it is computed wherever
     22     needed, in `v_portfolio` as `available_quantity` and in the sell path of
     23     UseCase0005. See the `Holds` section of ERModel
     24     for why this mirrors `available_balance`/`invested_balance` on `Users`.
     25 * '''Orders'''(__'''id'''__, ''user_id'', ''market_id'', side, type, status, quantity, price, placed_at, executed_at)
     26   * `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
     27 * '''Transactions'''(__'''id'''__, ''user_id'', type, amount, currency, ''related_order'', created_at, description)
     28   * `type ∈ {deposit, buy, sell, fee}`.
     29 * '''!MarketTrades'''(__'''id'''__, ''market_id'', executed_at, price, quantity, side, source)
     30 * '''!MarketCandles'''(__'''id'''__, ''market_id'', timeframe, open, high, low, close, volume, candle_time)
     31   * `UNIQUE(market_id, timeframe, candle_time)`.
     32 * '''Watchlists'''(__'''id'''__, ''user_id'', name, created_at)
     33 * '''!WatchlistItems'''(__'''id'''__, ''watchlist_id'', ''crypto_id'', added_at)
     34   * Transformation of the M:N relationship `Contains`. Candidate keys: `{id}`
     35     and `{watchlist_id, crypto_id}`, the latter enforced with
     36     `UNIQUE(watchlist_id, crypto_id)`.
     37
     38=== Transformation method used ===
     39
     40'''Partial transformation.''' Applied as follows:
     41
     42 * Each of the 8 entity sets in ERModel becomes one table, keeping
     43   its UUID (or serial) primary key.
     44 * Each '''1:N relationship without attributes''' is transformed by adding the
     45   parent's primary key as a foreign-key column on the child table — the "N"
     46   side. This is where every foreign key in the schema comes from, and it is why
     47   no foreign keys appear in the ER diagram itself:
     48   `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`,
     49   `Places` → `orders.user_id`, `Records` → `transactions.user_id`,
     50   `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`,
     51   `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`.
     52 * Each '''M:N relationship''' becomes its own table holding the two foreign keys
     53   plus the relationship's own attributes: `Holds` → `holdings`,
     54   `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's
     55   key and is enforced as a `UNIQUE` constraint in both tables.
     56 * '''Total participation''' in the ER model becomes `NOT NULL` on the
     57   corresponding foreign key; partial participation stays nullable. `Settles` is
     58   partial on both sides, which is exactly why `transactions.related_order` is
     59   the one nullable foreign key in the schema — a deposit has no originating
     60   order.
     61
     62=== Normalisation ===
     63
     64> '''Validated in P5.''' Normalization derives this
     65> exact schema independently — starting only from a single de-normalized relation of every
     66> model attribute and its functional dependencies, with no reference to the ER-to-relational
     67> transformation below — and shows it decomposes to '''BCNF''', one normal form stronger than
     68> the 3NF claimed here. The two designs agree relation for relation and key for key, so
     69> nothing here changed as a result; see that page's
     70> discussion section for what the one real
     71> difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this
     72> design is still the one used from P5 onward.
     73
     74All relations are in '''3NF''':
     75
     76 * Every attribute is atomic (no repeating groups, no composite fields).
     77 * No partial dependency exists because every primary key is a single UUID column.
     78 * No transitive dependency exists: every non-key attribute depends directly on the row identifier. For example, `holdings.quantity` depends on `holdings.id`, not on `user_id` via some intermediate.
     79 * `avg_price` in `Holdings` is a '''derived value''' cached for performance (it is
     80   the weighted-average entry price across all `buy` transactions for that
     81   `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram.
     82   We accept the denormalisation: it is recomputed by the database inside the same
     83   transaction as each buy, in the same statement that changes the quantity
     84   (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average
     85   and the stored quantity can never disagree.
     86 * `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the
     87   P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL`
     88   yields `NULL`, so a nullable average would have silently blanked the
     89   unrealised-P/L column for an existing position instead of failing loudly.
     90 * `holdings.reserved_quantity`, unlike `avg_price`, is '''not''' derived — it is
     91   written directly by the application (`trade.go`) as orders are placed and
     92   settled, the same way `quantity` itself is. `quantity - reserved_quantity`
     93   ("available") is the derived value here, and it is never stored, only
     94   computed where it is needed.
     95
     96=== Reservation and the order lifecycle ===
     97
     98`holdings.reserved_quantity` exists so that placing a sell order can be
     99checked against what a user actually has ''free'' to sell
     100(`quantity - reserved_quantity`), not against the raw `quantity`, which also
     101counts crypto already promised to another order that has not settled yet.
     102`CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` makes an
     103inconsistent reservation impossible at the database level, regardless of what
     104application code does. The exact statement sequence — lock the row, check the
     105available amount, reserve, then settle — is in
     106UseCase0005; the same
     107`SELECT … FOR UPDATE` locking that already protected `users.available_balance`
     108on the buy path is what makes two concurrent sell orders against the same
     109holding serialize correctly instead of racing.
     110
     111== DDL script ==
     112
     113The script that creates the entire schema is `../server/db/schema_creation.sql` (shown in full below). It is idempotent: it drops and recreates the `project` schema every run, so it works on an empty database and on a database that already has the schema.
    78114
    79115The script creates:
    80 - 10 tables with check constraints, primary keys, foreign keys and unique constraints.
    81 - 5 performance indexes.
    82 - 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L).
    83 
    84 === DML script (sample data)
    85 
    86 The script that loads realistic sample data is [`../server/db/data_load.sql`](../../server/db/data_load.sql). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded:
    87 - 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets.
    88 - 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex).
    89 - 18 recent market trades across all markets so `v_latest_prices` is populated.
    90 - 10 one-hour candles (BTC and ETH).
    91 - One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly.
    92 - Two watchlists with five watchlist items.
    93 
    94 === Relational diagram
    95 
    96 ![relational_schema](relational_schema.jpg)
    97 
    98 Generated in **Pgadmin** from the **live** `project` schema, in crow's-foot
     116 * 10 tables with check constraints, primary keys, foreign keys and unique constraints.
     117 * 5 performance indexes.
     118 * 2 views: `v_latest_prices` (latest trade price per market) and `v_portfolio` (per-user holdings valuation with unrealised P/L, plus `reserved_quantity` and the derived `available_quantity`).
     119
     120=== schema_creation.sql ===
     121
     122The two report functions at the end of the file (`report_top_traders` and `report_market_performance`) belong to Phase 6 (!AdvancedReports) and are left out here.
     123
     124{{{
     125-- schema_creation.sql
     126-- EduBerza - crypto exchange simulation database
     127-- Course: Databases 2025/2026 Winter, FINKI UKIM
     128--
     129-- This script is idempotent. It drops the `project` schema and all contained
     130-- objects, then recreates them from scratch. Safe to run on an empty database
     131-- or on a database where the schema already exists.
     132
     133DROP SCHEMA IF EXISTS project CASCADE;
     134CREATE SCHEMA project;
     135
     136CREATE EXTENSION IF NOT EXISTS pgcrypto;
     137
     138SET search_path TO project, public;
     139
     140-- ============================================================================
     141-- USERS
     142-- Platform users. Each user has virtual (prop) balances used for simulation.
     143-- ============================================================================
     144CREATE TABLE project.users (
     145    id                uuid            PRIMARY KEY DEFAULT gen_random_uuid(),
     146    username          varchar(50)     NOT NULL UNIQUE,
     147    email             varchar(255)    NOT NULL UNIQUE,
     148    full_name         varchar(200),
     149    password_hash     varchar(255)    NOT NULL,
     150    available_balance numeric(18,4)   NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
     151    invested_balance  numeric(18,4)   NOT NULL DEFAULT 0 CHECK (invested_balance  >= 0),
     152    created_at        timestamptz     NOT NULL DEFAULT now(),
     153    updated_at        timestamptz
     154);
     155
     156-- ============================================================================
     157-- CRYPTO
     158-- Catalog of crypto assets available on the platform.
     159-- ============================================================================
     160CREATE TABLE project.crypto (
     161    id         uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
     162    symbol     varchar(20)  NOT NULL UNIQUE,
     163    name       varchar(255) NOT NULL,
     164    created_at timestamptz  NOT NULL DEFAULT now()
     165);
     166
     167-- ============================================================================
     168-- MARKETS
     169-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD.
     170-- ============================================================================
     171CREATE TABLE project.markets (
     172    id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
     173    crypto_id      uuid        NOT NULL REFERENCES project.crypto(id),
     174    quote_currency char(3)     NOT NULL DEFAULT 'USD',
     175    is_active      boolean     NOT NULL DEFAULT true,
     176    created_at     timestamptz NOT NULL DEFAULT now(),
     177    CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency)
     178);
     179
     180-- ============================================================================
     181-- HOLDINGS
     182-- Per-user crypto position with running weighted average entry price.
     183-- ============================================================================
     184CREATE TABLE project.holdings (
     185    id                uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     186    user_id           uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
     187    crypto_id         uuid           NOT NULL REFERENCES project.crypto(id),
     188    quantity          numeric(20,4)  NOT NULL CHECK (quantity >= 0),
     189    -- Committed to the user's own open sell orders, not yet removed from the
     190    -- position. quantity - reserved_quantity is what is actually free to
     191    -- sell — the crypto-side equivalent of users.available_balance.
     192    reserved_quantity numeric(20,4)  NOT NULL DEFAULT 0
     193                                      CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
     194    -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
     195    -- v_portfolio can never silently produce NULL for an existing position.
     196    avg_price         numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
     197    created_at        timestamptz    NOT NULL DEFAULT now(),
     198    updated_at        timestamptz,
     199    CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
     200);
     201
     202-- ============================================================================
     203-- ORDERS
     204-- Orders placed by users on a market.
     205-- ============================================================================
     206CREATE TABLE project.orders (
     207    id          uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     208    user_id     uuid           NOT NULL REFERENCES project.users(id)   ON DELETE CASCADE,
     209    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     210    side        varchar(4)     NOT NULL CHECK (side   IN ('buy', 'sell')),
     211    type        varchar(20)    NOT NULL CHECK (type   IN ('market', 'limit')),
     212    status      varchar(20)    NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')),
     213    quantity    numeric(20,4)  NOT NULL CHECK (quantity > 0),
     214    price       numeric(18,6),
     215    placed_at   timestamptz    NOT NULL DEFAULT now(),
     216    executed_at timestamptz
     217);
     218
     219CREATE INDEX idx_orders_user      ON project.orders(user_id);
     220CREATE INDEX idx_orders_market    ON project.orders(market_id);
     221CREATE INDEX idx_orders_status    ON project.orders(status);
     222
     223-- ============================================================================
     224-- TRANSACTIONS
     225-- Financial ledger: deposits, buys, sells, fees.
     226-- ============================================================================
     227CREATE TABLE project.transactions (
     228    id            uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     229    user_id       uuid           NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
     230    type          varchar(50)    NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
     231    amount        numeric(18,4)  NOT NULL,
     232    currency      char(3)        NOT NULL DEFAULT 'USD',
     233    related_order uuid           REFERENCES project.orders(id),
     234    created_at    timestamptz    NOT NULL DEFAULT now(),
     235    description   text
     236);
     237
     238CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC);
     239
     240-- ============================================================================
     241-- MARKET TRADES
     242-- Raw executed trades on a market. Source of truth for current price.
     243-- ============================================================================
     244CREATE TABLE project.market_trades (
     245    id          bigserial      PRIMARY KEY,
     246    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     247    executed_at timestamptz    NOT NULL,
     248    price       numeric(18,6)  NOT NULL CHECK (price    > 0),
     249    quantity    numeric(20,6)  NOT NULL CHECK (quantity > 0),
     250    side        varchar(4)     CHECK (side IN ('buy', 'sell')),
     251    source      varchar(50)    NOT NULL DEFAULT 'simulation'
     252);
     253
     254CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
     255
     256-- ============================================================================
     257-- MARKET CANDLES
     258-- OHLCV aggregates over standard timeframes.
     259-- ============================================================================
     260CREATE TABLE project.market_candles (
     261    id          bigserial      PRIMARY KEY,
     262    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     263    timeframe   varchar(5)     NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')),
     264    open        numeric(18,6)  NOT NULL,
     265    high        numeric(18,6)  NOT NULL,
     266    low         numeric(18,6)  NOT NULL,
     267    close       numeric(18,6)  NOT NULL,
     268    volume      numeric(20,6)  NOT NULL,
     269    candle_time timestamptz    NOT NULL,
     270    CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time)
     271);
     272
     273CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC);
     274
     275-- ============================================================================
     276-- WATCHLISTS
     277-- ============================================================================
     278CREATE TABLE project.watchlists (
     279    id         uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
     280    user_id    uuid         NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
     281    name       varchar(100) NOT NULL,
     282    created_at timestamptz  NOT NULL DEFAULT now()
     283);
     284
     285CREATE TABLE project.watchlist_items (
     286    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
     287    watchlist_id uuid        NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE,
     288    crypto_id    uuid        NOT NULL REFERENCES project.crypto(id),
     289    added_at     timestamptz NOT NULL DEFAULT now(),
     290    CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id)
     291);
     292
     293-- ============================================================================
     294-- VIEWS
     295-- ============================================================================
     296
     297-- Latest trade price per market (current price).
     298CREATE OR REPLACE VIEW project.v_latest_prices AS
     299SELECT DISTINCT ON (t.market_id)
     300       t.market_id,
     301       c.symbol,
     302       m.quote_currency,
     303       t.price,
     304       t.executed_at
     305FROM   project.market_trades t
     306JOIN   project.markets       m ON m.id = t.market_id
     307JOIN   project.crypto        c ON c.id = m.crypto_id
     308ORDER  BY t.market_id, t.executed_at DESC;
     309
     310-- Portfolio valuation per user (holdings x latest price).
     311CREATE OR REPLACE VIEW project.v_portfolio AS
     312SELECT h.user_id,
     313       c.symbol,
     314       h.quantity,
     315       h.reserved_quantity,
     316       (h.quantity - h.reserved_quantity) AS available_quantity,
     317       h.avg_price,
     318       lp.price                           AS current_price,
     319       (h.quantity * lp.price)            AS market_value,
     320       (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
     321FROM   project.holdings h
     322JOIN   project.crypto   c ON c.id = h.crypto_id
     323LEFT   JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
     324LEFT   JOIN project.v_latest_prices lp ON lp.market_id = m.id;
     325}}}
     326
     327== DML script (sample data) ==
     328
     329The script that loads realistic sample data is `../server/db/data_load.sql` (shown in full below). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded:
     330 * 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets.
     331 * 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex).
     332 * 18 recent market trades across all markets so `v_latest_prices` is populated.
     333 * 10 one-hour candles (BTC and ETH).
     334 * One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly.
     335 * Two watchlists with five watchlist items.
     336
     337=== data_load.sql ===
     338
     339{{{
     340-- data_load.sql
     341-- EduBerza - sample data
     342-- Course: Databases 2025/2026 Winter, FINKI UKIM
     343--
     344-- Idempotent. Truncates all tables in the `project` schema and reloads
     345-- deterministic sample data. Run schema_creation.sql first if tables do
     346-- not yet exist.
     347--
     348-- All sample users have the password: test123
     349
     350SET search_path TO project, public;
     351
     352TRUNCATE TABLE
     353    project.watchlist_items,
     354    project.watchlists,
     355    project.market_candles,
     356    project.market_trades,
     357    project.transactions,
     358    project.orders,
     359    project.holdings,
     360    project.markets,
     361    project.crypto,
     362    project.users
     363RESTART IDENTITY CASCADE;
     364
     365-- ============================================================================
     366-- CRYPTO
     367-- ============================================================================
     368INSERT INTO project.crypto (id, symbol, name) VALUES
     369    ('11111111-1111-1111-1111-111111111111', 'BTC',  'Bitcoin'),
     370    ('22222222-2222-2222-2222-222222222222', 'ETH',  'Ethereum'),
     371    ('33333333-3333-3333-3333-333333333333', 'ADA',  'Cardano'),
     372    ('44444444-4444-4444-4444-444444444444', 'SOL',  'Solana'),
     373    ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin');
     374
     375-- ============================================================================
     376-- MARKETS (all quoted in USD)
     377-- ============================================================================
     378INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES
     379    ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true),
     380    ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true),
     381    ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true),
     382    ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true),
     383    ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true);
     384
     385-- ============================================================================
     386-- USERS
     387-- Password for all: test123 (stored as sha256 hex hash)
     388-- ============================================================================
     389INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES
     390    ('b1111111-1111-1111-1111-111111111111', 'alice',   'alice@example.com',   'Alice Johnson',
     391        encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0),
     392    ('b2222222-2222-2222-2222-222222222222', 'bob',     'bob@example.com',     'Bob Smith',
     393        encode(digest('test123', 'sha256'), 'hex'),  5000.0000, 0),
     394    ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis',
     395        encode(digest('test123', 'sha256'), 'hex'),  2500.0000, 0);
     396
     397-- ============================================================================
     398-- MARKET TRADES
     399-- Recent simulated trades per market, used as price source.
     400-- ============================================================================
     401INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES
     402    -- BTC/USD around $67,000
     403    ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy',  'simulation'),
     404    ('a1111111-1111-1111-1111-111111111111', now() - interval  '8 min', 66910.500000, 0.075000, 'sell', 'simulation'),
     405    ('a1111111-1111-1111-1111-111111111111', now() - interval  '5 min', 67020.750000, 0.200000, 'buy',  'simulation'),
     406    ('a1111111-1111-1111-1111-111111111111', now() - interval  '2 min', 67105.100000, 0.050000, 'buy',  'simulation'),
     407    ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'),
     408    -- ETH/USD around $3,500
     409    ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy',  'simulation'),
     410    ('a2222222-2222-2222-2222-222222222222', now() - interval  '6 min', 3502.750000, 0.800000, 'sell', 'simulation'),
     411    ('a2222222-2222-2222-2222-222222222222', now() - interval  '2 min', 3515.250000, 2.100000, 'buy',  'simulation'),
     412    ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy',  'simulation'),
     413    -- ADA/USD around $0.45
     414    ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500,  500.000000, 'buy',  'simulation'),
     415    ('a3333333-3333-3333-3333-333333333333', now() - interval  '3 min', 0.452000, 1200.000000, 'buy',  'simulation'),
     416    ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750,  800.000000, 'sell', 'simulation'),
     417    -- SOL/USD around $165
     418    ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy',  'simulation'),
     419    ('a4444444-4444-4444-4444-444444444444', now() - interval  '4 min', 165.500000,  5.500000, 'sell', 'simulation'),
     420    ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000,  8.000000, 'buy',  'simulation'),
     421    -- DOGE/USD around $0.12
     422    ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy',  'simulation'),
     423    ('a5555555-5555-5555-5555-555555555555', now() - interval  '3 min', 0.121250,  7500.000000, 'sell', 'simulation'),
     424    ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy',  'simulation');
     425
     426-- ============================================================================
     427-- MARKET CANDLES (1h aggregates, last 5 hours per market)
     428-- ============================================================================
     429INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES
     430    ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')),
     431    ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')),
     432    ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')),
     433    ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')),
     434    ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')),
     435    ('a2222222-2222-2222-2222-222222222222', '1h',  3460,  3490,  3450,  3485, 120.0, date_trunc('hour', now() - interval '5 hour')),
     436    ('a2222222-2222-2222-2222-222222222222', '1h',  3485,  3510,  3480,  3500, 135.0, date_trunc('hour', now() - interval '4 hour')),
     437    ('a2222222-2222-2222-2222-222222222222', '1h',  3500,  3520,  3495,  3515, 110.0, date_trunc('hour', now() - interval '3 hour')),
     438    ('a2222222-2222-2222-2222-222222222222', '1h',  3515,  3525,  3500,  3520, 125.5, date_trunc('hour', now() - interval '2 hour')),
     439    ('a2222222-2222-2222-2222-222222222222', '1h',  3520,  3530,  3510,  3520, 140.0, date_trunc('hour', now() - interval '1 hour'));
     440
     441-- ============================================================================
     442-- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice
     443-- Shows a fully-filled market buy and its resulting holding & ledger entry.
     444-- ============================================================================
     445INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
     446    ('c1111111-1111-1111-1111-111111111111',
     447     'b1111111-1111-1111-1111-111111111111',
     448     'a2222222-2222-2222-2222-222222222222',
     449     'buy', 'market', 'executed', 0.5000, 3500.000000,
     450     now() - interval '1 hour', now() - interval '1 hour');
     451
     452INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES
     453    ('b1111111-1111-1111-1111-111111111111',
     454     '22222222-2222-2222-2222-222222222222',
     455     0.5000, 3500.000000, now() - interval '1 hour');
     456
     457INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES
     458    ('b1111111-1111-1111-1111-111111111111', 'deposit',  10000.0000, 'USD', NULL,
     459        'Initial virtual deposit'),
     460    ('b1111111-1111-1111-1111-111111111111', 'buy',      -1750.0000, 'USD',
     461        'c1111111-1111-1111-1111-111111111111',
     462        'Market buy 0.5 ETH @ 3500.00');
     463
     464-- After the buy, alice's invested_balance reflects the used funds.
     465UPDATE project.users
     466   SET available_balance = 10000.0000 - 1750.0000,
     467       invested_balance  = 1750.0000,
     468       updated_at        = now()
     469 WHERE id = 'b1111111-1111-1111-1111-111111111111';
     470
     471-- ============================================================================
     472-- WATCHLISTS
     473-- ============================================================================
     474INSERT INTO project.watchlists (id, user_id, name) VALUES
     475    ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'),
     476    ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks');
     477
     478INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES
     479    ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'),
     480    ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'),
     481    ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'),
     482    ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'),
     483    ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555');
     484}}}
     485
     486== Relational diagram ==
     487
     488[[Image(relational_schema.jpg)]]
     489
     490Generated in '''Pgadmin''' from the '''live''' `project` schema, in crow's-foot
    99491notation — not drawn by hand, so it is evidence that the deployed database
    100492actually matches the design described above. Each box is a table with its
    … …  
    102494are the 12 declared foreign keys.
    103495
    104 ==== How to regenerate it
    105 
    106 **With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same
     496=== How to regenerate it ===
     497
     498'''With pgAdmin 4''', if DBeaver is unavailable — it reads the live schema the same
    107499way, so the result is equivalent in substance:
    108500
    109 1. Connect to the project database.
    110 2. Right-click the database → **ERD For Database** (or open a blank ERD and drag
    111    the `project` tables in).
    112 3. Arrange the tables to mirror `ERModel_v01.png`.
    113 4. **Download image** → PNG, then convert:
    114    `convert relational_schema.png relational_schema.jpg`
    115 
    116 === AI use
    117 
    118 Reasoning for the AI-assisted part of this phase, and the full interaction log, are on https://github.com/StefanTrsunov/bp/blob/main/docs/P2-RelationalDesign/RelationalDesignAIUsage.md
     501 1. Connect to the project database.
     502 2. Right-click the database → '''ERD For Database''' (or open a blank ERD and drag
     503    the `project` tables in).
     504 3. Arrange the tables to mirror `ERModel_v03.png`.
     505 4. '''Download image''' → PNG, then convert:
     506    `convert relational_schema.png relational_schema.jpg`