ER (Entity Relationship)
It is a high-level data model.
define the data elements and relationship for a specified system.
It develops a conceptual design for the database. It also develops a very simple
and easy to design view of data.
In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.
For example, Suppose we design a school database. In this database, the student will
be an entity with attributes like address, name, id, age, etc. The address can be
another entity with attributes like city, street name, pin code, etc and there will be a
relationship between them.
entity
attribut
es
Component of ER Diagram
1. Entity:
An entity may be any object, class, person or place.
represented as rectangles.
Weak Entity
An entity that depends on another entity called a weak entity.
The weak entity doesn't contain any key attribute of its own. The weak entity is
represented by a double rectangle.
Ellipse
used to represent an entity.
2. Attribute
The attribute is used to describe the property of an entity.
Eclipse is used to represent an attribute.
For example: id, age, contact number, name, etc. can be attributes of a student.
a. Key Attribute
represents a primary key.
represented text underlined.
Primar
y key
b. Composite Attribute
An attribute that composed of many other attributes is known as a composite
attribute.
The composite attribute is represented by an ellipse, and those ellipses are
connected with an ellipse.
c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as a
multivalued attribute. The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.
d. Derived Attribute
An attribute that can be derived from other attribute is known as a derived
attribute. It can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived from another
attribute like Date of birth.
3. Relationship
A relationship is used to describe the relation between entities.
Diamond or rhombus is used to represent the relationship.
Types
a. One-to-One Relationship
A female can marry to one male, and a male can marry to one female.
b. One-to-many relationship
Scientist can invent many inventions, but the invention is done by the only specific
scientist.
c. Many-to-one relationship
Student enrolls for only one course, but a course can have many students.
d. Many-to-many relationship
Employee can assign by many projects and project can have many employees.
Notation of ER diagram
Keys topic refer to word doc
ER diagram to Table
o Entity type becomes a table.
In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual
tables.
o All single-valued attribute becomes a column for the table.
In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of
STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of COURSE
table and so on.
o A key attribute of the entity type represented by the primary key.
In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are
the key attribute of the entity.
o The multivalued attribute is represented by a separate table.
In the student table, a hobby is a multivalued attribute. So it is not possible to
represent multiple values in a single column of STUDENT table. Hence we create a
table STUD_HOBBY with column name STUDENT_ID and HOBBY. Using both the
column, we create a composite key.
o Composite attribute represented by components.
In the given ER diagram, student address is a composite attribute. It contains CITY,
PIN, DOOR#, STREET, and STATE. In the STUDENT table, these attributes can merge
as an individual column.
o Derived attributes are not considered in the table.
In the STUDENT table, Age is the derived attribute. It can be calculated at any point of
time by calculating the difference between current date and Date of Birth.
Using these rules, you can convert the ER diagram to tables and columns and assign
the mapping between the tables. Table structure for the given ER diagram is as
below:
Relationship of higher degree
1. One-to-one (1:1)
2. One-to-many (1:M)
3. Many-to-many (M:N)
1. One-to-one
o In a one-to-one relationship, one occurrence of an entity relates to only one occurrence
in another entity.
o A one-to-one relationship rarely exists in practice.
o For example: if an employee is allocated a company car then that car can only be
driven by that employee.
o Therefore, employee and company car have a one-to-one relationship.
2. One-to-many
o In a one-to-many relationship, one occurrence in an entity relates to many occurrences
in another entity.
o For example: An employee works in one department, but a department has many
employees.
o Therefore, department and employee have a one-to-many relationship.
3. Many-to-many
o In a many-to-many relationship, many occurrences in an entity relate to many
occurrences in another entity.
o Same as a one-to-one relationship, the many-to-many relationship rarely exists in
practice.
o For example: At the same time, an employee can work on several projects, and a
project has a team of many employees.
o Therefore, employee and project have a many-to-many relationship.
