ER diagram for college management system
This ER diagram models a simple college management system with five tables: departments, students, faculty, courses and enrollments. It shows how students and teachers belong to departments, who teaches each course, and how marks and grades are recorded. It suits students preparing DBMS assignments and developers sketching a first college database schema.
Example: ER diagram for college management system: click to open it in the editor and change it.
Entities and key attributes
| Entity | Primary key | Key attributes | Foreign keys |
|---|---|---|---|
| departments | id | name, hod_name | none |
| students | id | name, email, dob, semester | department_id |
| faculty | id | name, email | department_id |
| courses | id | code, title, credits | faculty_id |
| enrollments | id | marks, grade | student_id, course_id |
Relationships and cardinality
- departments to students (1:N): a department has many students, and each student belongs to one department through department_id.
- departments to faculty (1:N): a department employs many faculty members, and each faculty member belongs to one department.
- faculty to courses (1:N): one faculty member can teach many courses, and each course has one teacher through faculty_id.
- students to enrollments (1:N): a student can have many enrollment records.
- courses to enrollments (1:N): a course can have many enrollment records.
Together, the last two relationships make enrollments a junction table. It resolves the many-to-many link between students and courses, and it stores the result of each pairing in marks and grade.
How to adapt it for your project
- Add a unique constraint on (student_id, course_id) in enrollments so the same student cannot enroll in the same course twice.
- If a course can have several teachers, replace faculty_id in courses with a junction table between faculty and courses.
- Store the head of department as a foreign key to faculty instead of the hod_name text field.
- Add tables your college needs, such as attendance, fees or exams, and link them to students or courses.
- Make code in courses and email in students and faculty unique.
Tip: To check your cardinalities, read each relationship aloud in both directions, for example "one course has many enrollments, each enrollment is for one course." If a sentence sounds wrong, the crow's-foot ends are probably reversed.
Frequently asked questions
What are the entities in an ER diagram for a college management system?
This diagram has five entities: departments, students, faculty, courses and enrollments. Each one has an integer id as its primary key.
How are students and courses related in a college ER diagram?
They have a many-to-many relationship that is resolved by the enrollments table. Each enrollment row links one student_id to one course_id and stores the marks and grade.
Which tables use foreign keys in this college database?
Students and faculty reference departments through department_id, and courses reference faculty through faculty_id. Enrollments references both students and courses.
Make your own ER diagram with AI →