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

Во нашата база ги имаме следните id: company_id, medicine_id, payment_method_id, user_id, facility_id, inventory_id, branded_medicine_id, branded_medicine_image_id, clubcard_id, contactinformation_id, healthprofile_id, supply_order_id, client_order_id, prescription_id, sensitiveclientdata_id, shopping_cart_id, payment_id, medicine_id_1, medicine_id_2.

Следат функционалните зависимости (ФЗ) извлечени директно од дефинираните ентитети (левата страна е PK или композитен PK). За описните полиња кои имаат исто име во повеќе релации ќе ја ставиме името на релацијата или скратено име како превикс — на пример company_description, branded_description, mi_description, allergic_description... — ова е само за јасност во анализата;

Исто така за user_id и client_id во заграда ќе ја пишуваме нивната улога за подобро следење на контекстот на релациите.


**company_id** -> company_name, company_description, registration_number \\
**medicine_id** -> medicine_name, medicine_active_ingredient \\
**payment_method_id** -> payment_method_name \\
**user_id** -> first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified \\
**facility_id** -> company_id, facility_name, facility_code \\
**inventory_id** -> facility_id \\
**medicine_id_1, medicine_id_2** -> mi_type, mi_description, mi_severity \\
**payment_id** -> user_id (улога: client), payment_method_id, payment_date, payment_amount, payment_status \\
**prescription_id** -> user_id (улога: client), medicine_id, issued_by, issued_at, valid_to, prescription_embg \\
**sensitiveclientdata_id** -> user_id (улога: client), user_id (улога: pharmacist), sensitive_embg, portrait_photo, verification_status \\
**shopping_cart_id** -> user_id (улога: client) \\
**supply_order_id** -> company_id (улога: distributor), company_id (улога: pharmacy), supply_order_date, supply_expected_arrival_date, supply_status, facility_id \\
**branded_medicine_id** -> company_id (улога: manufacturer), branded_name, branded_price, branded_description, dosage_form, strength, origin_country \\
**branded_medicine_image_id** -> branded_medicine_id, branded_image, is_main_image \\
**clubcard_id** -> user_id, club_program, points \\
**contactinformation_id** -> phone, contact_address, contact_user_id, contact_facility_id \\
**healthprofile_id** -> user_id (улога: client), blood_type \\
**inventory_id, branded_medicine_id** -> quantity, last_changed \\
**client_order_id** -> user_id (улога: client), company_id (улога: delivery), payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price \\
**client_order_id, branded_medicine_id** -> order_quantity \\
**company_id (улога: pharmacy), branded_medicine_id** -> / \\
**shopping_cart_id, branded_medicine_id** -> cart_quantity \\
**supply_order_id, branded_medicine_id** -> supply_quantity \\
**healthprofile_id, medicine_id** -> date_diagnosed, allergic_description, allergic_severity \\
**branded_medicine_id, medicine_id** -> / \\
**company_id (улога: distributor), branded_medicine_id** -> / \\

**R** = { company_id, company_name, company_description, registration_number, medicine_id, medicine_name, medicine_active_ingredient, payment_method_id, payment_method_name, user_id, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, facility_id, facility_name, facility_code, inventory_id, branded_medicine_id, branded_name, branded_price, branded_description, dosage_form, strength, origin_country, branded_medicine_image_id, branded_image, is_main_image, clubcard_id, club_program, points, contactinformation_id, phone, contact_address, contact_user_id, contact_facility_id, healthprofile_id, blood_type, quantity, last_changed, client_order_id, payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price, order_quantity, shopping_cart_id, cart_quantity, supply_order_id, supply_order_date, supply_expected_arrival_date, supply_status, supply_quantity, payment_date, payment_amount, payment_status, prescription_id, issued_by, issued_at, valid_to, prescription_embg, sensitiveclientdata_id, sensitive_embg, portrait_photo, verification_status, medicine_id_1, medicine_id_2, mi_type, mi_description, mi_severity, date_diagnosed, allergic_description, allergic_severity, company_role_notes…, user_role_notes… }.

**Лево** \\
branded_medicine_image_id \\
client_order_id \\
clubcard_id \\
company_id \\
contactinformation_id \\
facility_id \\
healthprofile_id \\
inventory_id \\
medicine_id \\
medicine_id_1 \\
medicine_id_2 \\
payment_id \\
payment_method_id \\
prescription_id \\
sensitiveclientdata_id \\
shopping_cart_id \\
supply_order_id \\
branded_medicine_id \\
user_id \\

