Changes between Version 16 and Version 17 of ERModel


Ignore:
Timestamp:
09/03/26 17:52:13 (7 days ago)
Author:
216009
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ERModel

    v16 v17  
    1 
    21== '''Entity-Relationship Model v01'''
    3 
    42
    53== Diagram
    64
    75[[Image(ERModel_v1.3.png)]]
    8 
    96
    107== Data Requirements
    … …  
    2623
    2724=== '''Faculty'''
    28 Represents a faculty within a university. It organizes students, professors, and subjects. Each faculty belongs to one university.
     25Represents a faculty within a university. It organizes students, professors, and subjects.
    2926
    3027'''Candidate keys:''' id (primary key).
    … …  
    4037'''study_field''' – study_field_enum, required
    4138
    42 '''university_id''' – integer, required, foreign key to University (on delete cascade)
    43 
    4439=== '''Professor'''
    45 This entity stores professor details. Each professor belongs to a specific faculty and can be associated with universities, students (advice sessions), and subject enrollments.
     40This entity stores professor details.
    4641
    4742'''Candidate keys:''' id (primary key).
    … …  
    5550'''surname''' – varchar(255), optional
    5651
    57 '''age''' – integer, optional, range 18–80 (constrained via CHECK constraint in SQL)
    58 
    59 '''facultyid''' – integer, required, foreign key to Faculty (on delete cascade)
     52'''age''' – integer, optional, range 18–80
    6053
    6154=== '''Student'''
    62 This entity stores student details. Each student is enrolled in a faculty, can take subjects, and can have advice sessions with professors.
     55This entity stores student details.
    6356
    6457'''Candidate keys:''' id (primary key), studentindex (unique key).
    … …  
    7669'''studentindex''' – integer, required, unique (mapped to Index on the diagram)
    7770
    78 '''facultyid''' – integer, required, foreign key to Faculty (on delete restrict)
    79 
    8071=== '''Subject'''
    81 This entity stores the courses or subjects offered by a faculty. Students can enroll in these subjects.
     72This entity stores the courses or subjects offered by a faculty.
    8273
    8374'''Candidate keys:''' id (primary key).
    … …  
    8980'''name''' – varchar(255), required
    9081
    91 '''semester''' – integer, optional, range 1–8 
     82'''semester''' – integer, optional, range 1–8
    9283
    9384'''credits''' – integer, optional, range 1–10
    9485
    95 '''facultyid''' – integer, required, foreign key to Faculty (on delete restrict) (implicitly handles the offers relation)
    96 
    97 === '''Student_Subject'''
    98 This entity represents a weak entity that depends on both Student and Subject. It tracks a specific student enrollment in a subject (course instance), including grades, attendance, and status, while resolving which professor teaches this specific instance.
    99 
    100 '''Primary Key:''' Composite key consisting of (student_id, subject_id, ss_id).
     86=== '''Student_Subject (Weak Entity)'''
     87This entity represents a weak entity that depends on both Student and Subject for its identity. It tracks a specific student enrollment in a subject (course instance).
    10188
    10289'''Discriminator (Partial Key):''' ss_id.
    10390
     91'''Composite Primary Key:''' Composed of Student key + Subject key + ss_id.
     92
    10493'''Attributes:'''
    10594
    106 '''ss_id''' – integer, required. Acts as the discriminator (partial key) for the weak entity. It uniquely identifies multiple enrollments of the same student in the same subject (e.g., if a student retakes a course).
    107 
    108 '''student_id''' – integer, required, identifying foreign key to Student (on delete cascade). Part of the composite primary key.
    109 
    110 '''subject_id''' – integer, required, identifying foreign key to Subject (on delete cascade). Part of the composite primary key.
    111 
    112 '''professor_id''' – integer, required, regular foreign key to Professor (on delete restrict) (materializes the assignTo relationship from the diagram).
     95'''ss_id''' – integer, required. Acts as the discriminator (partial key/dashed underline) for the weak entity. It uniquely identifies multiple enrollments of the same student in the same subject.
    11396
    11497'''enrollment_date''' – date, required, defaults to CURRENT_DATE (mapped to Enrollment_Date).
    … …  
    120103'''absences_count''' – integer, optional, defaults to 0 (mapped to Abesense_Count on the diagram).
    121104
     105== Relationships
    122106
     107=== '''part_of'''
     108Binary 1:N relationship between University (1) and Faculty (N). Indicates which university a faculty belongs to.
    123109
    124 === '''Advice'''
    125 This associative table tracks mentorship history and formal guidance sessions between professors and students.
     110=== '''employs'''
     111Binary 1:N relationship between Faculty (1) and Professor (N). Tracks which faculty employs a professor.
    126112
    127 '''Candidate keys:''' Composite primary key (student_id, professor_id, start_date).
     113=== '''enrolled_in'''
     114Binary 1:N relationship between Faculty (1) and Student (N). Tracks which faculty a student is enrolled in.
     115
     116=== '''offers'''
     117Binary 1:N relationship between Faculty (1) and Subject (N). Defines which subjects are offered by a faculty.
     118
     119=== '''affiliated_with'''
     120Binary M:N relationship between University and Professor. Allows a professor to be affiliated with multiple universities.
     121
     122=== '''Advises'''
     123Binary M:N relationship between Professor and Student tracking mentorship.
    128124
    129125'''Attributes:'''
    130126
    131 '''student_id''' – integer, required, foreign key to Student (on delete cascade)
     127'''start_date''' – date, required
    132128
    133 '''professor_id''' – integer, required, foreign key to Professor (on delete cascade)
     129'''end_date''' – date, optional
    134130
    135 '''start_date''' – date, required (mapped to start date)
     131=== '''Teach'''
     132Binary M:N relationship between Professor and Subject. Indicates which subjects a professor can teach.
    136133
    137 '''end_date''' – date, optional (mapped to end_date)
     134=== '''enrolls'''
     135Identifying 1:N relationship between Student (1) and weak entity Student_Subject (N).
    138136
    139 === '''Affiliated'''
    140 This table materializes the relationship between Professors and Universities. While drawn as a 1:N relationship (affiliated_with), it is physically implemented as a Many-to-Many bridge table to allow flexible multi-university affiliations.
     137=== '''has'''
     138Identifying 1:N relationship between Subject (1) and weak entity Student_Subject (N).
    141139
    142 '''Candidate keys:''' Composite primary key (university_id, professor_id).
    143 
    144 '''Attributes:'''
    145 
    146 '''university_id''' – integer, required, foreign key to University (on delete cascade)
    147 
    148 '''professor_id''' – integer, required, foreign key to Professor (on delete cascade)
     140=== '''assignTo'''
     141Regular 1:N relationship between Professor (1) and weak entity Student_Subject (N). Indicates which professor is assigned to a specific course instance enrollment.
    149142
    150143== Entity-Relationship Model History
    151144
    152 v1: First version of the conceptual ER model including University, Faculty, Professor, Student, Subject, Student_Subject weak entity, advice mentorship tracking, and professor_subject competency mapping. Attributes, candidate keys, nullability constraints, and precise entity relationships defined.
     145v1: First version of the conceptual ER model including University, Faculty, Professor, Student, Subject, Student_Subject weak entity, and precise entity relationships.
     146
     147v1.1: Corrected conceptual ER representations: removed all foreign keys from entity attributes, correctly specified weak entity discriminator and identifying relationships, converted bridge tables (Affiliated, Advises) into relationship types with attributes.