= Phase P5: Normalization = == a) Initial De-normalized Relation and Functional Dependencies == === Global Attribute List (unified relation R) === Сите атрибути од целиот (v04) 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, save_metrics_history, save_network_history, es_created_at, es_updated_at, network_id, network_name, cidr, gateway_ip, vlan, group_id, group_name, group_description, parent_group_id, computer_id, computer_name, computer_user, computer_ip, computer_os, first_seen, last_seen, sysmon_available, device_id, device_name, device_type, device_ip, mac, device_status, 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, nc_pid, local_address, remote_address, conn_status, nc_process_name, nc_timestamp, service_id, service_name, port, protocol, service_status, last_checked, availability_id, is_available, response_time_ms, sa_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, save_metrics_history, save_network_history, es_created_at, es_updated_at FD7: network_id -> tenant_id, env_name, network_name, cidr, gateway_ip, vlan FD8: group_id -> tenant_id, group_name, group_description, parent_group_id FD9: computer_id -> tenant_id, env_name, computer_name, computer_user, computer_ip, computer_os, first_seen, last_seen, sysmon_available, network_id, group_id FD10: device_id -> network_id, device_name, device_type, device_ip, mac, device_status FD11: process_id -> computer_id, pid, process_name, cpu_percent, memory_mb, proc_username, cmdline, proc_timestamp FD12: history_id -> computer_id, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, hist_timestamp FD13: net_conn_id -> computer_id, nc_pid, local_address, remote_address, conn_status, nc_process_name, nc_timestamp FD14: service_id -> computer_id, service_name, port, protocol, service_status, last_checked FD15: availability_id -> service_id, is_available, response_time_ms, sa_timestamp FD16: alert_id -> computer_id, alert_type, severity, description, alert_timestamp, resolved FD17: sysmon_event_id -> computer_id, event_id, event_type, message, sysmon_timestamp, details }}} Дополнителна (алтернативна) зависност: (computer_id, port, protocol) -> service_id (единственост на сервис по компјутер/порта/протокол — алтернативен клуч на NetworkServices). Ова е канонски cover (минимален сет): секоја FD има минимален детерминант на левата страна, нема вишок FD-и што можат да се изведат од другите, и нема redundant атрибути на десните страни. ---- == b) Candidate Keys and Primary Key Selection == === Идентификување примитивни атрибути === Атрибут е примитивен ако никогаш не се појавува на десната страна (RHS) на ниту една FD. Таквите атрибути мора да бидат дел од секој кандидат клуч. Проверка на секој сурогат-идентификатор против сите RHS на FD1-FD17: {{{ Примитивни атрибути (никогаш на RHS): user_id, env_id, env_token_id, device_id, process_id, history_id, net_conn_id, availability_id, alert_id, sysmon_event_id НЕ се примитивни (се појавуваат на RHS, значи изведливи): tenant_id (RHS во FD4, FD5, FD7, FD8, FD9) env_name (RHS во FD4, FD5, FD7, FD9) network_id (RHS во FD9, FD10) group_id (RHS во FD9) computer_id (RHS во FD11..FD14, FD16, FD17) service_id (RHS во FD15) }}} === Пробен клуч и Attribute Closure (K+) === {{{ K = { user_id, env_id, env_token_id, device_id, process_id, history_id, net_conn_id, availability_id, alert_id, sysmon_event_id } }}} Пресметка на closure K+ чекор по чекор: {{{ K+ (почетно) = K FD1 (user_id): + user_email, user_name, user_picture, user_created_at FD4 (env_id): + tenant_id, env_name, env_created_at FD5 (env_token_id): + token, et_created_at, expires_at FD10 (device_id): + network_id, device_name, device_type, device_ip, mac, device_status FD11 (process_id): + computer_id, pid, process_name, cpu_percent, memory_mb, proc_username, cmdline, proc_timestamp FD12 (history_id): + cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, hist_timestamp FD13 (net_conn_id): + nc_pid, local_address, remote_address, conn_status, nc_process_name, nc_timestamp FD15 (availability_id):+ service_id, is_available, response_time_ms, sa_timestamp FD16 (alert_id): + alert_type, severity, description, alert_timestamp, resolved FD17 (sysmon_event_id):+ event_id, event_type, message, sysmon_timestamp, details Сега network_id e во K+ -> FD7: + network_name, cidr, gateway_ip, vlan Сега service_id e во K+ -> FD14: + service_name, port, protocol, service_status, last_checked Сега computer_id e во K+ -> FD9: + computer_name, computer_user, computer_ip, computer_os, first_seen, last_seen, sysmon_available, group_id Сега group_id e во K+ -> FD8: + group_name, group_description, parent_group_id Сега (tenant_id, env_name) се двете во K+ -> FD6: + save_process_history, save_metrics_history, save_network_history, es_created_at, es_updated_at Сега (user_id, tenant_id) се двете во K+ -> FD3: + role, membership_created_at ------------------------------------------------------ K+ = сите атрибути на R => K е superkey ------------------------------------------------------ }}} === Минималност (доказ дека К е Candidate Key) === Сите 10 атрибути во К се примитивни (никогаш не се на RHS), па секој е задолжителен - ниту еден друг атрибут не може да го изведе. Секој од нив е и единствениот детерминант на своите зависни атрибути (пр. само env_token_id го дава token/expires_at; само availability_id ги дава is_available/response_time_ms). Тргнувањето на кој било од нив прави closure-от веднаш да падне под целосниот сет. {{{ => K е минимален superkey => K е Candidate Key Candidate Key = (user_id, env_id, env_token_id, device_id, process_id, history_id, net_conn_id, availability_id, alert_id, sysmon_event_id) }}} === Единственост на кандидат клучот === Бидејќи сите 10 атрибути во К се примитивни (задолжителни во секој candidate key), а нивниот closure веќе го покрива целосно R, не постои друга минимална комбинација атрибути што би била superkey. Следствено, ова е единствениот candidate key на R. {{{ PRIMARY KEY (R) = (user_id, env_id, env_token_id, device_id, process_id, history_id, net_conn_id, availability_id, alert_id, sysmon_event_id) }}} Забелешка: tenant_id, env_name, network_id, group_id, computer_id и service_id намерно НЕ се дел од клучот - тие се изведливи (redundant) атрибути преку FD4/FD7/FD9/FD10/FD14/FD15, и нивно вклучување би ја нарушило минималноста. === Нормална форма на почетната релација === {{{ 1NF: ДА - сите вредности се атомски, нема повторувачки групи/низи. 2NF: НЕ - клучот е составен (10 атрибути), но постојат атрибути што зависат само од ДЕЛ од клучот (парцијални зависности), пример: * user_email, ... зависат само од user_id * token, expires_at, ... зависат само од env_token_id * device_name, mac, ... зависат само од device_id * is_available, ... зависат само од availability_id * ... (аналогно за сите останати FD-и) Заклучок: почетната релација R е само во 1NF. }}} ---- == c) Step-by-Step Decomposition == === Чекор 1: 1NF -> 2NF (отстранување на парцијални зависности) === За секоја FD чија лева страна е proper subset на кандидат клучот, тој детерминант заедно со зависните атрибути се издвојува во нова релација. {{{ R1 Users(user_id, user_email, user_name, user_picture, user_created_at) PK: user_id R2 Tenants(tenant_id, tenant_name, owner_email, tenant_created_at) PK: tenant_id R3 Memberships(user_id, tenant_id, role, membership_created_at) PK: (user_id, tenant_id) R4 Environments(env_id, tenant_id, env_name, env_created_at) PK: env_id R5 EnvTokens(env_token_id, tenant_id, env_name, token, et_created_at, expires_at) PK: env_token_id R6 EnvSettings(tenant_id, env_name, save_process_history, save_metrics_history, save_network_history, es_created_at, es_updated_at) PK: (tenant_id, env_name) R7 Networks(network_id, tenant_id, env_name, network_name, cidr, gateway_ip, vlan) PK: network_id R8 ComputerGroups(group_id, tenant_id, group_name, group_description, parent_group_id) PK: group_id (parent_group_id -> ComputerGroups.group_id, само-референца) R9 Computers(computer_id, tenant_id, env_name, computer_name, computer_user, computer_ip, computer_os, first_seen, last_seen, sysmon_available, network_id, group_id) PK: computer_id R10 NetworkDevices(device_id, network_id, device_name, device_type, device_ip, mac, device_status) PK: device_id R11 Processes(process_id, computer_id, pid, process_name, cpu_percent, memory_mb, proc_username, cmdline, proc_timestamp) PK: process_id R12 ComputerHistory(history_id, computer_id, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, hist_timestamp) PK: history_id R13 NetworkConnections(net_conn_id, computer_id, nc_pid, local_address, remote_address, conn_status, nc_process_name, nc_timestamp) PK: net_conn_id R14 NetworkServices(service_id, computer_id, service_name, port, protocol, service_status, last_checked) PK: service_id Alternate key: (computer_id, port, protocol) R15 ServiceAvailability(availability_id, service_id, is_available, response_time_ms, sa_timestamp) PK: availability_id R16 SecurityAlerts(alert_id, computer_id, alert_type, severity, description, alert_timestamp, resolved) PK: alert_id R17 SysmonEvents(sysmon_event_id, computer_id, event_id, event_type, message, sysmon_timestamp, details) PK: sysmon_event_id }}} FD Preservation: секоја од оригиналните FD1-FD17 директно се содржи во точно една нова релација (R1<-FD1, ..., R17<-FD17), плус алтернативниот клуч на NetworkServices се чува во R14 => сите зависности се зачувани. === Формален доказ за Lossless Join (Chasing алгоритам) === Конструираме матрица на бркање (Chase Matrix): колоните се сите глобални атрибути, редовите се релациите R1-R17. Ако релацијата го содржи атрибутот, во ќелијата се впишува a_j, инаку b_i,j. Ги применуваме FD-ите и ги изедначуваме b со a вредности: 1. FD11/FD12/FD13/FD16/FD17 (примитивни клучеви process_id/history_id/net_conn_id/ alert_id/sysmon_event_id) го изедначуваат computer_id во сите нивни редови. 2. FD15 (availability_id) го изедначува service_id, а FD14 потоа го носи computer_id. 3. FD10 (device_id) го изедначува network_id. 4. FD9 (computer_id -> ...) кај сите редови што содржат computer_id (R9..R17) ги претвора b вредностите за атрибутите на компјутерот, вклучувајќи tenant_id, env_name, network_id и group_id, во a симболи. 5. FD7 (network_id) и FD8 (group_id) ги пропагираат мрежните и организациските атрибути како a симболи. 6. Клучен чекор: бидејќи по трансформациите редовите R4, R5, R6, R7, R9 сега имаат заеднички a_tenant_id и a_env_name, со FD6 овие атрибути (знамињата за историја) се пропагираат како a симболи низ сите нив. Бидејќи почетниот кандидат клуч е составен токму од независните примитивни клучеви, чии релации се спојуваат преку соодветните странски клучеви, на крајот се генерира целосен ред составен само од a симболи. Ова докажува дека декомпозицијата е Lossless Join. === Чекор 2: 2NF -> 3NF (транзитивни зависности) === За секоја R1-R17 се проверува дали не-клучен атрибут зависи од друг не-клучен атрибут. {{{ R1..R5, R7, R9..R17: секоја има единствен детерминант (сурогат/композитен PK); сите не-клучни атрибути зависат директно од целиот клуч. R6 (EnvSettings): трите знамиња зависат само од (tenant_id, env_name), не едно од друго -> нема транзитивност. R8 (ComputerGroups):parent_group_id е FK (не детерминира ништо во R8) -> нема транзитивност. R9 (Computers): network_id/group_id/env_name се FK-ови и не детерминираат други атрибути во R9 -> нема транзитивност. R14 (NetworkServices): постои алтернативен клуч (computer_id,port,protocol), но тоа е клуч (superkey), не не-клучен детерминант -> нема транзитивност. Заклучок: сите R1-R17 се веќе во 3NF по завршување на Чекор 1. }}} === Чекор 3: 3NF -> BCNF === За BCNF, за секоја нетривијална FD X -> Y, X мора да е superkey. {{{ R1..R13, R15..R17: единствената детерминанта во секоја е нејзиниот PK -> superkey. BCNF OK R14 (NetworkServices): две нетривијални зависности - service_id -> ... (PK, superkey) BCNF OK (computer_id,port,protocol) -> service_id (алтерн. клуч, superkey) BCNF OK Заклучок: сите R1-R17 се во BCNF. Декомпозицијата завршува по еден чекор (1NF -> 2NF), бидејќи истовремено ги отстранивме сите парцијални И транзитивни зависности. }}} ---- == d) Final Result and Discussion == === Финален нормализиран дизајн (BCNF) === {{{ 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, save_metrics_history, save_network_history, es_created_at, es_updated_at) Networks(network_id, tenant_id, env_name, network_name, cidr, gateway_ip, vlan) ComputerGroups(group_id, tenant_id, group_name, group_description, parent_group_id) Computers(computer_id, tenant_id, env_name, computer_name, computer_user, computer_ip, computer_os, first_seen, last_seen, sysmon_available, network_id, group_id) NetworkDevices(device_id, network_id, device_name, device_type, device_ip, mac, device_status) 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, nc_pid, local_address, remote_address, conn_status, nc_process_name, nc_timestamp) NetworkServices(service_id, computer_id, service_name, port, protocol, service_status, last_checked) ServiceAvailability(availability_id, service_id, is_available, response_time_ms, sa_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) }}} Сите 17 логички релации се во BCNF, со зачувани функционални зависности (FD preservation) и без губење на податоци при join (lossless join). === Дискусија: споредба со имплементираниот v04 дизајн === Формалната normalization постапка (од единствена денормализирана универзална релација, преку attribute closure и chasing анализа) резултира со дизајн кој е речиси идентичен со реалната имплементирана v04 шема. Ова потврдува дека дизајнот од почеток бил близу оптимален (BCNF), без непотребна редундантност. Разлики помеѓу теоретскиот модел и физичката имплементација: 1. '''Раздвојување тековна/историска состојба.''' Во нормализираниот модел постои по една логичка релација: Processes (R11), ComputerHistory (R12) и NetworkConnections (R13). Во физичкиот дизајн, секоја од нив е поделена на две табели со ИДЕНТИЧНА структура на атрибути: * computer_processes_current / computer_processes_history * computer_history_current / computer_history * network_connections_current / network_connections_history Ова НЕ е денормализација, туку свесна архитектурна оптимизација: табелата `_current` држи само последен snapshot (брз упис/читање, се препишува), додека `_history` расте со милиони записи за ретроспективна анализа. Дали се полни историјата се контролира по околина преку знамињата save_process_history, save_metrics_history и save_network_history во EnvSettings (R6). Логичката структура на атрибутите е идентична и еквивалентна на нормализираните R11/R12/R13. 2. '''Странски клучеви наместо изведени вредности.''' Атрибутите tenant_id, env_name, network_id, group_id, computer_id, service_id - кои во универзалната релација беа изведливи (redundant) - во физичкиот дизајн се реализираат како странски клучеви што ги реализираат BCNF релациите преку референци (нормален, посакуван резултат). 3. '''Само-референца.''' ComputerGroups.parent_group_id е странски клуч кон истата табела (хиерархија на групи). Тоа не нарушува BCNF - parent_group_id е обичен не-клучен атрибут што зависи само од group_id. 4. '''Алтернативен клуч.''' NetworkServices има уникатност (computer_id, port, protocol) покрај сурогат клучот service_id - алтернативен candidate key, во склад со BCNF. Заклучок: имплементираниот v04 дизајн останува дизајнот што ќе се користи во следните фази, бидејќи е веќе во BCNF; оваа анализа само формално го потврдува тоа преку attribute closure и chasing алгоритмот.