= Normalization =

This page presents the normalization of the BEST Skopje Hub relational model from one unified de-normalized relation through 1NF, 2NF, 3NF and BCNF.

== De-normalized database form ==

=== Unified de-normalized relation ===

{{{
U(
    announced_at, application_id, application_status, application_type,
    as_id, as_start_time, at_description, at_id, at_name,
    attendance_status, birthday, c_id, c_name, cooperation_type,
    ee_end_time, ee_id, ee_start_time, ee_title, ef_no, eft_id, eft_name,
    es_no, es_start_time, es_title, est_description, est_id, est_name,
    et_description, et_id, et_name, email, faculty, field_of_studies,
    first_name, fm_id, l_id, l_name, last_name, mandate_year,
    mentor_index, motivational_letter, ms_id, ms_name, msr_id,
    naframa_reference, needed_no_volunteers, of_id, of_title,
    phone_number, reason, rsvp_at, rsvp_status, student_index,
    valid_from, valid_to, volunteer_role, year_of_studies
)
}}}

=== Functional dependencies ===

The global functional dependencies for `U` are:

{{{
student_index -> first_name, last_name, faculty, field_of_studies,
                 year_of_studies, birthday, email, phone_number, mentor_index

ms_id -> ms_name
ms_name -> ms_id
msr_id -> student_index, ms_id, valid_from, valid_to

of_id -> of_title
of_title -> of_id
fm_id -> student_index, of_id, mandate_year

at_id -> at_name, at_description
at_name -> at_id
l_id -> l_name
l_name -> l_id
et_id -> et_name, et_description
et_name -> et_id
est_id -> est_name, est_description
est_name -> est_id
eft_id -> eft_name
eft_name -> eft_id

as_id -> at_id, l_id, as_start_time, ee_id
ee_id -> et_id, ee_title, ee_start_time, ee_end_time
(ee_id, es_no) -> est_id, l_id, es_start_time, es_title,
                     needed_no_volunteers
(ee_id, ef_no) -> eft_id

application_id -> student_index, ee_id, ef_no, motivational_letter,
                  application_type, application_status
(student_index, as_id) -> rsvp_status, rsvp_at, attendance_status,
                          reason, announced_at
(student_index, ee_id, es_no) -> volunteer_role
c_id -> c_name, naframa_reference
(c_id, ee_id) -> cooperation_type
}}}

=== Canonical cover ===

The canonical cover `Fc` is:

{{{
student_index -> first_name
student_index -> last_name
student_index -> faculty
student_index -> field_of_studies
student_index -> year_of_studies
student_index -> birthday
student_index -> email
student_index -> phone_number
student_index -> mentor_index
ms_id -> ms_name
ms_name -> ms_id
msr_id -> student_index
msr_id -> ms_id
msr_id -> valid_from
msr_id -> valid_to
of_id -> of_title
of_title -> of_id
fm_id -> student_index
fm_id -> of_id
fm_id -> mandate_year
at_id -> at_name
at_id -> at_description
at_name -> at_id
l_id -> l_name
l_name -> l_id
et_id -> et_name
et_id -> et_description
et_name -> et_id
est_id -> est_name
est_id -> est_description
est_name -> est_id
eft_id -> eft_name
eft_name -> eft_id
as_id -> at_id
as_id -> l_id
as_id -> as_start_time
as_id -> ee_id
ee_id -> et_id
ee_id -> ee_title
ee_id -> ee_start_time
ee_id -> ee_end_time
(ee_id, es_no) -> est_id
(ee_id, es_no) -> l_id
(ee_id, es_no) -> es_start_time
(ee_id, es_no) -> es_title
(ee_id, es_no) -> needed_no_volunteers
(ee_id, ef_no) -> eft_id
application_id -> student_index
application_id -> ee_id
application_id -> ef_no
application_id -> motivational_letter
application_id -> application_type
application_id -> application_status
(student_index, as_id) -> rsvp_status
(student_index, as_id) -> rsvp_at
(student_index, as_id) -> attendance_status
(student_index, as_id) -> reason
(student_index, as_id) -> announced_at
(student_index, ee_id, es_no) -> volunteer_role
c_id -> c_name
c_id -> naframa_reference
(c_id, ee_id) -> cooperation_type
}}}

`|Fc| = 62`. No left-hand-side attribute is extraneous and no dependency is redundant.

=== Candidate keys and primary key ===

{{{
CandidateKeys(U) = {
    {application_id, as_id, c_id, fm_id, msr_id, es_no}
}

PrimaryKey(U) =
    {application_id, as_id, c_id, fm_id, msr_id, es_no}
}}}

