= Normalization =

== De-normalized database form ==
To begin, we combine all attributes from our actual schema implementation into a single universal relation '''R''':

{{{
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 canonical cover of functional dependencies 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}
* {stud_id, prof_id, start_date} -> {end_date}
* {ss_id, stud_id, subj_id} -> {professor_id, enrollment_date, final_grade, status, absences_count}
* {univ_id, prof_id} -> (The "Affiliated" relationship table)

== Candidate keys and primary key ==

'''Candidate Key:''' {ss_id, stud_id, subj_id, prof_id, start_date}

'''Primary Key:''' {ss_id, stud_id, subj_id, prof_id, start_date}

'''Current Normal Form:''' '''1NF'''. The relation is in 1NF because all attributes contain atomic values, but it contains massive update redundancies and partial dependencies.

== 2NF decomposition ==
'''Analysis:''' The universal relation '''R''' has multiple Partial Functional Dependencies, where non-key attributes depend on only part of our composite identifier set.

'''Step 1:''' Attributes depending only on stud_id are moved to a new relation '''Student'''.
'''Step 2:''' Attributes depending only on prof_id are moved to a new relation '''Professor'''.
'''Step 3:''' Attributes depending only on subj_id are moved to a new relation '''Subject'''.
'''Step 4:''' Multi-attribute junction entities are isolated into weak entity relations and relationship tables.

'''Resulting Relations:'''

* '''R1 (Student):''' {stud_id, name, surname, location, index, fac_id}
* '''R2 (Professor):''' {prof_id, name, surname, age, fac_id}
* '''R3 (Subject):''' {subj_id, name, semester, credits, fac_id}
* '''R4 (University):''' {univ_id, name, location, is_private}
* '''R5 (Faculty):''' {fac_id, name, location, study_field, univ_id}
* '''R6 (Student_Subject):''' {ss_id, stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
* '''R7 (Advice):''' {stud_id, prof_id, start_date, end_date}
* '''R8 (Affiliated):''' {univ_id, prof_id}

== 3NF decomposition ==
'''Analysis:''' We evaluate schemas for Transitive Dependencies ($X \rightarrow Y \rightarrow Z$). Since the faculty metadata attributes were completely isolated into '''R5''', `fac_id` serves purely as a direct foreign key inside `Student` and `Professor`. There are no remaining transitive dependencies. All non-key attributes are now directly dependent only upon the primary key fields of their respective relations.

== BCNF if possible ==
'''Analysis:''' A relation is in BCNF if for every non-trivial functional dependency $X \rightarrow Y$, $X$ is a superkey. In our weak entities and junction tables ('''R6''', '''R7''', and '''R8'''), the determinants match our composite primary keys. The system design satisfies BCNF requirements naturally.

== Final result and discussion ==
=== Normalized relational model ===
The final design consists of 8 completely normalized tables matching our production DDL layout:

* '''University''' (Id, Name, Location, IsPrivate)
* '''Faculty''' (Id, Name, Location, study_field, University_Id)
* '''Professor''' (Id, Name, Surname, Age, Faculty_Id)
* '''Student''' (Id, Name, Surname, Location, Student_Index, Faculty_Id)
* '''Subject''' (Id, Name, Semester, Credits, Faculty_Id)
* '''Student_Subject''' (Ss_Id, Student_Id, Subject_Id, Professor_Id, Enrollment_Date, Status, Final_Grade, Absences_Count)
* '''Advice''' (Student_Id, Professor_Id, Start_Date, End_Date)
* '''Affiliated''' (University_Id, Professor_Id)

=== Discussion ===
The decomposition process successfully eliminated insertion, update, and deletion anomalies. Formal normalization confirmed that multi-semester course repetitions must use a weak entity design (`Student_Subject`) tracking the unique `Ss_Id` to prevent key collisions during active class retakes. All functional dependencies are fully preserved, and properties permit lossless joins.

= Normalization AI Usage =

'''Name of AI service/solution used:''' Gemini

'''URL:''' https://gemini.google.com/

'''Type of service/subscription:''' Free Tier

'''Final result:''' I paired with the AI assistant to mathematically audit my step-by-step relational decompositions. It helped me verify that my universal relation R was properly split across 1NF, 2NF, 3NF, and BCNF boundaries, ensuring full dependency preservation and lossless joins without changing my design logic.

'''Results in details / description:'''
* '''Functional Dependency Validation:''' I formulated the initial canonical cover rules mapping entity relations (like tracking how fac_id resolves faculty metadata and stud_id maps indexes). The AI reviewed my dependency list to confirm no logical transitivity loops were left unaddressed.
* '''Decomposition Step Auditing:''' I executed the structural extraction process to pull partial dependencies out of 1NF. The AI double-checked my 2NF math to ensure that composite key subsets (such as tracking multi-part student enrollment histories) were split into clean tables without dropping core variables.
* '''BCNF Condition Checking:''' I tested the resulting intersection schemas against strict Boyce-Codd Normal Form constraints. The AI verified my observation that because every determinant field behaves as a functional superkey, the tables already meet BCNF requirements naturally.
* '''Syntax Cleanliness:''' The AI helped verify that my notation, set brackets, and relational groupings matched the standard formats expected for our database engineering submission.

'''Entire AI usage log:'''

'''User:''' I have aggregated all the fields from my university ER diagram into a single universal relation R and mapped out my functional dependencies. Can you check my canonical cover list to verify if I missed any partial or transitive dependencies before I start the 2NF decomposition?

'''AI:''' Reviewed your functional dependency list. The dependencies you mapped out are mathematically sound and align perfectly with your business constraints. The subset keys for student attributes, professor bio-data, and subject specs are clearly defined, confirming that your universal relation is currently at a baseline 1NF due to these massive partial dependencies.

'''User:''' Excellent. I am running the 2NF decomposition now by breaking the universal set into 8 separate relations to isolate fields that rely on single identifiers versus composite keys. Can you look at my resulting tables (R1 through R8) to confirm that this breakdown eliminates the partial dependency anomalies?

'''AI:''' Assisted by validating your 2NF steps. Your extraction strategy successfully isolates the distinct entities. Moving things like student course enrollment tracking details into R6 (Student_Subject) and advisor periods into R7 (Advice) completely isolates the partial overlaps, leaving all non-key fields fully dependent on whole primary keys.

'''User:''' For 3NF and BCNF, since my non-key attributes are now solely determined by superkeys or primary keys across the junction configurations, I concluded that the schema passes both tests without further changes. Can you double-check my reasoning?

'''AI:''' Audited your conclusion. Your analysis is entirely correct. Since your faculty relations were already moved to R5 to prevent transitive student loops, and every remaining determinant in your intersection tables functions directly as a superkey, the layout satisfies BCNF automatically.

'''User:''' Great. Can you help format this complete normalization narrative and table list into clear Trac Wiki text headings so I can update my project space?

'''AI:''' Structured the final results, analysis breakdowns, and discussion commentary into compliant wiki blocks for direct integration into your Phase 5 layout.