The Entity-Relationship
Model
Database Design
Goal of design is to generate a formal specification of the database schema
Methodology:
Use E-R model to get a high-level graphical view of essential components of
enterprise and how they are related
Then convert E-R diagram to SQL DDL, or whatever database model you are
using
E-R Model is not SQL based. It's not limited to any particular DBMS. It is a
conceptual and semantic model – captures meanings rather than an actual
implementation
The E-R Model: The enterprise is viewed as set of
Entities
Relationships among entities
Symbols used in E-R Diagram
Entity – rectangle
Attribute – oval
Relationship – diamond
Link - line
Sample
ERD
Entities and Attributes
Entity: an object that is involved in the enterprise and that be distinguished
from other objects. (not shown in the ER diagram--is an instance)
Can be person, place, event, object, concept in the real world
Can be physical object or abstraction
Ex: "John", "CSE305"
Entity Type: set of similar objects or a category of entities; they are well
defined
A rectangle represents an entity set
Ex: students, courses
We often just say "entity" and mean "entity type"
Attribute: describes one aspect of an entity type; usually [and best when]
single valued and indivisible (atomic)
Represented by oval on E-R diagram
Ex: name, maximum enrollment
May be multi-valued – use double oval on E-R diagram
May be composite – use oval for composite attribute, with ovals for components
connected to it by lines
May be derived – use dashed oval
Entity Types
An entity type is named and is described by set of
attributes
Student: Id, Name, Address, Hobbies
Domain: possible values of an attribute.
Note that the value for an attribute can be a set or list of values,
sometimes called "multi-valued" attributes
E.g., (111111, John, 123 Main St, (stamps, coins))
Key: subset of attributes that uniquely identifies an entity
(candidate key)
Entity Types tend to correspond to nouns; attributes are
also nouns albeit descriptions of the parts of entities
May have null values for some entity attribute instances
Keys
Superkey: an attribute or set of attributes that uniquely identifies an
entity--there can be many of these
Composite key: a key requiring more than one attribute
Candidate key: a superkey such that no proper subset of its
attributes is also a superkey
Primary key: the candidate key chosen to be used for identifying
entities and accessing records. Unless otherwise noted "key"
means "primary key"
Alternate key: a candidate key not used for primary key
Secondary key: attribute or set of attributes commonly used for
accessing records, but not necessarily unique
Foreign key: term used in relational databases (but not in the E-R
model) for an attribute that is the primary key of another table and is
used to establish a relationship with that table where it appears as
an attribute also.
Graphical Representation in E-R
diagram
Rectangle -- Entity
Ellipses -- Attribute (underlined attributes are [part of] the primary key)
Double ellipses -- multi-valued attribute
Dashed ellipses-- derived attribute, e.g. age is derivable from birthdate and
current date.
[Drawing notes: keep all attributes above the entity. Lines have no arrows.
Use straight lines only]
Relationships
Relationship: connects two or more entities into an
association/relationship
"John" majors in "Computer Science"
Relationship Type: set of similar relationships
Student (entity type) is related to Department (entity type)
by MajorsIn (relationship type).
Relationship Types may also have attributes in the E-R
model. When they are mapped to the relational model,
the attributes become part of the relation. Represented
by a diamond on E-R diagram.
Relationship types can have descriptive attributes like
entity sets
Relationships tend to be verbs or verb
phrases; attributes of relationships are again nouns
Attributes and Roles
An attribute of a relationship type describes the
relationship
e.g., "John" majors in "CS" since 2000
John and CS are related
2000 describes the relationship - it's the value of
the since attribute of MajorsIn relationship type
The role of a relationship type names one of the related
entities
e.g., "John" is value of Student role, "CS" value
of Department role of MajorsIn relationship type
(John, CS, 2000) describes a relationship
Relationship Type
Relationship types are described by set of roles
and attributes
e.g., MajorsIn: Student, Department, Since
Think that entities are nouns; relationship types are
often verbs
students and departments are the entities (nouns) and
roles in relationship types
majors is the relationship type (verb)
i.e., "student" "majors in " "department"
Here we have used as the role name (Student) the
name of the entity type (Student) of the participant in the
relationship.
Degree of relationship
Binary – links two entity sets; set of ordered pairs (most
common)
Ternary – links three entity sets; ordered triples (rare)
N-ary – links n entity sets; ordered n-tuples (very rare)
Cardinality of Relationships
Number of entity instances to which another
entity set can map under the relationship
One-to-one: X-Y is 1:1 when each entity in X is
associated with at most one entity in Y, and each entity
in Y is associated with at most one entity in X.
One-to-many: X-Y is 1:M when each entity in X can be
associated with many entities in Y, but each entity in Y
is associated with at most one entity in X.
Many-to-many: X:Y is M:M if each entity in X can be
associated with many entities in Y, and each entity in Y
is associated with many entities in X ("many" =>one or
more and sometimes zero)
Methods 1 - 4
Method 5
Relationship Participation Constraints
Total participation
Every member of entity set must
participate in the relationship
Represented by double line from
entity rectangle to relationship
diamond
E.g., A Class entity cannot exist
unless related to
a Faculty member entity
Partial participation
Not every entity instance must
participate
Represented by single line from
entity rectangle to relationship
diamond
E.g., A Textbook entity can exist
without being related to a Class or
vice versa.
Roles
Problem: relationships can relate elements of same entity
type
e.g., ReportsTo relationship type relates two elements of Employee
entity type:
Bob reports to Mary since 2000
We do not have distinct names for the roles
It is not clear who reports to whom
Solution: the role name of relationship type need not be
same as name of entity type from which participants are
drawn
ReportsTo has roles Subordinate and Supervisor and attribute Since
Values of Subordinate and Supervisor both drawn from entity type
Employee
Optional to name role of each entity, but helpful in cases of
Recursive relationship – entity set relates to itself
Multiple relationships between same entity sets
Schema of a Relationship Type
Contains the following features:
Role names, Ri, and their corresponding entity sets. Roles
must be single valued (the number of roles is called
its degree)
Attribute names, Aj, and their corresponding domains.
Attributes in the E-R model may be set or multi-valued.
Key: Minimum set of roles and attributes that uniquely
identify a relationship
Relationship: <e1, …en; a1, …ak>
ei is an entity, a value from Ri’s entity set
aj is a set of attribute values with elements from domain of Aj
Existence Dependency and Weak
Entities
Existence dependency: Entity Y is
existence dependent on entity X is each
instance of Y must have a corresponding
instance of X
In that case, Y must have total participation
in its relationship with X
If Y does not have its own candidate key, Y
is called a weak entity, and X is strong
entity
Weak entity may have a partial key, called
a discriminator, that distinguishes instances
of the weak entity that are related to the
same strong entity
Use double rectangle for weak entity, with
double diamond for relationship connecting
it to its associated strong entity
Note: not all existence dependent entities
are weak – the lack of a key is essential to
definition