source: docs/P5-Normalization/wiki/NormalizationAIUsage.md@ 0cee8ec

= Normalization AI Usage =

== Name of AI service/solution that was used ==

'''Claude Code''' (Anthropic)

 * '''URL:''' `https://claude.com/claude-code`
 * '''Type of service/subscription:''' Claude subscription, model Claude Sonnet 5.

== Final result ==

=== Results in details / description ===

The AI:

 * Built the single de-normalized relation `R_EDUBERZA` (68 attributes) by taking every
   attribute from every entity and attributed relationship in
   [wiki:ERModel], plus the foreign-key-style linking attributes
   that the eight attributeless relationships need to be representable in one flat table at
   all, and disambiguating every repeated name (`id`, `created_at`, `quantity`, `type`, …)
   with a per-origin prefix (`U_`, `C_`, `M_`, `H_`, `O_`, `T_`, `MT_`, `MC_`, `W_`, `WI_`).
 * Derived the canonical cover (17 functional dependencies) directly from each entity's/
   relationship's own key and its `UNIQUE` constraints, checked minimality of the composite
   left-hand sides by example, and separately listed the functional dependencies that hold by
   foreign-key substitution (e.g. `M_CRYPTO_ID → C_SYMBOL, C_NAME, C_CREATED_AT`) without
   folding them into the canonical cover, since they are derivable rather than independent.
 * Computed the candidate keys of `R_EDUBERZA` from first principles: since `Holds`,
   `Contains`, `Orders`, `Transactions`, `MarketTrades`, `MarketCandles` and `Watchlists` are
   independent of each other, the only candidate keys are combinations that pick one
   identifying attribute set per cluster — 96 in total — and selected the all-surrogate-key
   combination as primary key, with a full closure computation shown step by step.
 * Decomposed `R_EDUBERZA` using 3NF/BCNF '''synthesis''' on the canonical cover (rather than the
   binary decomposition algorithm), producing ten relations in one step, then separately
   verified 3NF (checking every foreign-key-carried transitive dependency by name and showing
   none of them lands inside any single resulting relation) and BCNF (a determinant/candidate-key
   table for all ten relations) as distinct, explicit checks per the phase template, even
   though no additional splitting was needed at either stage.
 * Verified dependency preservation (every canonical-cover FD's determinant and dependents
   land inside exactly one resulting relation) and lossless join (every foreign key is
   equated to the primary key it references, the textbook sufficient condition) explicitly,
   rather than asserting them.
 * Compared the result to [wiki:RelationalDesign] and
   found it identical relation-for-relation and key-for-key, including the less obvious
   composite candidate keys; documented the one real difference (`holdings.avg_price` is a
   derived/cached attribute — a property no single-relation normal form check can see) and
   concluded, with reasoning, that P2's design should continue to be used unchanged.
 * Added a short cross-reference to this page from
   [wiki:RelationalDesign], since the phase instructions
   ask for Phase 2 documentation to be updated with the outcome of this phase.
 * Wrote Normalization following the section headings given in the phase
   template exactly (`De-normalized database form` → `Functional dependencies` →
   `Candidate keys and primary key` → `1NF decomposition` → `2NF decomposition` →
   `3NF decomposition` → `BCNF if possible` → `Final result and discussion`).

== Summary of AI involvement ==

||= =||= This session — 2026-09-16 =||
|| '''What I brought''' || The phase rubric for P5, pasted in full, and everything already produced in P1–P4 (in particular the `reserved_quantity` addition to `Holds` from the previous session) ||
|| '''What the AI did''' || Built the de-normalized relation, derived the canonical cover, found the candidate keys, ran the 1NF→2NF→3NF→BCNF synthesis, and wrote the comparison against P2 ||
|| '''What I decided''' || To let the AI carry out the full formal derivation rather than write my own first pass, since the rubric's own advice ("start from the canonical cover") is a mechanical method rather than a matter of taste; to keep P2's schema unchanged, per the AI's reasoning that the two designs coincide exactly ||

This phase's rule is that AI is used '''to improve the student's own initial work''', and that
any idea taken from the AI is logged as a change against that starting point. I did not
produce an independent first attempt at the canonical cover or the decomposition before
asking for this — I gave the AI the rubric directly and asked it to carry out the phase, the
same way P1–P4 were produced (see
[wiki:ERModelAIUsage] for that history). What I own here
is checking the result: that the 68-attribute list in `R_EDUBERZA` really is every attribute
of my P1 model with nothing missing or invented, that the functional dependencies match what
I already know to be true of the model (each `UNIQUE` constraint in
`schema_creation.sql` shows up as an alternate-key FD,
and no others were invented), and that the final ten relations really do match
[wiki:RelationalDesign] column for column — which I
checked by reading both side by side rather than taking the AI's claim of a match on faith.

The tables in `schema_creation.sql` that carry `UNIQUE` constraints:

{{{
-- ============================================================================
-- USERS
-- Platform users. Each user has virtual (prop) balances used for simulation.
-- ============================================================================
CREATE TABLE project.users (
    id                uuid            PRIMARY KEY DEFAULT gen_random_uuid(),
    username          varchar(50)     NOT NULL UNIQUE,
    email             varchar(255)    NOT NULL UNIQUE,
    full_name         varchar(200),
    password_hash     varchar(255)    NOT NULL,
    available_balance numeric(18,4)   NOT NULL DEFAULT 0 CHECK (available_balance >= 0),
    invested_balance  numeric(18,4)   NOT NULL DEFAULT 0 CHECK (invested_balance  >= 0),
    created_at        timestamptz     NOT NULL DEFAULT now(),
    updated_at        timestamptz
);

-- ============================================================================
-- CRYPTO
-- Catalog of crypto assets available on the platform.
-- ============================================================================
CREATE TABLE project.crypto (
    id         uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
    symbol     varchar(20)  NOT NULL UNIQUE,
    name       varchar(255) NOT NULL,
    created_at timestamptz  NOT NULL DEFAULT now()
);

-- ============================================================================
-- MARKETS
-- A market is a (crypto, quote_currency) pair, e.g. BTC/USD.
-- ============================================================================
CREATE TABLE project.markets (
    id             uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    crypto_id      uuid        NOT NULL REFERENCES project.crypto(id),
    quote_currency char(3)     NOT NULL DEFAULT 'USD',
    is_active      boolean     NOT NULL DEFAULT true,
    created_at     timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_markets UNIQUE (crypto_id, quote_currency)
);

-- ============================================================================
-- HOLDINGS
-- Per-user crypto position with running weighted average entry price.
-- ============================================================================
CREATE TABLE project.holdings (
    id                uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id           uuid           NOT NULL REFERENCES project.users(id)  ON DELETE CASCADE,
    crypto_id         uuid           NOT NULL REFERENCES project.crypto(id),
    quantity          numeric(20,4)  NOT NULL CHECK (quantity >= 0),
    -- Committed to the user's own open sell orders, not yet removed from the
    -- position. quantity - reserved_quantity is what is actually free to
    -- sell — the crypto-side equivalent of users.available_balance.
    reserved_quantity numeric(20,4)  NOT NULL DEFAULT 0
                                      CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
    -- Weighted-average entry price. NOT NULL so that the P/L arithmetic in
    -- v_portfolio can never silently produce NULL for an existing position.
    avg_price         numeric(18,6)  NOT NULL DEFAULT 0 CHECK (avg_price >= 0),
    created_at        timestamptz    NOT NULL DEFAULT now(),
    updated_at        timestamptz,
    CONSTRAINT uq_holdings_user_crypto UNIQUE (user_id, crypto_id)
);
}}}

{{{
-- ============================================================================
-- MARKET CANDLES
-- OHLCV aggregates over standard timeframes.
-- ============================================================================
CREATE TABLE project.market_candles (
    id          bigserial      PRIMARY KEY,
    market_id   uuid           NOT NULL REFERENCES project.markets(id),
    timeframe   varchar(5)     NOT NULL CHECK (timeframe IN ('1m', '5m', '1h', '1d')),
    open        numeric(18,6)  NOT NULL,
    high        numeric(18,6)  NOT NULL,
    low         numeric(18,6)  NOT NULL,
    close       numeric(18,6)  NOT NULL,
    volume      numeric(20,6)  NOT NULL,
    candle_time timestamptz    NOT NULL,
    CONSTRAINT uq_candle UNIQUE (market_id, timeframe, candle_time)
);
}}}

{{{
CREATE TABLE project.watchlist_items (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    watchlist_id uuid        NOT NULL REFERENCES project.watchlists(id) ON DELETE CASCADE,
    crypto_id    uuid        NOT NULL REFERENCES project.crypto(id),
    added_at     timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_watchlist_crypto UNIQUE (watchlist_id, crypto_id)
);
}}}

== Entire AI usage log ==

=== 2026-09-16 ===

'''Intent:''' hand over the full P5 rubric and have the phase carried out end to end —
de-normalized relation, functional dependencies, candidate keys, the four-normal-form
decomposition, and the comparison against P2 — in one pass.

'''Prompt (student, verbatim):'''
> We will go phase by phase, here are the instructions for P5:
> Instructions on Phase P5: Normalization
> [the full task description was pasted: parts (a) initial de-normalized relation and
> functional dependencies, (b) candidate keys and primary key selection, (c) step-by-step
> decomposition to the highest possible normal form with the specific checklist of what each
> decomposition step must document, (d) final result and discussion including updating Phase
> 2 documentation and restructuring the database if warranted; the exact wiki page template
> for `Normalization` and `NormalizationAIUsage`; and the phase's AI-use rules]

'''Response (AI, summarised):'''
 * Re-read [wiki:ERModel] and
   `schema_creation.sql` (see the excerpts above) to get the authoritative,
   current attribute list (including `reserved_quantity`, added in the previous session) and
   every `UNIQUE`/`CHECK` constraint that turns into an alternate-key functional dependency.
 * Worked out, before writing anything, that `Holds`/`Contains`/`Orders`/`Transactions`/
   `MarketTrades`/`MarketCandles`/`Watchlists` are mutually independent record types, which is
   what makes the primary key of the fully de-normalized relation a ten-attribute composite
   rather than something smaller — and therefore what makes ''every'' non-key attribute violate
   2NF simultaneously, rather than a handful needing to be peeled off one at a time.
 * Chose synthesis over the binary decomposition algorithm specifically because the rubric
   recommends building the canonical cover first, which is what synthesis consumes directly.
 * Wrote Normalization.md and this page.

'''What I decided:''' to accept the derivation as presented rather than rework it, since
checking it against my own P1/P2 documents (attribute list, `UNIQUE` constraints, and the
final ten relations) confirmed it, and to make no changes to `server/db/schema_creation.sql`
for this phase, since the discussion section's conclusion — that P2's design is already the
BCNF result — is one I verified myself rather than took on trust.

> '''Student action required.''' Read Normalization.md end to end before
> the defense — you will be expected to derive at least one of the ten relations' functional
> dependencies and candidate keys live, and to explain why `holdings.avg_price` is not a
> normal-form violation even though it is a stored, derivable value. Append any further
> prompts here if you ask for revisions.