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.

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

Professor

This entity stores professor details.

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

Student

This entity stores student details.

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)

Subject

This entity stores the courses or subjects offered by a faculty.

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

Student_Subject (Weak Entity)

This 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).

Discriminator (Partial Key): ss_id.

Composite Primary Key: (student_id, subject_id, ss_id)

Attributes:

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.

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.

Relationships

part_of

Binary 1:N relationship between University (1) and Faculty (N). Indicates which university a faculty belongs to.

employs

Binary 1:N relationship between Faculty (1) and Professor (N). Tracks which faculty employs a professor.

enrolled_in

Binary 1:N relationship between Faculty (1) and Student (N). Tracks which faculty a student is enrolled in.

offers

Binary 1:N relationship between Faculty (1) and Subject (N). Defines which subjects are offered by a faculty.

affiliated_with

Binary M:N relationship between University and Professor. Allows a professor to be affiliated with multiple universities.

Advises

Binary M:N relationship between Professor and Student tracking mentorship.

Attributes:

start_date – date, required

end_date – date, optional

Teach

Binary M:N relationship between Professor and Subject. Indicates which subjects a professor can teach.

enrolls

Identifying 1:N relationship between Student (1) and weak entity Student_Subject (N).

has

Identifying 1:N relationship between Subject (1) and weak entity Student_Subject (N).

assignTo

Regular 1:N relationship between Professor (1) and weak entity Student_Subject (N). Indicates which professor is assigned to a specific course instance enrollment.

Entity-Relationship Model History

v1: First version of the conceptual ER model including University, Faculty, Professor, Student, Subject, Student_Subject weak entity, and precise entity relationships.

v1.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.

v1.2: Removed underline from start_date in Advises relationship (since relationships cannot have primary key attributes), renamed duplicate enrolled_in relationship to enrolls, and ensured identifying relationships for Student_Subject are formally represented.