The six attributes in the displayed key do not occur on the right-hand side of any dependency in `Fc`, so every candidate key must contain all six. Their closure contains all 57 attributes of `U`. Removing any one of them leaves that attribute outside the closure, which proves that the set is minimal and that no other candidate key exists.

== 1NF decomposition ==

All 57 attributes of `U` are atomic. Multiple locations are represented by separate `(ee_id, l_id)` combinations rather than by a list in one attribute.

Therefore `U` is already in 1NF and no decomposition is needed at this step.

`U` is not in 2NF. Partial dependencies include:

{{{
application_id -> motivational_letter
msr_id -> valid_from
fm_id -> mandate_year
as_id -> as_start_time
c_id -> c_name
}}}

== 2NF decomposition ==

The key of `U` is `(application_id, as_id, c_id, fm_id, msr_id, es_no)`. The dependencies shown below have determinants that are proper subsets of this key, so they are the starting points for the 2NF decomposition.

The names follow the project terminology. The suffix `_2NF` marks an intermediate relation that still contains transitively determined descriptive attributes and will be divided further in the 3NF step.

|| Intermediate relation || Key || Functional dependencies that apply ||
|| `applications_2NF` || `application_id` || `application_id` determines the application, student, event edition and event function data ||
|| `membership_stage_records_2NF` || `msr_id` || `msr_id -> student_index, ms_id, valid_from, valid_to`, with `ms_id -> ms_name` ||
|| `function_mandates_2NF` || `fm_id` || `fm_id -> student_index, of_id, mandate_year`, with `of_id -> of_title` ||
|| `activity_sessions_2NF` || `as_id` || `as_id -> at_id, l_id, as_start_time, ee_id`, with the activity-type and location descriptions ||
|| `event_editions_2NF` || `ee_id` || `ee_id -> et_id, ee_title, ee_start_time, ee_end_time`, with the event-type description ||
|| `event_sessions_2NF` || `(ee_id, es_no)` || the key determines the event-session data, event-session-type description and location name ||
|| `event_functions_2NF` || `(ee_id, ef_no)` || `(ee_id, ef_no) -> eft_id`, with `eft_id -> eft_name` ||
|| `participation` || `(student_index, as_id)` || the key determines `rsvp_status`, `rsvp_at` and `attendance_status` ||
|| `announces_absence` || `(student_index, as_id)` || the key determines `reason` and `announced_at` ||
|| `volunteers` || `(student_index, ee_id, es_no)` || the key determines `volunteer_role` ||
|| `companies` || `c_id` || `c_id -> c_name, naframa_reference` ||
|| `cooperation` || `(c_id, ee_id)` || the key determines `cooperation_type` ||

Each extracted relation contains its displayed determinant and the attributes dependent on it. The determinant is the complete key of the extracted relation, while it remains in the other relation as the common join attribute. This removes the partial dependencies from `U`; the resulting intermediate relations are in 2NF.

Remaining transitive dependencies:

{{{
application_id -> student_index -> first_name
msr_id -> ms_id -> ms_name
fm_id -> of_id -> of_title
as_id -> at_id -> at_name
ee_id -> et_id -> et_name
(ee_id, es_no) -> est_id -> est_name
(ee_id, es_no) -> l_id -> l_name
(ee_id, ef_no) -> eft_id -> eft_name
}}}

== 3NF decomposition ==

The `_2NF` relations still contain descriptive attributes reached through another non-key attribute. For example, `msr_id -> ms_id` and `ms_id -> ms_name` place `ms_name` transitively under `msr_id`. The following decompositions remove those transitive dependencies:

|| Relation analyzed || Dependency causing the 3NF issue || Relations obtained || Keys after decomposition ||
|| `applications_2NF` || `application_id -> student_index -> student data` || `applications`, `students` || `application_id`; `student_index` ||
|| `membership_stage_records_2NF` || `msr_id -> ms_id -> ms_name` || `membership_stage_records`, `membership_stages` || `msr_id`; `ms_id` and `ms_name` ||
|| `function_mandates_2NF` || `fm_id -> of_id -> of_title` || `function_mandates`, `organizational_functions` || `fm_id`; `of_id` and `of_title` ||
|| `activity_sessions_2NF` || `as_id -> at_id -> at_name, at_description`; `as_id -> l_id -> l_name` || `activity_sessions`, `activity_types`, `locations` || `as_id`; `at_id` and `at_name`; `l_id` and `l_name` ||
|| `event_editions_2NF` || `ee_id -> et_id -> et_name, et_description` || `event_editions`, `event_types` || `ee_id`; `et_id` and `et_name` ||
|| `event_sessions_2NF` || `(ee_id, es_no) -> est_id -> est_name, est_description`; `(ee_id, es_no) -> l_id -> l_name` || `event_sessions`, `event_session_types`, `locations` || `(ee_id, es_no)`; `est_id` and `est_name`; `l_id` and `l_name` ||
|| `event_functions_2NF` || `(ee_id, ef_no) -> eft_id -> eft_name` || `event_functions`, `event_function_types` || `(ee_id, ef_no)`; `eft_id` and `eft_name` ||

