Ans-:
Defining the E-R diagram of
database:
Here we are going to design an Entity Relationship (ER) model for
a college database.
we have the following statements.
●
A college contains many
departments
●
Each department can offer any
number of courses
●
Many instructors can work in a
department
●
An instructor can work only in one
department
●
For each department, there is a
Head
●
An instructor can be head of only
one department
●
Each instructor can take any
number of courses
●
A course can be taken by only one
instructor
●
A student can enroll for any
number of courses
●
Each course can have any number of
students
Step 1: Identify the Entities
What are the entities here?
From the statements given, the entities are
Department
Course
Instructor
Student
Step 2: Identify the relationships
●
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)
●
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)
●
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)
●
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)
●
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.
