Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Draw an ER diagram for a University database application(DBMS)?
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 −
48K+ Views
