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

Во нашата база ги имаме следните 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 → (junction)
company_id (улога: pharmacy), branded_medicine_id → (junction)

Со ваква декомпозиција ги извлекуваме следните релации:
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) не соддржат дополнителни аттрибути. Сите во BCNF:

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