Changes between Version 1 and Version 2 of P5

Timestamp:

05/12/26 12:45:05 (2 weeks ago)

Author:

216009

Comment:

--

P5

@@ -1 @@
-.
+= 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.
+
+'''Results in details:'''
+
+'''Dependency Mapping:''' AI mapped "Advises" and "Teaches" relationships into junction tables.
+
+'''Formatting:''' AI structured the documentation for Trac WikiMarkup.
+
+'''Corrective Logic:''' AI identified the need for separate University and Faculty relations.