| 1 | = Relational Design =
|
|---|
| 2 |
|
|---|
| 3 | This page transforms [wiki:ERModel] '''v05''' into
|
|---|
| 4 | relations. Every relation below corresponds to exactly one entity set of the
|
|---|
| 5 | model, and every foreign key corresponds to exactly one relationship, so the
|
|---|
| 6 | two diagrams can be compared box for box and line for line (see
|
|---|
| 7 | Relational diagram).
|
|---|
| 8 |
|
|---|
| 9 | == Descriptive representation of the relational schema ==
|
|---|
| 10 |
|
|---|
| 11 | Notation: '''bold''' = primary key, ''italic'' = foreign key. After each foreign key
|
|---|
| 12 | comes the ER relationship it implements.
|
|---|
| 13 |
|
|---|
| 14 | * '''Users'''(__'''id'''__, username, email, full_name, password_hash, available_balance, invested_balance, reserved_balance, created_at, updated_at)
|
|---|
| 15 | * Entity set `Users`. Candidate keys: `{id}`, `{username}`, `{email}`. `UNIQUE(username)`, `UNIQUE(email)`.
|
|---|
| 16 | * '''Crypto'''(__'''id'''__, symbol, name, created_at)
|
|---|
| 17 | * Entity set `Cryptos`. Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
|
|---|
| 18 | * '''Markets'''(__'''id'''__, ''crypto_id'' [`QuotedOn`], quote_currency, is_active, created_at)
|
|---|
| 19 | * Entity set `Markets`. Candidate keys: `{id}`, `{crypto_id, quote_currency}` (the model's rule "a crypto is quoted at most once per currency"), enforced with `UNIQUE(crypto_id, quote_currency)`.
|
|---|
| 20 | * '''Holdings'''(__'''id'''__, ''user_id'' [`Holds`], ''crypto_id'' [`PositionIn`], quantity, reserved_quantity, avg_price, created_at, updated_at)
|
|---|
| 21 | * Entity set `Holdings`. Candidate keys: `{id}` and `{user_id, crypto_id}` (the model's rule "one holding per user and crypto"), enforced with `UNIQUE(user_id, crypto_id)`.
|
|---|
| 22 | * `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
|
|---|
| 23 | * `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` — the amount already committed to the user's own open sell orders. `quantity - reserved_quantity` (the amount actually free to sell) is not a stored column; it is computed wherever needed, in `v_portfolio` as `available_quantity` and in the sell path of [wiki:UseCase0005]. See [wiki:ERModel] (section "Holdings") for why this mirrors `available_balance`/`invested_balance` on `Users`.
|
|---|
| 24 | * '''Orders'''(__'''id'''__, ''user_id'' [`Places`], ''market_id'' [`PlacedOn`], side, type, status, quantity, filled_quantity, price, placed_at, executed_at)
|
|---|
| 25 | * Entity set `Orders`. `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, partially_filled, executed, cancelled}`, `0 ≤ filled_quantity ≤ quantity`.
|
|---|
| 26 | * '''Transactions'''(__'''id'''__, ''user_id'' [`Records`], type, amount, currency, ''related_order'' [`Settles`], created_at, description)
|
|---|
| 27 | * Entity set `Transactions`. `type ∈ {deposit, buy, sell, fee}`. `related_order` is nullable (see below).
|
|---|
| 28 | * '''!MarketTrades'''(__'''id'''__, ''market_id'' [`Fills`], executed_at, price, quantity, side, source, ''buy_order_id'' [`FillsBuy`], ''sell_order_id'' [`FillsSell`])
|
|---|
| 29 | * Entity set `MarketTrades`. `buy_order_id` and `sell_order_id` are both nullable (see below).
|
|---|
| 30 | * '''!OrderEvents'''(__'''id'''__, ''order_id'' [`Logs`], event_type, quantity, price, status_after, created_at)
|
|---|
| 31 | * Entity set `OrderEvents`. `event_type ∈ {placed, partially_filled, filled, cancelled}`.
|
|---|
| 32 | * '''!MarketCandles'''(__'''id'''__, ''market_id'' [`Aggregates`], timeframe, open, high, low, close, volume, candle_time)
|
|---|
| 33 | * Entity set `MarketCandles`. Candidate keys: `{id}`, `{market_id, timeframe, candle_time}` (the model's rule "one candle per market, timeframe and bucket"), enforced with `UNIQUE(market_id, timeframe, candle_time)`.
|
|---|
| 34 | * '''Watchlists'''(__'''id'''__, ''user_id'' [`Owns`], name, created_at)
|
|---|
| 35 | * Entity set `Watchlists`.
|
|---|
| 36 | * '''!WatchlistItems'''(__'''id'''__, ''watchlist_id'' [`Contains`], ''crypto_id'' [`Lists`], added_at)
|
|---|
| 37 | * Entity set `WatchlistItems`. Candidate keys: `{id}` and `{watchlist_id, crypto_id}` (the model's rule "an asset at most once per list"), enforced with `UNIQUE(watchlist_id, crypto_id)`.
|
|---|
| 38 |
|
|---|
| 39 | === Transformation method used ===
|
|---|
| 40 |
|
|---|
| 41 | '''Partial transformation.''' The model has 11 entity sets and 15 relationships.
|
|---|
| 42 | Every relationship is binary and 1:N with no attributes of its own (the two M:N
|
|---|
| 43 | relationships of earlier versions, `Holds` and `Contains`, were corrected into
|
|---|
| 44 | the entity sets `Holdings` and `WatchlistItems` in v05). The rules:
|
|---|
| 45 |
|
|---|
| 46 | * '''Each entity set becomes one relation''', with its own attributes and its own key `id` as primary key. 11 entity sets → 11 relations.
|
|---|
| 47 | * '''Each 1:N relationship becomes one foreign key''' on the relation of the "N" side, pointing to the primary key of the "1" side. No relationship gets its own table, because none is M:N and none has attributes. 15 relationships → 15 foreign keys:
|
|---|
| 48 |
|
|---|
| 49 | ||= ER relationship =||= 1 side → N side =||= Foreign key =||= Participation of the N side =||= `NULL`? =||
|
|---|
| 50 | || `QuotedOn` || Cryptos → Markets || `markets.crypto_id` || total || `NOT NULL` ||
|
|---|
| 51 | || `PlacedOn` || Markets → Orders || `orders.market_id` || total || `NOT NULL` ||
|
|---|
| 52 | || `Places` || Users → Orders || `orders.user_id` || total || `NOT NULL` ||
|
|---|
| 53 | || `Records` || Users → Transactions || `transactions.user_id` || total || `NOT NULL` ||
|
|---|
| 54 | || `Settles` || Orders → Transactions || `transactions.related_order` || partial || nullable ||
|
|---|
| 55 | || `Fills` || Markets → !MarketTrades || `market_trades.market_id` || total || `NOT NULL` ||
|
|---|
| 56 | || `FillsBuy` || Orders → !MarketTrades || `market_trades.buy_order_id` || partial || nullable ||
|
|---|
| 57 | || `FillsSell` || Orders → !MarketTrades || `market_trades.sell_order_id` || partial || nullable ||
|
|---|
| 58 | || `Logs` || Orders → !OrderEvents || `order_events.order_id` || total || `NOT NULL` ||
|
|---|
| 59 | || `Aggregates` || Markets → !MarketCandles || `market_candles.market_id` || total || `NOT NULL` ||
|
|---|
| 60 | || `Owns` || Users → Watchlists || `watchlists.user_id` || total || `NOT NULL` ||
|
|---|
| 61 | || `Holds` || Users → Holdings || `holdings.user_id` || total || `NOT NULL` ||
|
|---|
| 62 | || `PositionIn` || Cryptos → Holdings || `holdings.crypto_id` || total || `NOT NULL` ||
|
|---|
| 63 | || `Contains` || Watchlists → !WatchlistItems || `watchlist_items.watchlist_id` || total || `NOT NULL` ||
|
|---|
| 64 | || `Lists` || Cryptos → !WatchlistItems || `watchlist_items.crypto_id` || total || `NOT NULL` ||
|
|---|
| 65 |
|
|---|
| 66 | * '''Participation decides `NULL`.''' Total participation of the N side means every row must reference a parent, so the foreign key is `NOT NULL`. Partial participation leaves it nullable. There are exactly three partial ones: `Settles` (a deposit has no originating order), and `FillsBuy` / `FillsSell` (a trade against the simulated market has no user order on that side). Partial participation of the '''1''' side (for example, a user with no orders) needs no column at all. It simply means no row points at that parent.
|
|---|
| 67 | * '''Uniqueness rules of the model become `UNIQUE` constraints.''' The four rules the model states in words ("a crypto quoted once per currency", "one candle per market, timeframe and bucket", "one holding per user and crypto", "an asset once per list") involve a relationship, so Chen notation cannot draw them as keys. After transformation, the relationship is a foreign-key column, and each rule becomes an ordinary composite `UNIQUE` constraint, i.e. a second candidate key.
|
|---|
| 68 |
|
|---|
| 69 | Nothing in the schema comes from anywhere else. Every column is either an ER
|
|---|
| 70 | attribute or the foreign key of one listed relationship.
|
|---|
| 71 |
|
|---|
| 72 | === Normalisation ===
|
|---|
| 73 |
|
|---|
| 74 | > '''Checked in P5.''' [wiki:Normalization]
|
|---|
| 75 | > starts from a single de-normalized relation containing only the attributes
|
|---|
| 76 | > of the ER model and the functional dependencies that follow from its rules.
|
|---|
| 77 | > It decomposes that relation step by step to BCNF and arrives at these same 11
|
|---|
| 78 | > relations, with one deliberate difference: `transactions.user_id` (see the last
|
|---|
| 79 | > bullet below). The comparison is in the ''Discussion'' section at the end of that
|
|---|
| 80 | > page.
|
|---|
| 81 |
|
|---|
| 82 | All relations except `transactions` are in '''BCNF''', as P5 shows. `transactions`
|
|---|
| 83 | is in 2NF but not in 3NF, because of the deliberately kept `user_id` (last
|
|---|
| 84 | bullet):
|
|---|
| 85 |
|
|---|
| 86 | * Every attribute is atomic (no repeating groups, no composite fields).
|
|---|
| 87 | * No partial dependency exists: every candidate key is either the single column `id` or a composite key (`{user_id, crypto_id}`, …) on which no non-key attribute depends only partially.
|
|---|
| 88 | * No transitive dependency exists, except `transactions.user_id` (last bullet): every other non-key attribute depends directly on the row's own entity, never on another entity reached through a foreign key. For example, `holdings.quantity` depends on `holdings.id`, and nothing about the user or the crypto is copied into `holdings`.
|
|---|
| 89 | * `avg_price` in `Holdings` is a '''derived value''' cached for performance (it is the weighted-average entry price across all `buy` transactions for that `(user, crypto)` pair) — it is drawn as a derived attribute in the ER diagram. We accept the denormalisation: it is recomputed by the database inside the same transaction as each buy, in the same statement that changes the quantity (`INSERT … ON CONFLICT (user_id, crypto_id) DO UPDATE`), so the stored average and the stored quantity can never disagree.
|
|---|
| 90 | * `avg_price` is declared `NOT NULL DEFAULT 0`. This matters: it is used in the P/L arithmetic of `v_portfolio`, and in SQL any arithmetic involving `NULL` yields `NULL`, so a nullable average would have silently blanked the unrealised-P/L column for an existing position instead of failing loudly.
|
|---|
| 91 | * `holdings.reserved_quantity`, unlike `avg_price`, is '''not''' derived — it is written directly by the application (`trade.go`) as orders are placed and settled, the same way `quantity` itself is. `quantity - reserved_quantity` ("available") is the derived value here, and it is never stored, only computed where it is needed.
|
|---|
| 92 | * `transactions.user_id` is kept '''deliberately''', although for an entry that settles an order it repeats that order's user (`related_order → user_id`, a transitive dependency). A deposit has no order (`Settles` is partial), so `user_id` is the only way to record whose deposit it is. For entries with an order, the only code that sets `related_order` (the buy and sell inserts in `advanced_db.sql`) writes both from the same order row. No database constraint enforces this.
|
|---|
| 93 |
|
|---|
| 94 | === Reservation and the order lifecycle ===
|
|---|
| 95 |
|
|---|
| 96 | `holdings.reserved_quantity` exists so that placing a sell order can be
|
|---|
| 97 | checked against what a user actually has ''free'' to sell
|
|---|
| 98 | (`quantity - reserved_quantity`), not against the raw `quantity`, which also
|
|---|
| 99 | counts crypto already promised to another order that has not settled yet.
|
|---|
| 100 | `CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity)` makes an
|
|---|
| 101 | inconsistent reservation impossible at the database level, regardless of what
|
|---|
| 102 | application code does. The exact statement sequence — lock the row, check the
|
|---|
| 103 | available amount, reserve, then settle — is in
|
|---|
| 104 | [wiki:UseCase0005]; the same
|
|---|
| 105 | `SELECT … FOR UPDATE` locking that already protected `users.available_balance`
|
|---|
| 106 | on the buy path is what makes two concurrent sell orders against the same
|
|---|
| 107 | holding serialize correctly instead of racing. The cash side of a buy order
|
|---|
| 108 | (`users.reserved_balance`), `orders.filled_quantity` and `order_events` were
|
|---|
| 109 | added in P7; see
|
|---|
| 110 | [wiki:AdvancedDatabaseDevelopment].
|
|---|
| 111 |
|
|---|
| 112 | == DDL script ==
|
|---|
| 113 |
|
|---|
| 114 | The script that creates the schema is `../server/db/schema_creation.sql` (shown 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.
|
|---|
| 115 |
|
|---|
| 116 | The script creates:
|
|---|
| 117 | * 10 of the 11 tables, with check constraints, primary keys, foreign keys and unique constraints.
|
|---|
| 118 | * 8 performance indexes.
|
|---|
| 119 | * 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`).
|
|---|
| 120 |
|
|---|
| 121 | The 11th table, `order_events`, is created by
|
|---|
| 122 | `../server/db/advanced_db.sql` together with
|
|---|
| 123 | the P7 triggers that fill it. `./eduberza -init` runs both scripts in that
|
|---|
| 124 | order, so a freshly initialised database always has all 11 tables and all 15
|
|---|
| 125 | foreign keys.
|
|---|
| 126 |
|
|---|
| 127 | === schema_creation.sql ===
|
|---|
| 128 |
|
|---|
| 129 | The two report functions at the end of the file (`report_top_traders` and `report_market_performance`) belong to Phase 6 ([wiki:AdvancedReports]) and are left out here.
|
|---|
| 130 |
|
|---|
| 131 | {{{
|
|---|
| 132 | -- schema_creation.sql
|
|---|
| 133 | -- EduBerza - crypto exchange simulation database
|
|---|
| 134 | -- Course: Databases 2025/2026 Winter, FINKI UKIM
|
|---|
| 135 | --
|
|---|
| 136 | -- This script is idempotent. It drops the `project` schema and all contained
|
|---|
| 137 | -- objects, then recreates them from scratch. Safe to run on an empty database
|
|---|
| 138 | -- or on a database where the schema already exists.
|
|---|
| 139 |
|
|---|
| 140 | DROP SCHEMA IF EXISTS project CASCADE;
|
|---|
| 141 | CREATE SCHEMA project;
|
|---|
| 142 |
|
|---|
| 143 | CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|---|
| 144 |
|
|---|
| 145 | SET search_path TO project, public;
|
|---|
| 146 |
|
|---|
| 147 | -- ============================================================================
|
|---|
| 148 | -- USERS
|
|---|
| 149 | -- Platform users. Each user has virtual (prop) balances used for simulation.
|
|---|
| 150 | -- ============================================================================
|
|---|
| 151 | CREATE TABLE project.users (
|
|---|
| 152 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 153 | username varchar(50) NOT NULL UNIQUE,
|
|---|
| 154 | email varchar(255) NOT NULL UNIQUE,
|
|---|
| 155 | full_name varchar(200),
|
|---|
| 156 | password_hash varchar(255) NOT NULL,
|
|---|
| 157 | available_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
|
|---|
| 158 | invested_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (invested_balance >= 0),
|
|---|
| 159 | -- P7: cash committed to the user's active buy orders, moved out of
|
|---|
| 160 | -- available_balance when the order is placed and consumed as it fills.
|
|---|
| 161 | reserved_balance numeric(18,4) NOT NULL DEFAULT 0 CHECK (reserved_balance >= 0),
|
|---|
| 162 | created_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 163 | updated_at timestamptz
|
|---|
| 164 | );
|
|---|
| 165 |
|
|---|
| 166 | -- ============================================================================
|
|---|
| 167 | -- CRYPTO
|
|---|
| 168 | -- Catalog of crypto assets available on the platform.
|
|---|
| 169 | -- ============================================================================
|
|---|
| 170 | CREATE TABLE project.crypto (
|
|---|
| 171 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 172 | symbol varchar(20) NOT NULL UNIQUE,
|
|---|
| 173 | name varchar(255) NOT NULL,
|
|---|
| 174 | created_at timestamptz NOT NULL DEFAULT now()
|
|---|
| 175 | );
|
|---|
| 176 |
|
|---|
| 177 | -- ============================================================================
|
|---|
| 178 | -- MARKETS
|
|---|
| 179 | -- A market is a (crypto, quote_currency) pair, e.g. BTC/USD.
|
|---|
| 180 | -- ============================================================================
|
|---|
| 181 | CREATE TABLE project.markets (
|
|---|
| 182 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 183 | crypto_id uuid NOT NULL REFERENCES project.crypto(id),
|
|---|
| 184 | quote_currency char(3) NOT NULL DEFAULT 'USD',
|
|---|
| 185 | is_active boolean NOT NULL DEFAULT true,
|
|---|
| 186 | created_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 187 | CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency)
|
|---|
| 188 | );
|
|---|
| 189 |
|
|---|
| 190 | -- ============================================================================
|
|---|
| 191 | -- HOLDINGS
|
|---|
| 192 | -- Per-user crypto position with running weighted average entry price.
|
|---|
| 193 | -- ============================================================================
|
|---|
| 194 | CREATE TABLE project.holdings (
|
|---|
| 195 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 196 | user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
|
|---|
| 197 | crypto_id uuid NOT NULL REFERENCES project.crypto(id),
|
|---|
| 198 | quantity numeric(20,4) NOT NULL CHECK (quantity >= 0),
|
|---|
| 199 | -- Committed to the user's own open sell orders, not yet removed from the
|
|---|
| 200 | -- position. quantity - reserved_quantity is what is actually free to
|
|---|
| 201 | -- sell — the crypto-side equivalent of users.available_balance.
|
|---|
| 202 | reserved_quantity numeric(20,4) NOT NULL DEFAULT 0
|
|---|
| 203 | CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
|
|---|
| 204 | -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
|
|---|
| 205 | -- v_portfolio can never silently produce NULL for an existing position.
|
|---|
| 206 | avg_price numeric(18,6) NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
|
|---|
| 207 | created_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 208 | updated_at timestamptz,
|
|---|
| 209 | CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
|
|---|
| 210 | );
|
|---|
| 211 |
|
|---|
| 212 | -- ============================================================================
|
|---|
| 213 | -- ORDERS
|
|---|
| 214 | -- Orders placed by users on a market.
|
|---|
| 215 | -- ============================================================================
|
|---|
| 216 | CREATE TABLE project.orders (
|
|---|
| 217 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 218 | user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
|
|---|
| 219 | market_id uuid NOT NULL REFERENCES project.markets(id),
|
|---|
| 220 | side varchar(4) NOT NULL CHECK (side IN ('buy', 'sell')),
|
|---|
| 221 | type varchar(20) NOT NULL CHECK (type IN ('market', 'limit')),
|
|---|
| 222 | status varchar(20) NOT NULL CHECK (status IN ('open', 'partially_filled', 'executed', 'cancelled')),
|
|---|
| 223 | quantity numeric(20,4) NOT NULL CHECK (quantity > 0),
|
|---|
| 224 | -- P7: how much of the order has been traded so far; remaining is
|
|---|
| 225 | -- quantity - filled_quantity. Maintained from market_trades.
|
|---|
| 226 | filled_quantity numeric(20,4) NOT NULL DEFAULT 0
|
|---|
| 227 | CHECK (filled_quantity >= 0 AND filled_quantity <= quantity),
|
|---|
| 228 | price numeric(18,6),
|
|---|
| 229 | placed_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 230 | executed_at timestamptz
|
|---|
| 231 | );
|
|---|
| 232 |
|
|---|
| 233 | CREATE INDEX idx_orders_user ON project.orders(user_id);
|
|---|
| 234 | CREATE INDEX idx_orders_market ON project.orders(market_id);
|
|---|
| 235 | CREATE INDEX idx_orders_status ON project.orders(status);
|
|---|
| 236 |
|
|---|
| 237 | -- ============================================================================
|
|---|
| 238 | -- TRANSACTIONS
|
|---|
| 239 | -- Financial ledger: deposits, buys, sells, fees.
|
|---|
| 240 | -- ============================================================================
|
|---|
| 241 | CREATE TABLE project.transactions (
|
|---|
| 242 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 243 | user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
|
|---|
| 244 | type varchar(50) NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
|
|---|
| 245 | amount numeric(18,4) NOT NULL,
|
|---|
| 246 | currency char(3) NOT NULL DEFAULT 'USD',
|
|---|
| 247 | related_order uuid REFERENCES project.orders(id),
|
|---|
| 248 | created_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 249 | description text
|
|---|
| 250 | );
|
|---|
| 251 |
|
|---|
| 252 | CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC);
|
|---|
| 253 |
|
|---|
| 254 | -- ============================================================================
|
|---|
| 255 | -- MARKET TRADES
|
|---|
| 256 | -- Raw executed trades on a market. Source of truth for current price.
|
|---|
| 257 | -- ============================================================================
|
|---|
| 258 | CREATE TABLE project.market_trades (
|
|---|
| 259 | id bigserial PRIMARY KEY,
|
|---|
| 260 | market_id uuid NOT NULL REFERENCES project.markets(id),
|
|---|
| 261 | executed_at timestamptz NOT NULL,
|
|---|
| 262 | price numeric(18,6) NOT NULL CHECK (price > 0),
|
|---|
| 263 | quantity numeric(20,6) NOT NULL CHECK (quantity > 0),
|
|---|
| 264 | side varchar(4) CHECK (side IN ('buy', 'sell')),
|
|---|
| 265 | source varchar(50) NOT NULL DEFAULT 'simulation',
|
|---|
| 266 | -- P7: the orders this trade filled. NULL on a side means the counterparty
|
|---|
| 267 | -- was the simulated market (bot ticks have both NULL).
|
|---|
| 268 | buy_order_id uuid REFERENCES project.orders(id),
|
|---|
| 269 | sell_order_id uuid REFERENCES project.orders(id)
|
|---|
| 270 | );
|
|---|
| 271 |
|
|---|
| 272 | CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
|
|---|
| 273 | CREATE INDEX idx_market_trades_buy_order ON project.market_trades(buy_order_id) WHERE buy_order_id IS NOT NULL;
|
|---|
| 274 | CREATE INDEX idx_market_trades_sell_order ON project.market_trades(sell_order_id) WHERE sell_order_id IS NOT NULL;
|
|---|
| 275 |
|
|---|
| 276 | -- ============================================================================
|
|---|
| 277 | -- MARKET CANDLES
|
|---|
| 278 | -- OHLCV aggregates over standard timeframes.
|
|---|
| 279 | -- ============================================================================
|
|---|
| 280 | CREATE TABLE project.market_candles (
|
|---|
| 281 | id bigserial PRIMARY KEY,
|
|---|
| 282 | market_id uuid NOT NULL REFERENCES project.markets(id),
|
|---|
| 283 | timeframe varchar(5) NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')),
|
|---|
| 284 | open numeric(18,6) NOT NULL,
|
|---|
| 285 | high numeric(18,6) NOT NULL,
|
|---|
| 286 | low numeric(18,6) NOT NULL,
|
|---|
| 287 | close numeric(18,6) NOT NULL,
|
|---|
| 288 | volume numeric(20,6) NOT NULL,
|
|---|
| 289 | candle_time timestamptz NOT NULL,
|
|---|
| 290 | CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time)
|
|---|
| 291 | );
|
|---|
| 292 |
|
|---|
| 293 | CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC);
|
|---|
| 294 |
|
|---|
| 295 | -- ============================================================================
|
|---|
| 296 | -- WATCHLISTS
|
|---|
| 297 | -- ============================================================================
|
|---|
| 298 | CREATE TABLE project.watchlists (
|
|---|
| 299 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 300 | user_id uuid NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
|
|---|
| 301 | name varchar(100) NOT NULL,
|
|---|
| 302 | created_at timestamptz NOT NULL DEFAULT now()
|
|---|
| 303 | );
|
|---|
| 304 |
|
|---|
| 305 | CREATE TABLE project.watchlist_items (
|
|---|
| 306 | id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|---|
| 307 | watchlist_id uuid NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE,
|
|---|
| 308 | crypto_id uuid NOT NULL REFERENCES project.crypto(id),
|
|---|
| 309 | added_at timestamptz NOT NULL DEFAULT now(),
|
|---|
| 310 | CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id)
|
|---|
| 311 | );
|
|---|
| 312 |
|
|---|
| 313 | -- ============================================================================
|
|---|
| 314 | -- VIEWS
|
|---|
| 315 | -- ============================================================================
|
|---|
| 316 |
|
|---|
| 317 | -- Latest trade price per market (current price).
|
|---|
| 318 | CREATE OR REPLACE VIEW project.v_latest_prices AS
|
|---|
| 319 | SELECT DISTINCT ON (t.market_id)
|
|---|
| 320 | t.market_id,
|
|---|
| 321 | c.symbol,
|
|---|
| 322 | m.quote_currency,
|
|---|
| 323 | t.price,
|
|---|
| 324 | t.executed_at
|
|---|
| 325 | FROM project.market_trades t
|
|---|
| 326 | JOIN project.markets m ON m.id = t.market_id
|
|---|
| 327 | JOIN project.crypto c ON c.id = m.crypto_id
|
|---|
| 328 | ORDER BY t.market_id, t.executed_at DESC;
|
|---|
| 329 |
|
|---|
| 330 | -- Portfolio valuation per user (holdings x latest price).
|
|---|
| 331 | CREATE OR REPLACE VIEW project.v_portfolio AS
|
|---|
| 332 | SELECT h.user_id,
|
|---|
| 333 | c.symbol,
|
|---|
| 334 | h.quantity,
|
|---|
| 335 | h.reserved_quantity,
|
|---|
| 336 | (h.quantity - h.reserved_quantity) AS available_quantity,
|
|---|
| 337 | h.avg_price,
|
|---|
| 338 | lp.price AS current_price,
|
|---|
| 339 | (h.quantity * lp.price) AS market_value,
|
|---|
| 340 | (h.quantity * (lp.price - h.avg_price)) AS unrealized_pnl
|
|---|
| 341 | FROM project.holdings h
|
|---|
| 342 | JOIN project.crypto c ON c.id = h.crypto_id
|
|---|
| 343 | LEFT JOIN project.markets m ON m.crypto_id = c.id AND m.quote_currency = 'USD'
|
|---|
| 344 | LEFT JOIN project.v_latest_prices lp ON lp.market_id = m.id;
|
|---|
| 345 | }}}
|
|---|
| 346 |
|
|---|
| 347 | === order_events (from advanced_db.sql) ===
|
|---|
| 348 |
|
|---|
| 349 | {{{
|
|---|
| 350 | CREATE TABLE project.order_events (
|
|---|
| 351 | id bigserial PRIMARY KEY,
|
|---|
| 352 | order_id uuid NOT NULL REFERENCES project.orders(id) ON DELETE CASCADE,
|
|---|
| 353 | event_type varchar(20) NOT NULL
|
|---|
| 354 | CHECK (event_type IN ('placed', 'partially_filled', 'filled', 'cancelled')),
|
|---|
| 355 | quantity numeric(20,4) NOT NULL,
|
|---|
| 356 | price numeric(18,6),
|
|---|
| 357 | status_after varchar(20) NOT NULL,
|
|---|
| 358 | created_at timestamptz NOT NULL DEFAULT clock_timestamp()
|
|---|
| 359 | );
|
|---|
| 360 |
|
|---|
| 361 | CREATE INDEX idx_order_events_order ON project.order_events(order_id, id);
|
|---|
| 362 | }}}
|
|---|
| 363 |
|
|---|
| 364 | == DML script (sample data) ==
|
|---|
| 365 |
|
|---|
| 366 | The script that loads realistic sample data is `../server/db/data_load.sql` (shown below). It is idempotent: it truncates all tables with `CASCADE` then re-inserts. Loaded:
|
|---|
| 367 | * 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets.
|
|---|
| 368 | * 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex).
|
|---|
| 369 | * 18 recent market trades across all markets so `v_latest_prices` is populated.
|
|---|
| 370 | * 10 one-hour candles (BTC and ETH).
|
|---|
| 371 | * One fully-executed market-buy order for Alice, the matching holding, and two ledger entries (deposit + buy), with Alice's balances updated accordingly.
|
|---|
| 372 | * Two watchlists with five watchlist items.
|
|---|
| 373 |
|
|---|
| 374 | === data_load.sql ===
|
|---|
| 375 |
|
|---|
| 376 | {{{
|
|---|
| 377 | -- data_load.sql
|
|---|
| 378 | -- EduBerza - sample data
|
|---|
| 379 | -- Course: Databases 2025/2026 Winter, FINKI UKIM
|
|---|
| 380 | --
|
|---|
| 381 | -- Idempotent. Truncates all tables in the `project` schema and reloads
|
|---|
| 382 | -- deterministic sample data. Run schema_creation.sql first if tables do
|
|---|
| 383 | -- not yet exist.
|
|---|
| 384 | --
|
|---|
| 385 | -- All sample users have the password: test123
|
|---|
| 386 | --
|
|---|
| 387 | -- One transaction: the P7 checks in advanced_db.sql compare balances with
|
|---|
| 388 | -- the ledger at COMMIT, and the users are inserted with their balances
|
|---|
| 389 | -- before the deposit rows that back them. In an auto-commit client
|
|---|
| 390 | -- (DBeaver) every statement would otherwise be checked on its own.
|
|---|
| 391 |
|
|---|
| 392 | BEGIN;
|
|---|
| 393 |
|
|---|
| 394 | SET search_path TO project, public;
|
|---|
| 395 |
|
|---|
| 396 | TRUNCATE TABLE
|
|---|
| 397 | project.watchlist_items,
|
|---|
| 398 | project.watchlists,
|
|---|
| 399 | project.market_candles,
|
|---|
| 400 | project.market_trades,
|
|---|
| 401 | project.transactions,
|
|---|
| 402 | project.orders,
|
|---|
| 403 | project.holdings,
|
|---|
| 404 | project.markets,
|
|---|
| 405 | project.crypto,
|
|---|
| 406 | project.users
|
|---|
| 407 | RESTART IDENTITY CASCADE;
|
|---|
| 408 |
|
|---|
| 409 | -- ============================================================================
|
|---|
| 410 | -- CRYPTO
|
|---|
| 411 | -- ============================================================================
|
|---|
| 412 | INSERT INTO project.crypto (id, symbol, name) VALUES
|
|---|
| 413 | ('11111111-1111-1111-1111-111111111111', 'BTC', 'Bitcoin'),
|
|---|
| 414 | ('22222222-2222-2222-2222-222222222222', 'ETH', 'Ethereum'),
|
|---|
| 415 | ('33333333-3333-3333-3333-333333333333', 'ADA', 'Cardano'),
|
|---|
| 416 | ('44444444-4444-4444-4444-444444444444', 'SOL', 'Solana'),
|
|---|
| 417 | ('55555555-5555-5555-5555-555555555555', 'DOGE', 'Dogecoin');
|
|---|
| 418 |
|
|---|
| 419 | -- ============================================================================
|
|---|
| 420 | -- MARKETS (all quoted in USD)
|
|---|
| 421 | -- ============================================================================
|
|---|
| 422 | INSERT INTO project.markets (id, crypto_id, quote_currency, is_active) VALUES
|
|---|
| 423 | ('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'USD', true),
|
|---|
| 424 | ('a2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'USD', true),
|
|---|
| 425 | ('a3333333-3333-3333-3333-333333333333', '33333333-3333-3333-3333-333333333333', 'USD', true),
|
|---|
| 426 | ('a4444444-4444-4444-4444-444444444444', '44444444-4444-4444-4444-444444444444', 'USD', true),
|
|---|
| 427 | ('a5555555-5555-5555-5555-555555555555', '55555555-5555-5555-5555-555555555555', 'USD', true);
|
|---|
| 428 |
|
|---|
| 429 | -- ============================================================================
|
|---|
| 430 | -- USERS
|
|---|
| 431 | -- Password for all: test123 (stored as sha256 hex hash)
|
|---|
| 432 | -- ============================================================================
|
|---|
| 433 | INSERT INTO project.users (id, username, email, full_name, password_hash, available_balance, invested_balance) VALUES
|
|---|
| 434 | ('b1111111-1111-1111-1111-111111111111', 'alice', 'alice@example.com', 'Alice Johnson',
|
|---|
| 435 | encode(digest('test123', 'sha256'), 'hex'), 10000.0000, 0),
|
|---|
| 436 | ('b2222222-2222-2222-2222-222222222222', 'bob', 'bob@example.com', 'Bob Smith',
|
|---|
| 437 | encode(digest('test123', 'sha256'), 'hex'), 5000.0000, 0),
|
|---|
| 438 | ('b3333333-3333-3333-3333-333333333333', 'charlie', 'charlie@example.com', 'Charlie Davis',
|
|---|
| 439 | encode(digest('test123', 'sha256'), 'hex'), 2500.0000, 0);
|
|---|
| 440 |
|
|---|
| 441 | -- ============================================================================
|
|---|
| 442 | -- MARKET TRADES
|
|---|
| 443 | -- Recent simulated trades per market, used as price source.
|
|---|
| 444 | -- ============================================================================
|
|---|
| 445 | INSERT INTO project.market_trades (market_id, executed_at, price, quantity, side, source) VALUES
|
|---|
| 446 | -- BTC/USD around $67,000
|
|---|
| 447 | ('a1111111-1111-1111-1111-111111111111', now() - interval '10 min', 66850.250000, 0.120000, 'buy', 'simulation'),
|
|---|
| 448 | ('a1111111-1111-1111-1111-111111111111', now() - interval '8 min', 66910.500000, 0.075000, 'sell', 'simulation'),
|
|---|
| 449 | ('a1111111-1111-1111-1111-111111111111', now() - interval '5 min', 67020.750000, 0.200000, 'buy', 'simulation'),
|
|---|
| 450 | ('a1111111-1111-1111-1111-111111111111', now() - interval '2 min', 67105.100000, 0.050000, 'buy', 'simulation'),
|
|---|
| 451 | ('a1111111-1111-1111-1111-111111111111', now() - interval '30 second', 67140.000000, 0.030000, 'sell', 'simulation'),
|
|---|
| 452 | -- ETH/USD around $3,500
|
|---|
| 453 | ('a2222222-2222-2222-2222-222222222222', now() - interval '10 min', 3490.500000, 1.500000, 'buy', 'simulation'),
|
|---|
| 454 | ('a2222222-2222-2222-2222-222222222222', now() - interval '6 min', 3502.750000, 0.800000, 'sell', 'simulation'),
|
|---|
| 455 | ('a2222222-2222-2222-2222-222222222222', now() - interval '2 min', 3515.250000, 2.100000, 'buy', 'simulation'),
|
|---|
| 456 | ('a2222222-2222-2222-2222-222222222222', now() - interval '30 second', 3520.000000, 0.650000, 'buy', 'simulation'),
|
|---|
| 457 | -- ADA/USD around $0.45
|
|---|
| 458 | ('a3333333-3333-3333-3333-333333333333', now() - interval '10 min', 0.446500, 500.000000, 'buy', 'simulation'),
|
|---|
| 459 | ('a3333333-3333-3333-3333-333333333333', now() - interval '3 min', 0.452000, 1200.000000, 'buy', 'simulation'),
|
|---|
| 460 | ('a3333333-3333-3333-3333-333333333333', now() - interval '30 second', 0.453750, 800.000000, 'sell', 'simulation'),
|
|---|
| 461 | -- SOL/USD around $165
|
|---|
| 462 | ('a4444444-4444-4444-4444-444444444444', now() - interval '10 min', 164.250000, 10.000000, 'buy', 'simulation'),
|
|---|
| 463 | ('a4444444-4444-4444-4444-444444444444', now() - interval '4 min', 165.500000, 5.500000, 'sell', 'simulation'),
|
|---|
| 464 | ('a4444444-4444-4444-4444-444444444444', now() - interval '30 second', 166.100000, 8.000000, 'buy', 'simulation'),
|
|---|
| 465 | -- DOGE/USD around $0.12
|
|---|
| 466 | ('a5555555-5555-5555-5555-555555555555', now() - interval '10 min', 0.118500, 10000.000000, 'buy', 'simulation'),
|
|---|
| 467 | ('a5555555-5555-5555-5555-555555555555', now() - interval '3 min', 0.121250, 7500.000000, 'sell', 'simulation'),
|
|---|
| 468 | ('a5555555-5555-5555-5555-555555555555', now() - interval '30 second', 0.122000, 12000.000000, 'buy', 'simulation');
|
|---|
| 469 |
|
|---|
| 470 | -- ============================================================================
|
|---|
| 471 | -- MARKET CANDLES (1h aggregates, last 5 hours per market)
|
|---|
| 472 | -- ============================================================================
|
|---|
| 473 | INSERT INTO project.market_candles (market_id, timeframe, open, high, low, close, volume, candle_time) VALUES
|
|---|
| 474 | ('a1111111-1111-1111-1111-111111111111', '1h', 66200, 66500, 66050, 66400, 12.50, date_trunc('hour', now() - interval '5 hour')),
|
|---|
| 475 | ('a1111111-1111-1111-1111-111111111111', '1h', 66400, 66800, 66380, 66700, 15.30, date_trunc('hour', now() - interval '4 hour')),
|
|---|
| 476 | ('a1111111-1111-1111-1111-111111111111', '1h', 66700, 66950, 66650, 66900, 11.80, date_trunc('hour', now() - interval '3 hour')),
|
|---|
| 477 | ('a1111111-1111-1111-1111-111111111111', '1h', 66900, 67100, 66800, 67050, 14.20, date_trunc('hour', now() - interval '2 hour')),
|
|---|
| 478 | ('a1111111-1111-1111-1111-111111111111', '1h', 67050, 67200, 66900, 67140, 10.75, date_trunc('hour', now() - interval '1 hour')),
|
|---|
| 479 | ('a2222222-2222-2222-2222-222222222222', '1h', 3460, 3490, 3450, 3485, 120.0, date_trunc('hour', now() - interval '5 hour')),
|
|---|
| 480 | ('a2222222-2222-2222-2222-222222222222', '1h', 3485, 3510, 3480, 3500, 135.0, date_trunc('hour', now() - interval '4 hour')),
|
|---|
| 481 | ('a2222222-2222-2222-2222-222222222222', '1h', 3500, 3520, 3495, 3515, 110.0, date_trunc('hour', now() - interval '3 hour')),
|
|---|
| 482 | ('a2222222-2222-2222-2222-222222222222', '1h', 3515, 3525, 3500, 3520, 125.5, date_trunc('hour', now() - interval '2 hour')),
|
|---|
| 483 | ('a2222222-2222-2222-2222-222222222222', '1h', 3520, 3530, 3510, 3520, 140.0, date_trunc('hour', now() - interval '1 hour'));
|
|---|
| 484 |
|
|---|
| 485 | -- ============================================================================
|
|---|
| 486 | -- EXAMPLE ORDERS, HOLDINGS AND TRANSACTIONS for alice
|
|---|
| 487 | -- Shows a fully-filled market buy and its resulting holding & ledger entry.
|
|---|
| 488 | -- ============================================================================
|
|---|
| 489 | -- Imported as already completely filled (filled_quantity = quantity).
|
|---|
| 490 | INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
|
|---|
| 491 | ('c1111111-1111-1111-1111-111111111111',
|
|---|
| 492 | 'b1111111-1111-1111-1111-111111111111',
|
|---|
| 493 | 'a2222222-2222-2222-2222-222222222222',
|
|---|
| 494 | 'buy', 'market', 'executed', 0.5000, 0.5000, 3500.000000,
|
|---|
| 495 | now() - interval '1 hour', now() - interval '1 hour');
|
|---|
| 496 |
|
|---|
| 497 | INSERT INTO project.holdings (user_id, crypto_id, quantity, avg_price, updated_at) VALUES
|
|---|
| 498 | ('b1111111-1111-1111-1111-111111111111',
|
|---|
| 499 | '22222222-2222-2222-2222-222222222222',
|
|---|
| 500 | 0.5000, 3500.000000, now() - interval '1 hour');
|
|---|
| 501 |
|
|---|
| 502 | INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES
|
|---|
| 503 | ('b1111111-1111-1111-1111-111111111111', 'deposit', 10000.0000, 'USD', NULL,
|
|---|
| 504 | 'Initial virtual deposit'),
|
|---|
| 505 | ('b2222222-2222-2222-2222-222222222222', 'deposit', 5000.0000, 'USD', NULL,
|
|---|
| 506 | 'Initial virtual deposit'),
|
|---|
| 507 | ('b3333333-3333-3333-3333-333333333333', 'deposit', 2500.0000, 'USD', NULL,
|
|---|
| 508 | 'Initial virtual deposit'),
|
|---|
| 509 | ('b1111111-1111-1111-1111-111111111111', 'buy', -1750.0000, 'USD',
|
|---|
| 510 | 'c1111111-1111-1111-1111-111111111111',
|
|---|
| 511 | 'Market buy 0.5 ETH @ 3500.00');
|
|---|
| 512 |
|
|---|
| 513 | -- After the buy, alice's invested_balance reflects the used funds.
|
|---|
| 514 | UPDATE project.users
|
|---|
| 515 | SET available_balance = 10000.0000 - 1750.0000,
|
|---|
| 516 | invested_balance = 1750.0000,
|
|---|
| 517 | updated_at = now()
|
|---|
| 518 | WHERE id = 'b1111111-1111-1111-1111-111111111111';
|
|---|
| 519 |
|
|---|
| 520 | -- ============================================================================
|
|---|
| 521 | -- WATCHLISTS
|
|---|
| 522 | -- ============================================================================
|
|---|
| 523 | INSERT INTO project.watchlists (id, user_id, name) VALUES
|
|---|
| 524 | ('d1111111-1111-1111-1111-111111111111', 'b1111111-1111-1111-1111-111111111111', 'Favorites'),
|
|---|
| 525 | ('d2222222-2222-2222-2222-222222222222', 'b2222222-2222-2222-2222-222222222222', 'Bobs Picks');
|
|---|
| 526 |
|
|---|
| 527 | INSERT INTO project.watchlist_items (watchlist_id, crypto_id) VALUES
|
|---|
| 528 | ('d1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111'),
|
|---|
| 529 | ('d1111111-1111-1111-1111-111111111111', '22222222-2222-2222-2222-222222222222'),
|
|---|
| 530 | ('d1111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444'),
|
|---|
| 531 | ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'),
|
|---|
| 532 | ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555');
|
|---|
| 533 |
|
|---|
| 534 | COMMIT;
|
|---|
| 535 | }}}
|
|---|
| 536 |
|
|---|
| 537 | == Relational diagram ==
|
|---|
| 538 |
|
|---|
| 539 | [[Image(relational_diagram_v4.png, 800px)]]
|
|---|
| 540 |
|
|---|
| 541 | Generated in '''DBeaver''' from the '''live''' `project` schema (after
|
|---|
| 542 | `./eduberza -init`), not drawn by hand, so it shows what the deployed database
|
|---|
| 543 | actually contains. Each box is a table with its columns; the key icon marks the
|
|---|
| 544 | primary key, and the lines are the 15 declared foreign keys. The two foreign keys from
|
|---|
| 545 | `market_trades` to `orders` (`buy_order_id`, `sell_order_id`) connect the same
|
|---|
| 546 | two boxes, so DBeaver draws them on top of each other as one line.
|
|---|
| 547 |
|
|---|
| 548 | The tables are arranged in the '''same positions''' as the entity sets in
|
|---|
| 549 | `ERModel_v05.png`, so the two can be compared directly:
|
|---|
| 550 |
|
|---|
| 551 | * every rectangle of the ER diagram is one table in the same place;
|
|---|
| 552 | * every diamond of the ER diagram is one foreign-key line between the same two boxes. The dot is on the referencing ("N") table, next to the foreign-key column;
|
|---|
| 553 | * a double (total) line in the ER diagram is a `NOT NULL` foreign key, drawn by DBeaver as a solid line. The three single lines on the N side (`Settles`, `FillsBuy`, `FillsSell`) are the three nullable foreign keys, which DBeaver draws dashed, with a hollow diamond on the `orders` side. The table under Transformation method used lists all 15.
|
|---|
| 554 |
|
|---|
| 555 | Earlier images (`relational_schema.jpg`, `relational_schema_v2.png`,
|
|---|
| 556 | `relational_schema_v3.png`) were exported from pgAdmin, with a different layout
|
|---|
| 557 | and from an older schema. They are kept only as history.
|
|---|
| 558 |
|
|---|
| 559 | === How to regenerate it ===
|
|---|
| 560 |
|
|---|
| 561 | 1. Initialise the database: `./eduberza -init` (runs `schema_creation.sql` and `advanced_db.sql`, so `order_events` is included).
|
|---|
| 562 | 2. In DBeaver, connect to the project database and expand ''Schemas → project → Tables''.
|
|---|
| 563 | 3. Select all 11 tables → right-click → '''View Diagram''' (or create a new ER diagram and drag the tables in).
|
|---|
| 564 | 4. Drag each table to the position of its entity set in `ERModel_v05.png`:
|
|---|
| 565 |
|
|---|
| 566 | {{{
|
|---|
| 567 | column 1 column 2 column 3 column 4
|
|---|
| 568 | row 1 watchlist_items crypto markets market_candles
|
|---|
| 569 | row 2 watchlists holdings . market_trades
|
|---|
| 570 | row 3 users . orders .
|
|---|
| 571 | row 4 . transactions order_events .
|
|---|
| 572 | }}}
|
|---|
| 573 |
|
|---|
| 574 | Leave the empty cells (`.`) empty. They are where the relationship
|
|---|
| 575 | diamonds are in the ER diagram, so the foreign-key lines will run through
|
|---|
| 576 | the same gaps.
|
|---|
| 577 |
|
|---|
| 578 | 5. Right-click the canvas → '''Export diagram''' → PNG, saved as `relational_diagram_v4.png` in this folder.
|
|---|
