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.