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/solution that was used: Gemini 3 Flash

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 {stud_id, subj_id}) were split into clean junction attributes (R6 through R8) without dropping core non-key variables.

BCNF Condition Checking: I tested the resulting intersection schemas against the 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 {stud_id, subj_id} into R6 (Enrollment) and {stud_id, prof_id} 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.