ER-diagram

ER-Model
• The E-R data models is based on a perception of real
world that consist of a collection of basic objects
called entities and relationship among these
objects.
• In an E-R model a database can be modeled as a
collection of entities, and relationship among
entities.
Entity-Relationship Diagram

Once the entity types, relationships types, and their

corresponding attributes have been identified, the
next step is to graphically represent these components
using entity-relationship (E-R) diagram.
An E-R diagram is a specialized graphical tool that
demonstrates the interrelationships among various
entities of a database.
It is used to represent the overall logical structure of
the database.
While designing E-R diagrams, the emphasis is on the
schema of the database and not on the instances.
This is because the schema of the database is
changed rarely; however, the instances in the entity
and relationship sets change frequently.
Thus, E-R diagrams are more useful in designing the
database.
E-R diagram focuses high level database design and
hides low level details of database representation
therefore it can be used to communicate with users
of the system while collecting information.
Notation of E-R diagram

Rectangles represent entity sets.

Diamonds represent relationship sets.
Lines link attributes to entity sets and entity sets to
relationship sets.
Ellipses represent attributes
Double ellipses represent multivalued attributes.
Dashed ellipses denote derived attributes.
Underline indicates primary key attributes
Double Lines indicate total participation of an entity set
in a relationship set.
Double Rectangles represent weak entity sets.
Double Diamonds represent identifying relationship set
for weak entity set.
Attribute
• Simple or Atomic
• Composite
• Single-valued
• Multi-valued
• Derived
Domain – the set of permitted values for each attribute
primary key :
Strong and weak Entity
An entity set that does not have a primary key is
referred to as a weak entity set
Relationship and Relationship Sets
Association between two or more entities is called
relationship.
For example, Teacher teaches student, here teaches is
the association between entities Teacher and student.
A relationship set is a set of relationships of the same
type.
In another way, we can say that association between
two entity sets is called relationship set.
For example, Teacher teaches Student, here teaches is
the association between entity sets
Relationships are represented by diamond symbol in E-
R diagram.
A relationship set may also have attributes called descriptive
attributes. For example, the teach relationship set between entity
sets teacher and students may have the attribute teaches-date
Degree of a relationship
Number of entity sets that participate in a relationship
set is called degree of the relationship set.
On the basis of degree, relationships can be divided as
below:
• Unary Relationship
• Binary Relationship
• N-ary Relationship
Unary Relationship
If only one entity set participates in relationship more
than it is called unary relationship.
Here same entity set participates in relationship twice
with different roles.
Role names are specified above the link joining entity
set and relationship set.
This type of relationship set is sometimes called a
recursive relationship set.
In the example below, employee participates twice in
relationship, once as a manager and again as worker.
Binary Relationship
A Relationship set in which two entity sets participate
is called binary relationships.
In another way, we can say that relationship sets of
degree 2 are called binary relationship. This is the most
common type of relationship in database systems. For
example, the ―teaches― relationship given below has
degree two and hence it is binary relationship
 N-ary Relationship
