| Version 13 (modified by , 6 days ago) ( diff ) |
|---|
Нормализација
Во нашата база ги имаме следните 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
facility_id → company_id, facility_name, facility_code
inventory_id → facility_id
branded_medicine_id → company_id, 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, blood_type
inventory_id, branded_medicine_id → quantity, last_changed
client_order_id → user_id, company_id, 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
shopping_cart_id, branded_medicine_id → cart_quantity
supply_order_id → company_id, company_id, supply_order_date, supply_expected_arrival_date, supply_status, facility_id
supply_order_id, branded_medicine_id → supply_quantity
payment_id → user_id, payment_method_id, payment_date, payment_amount, payment_status
prescription_id → user_id, medicine_id, issued_by, issued_at, valid_to, prescription_embg
sensitiveclientdata_id → user_id, user_id, 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, branded_medicine_id → /
company_id, 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 }
