Note:

• moderator_id izbrishi nasekade
• klaj unique constraint na (parent_id,topic_title)
• тргни start_at vo moderator - thread.
• trgni transitivni relacii kaj project_title i topic_title deka nemat smisla.

Нормализација и Подобрување на дизајн

Функционални зависности(ФЗ)

Со цел целосно репрезентирање на атрибутите присутни во релациското мапирање, одредени атрибути со исти имиња ќе бидат преименувани. Лесно се заприметуваат таквите атрибути во релацијата R, на пр. user(id) -> user_id и permission(name) -> permission_name.

R(
   user: username, description, password, registered_at, email, sex,is_active,name, user_id
  feedback: feedback_submission_type, feedback_created_at, feedback_description 
  submission: submission_id, submission_status, submission_created_at, submission_description,
  blacklisted_user: blacklisted_start_date, blacklisted_end_date, reason,blacklisted_about_user
  tag: tag_name, 
  thread: thread_id, thread_created_at, thread_content 
  topic_thread: guidelines, topic_title, parent_id  
  project_thread: repo_url, project_title, 
  is_moderated_by: moderator_started_at, 
  associated_with: asc_started_at, asc_ended_at, 
  channel: channel_name, channel_description, channel_UUID
  permissions: perm_name, 
  project_roles: pr_name, pr_id, pr_override_type,
  sends_message_in: msg_send_at, msg_content,
) 

Иницијални ФЗ

1. user_id -> username, description, password, registered_at, email, sex,is_active,name

2. username -> user_id, description, password, registered_at, email, sex,is_active,name

3. submission_id -> feedback_submission_type, feedback_created_at, feedback_description

4. submission_id -> submission_status, submission_created_at, submission_description

5. {blacklisted_about_user,user_id, thread_id, blacklisted_start_date} -> blacklisted_end_date, reason

6. {thread_id, user_id} -> moderator_started_at

7. thread_id -> thread_created_at, thread_content

8. thread_id -> guidelines, topic_title, parent_id

9. thread_id -> repo_url, project_title

10. {thread_id, user_id } -> asc_started_at, asc_ended_at

11. channel_UUID -> channel_name,channel_description

12. {thread_id, msg_send_at, user_id } -> msg_content

13. pr_id -> pr_name, pr_override_type

15. parent_id -> thread_id

16. project_title -> repo_url, thread_id, thread_created_at, thread_content

17. {parent_id, topic_title} -> guidelines, thread_id, thread_created_at, thread_content

18. {pr_name, thread_id} -> pr_override_type

19. blacklisted_about_user -> user_id

Изведени ФЗ

2. parent_id -> thread_id

3. parent_id -> thread_created_at, thread_content

4. parent_id -> guidelines, topic_title, parent_id

5. parent_id -> repo_url, project_title

6. blacklisted_about_user -> username, description, password, registered_at, email, sex,is_active,name

Анализа на функционални зависности

За анализата на функционални зависности ќе го користиме методот на LHS/RHS поделба. Според овај метод потребно е да ги поделиме атрибутите во 3 групи, односно атрибути што се среќаваат на лева страна од Функционалните зависности(LHS) и според истата логика атрибути на десна страна и атрибути кои се среќаваат на 2те страни.

LHS:

submission_id, moderator_id,blacklisted_start_date, msg_send_at,pr_id,tag_name,perm_name,blacklisted_about_user, channel_UUID

RHS:

description, password, registered_at, email, sex,is_active,name, feedback_submission_type, feedback_created_at, feedback_description, submission_status, submission_created_at, submission_description,blacklisted_end_date, reason, tag_name,thread_created_at, thread_content, guidelines,repo_url, moderator_started_at, asc_started_at, channel_description, perm_name, pr_override_type, msg_content

LHS & RHS:

user_id,thread_id,username,channel_name,parent_id,project_title,topic_title, pr_name

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

Сегашна нормална форма

Имајќи предвид дека guidelines e повеќе вредносен атрибути, доаѓаме до заклучок дека сегашната имплементација на релација не е ни во 1NF форма.

Декомпозиција на R во 1NF

Со оделување на guidelines во посебна релација R1(thread_id, guideline) и R2(username, thread_id, password...) постигнуваме релациите да се во 1NF.

Според досега разгледаното за R1(thread_id, guideline) ги имаме следниве ФЗ:

1. thread_id -> thread_id
2. guideline -> guideline

Според функционалните зависимости, единствената комбинација на атрибути која го задоволува условот за супер клуч е {thread_id, guideline} , бидејќи {thread_id, guideline}+ = {thread_id, guideline} . Сепак, со цел да оптимизираме операциите на базата (особено внесување и пребарување), ќе воведеме нов сурогат клуч id како примарен клуч. Заедно со него ги додаваме и следниве ФЗ id -> thread_id, guideline .

Ова е практично бидејќи природниот клуч {thread_id, guideline} содржи guideline, која е varchar атрибут со променлива големина. Ако се користеше само природниот клуч, PostgreSQL ќе креираше B-tree индекс врз guideline, кој е поголем по големина и поради тоа побавно ќе се ажурира при DRU операции (потребни се повеќе IO страници за индексирање и податоци). Со воведување на сурогат клуч id како мал фиксно-димензионален атрибут (на пр. serial или bigserial), индексите се помали и операциите се поефикасни.

