Changes between Version 6 and Version 7 of P5


Ignore:
Timestamp:
06/29/26 14:24:01 (8 days ago)
Author:
216009
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P5

    v6 v7  
    1 = Normalization =
     1= Phase 5: Normalization =
    22
    33== De-normalized database form ==
    … …  
    55
    66{{{
    7 R = {univ_id, univ_name, univ_location, univ_is_private, fac_id, fac_name, fac_location,
    8 fac_study_field, prof_id, prof_name, prof_surname, prof_age, stud_id, stud_name,
    9 stud_surname, stud_location, stud_index, subj_id, subj_name, subj_semester,
    10 subj_credits, ss_id, enrollment_date, final_grade, status, absences_count,
    11 start_date, end_date}
     7R = {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}
    128}}}
    139
    … …  
    1511The canonical cover of functional dependencies based on the project business rules and database constraints:
    1612
    17 * univ_id -> {univ_name, univ_location, univ_is_private}
    18 * fac_id -> {fac_name, fac_location, fac_study_field, univ_id}
    19 * prof_id -> {prof_name, prof_surname, prof_age, fac_id}
    20 * stud_id -> {stud_name, stud_surname, stud_location, stud_index, fac_id}
    21 * subj_id -> {subj_name, subj_semester, subj_credits, fac_id}
    22 * {stud_id, prof_id, start_date} -> {end_date}
    23 * {ss_id, stud_id, subj_id} -> {professor_id, enrollment_date, final_grade, status, absences_count}
    24 * {univ_id, prof_id} -> (The "Affiliated" relationship table)
     13* FD1: univ_id -> {univ_name, univ_location, univ_is_private}
     14* FD2: fac_id -> {fac_name, fac_location, fac_study_field, univ_id}
     15* FD3: prof_id -> {prof_name, prof_surname, prof_age, fac_id}
     16* FD4: stud_id -> {stud_name, stud_surname, stud_location, stud_index, fac_id}
     17* FD5: subj_id -> {subj_name, subj_semester, subj_credits, fac_id}
     18* FD6: ss_id -> {stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
     19* FD7: {stud_id, prof_id, start_date} -> {end_date}
    2520
    2621== Candidate keys and primary key ==
    2722
    28 '''Candidate Key:''' {ss_id, stud_id, subj_id, prof_id, start_date}
     23=== Formal Verification of the Candidate Key (Closure Test) ===
     24To formally prove that a set of attributes K is a candidate key for R, it must satisfy two conditions:
     251. **Sufficiency (Superkey property):** The attribute closure of K must contain all attributes of R (K+ = R).
     262. **Minimality:** No proper subset of K can determine all attributes of R.
    2927
    30 '''Primary Key:''' {ss_id, stud_id, subj_id, prof_id, start_date}
     28Let us test the composite set K = {ss_id, start_date, univ_id}:
    3129
    32 '''Current Normal Form:''' '''1NF'''. The relation is in 1NF because all attributes contain atomic values, but it contains massive update redundancies and partial dependencies.
     30* **Step 1 (Base Case):**
     31
     32  K^0 = {ss_id, start_date, univ_id}
     33* **Step 2 (Apply FD1):** univ_id -> {univ_name, univ_location, univ_is_private}
     34
     35  K^1 = {ss_id, start_date, univ_id, univ_name, univ_location, univ_is_private}
     36* **Step 3 (Apply FD6):** ss_id -> {stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
     37
     38  K^2 = {ss_id, start_date, univ_id, univ_name, univ_location, univ_is_private, stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
     39* **Step 4 (Apply FD3, FD4, FD5):** prof_id, stud_id, and subj_id extract their respective metadata:
     40
     41  K^3 = K^2 + {prof_name, prof_surname, prof_age, fac_id, stud_name, stud_surname, stud_location, stud_index, subj_name, subj_semester, subj_credits}
     42* **Step 5 (Apply FD2):** fac_id -> {fac_name, fac_location, fac_study_field, univ_id}
     43
     44  K^4 = K^3 + {fac_name, fac_location, fac_study_field}
     45* **Step 6 (Apply FD7):** {stud_id, prof_id, start_date} -> {end_date}
     46
     47  K^5 = K^4 + {end_date}
     48
     49Since K^5 = R, the set {ss_id, start_date, univ_id} is a valid Superkey.
     50
     51=== Minimality Proof ===
     52* If we remove ss_id, the closure {start_date, univ_id}+ only yields {start_date, univ_id, univ_name, univ_location, univ_is_private}, failing to find any student, professor, or core entity data.
     53* If we remove start_date, the closure cannot resolve end_date via FD7.
     54* If we remove univ_id, the closure cannot resolve university and faculty metadata.
     55
     56Thus, no proper subset is a superkey. The set is strictly minimal.
     57
     58'''Candidate Key:''' {ss_id, start_date, univ_id}
     59'''Primary Key:''' {ss_id, start_date, univ_id}
     60
     61'''Current Normal Form:''' '''1NF'''. The relation is in 1NF because all attributes contain atomic values, but it contains partial dependencies where non-prime attributes depend on parts of the composite candidate key (e.g., univ_name depends strictly on univ_id, which is only a subset of the main key).
    3362
    3463== 2NF decomposition ==
    35 '''Analysis:''' The universal relation '''R''' has multiple Partial Functional Dependencies, where non-key attributes depend on only part of our composite identifier set.
     64'''Analysis:''' A relation is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on any candidate key. We must systematically check and extract every partial dependency violation.
    3665
    37 '''Step 1:''' Attributes depending only on stud_id are moved to a new relation '''Student'''.
    38 '''Step 2:''' Attributes depending only on prof_id are moved to a new relation '''Professor'''.
    39 '''Step 3:''' Attributes depending only on subj_id are moved to a new relation '''Subject'''.
    40 '''Step 4:''' Multi-attribute junction entities are isolated into weak entity relations and relationship tables.
     66=== Detailed Step-by-Step Verification ===
    4167
    42 '''Resulting Relations:'''
     68* '''Step 1: Isolation of University Entity (Resolving FD1)'''
     69  * *Violation:* The non-prime attributes {univ_name, univ_location, univ_is_private} depend entirely on univ_id, which is a proper subset of the primary key {ss_id, start_date, univ_id}.
     70  * *Action:* Separate into a distinct relation.
     71  * *Result:* '''R1 (University):''' {univ_id, univ_name, univ_location, univ_is_private} (Key: univ_id)
    4372
    44 * '''R1 (Student):''' {stud_id, name, surname, location, index, fac_id}
    45 * '''R2 (Professor):''' {prof_id, name, surname, age, fac_id}
    46 * '''R3 (Subject):''' {subj_id, name, semester, credits, fac_id}
    47 * '''R4 (University):''' {univ_id, name, location, is_private}
    48 * '''R5 (Faculty):''' {fac_id, name, location, study_field, univ_id}
    49 * '''R6 (Student_Subject):''' {ss_id, stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count}
    50 * '''R7 (Advice):''' {stud_id, prof_id, start_date, end_date}
    51 * '''R8 (Affiliated):''' {univ_id, prof_id}
     73* '''Step 2: Isolation of Faculty Entity (Resolving FD2)'''
     74  * *Violation:* Faculty metadata attributes depend transitively through the hierarchy via fac_id.
     75  * *Action:* Extract faculty structural descriptors to stop multi-attribute replication.
     76  * *Result:* '''R2 (Faculty):''' {fac_id, fac_name, fac_location, fac_study_field, univ_id} (Key: fac_id)
     77
     78* '''Step 3: Isolation of Student Entity (Resolving FD4)'''
     79  * *Violation:* {stud_name, stud_surname, stud_location, stud_index, fac_id} depend purely on stud_id, which is determined via ss_id (a subset of the primary key's downstream components). This creates an implicit partial dependency in the flattened schema.
     80  * *Action:* Move student profile vectors to a dedicated entity table.
     81  * *Result:* '''R3 (Student):''' {stud_id, stud_name, stud_surname, stud_location, stud_index, fac_id} (Key: stud_id)
     82
     83* '''Step 4: Isolation of Professor Entity (Resolving FD3)'''
     84  * *Violation:* {prof_name, prof_surname, prof_age, fac_id} depend directly on prof_id.
     85  * *Action:* Isolate professor demographics away from the transaction logs.
     86  * *Result:* '''R4 (Professor):''' {prof_id, prof_name, prof_surname, prof_age, fac_id} (Key: prof_id)
     87
     88* '''Step 5: Isolation of Subject Entity (Resolving FD5)'''
     89  * *Violation:* Curriculum properties {subj_name, subj_semester, subj_credits, fac_id} depend entirely on subj_id.
     90  * *Action:* Isolate course syllabus metadata.
     91  * *Result:* '''R5 (Subject):''' {subj_id, subj_name, subj_semester, subj_credits, fac_id} (Key: subj_id)
     92
     93* '''Step 6: Isolation of Multi-Attribute Weak Log Relations (Resolving FD6 and FD7)'''
     94  * *Action:* Separate transactional operational mappings into clean associative tables matching the remaining prime components.
     95  * *Result (Enrollments):* '''R6 (Student_Subject):''' {ss_id, stud_id, subj_id, prof_id, enrollment_date, final_grade, status, absences_count} (Key: ss_id)
     96  * *Result (Mentorship):* '''R7 (Advice):''' {stud_id, prof_id, start_date, end_date} (Key: {stud_id, prof_id, start_date})
     97  * *Result (Affiliation):* '''R8 (Affiliated):''' {univ_id, prof_id} (Key: {univ_id, prof_id})
     98
     99Every non-prime attribute in relations R1 through R8 now depends strictly on the full, complete primary key of its respective table. Partial dependencies are completely eliminated.
    52100
    53101== 3NF decomposition ==
    54 '''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.
     102'''Analysis:''' We evaluate the 2NF relations for Transitive Dependencies (X -> Y -> Z). In the original universal relation, a transitive dependency loop existed where stud_id -> fac_id -> univ_id. Through the exact isolation of R2 (Faculty) and R3 (Student), fac_id now acts purely as a direct foreign key. Every non-key attribute in relations R1 through R8 is now non-transitively dependent on the primary key fields. Thus, the schema satisfies 3NF.
    55103
    56104== BCNF if possible ==
    57 '''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.
     105'''Analysis:''' A relation is in BCNF if for every non-trivial functional dependency X -> Y, X is a superkey. In all the decomposed schemas (including the junction tables R6, R7, and R8), the left-hand side of every functional dependency acts as a superkey for that specific relation. Therefore, the schema satisfies BCNF requirements without needing further structural splits.
    58106
    59107== Final result and discussion ==