Unit-2: E-R Modeling
ER Model (Entity Relationship Model);
• It is used for logical representation of data
• It provides a conceptual view of the data
Use of ER Model?
• To design a database before implementation
• To ensure that the database meets the requirements
• To avoid changes in the design and implementation later on
Entity
• An entity is an object with physical existence
• Examples: student, course
• An entity has attributes or characteristics (e.g. roll number, age, address)
Relationship
• A relationship is an association between two or more entities
• Example: a student studies a course
• Relationships are represented by a diamond in an ER diagram
Advantages of ER Model
• Provides a conceptual view of the data
• Helps in designing a database that meets the requirements
• Can be implemented using SQL (Structured Query Language).
Component of ER Diagram
Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
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.
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
The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined.
Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse.
c. Multivalued Attribute
An attribute can have more than one value.
The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.
Types of Attributes in Relational Databases/Entity Relationship Models
• Single-Valued vs. Multi-Valued Attributes
o Single-valued attribute: has only one value (e.g. registration number, age)
o Multi-valued attribute: has more than one value (e.g. mobile number,
address)
• Simple vs. Composite Attributes
o Simple attribute: cannot be broken down further (e.g. age)
o Composite attribute: composed of more than one value (e.g. student's name,
address)
• Stored vs. Derived Attributes
o Stored attribute: value is fixed and stored (e.g. date of birth)
o Derived attribute: value is derived from other attributes (e.g. age)
• Key vs. Non-Key Attributes
o Key attribute: uniquely identifies each row (e.g. registration number)
o Non-key attribute: does not uniquely identify each row (e.g. student's name,
address)
• Required vs. Optional Attributes
o Required attribute: value is mandatory (e.g. student's name)
o Optional attribute: value is not mandatory (e.g. address)
• Complex Attributes
o Composite and multi-valued attribute (e.g. student's address with multiple
phone numbers)
One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known as
one-to-one relationship.
• A One-to-One relationship means that two entities are connected, but what is their
association
For example, A female can marry to one male, and a male can marry to one female.
b. One-to-many relationship
• A one-to-many relationship is a type of relationship where one entity can have
multiple instances of another entity.
For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.
c. Many-to-one relationship:
• A many-to-one relationship is a type of relationship where many entity can have only
instances of one entity.
For example, Student enrolls for only one course, but a course can have many students.
d. Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
For example, Employee can assign by many projects and project can have many employees.
Example of ER- modeling:
The ER model based on patient, hospital, doctor:
Logical level:
Physical level data: