Entity-Relationship Model v01

Diagram

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.