**Лево и десно** \\
company_id \\
medicine_id \\
payment_method_id \\
user_id \\
facility_id \\
inventory_id \\
branded_medicine_id \\
payment_id \\

**Десно** \\
company_name, company_description, registration_number, medicine_name, medicine_active_ingredient, payment_method_name, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified, facility_name, facility_code, branded_name, branded_price, branded_description, dosage_form, strength, origin_country, branded_image, is_main_image, club_program, points, phone, contact_address, contact_user_id, contact_facility_id, blood_type, quantity, last_changed, client_order_date, client_expected_arrival_date, client_order_status, total_price, order_quantity, cart_quantity, supply_order_date, supply_expected_arrival_date, supply_status, supply_quantity, payment_date, payment_amount, payment_status, issued_by, issued_at, valid_to, prescription_embg, sensitive_embg, portrait_photo, verification_status, mi_type, mi_description, mi_severity, date_diagnosed, allergic_description, allergic_severity.

== Покривачи за атрибутите од „Лево“ ==

**branded_medicine_image_id+** = { branded_medicine_image_id, branded_medicine_id, branded_image, is_main_image, company_id, branded_name, branded_price, branded_description, dosage_form, strength, origin_country } - **Не ги содржи сите атрибути.** \\
**client_order_id+** = { client_order_id, user_id, company_id, payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price, payment_method_id, payment_amount, payment_date, payment_status, payment_method_name } - **Не ги содржи сите атрибути.** \\
**clubcard_id+** = { clubcard_id, user_id, club_program, points, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified } - **Не ги содржи сите атрибути.** \\
**company_id+** = { company_id, company_name, company_description, registration_number } - **Не ги содржи сите атрибути.** \\
**contactinformation_id+** = { contactinformation_id, phone, contact_address, facility_id, facility_name, facility_code, company_id, company_name, company_description, registration_number, user_id, first_name, last_name, username } - **Не ги содржи сите атрибути.** \\
**facility_id+** = { facility_id, company_id, facility_name, facility_code, company_name, company_description, registration_number } - **Не ги содржи сите атрибути.** \\
**healthprofile_id+** = { healthprofile_id, user_id, blood_type, user_id, first_name, last_name, username } - **Не ги содржи сите атрибути.** \\
**inventory_id+** = { inventory_id, facility_id, facility_name, facility_code, company_id, company_name, company_description, registration_number } - **Не ги содржи сите атрибути.** \\
**medicine_id+** = { medicine_id, medicine_name, medicine_active_ingredient } - **Не ги содржи сите атрибути.** \\
**medicine_id_1+** = { medicine_id_1 } - **Не ги содржи сите атрибути.** \\
**medicine_id_2+** = { medicine_id_2 } - **Не ги содржи сите атрибути.** \\
**payment_id+** = { payment_id, user_id (улога: client), payment_method_id, payment_date, payment_amount, payment_status, payment_method_name } - **Не ги содржи сите атрибути.** \\
**payment_method_id+** = { payment_method_id, payment_method_name } - **Не ги содржи сите атрибути.** \\
**prescription_id+** = { prescription_id, user_id, medicine_id, issued_by, issued_at, valid_to, prescription_embg, medicine_name, medicine_active_ingredient } - **Не ги содржи сите атрибути.** \\
**sensitiveclientdata_id+** = { sensitiveclientdata_id, sensitive_embg, portrait_photo, verification_status, user_id, first_name, last_name } - **Не ги содржи сите атрибути.** \\
**shopping_cart_id+** = { shopping_cart_id, user_id, first_name, last_name } - **Не ги содржи сите атрибути** \\
**supply_order_id+** = { supply_order_id, supply_order_date, supply_expected_arrival_date, supply_status, facility_id, facility_name, company_id, company_name } - **Не ги содржи сите атрибути.** \\ 
**branded_medicine_id+** = { branded_medicine_id, company_id, branded_name, branded_price, branded_description, dosage_form, strength, origin_country } - **Не ги содржи сите атрибути.** \\ 
**user_id+** = { user_id, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified } - **Не ги содржи сите атрибути.** \\