In each row, the determinant of the transitive dependency becomes a key of its own relation and remains as a foreign key in the referencing relation. After these decompositions, no non-primary attribute is transitively dependent on a candidate key, so the relations are in 3NF.

== BCNF ==

|| Relation group || Candidate key or keys || BCNF check ||
|| `students` || `student_index` || the key determines the student attributes ||
|| `members`, `observers`, `full_members`, `alumni` || `student_index` || no non-trivial functional dependency has a non-key left-hand side ||
|| `young_members` || `student_index` || `student_index -> mentor_index` ||
|| `membership_stages` || `ms_id`, `ms_name` || both left-hand sides are candidate keys ||
|| `membership_stage_records` || `msr_id` || the key determines all non-primary attributes ||
|| `organizational_functions` || `of_id`, `of_title` || both left-hand sides are candidate keys ||
|| `board`, `non_board` || `of_id` || no non-trivial functional dependency has a non-key left-hand side ||
|| `function_mandates` || `fm_id` || the key determines all non-primary attributes ||
|| `activity_types` || `at_id`, `at_name` || both left-hand sides are candidate keys ||
|| `locations` || `l_id`, `l_name` || both left-hand sides are candidate keys ||
|| `activity_sessions`, `related_to` || `as_id` || the key determines the remaining attributes ||
|| `event_types` || `et_id`, `et_name` || both left-hand sides are candidate keys ||
|| `event_editions` || `ee_id` || the key determines all non-primary attributes ||
|| `event_edition_locations` || `(ee_id, l_id)` || no non-trivial functional dependency has a non-key left-hand side ||
|| `event_session_types` || `est_id`, `est_name` || both left-hand sides are candidate keys ||
|| `event_function_types` || `eft_id`, `eft_name` || both left-hand sides are candidate keys ||
|| `event_sessions` || `(ee_id, es_no)` || the key determines all non-primary attributes ||
|| `event_functions` || `(ee_id, ef_no)` || the key determines `eft_id` ||
|| `applications` || `application_id` || the key determines all non-primary attributes ||
|| `holds_event_function` || `(student_index, ee_id, ef_no)` || no non-trivial functional dependency has a non-key left-hand side ||
|| `participation`, `required_attendance`, `announces_absence`, `responsible_for` || `(student_index, as_id)` || every non-trivial dependency has the complete key on the left-hand side ||
|| `volunteers` || `(student_index, ee_id, es_no)` || the key determines `volunteer_role` ||
|| `companies` || `c_id` || the key determines the company attributes ||
|| `cooperation` || `(c_id, ee_id)` || the key determines `cooperation_type` ||

For every non-trivial dependency in these relations, the determinant is one of the candidate keys shown in the table. None of the 32 relations has a BCNF violation, so BCNF is the highest normal form reached by the decomposition.

=== Non-additivity (loss-less decomposition) and validity of the functional dependencies ===

For every binary FD-based decomposition step `R -> R1, R2`:

{{{
R1 intersect R2 = X
X -> R1
}}}

where `X` is the determinant used for that step. Therefore every binary step is loss-less.

Example:

{{{
ms_id -> ms_name

R1 = membership_stages(ms_id, ms_name)
R2 = membership_stage_records(msr_id, student_index, ms_id, valid_from, valid_to)

R1 intersect R2 = {ms_id}
ms_id -> R1
}}}

The preservation of the initial functional dependencies was checked separately against the final relations:

|| Dependencies from `Fc` || Final relation || Count ||
|| `student_index -> first_name, last_name, faculty, field_of_studies, year_of_studies, birthday, email, phone_number` || `students` || 8 ||
|| `student_index -> mentor_index` || `young_members` || 1 ||
|| `ms_id -> ms_name`; `ms_name -> ms_id` || `membership_stages` || 2 ||
|| `msr_id -> student_index, ms_id, valid_from, valid_to` || `membership_stage_records` || 4 ||
|| `of_id -> of_title`; `of_title -> of_id` || `organizational_functions` || 2 ||
|| `fm_id -> student_index, of_id, mandate_year` || `function_mandates` || 3 ||
|| `at_id -> at_name, at_description`; `at_name -> at_id` || `activity_types` || 3 ||
|| `l_id -> l_name`; `l_name -> l_id` || `locations` || 2 ||
|| `et_id -> et_name, et_description`; `et_name -> et_id` || `event_types` || 3 ||
|| `est_id -> est_name, est_description`; `est_name -> est_id` || `event_session_types` || 3 ||
|| `eft_id -> eft_name`; `eft_name -> eft_id` || `event_function_types` || 2 ||
|| `as_id -> at_id, l_id, as_start_time` || `activity_sessions` || 3 ||
|| `as_id -> ee_id` || `related_to` || 1 ||
|| `ee_id -> et_id, ee_title, ee_start_time, ee_end_time` || `event_editions` || 4 ||
|| `(ee_id, es_no) -> est_id, l_id, es_start_time, es_title, needed_no_volunteers` || `event_sessions` || 5 ||
|| `(ee_id, ef_no) -> eft_id` || `event_functions` || 1 ||
|| `application_id -> student_index, ee_id, ef_no, motivational_letter, application_type, application_status` || `applications` || 6 ||
|| `(student_index, as_id) -> rsvp_status, rsvp_at, attendance_status` || `participation` || 3 ||
|| `(student_index, as_id) -> reason, announced_at` || `announces_absence` || 2 ||
|| `(student_index, ee_id, es_no) -> volunteer_role` || `volunteers` || 1 ||
|| `c_id -> c_name, naframa_reference` || `companies` || 2 ||
|| `(c_id, ee_id) -> cooperation_type` || `cooperation` || 1 ||
|| '''Total''' || || '''62/62''' ||

All 62 dependencies from `Fc` can therefore be enforced within individual final relations. Together with the loss-less check for every binary decomposition step, this shows that the decomposition both preserves the functional dependencies and has the loss-less join property.

== Final result and discussion ==

=== Normalized relational model ===

The final normalized model contains the following relations:

{{{
students(student_index, first_name, last_name, faculty, field_of_studies,
         year_of_studies, birthday, email, phone_number)
members(student_index)
observers(student_index)
young_members(student_index, mentor_index)
full_members(student_index)
alumni(student_index)

membership_stages(ms_id, ms_name)
membership_stage_records(msr_id, student_index, ms_id, valid_from, valid_to)

organizational_functions(of_id, of_title)
board(of_id)
non_board(of_id)
function_mandates(fm_id, student_index, of_id, mandate_year)

activity_types(at_id, at_name, at_description)
locations(l_id, l_name)
activity_sessions(as_id, at_id, l_id, as_start_time)
related_to(as_id, ee_id)

event_types(et_id, et_name, et_description)
event_editions(ee_id, et_id, ee_title, ee_start_time, ee_end_time)
event_edition_locations(ee_id, l_id)
event_session_types(est_id, est_name, est_description)
event_function_types(eft_id, eft_name)
event_sessions(ee_id, es_no, est_id, l_id, es_start_time, es_title,
               needed_no_volunteers)
event_functions(ee_id, ef_no, eft_id)

applications(application_id, student_index, ee_id, ef_no,
             motivational_letter, application_type, application_status)
holds_event_function(student_index, ee_id, ef_no)

participation(student_index, as_id, rsvp_status, rsvp_at, attendance_status)
required_attendance(student_index, as_id)
announces_absence(student_index, as_id, reason, announced_at)
responsible_for(student_index, as_id)
volunteers(student_index, ee_id, es_no, volunteer_role)

companies(c_id, c_name, naframa_reference)
cooperation(c_id, ee_id, cooperation_type)
}}}

=== Discussion ===

The normalized model obtained independently in this phase has the same relations, attributes and keys as the design documented in RelationalDesign. The relations created from the ER model already separate students, reusable types, locations, concrete sessions, event editions, applications and M:N relationships in the same way required by the normalization procedure.

No relation from the Phase P2 design needs to be changed. The relational design from RelationalDesign will continue to be used for the following project phases.

== AI Use ==

AI was used during this phase as a consultation, drafting and checking tool while preparing the normalization from the completed ER model and relational design. The AI-assisted parts were reviewed against the project requirements and course material before being included.

Full AI usage documentation: NormalizationAIUsage.