Changes between Version 2 and Version 3 of Normalization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • Normalization

    v2 v3  
    1 = Normalization
    2 
    3 This phase deliberately ignores the design from [ERModel](../P1-ConceptualModel/ERModel.md)
    4 (P1) and [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) (P2) as a starting
     1= Normalization =
     2
     3This phase deliberately ignores the design from [wiki:ERModel]
     4(P1) and [wiki:RelationalDesign] (P2) as a starting
    55point. Instead it starts over from a single flat relation containing every attribute of the
    66model, derives the functional dependencies that hold on it, and decomposes it formally,
    7 step by step, using only Armstrong's axioms and the standard normal-form definitions. The
    8 [final section](#final-result-and-discussion) compares what falls out of that process with
     7step by step. The
     8final section compares what falls out of that process with
    99the P2 design.
    1010
    11 == De-normalized database form
    12 
    13 === Building one relation out of the whole model
     11== De-normalized database form ==
     12
     13=== Building one relation out of the whole model ===
    1414
    1515The ER model has ten entity/relationship sets carrying attributes (see
    16 [ERModel](../P1-ConceptualModel/ERModel.md)):
    17 -`Users`
    18 -`Cryptos`
    19 -`Markets`
    20 -`Orders`
    21 -`Transactions`
    22 -`MarketTrades`
    23 -`MarketCandles`
    24 -`Watchlists`
    25 And the two attributed relationships `Holds` and `Contains`. Eight more relationships (`QuotedOn`, `PlacedOn`,
     16[wiki:ERModel]): `Users`, `Cryptos`, `Markets`, `Orders`,
     17`Transactions`, `MarketTrades`, `MarketCandles`, `Watchlists`, and the two attributed
     18relationships `Holds` and `Contains`. Eight more relationships (`QuotedOn`, `PlacedOn`,
    2619`Places`, `Records`, `Settles`, `Fills`, `Aggregates`, `Owns`) carry no attributes of their
    2720own — in Chen notation they need none, because the diagram expresses the link itself as a
    … …  
    4033not contain duplicate names.
    4134
    42 | Prefix | Origin (P1 entity / relationship) | Attributes |
    43 |---|---|---|
    44 | `U_`  | Users        | `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` |
    45 | `C_`  | Cryptos      | `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT` |
    46 | `M_`  | Markets (+ `QuotedOn`) | `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` |
    47 | `H_`  | `Holds` (+ surrogate key) | `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` |
    48 | `O_`  | Orders (+ `PlacedOn`, `Places`) | `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` |
    49 | `T_`  | Transactions (+ `Records`, `Settles`) | `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` |
    50 | `MT_` | MarketTrades (+ `Fills`) | `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` |
    51 | `MC_` | MarketCandles (+ `Aggregates`) | `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` |
    52 | `W_`  | Watchlists (+ `Owns`) | `W_ID, W_USER_ID, W_NAME, W_CREATED_AT` |
    53 | `WI_` | `Contains` (+ surrogate key) | `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` |
     35||= Prefix =||= Origin (P1 entity / relationship) =||= Attributes =||
     36|| `U_` || Users || `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` ||
     37|| `C_` || Cryptos || `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT` ||
     38|| `M_` || Markets (+ `QuotedOn`) || `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` ||
     39|| `H_` || `Holds` (+ surrogate key) || `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` ||
     40|| `O_` || Orders (+ `PlacedOn`, `Places`) || `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` ||
     41|| `T_` || Transactions (+ `Records`, `Settles`) || `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` ||
     42|| `MT_` || !MarketTrades (+ `Fills`) || `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` ||
     43|| `MC_` || !MarketCandles (+ `Aggregates`) || `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` ||
     44|| `W_` || Watchlists (+ `Owns`) || `W_ID, W_USER_ID, W_NAME, W_CREATED_AT` ||
     45|| `WI_` || `Contains` (+ surrogate key) || `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` ||
    5446
    5547`H_ID` and `WI_ID` exist for the same reason they exist in P2: `Holds` and `Contains` are M:N
    5648relationships with their own attributes, and giving each its own surrogate key (rather than
    5749relying solely on the `{user,crypto}` / `{watchlist,crypto}` pair) is the same design choice
    58 already justified in [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md#descriptive-representation-of-the-relational-schema).
    59 
    60 This gives **one relation, `R_EDUBERZA`, of 68 attributes:**
    61 
    62 ```
     50already justified in [wiki:RelationalDesign] (section "Descriptive representation of the relational schema").
     51
     52This gives '''one relation, `R_EDUBERZA`, of 68 attributes:'''
     53
     54{{{
    6355R_EDUBERZA(
    6456  U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE,
    … …  
    7870  WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT
    7971)
    80 ```
     72}}}
    8173
    8274Every attribute is single-valued and atomic (a balance, a timestamp, a symbol, an amount —
    … …  
    8577checks.
    8678
    87 ## Functional dependencies
    88 
    89 ### Canonical cover
     79== Functional dependencies ==
     80
     81=== Canonical cover ===
    9082
    9183Read directly off the model: each entity's/relationship's own key determines its own
    … …  
    9486side of more than one dependency, which is what "canonical cover" requires.
    9587
    96 | # | Functional dependency | Source |
    97 |---|---|---|
    98 | FD1 | `U_ID → U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | Users |
    99 | FD2 | `U_USERNAME → U_ID` | Users (`UNIQUE(username)`) |
    100 | FD3 | `U_EMAIL → U_ID` | Users (`UNIQUE(email)`) |
    101 | FD4 | `C_ID → C_SYMBOL, C_NAME, C_CREATED_AT` | Cryptos |
    102 | FD5 | `C_SYMBOL → C_ID` | Cryptos (`UNIQUE(symbol)`) |
    103 | FD6 | `M_ID → M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` | Markets |
    104 | FD7 | `M_CRYPTO_ID, M_QUOTE_CURRENCY → M_ID` | Markets (`UNIQUE(crypto_id, quote_currency)`) |
    105 | FD8 | `H_ID → H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` | Holds |
    106 | FD9 | `H_USER_ID, H_CRYPTO_ID → H_ID` | Holds (`UNIQUE(user_id, crypto_id)`) |
    107 | FD10 | `O_ID → O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` | Orders |
    108 | FD11 | `T_ID → T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` | Transactions |
    109 | FD12 | `MT_ID → MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` | MarketTrades |
    110 | FD13 | `MC_ID → MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` | MarketCandles |
    111 | FD14 | `MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME → MC_ID` | MarketCandles (`UNIQUE(market_id, timeframe, candle_time)`) |
    112 | FD15 | `W_ID → W_USER_ID, W_NAME, W_CREATED_AT` | Watchlists |
    113 | FD16 | `WI_ID → WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` | Contains |
    114 | FD17 | `WI_WATCHLIST_ID, WI_CRYPTO_ID → WI_ID` | Contains (`UNIQUE(watchlist_id, crypto_id)`) |
    115 
    116 **Minimality, checked by example (Markets):** could FD7 drop an attribute from its left side?
     88||= # =||= Functional dependency =||= Source =||
     89|| FD1 || `U_ID → U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` || Users ||
     90|| FD2 || `U_USERNAME → U_ID` || Users (`UNIQUE(username)`) ||
     91|| FD3 || `U_EMAIL → U_ID` || Users (`UNIQUE(email)`) ||
     92|| FD4 || `C_ID → C_SYMBOL, C_NAME, C_CREATED_AT` || Cryptos ||
     93|| FD5 || `C_SYMBOL → C_ID` || Cryptos (`UNIQUE(symbol)`) ||
     94|| FD6 || `M_ID → M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` || Markets ||
     95|| FD7 || `M_CRYPTO_ID, M_QUOTE_CURRENCY → M_ID` || Markets (`UNIQUE(crypto_id, quote_currency)`) ||
     96|| FD8 || `H_ID → H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` || Holds ||
     97|| FD9 || `H_USER_ID, H_CRYPTO_ID → H_ID` || Holds (`UNIQUE(user_id, crypto_id)`) ||
     98|| FD10 || `O_ID → O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` || Orders ||
     99|| FD11 || `T_ID → T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` || Transactions ||
     100|| FD12 || `MT_ID → MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` || !MarketTrades ||
     101|| FD13 || `MC_ID → MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` || !MarketCandles ||
     102|| FD14 || `MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME → MC_ID` || !MarketCandles (`UNIQUE(market_id, timeframe, candle_time)`) ||
     103|| FD15 || `W_ID → W_USER_ID, W_NAME, W_CREATED_AT` || Watchlists ||
     104|| FD16 || `WI_ID → WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` || Contains ||
     105|| FD17 || `WI_WATCHLIST_ID, WI_CRYPTO_ID → WI_ID` || Contains (`UNIQUE(watchlist_id, crypto_id)`) ||
     106
     107'''Minimality, checked by example (Markets):''' could FD7 drop an attribute from its left side?
    117108`M_CRYPTO_ID` alone does not determine `M_ID` — many markets can reference the same crypto in
    118109different quote currencies (that is the entire point of the market entity), so two rows can
    … …  
    121112same check applies to FD9, FD14 and FD17, whose composite left sides come directly from the
    122113`UNIQUE` constraints already justified per-relation in
    123 [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md); none of those constraints
     114[wiki:RelationalDesign]; none of those constraints
    124115holds on a proper subset of its columns either.
    125116
    126 **No redundant dependency:** each of FD1–FD17 has a right side that is not implied by any
     117'''No redundant dependency:''' each of FD1–FD17 has a right side that is not implied by any
    127118other dependency in the set — for instance, nothing outside FD1 mentions `U_AVAILABLE_BALANCE`,
    128119so FD1 cannot be derived from the rest and cannot be dropped. This set is the canonical cover.
    129120
    130 ### Dependencies carried by foreign keys
     121=== Dependencies carried by foreign keys ===
    131122
    132123Six attributes above are foreign keys: `M_CRYPTO_ID`, `H_USER_ID`, `H_CRYPTO_ID`,
    … …  
    136127referenced key also holds, by substitution, on the referencing attribute:
    137128
    138 | Foreign key | References | Therefore also determines |
    139 |---|---|---|
    140 | `M_CRYPTO_ID` | `C_ID` | `C_SYMBOL, C_NAME, C_CREATED_AT` |
    141 | `H_USER_ID` | `U_ID` | all of `U_*` |
    142 | `H_CRYPTO_ID` | `C_ID` | all of `C_*` |
    143 | `O_USER_ID` | `U_ID` | all of `U_*` |
    144 | `O_MARKET_ID` | `M_ID` | all of `M_*`, and transitively all of `C_*` |
    145 | `T_USER_ID` | `U_ID` | all of `U_*` |
    146 | `T_RELATED_ORDER` | `O_ID` | all of `O_*`, and transitively `U_*`, `M_*`, `C_*` (when not null) |
    147 | `MT_MARKET_ID` | `M_ID` | all of `M_*`, transitively `C_*` |
    148 | `MC_MARKET_ID` | `M_ID` | all of `M_*`, transitively `C_*` |
    149 | `W_USER_ID` | `U_ID` | all of `U_*` |
    150 | `WI_WATCHLIST_ID` | `W_ID` | all of `W_*`, transitively `U_*` |
    151 | `WI_CRYPTO_ID` | `C_ID` | all of `C_*` |
    152 
    153 None of these is added to the canonical cover — each is *derivable* from FD1–FD17 by
     129||= Foreign key =||= References =||= Therefore also determines =||
     130|| `M_CRYPTO_ID` || `C_ID` || `C_SYMBOL, C_NAME, C_CREATED_AT` ||
     131|| `H_USER_ID` || `U_ID` || all of `U_*` ||
     132|| `H_CRYPTO_ID` || `C_ID` || all of `C_*` ||
     133|| `O_USER_ID` || `U_ID` || all of `U_*` ||
     134|| `O_MARKET_ID` || `M_ID` || all of `M_*`, and transitively all of `C_*` ||
     135|| `T_USER_ID` || `U_ID` || all of `U_*` ||
     136|| `T_RELATED_ORDER` || `O_ID` || all of `O_*`, and transitively `U_*`, `M_*`, `C_*` (when not null) ||
     137|| `MT_MARKET_ID` || `M_ID` || all of `M_*`, transitively `C_*` ||
     138|| `MC_MARKET_ID` || `M_ID` || all of `M_*`, transitively `C_*` ||
     139|| `W_USER_ID` || `U_ID` || all of `U_*` ||
     140|| `WI_WATCHLIST_ID` || `W_ID` || all of `W_*`, transitively `U_*` ||
     141|| `WI_CRYPTO_ID` || `C_ID` || all of `C_*` ||
     142
     143None of these is added to the canonical cover — each is ''derivable'' from FD1–FD17 by
    154144transitivity plus the foreign-key identity, which is exactly why a canonical cover excludes
    155145them. They matter anyway: they are precisely the transitive dependencies the 3NF check below
    156146has to rule out.
    157147
    158 ## Candidate keys and primary key
     148== Candidate keys and primary key ==
    159149
    160150`Orders`, `Transactions`, `MarketTrades`, `MarketCandles`, `Holds`, `Watchlists` and
    … …  
    167157That means no proper subset of attributes can functionally determine all 68 attributes of
    168158`R_EDUBERZA`: the only way to pin down a `H_*` value, an `O_*` value, a `T_*` value, an
    169 `MT_*` value, an `MC_*` value, a `W_*` value *and* a `WI_*` value at once is to state one
     159`MT_*` value, an `MC_*` value, a `W_*` value ''and'' a `WI_*` value at once is to state one
    170160identifying attribute from each cluster explicitly.
    171161
    172 **Chosen primary key** (closure shown below):
    173 
    174 ```
     162'''Chosen primary key''' (closure shown below):
     163
     164{{{
    175165{ U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID }
    176 ```
    177 
    178 **Closure check**, applying FD1–FD17 in turn to this set:
    179 
    180 | Step | Attributes added | Dependency used |
    181 |---|---|---|
    182 | start | `U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID` | — |
    183 | 1 | `U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | FD1 (`U_ID → …`) |
    184 | 2 | `C_SYMBOL, C_NAME, C_CREATED_AT` | FD4 |
    185 | 3 | `M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` | FD6 |
    186 | 4 | `H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` | FD8 |
    187 | 5 | `O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` | FD10 |
    188 | 6 | `T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` | FD11 |
    189 | 7 | `MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` | FD12 |
    190 | 8 | `MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` | FD13 |
    191 | 9 | `W_USER_ID, W_NAME, W_CREATED_AT` | FD15 |
    192 | 10 | `WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` | FD16 |
     166}}}
     167
     168'''Closure check''', applying FD1–FD17 in turn to this set:
     169
     170||= Step =||= Attributes added =||= Dependency used =||
     171|| start || `U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID` || — ||
     172|| 1 || `U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` || FD1 (`U_ID → …`) ||
     173|| 2 || `C_SYMBOL, C_NAME, C_CREATED_AT` || FD4 ||
     174|| 3 || `M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` || FD6 ||
     175|| 4 || `H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` || FD8 ||
     176|| 5 || `O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` || FD10 ||
     177|| 6 || `T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` || FD11 ||
     178|| 7 || `MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` || FD12 ||
     179|| 8 || `MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` || FD13 ||
     180|| 9 || `W_USER_ID, W_NAME, W_CREATED_AT` || FD15 ||
     181|| 10 || `WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` || FD16 ||
    193182
    194183The closure now contains all 68 attributes, so the set is a superkey; removing any one of its
    … …  
    197186key.
    198187
    199 **It is not the only one.** Any attribute that is itself a determinant of a whole cluster can
     188'''It is not the only one.''' Any attribute that is itself a determinant of a whole cluster can
    200189stand in for that cluster's id — `U_USERNAME` or `U_EMAIL` for `U_ID` (FD2/FD3), `C_SYMBOL`
    201 for `C_ID` (FD5), `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` for `M_ID` (FD7), `{H_USER_ID,
    202 H_CRYPTO_ID}` for `H_ID` (FD9), `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` for `MC_ID`
     190for `C_ID` (FD5), `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` for `M_ID` (FD7), `{H_USER_ID, H_CRYPTO_ID}` for `H_ID` (FD9), `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` for `MC_ID`
    203191(FD14), `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` for `WI_ID` (FD17) — giving 3 × 2 × 2 × 2 × 1 × 1 ×
    2041921 × 2 × 1 × 2 = 96 candidate keys in total. The all-surrogate-id combination above is chosen
    205 as **primary key** for the same reason `id` was chosen over `username`/`email`/`symbol`/etc.
    206 per entity in [ERModel](../P1-ConceptualModel/ERModel.md): it is opaque, and none of its parts
     193as '''primary key''' for the same reason `id` was chosen over `username`/`email`/`symbol`/etc.
     194per entity in [wiki:ERModel]: it is opaque, and none of its parts
    207195are things a user would ever legitimately change.
    208196
    209 **Normal form of `R_EDUBERZA` before decomposition:** 1NF only, and barely that — see 2NF
     197'''Normal form of `R_EDUBERZA` before decomposition:''' 1NF only, and barely that — see 2NF
    210198below. It cannot be in 2NF, 3NF or BCNF, since each of those requires 2NF as a precondition.
    211199
    212 ## 1NF decomposition
     200== 1NF decomposition ==
    213201
    214202No decomposition happens at this step. 1NF requires atomic, single-valued attributes and no
    215203repeating groups; `R_EDUBERZA` was built that way from the start (every column above is a
    216204single scalar), so the relation already satisfies 1NF as written in
    217 [De-normalized database form](#de-normalized-database-form). The real work starts at 2NF.
    218 
    219 ## 2NF decomposition
    220 
    221 **Relation analyzed:** `R_EDUBERZA`, all 68 attributes, primary key
     205De-normalized database form. The real work starts at 2NF.
     206
     207== 2NF decomposition ==
     208
     209'''Relation analyzed:''' `R_EDUBERZA`, all 68 attributes, primary key
    222210`{U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID}` (10 attributes), FD1–FD17
    223211in force.
    224212
    225 **Current normal form:** 1NF only (previous section).
    226 
    227 **Violations:** 2NF forbids a non-prime attribute from depending on *part* of a candidate
     213'''Current normal form:''' 1NF only (previous section).
     214
     215'''Violations:''' 2NF forbids a non-prime attribute from depending on ''part'' of a candidate
    228216key. Every single functional dependency in the canonical cover (FD1–FD17) has a left side
    229 that is a **proper subset** of the ten-attribute primary key — `U_ID` alone, `C_ID` alone, …,
     217that is a '''proper subset''' of the ten-attribute primary key — `U_ID` alone, `C_ID` alone, …,
    230218down to the two-attribute `{WI_WATCHLIST_ID, WI_CRYPTO_ID}`. There is no non-prime attribute
    231219in `R_EDUBERZA` that depends on the whole ten-attribute key and nothing smaller. In other
    232 words, *every* non-prime attribute violates 2NF at once — the violation is not a handful of
     220words, ''every'' non-prime attribute violates 2NF at once — the violation is not a handful of
    233221stray columns to peel off, it is the entire relation, because gluing ten independent record
    234222types together under one artificial composite key was never going to satisfy 2NF to begin
    235223with.
    236224
    237 **Decomposition.** This uses 3NF/BCNF **synthesis** (Bernstein's algorithm) rather than the
     225'''Decomposition.''' This uses 3NF/BCNF '''synthesis''' (Bernstein's algorithm) rather than the
    238226binary decomposition algorithm: since the canonical cover is already in hand (as the phase
    239227instructions recommend building first), synthesis creates one relation per left-hand side in
    … …  
    241229in two repeatedly. Grouping FD1–FD17 by determinant produces ten relations:
    242230
    243 | New relation | Attributes | Key(s) | Source FDs |
    244 |---|---|---|---|
    245 | `R_USERS` | `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` | `U_ID`, `U_USERNAME`, `U_EMAIL` | FD1, FD2, FD3 |
    246 | `R_CRYPTO` | `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT` | `C_ID`, `C_SYMBOL` | FD4, FD5 |
    247 | `R_MARKETS` | `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` | `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` | FD6, FD7 |
    248 | `R_HOLDINGS` | `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` | `H_ID`, `{H_USER_ID, H_CRYPTO_ID}` | FD8, FD9 |
    249 | `R_ORDERS` | `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` | `O_ID` | FD10 |
    250 | `R_TRANSACTIONS` | `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` | `T_ID` | FD11 |
    251 | `R_MARKET_TRADES` | `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` | `MT_ID` | FD12 |
    252 | `R_MARKET_CANDLES` | `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` | `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` | FD13, FD14 |
    253 | `R_WATCHLISTS` | `W_ID, W_USER_ID, W_NAME, W_CREATED_AT` | `W_ID` | FD15 |
    254 | `R_WATCHLIST_ITEMS` | `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` | `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` | FD16, FD17 |
    255 
    256 Every one of these ten relations now has **all** of its non-prime attributes depending on its
    257 **whole** key (in every case there is only one non-composite or one designated key doing the
     231||= New relation =||= Attributes =||= Key(s) =||= Source FDs =||
     232|| `R_USERS` || `U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT` || `U_ID`, `U_USERNAME`, `U_EMAIL` || FD1, FD2, FD3 ||
     233|| `R_CRYPTO` || `C_ID, C_SYMBOL, C_NAME, C_CREATED_AT` || `C_ID`, `C_SYMBOL` || FD4, FD5 ||
     234|| `R_MARKETS` || `M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT` || `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` || FD6, FD7 ||
     235|| `R_HOLDINGS` || `H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT` || `H_ID`, `{H_USER_ID, H_CRYPTO_ID}` || FD8, FD9 ||
     236|| `R_ORDERS` || `O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT` || `O_ID` || FD10 ||
     237|| `R_TRANSACTIONS` || `T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION` || `T_ID` || FD11 ||
     238|| `R_MARKET_TRADES` || `MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE` || `MT_ID` || FD12 ||
     239|| `R_MARKET_CANDLES` || `MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME` || `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` || FD13, FD14 ||
     240|| `R_WATCHLISTS` || `W_ID, W_USER_ID, W_NAME, W_CREATED_AT` || `W_ID` || FD15 ||
     241|| `R_WATCHLIST_ITEMS` || `WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT` || `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` || FD16, FD17 ||
     242
     243Every one of these ten relations now has '''all''' of its non-prime attributes depending on its
     244'''whole''' key (in every case there is only one non-composite or one designated key doing the
    258245determining, so 2NF holds trivially in each).
    259246
    260 **Dependency preservation.** FD1–FD17 is the canonical cover of `R_EDUBERZA`. Each FD's
     247'''Dependency preservation.''' FD1–FD17 is the canonical cover of `R_EDUBERZA`. Each FD's
    261248determinant and every one of its dependent attributes land inside exactly one of the ten new
    262249relations (see the "Source FDs" column above — no FD is split across two relations). The
    … …  
    264251again: nothing was lost.
    265252
    266 **Lossless join.** For every pair (referencing relation, referenced relation) connected by a
     253'''Lossless join.''' For every pair (referencing relation, referenced relation) connected by a
    267254foreign key — `R_MARKETS.M_CRYPTO_ID → R_CRYPTO.C_ID`, `R_HOLDINGS.H_USER_ID → R_USERS.U_ID` /
    268255`R_HOLDINGS.H_CRYPTO_ID → R_CRYPTO.C_ID`, `R_ORDERS.O_USER_ID → R_USERS.U_ID` /
    269256`R_ORDERS.O_MARKET_ID → R_MARKETS.M_ID`, `R_TRANSACTIONS.T_USER_ID → R_USERS.U_ID` /
    270 `R_TRANSACTIONS.T_RELATED_ORDER → R_ORDERS.O_ID`, `R_MARKET_TRADES.MT_MARKET_ID →
    271 R_MARKETS.M_ID`, `R_MARKET_CANDLES.MC_MARKET_ID → R_MARKETS.M_ID`,
    272 `R_WATCHLISTS.W_USER_ID → R_USERS.U_ID`, `R_WATCHLIST_ITEMS.WI_WATCHLIST_ID →
    273 R_WATCHLISTS.W_ID` / `R_WATCHLIST_ITEMS.WI_CRYPTO_ID → R_CRYPTO.C_ID` — the join attribute on
     257`R_TRANSACTIONS.T_RELATED_ORDER → R_ORDERS.O_ID`, `R_MARKET_TRADES.MT_MARKET_ID → R_MARKETS.M_ID`, `R_MARKET_CANDLES.MC_MARKET_ID → R_MARKETS.M_ID`,
     258`R_WATCHLISTS.W_USER_ID → R_USERS.U_ID`, `R_WATCHLIST_ITEMS.WI_WATCHLIST_ID → R_WATCHLISTS.W_ID` / `R_WATCHLIST_ITEMS.WI_CRYPTO_ID → R_CRYPTO.C_ID` — the join attribute on
    274259the "one" side is that relation's own primary key (`U_ID`, `C_ID`, `M_ID`, `O_ID`, `W_ID`).
    275260A join on a foreign key equated to the primary key it references is the textbook sufficient
    … …  
    278263spurious rows and none missing.
    279264
    280 ## 3NF decomposition
    281 
    282 **Relations analyzed:** each of the ten relations produced above, individually.
    283 
    284 For each relation, 3NF asks whether any non-prime attribute is *transitively* dependent on a
     265== 3NF decomposition ==
     266
     267'''Relations analyzed:''' each of the ten relations produced above, individually.
     268
     269For each relation, 3NF asks whether any non-prime attribute is ''transitively'' dependent on a
    285270key — i.e. determined by another non-prime attribute rather than directly by the key. This is
    286271exactly where the foreign-key-carried dependencies from
    287 [Dependencies carried by foreign keys](#dependencies-carried-by-foreign-keys) have to be
     272Dependencies carried by foreign keys have to be
    288273checked, because that table is precisely the list of "dependency that would cause a problem at
    289274the next higher normal form" the phase template asks for.
    290275
    291 **Worked example — `R_MARKETS`.** Its key `M_ID` determines `M_CRYPTO_ID`, and
     276'''Worked example — `R_MARKETS`.''' Its key `M_ID` determines `M_CRYPTO_ID`, and
    292277`M_CRYPTO_ID → C_SYMBOL, C_NAME, C_CREATED_AT` also holds (`M_CRYPTO_ID` draws its values from
    293278`C_ID`'s domain). If `C_SYMBOL`, `C_NAME` and `C_CREATED_AT` were still columns of
    … …  
    296281directly by `C_ID` (FD4), because FD4 — not the derived `M_CRYPTO_ID → C_SYMBOL` — is what the
    297282canonical cover actually contains. `R_MARKETS` itself has no attribute that determines another
    298 non-prime attribute of `R_MARKETS`; the transitive dependency is real, but it points *out* of
     283non-prime attribute of `R_MARKETS`; the transitive dependency is real, but it points ''out'' of
    299284the relation, not within it.
    300285
    301286The same reasoning applies to every other foreign key in the list: `H_USER_ID`/`H_CRYPTO_ID`,
    302287`O_USER_ID`/`O_MARKET_ID`, `T_USER_ID`/`T_RELATED_ORDER`, `MT_MARKET_ID`, `MC_MARKET_ID`,
    303 `W_USER_ID`, `WI_WATCHLIST_ID`/`WI_CRYPTO_ID` are all foreign keys sitting *alongside* a
     288`W_USER_ID`, `WI_WATCHLIST_ID`/`WI_CRYPTO_ID` are all foreign keys sitting ''alongside'' a
    304289non-key attribute set that depends only on their own relation's key, never on the foreign key
    305290itself. None of `R_USERS`, `R_CRYPTO`, `R_HOLDINGS`, `R_ORDERS`, `R_TRANSACTIONS`,
    306291`R_MARKET_TRADES`, `R_MARKET_CANDLES`, `R_WATCHLISTS`, `R_WATCHLIST_ITEMS` has a non-prime
    307 attribute that another non-prime attribute of the *same* relation determines.
    308 
    309 **Conclusion:** synthesising directly from the canonical cover in the 2NF step already
     292attribute that another non-prime attribute of the ''same'' relation determines.
     293
     294'''Conclusion:''' synthesising directly from the canonical cover in the 2NF step already
    310295avoided every transitive dependency — there is nothing left to decompose for 3NF. All ten
    311296relations from the previous section satisfy 3NF unchanged.
    312297
    313 ## BCNF if possible
    314 
    315 **Relations analyzed:** the same ten relations, checked against the stricter BCNF rule: every
     298== BCNF if possible ==
     299
     300'''Relations analyzed:''' the same ten relations, checked against the stricter BCNF rule: every
    316301determinant of every functional dependency that holds on the relation must be a candidate key
    317302of that relation (3NF allows an exception when the dependent side is prime; BCNF does not).
    318303
    319 | Relation | Functional dependencies in force | Determinant | Is it a candidate key? |
    320 |---|---|---|---|
    321 | `R_USERS` | FD1, FD2, FD3 | `U_ID`, `U_USERNAME`, `U_EMAIL` | Yes — all three are candidate keys |
    322 | `R_CRYPTO` | FD4, FD5 | `C_ID`, `C_SYMBOL` | Yes — both candidate keys |
    323 | `R_MARKETS` | FD6, FD7 | `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` | Yes — both candidate keys |
    324 | `R_HOLDINGS` | FD8, FD9 | `H_ID`, `{H_USER_ID, H_CRYPTO_ID}` | Yes — both candidate keys |
    325 | `R_ORDERS` | FD10 | `O_ID` | Yes — the only candidate key |
    326 | `R_TRANSACTIONS` | FD11 | `T_ID` | Yes — the only candidate key |
    327 | `R_MARKET_TRADES` | FD12 | `MT_ID` | Yes — the only candidate key |
    328 | `R_MARKET_CANDLES` | FD13, FD14 | `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` | Yes — both candidate keys |
    329 | `R_WATCHLISTS` | FD15 | `W_ID` | Yes — the only candidate key |
    330 | `R_WATCHLIST_ITEMS` | FD16, FD17 | `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` | Yes — both candidate keys |
    331 
    332 Every determinant in every relation is one of that relation's own candidate keys. **All ten
    333 relations are already in BCNF** — the highest of the four normal forms this phase asks for,
     304||= Relation =||= Functional dependencies in force =||= Determinant =||= Is it a candidate key? =||
     305|| `R_USERS` || FD1, FD2, FD3 || `U_ID`, `U_USERNAME`, `U_EMAIL` || Yes — all three are candidate keys ||
     306|| `R_CRYPTO` || FD4, FD5 || `C_ID`, `C_SYMBOL` || Yes — both candidate keys ||
     307|| `R_MARKETS` || FD6, FD7 || `M_ID`, `{M_CRYPTO_ID, M_QUOTE_CURRENCY}` || Yes — both candidate keys ||
     308|| `R_HOLDINGS` || FD8, FD9 || `H_ID`, `{H_USER_ID, H_CRYPTO_ID}` || Yes — both candidate keys ||
     309|| `R_ORDERS` || FD10 || `O_ID` || Yes — the only candidate key ||
     310|| `R_TRANSACTIONS` || FD11 || `T_ID` || Yes — the only candidate key ||
     311|| `R_MARKET_TRADES` || FD12 || `MT_ID` || Yes — the only candidate key ||
     312|| `R_MARKET_CANDLES` || FD13, FD14 || `MC_ID`, `{MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME}` || Yes — both candidate keys ||
     313|| `R_WATCHLISTS` || FD15 || `W_ID` || Yes — the only candidate key ||
     314|| `R_WATCHLIST_ITEMS` || FD16, FD17 || `WI_ID`, `{WI_WATCHLIST_ID, WI_CRYPTO_ID}` || Yes — both candidate keys ||
     315
     316Every determinant in every relation is one of that relation's own candidate keys. '''All ten relations are already in BCNF''' — the highest of the four normal forms this phase asks for,
    334317reached in the same step that fixed 2NF. This is not a coincidence: it happens because the
    335318canonical cover already grouped each relation's own key directly against its own attributes
    … …  
    339322
    340323No further decomposition is possible or necessary; splitting any of the ten relations further
    341 would only separate attributes that already depend on the *whole* key of a BCNF relation,
     324would only separate attributes that already depend on the ''whole'' key of a BCNF relation,
    342325which cannot fix anything and only costs a join.
    343326
    344 ## Final result and discussion
    345 
    346 ### Normalized relational model
    347 
    348 ```
     327== Final result and discussion ==
     328
     329=== Normalized relational model ===
     330
     331{{{
    349332R_USERS          (U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH,
    350333                   U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT)
    … …  
    363346R_WATCHLISTS     (W_ID, W_USER_ID → R_USERS, W_NAME, W_CREATED_AT)
    364347R_WATCHLIST_ITEMS(WI_ID, WI_WATCHLIST_ID → R_WATCHLISTS, WI_CRYPTO_ID → R_CRYPTO, WI_ADDED_AT)
    365 ```
     348}}}
    366349
    367350Ten relations, every one in BCNF, connected by the eleven foreign keys spelled out above.
    368351
    369 ### Discussion
    370 
    371 **This is the P2 design.** Strip the `U_`/`C_`/`M_`/… prefixes back to plain column names and
    372 `R_USERS, R_CRYPTO, R_MARKETS, R_HOLDINGS, R_ORDERS, R_TRANSACTIONS, R_MARKET_TRADES,
    373 R_MARKET_CANDLES, R_WATCHLISTS, R_WATCHLIST_ITEMS` are, attribute for attribute and key for
    374 key, `users, crypto, markets, holdings, orders, transactions, market_trades, market_candles,
    375 watchlists, watchlist_items` from
    376 [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md). Every foreign key matches,
    377 every candidate key matches (including the less obvious composite ones — `{user_id,
    378 crypto_id}` on `holdings`, `{crypto_id, quote_currency}` on `markets`, `{market_id, timeframe,
    379 candle_time}` on `market_candles`), and the normal form matches (P2 already claimed 3NF; this
     352=== Discussion ===
     353
     354'''This is the P2 design.''' Strip the `U_`/`C_`/`M_`/… prefixes back to plain column names and
     355`R_USERS, R_CRYPTO, R_MARKETS, R_HOLDINGS, R_ORDERS, R_TRANSACTIONS, R_MARKET_TRADES, R_MARKET_CANDLES, R_WATCHLISTS, R_WATCHLIST_ITEMS` are, attribute for attribute and key for
     356key, `users, crypto, markets, holdings, orders, transactions, market_trades, market_candles, watchlists, watchlist_items` from
     357[wiki:RelationalDesign]. Every foreign key matches,
     358every candidate key matches (including the less obvious composite ones — `{user_id, crypto_id}` on `holdings`, `{crypto_id, quote_currency}` on `markets`, `{market_id, timeframe, candle_time}` on `market_candles`), and the normal form matches (P2 already claimed 3NF; this
    380359phase shows the stronger result that the design is actually in BCNF).
    381360
    … …  
    388367attributes and the functional dependencies they obey, and mechanically applying 2NF/3NF/BCNF
    389368synthesis. Landing on the same ten relations either means the P2 transformation rules are
    390 sound for this particular model (which they are, for exactly the reason [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md#normalisation)
     369sound for this particular model (which they are, for exactly the reason [wiki:RelationalDesign] (Normalisation section)
    391370already argued: single-column UUID primary keys everywhere rule out partial dependencies by
    392371construction, and no non-key attribute references another non-key attribute anywhere in the
    … …  
    395374is the only credible one.
    396375
    397 **The one substantive difference** is `holdings.avg_price`, which P2 documents as a
    398 *derived* attribute — the running weighted-average buy price, recomputable from the `buy` rows
     376'''The one substantive difference''' is `holdings.avg_price`, which P2 documents as a
     377''derived'' attribute — the running weighted-average buy price, recomputable from the `buy` rows
    399378in `transactions` — kept as a stored column anyway for read performance
    400 ([RelationalDesign](../P2-RelationalDesign/RelationalDesign.md#normalisation) calls this out
     379([wiki:RelationalDesign] (Normalisation section) calls this out
    401380explicitly as an accepted denormalisation). Nothing in this phase's functional-dependency
    402381analysis can see that `H_AVG_PRICE` is derivable from `T_*` rows rather than stored
    403382independently — FD8 (`H_ID → H_AVG_PRICE`) is a perfectly ordinary functional dependency
    404 either way, because *derivability from a different relation's rows* is a property of the data
     383either way, because ''derivability from a different relation's rows'' is a property of the data
    405384and the application logic that maintains it (see
    406 [UseCase0004](../P3-UseCaseModel/UseCase0004.md)'s `ON CONFLICT … DO UPDATE`), not something
     385[wiki:UseCase0004]'s `ON CONFLICT … DO UPDATE`), not something
    407386that shows up as a violation of any single-relation normal form. Formal normalization and "no
    408387column is a cached computation of other columns" are related but different concerns; this
    409388phase only checked the first one.
    410389
    411 **Which design is used going forward:** P2's, unchanged. Since the two designs coincide
     390'''Which design is used going forward:''' P2's, unchanged. Since the two designs coincide
    412391exactly, "restructuring the database objects" means confirming there is nothing to change
    413 rather than writing new DDL. [`server/db/schema_creation.sql`](../../server/db/schema_creation.sql)
     392rather than writing new DDL. `server/db/schema_creation.sql`
    414393already matches `R_USERS`…`R_WATCHLIST_ITEMS` column-for-column (including
    415394`holdings.reserved_quantity`, added between P2 and this phase — see
    416 [RelationalDesignAIUsage](../P2-RelationalDesign/RelationalDesignAIUsage.md#session-3--2026-09-16)
     395[wiki:RelationalDesignAIUsage] (section "Session 3 — 2026-09-16")
    417396— which is `H_RESERVED_QUANTITY` above, correctly grouped under `R_HOLDINGS`'s key alongside
    418397`H_QUANTITY` and not treated as needing a relation of its own). P4's prototype
    419398(`server/trade.go`, `server/portfolio.go`) keeps working against the same schema without
    420 change. [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) has been updated with a
     399change. [wiki:RelationalDesign] has been updated with a
    421400short note pointing here as the formal validation of its normal-form claim.
     401
     402The table definitions in `server/db/schema_creation.sql`:
     403
     404{{{
     405CREATE TABLE project.users (
     406    id                uuid            PRIMARY KEY DEFAULT gen_random_uuid(),
     407    username          varchar(50)     NOT NULL UNIQUE,
     408    email             varchar(255)    NOT NULL UNIQUE,
     409    full_name         varchar(200),
     410    password_hash     varchar(255)    NOT NULL,
     411    available_balance numeric(18,4)   NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
     412    invested_balance  numeric(18,4)   NOT NULL DEFAULT 0 CHECK (invested_balance  >= 0),
     413    created_at        timestamptz     NOT NULL DEFAULT now(),
     414    updated_at        timestamptz
     415);
     416
     417-- ============================================================================
     418-- CRYPTO
     419-- Catalog of crypto assets available on the platform.
     420-- ============================================================================
     421CREATE TABLE project.crypto (
     422    id         uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
     423    symbol     varchar(20)  NOT NULL UNIQUE,
     424    name       varchar(255) NOT NULL,
     425    created_at timestamptz  NOT NULL DEFAULT now()
     426);
     427
     428-- ============================================================================
     429-- MARKETS
     430-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD.
     431-- ============================================================================
     432CREATE TABLE project.markets (
     433    id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
     434    crypto_id      uuid        NOT NULL REFERENCES project.crypto(id),
     435    quote_currency char(3)     NOT NULL DEFAULT 'USD',
     436    is_active      boolean     NOT NULL DEFAULT true,
     437    created_at     timestamptz NOT NULL DEFAULT now(),
     438    CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency)
     439);
     440
     441-- ============================================================================
     442-- HOLDINGS
     443-- Per-user crypto position with running weighted average entry price.
     444-- ============================================================================
     445CREATE TABLE project.holdings (
     446    id                uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     447    user_id           uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
     448    crypto_id         uuid           NOT NULL REFERENCES project.crypto(id),
     449    quantity          numeric(20,4)  NOT NULL CHECK (quantity >= 0),
     450    -- Committed to the user's own open sell orders, not yet removed from the
     451    -- position. quantity - reserved_quantity is what is actually free to
     452    -- sell — the crypto-side equivalent of users.available_balance.
     453    reserved_quantity numeric(20,4)  NOT NULL DEFAULT 0
     454                                      CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
     455    -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
     456    -- v_portfolio can never silently produce NULL for an existing position.
     457    avg_price         numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
     458    created_at        timestamptz    NOT NULL DEFAULT now(),
     459    updated_at        timestamptz,
     460    CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
     461);
     462
     463-- ============================================================================
     464-- ORDERS
     465-- Orders placed by users on a market.
     466-- ============================================================================
     467CREATE TABLE project.orders (
     468    id          uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     469    user_id     uuid           NOT NULL REFERENCES project.users(id)   ON DELETE CASCADE,
     470    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     471    side        varchar(4)     NOT NULL CHECK (side   IN ('buy', 'sell')),
     472    type        varchar(20)    NOT NULL CHECK (type   IN ('market', 'limit')),
     473    status      varchar(20)    NOT NULL CHECK (status IN ('open', 'executed', 'cancelled')),
     474    quantity    numeric(20,4)  NOT NULL CHECK (quantity > 0),
     475    price       numeric(18,6),
     476    placed_at   timestamptz    NOT NULL DEFAULT now(),
     477    executed_at timestamptz
     478);
     479
     480CREATE INDEX idx_orders_user      ON project.orders(user_id);
     481CREATE INDEX idx_orders_market    ON project.orders(market_id);
     482CREATE INDEX idx_orders_status    ON project.orders(status);
     483
     484-- ============================================================================
     485-- TRANSACTIONS
     486-- Financial ledger: deposits, buys, sells, fees.
     487-- ============================================================================
     488CREATE TABLE project.transactions (
     489    id            uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
     490    user_id       uuid           NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
     491    type          varchar(50)    NOT NULL CHECK (type IN ('deposit', 'buy', 'sell', 'fee')),
     492    amount        numeric(18,4)  NOT NULL,
     493    currency      char(3)        NOT NULL DEFAULT 'USD',
     494    related_order uuid           REFERENCES project.orders(id),
     495    created_at    timestamptz    NOT NULL DEFAULT now(),
     496    description   text
     497);
     498
     499CREATE INDEX idx_transactions_user ON project.transactions(user_id, created_at DESC);
     500
     501-- ============================================================================
     502-- MARKET TRADES
     503-- Raw executed trades on a market. Source of truth for current price.
     504-- ============================================================================
     505CREATE TABLE project.market_trades (
     506    id          bigserial      PRIMARY KEY,
     507    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     508    executed_at timestamptz    NOT NULL,
     509    price       numeric(18,6)  NOT NULL CHECK (price    > 0),
     510    quantity    numeric(20,6)  NOT NULL CHECK (quantity > 0),
     511    side        varchar(4)     CHECK (side IN ('buy', 'sell')),
     512    source      varchar(50)    NOT NULL DEFAULT 'simulation'
     513);
     514
     515CREATE INDEX idx_market_trades_market_time ON project.market_trades(market_id, executed_at DESC);
     516
     517-- ============================================================================
     518-- MARKET CANDLES
     519-- OHLCV aggregates over standard timeframes.
     520-- ============================================================================
     521CREATE TABLE project.market_candles (
     522    id          bigserial      PRIMARY KEY,
     523    market_id   uuid           NOT NULL REFERENCES project.markets(id),
     524    timeframe   varchar(5)     NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')),
     525    open        numeric(18,6)  NOT NULL,
     526    high        numeric(18,6)  NOT NULL,
     527    low         numeric(18,6)  NOT NULL,
     528    close       numeric(18,6)  NOT NULL,
     529    volume      numeric(20,6)  NOT NULL,
     530    candle_time timestamptz    NOT NULL,
     531    CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time)
     532);
     533
     534CREATE INDEX idx_market_candles_market_tf_time ON project.market_candles(market_id, timeframe, candle_time DESC);
     535
     536-- ============================================================================
     537-- WATCHLISTS
     538-- ============================================================================
     539CREATE TABLE project.watchlists (
     540    id         uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
     541    user_id    uuid         NOT NULL REFERENCES project.users(id) ON DELETE CASCADE,
     542    name       varchar(100) NOT NULL,
     543    created_at timestamptz  NOT NULL DEFAULT now()
     544);
     545
     546CREATE TABLE project.watchlist_items (
     547    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
     548    watchlist_id uuid        NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE,
     549    crypto_id    uuid        NOT NULL REFERENCES project.crypto(id),
     550    added_at     timestamptz NOT NULL DEFAULT now(),
     551    CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id)
     552);
     553}}}