Нормализација и подобрувања на дизајнот

Во оваа секција е прикажана нормализацијата на целокупниот ER модел до трета нормална форма (3NF), за да се отстранат зависностите, се избегне дуплирање на податоци и да се подобри интегритетот.

normalized_ddl_schema.sql​

Објаснување на нормализацијата

Нормализацијата беше извршена како анализа на целиот модел составен од 12 табели, при што се разгледуваа зависностите помеѓу табелите, а не само во рамки на секоја одделно. На тој начин се овозможи откривање на заеднички функционални зависности и елиминирање на дуплирани ентитети и атрибути.

Клучни подобрувања:

• Централизација на статусите преку единствена табела statuses, користена и во subscription и notifications.
• Централизација на земјите преку табелата countries, споделена од actors и locations.
• Елиминирање на транзитивни зависности преку атрибути како role_id, country_code, и type_code.
• Генерација на дополнителни референтни табели како actor_types, roles, и statuses со foreign keys за интегритет.

Со оваа нормализација до 3NF, се подобрува конзистентноста, се намалува дуплирањето на податоци и се поедноставува одржувањето на базата.

actors(actor_id, actor_code, actor_name, country_code, type_code)

Функционални зависности: actor_id → actor_code, actor_name, country_code, type_code country_code → country_name type_code → type_description

1NF: Атомски вредности / 2NF: Сите атрибути зависат од примарниот клуч / 3NF: Постои транзитивна зависност → actor_id → country_code → country_name

Декомпозиција: actors(actor_id, actor_code, actor_name, country_code, type_code) countries(country_code, country_name) actor_types(type_code, type_description)

locations(location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id)

Функционални зависности: location_id → full_name, country_code, adm1_code, latitude, longitude, feature_id country_code → country_name

1NF: Атомски вредности / 2NF: Сите атрибути зависат од примарниот клуч / 3NF: Транзитивна зависност преку country_code

Декомпозиција: locations(location_id, full_name, country_code, adm1_code, latitude, longitude, feature_id) countries(country_code, country_name)

users(user_id, first_name, last_name, email, password_hash, role_id, last_login_date, register_date)

Функционални зависности: user_id → first_name, last_name, email, password_hash, role_id, last_login_date, register_date role_id → role_name

1NF: Атомски вредности / 2NF: Зависности од целиот клуч / 3NF: Транзитивна зависност преку role_id

Декомпозиција: users(user_id, first_name, last_name, email, password_hash, role_id, last_login_date, register_date) roles(role_id, role_name)

subscription(subscription_id, user_id, plan_id, start_date, end_date, status)

Функционални зависности: subscription_id → user_id, plan_id, start_date, end_date, status status → status_description

1NF: Атомски вредности / 2NF: Сите зависности од клучот / 3NF: Транзитивна зависност преку status

Декомпозиција: subscription(subscription_id, user_id, plan_id, start_date, end_date, status_code) statuses(status_code, status_description)

notifications(notification_id, user_id, event_id, notification_date, status)

Функционални зависности: notification_id → user_id, event_id, notification_date, status status → status_description

1NF: Атомски вредности / 2NF: Зависности од целиот клуч / 3NF: Транзитивна зависност преку status

Декомпозиција: notifications(notification_id, user_id, event_id, notification_date, status_code) statuses(status_code, status_description)

predictions(prediction_id, event_id, actor_id, predicted_date, prediction_type, confidence_score)

Функционални зависности: prediction_id → event_id, actor_id, predicted_date, prediction_type, confidence_score

1NF: Атомски вредности / 2NF: Нема составен клуч, сите зависности се од примарниот клуч / 3NF: Нема транзитивни зависности

Декомпозиција: / (нема потреба)

conflict_risk(conflict_id, actor1_id, actor2_id, risk_score, predicted_date, description)

Функционални зависности: conflict_id → actor1_id, actor2_id, risk_score, predicted_date, description

1NF: Атомски вредности / 2NF: Сите зависности се од примарниот клуч / 3NF: Нема транзитивни зависности

Декомпозиција: / (нема потреба)

event_analytics(analytics_id, actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description)

Функционални зависности: analytics_id → actor_id, event_id, date, conflict_coefficient, mentions_count, avg_tone, description

1NF: Атомски вредности / 2NF: Сите зависности се од клучот / 3NF: Нема транзитивни зависности

Декомпозиција: / (нема потреба)

Нови релации по нормализација

CREATE TABLE countries (

country_code VARCHAR(5) PRIMARY KEY,

country_name VARCHAR(100)

);

CREATE TABLE actor_types (

type_code VARCHAR(10) PRIMARY KEY,

type_description VARCHAR(100)

);

CREATE TABLE roles (

role_id SERIAL PRIMARY KEY,

role_name VARCHAR(50) NOT NULL

);

CREATE TABLE statuses (

status_code VARCHAR(20) PRIMARY KEY,

status_description TEXT

);