Систематска анализа и нормализација на базата Synergymed

---

Целосна и детална анализа на сите ентитети и релации во базата SynergyMed.

Целта е да се докаже, со користење на функционални зависности, покривачи и суперклучеви, дека секоја релација ја задоволува Boyce–Codd Normal Form (BCNF).

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

Првичниот дизајн не ја содржеше релацијата Facility. Наместо тоа, релацијата Inventory беше директно врзана со Company, според следниот модел:

Inventory(id, company_id, code, ...)

Функционалните зависности беа:

• id → {company_id, code, ...}
  
• code → id
  
• company_id → code
  

Последната зависност company_id → code претставува проблем: левата страна (company_id) не е суперклуч, а детерминира друг атрибут. Ова е директно прекршување на условот за BCNF.

Доказ преку покривач:

• (company_id)⁺ = {company_id, code}
  

→ не го покрива целиот сет на атрибути во Inventory.

• Значи company_id не е суперклуч, а сепак детерминира code.
  
• Следува: Inventory не е во BCNF.
  

---

Декомпозиција за да се постигне BCNF

За да се елиминира прекршувањето, воведовме нова релација Facility.
Сега добиваме:

Facility(id, company_id, facility_name, code)
Inventory(id, facility_id, ...)

Нови функционални зависности:

• Во Facility:
  id → {company_id, facility_name, code}
  code → id
  → во двата случаи левата страна е клуч → Facility е во BCNF.
  

• Во Inventory:
  id → {facility_id, ...}
  facility_id е уникатен → facility_id → id
  → левата страна секогаш е клуч → Inventory е во BCNF.
  

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

---

Идентификување на суперклучеви и нивните покривачи после нормализацијата

Users
Суперклуч: id
id⁺ = {id, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth}

Суперклуч: username
username⁺ = {username, id, first_name, last_name, hashed_password, e_mail, gender, date_created, date_of_birth}

Суперклуч: e_mail
e_mail⁺ = {e_mail, id, first_name, last_name, username, hashed_password, gender, date_created, date_of_birth}

Client
Суперклуч: user_id
user_id⁺ = {user_id, is_verified}

Pharmacist
Суперклуч: user_id
user_id⁺ = {user_id}

Admin
Суперклуч: user_id
user_id⁺ = {user_id}

Company
Суперклуч: id
id⁺ = {id, company_name, description, registration_number}

Суперклуч: registration_number
registration_number⁺ = {registration_number, id, company_name, description}

Pharmacy
Суперклуч: company_id
company_id⁺ = {company_id}

Distributor
Суперклуч: company_id
company_id⁺ = {company_id}

Manufacturer
Суперклуч: company_id
company_id⁺ = {company_id}

DeliveryCompany
Суперклуч: company_id
company_id⁺ = {company_id}

Facility
Суперклуч: id
id⁺ = {id, company_id, facility_name, code}

Суперклуч: code
code⁺ = {code, id, company_id, facility_name}

Inventory
Суперклуч: id
id⁺ = {id, facility_id}

Суперклуч: facility_id
facility_id⁺ = {facility_id, id}

Medicine
Суперклуч: id
id⁺ = {id, medicine_name, active_ingredient}

BrandedMedicine
Суперклуч: id
id⁺ = {id, manufacturer_id, name, price, description, dosage_form, strength, origin_country}

BrandedMedicineImage
Суперклуч: id
id⁺ = {id, branded_medicine_id, image}

ContactInformation
Суперклуч: id
id⁺ = {id, phone, address, user_id, facility_id}

ClubCard
Суперклуч: id
id⁺ = {id, user_id, club_program, points}

PaymentMethod
Суперклуч: id
id⁺ = {id, method_name}

Payment
Суперклуч: id
id⁺ = {id, client_id, payment_method_id, payment_date, amount, status}

ClientOrder
Суперклуч: id
id⁺ = {id, client_id, delivery_company_id, payment_id, order_date, expected_arrival_date, status, total_price}

Суперклуч: payment_id
payment_id⁺ = {payment_id, id, client_id, delivery_company_id, order_date, expected_arrival_date, status, total_price}

ShoppingCart
Суперклуч: id
id⁺ = {id, client_id}

SupplyOrder
Суперклуч: id
id⁺ = {id, distributor_id, pharmacy_id, order_date, expected_arrival_date, status}

HealthProfile
Суперклуч: id
id⁺ = {id, client_id, blood_type}

SensitiveClientData
Суперклуч: id
id⁺ = {id, client_id, pharmacist_id, embg, portrait_photo, verification_status}

Prescription
Суперклуч: id
id⁺ = {id, client_id, medicine_id, issued_by, issued_at, valid_to, embg}

ClientOrder_BrandedMedicine
Суперклуч: {order_id, branded_medicine_id}
{order_id, branded_medicine_id}⁺ = {order_id, branded_medicine_id, quantity}

ShoppingCart_BrandedMedicine
Суперклуч: {shopping_cart_id, branded_medicine_id}
{shopping_cart_id, branded_medicine_id}⁺ = {shopping_cart_id, branded_medicine_id, quantity}

SupplyOrder_BrandedMedicine
Суперклуч: {supply_order_id, branded_medicine_id}
{supply_order_id, branded_medicine_id}⁺ = {supply_order_id, branded_medicine_id, quantity}

Inventory_BrandedMedicine
Суперклуч: {inventory_id, branded_medicine_id}
{inventory_id, branded_medicine_id}⁺ = {inventory_id, branded_medicine_id, quantity, last_changed}

Pharmacy_Catalog
Суперклуч: {pharmacy_id, branded_medicine_id}
{pharmacy_id, branded_medicine_id}⁺ = {pharmacy_id, branded_medicine_id}

Distributor_BrandedMedicine
Суперклуч: {distributor_id, branded_medicine_id}
{distributor_id, branded_medicine_id}⁺ = {distributor_id, branded_medicine_id}

Branded_Medicine_InstanceOf_Medicine
Суперклуч: {branded_medicine_id, medicine_id}
{branded_medicine_id, medicine_id}⁺ = {branded_medicine_id, medicine_id}

AllergicReaction_HealthProfile_Medicine
Суперклуч: {health_profile_id, medicine_id}
{health_profile_id, medicine_id}⁺ = {health_profile_id, medicine_id, date_diagnosed, description, severity}

MedicineInteraction
Суперклуч: {medicine_id_1, medicine_id_2}
{medicine_id_1, medicine_id_2}⁺ = {medicine_id_1, medicine_id_2, type, description, severity}

---

Проверка на BCNF

Users
Табелата Users ги чува сите основни податоци за корисниците. Примарен клуч е id, но истовремено username и e_mail се декларирани како уникатни и затоа претставуваат алтернативни клучеви. Функционалните зависности се: id → {first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth}, како и username → id и e_mail → id. Бидејќи секоја зависност има клуч (или кандидат клуч) на левата страна, секој атрибут е целосно функционално зависен од клучот.
Заклучок: Users е во BCNF.

Client, Pharmacist, Admins
Овие три релации се специјализации на Users и го користат user_id како примарен клуч. Во Client, атрибутот is_verified зависи единствено од user_id. Кај Pharmacist и Admins нема дополнителни атрибути освен врската со Users. Затоа, за сите три релации, левата страна на секоја функционална зависност е суперклуч.
Заклучок: сите три релации се во BCNF.

Company
Релацијата Company има примарен клуч id, но и registration_number е уникатен и претставува алтернативен клуч. Зависностите се: id → {company_name, description, registration_number} и registration_number → id. И во двата случаи левата страна е клуч, што гарантира исполнување на BCNF.
Заклучок: Company е во BCNF.

Pharmacy, Distributor, Manufacturer, DeliveryCompany
Овие четири релации се специјализации на Company и го користат company_id како примарен клуч. Немаат сопствени не-клучни атрибути, па единствената зависност е company_id → {}. Како што секогаш левата страна е клуч, сите се во BCNF.

Facility
Табелата Facility има два клуча: id и code (кој е уникатен). Зависностите се id → {company_id, facility_name, code} и code → id. Во двата случаи, левата страна е суперклуч, со што сите атрибути зависат целосно од клучевите.
Заклучок: Facility е во BCNF.

Inventory
Релацијата Inventory има примарен клуч id и дополнително facility_id е уникатен, што создава уште еден алтернативен клуч. Зависностите се id → facility_id и facility_id → id. Ова е двонасочна зависност помеѓу два клуча. Затоа релацијата е во BCNF.

Medicine
Релацијата Medicine има едноставен примарен клуч id. Функционалната зависност е id → {medicine_name, active_ingredient}. Нема дополнителни уникатни ограничувања, па сите атрибути зависат исклучиво од клучот.
Заклучок: Medicine е во BCNF.

BrandedMedicine
Оваа релација го користи id како примарен клуч и содржи повеќе атрибути (manufacturer_id, name, price, description, dosage_form, strength, origin_country). Функционалната зависност е id → {сите останати атрибути}. Производителот може да има повеќе лекови, па manufacturer_id сам по себе не е клуч. Левата страна (id) е клуч, така што BrandedMedicine е во BCNF.

BrandedMedicineImage
Тука примарен клуч е id. Зависноста е id → {branded_medicine_id, image}. Бидејќи секој атрибут зависи од примарниот клуч, релацијата е во BCNF.

ContactInformation
Примарен клуч е id. Дополнителното ограничување бара секоја контакт информација да се однесува на точно еден објект или точно еден корисник. Зависностите се id → {phone, address, user_id, facility_id}. Сите зависности се со клуч на левата страна, па ContactInformation е во BCNF.

ClubCard
Оваа релација има примарен клуч id. Сите останати атрибути (user_id, club_program, points) зависат единствено од овој клуч. Нема транзитивни зависности. Заклучок: ClubCard е во BCNF.

PaymentMethod
Со примарен клуч id, единствената зависност е id → method_name. Затоа релацијата е во BCNF.

Payment
Релацијата Payment има примарен клуч id. Атрибутите client_id, payment_method_id, payment_date, amount, status зависат директно од id. Нема други уникатни ограничувања што би создале транзитивни зависности.
Заклучок: Payment е во BCNF.

