= Phase 5: Normalization =

== Denormalized Form ==
A single flat table containing all entities and their relationships, with no structural constraints. Multi-valued attributes and repeating groups are present.

=== Initial Denormalized Relation ===
R( univ_id, univ_name, univ_location, univ_is_private, fac_id, fac_name, fac_location, fac_study_field, prof_id, prof_name, prof_surname, prof_age, stud_id, stud_name, stud_surname, stud_location, stud_index, subj_id, subj_name, subj_semester, subj_credits, ss_id, enrollment_date, final_grade, status, absences_count, start_date, end_date )

== Functional Dependencies ==
The following functional dependencies hold in the University system based on the project business rules and database constraints:

* univ_id -> univ_name, univ_location, univ_is_private
* fac_id -> fac_name, fac_location, fac_study_field, univ_id
* prof_id -> prof_name, prof_surname, prof_age, fac_id
* stud_id -> stud_name, stud_surname, stud_location, stud_index, fac_id
* subj_id -> subj_name, subj_semester, subj_credits, fac_id
* ss_id -> stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count
* {stud_id, prof_id, start_date} -> end_date

== Candidate Key Determination ==
Attribute set X is a candidate key if:
1. X+ = R (Sufficiency property)
2. X is minimal

=== Candidate Key ===
K = { ss_id, start_date, univ_id }

=== Minimality Check ===
* Removing ss_id loses the student enrollment log branch, leaving grades and statuses completely undetermined.
* Removing start_date loses the advice/mentorship timeline branch, making it impossible to determine the end_date.
* Removing univ_id loses the university core branding and metadata branch.
Therefore, every attribute within the composite set K is strictly required, proving the key is minimal.

== Sample Denormalized Data ==
|| univ_name || fac_name || prof_name || stud_name || subj_name || final_grade || start_date ||
|| UKIM || FINKI || Ivan || Ana || Bazi na Podatoci || 10 || 2026-07-01 ||
|| UGD || Praven || Boris || Mate || Kazneno Pravo || 8 || 2026-07-01 ||

== First Normal Form (1NF) ==
=== Definition ===
A relation is in 1NF when:
* Row ordering carries no meaning
* Every attribute contains atomic values
* Data types are consistent
* A primary key uniquely identifies each tuple
* No repeating groups exist

=== Transformation to 1NF ===
The original denormalized relation contains multiple conceptual entities combined into one structure. Repeating groups are eliminated by separating repeating occurrences into individual tuples. All attributes contain strictly atomic values, satisfying the baseline 1NF constraints.

== Transition to Second Normal Form (2NF) ==
To achieve 2NF:
* Partial dependencies must be systematically removed.
* Attributes depending only on a part of a composite key must be separated into independent relations.

== Second Normal Form (2NF) Decomposition ==

=== R1 - UNIVERSITY ===
{univ_id} -> {univ_name, univ_location, univ_is_private}

* '''Candidate Key Verification:''' univ_id is the surrogate primary key. No partial dependencies exist.
* '''Sample Data:'''
|| univ_id || univ_name || univ_location || univ_is_private ||
|| 1 || UKIM || Skopje || False ||
|| 2 || UGD || Shtip || False ||
* '''Lossless Join Test:'''
  R intersection R1 = {univ_id, univ_name, univ_location, univ_is_private}
  Since univ_id -> R1, the decomposition is lossless.

=== R2 - FACULTY ===
{fac_id} -> {fac_name, fac_location, fac_study_field, univ_id}

* '''Candidate Key Verification:''' fac_id is the surrogate primary key.
* '''Sample Data:'''
|| fac_id || fac_name || fac_location || fac_study_field || univ_id ||
|| 1 || FINKI || Skopje || Computer Science || 1 ||
|| 2 || Praven || Shtip || Law || 2 ||
* '''Lossless Join Test:'''
  R1.1 intersection R2 = {fac_id, fac_name, fac_location, fac_study_field, univ_id}
  Since fac_id -> R2, the decomposition is lossless.

=== R3 - STUDENT ===
{stud_id} -> {stud_name, stud_surname, stud_location, stud_index, fac_id}

* '''Candidate Key Verification:''' stud_id is the primary key.
* '''Sample Data:'''
|| stud_id || stud_name || stud_surname || stud_location || stud_index || fac_id ||
|| 1 || Elena || Petrova || Skopje || 211001 || 1 ||
|| 2 || Marko || Jovanov || Veles || 222045 || 1 ||
* '''Lossless Join Test:'''
  R2.1 intersection R3 = {stud_id, stud_name, stud_surname, stud_location, stud_index, fac_id}
  Since stud_id -> R3, the decomposition is lossless.

=== R4 - PROFESSOR ===
{prof_id} -> {prof_name, prof_surname, prof_age, fac_id}

* '''Candidate Key Verification:''' prof_id is the primary key.
* '''Sample Data:'''
|| prof_id || prof_name || prof_surname || prof_age || fac_id ||
|| 1 || Ivan || Petrov || 45 || 1 ||
|| 2 || Ana || Markoska || 38 || 1 ||
* '''Lossless Join Test:'''
  R3.1 intersection R4 = {prof_id, prof_name, prof_surname, prof_age, fac_id}
  Since prof_id -> R4, the decomposition is lossless.

