== '''Entity-Relationship Model v01'''


== Diagram

[[Image(ERModel_v1.2.png)]]


== Data Requirements

=== '''University'''
This entity stores information about universities. It defines which faculties belong to which university and tracks affiliations with professors.

'''Candidate keys:''' id (primary key).

'''Attributes:'''

'''id''' – serial (integer), required, unique

'''name''' – varchar(255), required

'''location''' – varchar(100), optional

'''isprivate''' – boolean, required (mapped to is_private on the diagram)

=== '''Faculty'''
Represents a faculty within a university. It organizes students, professors, and subjects. Each faculty belongs to one university.

'''Candidate keys:''' id (primary key).

'''Attributes:'''

'''id''' – serial (integer), required, unique

'''name''' – varchar(255), required

'''location''' – varchar(100), optional

'''study_field''' – study_field_enum, required

'''university_id''' – integer, required, foreign key to University (on delete cascade)

=== '''Professor'''
This entity stores professor details. Each professor belongs to a specific faculty and can be associated with universities, students (advice sessions), and subject enrollments.

'''Candidate keys:''' id (primary key).

'''Attributes:'''

'''id''' – serial (integer), required, unique

'''name''' – varchar(255), required

'''surname''' – varchar(255), optional

'''age''' – integer, optional, range 18–80 (constrained via CHECK constraint in SQL)

'''facultyid''' – integer, required, foreign key to Faculty (on delete cascade)

=== '''Student'''
This entity stores student details. Each student is enrolled in a faculty, can take subjects, and can have advice sessions with professors.

'''Candidate keys:''' id (primary key), studentindex (unique key).

'''Attributes:'''

'''id''' – serial (integer), required, unique

'''name''' – varchar(255), required

'''surname''' – varchar(255), optional

'''location''' – varchar(100), optional

'''studentindex''' – integer, required, unique (mapped to Index on the diagram)

'''facultyid''' – integer, required, foreign key to Faculty (on delete restrict)

=== '''Subject'''
This entity stores the courses or subjects offered by a faculty. Students can enroll in these subjects.

'''Candidate keys:''' id (primary key).

'''Attributes:'''

'''id''' – serial (integer), required, unique

'''name''' – varchar(255), required

'''semester''' – integer, optional, range 1–8 

'''credits''' – integer, optional, range 1–10

'''facultyid''' – integer, required, foreign key to Faculty (on delete restrict) (implicitly handles the offers relation)

=== '''Student_Subject'''
This entity represents a specific student enrollment in a subject (course instance). It tracks grades, attendance, and status. It also acts as the central link resolving which professor teaches this specific instance to the student.

'''Candidate keys:''' ss_id (primary key).

'''Attributes:'''

'''ss_id''' – serial (integer), required, unique (mapped to ss_id on the diagram)

'''student_id''' – integer, required, foreign key to Student (on delete cascade)

'''subject_id''' – integer, required, foreign key to Subject (on delete cascade)

'''professor_id''' – integer, required, foreign key to Professor (on delete restrict) (materializes the assignTo relationship from the diagram)

'''enrollment_date''' – date, required, defaults to CURRENT_DATE (mapped to Enrollment_Date)

'''status''' – varchar(20), optional, defaults to 'Enrolled'

'''final_grade''' – integer, optional (mapped to Final_Grade)

'''absences_count''' – integer, optional, defaults to 0 (mapped to Abesense_Count on the diagram)

=== '''Advice'''
This associative table tracks mentorship history and formal guidance sessions between professors and students.

'''Candidate keys:''' Composite primary key (student_id, professor_id, start_date).

'''Attributes:'''

'''student_id''' – integer, required, foreign key to Student (on delete cascade)

'''professor_id''' – integer, required, foreign key to Professor (on delete cascade)

'''start_date''' – date, required (mapped to start date)

'''end_date''' – date, optional (mapped to end_date)

=== '''Affiliated'''
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.

'''Candidate keys:''' Composite primary key (university_id, professor_id).

'''Attributes:'''

'''university_id''' – integer, required, foreign key to University (on delete cascade)

'''professor_id''' – integer, required, foreign key to Professor (on delete cascade)

== Entity-Relationship Model History

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.