Changes between Version 6 and Version 7 of normalization


Ignore:
Timestamp:
09/20/25 15:31:41 (4 weeks ago)
Author:
155036
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • normalization

    v6 v7  
    1 == Нормализација и подобрувања на дизајнот ==
     1== Нормализација (од глобална релација до 3NF) — методологија и докази ==
    22
    3 Во оваа секција е прикажана нормализацијата на целокупниот ER модел до трета нормална форма (3NF), за да се отстранат зависностите, се избегне дуплирање на податоци и да се подобри интегритетот.
     3Овој дел ја прикажува нормализацијата преку глобален модел и формални чекори до 3NF,
     4со цел елиминација на повторувања, парцијални и транзитивни зависности и аномалии
     5при INSERT/UPDATE/DELETE.
    46
    57[attachment:normalized_ddl_schema.sql]
    68
    7 === Објаснување на нормализацијата ===
     9=== 1) Глобална универзална релација и атрибути ===
    810
    9 Нормализацијата беше извршена како анализа на целиот модел составен од 12 табели, при што се разгледуваа зависностите помеѓу табелите, а не само во рамки на секоја одделно. На тој начин се овозможи откривање на заеднички функционални зависности и елиминирање на дуплирани ентитети и атрибути.
     11Нека глобалната релација е составена од следните 11 ентитети:
    1012
    11 Клучни подобрувања:
    12 - Централизација на статусите преку единствена табела **statuses**, користена и во **subscription** и **notifications**.
    13 - Централизација на земјите преку табелата **countries**, споделена од **actors** и **locations**.
    14 - Елиминирање на транзитивни зависности преку атрибути како **role_id**, **country_code**, и **type_code**.
    15 - Генерација на дополнителни референтни табели како **actor_types**, **roles**, и **statuses** со foreign keys за интегритет.
     13 * **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`
     14 * **Actors**: `actor_id, actor_code, actor_name, country_code, type_code`
     15 * **Locations**: `location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id`
     16 * **Event Details**: `global_event_id, actor_id, location_id`
     17 * **Users**: `user_id, first_name, last_name, email, password_hash, role_id, last_login_date, register_date`
     18 * **Roles**: `role_id, role_name`
     19 * **Plan**: `plan_id, plan_name, max_events, max_notifications, max_predictions`
     20 * **Subscription**: `subscription_id, user_id, plan_id, start_date, end_date, status`
     21 * **Notifications**: `notification_id, user_id, event_id, notification_date, status`
     22 * **Predictions**: `prediction_id, event_id, actor_id, predicted_date, prediction_type, confidence_score`
     23 * **Conflict Risk**: `conflict_id, actor1_id, actor2_id, risk_score, predicted_date, description`
     24 * **Event Analytics**: `analytics_id, actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description`
    1625
    17 Со оваа нормализација до 3NF, се подобрува конзистентноста, се намалува дуплирањето на податоци и се поедноставува одржувањето на базата.
     26=== 2) Клучни функционални зависности (F) ===
    1827
     28Изведени oд PK/FK и бизнис-правила:
    1929
    20 === **actors**(**actor_id**, **actor_code**, **actor_name**, **country_code**, **type_code**) ===
     30 * `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`
     31 * `actor_id → actor_code, actor_name, country_code, type_code`
     32 * `location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id`
     33 * `user_id → first_name, last_name, email, password_hash, role_id, last_login_date, register_date`
     34 * `role_id → role_name`
     35 * `plan_id → plan_name, max_events, max_notifications, max_predictions`
     36 * `subscription_id → user_id, plan_id, start_date, end_date, status`
     37 * `notification_id → user_id, event_id, notification_date, status`
     38 * `prediction_id → event_id, actor_id, predicted_date, prediction_type, confidence_score`
     39 * `conflict_id → actor1_id, actor2_id, risk_score, predicted_date, description`
     40 * `analytics_id → actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description`
    2141
    22 Функционални зависности: 
    23 **actor_id** → **actor_code**, **actor_name**, **country_code**, **type_code** 
    24 **country_code** → **country_name** 
    25 **type_code** → **type_description**
     42Транзитивни зависности:
     43 * `country_code → country_name`
     44 * `type_code → type_description`
     45 * `status → status_description`
    2646
    27 1NF: Атомски вредности  / 
    28 2NF: Сите атрибути зависат од примарниот клуч  / 
    29 3NF: Постои транзитивна зависност → **actor_id** → **country_code** → **country_name**
     47=== 3) Прва нормална форма (1NF) ===
    3048
    31 Декомпозиција: 
    32 **actors**(**actor_id**, **actor_code**, **actor_name**, **country_code**, **type_code**) 
    33 **countries**(**country_code**, **country_name**) 
    34 **actor_types**(**type_code**, **type_description**)
     49Сите атрибути се атомски и нема повторувачки групи.
     50Мултивредносните атрибути за актери и локации кај настаните се издвоени во
     51посебна релација со составен клуч:
    3552
    36 === **locations**(**location_id**, **full_name**, **country_code**, **adm1_code**, **latitude**, **longitude**, **feature_id**) ===
     53{{{
     54EventDetails(global_event_id, actor_id, location_id)
     55PK = (global_event_id, actor_id, location_id)
     56}}}
    3757
    38 Функционални зависности: 
    39 **location_id** → **full_name**, **country_code**, **adm1_code**, **latitude**, **longitude**, **feature_id** 
    40 **country_code** → **country_name**
     58Со ова сите релации се во 1NF.
    4159
    42 1NF: Атомски вредности  / 
    43 2NF: Сите атрибути зависат од примарниот клуч  / 
    44 3NF: Транзитивна зависност преку **country_code**
     60=== 4) Втора нормална форма (2NF) ===
    4561
    46 Декомпозиција: 
    47 **locations**(**location_id**, **full_name**, **country_code**, **adm1_code**, **latitude**, **longitude**, **feature_id**) 
    48 **countries**(**country_code**, **country_name**)
     62Критериум: кај релации со составен PK, ниту еден нефункционален атрибут да не зависи само од дел од PK.
    4963
    50 === **users**(**user_id**, **first_name**, **last_name**, **email**, **password_hash**, **role_id**, **last_login_date**, **register_date**) ===
     64*Проверка:* 
     65{{{
     66R_EventDetails(global_event_id, actor_id, location_id)
     67FDs: (global_event_id, actor_id, location_id) → ∅
     68}}}
    5169
    52 Функционални зависности: 
    53 **user_id** → **first_name**, **last_name**, **email**, **password_hash**, **role_id**, **last_login_date**, **register_date** 
    54 **role_id** → **role_name**
     70Нема нефункционални атрибути што зависат од дел од составениот клуч. 
     71Кај сите останати ентитети PK е едноставен, па по дефиниција се во 2NF.
    5572
    56 1NF: Атомски вредности / 
    57 2NF: Зависности од целиот клуч / 
    58 3NF: Транзитивна зависност преку **role_id**
     73Со тоа сите релации се во 2NF.
    5974
    60 Декомпозиција: 
    61 **users**(**user_id**, **first_name**, **last_name**, **email**, **password_hash**, **role_id**, **last_login_date**, **register_date**) 
    62 **roles**(**role_id**, **role_name**)
     75=== 5) Трета нормална форма (3NF) ===
    6376
    64 === **subscription**(**subscription_id**, **user_id**, **plan_id**, **start_date**, **end_date**, **status**) ===
     77Критериум: атрибут што не е клуч да не зависи транзитивно од клуч преку друг атрибут што не е клуч.
    6578
    66 Функционални зависности: 
    67 **subscription_id** → **user_id**, **plan_id**, **start_date**, **end_date**, **status** 
    68 **status** → **status_description**
     79Транзитивни зависности во глобалната релација:
     80{{{
     81country_code → country_name
     82type_code    → type_description
     83status       → status_description
     84}}}
    6985
    70 1NF: Атомски вредности / 
    71 2NF: Сите зависности од клучот / 
    72 3NF: Транзитивна зависност преку **status**
     86Разложување со проекции:
     87{{{
     88Actors     = π_{actor_id, actor_code, actor_name, country_code, type_code}(R)
     89Countries  = π_{country_code, country_name}(R)
     90ActorTypes = π_{type_code, type_description}(R)
    7391
    74 Декомпозиција: 
    75 **subscription**(**subscription_id**, **user_id**, **plan_id**, **start_date**, **end_date**, **status_code**) 
    76 **statuses**(**status_code**, **status_description**)
     92Locations  = π_{location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id}(R)
     93Countries  = π_{country_code, country_name}(R)
    7794
    78 === **notifications**(**notification_id**, **user_id**, **event_id**, **notification_date**, **status**) ===
     95Subscription  = π_{subscription_id, user_id, plan_id, start_date, end_date, status}(R)
     96Notifications = π_{notification_id, user_id, event_id, notification_date, status}(R)
     97Statuses      = π_{status, status_description}(R)
     98}}}
    7999
    80 Функционални зависности: 
    81 **notification_id** → **user_id**, **event_id**, **notification_date**, **status** 
    82 **status** → **status_description**
     100Lossless-join услови:
     101{{{
     102R = Actors ⨝ Countries ⨝ ActorTypes
     103R = Locations ⨝ Countries
     104R = Subscription ⨝ Statuses
     105R = Notifications ⨝ Statuses
     106}}}
    83107
    84 1NF: Атомски вредности / 
    85 2NF: Зависности од целиот клуч / 
    86 3NF: Транзитивна зависност преку **status**
     108Зачувување на зависности:
     109 * `actor_id → actor_code, actor_name, country_code, type_code` во Actors 
     110 * `country_code → country_name` во Countries 
     111 * `type_code → type_description` во Actor Types 
     112 * `location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id` во Locations 
     113 * `subscription_id → …, status` во Subscription 
     114 * `notification_id → …, status` во Notifications 
     115 * `status → status_description` во Statuses 
    87116
    88 Декомпозиција: 
    89 **notifications**(**notification_id**, **user_id**, **event_id**, **notification_date**, **status_code**) 
    90 **statuses**(**status_code**, **status_description**)
     117Со ова сите транзитивни зависности се елиминирани, а сите релации се lossless и ги зачувуваат функционалните зависности. 
     118Затоа глобалната релација е во 3NF.
    91119
    92 === **predictions**(**prediction_id**, **event_id**, **actor_id**, **predicted_date**, **prediction_type**, **confidence_score**) ===
     120=== 6) Резултантна 3NF шема ===
    93121
    94 Функционални зависности: 
    95 **prediction_id** → **event_id**, **actor_id**, **predicted_date**, **prediction_type**, **confidence_score**
     122 * Fact-Detail: `events`, `event_details`, `predictions`, `conflict_risk`, `event_analytics`
     123 * Dimensions-Master: `actors`, `locations`, `users`, `roles`, `plan`, `countries`, `actor_types`, `statuses`
     124 * Operational: `subscription`, `notifications`
    96125
    97 1NF: Атомски вредности / 
    98 2NF: Нема составен клуч, сите зависности се од примарниот клуч / 
    99 3NF: Нема транзитивни зависности
     126=== 7) Нефункционални подобрувања (не ја менуваат 3NF) ===
    100127
    101 Декомпозиција: / (нема потреба)
     128{{{
     129-- UNIQUE клучеви:
     130ALTER TABLE actors    ADD CONSTRAINT uq_actors_code     UNIQUE (actor_code);
     131ALTER TABLE locations ADD CONSTRAINT uq_locations_feat  UNIQUE (feature_id);
    102132
    103 === **conflict_risk**(**conflict_id**, **actor1_id**, **actor2_id**, **risk_score**, **predicted_date**, **description**) ===
     133-- Индекси за споеви/филтри:
     134CREATE INDEX ix_event_details_event   ON event_details (global_event_id);
     135CREATE INDEX ix_event_details_actor   ON event_details (actor_id);
     136CREATE INDEX ix_event_details_loc     ON event_details (location_id);
    104137
    105 Функционални зависности: 
    106 **conflict_id** → **actor1_id**, **actor2_id**, **risk_score**, **predicted_date**, **description**
     138CREATE INDEX ix_notifications_user    ON notifications (user_id, notification_date);
     139CREATE INDEX ix_notifications_event   ON notifications (event_id);
    107140
    108 1NF: Атомски вредности / 
    109 2NF: Сите зависности се од примарниот клуч / 
    110 3NF: Нема транзитивни зависности
     141CREATE INDEX ix_predictions_event     ON predictions (event_id);
     142CREATE INDEX ix_predictions_actor     ON predictions (actor_id);
    111143
    112 Декомпозиција: / (нема потреба)
     144CREATE INDEX ix_analytics_event_actor ON event_analytics (event_id, actor_id, date);
     145CREATE INDEX ix_conflict_pair_date    ON conflict_risk (actor1_id, actor2_id, predicted_date);
    113146
    114 === **event_analytics**(**analytics_id**, **actor_id**, **event_id**, **date**, **conflict_coefficient**, **mentions_count**, **avg_tone**, **description**) ===
     147-- CHECK ограничувања:
     148ALTER TABLE subscription  ADD CONSTRAINT ck_sub_dates  CHECK (end_date IS NULL OR end_date >= start_date);
     149ALTER TABLE events        ADD CONSTRAINT ck_year       CHECK (year BETWEEN 1900 AND 2100);
     150ALTER TABLE predictions   ADD CONSTRAINT ck_conf_score CHECK (confidence_score BETWEEN 0 AND 100);
     151ALTER TABLE conflict_risk ADD CONSTRAINT ck_risk_score CHECK (risk_score BETWEEN 0 AND 100);
     152}}}
    115153
    116 Функционални зависности: 
    117 **analytics_id** → **actor_id**, **event_id**, **date**, **conflict_coefficient**, **mentions_count**, **avg_tone**, **description**
     154=== 8) Финален заклучок ===
    118155
    119 1NF: Атомски вредности / 
    120 2NF: Сите зависности се од клучот / 
    121 3NF: Нема транзитивни зависности
    122 
    123 Декомпозиција: / (нема потреба)
    124 
    125 === **Нови релации по нормализација** ===
    126 
    127 CREATE TABLE **countries** ( 
    128  **country_code** VARCHAR(5) PRIMARY KEY, 
    129  **country_name** VARCHAR(100) 
    130 );
    131 
    132 CREATE TABLE **actor_types** ( 
    133  **type_code** VARCHAR(10) PRIMARY KEY, 
    134  **type_description** VARCHAR(100) 
    135 );
    136 
    137 CREATE TABLE **roles** ( 
    138  **role_id** SERIAL PRIMARY KEY, 
    139  **role_name** VARCHAR(50) NOT NULL 
    140 );
    141 
    142 CREATE TABLE **statuses** ( 
    143  **status_code** VARCHAR(20) PRIMARY KEY, 
    144  **status_description** TEXT 
    145 );
     156 * Нормализацијата од 11 ентитети создаде уште 3 нови референтни ентитети: `Countries`, `ActorTypes`, `Statuses`. Вкупно: 14 ентитети.
     157 * Индексите, UNIQUE и CHECK ограничувањата се опционални подобрувања за перформанси и квалитет на податоци и не ја менуваат нормалната форма.