Draw an ER diagram for a University database application(DBMS)?

DBMSDatabaseBig Data Analytics

Problem

Drawing of ER model of university database application considering the constraints −

  • A university has many departments.

  • Each department has multiple instructors (one person is HOD). Here the HOD refers to the head of department.

  • An instructor belongs to only one department.

  • Each department offers multiple courses, each subject is taught by a single instructor.

  • A student may enroll for many courses offered by different departments.

Solution

Follow the steps given below to draw an Entity Relationship (ER) diagram for a University database application −

Step 1 − Identifying the entity sets.

The entity set has multiple instances in a given business scenario.

As per the given constraints the entity sets are as follows −

  • Department

  • Course

  • Student

  • Instructor

Head of the Department (HOD) is not an entity set. It is a relationship between the instructor and department entities.

Step 2 − Identifying the attributes for the given entities

  • Department − the relevant attributes are department Name and location.

  • Course − The relevant attributes are courseNo, course Name, Duration, and prerequisite.

  • Instructor − The relevant attributes are Instructor Name, Room No, and telephone number.

  • Student − The relevant attributes are Student No, Student Name, and date of birth.

Step 3 − Identifying the Key attributes

  • Department Name is the key attribute for Department.

  • CourseNo is the key attribute for Course entity.

  • Instructor Name is the key attribute for the Instructor entity.

  • StudentNo is the key attribute for Student entities.

Step 4 − Identifying the relationship between entity sets

  • The department offers multiple courses and each course belongs to only one department, hence cardinality between department and course if one to many.

  • One course is enrolled by multiple students and one student for multiple courses. Hence, relationships are many to many.

  • One department has multiple instructors and one instructor belongs to one and only one department, hence the relationship is one to many.

  • Each department has one “HOD” and one instructor is “HOD” for only one department, hence the relationship is one to one. Here, HOD refers to the head of the department.

  • One course is taught by only one instructor but one instructor teaches many courses hence the relationship between course and instructor is many to one.

The relationship between instructor and student is not defined because of the following reasons −

  • There is no significance in the relationship.

  • We can always derive this relationship indirectly through course and instructors, and course and student.

Step 5 − Complete ER model

The complete ER Model is as follows −

raja
Published on 03-Jul-2021 14:12:20
Advertisements