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

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

За подобрување на самиот вокабулар на базата, ќе ги променам имињата на некои од атрибутите само во текот на нормализацијата, за да може подобро да ги одредувам самите функционални зависности од големата релација (ги посочувам табелите каде е извршена промената пред почетокот, за да може понатаму да не ги споменувам, бидејќи изведувањето на функционалните зависности ќе биде без осврнување на веќе постоечкиот 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 е правилно, во склоп на правилата за формирање на супер клучот во нашата шема.

complete_closure = { log_id, export_id, punishment_id, r_d_id, report_id }

1NF - Прва Нормална форма

Оваа нормална форма бара секој атрибут во секоја редица да содржи само една вредност, односно атрибутите да не се составени од повеќе вредности. Не смее да има листи, множества или некои типови на колекции кои во суштина даваат повеќе од една информација.

Пример за нешто што не би било во нормална форма е доколку се постават повеќе вредности за contact_phone (“071628936”, “078549987”) во иста колона. Ова би го нарушило правилот на 1NF.

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

2NF - Втора Нормална форма

Втората нормална форма е задоволена ако е задоволена првата нормална форма и дополнително ако нема парцијални функционални зависности. Подобро објаснето, секој атрибут кој не е примарен, треба целосно да биде функционално зависен од примарниот клуч.

Релациите кои се добиваат ги задоволуваат следните 2 услови, за да бидат во 2NF:

• Секој атрибут е целосно функционално зависен од целиот (композитен) клуч кога има истиот постои (доколку е атомски, 2NF е веќе задоволена).

За да ги отстраниме парцијалните зависности потребно е да ги оделиме атрибутите во релации во кои целосно ќе зависат од нивниот клуч:

R1: export_id PK, session_id , export_format, file_name, filter_summary, export_date

R2: log_id PK, profile_id, change_description, changed_at

R3: punishment_id PK, report_id, value_unit, punishment_type, fine_to_pay, release_date

R4: r_d_id PK, diagnosis_id, report_id, added_on

R5: report_id PK, report_type, report_created_at, summary, person_id, location, resolved, crime_type_id, descriptive_punishment, academic_field, institution_id, description_of_report, start_date, end_date, job_role, income_per_month, next_control_date, doctor_id – е во BCNF, но ќе резултира со многу null полиња во еден запис, по што може да употребиме „Supertype-Subtype Specialization“

Бидејќи report_id е клуч кај сите, и тие би биле соодветно диференцирани по тип земајќи го во предвид report_type, ќе може да се издвои еден Supertype и од него да произлегуваат соодветно неколку Subtypes. За правилно да го направиме ова, ќе ги гледаме функционалните зависности:

• R5.1: report_id PK, report_type, report_created_at, summary, person_id
• R5.1.1: report_id PK, location, resolved, crime_type_id, descriptive_punishment
• R5.1.2: report_id PK, academic_field, institution_id, description_of_report
• R5.1.3: report_id PK, start_date, end_date, job_role, income_per_month
• R5.1.4: report_id PK, next_control_date, doctor_id

BCNF комбинирано со 3NF

Пред да продолжиме со било што друго, потребно е соодветно да разгледаме што е добиено во претходните нормализирани форми. Се добиваат R1, R2, R3, R4, R5 ---> R5.1, R5.1.1, R5.1.2, R5.1.3, R5.1.4. Според условите кои ги поставува BCNF, овие релации ја задоволуваат бојс-кодовата нормална форма, а тоа би значело дека исто времено ја задоволуваат и 3NF.

Останѕува уште: R6: rest_of_the_attributes_relation = { report_id, export_id, log_id, punishment_id, r_d_id, user_id, user_name, user_surname, email, password_hash, is_user_active, user_created_at, role_id, username, profile_created_at, role_name, role_descrioption, filter_description, searched_at, 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, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – во ниту еден случај не е во BCNF

Гарантиравме дека role_name е уникатно, па затоа може да издвоиме релација

Ф.З кои ги гледаме:

• role_id -> role_name
• role_name -> role_description

R6.1: role_id PK, role_name UNIQUE, role_description – во BCNF

R6.2: role_id U R6-initial -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, user_name, user_surname, email, password_hash, is_user_active, user_created_at, role_id, username, profile_created_at, filter_description, searched_at, 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, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

Сега може со корисник да одиме, каде ги гледаме следните Ф.З

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

Бидејќи email е во склоп со функционалната зависност, но тој е исто така уникат, може да кажеме дека имаме алтернативен клуч, по кој исто така може да ги пребаруваме самите записи.

R6.2.1: user_id PK, email UNIQUE, user_name, user_surname, password_hash, is_user_active, user_created_at – во BCNF

R6.2.2: user_id U R6.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, username, profile_created_at, filter_description, searched_at, 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, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

Потоа тргнуваме со следната функционална зависност а тоа е

• profile_id -> user_id, role_id, profile_created_at, username

R6.2.2.1: profile_id PK, user_id UNIQUE FK→R6.2.1, role_id FK→R6.1, profile_created_at, username – во BCNF

R6.2.2.2: profile_id U R6.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, filter_description, searched_at, 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, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

Еве уште една функционална зависност поради која се нарушува правилото за BCNF

• session_id → user_id, filter_description, searched_at

R6.2.2.2.1: session_id PK, user_id FK→ R6.2.1, filter_description, searched_at – во BCNF

R 6.2.2.2.2: session_id U R6.2.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, 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, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

Следна Ф.З е

• person_id → embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone

R6.2.2.2.2.1: person_id PK, embg, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone – во BCNF

R 6.2.2.2.2.2: person_id U R6.2.2.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, institution_name, institution_address, city, type, year_established, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

Потоа следува

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

R6.2.2.2.2.2.1: institution_id PK, institution_name, institution_address, city, type, year_established – во BCNF

R 6.2.2.2.2.2: institution_id U R6.2.2.2.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, label, severity_level, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

• Функционална зависност crime_type_id -> severity_level, label

R6.2.2.2.2.2.2.1: crime_type_id PK, severity_level, label – во BCNF

R 6.2.2.2.2.2.2.2: crime_type_id U R6.2.2.2.2.2.2-> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, doctor_name, doctor_surname, specialization, years_of_experience, is_active, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

• Ф.З doctor_id -> doctor_name, doctor_surname, specialization, years_of_experience, is_active

R6.2.2.2.2.2.2.2.1: doctor_id PK, doctor_name, doctor_surname, specialization, years_of_experience, is_active – во BCNF

R 6.2.2.2.2.2.2.2.2: crime_type_id U R6.2.2.2.2.2.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id, short_description, therapy, is_chronic, severity } – сеуште не е во BCNF

• Ф.З diagnosis_id -> short_description, therapy, is_chronic, severity

R6.2.2.2.2.2.2.2.2.1: diagnosis_id PK, short_description, therapy, is_chronic, severity – во BCNF

R 6.2.2.2.2.2.2.2.2.2: crime_type_id U R6.2.2.2.2.2.2.2.2 -> { report_id, export_id, log_id, punishment_id, r_d_id, user_id, role_id} – оваа релација е непотребна бидејќи содржи само клучеви, кои си имаат веќе свои посебни релации, затоа истата не би била додадена во самата шема од табели.

Издвоени сите релации кои се добиваат на крајот од нормализацијата:

EXPORT(export_id PK, session_id FK→SESSION, export_format, file_name, filter_summary, export_date)

LOG(log_id PK, profile_id FK→PROFILE, changed_at, change_description)

PUNISHMENT(punishment_id PK, report_id FK→CRIMINAL_REPORT, value_unit, punishment_type, fine_to_pay, release_date)

MEDICALREPORT_DIAGNOSIS(r_d_id PK, report_id FK→MEDICAL_REPORT, diagnosis_id FK→DIAGNOSIS, added_on)

REPORT(report_id PK, report_type, report_created_at, summary, person_id FK→PERSON)

CRIME_REPORT(report_id PK/FK→REPORT, location, resolved, crime_type_id FK→CRIME_TYPE, descriptive_punishment)

ACADEMIC_REPORT(report_id PK/FK→REPORT, academic_field, institution_id FK→INSTITUTION, description_of_report)

EMPLOYMENT_REPORT(report_id PK/FK→REPORT, start_date, end_date, job_role, income_per_month)

MEDICAL_REPORT(report_id PK/FK→REPORT, next_control_date, doctor_id FK→DOCTOR)

USER(user_id PK, email UNIQUE, user_name, user_surname, password_hash, is_user_active, user_created_at)

PROFILE(profile_id PK, user_id UNIQUE FK→USER, role_id FK→ROLE, profile_created_at, username)

ROLE(role_id PK, role_name UNIQUE, role_description) (or split ROLE/ROLE_NAME if role_name isn’t unique)

SESSION(session_id PK, user_id FK→USER, filter_description, searched_at)

PERSON(person_id PK, embg UNIQUE, name, surname, gender, date_of_birth, is_alive, date_of_death, address_of_living, contact_phone)

INSTITUTION(institution_id PK, institution_name, institution_address, city, type, year_established)

CRIME_TYPE(crime_type_id PK, label, severity_level)

DOCTOR(doctor_id PK, doctor_name, doctor_surname, specialization, years_of_experience, is_active)

DIAGNOSIS(diagnosis_id PK, short_description, therapy, is_chronic, severity)

Заклучок

Процесот на нормализација на базата на податоци Reportium беше успешно спроведен преку систематска примена на теоријата на релациони бази на податоци. Почнувајќи од единечна мега-релација R која ги содржеше сите можни атрибути од системот, преку детална анализа на функционалните зависности, идентификување на кандидат клучеви и примена на алгоритмот за декомпозиција, успешно се постигна Бојс-Кодова нормална форма (BCNF) за сите релации во шемата.

Финалната нормализирана шема се состои од 18 логички поврзани релации кои го опфаќаат целосниот домен на системот Reportium.

Секоја релација има јасно дефиниран примарен клуч, не содржи парцијални или транзитивни функционални зависности, и ги задоволува сите критериуми за добар дизајн според теоријата на релациони бази на податоци. Декомпозицијата е извршена lossless (без загуба на податоци) и dependency-preserving (со зачувување на функционалните зависности).

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