Entity Relation Diagram | University Management System Part 1

Exciting stuff today people. Today , we are actually going to get our hands-dirty and do a hands-on practice for designing a database management system by creating ERD Case Study for University Management System . In case , you need an overview of previous articles :

  1. What is a database?
  2. Database management system
  3. DBMS Models & Relationships
  4. Data Modelling & Crow’s Foot Notation

In all these articles , we have tried to keep things simple and use visual examples to explain everything . You can go through them and decide for yourself if they help you!

We will design a University Database Management System  today , because you’ll be able to understand the complete requirements for such scenario. Before we rush towards designing  , I want to introduce you to a very simple technique to crack case study.  Use this and the whole entity-relation diagram will be created before you know it.

There are four steps in designing a ERD for a DBMS .

  1. Identify Entity and members
  2. Decide relationships and Cardinality and Modality
  3. Draw Entities separately
  4. Connect relationships and entities

 

Now you know what to do with the user requirement ,  Just go through the entire case study first and then apply these four steps on it.

ERD Case Study :

In a University  , there are several departments and each department has a head of department who belongs to Faculty. Department have a name , phone extension , specific mailing address and Students that belong to the department. Students can belong to only one Department at a time and Department can have more than one or no Student

Students and faculty have names and unique identification numbers , with address , age , gender and other information. Student studies different Courses offered by University . Faculty teaches these Courses . In each semester one student can take more than one course and Faculty can teach more than one courses . Faculty members can teach in multiple Departments. Each course can be taught by many faculty members or no one

Faculty members are also working on multiple research projects. These projects are funded by government and university. One project can have more than one faculty member and one faculty member can work on more than one project

 

Huff , Looks like a long task . Lets apply our four steps on this requirement. Think of them and study this requirement again.

Identify Entity and Members  ;

Long ago ,  we told you how to identify entities . Remember? No? No worries  .  You can find it  ERD Terminologies . Start identifying nouns in above statement and make them bold characters.

In a University  , there are several departments and each department has a head of department who belongs to Faculty. Department have a name , phone extension , specific mailing address and Students that belong to the department. Students can belong to only one Department at a time and Department can have more than one or no Student

Students and faculty have names and unique identification numbers , with address , age , gender and other information. Student studies different Courses offered by University . Faculty teaches these Courses . In each semester one student can take more than one course and Faculty can teach more than one courses . Faculty members can teach in multiple Departments. Each course can be taught by many faculty members or no one

Faculty members are also working on multiple research projects. These projects are funded by government and university. One project can have more than one faculty member and one faculty member can work on more than one project

Decide Relationships , Cardinality and Modality

No idea how to do it? Go  ERD Terminologies ERD Terminologies. Simply identify verbs and identify them . Let’s make them bold Italic characters,

In a University  , there are several departments and each department has a head of department who belongs to Faculty. Department have a name , phone extension , specific mailing address and Students that belong to the department. Students can belong to only one Department at a time and Department can have more than one or no Student

Students and faculty have names and unique identification numbers , with address , age , gender and other information. Student studies different Courses offered by University . Faculty teaches these Courses . In each semester one student can take more than one course and Faculty can teach more than one courses . Faculty members can teach in multiple Departments. Each course can be taught by many faculty members or no one

Faculty members are also working on multiple research projects. These projects are funded by government and university. One project can have more than one faculty member and one faculty member can work on more than one project

Draw Entities  & Attribute Separately :

You may wonder about the members , as they can sometimes are missing , so we add the missing attributes that are not in the requirement by the knowledge of the industry we are designing the DBMS for . A primary key is a must attribute for  every entity .

Student have Name , age , gender , address , phone Number , Roll Number , Semester , Course_ID and Student_ID. Faculty have Name , age , gender , address , phone Number ,  Semester , Course_ID, Grade , Salary , Faculty_ID and designation. Course have Name , Code , Student_ID , Faculty_ID , Department_ID and Course_ID. Department have Name , Student_ID , Faculty_ID and Department_ID. Research Project – Project_ID, Faculty_ID , Name , Duration.

See Below the diagramERD Case StudyCreate Relationships Between Entities.

We know the relationships from above steps and also what will be the cardinality and modality. Using Crow’s Foot Notation  . We have combined them .

ERD Case Study

If we have to read this diagram , this is how it will go . Reading symbols at the other end

Student-Department ; One Student Belongs to One and Only one Department

Department-Student : Department can have more than one student and no Student. ( *Business Logic ) .

Student-Course  : Student must have one course and can have more than one.

Course-Student : One course can be offered to many students or no students at all

Course-Faculty : One course can be taught by many faculty member or no one (* Business Logic )

Faculty-Course : One faculty member will teach one course or more than one courses

Can you do the same for Research Project and Faculty? Comment below . If you can.

*Business Logic : While you design an ERD  . Never ever do anything with common sense and stick strictly with the logic , common sense says if department doesn’t have a student, how can it exist? But the user requirement says it can, Hence it will. Always stick with the user requirements

Concluding this long article , we have finally connected all the dots ( previous articles ) and develop this article in which we designed a ERD. Next step is to normalise this ERD and resolve relationships .

Also See : ERD Case Study Part 2 University Management Systems

If you liked this . Please  , share with your fellows and subscribe us for more articles. We will soon be going towards query writing as well.

Happy Learning!
Cheers

6 thoughts on “Entity Relation Diagram | University Management System Part 1

Leave a Reply