Со ова R1 ни се совпаѓат со релацијата topic_guidelines, отсега понатаму ќе ја референцираме како таква.

Декомпозиција R1 до BCNF

Според следниве ФЗ за R1:

1. thread_id -> thread_id
2. guideline -> guideline
3. id -> thread_id, guideline

Според тоа што само id ги определува сите атрибути( id+ -> id,thread_id,guideline) следува дека единствен супер клуч е id.

Клуч за оваа релациja би бил id, бидејќи неговиот затварач е id+ = {thread_id, guideline} и е најмал затварач кој ја определува целата релација.

Според 2NF релацијата не смеe да содржи парцијални зависности. Ова веќе е задоволено од ФЗ за R1. Според 3NF релацијата потребно е секоја ФЗ(X->A) да ги задоволи следниве својства:

1. X e супер клуч на R
2. А е примарен атрибут во R.

Според ова релацијата е веќе во 3NF. Според BCNF секоја релација потребно е да го задоволува следново својство:

"За секое нетривијално (X->A) важи дека X е суперклуч на R".

Според тоа што id e клуч на R1, следува дека релацијата е во BCNF.

Декомпозиција R во 2NF

Доколку би ги избрале само атрибутите на левата страна, во таков случај не би можеле да пристапиме до сите ФЗ во кои е вклучен thread_id. Заради тоа ке го избериме thread_id. Со него имаме:

    { 
submission_id
user_id,
blacklisted_start_date
msg_send_at
pr_id
tag_name
perm_name
channel_UUID,
thread_id,
blacklisted_about_user,
msg_send_at
} = { 
submission_id
blacklisted_start_date
msg_send_at
pr_id
tag_name
perm_name
blacklisted_about_user
channel_UUID, 
feedback_submission_type, feedback_created_at, feedback_description,
submission_status, submission_created_at, submission_description ,
channel_name,channel_description, 
pr_name, pr_override_type, 
user_id, 
username, description, password, registered_at, email, sex,is_active,name,
blacklisted_end_date, reason,
moderator_started_at,
thread_created_at, thread_content,
topic_title, parent_id,
repo_url, project_title,
asc_started_at, asc_ended_at,
msg_send_at,msg_content,
thread_id,
pr_override_type
}

R1(user_id,username, description, password, registered_at, email, sex,is_active,name) /done

R2(submission_id,feedback_submission_type, feedback_created_at, feedback_description,submission_status, submission_created_at, submission_description) / done

R3(blacklisted_about_user,user_id, thread_id, blacklisted_start_date,blacklisted_end_date, reason)

R4(thread_id, user_id, moderator_started_at, moderator_started_at) /done

R5(thread_id,thread_created_at, thread_content,topic_title, parent_id,repo_url, project_title) /done

R6(channel_UUID,channel_name,channel_description) /done

R7(pr_id,pr_name, pr_override_type) /done

R8(thread_id, msg_send_at, user_id,msg_content) /done R9(thread_id,user_id,asc_started_at, asc_ended_at) #thread_id, user_id / done

R10(perm_name) / done R11(tag_name) / done

Нормализација на R1 до 3NF

Најпрвин да заприметиме дека оваа релација е устaри релацијата users. За оваа релација важат следниве ФЗс:

1. user_id -> username, description, password, registered_at, email, sex,is_active,name

2. username -> user_id, description, password, registered_at, email, sex,is_active,name

Од тоа што немаме транзитивни зависности во оваа релациja, следува дека R1 e веќе во 3NF.

Нормализација на R1 до BCNF

Oд претходниот чекор согледуваме дека секоја ФЗс вклучува супер клуч, што значи дека релацијта R1 e веќе во BCNF.

Нормализација на R2 до 3NF

Да заприметиме дека единствени ФЗс за оваа релација се:

1. submission_id -> feedback_submission_type, feedback_created_at, feedback_description

2. submission_id -> submission_status, submission_created_at, submission_description

Со користење правилото Унија/Декомпозиција од привалата за изведување на Армстронг добиваме:

1. submission_id -> submission_status, submission_created_at, submission_description, feedback_submission_type, feedback_created_at, feedback_description

Од тоа што немаме транзитивни зависности, следува дека R2 е веќе во 3NF.

Нормализација на R2 до BCNF

Од претходниот чекор, согледаме дека имаме само една ФЗ, што значи дека таа ФЗ мора да содржи суперклуч за релациjaта. Според претходно дискусираното согледуваме дека R2 e веќе во BCNF.

Подобрување на нормализација на R2

Oд демонот на апликацијата согледува дека не секој submission мора да има feedback, тоа значи дека доста инстанци ќе имат null вредности за колоните feedback_submission_type, feedback_created_at, feedback_description. Со цел одбегнување на записи со доста null вредности ќе изведиме 2 нови релации.

