| Version 3 (modified by , 4 days ago) ( diff ) |
|---|
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 вредности:
- FD11/FD12/FD13/FD16/FD17 (примитивни клучеви process_id/history_id/net_conn_id/ alert_id/sysmon_event_id) го изедначуваат computer_id во сите нивни редови.
- FD15 (availability_id) го изедначува service_id, а FD14 потоа го носи computer_id.
- FD10 (device_id) го изедначува network_id.
- FD9 (computer_id -> ...) кај сите редови што содржат computer_id (R9..R17) ги претвора b вредностите за атрибутите на компјутерот, вклучувајќи tenant_id, env_name, network_id и group_id, во a симболи.
- FD7 (network_id) и FD8 (group_id) ги пропагираат мрежните и организациските атрибути како a симболи.
- Клучен чекор: бидејќи по трансформациите редовите 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), без непотребна редундантност.
Разлики помеѓу теоретскиот модел и физичката имплементација:
- Раздвојување тековна/историска состојба. Во нормализираниот модел постои
по една логичка релација: 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.
- Странски клучеви наместо изведени вредности. Атрибутите tenant_id, env_name, network_id, group_id, computer_id, service_id - кои во универзалната релација беа изведливи (redundant) - во физичкиот дизајн се реализираат како странски клучеви што ги реализираат BCNF релациите преку референци (нормален, посакуван резултат).
- Само-референца. ComputerGroups.parent_group_id е странски клуч кон истата табела (хиерархија на групи). Тоа не нарушува BCNF - parent_group_id е обичен не-клучен атрибут што зависи само од group_id.
- Алтернативен клуч. NetworkServices има уникатност (computer_id, port, protocol) покрај сурогат клучот service_id - алтернативен candidate key, во склад со BCNF.
Заклучок: имплементираниот v04 дизајн останува дизајнот што ќе се користи во следните фази, бидејќи е веќе во BCNF; оваа анализа само формално го потврдува тоа преку attribute closure и chasing алгоритмот.
Note:
See TracWiki
for help on using the wiki.
