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

Во нашата база ги имаме следните 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 }