= Normalization =

== De-normalized database form ==
To begin, we combine all attributes from the ER model 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, enroll_id, enroll_date, final_grade, status, absence_count,
advice_id, request_sent, consult_date, advice_status}
}}}

== Functional dependencies ==
The canonical cover of functional dependencies based on the project business rules:

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}

{prof_id, subj_id} -> (The "Teach" relationship)

{stud_id, subj_id} -> {enroll_date, final_grade, status, absence_count}

{stud_id, prof_id} -> {request_sent, consult_date, advice_status}

== Candidate keys and primary key ==

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

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

'''Current Normal Form:''' '''1NF'''. The relation is in 1NF because all attributes are atomic, but it contains massive 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 the primary key.

'''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:''' Attributes depending on the combinations are moved to junction relations.

'''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_Data):''' {univ_id, univ_name, univ_location, univ_is_private}

'''R5 (Faculty_Data):''' {fac_id, fac_name, fac_location, fac_study_field, univ_id}

'''R6 (Enrollment):''' {stud_id, subj_id, enroll_date, grade, status, absence}

'''R7 (Advice):''' {stud_id, prof_id, request_sent, consult_date, status}

'''R8 (Teach):''' {prof_id, subj_id}

== 3NF decomposition ==
'''Analysis:''' We check for Transitive Dependencies. In '''R1 (Student)''', the fac_id determines the faculty location, but since we already moved faculty attributes to '''R5''', there are no remaining transitive dependencies in the primary entities. All non-key attributes are now functionally dependent only on the primary keys of their respective tables.

== BCNF if possible ==
'''Analysis:''' A relation is in BCNF if for every non-trivial dependency X -> Y, X is a superkey. In our Enrollment ('''R6'''), Advice ('''R7'''), and Teach ('''R8''') relations, the determinants are the composite primary keys. The design is already in BCNF.

== Final result and discussion ==
=== Normalized relational model ===
The final design consists of 8 normalized tables:

'''University''' (univ_id, name, location, is_private)

'''Faculty''' (fac_id, name, location, study_field, univ_id)

'''Professor''' (prof_id, name, surname, age, fac_id)

'''Student''' (stud_id, name, surname, location, index, fac_id)

'''Subject''' (subj_id, name, semester, credits, fac_id)

'''Subject_Professor''' (prof_id, subj_id)

'''Student_Subject''' (stud_id, subj_id, enroll_date, final_grade, status, absence_count)

'''Advice''' (stud_id, prof_id, request_sent, consult_date, advice_status)

=== Discussion ===
The decomposition process successfully eliminated update, insertion, and deletion anomalies. Formal normalization confirmed that the "Teach" relationship must be a separate junction table (subject_professor) to avoid partial dependency issues. All decompositions preserve functional dependencies and maintain loss-less join properties.

= Normalization AI Usage =

'''Name of AI service:''' Gemini 3 Flash

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

'''Type of service:''' Free Tier

'''Final result:''' AI verified functional dependencies against the ER diagram and provided BCNF decomposition logic.