ClientOrder
Оваа релација има два кандидат клуча: id (PK) и payment_id (unique). Зависностите се id → {client_id, delivery_company_id, payment_id, order_date, expected_arrival_date, status, total_price} и payment_id → id. Бидејќи во двата случаи левата страна е клуч, релацијата е во BCNF.

ShoppingCart
Примарен клуч е id. Единствената зависност е id → {client_id}. Бидејќи левата страна е клуч, релацијата е во BCNF.

SupplyOrder
Примарен клуч е id. Сите останати атрибути (distributor_id, pharmacy_id, order_date, expected_arrival_date, status) зависат од овој клуч.
Заклучок: SupplyOrder е во BCNF.

HealthProfile
Со примарен клуч id, останатите атрибути (client_id, blood_type) зависат исклучиво од клучот.
Заклучок: HealthProfile е во BCNF.

SensitiveClientData
Примарен клуч е id. Сите останати атрибути (client_id, pharmacist_id, embg, portrait_photo, verification_status) зависат од него.
Затоа релацијата е во BCNF.

Prescription
Примарен клуч е id. Функционалната зависност е id → {client_id, medicine_id, issued_by, issued_at, valid_to, embg}. Иако embg може да се повторува за повеќе рецепти, примарниот клуч секогаш е id, што гарантира BCNF.
Заклучок: Prescription е во BCNF.

ClientOrder_BrandedMedicine
Оваа релација ги поврзува клиентските нарачки со брендираните лекови и содржи и атрибут quantity. Примарен клуч е составен: {order_id, branded_medicine_id}. Функционалната зависност е {order_id, branded_medicine_id} → {quantity}. Покривачот (order_id, branded_medicine_id)⁺ = {order_id, branded_medicine_id, quantity}, што ги содржи сите атрибути на релацијата.
Затоа ClientOrder_BrandedMedicine е во BCNF.

ShoppingCart_BrandedMedicine
Оваа релација ги поврзува кошничките со брендираните лекови и содржи количество. Примарен клуч е {shopping_cart_id, branded_medicine_id}. Функционалната зависност е {shopping_cart_id, branded_medicine_id} → {quantity}. Покривачот на составниот клуч ги опфаќа сите атрибути, па нема парцијални ниту транзитивни зависности. Заклучок: ShoppingCart_BrandedMedicine е во BCNF.

SupplyOrder_BrandedMedicine
Оваа релација поврзува нарачки за снабдување со брендирани лекови и содржи quantity. Примарен клуч е {supply_order_id, branded_medicine_id}. Зависност: {supply_order_id, branded_medicine_id} → {quantity}. Покривачот на составниот клуч ги содржи сите атрибути.
Заклучок: SupplyOrder_BrandedMedicine е во BCNF.

Inventory_BrandedMedicine
Оваа релација поврзува магацини со брендирани лекови и содржи {quantity, last_changed}. Примарен клуч е {inventory_id, branded_medicine_id}. Зависностите се {inventory_id, branded_medicine_id} → {quantity, last_changed}. Покривачот на составниот клуч ги содржи сите атрибути, што значи дека е во BCNF.

Pharmacy_Catalog
Релацијата ги поврзува аптеките со брендирани лекови. Примарен клуч е {pharmacy_id, branded_medicine_id}. Единствената зависност е составниот клуч → {} (нема дополнителни атрибути). Покривачот на клучот ги содржи сите атрибути, па Pharmacy_Catalog е во BCNF.

Distributor_BrandedMedicine
Оваа релација поврзува дистрибутери со брендирани лекови. Примарен клуч е {distributor_id, branded_medicine_id}. Нема дополнителни атрибути, па функционалната зависност е {distributor_id, branded_medicine_id} → {}. Бидејќи клучот ги покрива сите атрибути, Distributor_BrandedMedicine е во BCNF.

Branded_Medicine_InstanceOf_Medicine
Оваа релација ја врзува врската помеѓу брендирани и генерички лекови. Примарен клуч е {branded_medicine_id, medicine_id}. Зависноста е составниот клуч → {}. Покривачот на клучот ги содржи сите атрибути.
Заклучок: Branded_Medicine_InstanceOf_Medicine е во BCNF.

AllergicReaction_HealthProfile_Medicine
Оваа релација ги поврзува здравствените профили со лекови што предизвикуваат алергиски реакции, и содржи дополнителни атрибути {date_diagnosed, description, severity}. Примарен клуч е {health_profile_id, medicine_id}. Функционалната зависност е {health_profile_id, medicine_id} → {date_diagnosed, description, severity}. Покривачот на составниот клуч ги содржи сите атрибути. Затоа AllergicReaction_HealthProfile_Medicine е во BCNF.

MedicineInteraction
Оваа релација ги опишува интеракциите помеѓу парови лекови. Примарен клуч е {medicine_id_1, medicine_id_2}. Дополнителните атрибути се {type, description, severity}. Функционалната зависност е {medicine_id_1, medicine_id_2} → {type, description, severity}. Со оглед дека составниот клуч ги покрива сите атрибути, MedicineInteraction е во BCNF.