= **Нормализација** = == **Де-нормализирана база на податоци** == Се тргнува од една глобална, де-нормализирана релација што ги содржи атрибутите од целиот модел: `R = { user_id, email, username, password, training_user_id, training_gender, training_age, training_weight, training_id, training_date, training_type, training_duration, training_calories, investor_user_id, asset_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity, weight_user_id, weight_current, weight_height, weight_goal_weight, weight_goal_calories, daily_intake_id, daily_intake_date, daily_intake_calories, discipline_user_id, custom_tracking_id, custom_tracking_name, task_id, task_name, task_is_finished, daily_completion_id, daily_completion_date, daily_completion_procent, finance_user_id, finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit, income_id, income_date, income_amount }` **Напомена:** Атрибутите `num_tasks` и `tasks` се отстранети уште од првата нормализација бидејќи се изведени/не-атомски вредности и не припаѓаат на релацискиот модел. Слично, `weight_user_id` во `TRAINING_SESSIONS` е отстранет бидејќи претставува непотребна вкрстена зависност меѓу два независни профили на ист корисник. == **Функционални зависности** == Канонски покривач на функционалните зависности: - **FD1:** `user_id -> email, username, password` - **FD2:** `email -> user_id` - **FD3:** `username -> user_id` - **FD4:** `training_user_id -> training_gender, training_age, training_weight` - **FD5:** `training_id -> training_user_id, training_date, training_type, training_duration, training_calories` - **FD6:** `investor_user_id -> user_id` - **FD7:** `asset_id -> investor_user_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity` - **FD8:** `weight_user_id -> weight_current, weight_height, weight_goal_weight, weight_goal_calories` - **FD9:** `daily_intake_id -> weight_user_id, daily_intake_date, daily_intake_calories` - **FD10:** `discipline_user_id -> user_id` - **FD11:** `custom_tracking_id -> user_id, custom_tracking_name` - **FD12:** `task_id -> discipline_user_id, custom_tracking_id, task_name, task_is_finished` - **FD13:** `daily_completion_id -> user_id, daily_completion_date, daily_completion_procent` - **FD14:** `(task_id, daily_completion_id) -> /` (нема дополнителни атрибути — само врска) - **FD15:** `finance_user_id -> finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit` - **FD16:** `income_id -> finance_user_id, income_date, income_amount` == **Кандидат клучеви и примарен клуч** == === **Пресметување на затворања (closures)** === За да се најдат кандидат клучевите, пресметуваме затворање за секој атрибут (или комбинација) и проверуваме дали го покрива целиот `R`. **Атрибути кои се појавуваат само на десна страна** (никогаш детерминанти): - `email`, `username` — само преку `FD2`/`FD3` се детерминанти, но се и десни во `FD1` - `training_gender`, `training_age`, `training_weight`, `training_date`, `training_type`, `training_duration`, `training_calories` - `asset_ticker_symbol`, `asset_buy_price`, `asset_buy_date`, `asset_quantity` - `weight_current`, `weight_height`, `weight_goal_weight`, `weight_goal_calories` - `daily_intake_date`, `daily_intake_calories` - `custom_tracking_name`, `task_name`, `task_is_finished` - `daily_completion_date`, `daily_completion_procent` - `finance_spending_budget`, `finance_saving_budget`, `finance_investing_budget`, `finance_donation_budget`, `finance_credit` - `income_date`, `income_amount` Овие атрибути **не можат** да бидат дел од кандидат клуч бидејќи не детерминираат ништо надвор од себе. **Атрибути кои се само на лева страна** (мора да бидат во секој кандидат клуч): - `training_id`, `asset_id`, `daily_intake_id`, `task_id`, `daily_completion_id`, `income_id` **Останати детерминанти:** - `user_id`, `email`, `username`, `training_user_id`, `investor_user_id`, `weight_user_id`, `discipline_user_id`, `custom_tracking_id`, `finance_user_id` === **Затворање на минималниот суперклуч** === Пробуваме со комбинација на сите атрибути што се само леви + по еден претставник од групите поврзани со `user_id`: `K = { user_id, training_id, asset_id, daily_intake_id, task_id, daily_completion_id, income_id, training_user_id, investor_user_id, weight_user_id, discipline_user_id, custom_tracking_id, finance_user_id }` **`K+` пресметување:** - `user_id` -> `email, username, password` (FD1) - `training_user_id` -> `training_gender, training_age, training_weight` (FD4) - `training_id` -> `training_user_id, training_date, training_type, training_duration, training_calories` (FD5) - `investor_user_id` -> `user_id` (FD6) - `asset_id` -> `investor_user_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity` (FD7) - `weight_user_id` -> `weight_current, weight_height, weight_goal_weight, weight_goal_calories` (FD8) - `daily_intake_id` -> `weight_user_id, daily_intake_date, daily_intake_calories` (FD9) - `discipline_user_id` -> `user_id` (FD10) - `custom_tracking_id` -> `user_id, custom_tracking_name` (FD11) - `task_id` -> `discipline_user_id, custom_tracking_id, task_name, task_is_finished` (FD12) - `daily_completion_id` -> `user_id, daily_completion_date, daily_completion_procent` (FD13) - `finance_user_id` -> `finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit` (FD15) - `income_id` -> `finance_user_id, income_date, income_amount` (FD16) `K+ = R` → **`K` е суперклуч** ✓ **Минималност:** Отстранувањето на кој било атрибут од `K` резултира во непокриени атрибути (на пример, без `training_id` не можеме да добиеме `training_date`, `training_type` итн.), па `K` е минимален. **Примарен клуч:** `PK = { user_id, training_id, asset_id, daily_intake_id, task_id, daily_completion_id, income_id, training_user_id, investor_user_id, weight_user_id, discipline_user_id, custom_tracking_id, finance_user_id }` **Напомена:** Постојат и алтернативни кандидат клучеви: - `email` може да замени `user_id` (FD2: `email -> user_id`) - `username` може да замени `user_id` (FD3: `username -> user_id`) === **Алтернативни кандидат клучеви** === **Проверка на {email} како кандидат клуч:** - {email}+ = email -> user_id (FD2) → {user_id, email} - user_id -> username, password (FD1) - {email}+ = {email, user_id, username, password} - Не ги содржи training_id, asset_id, итн. → {email} САМ по себе НЕ е суперклуч за R. Бидејќи R содржи атрибути од повеќе независни групи (тренинг, средства, задачи...), ниту еден атрибут или мала комбинација не може да биде суперклуч за целата R. Затоа единствениот кандидат клуч за глобалната R е композитниот клуч наведен погоре. **Важно:** {email} и {username} се кандидат клучеви САМО во релацијата USERS по декомпозицијата, не во глобалната R. == **Проверка за 1НФ** == Релацијата `R` **НЕ ја задоволува 1НФ** поради: 1. **Повторливи групи** — еден корисник има повеќе `training_sessions`, повеќе `incomes`, повеќе `assets` итн., што значи дека редот би морал да се повторува или атрибутите би биле листи. 2. **Не-атомски атрибути** — во оригиналниот дизајн постоеја `tasks` (`TEXT` листа) и `num_tasks` (изведена вредност) во `DISCIPLINE_USERS` и `CUSTOM_TRACKING_CATEGORIES`. == **Декомпозиција по 1НФ** == **Релација што се анализира:** `R` (глобална) **Проблем:** Повторливи групи и не-атомски атрибути. **Решение:** Секој ентитет и врска добива своја посебна релација со атомски атрибути и локален примарен клуч. **Посебен случај — `TASK_DAILY_COMPLETION`:** Задачите и дневните завршувања се во врска **многу-кон-многу**. Се воведува посредна релација: TASK_DAILY_COMPLETION(task_id, daily_completion_id) **Резултат по 1НФ декомпозиција:** - `R1: USERS(user_id, email, username, password)` - `R2: TRAINING_USERS(training_user_id, training_gender, training_age, training_weight)` - `R3: TRAINING_SESSIONS(training_id, training_user_id, training_date, training_type, training_duration, training_calories)` - `R4: INVESTOR_USERS(investor_user_id)` - `R5: ASSETS(asset_id, investor_user_id, asset_ticker_symbol, asset_buy_price, asset_buy_date, asset_quantity)` - `R6: WEIGHT_USERS(weight_user_id, weight_current, weight_height, weight_goal_weight, weight_goal_calories)` - `R7: DAILY_INTAKES(daily_intake_id, weight_user_id, daily_intake_date, daily_intake_calories)` - `R8: DISCIPLINE_USERS(discipline_user_id)` - `R9: CUSTOM_TRACKING_CATEGORIES(custom_tracking_id, user_id, custom_tracking_name)` - `R10: TASKS(task_id, discipline_user_id, custom_tracking_id, task_name, task_is_finished)` - `R11: DAILY_COMPLETION(daily_completion_id, user_id, daily_completion_date, daily_completion_procent)` - `R12: TASK_DAILY_COMPLETION(task_id, daily_completion_id)` - `R13: FINANCE_USERS(finance_user_id, finance_spending_budget, finance_saving_budget, finance_investing_budget, finance_donation_budget, finance_credit)` - `R14: INCOMES(income_id, finance_user_id, income_date, income_amount)` - Сите атрибути се атомски. ✓ - Нема повторливи групи. ✓ == **Проверка за 2НФ** == **2НФ бара:** релацијата е во 1НФ и **нема парцијална зависност** — секој не-клучен атрибут зависи од **целиот** примарен клуч, не само од дел од него. Парцијална зависност постои само кога примарниот клуч е **сложен**. Ги разгледуваме само релациите со сложен `PK`: **`R12: TASK_DAILY_COMPLETION(task_id, daily_completion_id)`** - `PK: {task_id, daily_completion_id}` - Нема не-клучни атрибути → нема парцијални зависности → во 2НФ ✓ Сите останати релации (`R1`–`R11`, `R13`–`R14`) имаат **прост примарен клуч**, па парцијална зависност е невозможна → автоматски во 2НФ ✓ === **2NF проверка по релации (по 1НФ декомпозицијата)** === | Релација | PK | Дали PK е сложен? | Не-клучни атрибути | Парцијална зависност? | 2НФ? | |----------|-----|------------------|-------------------|----------------------|------| | USERS | user_id | Не | email, username, password | Невозможна | ✓ | | TRAINING_USERS | training_user_id | Не | gender, age, weight | Невозможна | ✓ | | TRAINING_SESSIONS | training_id | Не | user_id, date, type, duration, calories | Невозможна | ✓ | | INVESTOR_USERS | investor_user_id | Не | (нема) | Невозможна | ✓ | | ASSETS | asset_id | Не | user_id, ticker_symbol, buy_price, buy_date, quantity | Невозможна | ✓ | | WEIGHT_USERS | weight_user_id | Не | weight, height, goal_weight, goal_calories | Невозможна | ✓ | | DAILY_INTAKES | daily_intake_id | Не | user_id, date, calories | Невозможна | ✓ | | DISCIPLINE_USERS | discipline_user_id | Не | (нема) | Невозможна | ✓ | | CUSTOM_TRACKING_CATEGORIES | custom_tracking_id | Не | user_id, name | Невозможна | ✓ | | TASKS | task_id | Не | discipline_user_id, custom_tracking_id, name, is_finished | Невозможна | ✓ | | DAILY_COMPLETION | daily_completion_id | Не | user_id, date, procent | Невозможна | ✓ | | TASK_DAILY_COMPLETION | {task_id, daily_completion_id} | **Да** | (нема) | Нема не-клучни атрибути | ✓ | | FINANCE_USERS | finance_user_id | Не | spending_budget, saving_budget, investing_budget, donation_budget, credit | Невозможна | ✓ | | INCOMES | income_id | Не | user_id, date, amount | Невозможна | ✓ | **Заклучок:** Сите 14 релации се во 2НФ. Декомпозиција не е потребна. == **Декомпозиција по 2НФ** == Бидејќи сите релации веќе се во 2НФ, нема декомпозиција. Релациите од 1НФ чекорот се пренесуваат непроменети. == **Проверка за 3НФ** == **3НФ бара:** релацијата е во 2НФ и **нема транзитивна зависност** — не-клучен атрибут не смее да зависи од друг не-клучен атрибут. Ги разгледуваме релациите каде транзитивна зависност е можна: **`R3: TRAINING_SESSIONS`** - Атрибути: `training_id`, `training_user_id`, `training_date`, `training_type`, `training_duration`, `training_calories` - `PK`: `training_id` - `FDs`: `training_id -> training_user_id, training_date, training_type, training_duration, training_calories` - Не-клучни атрибути: `training_user_id`, `training_date`, `training_type`, `training_duration`, `training_calories` - Постои ли транзитивност? `training_user_id` е не-клучен, но не детерминира ништо во оваа релација → нема транзитивност → во 3НФ ✓ **`R5: ASSETS`** - Атрибути: `asset_id`, `investor_user_id`, `asset_ticker_symbol`, `asset_buy_price`, `asset_buy_date`, `asset_quantity` - `PK`: `asset_id` - `FDs`: `asset_id ->` сите атрибути - Не-клучен `investor_user_id` не детерминира ништо во оваа релација → во 3НФ ✓ **`R7: DAILY_INTAKES`** - Атрибути: `daily_intake_id`, `weight_user_id`, `daily_intake_date`, `daily_intake_calories` - `PK`: `daily_intake_id` - `FDs`: `daily_intake_id -> weight_user_id, daily_intake_date, daily_intake_calories` - `weight_user_id` не детерминира ништо во оваа релација → во 3НФ ✓ **`R9: CUSTOM_TRACKING_CATEGORIES`** - Атрибути: `custom_tracking_id`, `user_id`, `custom_tracking_name` - `PK`: `custom_tracking_id` - `FDs`: `custom_tracking_id -> user_id, custom_tracking_name` - `user_id` не детерминира ништо во оваа релација → во 3НФ ✓ **`R10: TASKS`** - Атрибути: `task_id`, `discipline_user_id`, `custom_tracking_id`, `task_name`, `task_is_finished` - `PK`: `task_id` - `FDs`: `task_id -> discipline_user_id, custom_tracking_id, task_name, task_is_finished` - Ниту `discipline_user_id` ниту `custom_tracking_id` детерминираат ништо во оваа релација → во 3НФ ✓ **`R14: INCOMES`** - Атрибути: `income_id`, `finance_user_id`, `income_date`, `income_amount` - `PK`: `income_id` - `FDs`: `income_id -> finance_user_id, income_date, income_amount` - `finance_user_id` не детерминира ништо во оваа релација → во 3НФ ✓ **Заклучок:** Сите релации се во **3НФ**. Декомпозиција не е потребна. == **Проверка за БКНФ** == **БКНФ бара:** за секоја нетривијална `FD X -> Y`, `X` мора да биде **суперклуч**. Ги разгледуваме релациите каде постојат повеќе кандидат клучеви: **`R1: USERS(user_id, email, username, password)`** - `FDs`: - `user_id -> email, username, password` - `email -> user_id` (FD2) - `username -> user_id` (FD3) - Кандидат клучеви: `{user_id}`, `{email}`, `{username}` - Проверка: - `user_id -> ...` : `user_id` е кандидат клуч ✓ - `email -> ...` : `email` е кандидат клуч ✓ - `username -> ...` : `username` е кандидат клуч ✓ - Сите детерминанти се кандидат клучеви → `USERS` е во БКНФ ✓ **`R10: TASKS`** - `FDs`: `task_id -> discipline_user_id, custom_tracking_id, task_name, task_is_finished` - Единствен кандидат клуч: `{task_id}` - `task_id` е кандидат клуч → во БКНФ ✓ **Напомена за CHECK constraint:** Условот `(discipline_user_id IS NOT NULL AND custom_tracking_id IS NULL) OR (discipline_user_id IS NULL AND custom_tracking_id IS NOT NULL)` не е функционална зависност туку ограничување на домен — не влијае на БКНФ. Сите останати релации имаат единствен кандидат клуч (нивниот сурогатен `PK`) и сите детерминанти во нив се суперклучеви → сите се во БКНФ ✓ **Заклучок:** **Целиот модел е во БКНФ.** === **Lossless Join проверка** === За секоја декомпозиција `R -> R1, R2`, `join`-от е lossless ако и само ако: `(R1 ∩ R2) -> R1` **ИЛИ** `(R1 ∩ R2) -> R2` Бидејќи декомпозицијата е вршена директно од глобалната релација `R` во посебни релации (не постепено `R->R1,R2->R3,R4...`), ја применуваме проверката за секој пар поврзани релации преку странски клуч: - `USERS` — `TRAINING_USERS`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `USERS` — `FINANCE_USERS`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `USERS` — `INVESTOR_USERS`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `USERS` — `DISCIPLINE_USERS`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `USERS` — `CUSTOM_TRACKING_CATEGORIES`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `USERS` — `DAILY_COMPLETION`: Пресек `{user_id}` → `user_id` е `PK` во `USERS` → суперклуч ✓ - `TRAINING_USERS` — `TRAINING_SESSIONS`: Пресек `{training_user_id}` → `training_user_id` е `PK` во `TRAINING_USERS` → суперклуч ✓ - `INVESTOR_USERS` — `ASSETS`: Пресек `{investor_user_id}` → `investor_user_id` е `PK` во `INVESTOR_USERS` → суперклуч ✓ - `WEIGHT_USERS` — `DAILY_INTAKES`: Пресек `{weight_user_id}` → `weight_user_id` е `PK` во `WEIGHT_USERS` → суперклуч ✓ - `FINANCE_USERS` — `INCOMES`: Пресек `{finance_user_id}` → `finance_user_id` е `PK` во `FINANCE_USERS` → суперклуч ✓ - `DISCIPLINE_USERS` — `TASKS`: Пресек `{discipline_user_id}` → `discipline_user_id` е `PK` во `DISCIPLINE_USERS` → суперклуч ✓ - `CUSTOM_TRACKING_CATEGORIES` — `TASKS`: Пресек `{custom_tracking_id}` → `custom_tracking_id` е `PK` во `CUSTOM_TRACKING_CATEGORIES` → суперклуч ✓ - `TASKS` — `TASK_DAILY_COMPLETION`: Пресек `{task_id}` → `task_id` е `PK` во `TASKS` → суперклуч ✓ - `DAILY_COMPLETION` — `TASK_DAILY_COMPLETION`: Пресек `{daily_completion_id}` → `daily_completion_id` е `PK` во `DAILY_COMPLETION` → суперклуч ✓ **Заклучок:** Сите декомпозиции имаат **lossless join** својство. ✓ === **Dependency Preservation проверка** === Проверуваме дека секоја `FD` од оригиналниот сет е зачувана во барем една од финалните релации: - `FD1: user_id -> email, username, password` → `USERS` ✓ - `FD2: email -> user_id` → `USERS` ✓ - `FD3: username -> user_id` → `USERS` ✓ - `FD4: training_user_id -> training_gender, training_age, training_weight` → `TRAINING_USERS` ✓ - `FD5: training_id -> training_user_id, ...` → `TRAINING_SESSIONS` ✓ - `FD6: investor_user_id -> user_id` → `INVESTOR_USERS` (преку `FK`) ✓ - `FD7: asset_id -> investor_user_id, ...` → `ASSETS` ✓ - `FD8: weight_user_id -> weight_current, ...` → `WEIGHT_USERS` ✓ - `FD9: daily_intake_id -> weight_user_id, ...` → `DAILY_INTAKES` ✓ - `FD10: discipline_user_id -> user_id` → `DISCIPLINE_USERS` (преку `FK`) ✓ - `FD11: custom_tracking_id -> user_id, name` → `CUSTOM_TRACKING_CATEGORIES` ✓ - `FD12: task_id -> discipline_user_id, ...` → `TASKS` ✓ - `FD13: daily_completion_id -> user_id, ...` → `DAILY_COMPLETION` ✓ - `FD14: (task_id, daily_completion_id) -> /` → `TASK_DAILY_COMPLETION` ✓ - `FD15: finance_user_id -> finance_spending_budget, ...` → `FINANCE_USERS` ✓ - `FD16: income_id -> finance_user_id, ...` → `INCOMES` ✓ **Заклучок:** **Сите функционални зависности се зачувани.** ✓ == **Финален резултат и дискусија** == === **Нормализиран релациски модел** === USERS(user_id, email, username, password) CK: {user_id}, {email}, {username} TRAINING_USERS(user_id, gender, age, weight) PK: user_id FK: user_id -> USERS TRAINING_SESSIONS(training_id, training_user_id, date, type, duration, calories) PK: training_id FK: training_user_id -> TRAINING_USERS INVESTOR_USERS(user_id) PK: user_id FK: user_id -> USERS ASSETS(asset_id, user_id, ticker_symbol, buy_price, buy_date, quantity) PK: asset_id FK: user_id -> INVESTOR_USERS WEIGHT_USERS(user_id, weight, height, goal_weight, goal_calories) PK: user_id FK: user_id -> USERS DAILY_INTAKES(daily_intake_id, user_id, date, calories) PK: daily_intake_id FK: user_id -> WEIGHT_USERS DISCIPLINE_USERS(user_id) PK: user_id FK: user_id -> USERS CUSTOM_TRACKING_CATEGORIES(custom_tracking_id, user_id, name) PK: custom_tracking_id FK: user_id -> USERS TASKS(task_id, discipline_user_id, custom_tracking_id, name, is_finished) PK: task_id FK: discipline_user_id -> DISCIPLINE_USERS FK: custom_tracking_id -> CUSTOM_TRACKING_CATEGORIES CHECK: точно еден од двата FK е NOT NULL DAILY_COMPLETION(daily_completion_id, user_id, date, procent) PK: daily_completion_id FK: user_id -> USERS TASK_DAILY_COMPLETION(task_id, daily_completion_id) PK: {task_id, daily_completion_id} FK: task_id -> TASKS FK: daily_completion_id -> DAILY_COMPLETION FINANCE_USERS(user_id, spending_budget, saving_budget, investing_budget, donation_budget, credit) PK: user_id FK: user_id -> USERS INCOMES(income_id, user_id, date, amount) PK: income_id FK: user_id -> FINANCE_USERS === **Дискусија** === **Разлики во однос на претходниот дизајн (Phase 2):** 1. **Отстранет `weight_user_id` од `TRAINING_SESSIONS`.** Во оригиналниот DDL, `TRAINING_SESSIONS` имаше `FK` кон `WEIGHT_USERS`. Ова е непотребна вкрстена зависност — тренинг сесијата логички припаѓа на тренинг профилот, не на weight профилот. Двата профила независно се врзуваат со `USERS` преку `user_id`. 2. **Отстранети `num_tasks` и `tasks` од `DISCIPLINE_USERS` и `CUSTOM_TRACKING_CATEGORIES`.** `num_tasks` е изведен атрибут (`COUNT` на `TASKS`) — чување на изведени вредности го нарушува принципот на нормализација и создава ризик од инконзистентност. `tasks` (`TEXT` листа) е не-атомски атрибут и директно ја нарушува `1НФ`. 3. **Додадени `UNIQUE` constraints на `email` и `username` во `USERS`.** Нормализацијата формално идентификуваше `{email}` и `{username}` како кандидат клучеви, па DDL мора да ги енфорцира. 4. **`GENERATED BY DEFAULT AS IDENTITY` за сите сурогатни `PK`-а.** Технички подобрување усогласено со нормализираниот модел. **Модел за употреба во понатамошните фази:** нормализираниот модел (финалниот DDL од Phase 5).