• Relationship set in which more than two entity sets
involves is called n-ary relationship.
• Ternary and quaternary relationships are special cases of
n-ary relationship. For example:
Constraints on ER Model
Relationship sets in ER model usually have certain
constraints that limit the possible combinations of
entities that may involve in the corresponding
relationship set.
• Database content must confirm these constraints.
The most important constraints are:
• Mapping cardinalities and
• Participation constraints
Mapping Cardinality Constraints
ER model constraint that describes maximum number
of possible relationship occurrences for an entity set
participating in a given relationship type is called
mapping cardinality.
• It is also termed as cardinality ratio. On the basis of
cardinality ratio, relationships can be categorized
into:
• One-to- One
• One-to- Many
• Many-to-One
• Many-to-Many
• We express cardinality constraints by drawing either
a directed line ( ), signifying “one” or
• an undirected line (—), signifying “many”, between
the relationship set and the entity set.
One-to-One Relationship
• If every entity in A is associated with at most one
entity in B and vice-versa then the relationship is
called one-to-one relationship.
• The following figure shows one to one mapping
cardinality between entity sets A and B.
• For example in the ER diagram given below, every
college has only one Principal and a person can be
Principal of only one college therefore it shows one-
to-one relationship between college and principal.
One-to-Many Relationship:
If an entity in A can be associated with any number
(zero or more) of entities in B but every entity in B can
be associated with at most one entity in A then it
is called one-to-many relationship.
For example, a college can have any number of rooms
but room can have only one college.
Thus ER diagram given below is an example of one-to-
many relationship
Figure: One-to-many mapping between two sets
Many-to-One Relationship
If very entity in A can be associated only one of entities
in B but an entity in B can be associated with any
number of entities in A, then it is called many-to one
relationship.
For example, players play in a ground. Therefore ER
diagram given below is an example of many-to-one
relationship.
Many-to-Many Relationship
If an entity in A can be associated with any number of
entities in B and vice versa then it is called many-to-
many relationship. For example, a student can enroll
into more than one subject and a subject can be
enrolled by many students.
Therefore ER diagram given below is an example of
many-to-many relationship.
Participation Constraints
Constraint on ER model that determines whether all or only
some entity occurrences participate in a relationship is
called participation constraint.
• It specifies whether the existence of an entity depends on
its being related to another entity via the relationship
type. There are two types of participation constraints:
• Total Participation Constraints and
• Partial Participation Constraints.
• The participation of an entity set A in a relationship set R is said to
be total if every entity in A participates in relationship at least
once.
The participation of an entity set A in a relationship set R is said to
be partial if only some of the members of an entity set A
participate in relationship.
• Total participation and partial participation is denoted by single
line and double line in ER diagrams respectively.
For example, consider Customer and Loan entity sets
in a banking system, and a relationship set borrower
between them indicates that only some of the
customers have Loan but every Loan should be
associated with some customer.
Therefore there is total participation of entity set
Loan in the relationship set borrower but participation
of entity set customer is partial in relationship set
borrower.
Here, Loan entity set cannot exist without Customer
entity set but existence of Customer entity set is
independent of Loan entity set.
Keys
Set of one or more attributes whose values are distinct
for each individual entity in the entity set is called key,
and its values can be used to identify each entity
uniquely.
For example, the Name attribute is a key of the entity
set Company can be a key because no two companies
are allowed to have the same name.
There are different types of keys which are:
• Super key
• Candidate key
• Primary key
• Composite key
• Foreign key
Super Key: A super key of an entity set is a set of one or more
attributes whose values uniquely determine each entity in the entity set.
If K is a super key and any superset of K is also super key.
For example student-id attribute of the entity set student is sufficient to
distinguish one student entity from another.
Thus the student-id is a super key. Similarly the set of attributes
{studentid, name, program, semester section}
is also a super key of the entity set student.
Candidate Key:
A candidate key of an entity set is a minimal super key. That is a super
key which does not have any proper subset is called candidate key.
For example, student-id is candidate key of the entity set student but set
of attributes
{studentid, name, program, semester, section} is not candidate key of
the entity set student because it has proper subset
{student-id, program, semester} which is also key.
All candidate keys are super keys but vice versa is not true.
Primary key:
A primary key is a candidate key that is chosen by the
database designer as the principle means of uniquely
identifying entities within an entity set.
There may exist several candidate keys, one of the
candidate keys is selected to be the primary key.
For example, entity set student have two candidate keys:
{student-id}, and {student-id, program, semester},
If database designer choose student-id for the purpose of
uniquely identifying entities within entity set then it
becomes primary key.
Primary key must satisfy following two characteristics:
• It cannot be null
• It cannot be duplicate
Composite Key
If a primary key contains more than one attribute then it is called
composite key.
For example, if database designer choose student-id as primary key
then it not composite key but if database designer choose {student-
id, program, semester} as primary key then it is called composite key.

