source: docs/P5-Normalization/NormalizationAIUsage.md@ 8b447ef

# 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
  [ERModel](../P1-ConceptualModel/ERModel.md), 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 [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) 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
  [RelationalDesign](../P2-RelationalDesign/RelationalDesign.md), since the phase instructions
  ask for Phase 2 documentation to be updated with the outcome of this phase.
- Wrote [Normalization](Normalization.md) 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
[ERModelAIUsage](../P1-ConceptualModel/ERModelAIUsage.md) 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`](../../server/db/schema_creation.sql) shows up as an alternate-key FD,
and no others were invented), and that the final ten relations really do match
[RelationalDesign](../P2-RelationalDesign/RelationalDesign.md) column for column — which I
checked by reading both side by side rather than taking the AI's claim of a match on faith.

## 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 [ERModel](../P1-ConceptualModel/ERModel.md) and
  [`schema_creation.sql`](../../server/db/schema_creation.sql) 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](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](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.