== Нормализација (од глобална релација до 3NF) — методологија и докази ==

Овој дел ја прикажува нормализацијата преку глобален модел и формални чекори до 3NF,
со цел елиминација на повторувања, парцијални и транзитивни зависности и аномалии
при INSERT/UPDATE/DELETE.

[attachment:normalized_ddl_schema.sql]

=== 1) Глобална универзална релација и атрибути ===

Нека глобалната релација е составена од следните 11 ентитети:

 * **Events**: `global_event_id, sql_date, month_year, year, fraction_date, is_root_event, event_code, goldstein_scale, num_mentions, num_sources, num_articles, avg_tone`
 * **Actors**: `actor_id, actor_code, actor_name, country_code, type_code`
 * **Locations**: `location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id`
 * **Event Details**: `global_event_id, actor_id, location_id`
 * **Users**: `user_id, first_name, last_name, email, password_hash, role_id, last_login_date, register_date`
 * **Roles**: `role_id, role_name`
 * **Plan**: `plan_id, plan_name, max_events, max_notifications, max_predictions`
 * **Subscription**: `subscription_id, user_id, plan_id, start_date, end_date, status`
 * **Notifications**: `notification_id, user_id, event_id, notification_date, status`
 * **Predictions**: `prediction_id, event_id, actor_id, predicted_date, prediction_type, confidence_score`
 * **Conflict Risk**: `conflict_id, actor1_id, actor2_id, risk_score, predicted_date, description`
 * **Event Analytics**: `analytics_id, actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description`

=== 2) Клучни функционални зависности (F) ===

Изведени oд PK/FK и бизнис-правила:

 * `global_event_id → sql_date, month_year, year, fraction_date, is_root_event, event_code, goldstein_scale, num_mentions, num_sources, num_articles, avg_tone`
 * `actor_id → actor_code, actor_name, country_code, type_code`
 * `location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id`
 * `user_id → first_name, last_name, email, password_hash, role_id, last_login_date, register_date`
 * `role_id → role_name`
 * `plan_id → plan_name, max_events, max_notifications, max_predictions`
 * `subscription_id → user_id, plan_id, start_date, end_date, status`
 * `notification_id → user_id, event_id, notification_date, status`
 * `prediction_id → event_id, actor_id, predicted_date, prediction_type, confidence_score`
 * `conflict_id → actor1_id, actor2_id, risk_score, predicted_date, description`
 * `analytics_id → actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description`

Транзитивни зависности:
 * `country_code → country_name`
 * `type_code → type_description`
 * `status → status_description`

=== 2a) Примарен клуч на глобалната релација (корекција) ===

За глобалната релација R, минималниот кандидат-клуч (составен клуч) што ги покрива сите домени,
без да содржи атрибути што се функционално детерминирани од други елементи од клучот, е:

{{{
K_R = { global_event_id,
        actor_id,
        location_id,
        user_id,
        subscription_id,
        notification_id,
        prediction_id,
        conflict_id,
        analytics_id }
}}}

Образложение за минималност:
- role_id е детерминиран од user_id → не треба во K_R
- plan_id е детерминиран од subscription_id → не треба во K_R
- country_code, type_code се детерминирани од actor_id → не треба во K_R
- status е детерминиран од subscription_id и/или notification_id → не треба во K_R

Со ова, K_R е минимален и служи како основа за формална проверка на 1NF/2NF/3NF.


=== 3) Прва нормална форма (1NF) ===

Со примарниот клуч K_R дефиниран, проверуваме дали сите атрибути се атомски и дали нема 
повторувачки групи. Атрибутите се атомски (нема сложени или мултивредносни полиња).

Проблем се појавува кај мултивредносните односи настан–актор–локација, кои се решаваат 
со издвојување во посебна релација:

{{{
EventDetails(global_event_id, actor_id, location_id)
PK = (global_event_id, actor_id, location_id)
}}}

По ова разложување, глобалната релација е во 1NF.


=== 4) Втора нормална форма (2NF) ===

Критериум: секој нефункционален атрибут мора да зависи од целиот составен клуч K_R, а не 
само од дел од него.

Постојат масовни парцијални зависности, затоа што повеќе атрибути зависат 
од поединечни идентификатори (подмножества на K_R). Примери:

{{{
global_event_id → {sql_date, month_year, year, fraction_date, is_root_event,
                   event_code, goldstein_scale, num_mentions, num_sources,
                   num_articles, avg_tone}

actor_id        → {actor_code, actor_name, country_code, type_code}

location_id     → {full_name, country_code, adm1_code, latitude, longitude,
                   feature_id}

user_id         → {first_name, last_name, email, password_hash, role_id,
                   last_login_date, register_date}

role_id         → {role_name}

plan_id         → {plan_name, max_events, max_notifications, max_predictions}

subscription_id → {user_id, plan_id, start_date, end_date, status}

notification_id → {user_id, event_id, notification_date, status}

prediction_id   → {event_id, actor_id, predicted_date, prediction_type,
                   confidence_score}

conflict_id     → {actor1_id, actor2_id, risk_score, predicted_date, description}

analytics_id    → {actor_id, event_id, date, conflict_coefficient, mentions_count,
                   avg_tone, description}
}}}

Секој блок зависи само од еден дел од составниот клуч K_R, па глобалната релација R не е во 2NF.

*Решение:* разложување на R според идентификаторите, со тоа секоја група атрибути се 
преселува во сопствена релација каде PK е токму тој идентификатор. Ова дава:

{{{
Events(global_event_id PK, sql_date, month_year, year, fraction_date, is_root_event,
       event_code, goldstein_scale, num_mentions, num_sources, num_articles, avg_tone)

Actors(actor_id PK, actor_code, actor_name, country_code, type_code)

Locations(location_id PK, full_name, country_code, adm1_code, latitude, longitude,
          feature_id)

Users(user_id PK, first_name, last_name, email, password_hash, role_id,
      last_login_date, register_date)

Roles(role_id PK, role_name)

Plan(plan_id PK, plan_name, max_events, max_notifications, max_predictions)

Subscription(subscription_id PK, user_id, plan_id, start_date, end_date, status)

Notifications(notification_id PK, user_id, event_id, notification_date, status)

Predictions(prediction_id PK, event_id, actor_id, predicted_date, prediction_type,
            confidence_score)

Conflict_Risk(conflict_id PK, actor1_id, actor2_id, risk_score, predicted_date,
              description)

Event_Details(global_event_id, actor_id, location_id,
              PK = (global_event_id, actor_id, location_id))

Event_Analytics(analytics_id PK, actor_id, event_id, date,
                conflict_coefficient, mentions_count, avg_tone, description)
}}}

Со ова секој нефункционален атрибут во секоја релација зависи од целиот PK на таа релација.  
Затоа новото множество релации е во 2NF.


=== 5) Трета нормална форма (3NF) ===

Критериум: атрибут што не е клуч да не зависи транзитивно од клуч преку друг атрибут што не е клуч.

Во глобалната релација R постојат транзитивни зависности:
{{{
country_code → country_name
type_code    → type_description
status       → status_description
}}}

За нив дефинираме посебни релации со експлицитни PK/FK:

{{{
Countries(country_code PK, country_name)
ActorTypes(type_code PK, type_description)
Statuses(status PK, status_description)

Actors(actor_id PK, actor_code, actor_name,
       country_code FK→Countries(country_code),
       type_code    FK→ActorTypes(type_code))

Locations(location_id PK, full_name,
          country_code FK→Countries(country_code),
          adm1_code, latitude, longitude, feature_id)

Subscription(subscription_id PK, user_id, plan_id, start_date, end_date,
             status FK→Statuses(status))

Notifications(notification_id PK, user_id, event_id, notification_date,
              status FK→Statuses(status))
}}}

Lossless-join услови (базирани на PK/FK):
{{{
Actors       ⨝_{Actors.country_code    = Countries.country_code}   Countries
Actors       ⨝_{Actors.type_code       = ActorTypes.type_code}     ActorTypes
Locations    ⨝_{Locations.country_code = Countries.country_code}   Countries
Subscription ⨝_{Subscription.status    = Statuses.status}          Statuses
Notifications⨝_{Notifications.status   = Statuses.status}          Statuses
}}}

Објаснување:
 * Сите споеви се FK→PK (еден кон многу), па се lossless (не се губат и не се создаваат лажни tuples).
 * Сите функционални зависности се зачувани:
   - `actor_id → actor_code, actor_name, country_code, type_code` во Actors
   - `country_code → country_name` во Countries
   - `type_code → type_description` во ActorTypes
   - `location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id` во Locations
   - `subscription_id → user_id, plan_id, start_date, end_date, status` во Subscription
   - `notification_id → user_id, event_id, notification_date, status` во Notifications
   - `status → status_description` во Statuses

Со ова транзитивните зависности се елиминирани, а преку PK/FK формата споевите се lossless 
и зависностите се зачувани. Следствено, релациите се во 3NF.


=== 6) Резултантна 3NF шема ===

 * Fact-Detail: `events`, `event_details`, `predictions`, `conflict_risk`, `event_analytics`
 * Dimensions-Master: `actors`, `locations`, `users`, `roles`, `plan`, `countries`, `actor_types`, `statuses`
 * Operational: `subscription`, `notifications`