Нормализација на базата

Реименување пред стартот со нормализацијата

За подобрување на самиот вокабулар на базата, ќе ги променам имињата на некои од атрибутите само во текот на нормализацијата, за да може подобро да ги одредувам самите функционални зависности од големата релација (ги посочувам табелите каде е извршена промената пред почетокот, за да може понатаму да не ги споменувам, бидејќи изведувањето на функционалните зависности ќе биде без осврнување на веќе постоечкиот ER дијаграм и шема од табели):

• ReportiumUser(name) -> user_name
• ReportiumUser(surname) -> user_surname
• ReportiumUser(created_at) -> user_created_at
• ReportiumUser(is_active) -> is_user_active
• Institution (name) -> institution_name
• Institution (address) -> institution_address
• Doctor(name) -> doctor_name
• Doctor(surname) -> doctor_surname
• Report(created_at) -> report_created_at
• Person(address_of_living) -> address
• Role(description) -> role_description
• MedicalReport_Diagnosis(id) -> r_d_id

Целта на целиот овој процес е гарантирана нормализирана форма преку примена на функционалните зависности и идентификување на клучевите. Најпрвин се дефинира нешто кое според критериумите за добар дизајн е тотално обратното, односно мега релација која ги сочинува сите можни релации/атрибути во неа, и истата преку процесот до нормализирана форма ќе се декомпонира на релации.

Форирање на Мега-Релација

Нека таа релација се означи со R - единечна мега релација (Де-Нормализирана табела), која ги содржи можни ентитети и нивните атрибути.

R(user_id, user_name, user_surname, email, password_hash, is_user_active, user_created_at, profile_id, role_id, username, profile_created_at, log_id, changed_at, change_description,role_name, role_descrioption, session_id, filter_description, searched_at, export_id, file_name, filter_summary, export_format, export_date, person_id, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone, report_id, report_type, summary, report_created_at, institution_id, academic_field, description_of_report, institution_name, institution_address, city, type, year_established, location, resolved, crime_type_id, descriptive_punishment, label, severity_level, punishment_id, value_unit, punishment_type, fine_to_pay, release_date, start_date, end_date, job_role, income_per_month, doctor_id, next_control_date, r_d_id, diagnosis_id, added_on, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity)

Дефинирање на Функционални Зависности

Функционална зависност [x -> y] – вредноста на x го детерминира y, и y е функционално зависно од x. Според она кое може да се види од самата релација, за такви вредности x ќе ги претпоставам оние кои може да се уникатни/стабилни (да претставуваат нешто), како на пример *_id (идентификаторите) … Напомена: Во овој процес на дефинирање на Ф.З не се зема во обѕир постоечката шема, туку се дефинираат самостојно, без да го гледаме она што веќе постои. Тоа се прави со цел да се добијат сите можни релации кои би ја довеле шемата во нормализирана форма.

1. Еден корисник се одликува со низа од атрибути, па она кое би го одредувало секој корисник би било соодветно неговиот user_id, а од десната страна би биле соодветно user_name, user_surname, email, password_hash, is_user_active, user_created_at. Но исто така email се употребува за најава на корисникот, по што тој може да биде исто така еден кандидат клуч за самиот корисник, па ни произлегува уште една функционална зависност која може да се дефинира. Со еден email може да се најде соодветно user_id, кој би ги нашол сите останати атрибути кои зависат од него (email во user_id во user_name/user_surname….)

user_id -> email, user_name, user_surname, password_hash, is_user_active, user_created_at

email -> user_id

2. Една улога би имало соодветно идентификатори role_id, кој ќе идентификува role_name, а тој role_name би бил објаснет со некаков role_description. Ова го размислувам дека секој role_name би бил уникатен, сигурно не би имало 2 улоги со исто име а за различна намена, затоа би можело да развиеме следните 2 ф.з.

role_id -> role_name

role_name -> role_description

3. За секој корисник, според постоечките атрибути би требало да постои некаков си профил, кој ќе се одредува со profile_id, а зависните од него би биле user_id бидејќи треба 1 профил да биде само на 1 корисник, потоа profile_created_at, исто така и role_id бидејќи секој профил би се одликувал со некаква улога во апликацијата и некаков username. Username не одговара да биде соодветно идентификатор, бидејќи нема никаква улога за најава и со тоа не е ставен да биде како уникат, туку само како репрезентативност на самиот кориснички профил, по што не би имале уште една друга функционална зависност. 1 профил одоговара на 1 корисник секогаш, затоа мора да ја додадеме и обратната врска за од корисник во профил.

