Нормализација

Цел: Да се прикаже процесот на нормализација на базата BlissCore (јога часови, настани, пакети, мерч опрема) со проверка на 1NF, 2NF, фокус на 3NF и BCNF. На крај се наведени финалните релации и применетите ограничувања (UNIQUE, CHECK) и индекси за перформанси.

Поддомени и почетни (денормализирани) релации

За потребите на нормализација се издвоени најбитните поддомени:

Евиденција за часови и резервации R₁ = {class_id, date, start_time, end_time, location, capacity, seats_available, instructor_id, instructor_email, training_id, training_name, user_id, username, email}

Интуитивни зависности:

class_id → date, start_time, end_time, location, capacity, seats_available, instructor_id

instructor_id → instructor_email

training_id → training_name

user_id → username, email

Евиденција за настани и пријави R₂ = {event_id, event_name, date, time, location, user_id, email}

event_id → event_name, date, time, location

user_id → email

Евиденција за пакети и купувања R₃ = {package_id, package_name, price, num_classes, user_id, email}

package_id → package_name, price, num_classes

user_id → email

Евиденција за мерч и купувања R₄ = {merch_id, item_name, price, user_id, email}

merch_id → item_name, price

user_id → email

Сите атрибути се атомски → 1NF е задоволена.

2NF и 3NF / BCNF (декомпозиција)

Проблемите во R₁…R₄ се транзитивни зависности (пример: instructor_id → instructor_email, training_id → training_name, user_id → email) и мешање на фактите од различни ентитети во иста релација. Заради тоа се врши декомпозиција до 3NF/BCNF.

R₁ (Часови и резервации)

Клучеви по домен: class_id (за клас), instructor_id (за инструктор), training_id (за тренинг), user_id (за корисник).

Транзитивни зависности:

class_id → instructor_id и instructor_id → instructor_email

class_id → training_id и training_id → training_name

user_id → email

Декомпозиција (BCNF):

Class(class_id, date, start_time, end_time, location, capacity, seats_available, instructor_id)

Instructor(instructor_id, instructor_email, …)

Training(training_id, training_name, …)

User(user_id, username, email, …)

Class_Includes_Training(class_id, training_id) (M:N ако клас има повеќе тренинзи)

User_Booked_Class(user_id, class_id) (резервации)

Сите транзитивни зависности се извадени во посебни релации → 3NF / BCNF.

R₂ (Настани и пријави)

event_id → event_name, date, time, location

user_id → email

Декомпозиција (BCNF):

Event(event_id, event_name, date, time, location)

User_Event(user_id, event_id) (пријави на корисник на настан)

R₃ (Пакети и купувања)

package_id → package_name, price, num_classes

user_id → email

Декомпозиција (BCNF):

Package(package_id, package_name, price, num_classes)

User_Purchased_Package(user_id, package_id)

R₄ (Мерч и купувања)

merch_id → item_name, price

user_id → email

Декомпозиција (BCNF):

Merch_Items(merch_id, item_name, price, …)

User_Purchased_Merch(user_id, merch_id)

Дополнително (пакети што вклучуваат часови)

Package_Includes_Class(package_id, class_id) (M:N врска)

Финални релации (сите во 3НФ / BCNF)

User(user_id, username, email, password_hash, first_name, last_name)

Instructor(instructor_id, instructor_email, instructor_password_hash, first_name, last_name, biography)

Training(training_id, training_name, description, duration, intensity_level)

Class(class_id, date, start_time, end_time, location, capacity, seats_available, instructor_id)

Event(event_id, event_name, date, time, location)

Package(package_id, package_name, price, num_classes)

Merch_Items(merch_id, item_name, price, …)

Мостови (M:N):

Class_Includes_Training(class_id, training_id)

User_Booked_Class(user_id, class_id)

User_Event(user_id, event_id)

User_Purchased_Package(user_id, package_id)

User_Purchased_Merch(user_id, merch_id)

Package_Includes_Class(package_id, class_id)

Секоја релација има примарен клуч; странските клучеви ги поврзуваат ентитетите и мостовите.

Заклучок

Со декомпозиција по ентитети и мостови ги елиминиравме транзитивните зависности и постигнавме 3NФ/BCNF

Комуникација преку домени

Објаснување

Комуникацијата преку различни домени е овозможена со заеднички идентификатори (foreign keys) и мост-табели што ги поврзуваат ентитетите низ домените. Ова се изведува со JOIN во SQL прашања, кои овозможуваат податоци од повеќе домени да се комбинираат во еден резултат.

Примери на cross-domain прашања

A) „Кои часови ги резервирал корисникот X, со информации за тренинг и инструктор?“

SELECT c.class_id, c.date, c.start_time, c.location,
       t.training_name,
       i.first_name || ' ' || i.last_name AS instructor
FROM "User_Booked_Class" ubc
JOIN "Class" c                 ON c.class_id = ubc.class_id
LEFT JOIN "Class_Includes_Training" cit ON cit.class_id = c.class_id
LEFT JOIN "Training" t         ON t.training_id = cit.training_id
LEFT JOIN "Instructor" i       ON i.instructor_id = c.instructor_id
WHERE ubc.user_id = $1
ORDER BY c.date, c.start_time;

B) „Кои часови ги отклучуваат пакетите што сум ги купил?“

SELECT DISTINCT c.class_id, c.date, c.start_time, c.location, t.training_name
FROM "User_Purchased_Package" upp
JOIN "Package_Includes_Class" pic ON pic.package_id = upp.package_id
JOIN "Class" c                    ON c.class_id = pic.class_id
LEFT JOIN "Class_Includes_Training" cit ON cit.class_id = c.class_id
LEFT JOIN "Training" t            ON t.training_id = cit.training_id
WHERE upp.user_id = $1
ORDER BY c.date, c.start_time;

C) „Кои учесници на настани исто така резервирале клас за тренинг ‘Vinyasa’?“

SELECT DISTINCT u.user_id, u.username, e.event_name, e.date
FROM "User_Event" ue
JOIN "Event" e ON e.event_id = ue.event_id
JOIN "User_Booked_Class" ubc ON ubc.user_id = ue.user_id
JOIN "Class_Includes_Training" cit ON cit.class_id = ubc.class_id
JOIN "Training" t ON t.training_id = cit.training_id
JOIN "User" u ON u.user_id = ue.user_id
WHERE t.training_name = 'Vinyasa'
ORDER BY e.date;

D) „Корисници кои купиле мерч по купување на било кој пакет“

SELECT u.user_id, u.email, COUNT(DISTINCT upm.merch_id) merch_count
FROM "User" u
JOIN "User_Purchased_Package" upp ON upp.user_id = u.user_id
JOIN "User_Purchased_Merch" upm   ON upm.user_id = u.user_id
GROUP BY u.user_id, u.email
ORDER BY merch_count DESC;

Имплементација во Express

Во Express backend-от, овие SQL прашања стануваат controller actions кои се извршуваат и враќаат JSON. Надворешните клучеви и индексите овозможуваат овие JOIN-и да бидат брзи и конзистентни.