1. R21(submission_id,submission_status, submission_created_at, submission_description)
2. R22(submission_id,feedback_submission_type, feedback_created_at, feedback_description)

На овај начин доколку некој submission нема feedback тогаш нема да има запис во R22 и нема да има null вредности во записите. За разлика од претходното решение доста каде што feedback колоните би биле полни со null вредности.

Притоа да согледаме дека R21 e релациjaта submission и R22 е feedback.

Нормализација на R4 до BCNF

Единствена ФЗ присутна во оваа релациja е:

{thread_id, user_id} -> moderator_started_at

R4 е веќе во BCNF, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависимости.

Нормализација на R5 до 3NF

ФЗс пристурни во оваа релација се:

1. thread_id -> thread_created_at, thread_content, topic_title, parent_id, repo_url, project_title
2. project_title -> thread_created_at, thread_content, repo_url
3. {parent_id, topic_title} -> thread_content, thread_created_at
4. parent_id -> thread_created_at, thread_content, topic_title, parent_id, repo_url, project_title, thread_id

R51(project_title, thread_created_at, thread_content, repo_url) R52(parent_id, topic_title,thread_content, thread_created_at) R53(thread_id, project_title, topic_title, parent_id)

Може да согледаме дека thread_created_at и thread_content се повторуваат во R51 и R52, па според тоа може да ги изведиме во една заедничка релација, односно релацијата R53. Според тоа ги добиваме следниве релации:

R51(project_title, repo_url) R52(parent_id, topic_title) R53(thread_id, project_title, topic_title, parent_id, thread_created_at, thread_content)

Од тоа што thread може да е или проект или топик, но не и двете, тоа повлекува дека:

• thread е проект -> topic_title, parent_id се null вредности.
• thread е топик -> project_title е null вредност.

Со цел да избегнеме null вредности и да обезбедиме lossless join ќе го направиме следното:

• R51 ќе додадеме thread_id (за постигнување на lossless join), додека од R53 ќе отстраниме project_title.
• R52 ќе додадеме thread_id (за постигнување на lossless join), додека од R53 ќе остраниме topic_title, parent_id.

Со тоа ги добиваме следниве релации:

R51(project_title, repo_url, thread_id) R52(parent_id, topic_title, thread_id) R53(thread_id, thread_created_at, thread_content)

Нормализација на R51 до BCNF

Eдинстевни ФЗ присутни во оваа релација се:

1. thread_id -> repo_url,project_title
2. project_title -> repo_url, thread_id

Лесно се согледува дека thread_id и project_title се кандидат клучеви, па според тоа бидејќи немаме ниту еден атрибут на левата старна во ФЗти, кои не е кандидат клуч, релацијата е во BCNF.

Притоа да согледаме дека R51 е релацијата project_thread.

Нормализација на R52 до BCNF

Eдинстевни ФЗ присутни во оваа релација се:

1. thread_id -> topic_title,parent_id
2. {topic_title,parent_id} -> thread_id
3. parent_id -> topic_title, thread_id

Бијдеќи parent_id и thread_id се кандидат клучеви( {parent_id, topic_title} е супер клуч) не постои ФЗ, во која левата страна не е супер клуч. Тоа значи дека R52 e во BCNF.

Притоа да согледаме дека R52 е релацијата topic_thread.

Нормализација на R53 до BCNF

Eдинстевни ФЗ присутни во оваа релација се:

1. thread_id -> thread_created_at, thread_content

Бидејќи само thread_id e кандидат клуч, не постои ФЗ во која левата страна не е супер клуч. Според тоа R53 е во BCNF.

Притоа да согледаме дека R53 е релацијата thread.

Нормализација на R6 до BCNF

Единствена ФЗ присутна во оваа релациja е:

channel_UUID -> channel_name,channel_description

R6 е веќе во BCNF, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависимости.

Притоа да согледаме дека R6 е релацијата channel.

Нормализација на R7 до BCNF

Единствена ФЗ присутна во оваа релациja е:

pr_id -> pr_name, pr_override_type

R7 е веќе во BCNF, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависимости.

Да заприметиме дека R7 е уствари project_role.

Нормализација на R8 до BCNF

Единствена ФЗ присутна во оваа релациja е:

{thread_id, msg_send_at, user_id} -> msg_content

R8 е веќе во BCNF, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависимости.

Притоа да согледаме дека R8 е релацијата messages.

Нормализација на R9 до BCNF

Единствена ФЗ присутна во оваа релациja е:

{thread_id,user_id} -> asc_started_at, asc_ended_at

R9 е веќе во BCNF, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависности.

Пртиоа да согледаме дека R9 е релацијата developer_associated_with_project.

Нормализација на R10 до BCNF

Најпрвин да заприметиме дека R10 е уствари релацијата permissions.

Бидејќи немаме транзитивни релации и бидејќи perm_name+ = {perm_name} е супер клуч на релацијата, добиваме дека R1 е во BCNF.

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

Најпрвин да заприметиме декa R11 е уствари релацијата tag. Исто како и кај R10, лесно се согледува дека и оваа релација е во BCNF.