Entity Relationship (ER) Modeling - Learn with a Complete Example

Prerequisite :Basic knowledge about ER Modeling. It is recommened to read the previous topic if you have not done so before proceeding further.

Here we are going to design an Entity Relationship (ER) model for a college database . Say we have the following statements.

  1. A college contains many departments
  2. Each department can offer any number of courses
  3. Many instructors can work in a department
  4. An instructor can work only in one department
  5. For each department there is a Head
  6. An instructor can be head of only one department
  7. Each instructor can take any number of courses
  8. A course can be taken by only one instructor
  9. A student can enroll for any number of courses
  10. Each course can have any number of students

Good to go. Let's start our design.(Remember our previous topic and the notations we have used for entities, attributes, relations etc )

Step 1 : Identify the Entities

What are the entities here?

From the statements given, the entities are

  1. Department
  2. Course
  3. Instructor
  4. Student
Stem 2 : Identify the relationships
  1. One department offers many courses. But one particular course can be offered by only one department. hence the cardinality between department and course is One to Many (1:N)
  2. One department has multiple instructors . But instructor belongs to only one department. Hence the cardinality between department and instructor is One to Many (1:N)
  3. One department has only one head and one head can be the head of only one department. Hence the cardinality is one to one. (1:1)
  4. One course can be enrolled by many students and one student can enroll for many courses. Hence the cardinality between course and student is Many to Many (M:N)
  5. One course is taught by only one instructor. But one instructor teaches many courses. Hence the cardinality between course and instructor is Many to One (N :1)
Step 3: Identify the key attributes
  • "Departmen_Name" can identify a department uniquely. Hence Department_Name is the key attribute for the Entity "Department".
  • Course_ID is the key attribute for "Course" Entity.
  • Student_ID is the key attribute for "Student" Entity.
  • Instructor_ID is the key attribute for "Instructor" Entity.
Step 4: Identify other relevant attributes
  • For the department entity, other attributes are location
  • For course entity, other attributes are course_name,duration
  • For instructor entity, other attributes are first_name, last_name, phone
  • For student entity, first_name, last_name, phone
Step 5: Draw complete ER diagram

By connecting all these details, we can now draw ER diagram as given below.

ER diagram

 
 
 
 
Comments(15) Sign in (optional)
showing 1-10 of 15 comments,   sorted newest to the oldest
lavanya
2016-03-30 06:59:28 
please tell me for attendance management android app, we can take attendance is an entity. If we take attendance is an entity then what's there key attribute?
(1) (0) Reply
simileoluwa
2016-07-22 19:06:44 
Attendance could be taken as an entity. Their phone number or if they have a Social Security Number (we don't have that in Nigeria). Anything that uniquely differentiates them @lavanya. I hope it is not late.
(1) (0) Reply
vasu
2015-07-16 03:19:20 
STUDENT and COURSE ENROLLED, is an example of
a)    one-to-one relationship.
b)    One-to-many relationship.
c)    Many-to-one relationship.
d)    Many-to-many relationship

What is the correct answer ? B or C ?
(0) (0) Reply
Anonymous
2016-01-12 20:11:17 
It's 'D' because a student can enroll in any number of courses and a course can have any no. of students...
(0) (0) Reply
Hardik Desai
2015-08-31 02:28:56 
option D
AS many Student can be enrolled in many courses and many courses have many students
(0) (0) Reply
B.Bharat
2015-07-03 10:29:52 
I have text String as input for er generation then which part can i consider as attributes,entity And relationship from particular text string??
(0) (0) Reply
yash
2015-06-28 17:45:50 
1. Draw the ER diagram for the below entities showing the relationship, cardinality and optionality and Normalize to 3 NF Library(library_Name, Library_Address, Library_Contact_No) Librarian(Librarian_Name, Librarian_Address, Depart_Name) Members(Member_Name, Member_Id, Member Address, Member_Contact_No) Book(Book No, Book Name, Author_Name, Rent_Flag. Rent_Price)
(0) (0) Reply
Ritu
2015-04-17 07:00:19 
very easy to understand...please come up with more models describing one to one and one to many relationship
(0) (0) Reply
devki
2015-04-15 15:40:01 
how to go through next chapter?
(0) (0) Reply
MV
2015-03-22 08:35:55 
I could never understnd this in my 4 years college span which i understood here in 5 minutes. Thanks for the simple yet a detailed explanation!!
(0) (0) Reply
12Next Go
showing 1-10 of 15 comments
 
Add a new comment...  (Use Discussion Board for posting new aptitude questions.)

Name:
Email: (optional)
10 + 3 = (please answer the simple math question)

Post Your Comment
X  
View & Edit Profile Sign out
X
Sign in
Google
Facebook
Twitter
Yahoo
LinkedIn
X