profile_id -> user_id, role_id, profile_created_at, username

user_id -> profile_id

4. Еден лог би се идентификувал со свој идентификатор log_id, а функционално зависни од него би биле соодветно profile_id (за кој профил би се оденсувало), опис на акцијата (change_description) и кога е создаден истиот во база (changed_at)

log_id -> profile_id, change_description, changed_at

5. Сесија на корисник или пребарување, се одликува соодветно со некој идентификатор session_id, а таа соодветно ги претставува атрибутите како user_id, но бидејќи за еден корисник може да има повеќе сесии, нема да ја вметнеме обратната релација. Таа има соодветно некаков filter_description и кога е пребарана (searched_at)

session_id -> user_id, filter_description, searched_at

6. Една сесија на пребарување би можела да се експортира па би добиле соодветно export_id, кој предводи session_id, како се вика самата датотека, кој е форматот, кога се експортира и некакво сумаризирање. Една сесија може да се експортира повеќе пати, со различен формат на датотека или име и слично, но едно експортирање на сесијата може да биде само на таа сесија. Примарен клуч би било export_id, а forreign би било session_id.

export_id -> session_id, export_format, file_name, filter_summary, export_date

7. Сега доаѓам до Person ентитет кој може да го истранам од самата мега релација, кој би имал 2 кандидат клучеви или embg бидејќи е уникатен за сите корисници, или пак person_id. Сметам дека матичниот број е уникатен, но ако размислиме малку подалеку, потои некаква веројатност да се случи 2 personi кои се регистрирани во апликацијата или би се регистрирале да се од различна земја, па нивните форматирања да се различни, но сепак да дојде до појава на 2 исти матични броеви (уникатни идентификатори за личноста). Па затоа, сметам дека поставувањето на матичниот број би било уникатно ако апликацијата би била направена доменски за македонски извештаи, а на ниво на истата може да се регистрираат било какви персони, по што embg не би бил одредувачи (кандидат клуч). Доклку би имале некој си country_code, тогаш комбиниран клуч {embg, country_code} би било опција за кандидат клуч, но во оваа апликација тоа го нема, па ќе се осврнеме на person_id. Еден персон би се одликувал со name, surname, gender, DOB, is_alive flag, date_of_death, address_of_living, contact_phone и embg би било одредувачко од person_id заедно со наведените атрибути.

person_id -> embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living,contact_phone

8. Ќе продолжиме од оние сигурните ентитети, како што е Insitution. Би се одликувал со уникатен кандидат клуч institution_id, а би содржел institution_name, institution_address, city, type, year_established

institution_id -> institution_name, institution_address, city, type, year_established

9. Сега доаѓа на ред Report. Секој извештај како ентитет, има под-ентитет кој го претставува и има 1-1 релација со некој од 4те можни ентитети. Се креира извештај, се поставува неговиот тип, и тој носи креирање на под-ентите од соодветниот тип кој може да повлекува и некои други ентитети за да биде комплетиран. Тоа значи дека да тргнеме од report_id, може да дојдеме до 1 од 4те под-извештаи (Академски, Медицински, Работен, Криминален), но не може да гарантираме дека од СЕКОЕ report_id може да дојдеме до било кој од овие, бидејќи е во релација 1 <-> 1.

Генерички би било да постои Report кој се одликува со уникатно report_id, и атрибути кои се ф.з од тоа report_id како report_created_at, summary, потоа најважното кое ја прави магијата е report_type и person_id бидејќи секој извештај се однесува на некоја персона во базата на податоци, односно не може да се напише извештај конкретно во оваа апликација кој ќе се однесува на person_id = null.

report_id -> report_type, report_created_at, summary, person_id

10. ПРВИОТ ТИП НА ИЗВЕШТАЈ – КРИМИНАЛЕН ИЗВЕШТАЈ

