Normalization

This phase deliberately ignores the design from [ERModel](../P1-ConceptualModel/ERModel.md) (P1) and [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) (P2) as a starting point.

Instead, it starts from a single flat relation containing every attribute of the model, derives the functional dependencies that hold on that relation, and decomposes it formally using Armstrong's axioms and the standard definitions of 1NF, 2NF, 3NF and BCNF.

The [Final result and discussion](#final-result-and-discussion) section then compares the result of this independent normalization process with the P2 relational design.

De-normalized database form

Building one relation from the whole model

The ER model contains ten entity/relationship sets carrying attributes:

• Users
• Cryptos
• Markets
• Holds
• Orders
• Transactions
• MarketTrades
• MarketCandles
• Watchlists
• Contains

Eight additional relationships (QuotedOn, PlacedOn, Places, Records, Settles, Fills, Aggregates, Owns) carry no attributes of their own.

In Chen notation these relationships require no attributes because the relationship itself represents the connection between entity sets. A single flat relation has no such mechanism, so the referenced key must be represented by an ordinary attribute. The following foreign-key-style attributes are therefore included in the flat relation:

• M_CRYPTO_ID
• O_USER_ID
• O_MARKET_ID
• T_USER_ID
• T_RELATED_ORDER
• MT_MARKET_ID
• MC_MARKET_ID
• W_USER_ID
• H_USER_ID
• H_CRYPTO_ID
• WI_WATCHLIST_ID
• WI_CRYPTO_ID

These attributes are not copied from the P2 schema as a design decision. They are required because a flat relation cannot otherwise preserve the links expressed by the original ER relationships.

Because names such as id, created_at, quantity, type, name, price and side occur in more than one entity or relationship set, every attribute is prefixed with a short code identifying its origin.

| Prefix | Origin | Attributes | | ------ | ----------------------------------- | ------------------------------------------------------------------------------------------------------------------------------ | | 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 | | C_ | Cryptos | C_ID, C_SYMBOL, C_NAME, C_CREATED_AT | | M_ | Markets + QuotedOn | M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT | | 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 | | 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 | | T_ | Transactions + Records, Settles | T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION | | MT_ | MarketTrades + Fills | MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE | | MC_ | MarketCandles + Aggregates | MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME | | W_ | Watchlists + Owns | W_ID, W_USER_ID, W_NAME, W_CREATED_AT | | WI_ | Contains + surrogate key | WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT |

H_ID and WI_ID are included because Holds and Contains are M:N relationships with attributes and therefore require their own identifying attribute in the relational representation.

The resulting flat relation is:

## [source,sql]

R_EDUBERZA( U_ID, U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT,

C_ID, C_SYMBOL, C_NAME, C_CREATED_AT,

M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT,

H_ID, H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT,

O_ID, O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT,

T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION,

MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE,

MC_ID, MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME,

W_ID, W_USER_ID, W_NAME, W_CREATED_AT,

WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT ) -

This relation contains 68 attributes.

Every attribute is single-valued and atomic: balances, timestamps, identifiers, symbols, amounts and similar values are all scalar values rather than lists or nested records.

Therefore R_EDUBERZA satisfies 1NF as written. The remaining sections determine whether it satisfies 2NF, 3NF or BCNF.

Functional dependencies

Canonical cover

The initial functional dependencies are read directly from the identifying constraints of the entity and relationship sets.

Each entity's or relationship's key determines the attributes belonging to that entity or relationship, while unique candidate keys determine the corresponding primary key.

| # | Functional dependency | Source | | ---- | -------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------- | | 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 | | FD2 | U_USERNAME -> U_ID | Users (UNIQUE(username)) | | FD3 | U_EMAIL -> U_ID | Users (UNIQUE(email)) | | FD4 | C_ID -> C_SYMBOL, C_NAME, C_CREATED_AT | Cryptos | | FD5 | C_SYMBOL -> C_ID | Cryptos (UNIQUE(symbol)) | | FD6 | M_ID -> M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT | Markets | | FD7 | {M_CRYPTO_ID, M_QUOTE_CURRENCY} -> M_ID | Markets (UNIQUE(crypto_id, quote_currency)) | | FD8 | H_ID -> H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT | Holds | | FD9 | {H_USER_ID, H_CRYPTO_ID} -> H_ID | Holds (UNIQUE(user_id, crypto_id)) | | 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 | | FD11 | T_ID -> T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION | Transactions | | FD12 | MT_ID -> MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE | MarketTrades | | FD13 | MC_ID -> MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME | MarketCandles | | FD14 | {MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} -> MC_ID | MarketCandles (UNIQUE(market_id, timeframe, candle_time)) | | FD15 | W_ID -> W_USER_ID, W_NAME, W_CREATED_AT | Watchlists | | FD16 | WI_ID -> WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT | Contains | | FD17 | {WI_WATCHLIST_ID, WI_CRYPTO_ID} -> WI_ID | Contains (UNIQUE(watchlist_id, crypto_id)) |

No dependency above contains an extraneous attribute on its determinant, and the dependent attributes are assigned to the entity or relationship that actually owns them.

Minimality of the composite dependencies

The composite determinants in FD7, FD9, FD14 and FD17 are minimal.

For example, in FD7:

{M_CRYPTO_ID, M_QUOTE_CURRENCY} -> M_ID

M_CRYPTO_ID alone does not determine M_ID, because the same crypto may be traded in several quote currencies. M_QUOTE_CURRENCY alone also does not determine M_ID, because many different crypto assets may use the same quote currency.

Therefore neither attribute can be removed from the determinant.

The same reasoning applies to:

• FD9: {H_USER_ID, H_CRYPTO_ID} -> H_ID
• FD14: {MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} -> MC_ID
• FD17: {WI_WATCHLIST_ID, WI_CRYPTO_ID} -> WI_ID

Their left-hand sides are exactly the uniqueness constraints of the corresponding relations.

Dependencies carried by foreign keys

The flat relation also carries dependencies induced by foreign keys.

Because a referencing attribute takes its values from the candidate-key domain of another entity, the attributes determined by that referenced key are also fixed for the corresponding foreign-key value.

| Foreign key | References | Therefore determines | | ----------------- | ---------- | ------------------------------------------------------------------------ | | M_CRYPTO_ID | C_ID | C_SYMBOL, C_NAME, C_CREATED_AT | | H_USER_ID | U_ID | all U_* attributes | | H_CRYPTO_ID | C_ID | all C_* attributes | | O_USER_ID | U_ID | all U_* attributes | | O_MARKET_ID | M_ID | all M_* attributes, and transitively C_* | | T_USER_ID | U_ID | all U_* attributes | | T_RELATED_ORDER | O_ID | all O_* attributes, and transitively U_*, M_*, C_* when not null | | MT_MARKET_ID | M_ID | all M_* attributes, and transitively C_* | | MC_MARKET_ID | M_ID | all M_* attributes, and transitively C_* | | W_USER_ID | U_ID | all U_* attributes | | WI_WATCHLIST_ID | W_ID | all W_* attributes, and transitively U_* | | WI_CRYPTO_ID | C_ID | all C_* attributes |

These dependencies are not added as separate entries to the canonical cover. They are derivable from FD1-FD17 together with the foreign-key constraints and transitivity.

They are nevertheless important during the 3NF analysis because they are the source of the transitive dependencies that would exist if the referenced attributes were copied into the referencing relation.

Candidate keys and primary key

Candidate-key structure of R_EDUBERZA

The entity and relationship clusters in R_EDUBERZA are structurally independent.

For example, an order identifier tells us nothing about which unrelated transaction, watchlist item, holding or market-candle tuple happens to coexist in the same flat row. A user may have zero orders, zero holdings, zero transactions, and zero watchlists, so none of those identifiers can be inferred from another cluster.

The nullable T_RELATED_ORDER attribute also cannot serve as a universal bridge between transactions and orders.

Therefore a key for R_EDUBERZA must contain one determinant from each independent cluster.

Chosen primary key

The chosen key is:

## [source,text]

## {U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID}

Closure check

Applying FD1-FD17 to this attribute set gives the following closure:

| Step | Attributes added | Dependency | | ----- | ------------------------------------------------------------------------------------------------------------------------ | ---------- | | Start | U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID | — | | 1 | U_USERNAME, U_EMAIL, U_FULL_NAME, U_PASSWORD_HASH, U_AVAILABLE_BALANCE, U_INVESTED_BALANCE, U_CREATED_AT, U_UPDATED_AT | FD1 | | 2 | C_SYMBOL, C_NAME, C_CREATED_AT | FD4 | | 3 | M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT | FD6 | | 4 | H_USER_ID, H_CRYPTO_ID, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT | FD8 | | 5 | O_USER_ID, O_MARKET_ID, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT | FD10 | | 6 | T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION | FD11 | | 7 | MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE | FD12 | | 8 | MC_MARKET_ID, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME | FD13 | | 9 | W_USER_ID, W_NAME, W_CREATED_AT | FD15 | | 10 | WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT | FD16 |

The closure contains all 68 attributes, so the set is a superkey.

Removing any one of the ten identifier attributes leaves an entire independent cluster unreachable. Therefore the key is minimal and is a candidate key.

Other candidate keys

The chosen primary key is not the only candidate key.

Any determinant that uniquely identifies one cluster can replace that cluster's surrogate identifier:

• U_USERNAME or U_EMAIL can replace U_ID.
• C_SYMBOL can replace C_ID.
• {M_CRYPTO_ID, M_QUOTE_CURRENCY} can replace M_ID.
• {H_USER_ID, H_CRYPTO_ID} can replace H_ID.
• {MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} can replace MC_ID.
• {WI_WATCHLIST_ID, WI_CRYPTO_ID} can replace WI_ID.

This produces:

3 x 2 x 2 x 2 x 1 x 1 x 1 x 2 x 1 x 2 = 96

candidate keys.

The all-surrogate-ID combination is chosen as the primary key because the surrogate identifiers are opaque, stable and independent of user-facing values such as usernames, e-mail addresses and symbols.

Normal form before decomposition

R_EDUBERZA is in 1NF only.

It is not in 2NF because non-prime attributes depend on proper subsets of its ten-attribute candidate key. Since 2NF is a precondition for both 3NF and BCNF, the relation is also not in 3NF or BCNF.

1NF decomposition

No decomposition is necessary for 1NF.

R_EDUBERZA already contains only atomic, single-valued attributes and has no repeating groups.

The real decomposition therefore begins with the 2NF analysis.

2NF decomposition

Relation analyzed

R_EDUBERZA, containing all 68 attributes, with primary key

{U_ID, C_ID, M_ID, H_ID, O_ID, T_ID, MT_ID, MC_ID, W_ID, WI_ID}.

FD1-FD17 are in force.

2NF violations

2NF forbids a non-prime attribute from depending on a proper subset of a candidate key.

Every dependency in the canonical cover has a determinant smaller than the ten-attribute key:

• U_ID
• C_ID
• M_ID
• H_ID
• O_ID
• T_ID
• MT_ID
• MC_ID
• W_ID
• WI_ID
• or one of the corresponding composite candidate keys.

Therefore every non-prime attribute in R_EDUBERZA is involved in a partial dependency.

This is not a small collection of isolated violations. The entire flat relation violates 2NF because ten independent record types were artificially combined under one composite key.

Decomposition by 3NF/BCNF synthesis

Since a canonical cover is already available, the decomposition uses the standard synthesis approach rather than repeatedly applying binary decomposition.

Grouping the functional dependencies by determinant produces ten relations:

| New relation | Attributes | Candidate keys | Source FDs | | ------------------- | ------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------- | ---------- | | 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-FD3 | | R_CRYPTO | C_ID, C_SYMBOL, C_NAME, C_CREATED_AT | C_ID, C_SYMBOL | FD4-FD5 | | 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 | | 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 | | 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 | | R_TRANSACTIONS | T_ID, T_USER_ID, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER, T_CREATED_AT, T_DESCRIPTION | T_ID | FD11 | | R_MARKET_TRADES | MT_ID, MT_MARKET_ID, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE | MT_ID | FD12 | | 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 | | R_WATCHLISTS | W_ID, W_USER_ID, W_NAME, W_CREATED_AT | W_ID | FD15 | | R_WATCHLIST_ITEMS | WI_ID, WI_WATCHLIST_ID, WI_CRYPTO_ID, WI_ADDED_AT | WI_ID, {WI_WATCHLIST_ID, WI_CRYPTO_ID} | FD16-FD17 |

Every non-prime attribute now depends on the whole key of its own relation, so the 2NF violations have been removed.

Dependency preservation

FD1-FD17 form the canonical cover of R_EDUBERZA.

Every dependency is contained completely within one of the ten new relations. Therefore the union of the dependencies preserved by the decomposed relations is again FD1-FD17.

No functional dependency from the canonical cover is lost.

Lossless join

The resulting relations are connected through foreign-key/primary-key pairs.

The important joins are:

• R_MARKETS.M_CRYPTO_ID -> R_CRYPTO.C_ID
• R_HOLDINGS.H_USER_ID -> R_USERS.U_ID
• R_HOLDINGS.H_CRYPTO_ID -> R_CRYPTO.C_ID
• R_ORDERS.O_USER_ID -> R_USERS.U_ID
• R_ORDERS.O_MARKET_ID -> R_MARKETS.M_ID
• R_TRANSACTIONS.T_USER_ID -> R_USERS.U_ID
• 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
• 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

For each such join, the shared attribute on the referenced side is a candidate key of that relation.

Consequently, joining the relations through these foreign-key/primary-key pairs is lossless: no original tuples are lost and no spurious tuples are introduced.

3NF decomposition

Relations analyzed

The ten relations produced by the 2NF synthesis are checked individually for 3NF.

The main question is whether a non-prime attribute is transitively dependent on a candidate key through another non-prime attribute.

The foreign-key-carried dependencies from [Dependencies carried by foreign keys](#dependencies-carried-by-foreign-keys) are considered here because they are exactly the dependencies that could create transitive relationships.

Worked example: R_MARKETS

R_MARKETS contains:

M_ID, M_CRYPTO_ID, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT

The relation has:

M_ID -> M_CRYPTO_ID

and, through the foreign-key dependency:

M_CRYPTO_ID -> C_SYMBOL, C_NAME, C_CREATED_AT

Therefore, if the crypto attributes were also stored inside R_MARKETS, the following transitive dependency would exist:

M_ID -> M_CRYPTO_ID -> C_SYMBOL

That would violate 3NF.

The crypto attributes are not stored there. They are already isolated in R_CRYPTO, where:

C_ID -> C_SYMBOL, C_NAME, C_CREATED_AT

Therefore the transitive dependency points to another relation rather than existing between attributes of R_MARKETS itself.

Remaining relations

The same reasoning applies to all other foreign keys:

• H_USER_ID and H_CRYPTO_ID
• O_USER_ID and O_MARKET_ID
• T_USER_ID and T_RELATED_ORDER
• MT_MARKET_ID
• MC_MARKET_ID
• W_USER_ID
• WI_WATCHLIST_ID and WI_CRYPTO_ID

In every case, the referenced attributes are stored in their own relation.

Within each individual relation, there is no non-prime attribute that determines another non-prime attribute.

Therefore no further decomposition is required for 3NF.

Conclusion

The ten relations produced by the 2NF synthesis are already in 3NF.

No additional decomposition is necessary.

BCNF if possible

BCNF criterion

BCNF is stricter than 3NF.

For every non-trivial functional dependency X -> Y that holds in a relation, X must be a candidate key of that relation.

The ten relations are therefore checked against all dependencies that hold within each relation.

| Relation | Functional dependencies | Determinant | Candidate key? | | ------------------- | ----------------------- | ------------------------------------------------------- | --------------- | | R_USERS | FD1, FD2, FD3 | U_ID, U_USERNAME, U_EMAIL | Yes — all three | | R_CRYPTO | FD4, FD5 | C_ID, C_SYMBOL | Yes — both | | R_MARKETS | FD6, FD7 | M_ID, {M_CRYPTO_ID, M_QUOTE_CURRENCY} | Yes — both | | R_HOLDINGS | FD8, FD9 | H_ID, {H_USER_ID, H_CRYPTO_ID} | Yes — both | | R_ORDERS | FD10 | O_ID | Yes | | R_TRANSACTIONS | FD11 | T_ID | Yes | | R_MARKET_TRADES | FD12 | MT_ID | Yes | | R_MARKET_CANDLES | FD13, FD14 | MC_ID, {MC_MARKET_ID, MC_TIMEFRAME, MC_CANDLE_TIME} | Yes — both | | R_WATCHLISTS | FD15 | W_ID | Yes | | R_WATCHLIST_ITEMS | FD16, FD17 | WI_ID, {WI_WATCHLIST_ID, WI_CRYPTO_ID} | Yes — both |

Every determinant is therefore a candidate key of its own relation.

Conclusion

All ten relations satisfy BCNF.

No further decomposition is necessary.

Splitting any of these relations further would not resolve a normal-form violation because every determinant already identifies a complete candidate key.

The result is therefore stronger than a 3NF-only decomposition: the final relations are in BCNF.

Final result and discussion

Normalized relational model

The normalized schema is:

## [source,text]

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 )

R_CRYPTO( C_ID, C_SYMBOL, C_NAME, C_CREATED_AT )

R_MARKETS( M_ID, M_CRYPTO_ID -> R_CRYPTO, M_QUOTE_CURRENCY, M_IS_ACTIVE, M_CREATED_AT )

R_HOLDINGS( H_ID, H_USER_ID -> R_USERS, H_CRYPTO_ID -> R_CRYPTO, H_QUANTITY, H_RESERVED_QUANTITY, H_AVG_PRICE, H_CREATED_AT, H_UPDATED_AT )

R_ORDERS( O_ID, O_USER_ID -> R_USERS, O_MARKET_ID -> R_MARKETS, O_SIDE, O_TYPE, O_STATUS, O_QUANTITY, O_PRICE, O_PLACED_AT, O_EXECUTED_AT )

R_TRANSACTIONS( T_ID, T_USER_ID -> R_USERS, T_TYPE, T_AMOUNT, T_CURRENCY, T_RELATED_ORDER -> R_ORDERS, T_CREATED_AT, T_DESCRIPTION )

R_MARKET_TRADES( MT_ID, MT_MARKET_ID -> R_MARKETS, MT_EXECUTED_AT, MT_PRICE, MT_QUANTITY, MT_SIDE, MT_SOURCE )

R_MARKET_CANDLES( MC_ID, MC_MARKET_ID -> R_MARKETS, MC_TIMEFRAME, MC_OPEN, MC_HIGH, MC_LOW, MC_CLOSE, MC_VOLUME, MC_CANDLE_TIME )

R_WATCHLISTS( W_ID, W_USER_ID -> R_USERS, W_NAME, W_CREATED_AT )

R_WATCHLIST_ITEMS( WI_ID, WI_WATCHLIST_ID -> R_WATCHLISTS, WI_CRYPTO_ID -> R_CRYPTO, WI_ADDED_AT ) -

The result contains ten relations connected through eleven foreign keys. Every relation satisfies BCNF.

Comparison with P2

This normalized result is the same relational structure as the P2 design.

After removing the U_, C_, M_, H_, O_, T_, MT_, MC_, W_ and WI_ prefixes, the relations correspond directly to:

• users
• crypto
• markets
• holdings
• orders
• transactions
• market_trades
• market_candles
• watchlists
• watchlist_items

The candidate keys also match, including the composite keys:

• {user_id, crypto_id} on holdings
• {crypto_id, quote_currency} on markets
• {market_id, timeframe, candle_time} on market_candles
• {watchlist_id, crypto_id} on watchlist_items

The foreign-key structure is also identical.

P2 arrived at this structure through the standard ER-to-relational transformation:

• each entity becomes a relation keyed by its identifier;
• each attributed M:N relationship becomes a relation of its own;
• each 1:N relationship is represented by a foreign key on the N side.

This normalization phase reaches the same result independently by starting from a single flat relation and applying functional-dependency-based normalization.

The agreement between the two methods therefore provides an independent check of the relational design.

The holdings.avg_price exception

The one important distinction is holdings.avg_price.

P2 identifies avg_price as a derived attribute: it represents the running weighted-average buy price and can be recomputed from the relevant transaction history. It is nevertheless stored for performance reasons.

The functional-dependency analysis performed in this phase does not classify that attribute as a normal-form violation.

Within the relation, the dependency

H_ID -> H_AVG_PRICE

is still a valid functional dependency.

The fact that H_AVG_PRICE can also be recomputed from rows in another relation is a different issue from whether a functional dependency inside one relation violates 2NF, 3NF or BCNF.

Therefore normalization does not require H_AVG_PRICE to be removed.

The stored value remains an intentional denormalisation for performance.

Design used going forward

The P2 design is retained unchanged.

The normalization phase confirms that there is no need to restructure the database objects: the independent normalization process produces the same ten relations already implemented by the project.

The existing [server/db/schema_creation.sql](../../server/db/schema_creation.sql) therefore remains the authoritative physical schema.

The reserved_quantity attribute added to holdings is naturally part of R_HOLDINGS because it depends on H_ID, just like H_QUANTITY and H_AVG_PRICE; it does not introduce a separate relation or a new normalization requirement.

P4's prototype can therefore continue using the same database structure without schema changes.

Normalization history

• v01 — First complete normalization analysis. Started from a single 68-attribute relation reconstructed from the P1 model rather than from the P2 tables.
• 2NF decomposition — Applied canonical-cover synthesis to separate the ten independent entity/relationship clusters.
• 3NF verification — Checked foreign-key-induced transitive dependencies and confirmed that none remains inside an individual relation.
• BCNF verification — Checked every determinant and established that each is a candidate key of its relation.
• Final result — The independently derived schema matches P2 relation for relation, including candidate keys and foreign keys. The only documented denormalisation remains the stored holdings.avg_price.

The formal normalization analysis therefore validates the existing relational design rather than requiring a new one.