Нормализација (од глобална релација до 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

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

Сите атрибути се атомски и нема повторувачки групи. Мултивредносните атрибути за актери и локации кај настаните се издвоени во посебна релација со составен клуч:

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

Со ова сите релации се во 1NF.

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

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

*Проверка:*

R_EventDetails(global_event_id, actor_id, location_id)
FDs: (global_event_id, actor_id, location_id) → ∅

Нема нефункционални атрибути што зависат од дел од составениот клуч. Кај сите останати ентитети PK е едноставен, па по дефиниција се во 2NF.

Со тоа сите релации се во 2NF.

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

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

Транзитивни зависности во глобалната релација:

country_code → country_name
type_code    → type_description
status       → status_description

Разложување со проекции:

Actors     = π_{actor_id, actor_code, actor_name, country_code, type_code}(R)
Countries  = π_{country_code, country_name}(R)
ActorTypes = π_{type_code, type_description}(R)

Locations  = π_{location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id}(R)
Countries  = π_{country_code, country_name}(R)

Subscription  = π_{subscription_id, user_id, plan_id, start_date, end_date, status}(R)
Notifications = π_{notification_id, user_id, event_id, notification_date, status}(R)
Statuses      = π_{status, status_description}(R)

Lossless-join услови:

R = Actors ⨝ Countries ⨝ ActorTypes
R = Locations ⨝ Countries
R = Subscription ⨝ Statuses
R = Notifications ⨝ Statuses

Зачувување на зависности:

• actor_id → actor_code, actor_name, country_code, type_code во Actors
• country_code → country_name во Countries
• type_code → type_description во Actor Types
• location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id во Locations
• subscription_id → …, status во Subscription
• notification_id → …, status во Notifications
• status → status_description во Statuses

Со ова сите транзитивни зависности се елиминирани, а сите релации се 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) Нефункционални подобрувања (не ја менуваат 3NF)

-- UNIQUE клучеви:
ALTER TABLE actors    ADD CONSTRAINT uq_actors_code     UNIQUE (actor_code);
ALTER TABLE locations ADD CONSTRAINT uq_locations_feat  UNIQUE (feature_id);

-- Индекси за споеви/филтри:
CREATE INDEX ix_event_details_event   ON event_details (global_event_id);
CREATE INDEX ix_event_details_actor   ON event_details (actor_id);
CREATE INDEX ix_event_details_loc     ON event_details (location_id);

CREATE INDEX ix_notifications_user    ON notifications (user_id, notification_date);
CREATE INDEX ix_notifications_event   ON notifications (event_id);

CREATE INDEX ix_predictions_event     ON predictions (event_id);
CREATE INDEX ix_predictions_actor     ON predictions (actor_id);

CREATE INDEX ix_analytics_event_actor ON event_analytics (event_id, actor_id, date);
CREATE INDEX ix_conflict_pair_date    ON conflict_risk (actor1_id, actor2_id, predicted_date);

-- CHECK ограничувања:
ALTER TABLE subscription  ADD CONSTRAINT ck_sub_dates  CHECK (end_date IS NULL OR end_date >= start_date);
ALTER TABLE events        ADD CONSTRAINT ck_year       CHECK (year BETWEEN 1900 AND 2100);
ALTER TABLE predictions   ADD CONSTRAINT ck_conf_score CHECK (confidence_score BETWEEN 0 AND 100);
ALTER TABLE conflict_risk ADD CONSTRAINT ck_risk_score CHECK (risk_score BETWEEN 0 AND 100);

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

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