=== Kомбинирање на покривачите со цел да стигнеме до сите атрибути ===
{ client_order_id }+ додава: user_id, company_id, payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price, payment_method_id, payment_amount, payment_date, payment_status, payment_method_name. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id }+ додава: order_quantity, branded_name, branded_price, branded_description, dosage_form, strength, company_id, origin_country. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id }+ додава: supply_quantity, supply_order_date, supply_expected_arrival_date, supply_status, company_id, company_id, facility_id, facility_name, facility_code, company_name, company_description, registration_number. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id }+ додава: prescription_id, user_id, medicine_id, issued_by, issued_at, valid_to, prescription_embg, medicine_name, medicine_active_ingredient. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id }+ додава: sensitiveclientdata_id, user_id, user_id, sensitive_embg, portrait_photo, verification_status. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2 }+ додава: mi_type, mi_description, mi_severity. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2, contactinformation_id }+ додава: contactinformation_id, phone, contact_address, contact_user_id, contact_facility_id, facility_id, facility_name, facility_code, company_id, company_name, company_description, registration_number. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2, contactinformation_id, healthprofile_id, medicine_id }+ додава: date_diagnosed, allergic_description, allergic_severity. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2, contactinformation_id, healthprofile_id, medicine_id, branded_medicine_image_id }+ додава: branded_medicine_image_id, branded_image, is_main_image, branded_name, branded_price, branded_description, dosage_form, strength, company_id, origin_country. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2, contactinformation_id, healthprofile_id, medicine_id, branded_medicine_image_id, inventory_id }+ додава: quantity, last_changed, inventory_id, facility_id, facility_name, facility_code, company_id, company_name, company_description, registration_number. - **Не ги содржи сите атрибути.**

{ client_order_id, branded_medicine_id, supply_order_id, prescription_id, sensitiveclientdata_id, medicine_id_1, medicine_id_2, contactinformation_id, healthprofile_id, medicine_id, branded_medicine_image_id, inventory_id, shopping_cart_id }+ додава: cart_quantity, shopping_cart_id, user_id. — **Со оваа комбинација унијата ги покрива сите атрибути на R.**

Со оваа последна комбинација добивме унија која ја покрива целата шема R. Задоволена е 1НФ. Меѓутоа, не е задоволена 2НФ поради постоење на парцијални зависимости — некои примери се:
quantity, last_changed зависат од (inventory_id, branded_medicine_id) — делумна зависност ако останат во големата релација.
order_quantity зависи од (client_order_id, branded_medicine_id).
cart_quantity зависи од (shopping_cart_id, branded_medicine_id).
supply_quantity зависи од (supply_order_id, branded_medicine_id).
mi_type/mi_description/mi_severity зависат од (medicine_id_1, medicine_id_2).

== Декомпозиција во 2НФ ==
За да ги отстраниме парцијалните зависиности ги делиме атрибутите во релации каде не-клучните атрибути целосно зависат од нивниот клуч:
company_id → company_name, company_description, registration_number \\
medicine_id → medicine_name, medicine_active_ingredient \\
payment_method_id → payment_method_name \\
user_id → first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified (Client) \\
facility_id → company_id, facility_name, facility_code \\
inventory_id → facility_id \\
branded_medicine_id → company_id (улога: manufacturer), branded_name, branded_price, branded_description, dosage_form, strength, origin_country \\
branded_medicine_image_id → branded_medicine_id, branded_image, is_main_image \\
clubcard_id → user_id, club_program, points \\
contactinformation_id → phone, contact_address, contact_user_id, contact_facility_id \\
healthprofile_id → user_id (улога: client), blood_type \\
inventory_id, branded_medicine_id → quantity, last_changed \\
client_order_id → user_id (улога: client), company_id (улога: delivery), payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price 
client_order_id, branded_medicine_id → order_quantity \\
shopping_cart_id → user_id (улога: client) \\
shopping_cart_id, branded_medicine_id → cart_quantity
supply_order_id → company_id (улога: distributor), company_id (улога: pharmacy), supply_order_date, supply_expected_arrival_date, supply_status, facility_id \\
supply_order_id, branded_medicine_id → supply_quantity \\
payment_id → user_id (улога: client), payment_method_id, payment_date, payment_amount, payment_status \\
prescription_id → user_id (улога: client), medicine_id, issued_by, issued_at, valid_to, prescription_embg \\
sensitiveclientdata_id → user_id (улога: client), user_id (улога: pharmacist), sensitive_embg, portrait_photo, verification_status \\
medicine_id_1, medicine_id_2 → mi_type, mi_description, mi_severity \\
healthprofile_id, medicine_id → date_diagnosed, allergic_description, allergic_severity \\
branded_medicine_id, medicine_id → / \\
company_id (улога: distributor), branded_medicine_id → / \\
company_id (улога: pharmacy), branded_medicine_id → / \\

