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

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

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

7) Финален заклучок

• Нормализацијата од 11 ентитети создаде уште 3 нови референтни ентитети: Countries, ActorTypes, Statuses. Вкупно: 14 ентитети.
• Индексите, UNIQUE и CHECK ограничувањата се опционални подобрувања за перформанси и квалитет на податоци и не ја менуваат нормалната форма.