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

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

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