=== R5 - SUBJECT ===
{subj_id} -> {subj_name, subj_semester, subj_credits, fac_id}

* '''Candidate Key Verification:''' subj_id is the primary key.
* '''Sample Data:'''
|| subj_id || subj_name || subj_semester || subj_credits || fac_id ||
|| 1 || Databases || Summer || 6 || 1 ||
|| 2 || APS || Winter || 6 || 1 ||
* '''Lossless Join Test:'''
  R4.1 intersection R5 = {subj_id, subj_name, subj_semester, subj_credits, fac_id}
  Since subj_id -> R5, the decomposition is lossless.

=== R6 - STUDENT_SUBJECT ===
{ss_id} -> {stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}

* '''Candidate Key Verification:''' ss_id is the unique primary key log identifier.
* '''Sample Data:'''
|| ss_id || stud_id || subj_id || prof_id || enrollment_date || final_grade || status || absences_count ||
|| 1 || 1 || 1 || 1 || 2026-02-15 || 10 || Active || 2 ||
|| 2 || 2 || 2 || 2 || 2026-02-15 || 8 || Active || 0 ||
* '''Lossless Join Test:'''
  R5.1 intersection R6 = {ss_id, stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
  Since ss_id -> R6, the decomposition is lossless.

=== R7 - ADVICE ===
{stud_id, prof_id, start_date} -> {end_date}

* '''Candidate Key Verification:''' Composite primary key consisting of {stud_id, prof_id, start_date}.
* '''Sample Data:'''
|| stud_id || prof_id || start_date || end_date ||
|| 1 || 1 || 2026-07-01 || 2026-12-31 ||
|| 2 || 2 || 2026-07-01 || 2026-12-31 ||
* '''Lossless Join Test:'''
  R6.1 intersection R7 = {stud_id, prof_id, start_date, end_date}
  Since {stud_id, prof_id, start_date} -> R7, the decomposition is lossless.

=== R8 - AFFILIATED ===
{univ_id, prof_id} -> No non-key attributes.

* '''Candidate Key Verification:''' Pure composite relationship table with key {univ_id, prof_id}.
* '''Sample Data:'''
|| univ_id || prof_id ||
|| 1 || 1 ||
|| 1 || 2 ||
* '''Lossless Join Test:'''
  R7.1 intersection R8 = {univ_id, prof_id}
  Since {univ_id, prof_id} defines the relationship matrix, the split is entirely lossless.

== Third Normal Form (3NF) ==
=== Definition ===
A relation is in 3NF if:
* It is already in 2NF.
* No transitive dependencies exist.
* Every non-key attribute depends strictly and directly only upon the primary key.

=== Transition from 2NF to 3NF ===
During the 2NF phase, transitive paths like stud_id -> fac_id -> univ_id were systematically resolved by isolating R2 (Faculty) and R3 (Student). Thus, fac_id acts purely as a direct atomic foreign key constraint. There are no remaining non-key dependencies on other non-key values, meaning the schema satisfies 3NF parameters naturally.

== Boyce-Codd Normal Form (BCNF) ==
=== Verification ===
A relation is in BCNF if for every non-trivial functional dependency X -> Y, X behaves as a superkey. In all our fully decomposed weak log entities and bridge relations (R6, R7, R8), the left-hand determinant always functions as the absolute candidate key or superkey. The database layout naturally achieves full BCNF certification.

== Final Schema Summary ==
|| Relation || Primary Key || Foreign Keys || Normal Form ||
|| UNIVERSITY || univ_id || — || 3NF / BCNF ||
|| FACULTY || fac_id || univ_id -> UNIVERSITY || 3NF / BCNF ||
|| STUDENT || stud_id || fac_id -> FACULTY || 3NF / BCNF ||
|| PROFESSOR || prof_id || fac_id -> FACULTY || 3NF / BCNF ||
|| SUBJECT || subj_id || fac_id -> FACULTY || 3NF / BCNF ||
|| STUDENT_SUBJECT || ss_id || stud_id -> STUDENT, subj_id -> SUBJECT, prof_id -> PROFESSOR || 3NF / BCNF ||
|| ADVICE || stud_id, prof_id, start_date || stud_id -> STUDENT, prof_id -> PROFESSOR || 3NF / BCNF ||
|| AFFILIATED || univ_id, prof_id || univ_id -> UNIVERSITY, prof_id -> PROFESSOR || 3NF / BCNF ||

== Final Conclusion ==
Through structured formal normalization paths, closure determination, and intersection-based lossless join verifications, our University database structure has been fully optimized. It completely eliminates data anomalies, structural redundancies, and operational hazards, ensuring 100% relational integrity.

= Normalization AI Usage =
'''Name of AI service/solution used:''' Gemini
'''URL:''' https://gemini.google.com/
'''Type of service/subscription:''' Free Tier
'''Final result:''' Verified formal closures, mathematical integrity of keys, and formatting alignments for the Trac Wiki interface.