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.