Foreign key:
A foreign key (FK) is an attribute or combination of attributes that is
used to establish and enforce relationship between two relations
(table).
A set of attributes that references primary key of another table is
called foreign key.
For example, if a student enrolls in program then program-id
(primary key of relation program) can be used as foreign key in
student relation,
Eg.
Example: Consider a bus ticketing system that records
information about the passenger, bus and route.
Passenger is assigned to a bus travels to route. A bus
contains many passengers and a passenger can be
assigned into only one bus.
Many buses travel in same route but a bus can travel in
only one route. The attributes of passenger are pid
(unique), gender and telephone (multi-valued).
Similarly bus contains regno (unique) and color and
route contains rid (unique), distance and rate (based
on distance).
Now draw the E-R diagram to represent this situation.
Eg2.
Construct an E-R diagram for a hospital with a set of
patients and a set of medical doctors. Associate with
each patient a log of the various tests and examinations
conducted.
OR
patients (patient-id, name, insurance, date-admitted, date-
checked-out)
doctors (doctor-id, name, specialization)
test (testid, testname, date, time, result)
doctor-patient (patient-id, doctor-id)
test-log (testid, patient-id) performed-by (testid, doctor id)
Construct an E-R diagram for a car-insurance company whose customers own one
or more cars each. Each car has associated with it zero to any number of recorded
accidents.
Example: Consider the following relations:
Computer (c-no, price)
Student (roll, name, grade)
Faculty (fname, credit-hours)
Uses ( c-name, roll)
Belongs (c-no, fname)
Make E-R diagram for above database.
Draw an E-R diagram for book shop information. The
book club has members. The book club sells book to its
member. The member places orders for books, which the
book club fulfills. Each order contains one or more than
one books. The book is written by author. The publisher
publishes the books. An author can write more than one
book and a book can have more than one author. A book
is published by a publisher, but a publisher publishes
many books. A member can place more than one order.
(Assume necessary attributes)
A publishing company produces books on various
subjects. The books are written by authors who specialize
in one particular subject.
The company employs editors who no necessarily being
specialists in a particular area each take sole
responsibilities for editing one or more book publications.
Every book require some items for publication. These
items supplied by suppliers.
One supplier can supply many items. Shop owner buys
books form the publisher. Shop owner can buy many
books form the publisher. Shop owner can buy many
books but one book can be bought by one shop owner
only. Books are uniquely identified by book id.
A university registrar’s office maintains data about
the following entities:
(a)courses, including number, title, credits, syllabus,
and prerequisites; (b) course offerings, including
course number, year, semester, section number,
instructor(s), timings, and classroom; (c) students,
including student-id, name, and program; and (d)
instructors, including identification number, name,
department, and title.
Further, the enrollment of students in courses and
grades awarded to students in each course they are
enrolled for must be appropriately modeled.
Construct an E-R diagram for the registrar’s office.
Document all assumptions that you make about the
mapping constraints.
ER-diagram for University
Consider a database used to record the marks that
students get in different exams of different course
offerings.
• ER diagram for Company database system
Extended E-R model (EER model)
• The EER model includes all of the concepts
introduced by the ER model.
• Additionally it includes the concepts of a subclass
and super class, along with the concepts
of specialization and generalization.
• There are basically four concepts of EER-Model:
• subclass/superclass relationship
• Specialization and Generalization
• Aggregation
• Categories
Why EER?
• The ER modeling concepts are not sufficient for
representing new database applications,
• which have more complex requirements than do the
more traditional applications.
• Additional semantic data modeling concepts must be
used to represent these requirements as accurately
and clearly as possible.
Subclass and superclass
• The class that is derived from another class is called a
subclass.
• The class from which a subclass derives is called the
superclass.
• The following figure illustrates these two types of
classes:
• An entity type may have additional meaningful sub-
groupings of its entities.
• Example:
EMPLOYEE may be further grouped into
{SECRETARY, ENGINEER, TECHNICIAN, MANAGER,
SALARIED_EMPLOYEE, HOURLY_EMPLOYEE …}
Specialization and
Generalization
• The process of defining a set of subclasses from a superclass
is known as specialization.
• The set of subclasses that form a specialization is defined on
the basis of some distinguishing characteristics of the
entities in the superclass.
{SECRETARY, ENGINEER, TECHNICIAN}
is a specialization of EMPLOYEE based on the job type of each
entity.
The same entity type may have several specializations based on
different distinguishing characteristics.
The EMPLOYEE entity type may have two specializations:
– Based on the methods of pay:
{SALARIED_EMPLOYEE, HOURLY_EMPLOYEE}
– Based on the type of job:
{SECRETARY, ENGINEER, TECHNICIAN}
Generalization
• It is a bottom-up design process.
• Here, we combine a number of entity sets that share
the same features into a higher-level entity set.
• The original classes become the subclass of the
newly formed generalized superclass.
• The reason, a designer applies generalization is to
emphasize the similarities among the entity sets and
hide their differences.
• Specialization and generalization are simple
inversions of each other they are represented in an
E-R diagram in the same way.
• The terms specialization and generalization are used
interchangeably.
Example:
• CAR, TRUCK generalized into VEHICLE; both
CAR,TRUCK become subclasses of the super class
VEHICLE.
• We can view {CAR, TRUCK} as a specialization of
VEHICLE
• Alternatively, we can view VEHICLE as a
generalization of CAR and TRUCK
Constraints on Specialization and Generalization

The constraints apply to a specialization/generalization

