Changes between Initial Version and Version 1 of Normalization

Timestamp:

07/07/26 18:48:34 (6 hours ago)

Author:

231118

Comment:

--

Normalization

@@ +1 @@
+= Phase P5: Normalization =
+
+== a) Initial De-normalized Relation and Functional Dependencies ==
+
+=== Global Attribute List (unified relation R) ===
+
+Сите атрибути од целиот ER модел, обединети во една единствена релација R, без дупликати имиња:
+
+{{{
+R(
+  user_id, user_email, user_name, user_picture, user_created_at,
+  tenant_id, tenant_name, owner_email, tenant_created_at,
+  role, membership_created_at,
+  env_id, env_name, env_created_at,
+  env_token_id, token, et_created_at, expires_at,
+  save_process_history, es_created_at, es_updated_at,
+  computer_id, computer_name, computer_user, computer_ip, computer_os,
+  first_seen, last_seen, sysmon_available,
+  process_id, pid, process_name, cpu_percent, memory_mb,
+  proc_username, cmdline, proc_timestamp,
+  history_id, cpu_usage, ram_usage, disk_usage,
+  network_sent_mb, network_recv_mb, hist_timestamp,
+  net_conn_id, local_address, remote_address, status,
+  nc_process_name, nc_timestamp,
+  alert_id, alert_type, severity, description, alert_timestamp, resolved,
+  sysmon_event_id, event_id, event_type, message, sysmon_timestamp, details
+)
+}}}
+
+=== Canonical Cover — Global Set of Functional Dependencies ===
+
+{{{
+FD1:  user_id -> user_email, user_name, user_picture, user_created_at
+FD2:  tenant_id -> tenant_name, owner_email, tenant_created_at
+FD3:  (user_id, tenant_id) -> role, membership_created_at
+FD4:  env_id -> tenant_id, env_name, env_created_at
+FD5:  env_token_id -> tenant_id, env_name, token, et_created_at, expires_at
+FD6:  (tenant_id, env_name) -> save_process_history, es_created_at, es_updated_at
+FD7:  computer_id -> tenant_id, env_name, computer_name, computer_user,
+                      computer_ip, computer_os, first_seen, last_seen, sysmon_available
+FD8:  process_id -> computer_id, pid, process_name, cpu_percent, memory_mb,
+                     proc_username, cmdline, proc_timestamp
+FD9:  history_id -> computer_id, cpu_usage, ram_usage, disk_usage,
+                     network_sent_mb, network_recv_mb, hist_timestamp
+FD10: net_conn_id -> computer_id, pid, local_address, remote_address,
+                      status, nc_process_name, nc_timestamp
+FD11: alert_id -> computer_id, alert_type, severity, description,
+                   alert_timestamp, resolved
+FD12: sysmon_event_id -> computer_id, event_id, event_type, message,
+                          sysmon_timestamp, details
+}}}
+
+Ова е канонски cover (минимален сет): секоја FD има единствен атрибут / минимален сет на левата страна, нема вишок FD-и кои можат да се изведат од другите, и нема redundant атрибути на десните страни.
+
+----
+
+== b) Candidate Keys and Primary Key Selection ==
+
+=== Идентификување примитивни атрибути ===
+
+Атрибут е '''примитивен''' ако никогаш не се појавува на десната страна (RHS) на ниту една FD. Таквите атрибути мора да бидат дел од секој кандидат клуч, бидејќи ниту една FD не може да ги "произведе" - единствениот начин да се дојде до нив е да се земат директно.
+
+Проверка на секој атрибут од R против сите RHS на FD1-FD12:
+
+{{{
+Примитивни атрибути (никогаш на RHS):
+  user_id, env_id, env_token_id, process_id,
+  history_id, net_conn_id, alert_id, sysmon_event_id
+
+Забелешка: tenant_id и computer_id НЕ се примитивни -
+тие се појавуваат на RHS во FD4, FD5, FD7 (изведливи атрибути).
+}}}
+
+=== Пробен клуч и Attribute Closure (K+) ===
+
+{{{
+K = { user_id, env_id, env_token_id, process_id,
+      history_id, net_conn_id, alert_id, sysmon_event_id }
+}}}
+
+Пресметка на closure K+ чекор по чекор:
+
+{{{
+K+ (почетно) = K
+
+Применуваме FD1 (user_id):
+  K+ = K+ U { user_email, user_name, user_picture, user_created_at }
+
+Применуваме FD4 (env_id):
+  K+ = K+ U { tenant_id, env_name, env_created_at }
+
+Применуваме FD5 (env_token_id):
+  K+ = K+ U { token, et_created_at, expires_at }
+  (tenant_id, env_name веќе се во K+)
+
+Применуваме FD8 (process_id):
+  K+ = K+ U { computer_id, pid, process_name, cpu_percent, memory_mb,
+              proc_username, cmdline, proc_timestamp }
+
+Применуваме FD9 (history_id):
+  K+ = K+ U { cpu_usage, ram_usage, disk_usage,
+              network_sent_mb, network_recv_mb, hist_timestamp }
+
+Применуваме FD10 (net_conn_id):
+  K+ = K+ U { local_address, remote_address, status,
+              nc_process_name, nc_timestamp }
+
+Применуваме FD11 (alert_id):
+  K+ = K+ U { alert_type, severity, description, alert_timestamp, resolved }
+
+Применуваме FD12 (sysmon_event_id):
+  K+ = K+ U { event_id, event_type, message, sysmon_timestamp, details }
+
+Сега computer_id е веќе во K+ -> применуваме FD7:
+  K+ = K+ U { computer_name, computer_user, computer_ip, computer_os,
+              first_seen, last_seen, sysmon_available }
+
+Сега (tenant_id, env_name) се двете во K+ -> применуваме FD6:
+  K+ = K+ U { save_process_history, es_created_at, es_updated_at }
+
+Сега (user_id, tenant_id) се двете во K+ -> применуваме FD3:
+  K+ = K+ U { role, membership_created_at }
+
+------------------------------------------------------
+K+ = сите атрибути на R  =>  K е superkey
+------------------------------------------------------
+}}}
+
+=== Минималност (доказ дека К е Candidate Key) ===
+
+За да биде К candidate key, а не само superkey, мора да е '''минимален''' - тргнувањето на било кој атрибут од К мора да го "скрши" closure-от (да не стигне повеќе до сите атрибути).
+
+Бидејќи сите 8 атрибути во К се '''примитивни''' (никогаш не се на RHS на ниту една FD), секој од нив е задолжителен - ниту еден друг атрибут во целата релација не може да го "изведе". Значи тргнувањето на кој било од нив прави closure-от веднаш да падне под целосниот сет атрибути.
+
+{{{
+=> K е минимален superkey => K е Candidate Key
+
+Candidate Key = (user_id, env_id, env_token_id, process_id,
+                  history_id, net_conn_id, alert_id, sysmon_event_id)
+}}}
+
+=== Единственост на кандидат клучот ===
+
+Бидејќи сите 8 атрибути во К се примитивни (задолжителни во секој candidate key), а нивниот closure веќе покрива целосно R, не постои друга комбинација атрибути (со помалку или различни примитивни атрибути) која исто така би била минимален superkey. Следствено, ова е '''единствениот''' candidate key на релацијата R.
+
+{{{
+PRIMARY KEY (R) = (user_id, env_id, env_token_id, process_id,
+                    history_id, net_conn_id, alert_id, sysmon_event_id)
+}}}
+
+'''Забелешка:''' tenant_id и computer_id намерно НЕ се дел од клучот, иако интуитивно "изгледаат" како да треба - тие се redundant атрибути (изводливи преку env_id/computer_id верижно преку FD4, FD7), и нивно вклучување во клучот би го нарушило условот за минималност.
+
+=== Нормална форма на почетната релација ===
+
+{{{
+1NF: ДА  - сите вредности се атомски, нема повторувачки групи/низи
+
+2NF: НЕ  - клучот е composite (8 атрибути), но постојат атрибути кои
+           зависат само од ДЕЛ од клучот:
+             * user_email, user_name, ... зависат само од user_id
+             * token, et_created_at, ... зависат само од env_token_id
+             * process_name, cpu_percent, ... зависат само од process_id
+             * ... (аналогно за сите останати FD-и)
+           Ова е класична парцијална зависност -> нарушување на 2NF.
+
+Заклучок: почетната релација R е само во 1NF.
+Декомпозицијата започнува со отстранување на парцијалните зависности.
+}}}
+
+----
+
+== c) Step-by-Step Decomposition ==
+
+=== Чекор 1: 1NF -> 2NF (отстранување на парцијални зависности) ===
+
+За секоја FD чија лева страна е '''подмножество''' (proper subset) на кандидат клучот, тој дел од клучот заедно со сите атрибути кои зависи од него се издвојува во нова релација.
+
+'''Анализирана релација:''' R (почетна, 1NF)
+'''FD-и кои предизвикуваат проблем:''' FD1, FD2, FD3, FD4, FD5, FD6, FD7, FD8, FD9, FD10, FD11, FD12 (сите, бидејќи левите страни се proper subsets на 8-атрибутниот клуч)
+'''Прв старт на декомпозиција:''' FD1 (user_id -> ...), потоа редоследно и останатите
+
+Резултат по декомпозицијата:
+
+{{{
+R1: Users(user_id, user_email, user_name, user_picture, user_created_at)
+    FD: user_id -> сите останати
+    Candidate key: {user_id}   PK: user_id
+
+R2: Tenants(tenant_id, tenant_name, owner_email, tenant_created_at)
+    FD: tenant_id -> сите останати
+    Candidate key: {tenant_id}   PK: tenant_id
+
+R3: Memberships(user_id, tenant_id, role, membership_created_at)
+    FD: (user_id, tenant_id) -> role, membership_created_at
+    Candidate key: {user_id, tenant_id}   PK: (user_id, tenant_id)
+
+R4: Environments(env_id, tenant_id, env_name, env_created_at)
+    FD: env_id -> tenant_id, env_name, env_created_at
+    Candidate key: {env_id}   PK: env_id
+
+R5: EnvTokens(env_token_id, tenant_id, env_name, token, et_created_at, expires_at)
+    FD: env_token_id -> сите останати
+    Candidate key: {env_token_id}   PK: env_token_id
+
+R6: EnvSettings(tenant_id, env_name, save_process_history, es_created_at, es_updated_at)
+    FD: (tenant_id, env_name) -> save_process_history, es_created_at, es_updated_at
+    Candidate key: {tenant_id, env_name}   PK: (tenant_id, env_name)
+
+R7: Computers(computer_id, tenant_id, env_name, computer_name, computer_user,
+              computer_ip, computer_os, first_seen, last_seen, sysmon_available)
+    FD: computer_id -> сите останати
+    Candidate key: {computer_id}   PK: computer_id
+
+R8: Processes(process_id, computer_id, pid, process_name, cpu_percent,
+              memory_mb, proc_username, cmdline, proc_timestamp)
+    FD: process_id -> сите останати
+    Candidate key: {process_id}   PK: process_id
+
+R9: ComputerHistory(history_id, computer_id, cpu_usage, ram_usage, disk_usage,
+                     network_sent_mb, network_recv_mb, hist_timestamp)
+    FD: history_id -> сите останати
+    Candidate key: {history_id}   PK: history_id
+
+R10: NetworkConnections(net_conn_id, computer_id, pid, local_address,
+                         remote_address, status, nc_process_name, nc_timestamp)
+    FD: net_conn_id -> сите останати
+    Candidate key: {net_conn_id}   PK: net_conn_id
+
+R11: SecurityAlerts(alert_id, computer_id, alert_type, severity,
+                     description, alert_timestamp, resolved)
+    FD: alert_id -> сите останати
+    Candidate key: {alert_id}   PK: alert_id
+
+R12: SysmonEvents(sysmon_event_id, computer_id, event_id, event_type,
+                   message, sysmon_timestamp, details)
+    FD: sysmon_event_id -> сите останати
+    Candidate key: {sysmon_event_id}   PK: sysmon_event_id
+}}}
+
+'''FD Preservation:''' секоја од оригиналните FD1-FD12 директно се содржи во точно една од новите релации (R1<-FD1, R2<-FD2, R3<-FD3, R4<-FD4, R5<-FD5, R6<-FD6, R7<-FD7, R8<-FD8, R9<-FD9, R10<-FD10, R11<-FD11, R12<-FD12) => сите FD-и се зачувани.
+
+'''Lossless Join проверка''' (за секоја поделба, заедничкиот атрибут мора да е клуч на барем едната страна):
+
+{{{
+R  ∩ R1  = {user_id}                 -> клуч на R1              OK
+R  ∩ R2  = {tenant_id}               -> клуч на R2              OK
+R  ∩ R3  = {user_id, tenant_id}      -> клуч на R3              OK
+R  ∩ R4  = {env_id}                  -> клуч на R4              OK
+R  ∩ R5  = {env_token_id}            -> клуч на R5              OK
+R  ∩ R6  = {tenant_id, env_name}     -> клуч на R6              OK
+R  ∩ R7  = {computer_id}             -> клуч на R7              OK
+R  ∩ R8  = {process_id}              -> клуч на R8              OK
+R  ∩ R9  = {history_id}              -> клуч на R9              OK
+R  ∩ R10 = {net_conn_id}             -> клуч на R10             OK
+R  ∩ R11 = {alert_id}                -> клуч на R11             OK
+R  ∩ R12 = {sysmon_event_id}         -> клуч на R12             OK
+}}}
+
+Сите поделби ја задоволуваат lossless join теоремата (заедничкиот атрибут е клуч на новодобиената релација) => декомпозицијата е lossless.
+
+=== Чекор 2: 2NF -> 3NF (проверка на транзитивни зависности) ===
+
+За секоја R1-R12, се проверува дали не-клучен атрибут зависи од друг не-клучен атрибут (наместо директно од клучот).
+
+{{{
+R1  (Users):               само еден детерминант (user_id) во FD1        -> нема транзитивност
+R2  (Tenants):              само еден детерминант (tenant_id) во FD2      -> нема транзитивност
+R3  (Memberships):          role, membership_created_at немаат меѓусебна
+                             зависност                                    -> нема транзитивност
+R4  (Environments):         env_name, env_created_at зависат само од
+                             env_id, не едно од друго                     -> нема транзитивност
+R5  (EnvTokens):             token, expires_at зависат само од
+                             env_token_id                                 -> нема транзитивност
+R6  (EnvSettings):           save_process_history не зависи од друг
+                             не-клучен атрибут                            -> нема транзитивност
+R7  (Computers):             computer_name, computer_os, ... зависат само
+                             од computer_id (env_name овде е FK, не
+                             детерминира ништо друго)                     -> нема транзитивност
+R8  (Processes):             сите атрибути зависат директно од process_id -> нема транзитивност
+R9  (ComputerHistory):       сите атрибути зависат директно од history_id -> нема транзитивност
+R10 (NetworkConnections):    сите атрибути зависат директно од net_conn_id -> нема транзитивност
+R11 (SecurityAlerts):        сите атрибути зависат директно од alert_id   -> нема транзитивност
+R12 (SysmonEvents):          сите атрибути зависат директно од
+                             sysmon_event_id                              -> нема транзитивност
+
+Заклучок: сите R1-R12 се веќе во 3NF по завршување на Чекор 1.
+}}}
+
+=== Чекор 3: 3NF -> BCNF ===
+
+За BCNF, за секоја нетривијална FD X -> Y во релацијата, X мора да е superkey.
+
+{{{
+R1:  единствена FD е user_id -> ...           user_id е PK -> superkey       BCNF OK
+R2:  единствена FD е tenant_id -> ...         tenant_id е PK -> superkey     BCNF OK
+R3:  единствена FD е (user_id,tenant_id)->... тоа е PK -> superkey           BCNF OK
+R4:  единствена FD е env_id -> ...            env_id е PK -> superkey        BCNF OK
+R5:  единствена FD е env_token_id -> ...      env_token_id е PK -> superkey  BCNF OK
+R6:  единствена FD е (tenant_id,env_name)->.. тоа е PK -> superkey           BCNF OK
+R7:  единствена FD е computer_id -> ...       computer_id е PK -> superkey   BCNF OK
+R8:  единствена FD е process_id -> ...        process_id е PK -> superkey    BCNF OK
+R9:  единствена FD е history_id -> ...        history_id е PK -> superkey    BCNF OK
+R10: единствена FD е net_conn_id -> ...       net_conn_id е PK -> superkey   BCNF OK
+R11: единствена FD е alert_id -> ...          alert_id е PK -> superkey      BCNF OK
+R12: единствена FD е sysmon_event_id -> ...   sysmon_event_id е PK -> superkey BCNF OK
+
+Заклучок: сите R1-R12 се веќе во BCNF.
+Декомпозицијата завршува по еден единствен чекор (1NF -> 2NF), бидејќи
+истовремено ги отстранивме сите парцијални И транзитивни зависности.
+}}}
+
+----
+
+== d) Final Result and Discussion ==
+
+=== Финален нормализиран дизајн ===
+
+{{{
+Users(user_id, user_email, user_name, user_picture, user_created_at)
+Tenants(tenant_id, tenant_name, owner_email, tenant_created_at)
+Memberships(user_id, tenant_id, role, membership_created_at)
+Environments(env_id, tenant_id, env_name, env_created_at)
+EnvTokens(env_token_id, tenant_id, env_name, token, et_created_at, expires_at)
+EnvSettings(tenant_id, env_name, save_process_history, es_created_at, es_updated_at)
+Computers(computer_id, tenant_id, env_name, computer_name, computer_user,
+          computer_ip, computer_os, first_seen, last_seen, sysmon_available)
+Processes(process_id, computer_id, pid, process_name, cpu_percent, memory_mb,
+          proc_username, cmdline, proc_timestamp)
+ComputerHistory(history_id, computer_id, cpu_usage, ram_usage, disk_usage,
+          network_sent_mb, network_recv_mb, hist_timestamp)
+NetworkConnections(net_conn_id, computer_id, pid, local_address, remote_address,
+          status, nc_process_name, nc_timestamp)
+SecurityAlerts(alert_id, computer_id, alert_type, severity, description,
+          alert_timestamp, resolved)
+SysmonEvents(sysmon_event_id, computer_id, event_id, event_type, message,
+          sysmon_timestamp, details)
+}}}
+
+Сите 12 релации се во '''BCNF''', со зачувани функционални зависности (FD preservation) и без губење на податоци при join (lossless join).
+
+=== Дискусија: споредба со дизајнот од Фаза 2 ===
+
+Формалната normalization постапка (стартувајќи од единствена денормализирана универзална релација и attribute closure анализа) резултира со дизајн кој е '''речиси идентичен''' со реалната имплементирана шема од Фаза 2 на проектот. Ова е очекувано и добар знак - потврдува дека физичкиот дизајн од самиот почеток бил веќе близу оптимален (3NF/BCNF), без непотребна редундантност.
+
+Единствената разлика е физичка, не логичка: во реалната имплементација, логичкиот ентитет `Processes` е физички распослан на три табели -
+`computer_processes`, `computer_processes_current` и `computer_processes_history` - истата структура, но со различна намена (тековна снимка наспроти историски архив). Ова е инженерска одлука мотивирана од перформанси и стратегија за архивирање на податоци, а не грешка во нормализацијата - логички, и трите се инстанци на истата нормализирана релема `Processes(process_id, computer_id, pid, process_name, cpu_percent, memory_mb, proc_username, cmdline, proc_timestamp)`.
+
+'''Заклучок:''' дизајнот од Фаза 2 останува дизајнот кој ќе се користи во следните фази на проектот, бидејќи е веќе во BCNF и оваа анализа само формално го потврдува тоа.