Секој извештај би имал подтип кој го наследува технички генеричкиот Report со соодветниот тип за него. Во случајов креирањето на Report (report_type=criminal) 1<-> 1 CriminalReport Примарен клуч би бил report_id, кој воедно е и туѓ клуч, а атрибути кои треба да бидат одредени од него би биле location, resolved, за каков тип се однесува crime_type_id и descriptive_punishment, каде објаснето би било самата казна на криминалот. Исто така, еден тип на криминал може да има еден извештај за криминал, не и повеќе, па затоа се извлечени соодветно само тие две функционални зависности. Типот на криминал ќе се одликува со severity_level и label

report_id -> location, resolved, crime_type_id, descriptive_punishment

crime_type_id -> severity_level, label

Тука ни е и самата казна, која има свој идентификатор punishment_id, но бидејќи е осмислено да има повеќе казни за еден криминал, по што во ентитетот на казната би припаднал туѓ клуч кон report_id (за кој извештај се однесува). Исто така, казните се дефинираат посебно за секој report, по што нема доделување на веќе постоечки казни за повеќе криминални извештаи. Таа се одликува со value_unit, punishment_type, fine_to_pay, release_date, каде соодветно во зависност од типот на казна, се одредува вредносната единица и едното од release_date и fine_to_pay е соодветно пополнето, а другото null. Ова би се направило за да се задоволат потребите на апликацијата.

punishment_id -> report_id, value_unit, punishment_type, fine_to_pay, release_date

11. ВТОРИОТ ТИП НА ИЗВЕШТАЈ – АКАДЕМСКИ ИЗВЕШТАЈ

Report (report_type=academic) 1<-> 1 AcademicReport Исто така примарен клуч ќе е report_id, а ќе ги одредува соодветно academic_field, description_of_report и ќе биде поврзано со институција (institution_id) во која се одвивал процесот, соодветно затоа и би постоел единствено institution ентитетот, со тие атрибути.

report_id -> academic_field, institution_id, description_of_report (за institution_id -> …, веќе го дефиниравме во точка 8)

12. ТРЕТИОТ ТИП НА ИЗВЕШТАЈ – РАБОТЕН ИЗВЕШТАЈ

Report (report_type=employment) 1<-> 1 EmploymentReport Атрибутите би биле start_date, end_date, job_role, income_per_month (релативно едноставен)

report_id -> start_date, end_date, job_role, income_per_month

13. ЧЕТВРТИОТ ТИП НА ИЗВЕШТАЈ – МЕДИЦИНСКИ ИЗВЕШТАЈ

Report (report_type=medical) 1<-> 1 MedicalReport Секој извештај има свој уникатен report_id како и другите по горе наведени 3, а како атрибути кои се ф.з од него се соодветниот доктор кој го издал извештајот (референца до него преку doctor_id), потоа од преостанатите во релацијата би имал next_control_date и тоа во MedicalReport.

report_id -> next_control_date, doctor_id

Докторот си е свој ентите кој би имал doctor_name, doctor_surname, specialization, years_of_experience и is_active

doctor_id -> doctor_name, doctor_surname, specialization, years_of_experience, is_active

Преостанува уште неколку атрибути од кои би се создале некои логички Ф.З. Соодветно, има нешто со дијагнози, па така би откриле дека постои некој ентитет Diagnosis, кој има свој идентификатор (diagnosis_id), потоа short_description, therapy, is_chronic и severity.

diagnosis_id -> short_description, therapy, is_chronic, severity

Бидејќи дијагнозите во светот на медицината релативно се повторуваат, веќе се видени повеќето, може да се дозволи меморирање на дијагнози во самата апликациска база кои ќе се РЕИСКОРИСТУВААТ и за други извештаи. Па затоа, мора да се создаде посебна табела, која би имало смисла да има секој ред посебно id, кое ќе одредува комбинација од 2 туѓи клуча – едниот report_id, а другиот назначената дијагноза diagnosis_id, и датум кога е додаден записот во табелата.

r_d_id -> diagnosis_id, report_id, added_on

По процесот на издвојување на функционални зависности се добива следново:

