Changes between Initial Version and Version 1 of Normalization

Timestamp:

09/24/26 09:30:38 (3 days ago)

Author:

236024

Comment:

--

Normalization

@@ +1 @@
+= 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.