Ignore:
Timestamp:
09/29/26 20:55:13 (6 hours ago)
Author:
Stefan <trsunovstefan@…>
Branches:
main
Parents:
0cee8ec
Message:

Correct P1/P2 consistency (Holdings, WatchlistItems), redo P5 normalization

Location:
docs/P2-RelationalDesign
Files:
1 added
5 edited

Legend:

Unmodified
Added
Removed

  • docs/P2-RelationalDesign/RelationalDesign.md

    r0cee8ec r1549dae  
    11# Relational Design
    22
     3This page transforms [ERModel](../P1-ConceptualModel/ERModel.md) **v05** into
     4relations. Every relation below corresponds to exactly one entity set of the
     5model, and every foreign key corresponds to exactly one relationship, so the
     6two diagrams can be compared box for box and line for line (see
     7[Relational diagram](#relational-diagram)).
     8
    39## Descriptive representation of the relational schema
    410
    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)`.
     11Notation: **bold** = primary key, *italic* = foreign key. After each foreign key
     12comes the ER relationship it implements.
     13
     14- **Users**(<u>**id**</u>, 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)`.
    916- **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, 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.
     17  - Entity set `Cryptos`. Candidate keys: `{id}`, `{symbol}`. `UNIQUE(symbol)`.
     18- **Markets**(<u>**id**</u>, *crypto_id* [`QuotedOn`], quote_currency, is_active, created_at)
     19  - Entity set `Markets`. Candidate keys: `{id}`, `{crypto_id, quote_currency}`
     20    (the model's rule "a crypto is quoted at most once per currency"),
     21    enforced with `UNIQUE(crypto_id, quote_currency)`.
     22- **Holdings**(<u>**id**</u>, *user_id* [`Holds`], *crypto_id* [`PositionIn`], quantity, reserved_quantity, avg_price, created_at, updated_at)
     23  - Entity set `Holdings`. Candidate keys: `{id}` and `{user_id, crypto_id}`
     24    (the model's rule "one holding per user and crypto"), enforced with
     25    `UNIQUE(user_id, crypto_id)`.
    1826  - `avg_price` is `NOT NULL DEFAULT 0 CHECK (avg_price >= 0)`.
    1927  - `reserved_quantity` is `NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0
    … …  
    2331    needed, in `v_portfolio` as `available_quantity` and in the sell path of
    2432    [UseCase0005](../P3-UseCaseModel/UseCase0005.md). See
    25     [ERModel](../P1-ConceptualModel/ERModel.md#holds--users-m--cryptos-n-partial-on-both-sides-with-attributes)
     33    [ERModel](../P1-ConceptualModel/ERModel.md#holdings)
    2634    for why this mirrors `available_balance`/`invested_balance` on `Users`.
    27 - **Orders**(<u>**id**</u>, *user_id*, *market_id*, side, type, status, quantity, price, placed_at, executed_at)
    28   - `side ∈ {buy, sell}`, `type ∈ {market, limit}`, `status ∈ {open, executed, cancelled}`.
    29 - **Transactions**(<u>**id**</u>, *user_id*, type, amount, currency, *related_order*, created_at, description)
    30   - `type ∈ {deposit, buy, sell, fee}`.
    31 - **MarketTrades**(<u>**id**</u>, *market_id*, executed_at, price, quantity, side, source)
    32 - **MarketCandles**(<u>**id**</u>, *market_id*, timeframe, open, high, low, close, volume, candle_time)
    33   - `UNIQUE(market_id, timeframe, candle_time)`.
    34 - **Watchlists**(<u>**id**</u>, *user_id*, name, created_at)
    35 - **WatchlistItems**(<u>**id**</u>, *watchlist_id*, *crypto_id*, added_at)
    36   - Transformation of the M:N relationship `Contains`. Candidate keys: `{id}`
    37     and `{watchlist_id, crypto_id}`, the latter enforced with
    38     `UNIQUE(watchlist_id, crypto_id)`.
     35- **Orders**(<u>**id**</u>, *user_id* [`Places`], *market_id* [`PlacedOn`], side, type, status, quantity, filled_quantity, price, placed_at, executed_at)
     36  - Entity set `Orders`. `side ∈ {buy, sell}`, `type ∈ {market, limit}`,
     37    `status ∈ {open, partially_filled, executed, cancelled}`,
     38    `0 ≤ filled_quantity ≤ quantity`.
     39- **Transactions**(<u>**id**</u>, *user_id* [`Records`], type, amount, currency, *related_order* [`Settles`], created_at, description)
     40  - Entity set `Transactions`. `type ∈ {deposit, buy, sell, fee}`.
     41    `related_order` is nullable (see below).
     42- **MarketTrades**(<u>**id**</u>, *market_id* [`Fills`], executed_at, price, quantity, side, source, *buy_order_id* [`FillsBuy`], *sell_order_id* [`FillsSell`])
     43  - Entity set `MarketTrades`. `buy_order_id` and `sell_order_id` are both
     44    nullable (see below).
     45- **OrderEvents**(<u>**id**</u>, *order_id* [`Logs`], event_type, quantity, price, status_after, created_at)
     46  - Entity set `OrderEvents`. `event_type ∈ {placed, partially_filled, filled, cancelled}`.
     47- **MarketCandles**(<u>**id**</u>, *market_id* [`Aggregates`], timeframe, open, high, low, close, volume, candle_time)
     48  - Entity set `MarketCandles`. Candidate keys: `{id}`, `{market_id, timeframe,
     49    candle_time}` (the model's rule "one candle per market, timeframe and
     50    bucket"), enforced with `UNIQUE(market_id, timeframe, candle_time)`.
     51- **Watchlists**(<u>**id**</u>, *user_id* [`Owns`], name, created_at)
     52  - Entity set `Watchlists`.
     53- **WatchlistItems**(<u>**id**</u>, *watchlist_id* [`Contains`], *crypto_id* [`Lists`], added_at)
     54  - Entity set `WatchlistItems`. Candidate keys: `{id}` and `{watchlist_id,
     55    crypto_id}` (the model's rule "an asset at most once per list"), enforced
     56    with `UNIQUE(watchlist_id, crypto_id)`.
    3957
    4058### Transformation method used
    4159
    42 **Partial transformation.** Applied as follows:
    43 
    44 - Each of the 8 entity sets in [ERModel](../P1-ConceptualModel/ERModel.md) becomes one table, keeping
    45   its UUID (or serial) primary key.
    46 - Each **1:N relationship without attributes** is transformed by adding the
    47   parent's primary key as a foreign-key column on the child table — the "N"
    48   side. This is where every foreign key in the schema comes from, and it is why
    49   no foreign keys appear in the ER diagram itself:
    50   `QuotedOn` → `markets.crypto_id`, `PlacedOn` → `orders.market_id`,
    51   `Places` → `orders.user_id`, `Records` → `transactions.user_id`,
    52   `Settles` → `transactions.related_order`, `Fills` → `market_trades.market_id`,
    53   `Aggregates` → `market_candles.market_id`, `Owns` → `watchlists.user_id`.
    54 - Each **M:N relationship** becomes its own table holding the two foreign keys
    55   plus the relationship's own attributes: `Holds` → `holdings`,
    56   `Contains` → `watchlist_items`. The pair of foreign keys is the relationship's
    57   key and is enforced as a `UNIQUE` constraint in both tables.
    58 - **Total participation** in the ER model becomes `NOT NULL` on the
    59   corresponding foreign key; partial participation stays nullable. `Settles` is
    60   partial on both sides, which is exactly why `transactions.related_order` is
    61   the one nullable foreign key in the schema — a deposit has no originating
    62   order.
     60**Partial transformation.** The model has 11 entity sets and 15 relationships.
     61Every relationship is binary and 1:N with no attributes of its own (the two M:N
     62relationships of earlier versions, `Holds` and `Contains`, were corrected into
     63the entity sets `Holdings` and `WatchlistItems` in v05). The rules:
     64
     65- **Each entity set becomes one relation**, with its own attributes and its
     66  own key `id` as primary key. 11 entity sets → 11 relations.
     67- **Each 1:N relationship becomes one foreign key** on the relation of the "N"
     68  side, pointing to the primary key of the "1" side. No relationship gets its
     69  own table, because none is M:N and none has attributes. 15 relationships →
     70  15 foreign keys:
     71
     72  | ER relationship | 1 side → N side | Foreign key | Participation of the N side | `NULL`? |
     73  |---|---|---|---|---|
     74  | `QuotedOn`   | Cryptos → Markets             | `markets.crypto_id`            | total   | `NOT NULL` |
     75  | `PlacedOn`   | Markets → Orders              | `orders.market_id`             | total   | `NOT NULL` |
     76  | `Places`     | Users → Orders                | `orders.user_id`               | total   | `NOT NULL` |
     77  | `Records`    | Users → Transactions          | `transactions.user_id`         | total   | `NOT NULL` |
     78  | `Settles`    | Orders → Transactions         | `transactions.related_order`   | partial | nullable |
     79  | `Fills`      | Markets → MarketTrades        | `market_trades.market_id`      | total   | `NOT NULL` |
     80  | `FillsBuy`   | Orders → MarketTrades         | `market_trades.buy_order_id`   | partial | nullable |
     81  | `FillsSell`  | Orders → MarketTrades         | `market_trades.sell_order_id`  | partial | nullable |
     82  | `Logs`       | Orders → OrderEvents          | `order_events.order_id`        | total   | `NOT NULL` |
     83  | `Aggregates` | Markets → MarketCandles       | `market_candles.market_id`     | total   | `NOT NULL` |
     84  | `Owns`       | Users → Watchlists            | `watchlists.user_id`           | total   | `NOT NULL` |
     85  | `Holds`      | Users → Holdings              | `holdings.user_id`             | total   | `NOT NULL` |
     86  | `PositionIn` | Cryptos → Holdings            | `holdings.crypto_id`           | total   | `NOT NULL` |
     87  | `Contains`   | Watchlists → WatchlistItems   | `watchlist_items.watchlist_id` | total   | `NOT NULL` |
     88  | `Lists`      | Cryptos → WatchlistItems      | `watchlist_items.crypto_id`    | total   | `NOT NULL` |
     89
     90- **Participation decides `NULL`.** Total participation of the N side means
     91  every row must reference a parent, so the foreign key is `NOT NULL`. Partial
     92  participation leaves it nullable. There are exactly three partial ones:
     93  `Settles` (a deposit has no originating order), and `FillsBuy` / `FillsSell`
     94  (a trade against the simulated market has no user order on that side).
     95  Partial participation of the **1** side (for example, a user with no orders)
     96  needs no column at all. It simply means no row points at that parent.
     97- **Uniqueness rules of the model become `UNIQUE` constraints.** The four
     98  rules the model states in words ("a crypto quoted once per currency", "one
     99  candle per market, timeframe and bucket", "one holding per user and crypto",
     100  "an asset once per list") involve a relationship, so Chen notation cannot
     101  draw them as keys. After transformation, the relationship is a foreign-key
     102  column, and each rule becomes an ordinary composite `UNIQUE` constraint, i.e. a
     103  second candidate key.
     104
     105Nothing in the schema comes from anywhere else. Every column is either an ER
     106attribute or the foreign key of one listed relationship.
    63107
    64108### Normalisation
    65109
    66 > **Validated in P5.** [Normalization](../P5-Normalization/Normalization.md) derives this
    67 > exact schema independently — starting only from a single de-normalized relation of every
    68 > model attribute and its functional dependencies, with no reference to the ER-to-relational
    69 > transformation below — and shows it decomposes to **BCNF**, one normal form stronger than
    70 > the 3NF claimed here. The two designs agree relation for relation and key for key, so
    71 > nothing here changed as a result; see that page's
    72 > [discussion](../P5-Normalization/Normalization.md#discussion) for what the one real
    73 > difference is (`avg_price`, a stored derived value, not a normalisation issue) and why this
    74 > design is still the one used from P5 onward.
    75 
    76 All relations are in **3NF**:
     110> **Checked in P5.** [Normalization](../P5-Normalization/Normalization.md)
     111> starts from a single de-normalized relation containing only the attributes
     112> of the ER model and the functional dependencies that follow from its rules.
     113> It decomposes that relation step by step to BCNF and arrives at these same 11
     114> relations, with one deliberate difference: `transactions.user_id` (see the last
     115> bullet below). The comparison is in the *Discussion* section at the end of that
     116> page.
     117
     118All relations except `transactions` are in **BCNF**, as P5 shows. `transactions`
     119is in 2NF but not in 3NF, because of the deliberately kept `user_id` (last
     120bullet):
    77121
    78122- Every attribute is atomic (no repeating groups, no composite fields).
    79 - No partial dependency exists because every primary key is a single UUID column.
    80 - 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.
     123- No partial dependency exists: every candidate key is either the single
     124  column `id` or a composite key (`{user_id, crypto_id}`, …) on which no
     125  non-key attribute depends only partially.
     126- No transitive dependency exists, except `transactions.user_id` (last bullet):
     127  every other non-key attribute depends directly on the row's own entity, never
     128  on another entity reached through a foreign key.
     129  For example, `holdings.quantity` depends on `holdings.id`, and nothing about
     130  the user or the crypto is copied into `holdings`.
    81131- `avg_price` in `Holdings` is a **derived value** cached for performance (it is
    82132  the weighted-average entry price across all `buy` transactions for that
    … …  
    95145  ("available") is the derived value here, and it is never stored, only
    96146  computed where it is needed.
     147- `transactions.user_id` is kept **deliberately**, although for an entry that
     148  settles an order it repeats that order's user (`related_order → user_id`, a
     149  transitive dependency). A deposit has no order (`Settles` is partial), so
     150  `user_id` is the only way to record whose deposit it is. For entries with an
     151  order, the only code that sets `related_order` (the buy and sell inserts in
     152  `advanced_db.sql`) writes both from the same order row. No database
     153  constraint enforces this.
    97154
    98155### Reservation and the order lifecycle
    … …  
    109166`SELECT … FOR UPDATE` locking that already protected `users.available_balance`
    110167on the buy path is what makes two concurrent sell orders against the same
    111 holding serialize correctly instead of racing.
     168holding serialize correctly instead of racing. The cash side of a buy order
     169(`users.reserved_balance`), `orders.filled_quantity` and `order_events` were
     170added in P7; see
     171[AdvancedDatabaseDevelopment](../P7-AdvancedDatabaseDevelopment/AdvancedDatabaseDevelopment.md).
    112172
    113173## DDL script
    114174
    115 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.
     175The script that creates the 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.
    116176
    117177The script creates:
    118 - 10 tables with check constraints, primary keys, foreign keys and unique constraints.
    119 - 5 performance indexes.
     178- 10 of the 11 tables, with check constraints, primary keys, foreign keys and unique constraints.
     179- 8 performance indexes.
    120180- 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`).
     181
     182The 11th table, `order_events`, is created by
     183[`../server/db/advanced_db.sql`](../../server/db/advanced_db.sql) together with
     184the P7 triggers that fill it. `./eduberza -init` runs both scripts in that
     185order, so a freshly initialised database always has all 11 tables and all 15
     186foreign keys.
    121187
    122188## DML script (sample data)
    … …  
    132198## Relational diagram
    133199
    134 ![relational_schema](relational_schema.jpg)
    135 
    136 Generated in **Pgadmin** from the **live** `project` schema, in crow's-foot
    137 notation — not drawn by hand, so it is evidence that the deployed database
    138 actually matches the design described above. Each box is a table with its
    139 columns and declared types; key icons mark primary keys and the arrowed lines
    140 are the 12 declared foreign keys.
     200![relational_diagram_v4](relational_diagram_v4.png)
     201
     202Generated in **DBeaver** from the **live** `project` schema (after
     203`./eduberza -init`), not drawn by hand, so it shows what the deployed database
     204actually contains. Each box is a table with its columns; the key icon marks the
     205primary key, and the lines are the 15 declared foreign keys. The two foreign keys from
     206`market_trades` to `orders` (`buy_order_id`, `sell_order_id`) connect the same
     207two boxes, so DBeaver draws them on top of each other as one line.
     208
     209The tables are arranged in the **same positions** as the entity sets in
     210`ERModel_v05.png`, so the two can be compared directly:
     211
     212- every rectangle of the ER diagram is one table in the same place;
     213- every diamond of the ER diagram is one foreign-key line between the same two
     214  boxes. The dot is on the referencing ("N") table, next to the foreign-key
     215  column;
     216- a double (total) line in the ER diagram is a `NOT NULL` foreign key, drawn by
     217  DBeaver as a solid line. The three single lines on the N side (`Settles`,
     218  `FillsBuy`, `FillsSell`) are the three nullable foreign keys, which DBeaver
     219  draws dashed, with a hollow diamond on the `orders` side. The table under
     220  [Transformation method used](#transformation-method-used) lists all 15.
     221
     222Earlier images (`relational_schema.jpg`, `relational_schema_v2.png`,
     223`relational_schema_v3.png`) were exported from pgAdmin, with a different layout
     224and from an older schema. They are kept only as history.
    141225
    142226### How to regenerate it
    143227
    144 **With pgAdmin 4**, if DBeaver is unavailable — it reads the live schema the same
    145 way, so the result is equivalent in substance:
    146 
    147 1. Connect to the project database.
    148 2. Right-click the database → **ERD For Database** (or open a blank ERD and drag
    149    the `project` tables in).
    150 3. Arrange the tables to mirror `ERModel_v03.png`.
    151 4. **Download image** → PNG, then convert:
    152    `convert relational_schema.png relational_schema.jpg`
    153 
     2281. Initialise the database: `./eduberza -init` (runs `schema_creation.sql` and
     229   `advanced_db.sql`, so `order_events` is included).
     2302. In DBeaver, connect to the project database and expand
     231   *Schemas → project → Tables*.
     2323. Select all 11 tables → right-click → **View Diagram** (or create a new ER
     233   diagram and drag the tables in).
     2344. Drag each table to the position of its entity set in `ERModel_v05.png`:
     235
     236   ```
     237            column 1          column 2        column 3        column 4
     238   row 1    watchlist_items   crypto          markets         market_candles
     239   row 2    watchlists        holdings        .               market_trades
     240   row 3    users             .               orders          .
     241   row 4    .                 transactions    order_events    .
     242   ```
     243
     244   Leave the empty cells (`.`) empty. They are where the relationship
     245   diamonds are in the ER diagram, so the foreign-key lines will run through
     246   the same gaps.
     247
     2485. Right-click the canvas → **Export diagram** → PNG, saved as
     249   `relational_diagram_v4.png` in this folder.

  • docs/P2-RelationalDesign/RelationalDesignAIUsage.md

    r0cee8ec r1549dae  
    1212### Diagram
    1313
    14 The student produces `relational_schema.jpg` in DBeaver from the live `project` schema; see [RelationalDesign](RelationalDesign.md) for instructions.
     14`relational_diagram_v4.png` was exported by the student in DBeaver from the live `project` schema, with the tables in the same positions as the entity sets of `ERModel_v05.png`; see [RelationalDesign](RelationalDesign.md#how-to-regenerate-it) for instructions.
    1515
    1616### Results in details / description
    … …  
    100100`trade.go` alone to keep the reservation consistent — the same reasoning
    101101already applied to `avg_price NOT NULL` in session 2.
     102
     103### Session 4 — 2026-09-29 (Claude Opus 5.5): correction after review
     104
     105**Prompt (student, excerpt):** I pasted the reviewer's comments on P2:
     106
     107> the `contains` relationship from the model is implemented differently in the database
     108> the `holds` relationship from the model is implemented differently in the database
     109> there may be other inconsistencies that are difficult to spot; the Phase 2 diagram should
     110> have been created using DBeaver with a layout visually similar to the ERD
     111
     112> I suggest correcting only the Phase 1 model so that it matches Phase 2 [...] For Phase 2,
     113> simply update the image according to the requirements.
     114
     115**Response (AI, summarised):**
     116
     117- Explained the mismatch. In the model, `Holds` and `Contains` were M:N relationships, whose
     118  table key would be the pair `{user_id, crypto_id}` / `{watchlist_id, crypto_id}`. But
     119  `holdings` and `watchlist_items` have their own `id` primary key, which is how an entity
     120  set is implemented. Following the reviewer's advice, P1 was corrected (v05, entity sets
     121  `Holdings` and `WatchlistItems`), and the database was not changed.
     122- Found the other inconsistencies between this page and the live schema. The page was
     123  missing `users.reserved_balance`, `orders.filled_quantity`, the status
     124  `partially_filled`, `market_trades.buy_order_id` / `sell_order_id` and the whole
     125  `order_events` table. The "10 tables", "5 indexes" and "the one nullable foreign key"
     126  counts were also out of date (really 11 tables, 8 indexes in `schema_creation.sql`, and 3
     127  nullable foreign keys).
     128- Rewrote [RelationalDesign](RelationalDesign.md). Each relation is labelled with its entity
     129  set and each foreign key with its relationship, and the transformation is a table of all
     130  15 relationships → 15 foreign keys, with `NOT NULL` following participation.
     131- Wrote export instructions with a table grid that mirrors `ERModel_v05.png`.
     132
     133**What I decided:** to correct P1 instead of the database, as the reviewer suggested. I
     134exported `relational_diagram_v4.png` from DBeaver myself, with the tables arranged like the
     135ER diagram.

  • docs/P2-RelationalDesign/wiki/RelationalDesign.md

    r0cee8ec r1549dae  
    11= Relational Design =
    22
     3This page transforms [wiki:ERModel] '''v05''' into
     4relations. Every relation below corresponds to exactly one entity set of the
     5model, and every foreign key corresponds to exactly one relationship, so the
     6two diagrams can be compared box for box and line for line (see
     7Relational diagram).
     8
    39== Descriptive representation of the relational schema ==
    410
    5 Notation: '''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)`.
     11Notation: '''bold''' = primary key, ''italic'' = foreign key. After each foreign key
     12comes 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)`.
    916 * '''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.
     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)`.
    1822   * `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      [wiki:UseCase0005]. See the `Holds` section of [wiki: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)`.
     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)`.
    3738
    3839=== Transformation method used ===
    3940
    40 '''Partial transformation.''' Applied as follows:
    41 
    42  * Each of the 8 entity sets in [wiki: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.
     41'''Partial transformation.''' The model has 11 entity sets and 15 relationships.
     42Every relationship is binary and 1:N with no attributes of its own (the two M:N
     43relationships of earlier versions, `Holds` and `Contains`, were corrected into
     44the 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
     69Nothing in the schema comes from anywhere else. Every column is either an ER
     70attribute or the foreign key of one listed relationship.
    6171
    6272=== Normalisation ===
    6373
    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 
    74 All relations are in '''3NF''':
     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
     82All relations except `transactions` are in '''BCNF''', as P5 shows. `transactions`
     83is in 2NF but not in 3NF, because of the deliberately kept `user_id` (last
     84bullet):
    7585
    7686 * 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.
     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.
    9593
    9694=== Reservation and the order lifecycle ===
    … …  
    107105`SELECT … FOR UPDATE` locking that already protected `users.available_balance`
    108106on the buy path is what makes two concurrent sell orders against the same
    109 holding serialize correctly instead of racing.
     107holding serialize correctly instead of racing. The cash side of a buy order
     108(`users.reserved_balance`), `orders.filled_quantity` and `order_events` were
     109added in P7; see
     110[wiki:AdvancedDatabaseDevelopment].
    110111
    111112== DDL script ==
    112113
    113 The 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.
     114The 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.
    114115
    115116The script creates:
    116  * 10 tables with check constraints, primary keys, foreign keys and unique constraints.
    117  * 5 performance indexes.
     117 * 10 of the 11 tables, with check constraints, primary keys, foreign keys and unique constraints.
     118 * 8 performance indexes.
    118119 * 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
     121The 11th table, `order_events`, is created by
     122`../server/db/advanced_db.sql` together with
     123the P7 triggers that fill it. `./eduberza -init` runs both scripts in that
     124order, so a freshly initialised database always has all 11 tables and all 15
     125foreign keys.
    119126
    120127=== schema_creation.sql ===
    … …  
    150157    available_balance numeric(18,4)   NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
    151158    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),
    152162    created_at        timestamptz     NOT NULL DEFAULT now(),
    153163    updated_at        timestamptz
    … …  
    210220    side        varchar(4)     NOT NULL CHECK (side   IN ('buy', 'sell')),
    211221    type        varchar(20)    NOT NULL CHECK (type   IN ('market', 'limit')),
    212     status      varchar(20)    NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')),
     222    status      varchar(20)    NOT NULL CHECK (status IN ('open', 'partially_filled', 'executed', 'cancelled')),
    213223    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),
    214228    price       numeric(18,6),
    215229    placed_at   timestamptz    NOT NULL DEFAULT now(),
    … …  
    249263    quantity    numeric(20,6)  NOT NULL CHECK (quantity > 0),
    250264    side        varchar(4)     CHECK (side IN ('buy', 'sell')),
    251     source      varchar(50)    NOT NULL DEFAULT 'simulation'
     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)
    252270);
    253271
    254272CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
     273CREATE INDEX idx_market_trades_buy_order  ON project.market_trades(buy_order_id)  WHERE buy_order_id  IS NOT NULL;
     274CREATE INDEX idx_market_trades_sell_order ON project.market_trades(sell_order_id) WHERE sell_order_id IS NOT NULL;
    255275
    256276-- ============================================================================
    … …  
    325345}}}
    326346
     347=== order_events (from advanced_db.sql) ===
     348
     349{{{
     350CREATE 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
     361CREATE INDEX idx_order_events_order ON project.order_events(order_id, id);
     362}}}
     363
    327364== DML script (sample data) ==
    328365
    329 The 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:
     366The 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:
    330367 * 5 crypto assets (BTC, ETH, ADA, SOL, DOGE) and 5 USD-quoted markets.
    331368 * 3 sample users (`alice`, `bob`, `charlie`) with password `test123` (sha256 hex).
    … …  
    347384--
    348385-- 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
     392BEGIN;
    349393
    350394SET search_path TO project, public;
    … …  
    443487-- Shows a fully-filled market buy and its resulting holding & ledger entry.
    444488-- ============================================================================
    445 INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, price, placed_at, executed_at) VALUES
     489-- Imported as already completely filled (filled_quantity = quantity).
     490INSERT INTO project.orders (id, user_id, market_id, side, type, status, quantity, filled_quantity, price, placed_at, executed_at) VALUES
    446491    ('c1111111-1111-1111-1111-111111111111',
    447492     'b1111111-1111-1111-1111-111111111111',
    448493     'a2222222-2222-2222-2222-222222222222',
    449      'buy', 'market', 'executed', 0.5000, 3500.000000,
     494     'buy', 'market', 'executed', 0.5000, 0.5000, 3500.000000,
    450495     now() - interval '1 hour', now() - interval '1 hour');
    451496
    … …  
    457502INSERT INTO project.transactions (user_id, type, amount, currency, related_order, description) VALUES
    458503    ('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,
    459508        'Initial virtual deposit'),
    460509    ('b1111111-1111-1111-1111-111111111111', 'buy',      -1750.0000, 'USD',
    … …  
    482531    ('d2222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111'),
    483532    ('d2222222-2222-2222-2222-222222222222', '55555555-5555-5555-5555-555555555555');
     533
     534COMMIT;
    484535}}}
    485536
    486537== Relational diagram ==
    487538
    488 [[Image(relational_schema.jpg)]]
    489 
    490 Generated in '''Pgadmin''' from the '''live''' `project` schema, in crow's-foot
    491 notation — not drawn by hand, so it is evidence that the deployed database
    492 actually matches the design described above. Each box is a table with its
    493 columns and declared types; key icons mark primary keys and the arrowed lines
    494 are the 12 declared foreign keys.
     539[[Image(relational_diagram_v4.png, 800px)]]
     540
     541Generated in '''DBeaver''' from the '''live''' `project` schema (after
     542`./eduberza -init`), not drawn by hand, so it shows what the deployed database
     543actually contains. Each box is a table with its columns; the key icon marks the
     544primary 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
     546two boxes, so DBeaver draws them on top of each other as one line.
     547
     548The 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
     555Earlier images (`relational_schema.jpg`, `relational_schema_v2.png`,
     556`relational_schema_v3.png`) were exported from pgAdmin, with a different layout
     557and from an older schema. They are kept only as history.
    495558
    496559=== How to regenerate it ===
    497560
    498 '''With pgAdmin 4''', if DBeaver is unavailable — it reads the live schema the same
    499 way, so the result is equivalent in substance:
    500 
    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`
     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.

  • docs/P2-RelationalDesign/wiki/RelationalDesignAIUsage.md

    r0cee8ec r1549dae  
    1212=== Diagram ===
    1313
    14 The student produces `relational_schema.jpg` in DBeaver from the live `project` schema; see [wiki:RelationalDesign] for instructions.
     14`relational_diagram_v4.png` was exported by the student in DBeaver from the live `project` schema, with the tables in the same positions as the entity sets of `ERModel_v05.png`; see [wiki:RelationalDesign] (section "How to regenerate it") for instructions.
    1515
    1616=== Results in details / description ===
    … …  
    9696`trade.go` alone to keep the reservation consistent — the same reasoning
    9797already applied to `avg_price NOT NULL` in session 2.
     98
     99=== Session 4 — 2026-09-29 (Claude Opus 5.5): correction after review ===
     100
     101'''Prompt (student, excerpt):''' I pasted the reviewer's comments on P2:
     102
     103> the `contains` relationship from the model is implemented differently in the database
     104> the `holds` relationship from the model is implemented differently in the database
     105> there may be other inconsistencies that are difficult to spot; the Phase 2 diagram should
     106> have been created using DBeaver with a layout visually similar to the ERD
     107
     108> I suggest correcting only the Phase 1 model so that it matches Phase 2 [...] For Phase 2,
     109> simply update the image according to the requirements.
     110
     111'''Response (AI, summarised):'''
     112
     113 * Explained the mismatch. In the model, `Holds` and `Contains` were M:N relationships, whose table key would be the pair `{user_id, crypto_id}` / `{watchlist_id, crypto_id}`. But `holdings` and `watchlist_items` have their own `id` primary key, which is how an entity set is implemented. Following the reviewer's advice, P1 was corrected (v05, entity sets `Holdings` and `WatchlistItems`), and the database was not changed.
     114 * Found the other inconsistencies between this page and the live schema. The page was missing `users.reserved_balance`, `orders.filled_quantity`, the status `partially_filled`, `market_trades.buy_order_id` / `sell_order_id` and the whole `order_events` table. The "10 tables", "5 indexes" and "the one nullable foreign key" counts were also out of date (really 11 tables, 8 indexes in `schema_creation.sql`, and 3 nullable foreign keys).
     115 * Rewrote [wiki:RelationalDesign]. Each relation is labelled with its entity set and each foreign key with its relationship, and the transformation is a table of all 15 relationships → 15 foreign keys, with `NOT NULL` following participation.
     116 * Wrote export instructions with a table grid that mirrors `ERModel_v05.png`.
     117
     118'''What I decided:''' to correct P1 instead of the database, as the reviewer suggested. I
     119exported `relational_diagram_v4.png` from DBeaver myself, with the tables arranged like the
     120ER diagram.
Note: See TracChangeset for help on using the changeset viewer.