• user_id -> email, user_name, user_surname, password_hash, is_user_active, user_created_at
• email -> user_id
• role_id -> role_name
• role_name -> role_description
• profile_id -> user_id, role_id, profile_created_at, username
• user_id -> profile_id
• log_id -> profile_id, change_description, changed_at
• session_id -> user_id, filter_description, searched_at
• export_id -> session_id, export_format, file_name, filter_summary, export_date
• person_id -> embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone
• institution_id -> institution_name, institution_address, city, type, year_established
• report_id -> report_type, report_created_at, summary, person_id
• report_id -> location, resolved, crime_type_id, descriptive_punishment
• crime_type_id -> severity_level, label
• punishment_id -> report_id, value_unit, punishment_type, fine_to_pay, release_date
• report_id -> academic_field, institution_id, description_of_report
• report_id -> start_date, end_date, job_role, income_per_month
• report_id -> next_control_date, doctor_id
• doctor_id -> doctor_name, doctor_surname, specialization, years_of_experience, is_active
• diagnosis_id -> short_description, therapy, is_chronic, severity
• r_d_id -> diagnosis_id, report_id, added_on

Покривачи и кандидат клуч

Атрибути кои се појавуваат само на левата страна (детерминанти):

{ export_id, punishment_id, r_d_id, log_id }

Атрибути кои се појавуваат само на десната страна (зависни/детерминирани):

{ user_name, user_surname, password_hash, profile_created_at, username, filter_description, searched_at, export_format, file_name, filter_summary, export_date, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone, institution_name, institution_address, city, type, year_established, report_type, report_created_at, summary, location, resolved, descriptive_punishment, severity_level, label, value_unit, punishment_type, fine_to_pay, release_date, academic_field, description_of_report, start_date, end_date, job_role, income_per_month, next_control_date, doctor_name, doctor_surname, specialization, years_of_experience, short_description, therapy, is_chronic, severity, added_on, change_description, changed_at }

Атрибути кои се појавуваат и од левата и од десната страна:

{ user_id, email, role_id, role_name, profile_id, session_id, person_id, institution_id, report_id, crime_type_id, doctor_id, diagnosis_id }

Потребно е да најдеме покривач така што ги поврзуваме само оние вредности кои се одредуваат преку тој атрибут, а не сите вредности кои може да се стигне тргнувајќи од него. Тие се наоѓаат така што се тргнува од оние атрибути кои се само од левата страна.

Покривач - X+ = { сите атрибути што можат да се изведат од X преку дадените функционални зависиности }

{export_id}+ = { export_id, session_id, export_format, file_name, filter_summary, export_date, user_id, filter_description, searched_at, profile_id, email, user_name, user_surname, password_hash, is_user_active, user_created_at, role_id, profile_created_at, username, role_name, role_description } – овој покривач не ги содржи сите атрибути од релацијата R-mega relation.

{punishment_id}+ = { punishment_id, report_id, value_unit, punishment_type, fine_to_pay, release_date, report_type, report_created_at, summary, person_id, location, resolved, crime_type_id, descriptive_punishment, severity_level, label, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone } – овој покривач повторно не ги содржи сите атрибути од релацијата R-mega relation.

• Забелешка за punishment_id: Покривачот на даден атрибут ги содржи само оние атрибути кои логички може да се пристапат преку него, не сите кои го содржат. Поради тоа, не ги вклучувам сите можни извештаи и нивните атрибути, бидејќи преку punishment_id може да се пристапи до criminal report и ентитетите кои се поврзани со овој извештај, се разбира, вклулувајќи ги сите нивните атрибути.

{r_d_id}+ = { r_d_id, diagnosis_id, report_id, added_on, short_description, therapy, is_chronic, severity, report_type, report_created_at, summary, person_id, next_control_date, doctor_id, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone, doctor_name, doctor_surname, specialization, years_of_experience, is_active } – овој покривач повторно не ги содржи сите атрибути од релацијата R-mega relation.

• Забелешка за r_d_id: Како и punishment_id, така и тука се среќавам со истиот случај и решавам дека ќе ги додавам во покривачот сите оние атрибути кои може логички да се пристапат, не и сите можни reports.

{log_id}+ = { log_id, profile_id, change_description, changed_at, user_id, role_id, profile_created_at, username, role_name, role_description, email, user_name, user_surname, password_hash, is_user_active, user_created_at } – овој покривач повторно не ги содржи сите атрибути од релацијата R-mega relation.

Потсетување: Овие функционални зависности се дефинирани по моја логика, без да го гледам ER дијаграмот кој го имам. Е сега, логички размислувам за тоа report_type кое предодредува кој тип на извештај-ентитет ќе се креира. Поради тоа, не одам на варијатната да ги пристапам сите можни атрибути преку report_id, туку си ги додавам оние кои сметам дека навистина би можеле да се пристапат преку покривачот ако се тргне од него. Иако сите атрибути се физички во иста мега-табела R, логички може да се пристапи само до оние кои имаат смисла, што сметам дека е правилно во мојот специфичен случај.

Имајќи ги претходните покривачи, може да видиме дека ниту еден од нив не е комплетен (не ја опфаќа целосната мега релација), за да биде назначен кандидат клуч или супер клуч. Според правилата, може да се започне со комбинирање на истите, за да се добие некој кандидат клуч/супер клуч.

{export_id, punishment_id}+ = { export_id, session_id, export_format, file_name, filter_summary, export_date, user_id, filter_description, searched_at, profile_id, email, user_name, user_surname, password_hash, is_user_active, user_created_at, role_id, profile_created_at, username, role_name, role_description, punishment_id, report_id, value_unit, punishment_type, fine_to_pay, release_date, report_type, report_created_at, summary, person_id, location, resolved, crime_type_id, descriptive_punishment, severity_level, label, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone } – сепак не ги исполнува барањата, односно не ја содржи R-mega relation

{export_id, punishment_id, r_d_id}+ = { export_id, session_id, export_format, file_name, filter_summary, export_date, user_id, filter_description, searched_at, profile_id, email, user_name, user_surname, password_hash, is_user_active, user_created_at, role_id, profile_created_at, username, role_name, role_description, punishment_id, report_id, value_unit, punishment_type, fine_to_pay, release_date, report_type, report_created_at, summary, person_id, location, resolved, crime_type_id, descriptive_punishment, severity_level, label, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone, r_d_id, diagnosis_id, added_on, short_description, therapy, is_chronic, severity, next_control_date, doctor_id, doctor_name, doctor_surname, specialization, years_of_experience, is_active } – иако е доста блиску до R, сепак не ги исполнува барањата, односно не ја содржи целата релација

{export_id, punishment_id, r_d_id, log_id}+ = { export_id, session_id, export_format, file_name, filter_summary, export_date, user_id, filter_description, searched_at, profile_id, email, user_name, user_surname, password_hash, is_user_active, user_created_at, role_id, profile_created_at, username, role_name, role_description, punishment_id, report_id, value_unit, punishment_type, fine_to_pay, release_date, report_type, report_created_at, summary, person_id, location, resolved, crime_type_id, descriptive_punishment, severity_level, label, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone, r_d_id, diagnosis_id, added_on, short_description, therapy, is_chronic, severity, next_control_date, doctor_id, doctor_name, doctor_surname, specialization, years_of_experience, is_active, log_id, change_description, changed_at } – иако е доста блиску до R, сепак не ги исполнува барањата, односно не ја содржи целата релација

Наидуваме на необична ситуација, каде доколку би тргнале од punishment_id и го земеме report_id како општо само report_id, кое може да е наменето за било кое од четирите reports, тогаш без никакви пречки веќе ќе ја имавме целосната релација бидејќи покривачот на punishment_id ќе ги содржеше остатокот од атрибути кои се потребни да добиеме супер клуч со комбинираната релација. Логички изведено, доколку тргнеме од punishment_id, може да стигнеме само до report_id ∈ CriminalReport, што е логично. Па така, не можеме да го земеме report_id кое е покриено од punishment_id за да дојдеме до Academic Report или Employment Report кое ќе го опфати и Institution, што би било решение за супер клуч.

Бидејќи punishment_id → report_id важи само за криминални извештаи, а r_d_id → report_id само за медицински, потребно е да се додаде независно report_id што ќе биде генеричко за сите можни работи кои може да се стигне од него, бидејќи нема потекло, туку е само report_id. Со тоа ги опфаќам останатите атрибути кои фалат. Според „Секој атрибут може да биде дел од кандидат клучот“, може да се заклучи дека додавањето на report_id е правилно, во склоп на правилата за формирање на супер клучот во нашата шема.

span(style="color:#FF0000") complete_closure = {log_id, export_id, punishment_id, r_d_id, report_id}