Relational Design

Релацискиот модел е добиен со делумна трансформација (partial transformation) на ЕР-моделот: секој ентитет е мапиран во посебна релација, врската M:N (Users–Tenants) е реализирана со посебна релација (Memberships), врските 1:N со надворешен клуч на страната „повеќе", а врската 1:1 (Environments–Env_settings) со заеднички клуч. Делумната трансформација е избрана бидејќи ентитетите имаат јасни, стабилни идентификатори и нема потреба од спојување на релации.

Descriptive representation of the relational schema

Ознаки: примарен клуч е подвлечен (...); надворешен клуч е означен со стрелка (→ табела). Составен надворешен клуч се однесува на групата атрибути во заграда.

• tenants(id, name, owner_email, created_at)
• users(id, email (UNIQUE), name, picture, created_at)
• memberships(user_id → users, tenant_id → tenants, role, created_at)
• environments(id, tenant_id → tenants, name, created_at); UNIQUE(tenant_id, name)
• env_tokens(id, (tenant_id, env_name) → environments(tenant_id, name), token (UNIQUE), created_at, expires_at)
• env_settings(tenant_id, env_name, save_process_history, save_metrics_history, save_network_history, created_at, updated_at); (tenant_id, env_name) → environments(tenant_id, name)
• networks(id, (tenant_id, env_name) → environments(tenant_id, name), name, cidr, gateway_ip, vlan); UNIQUE(tenant_id, name)
• computer_groups(id, tenant_id → tenants, name, description, parent_id → computer_groups); UNIQUE(tenant_id, name)
• computers(id, (tenant_id, env_name) → environments(tenant_id, name), name, user, ip, os, first_seen, last_seen, sysmon_available, network_id → networks, group_id → computer_groups); UNIQUE(tenant_id, name)
• network_devices(id, network_id → networks, name, device_type, ip, mac, status)
• computer_history_current(id, computer_id → computers, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp)
• computer_history(id, computer_id → computers, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp)
• computer_processes_current(id, computer_id → computers, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
• computer_processes_history(id, computer_id → computers, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
• network_connections_current(id, computer_id → computers, pid, local_address, remote_address, status, process_name, timestamp)
• network_connections_history(id, computer_id → computers, pid, local_address, remote_address, status, process_name, timestamp)
• network_services(id, computer_id → computers, service_name, port, protocol, status, last_checked); UNIQUE(computer_id, port, protocol)
• service_availability(id, service_id → network_services, is_available, response_time_ms, timestamp)
• sysmon_events(id, computer_id → computers, event_id, event_type, message, timestamp, details)
• security_alerts(id, computer_id → computers, alert_type, severity, description, timestamp, resolved)

Забелешка: до tenant за computers, env_tokens, env_settings и networks се стигнува транзитивно преку environments (составен клуч tenant_id + env_name), а не со директна врска кон tenants — усогласено со ЕР-моделот (релациите „contains", „issues", „configured_by", „has_networks").

DDL script for creating the database schema and objects

Скриптата ја (ре-)креира схемата project со сите табели, клучеви и ограничувања. Работи и на празна база и на база каде објектите веќе постојат (прво DROP SCHEMA ... CASCADE, потоа повторно креирање). schema_creation.sql​

DML script for filling tables with data

Скриптата ги (ре-)полни сите табели. Работи и на празни табели и на табели што веќе содржат податоци (прво TRUNCATE ... RESTART IDENTITY CASCADE, потоа вметнување). data_load.sql​

Relational diagram

Релацискиот дијаграм е изработен во DBeaver врз схемата project, со crow's-foot нотација, со распоред на табелите усогласен со ЕР-дијаграмот од претходната фаза.