may be categorized into three parts:
Part 1:
• Condition defined constraints
• User-defined constraints
Part 2:
• Disjoint Constraint
• Overlapping constraint
Part 3:
Completeness Constraint or Participation constraint
• Total generalization/Specialization
• Partial generalization/Specialization
Condition defined constraints
• If we can determine exactly those entities that will
become members of each subclass by a condition,
the subclasses are called predicate-defined (or
condition-defined) subclasses.
• Here, condition is a constraint that determines
subclass members.
• For example, if the employee entity set has an
attribute job-type, we can specify the condition of
membership in the secretary subclass by a condition
(job-type=“secretary”), which we call defining
predicate of the subclass.
User-defined constraints

• If no condition determines membership, the subclass

is called user-defined.
• Membership in a subclass is determined by the
database users by applying an operation to add an
entity to the subclass.
• Membership in the subclass is specified individually
for each entity in the superclass by the user.
Disjoint Constraint
It specifies that the subclasses of the specialization
must be disjoint.
Here an entity can be a member of at most one of the
subclasses of the specialization and it is represented by
d in EER diagram.
Overlapping constraint
It specifies that the subclasses are not constrained to
be disjoint, i.e., the same entity may be a
member of more than one subclass of the
specialization and it is represented by o in EER
diagram.
The differences between the
specialization and generalization
The specialization process corresponds to a top-down
conceptual refinement process during conceptual
schema design.
• we typically start with an entity type and then define
subclasses of the entity type by successive
specialization.
• The generalization process corresponds to a bottom-
up conceptual synthesis.
– we typically start with an entity type of subclasses
and then define super classes of the entity type by
successive generalization.
UNION Types or Categories
• It is possible that single superclass/subclass
relationship has more than one super-class
representing different entity types.
• In this case, the subclass will represent a collection of
objects that is (a subset of) the UNION of distinct
entity types. we call such a subclass a union type or a
category.
• A category has two or more super classes that may
represent distinct entity types, whereas non-category
superclass/subclass relationships always have a single
superclass.
• Two categories in car registration database.
– OWNER is a subclass of the union of PERSON,
BANK, and COMPANY
– REGISTRERED_VEHICLE is a subclass of the union of
CAR and TRUCK
• An entity that is a member of OWNER must exist in
only one of the superclass.
• Attribute inheritance works more selectively in the
case of categories.
Example: Database for vehicle registration, vehicle
owner can be a person, a bank (holding a lien on a
vehicle) or a company.
• Category (subclass) OWNER is a subset of the union
of the three super classes COMPANY, BANK, and
PERSON
• A category member must exist in at least one of its
superclasses
Note: It differs from shared subclass, which is subset of
the intersection of its superclasses (shared subclass
member must exist in all of its superclasses).

Aggregation:
One of the limitations of E-R model is that it cannot
express relationship among relationships.
One of the solutions in such a situation is using
aggregation.
Aggregation is an abstraction in which relationship sets
(along with their associated entity sets) are treated as
higher-level entity sets and can participate in relationships.
Using High-Level Conceptual Data Models for
Database Design

The first step shown is requirements collection and

analysis.
During this step, the database designers interview
prospective database users to understand and
document their data requirements.
The result of this step is a concisely written set of users’
requirements.
These requirements should be specified in as detailed
and complete a form as possible. In parallel with
specifying the data requirements, it is useful to specify
A simplified diagram to illustrate
the
main phases of database design.
• the known functional requirements of the application.
These consist of the userdefined operations
(or transactions) that will be applied to the database,
including both retrievals and updates.
In software design, it is common to use data flow
diagrams, sequence diagrams, scenarios, and other
techniques to specify functional requirements.
Once the requirements have been collected and
analyzed, the next step is to create a conceptual schema
for the database, using a high-level conceptual data
model.
This step is called conceptual design. The conceptual
schema is a concise description of
• The next step in database design is the actual
implementation of the database, using a commercial
DBMS.
• Most current commercial DBMSs use an
implementation data model—such as the relational or
the object-relational database model—so the
conceptual schema is transformed from the high-level
data model into the implementation data model.
• This step is called logical design or data model
mapping; its result is a database schema in the
implementation data model of the DBMS.
• Data model mapping is often automated or semi
automated within the database design tools.
• The last step is the physical design phase, during
which the internal storage structures, file
organizations, indexes, access paths, and physical
design parameters for the database files are specified.
In parallel with these activities, application programs
are designed and implemented as database
transactions corresponding to the high level
transaction specifications.