Draw an ER diagram for a School Management System based on the following information.
You have been asked to implement a database for a school management system (SMS). This primarily consists of
maintaining students' information like name, address, date of birth, roll number, deparment, and so on.
Details about the school to be stored includes school's name, location. Although it is unlikely that there
would be two schools at the same place with same name,
but our SMS would like to accomodate this possibility.
Faculty members works in the school. They teaches the students. A faculty member normally teaches multiple
students at a time. Also, he can teach multiple courses to the students.
E R Diagram for School Management System
Step
We will follow the steps shown below to draw the ER diagram
Identify sets and their attributes
Draw the entities
Identify relationships
Draw the relationships
Identify mapping cardinalities and represent them
• One of the primary entities interacting with SMS is student
• Attributes of a student includes name, address, date of birth, roll, department
• Roll number can uniquely identify a student; hence, it is the primary key.
• Next entity is school
• Attributes are: name, location, id
• Id can uniquely identify a school. So, its the primary key for school.
• Faculty is our next entity
• Possible attributes are: name, address, date of joining.
• It is quite likely that there are multiple faculties with the same date.
Its also likely that they would join on the same date. Therefore, we add another attritubute, id, to uniquely identify a faculty member.
• Our final entity of concern is course
• Possible attributes are: id, title, credits
• Id is the primary key.
Having identified teh entity sets and their attributes, let's represent them in an ER diagram.
Roll* is the Primary key
Id* is the Primary key
Id* is the Primary key
Id* is the Primary key
We have identified our entity sets. Let's now try
to figure out possible relationships among them.
• Between student and school:
• Student studies in school
• Between faculty and school:
• Faculty works in school
• Between faculty and course:
• Faculty teaches course
• Between student and course:
• Student takes course
Now we will represent the entity sets, attributes and relationships
identified so far in an ER diagram.
Having represented the relationships, lets now mark
the mapping cardinalities for each relationaship.
A student studies only in one school, but many students can study in a given school.
A faculty member works only in one school,
but multiple teachers do work in a given school.
A faculty member can teach multiple courses. However, a given course is normally
taught by a single teacher.
A student can take multiple courses.
A single course can be taken by multiple students.
