Note:

• moderator_id izbrishi nasekade
• klaj unique constraint na (parent_id,topic_title)
• тргни start_at vo moderator - thread.

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

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

Со цел целосно репрезентирање на атрибутите присутни во релациското мапирање, одредени атрибути со исти имиња ќе бидат преименувани. Лесно се заприметуваат таквите атрибути во релацијата 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)

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, заради тоа што единствената ФЗ го вклучува суперклучот и нема транзитивни зависимости.

Нормализација на 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.