Со ваква декомпозиција ги извлекуваме следните релации:
R1 { company_id, company_name, company_description, registration_number } - **во BCNF** \\
R2 { medicine_id, medicine_name, medicine_active_ingredient } - **во BCNF** \\
R3 { payment_method_id, payment_method_name } - **во BCNF** \\
R4 { user_id, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked, is_credentials_non_expired, is_enabled, is_verified } - **во BCNF** \\
R5 { facility_id, company_id, facility_name, facility_code } - **во BCNF** \\
R6 { inventory_id, facility_id } - **во BCNF** \\
R7 { branded_medicine_id, company_id (улога: manufacturer), branded_name, branded_price, branded_description, dosage_form, strength, origin_country } - **во BCNF** \\
R8 { branded_medicine_image_id, branded_medicine_id, branded_image, is_main_image } - **во BCNF** \\
R9 { clubcard_id, user_id, club_program, points } - **во BCNF** \\
R10 { contactinformation_id, phone, contact_address, contact_user_id, contact_facility_id } - **во BCNF**
R11 { healthprofile_id, user_id (улога: client), blood_type } - **во BCNF** \\
R12 { inventory_id, branded_medicine_id, quantity, last_changed } - **во BCNF** \\
R13 { client_order_id, user_id (улога: client), company_id (улога: delivery), payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price } - **во BCNF** \\
R14 { client_order_id, branded_medicine_id, order_quantity } - **во BCNF** \\
R15 { shopping_cart_id, user_id (улога: client) } - **во BCNF** \\
R16 { shopping_cart_id, branded_medicine_id, cart_quantity } - **во BCNF** \\
R17 { supply_order_id, company_id (улога: distributor), company_id (улога: pharmacy), supply_order_date, supply_expected_arrival_date, supply_status, facility_id } - **во BCNF** \\
R18 { supply_order_id, branded_medicine_id, supply_quantity } - **во BCNF** \\
R19 { payment_id, user_id (улога: client), payment_method_id, payment_date, payment_amount, payment_status } - **во BCNF** \\
R20 { prescription_id, user_id (улога: client), medicine_id, issued_by, issued_at, valid_to, prescription_embg } - **во BCNF**
R21 { sensitiveclientdata_id, user_id (улога: client), user_id (улога: pharmacist), sensitive_embg, portrait_photo, verification_status } - **во BCNF** \\
R22 { medicine_id_1, medicine_id_2, mi_type, mi_description, mi_severity } - **во BCNF** \\
R23 { healthprofile_id, medicine_id, date_diagnosed, allergic_description, allergic_severity } - **во BCNF** \\
R24 { branded_medicine_id, medicine_id } (Branded_Medicine_InstanceOf_Medicine) - **во BCNF** \\
R25 { company_id (улога: distributor), branded_medicine_id } (Distributor_BrandedMedicine) - **во BCNF** \\
R26 { company_id (улога: pharmacy), branded_medicine_id } (Pharmacy_Catalog) - **во BCNF** \\


Проверка на 3НФ (транзитивни зависиности) и финална декомпозиција.
Сега ги проверуваме R1..R26 за транзитивни зависиности. Ако некаде постои транзитивност, ќе го декомпонираме дополнително.
Проверка на најважните случаи:
R7 (branded_medicine_id -> company_id (улога: manufacturer), …) — company_id е FK кон Company; Company е одвоена во R1, па нема транзитивна зависност во R7. R7 е во BCNF. \\
R5 (facility_id -> company_id, …) — company_… се во R1; нема транзитивност во R5. \\
R19 (payment_id -> payment_method_id, …) и R3 (payment_method_id -> payment_method_name) — се одделени: посебна релација за PaymentMethod (R3), а Payment држи само FK payment_method_id. Нема транзитивност во R19. \\
R12 (inventory_id, branded_medicine_id -> quantity, …) и R6 (inventory_id -> facility_id) — R6 и R12 се одделни; нема транзитивност во R12. \\
По проверка на секоја релација R1..R26 утврдуваме дека ниедна од нив не содржи внатрешни транзитивни зависиности (сите транзитивни детерминанти се извлечени во посебни релации: Company, PaymentMethod, Users, Facility, BrandedMedicine итн.). Следствено, R1..R26 се во 3НФ и BCNF. 

== Финални релации во BCNF ==
**Company** { company_id, company_name, company_description, registration_number } - **BCNF**\\
**Medicine** { medicine_id, medicine_name, medicine_active_ingredient } - **BCNF** \\
**!PaymentMethod** { payment_method_id, payment_method_name } - **BCNF** \\
**Users** { user_id, first_name, last_name, username, hashed_password, e_mail, gender, date_created, date_of_birth, is_account_non_expired, is_account_non_locked,  is_credentials_non_expired, is_enabled, is_verified } - **BCNF** \\
**Facility** { facility_id, company_id, facility_name, facility_code } - **BCNF** \\
**Inventory** { inventory_id, facility_id } - **BCNF** \\
**!BrandedMedicine** { branded_medicine_id, company_id (улога: manufacturer), branded_name, branded_price, branded_description, dosage_form, strength, origin_country } - **BCNF** \\
**!BrandedMedicineImage** { branded_medicine_image_id, branded_medicine_id, branded_image, is_main_image } - **BCNF** \\
**!ClubCard** { clubcard_id, user_id, club_program, points } - **BCNF** \\
**!ContactInformation** { contactinformation_id, phone, contact_address, contact_user_id, contact_facility_id } - **BCNF** \\
**!HealthProfile** { healthprofile_id, user_id (улога: client), blood_type } - **BCNF**\\
**Inventory_BrandedMedicine** { inventory_id, branded_medicine_id, quantity, last_changed } - **BCNF** \\
**!ShoppingCart** { shopping_cart_id, user_id (улога: client) } - **BCNF** \\
**ShoppingCart_BrandedMedicine** { shopping_cart_id, branded_medicine_id, cart_quantity } - **BCNF** \\
**!ClientOrder** { client_order_id, user_id (улога: client), company_id (улога: delivery), payment_id, client_order_date, client_expected_arrival_date, client_order_status, total_price } - **BCNF** \\
**ClientOrder_BrandedMedicine** { client_order_id, branded_medicine_id, order_quantity } - **BCNF**
**Payment** { payment_id, user_id (улога: client), payment_method_id, payment_date, payment_amount, payment_status } - **BCNF** \\
**Prescription** { prescription_id, user_id (улога: client), medicine_id, issued_by, issued_at, valid_to, prescription_embg } - **BCNF** \\
**!SensitiveClientData** { sensitiveclientdata_id, user_id (улога: client), user_id (улога: pharmacist), sensitive_embg, portrait_photo, verification_status } - **BCNF** \\
**!SupplyOrder** { supply_order_id, company_id (улога: distributor), company_id (улога: pharmacy), supply_order_date, supply_expected_arrival_date, supply_status, facility_id } - **BCNF** \\
**SupplyOrder_BrandedMedicine** { supply_order_id, branded_medicine_id, supply_quantity } - **BCNF** \\
**Pharmacy_Catalog** { company_id (улога: pharmacy), branded_medicine_id } - **BCNF** \\
**Distributor_BrandedMedicine** { company_id (улога: distributor), branded_medicine_id } - **BCNF** \\
**Branded_Medicine_InstanceOf_Medicine** { branded_medicine_id, medicine_id } - **BCNF** \\
**!MedicineInteraction** { medicine_id_1, medicine_id_2, mi_type, mi_description, mi_severity } - **BCNF** \\
**AllergicReaction_HealthProfile_Medicine** { healthprofile_id, medicine_id, date_diagnosed, allergic_description, allergic_severity } - **BCNF** \\

Дополнителни релациите Company и Client ги поделив на подтипови кои ги рефернецираат id-то на нивните родители. Оваа поделба ја правиме за подобра контекстуализација, и овие ентитети (освен Client, на кој му е додаден атрибутот is_verified, кој е одземен на самиот user, затпа што има повеќе логика да постои само кај клиент) не соддржат дополнителни аттрибути. Сите во BCNF:

**Admins** { user_id } \\
**Pharmacist** { user_id } \\
**Client** { user_id, is_verified } \\
**!DeliveryCompany** { company_id } \\
**Distributor** { company_id } \\
**Manufacturer** { company_id } \\
**Pharmacy** { company_id } \\