Entity Attribute Relationship

(EAR) Diagrams
CIS 205
 (EAR) Diagrams
• Database Design • Primary Key
• Entity • Ternary
• Attributes Relationships
• Null Values • Summary of ER
• Relationships Notation
• Well-Defined ERDs • Applying the
Concepts
• Visualizing ERDs
• Complete Example
 Database Design
• Understand the problem domain. Analyze the
database requirements.
• Design a conceptual schema by creating an ER
diagram
• Design a logical schema
• Verify the design with the users and
other team members
 Database Design
• Understand the problem domain. Analyze the
database requirements.
– Write a summary specification in English
– What do you need to store in the database?
– What queries and reports do you need to
generate?
 Database Design
• Design a conceptual schema by creating an ER
diagram
– Identify entity types. Assign a singular noun to each entity
type.
– Identify relationships between entities. Use a meaningful
verb for a relationship name.
– Draw an ERD without attributes
– Assign attributes to entities & relationships
– Select the identifier (primary key) for each entity type
– Check ERD against database requirements
 Database Design
• Design a logical schema
– Translate the ERD into a relational schema
– Determine the primary key of each relation
– Check normalization (at least 3NF) will discuss
later
– Create data dictionaries
– Do database prototyping & modify the design if
necessary
 Entities in an EAR Diagram
• A “thing”; an object
– People, places,
tangible physical thing,
organizations, events,
concepts SSN
Name

• Naming convention  singular

noun instead of plural form Address
Employee
• Key Attribute  uniquely
identifies each instance of an Sex
entity
• Descriptive Attributes  non-
key attributes
 Attributes
• Properties of the
Entity Name: Car
entity Attributes VIN Color Model …
Values 123XYZ… Silver Honda …
• Each attribute has…
– a value
– domain  allowable
Entity Name: Employee
values (i.e. field type) Attributes SSN Name Age Address …
Values 123456789 Jane Doe 39 1 Ohm Dr… …
numbers alphabetic
Domain only characters 18 - 70
 Types of Attributes
• Composite vs. Simple (Atomic)
• Single-valued vs. Multivalued
• Stored vs. Derived
• Complex
 Types of Attributes

• Composite • Simple (Atomic)

– Can be divided into – Cannot be divided into
smaller subparts smaller subparts
Address

Number
City State
StreetAddress City State Zip
Street Zip

Address
ApartmentNumber

Number Street ApartmentNumber

(See Fig. 3.4)

When to use composite attributes?

When composite attribute is used as a unit.
 Single-Valued vs. Multivalued
• Single-valued • Multivalued
– Can only have one value – Can have multiple values

– Example  Age – Example 

CollegeDegrees
– Symbol 
– Symbol 
 Derived vs. Stored
• Derived • Stored
– Attribute that can be – Attribute that will be
computed stored in the database

– Not necessarily stored in – Symbol 

the database

– Symbol 
 Null Values
• A special value
• Can mean:
– Not applicable
– Unknown
• NULL Empty String
 Relationships
• An association between entities
• Relationship Name
– meaningful verb
• Role name
– signifies the role that an entity plays in each relationship
instance
• Symbol
– diamond with the relationship name displayed within the
diamond
 Relationships
• Degree of relationship
– the number of participating entities

• Unary (Recursive) Relationship

– degree of 1

• Binary Relationship
– degree of 2

• Ternary Relationship
– degree of 3
 Relationships: Constraints

• Cardinality ratio (max. cardinality)

– Max. # of relationship instances that
an entity can participate in
– 1:1, 1:N, M:N
– Symbol
• ratio displayed on diamond
• Look across notation for cardinality
1
N Department
Employee Works_For

Each employee may work for one and only one department

Each department must have working for it one or more employees

 Relationships: Constraints
• Participation (min. cardinality)
– Total participation (mandatory)
• Symbol  Double line
– Partial participation (optional)
• Symbol  Single line
• Look here notation for participation

1
N Department
Employee Works_For

Each employee may work for one and only one department

Each department must have working for it one or more employees

 Relationships: Constraints

• Cardinality ratio ()
Structural
Constraint
• Participation (optional, mandatory)
 Relationships: More Examples

• One-to-One (1:1)
– Each customer may have at most one account
– One account cannot be owned by more than one
customer 1
1 Account
Customer owns

• One-to-Many (1:N)
– Each customer can have many accounts
– Each account cannot be owned by more than one customer

N
1 Account
Customer owns
 Relationships: More Examples

• Many-to-Many
– Each customer can have many accounts
– Each account must be owned by many customers

N
N Account
Customer owns
 Relationships

• Relationship Identifier (PK,FK)

– Concatenation of identifiers of participating entity
types regardless of the cardinality
• Attributes of Relationships
– Properties of a relationship, not of an entity
– Two types of relationship attributes
• Relationship Identifier (PK,FK)
– Mandatory
– Not shown on the ERD
• Optional properties
– Must be shown on the ERD
 Well-Defined ERDs

• Basic Rules
– All entities & relationships must be connected
– Each entity must have at least one relationship
– All entity names must be unique
– Use a singular noun for an entity name and an attribute
name
– A relationship cannot be directly connected to another
relationship
– Use a meaningful verb for a relationship name whenever
possible
– Every entity must have at least one unique key (atomic or
concatenated)
 Well-Defined ERDs

• Other Tips
– Do not show the primary key of one entity in either another
entity or a relationship
– Minimize line-crossing in ERDs
– List all attributes of an entity on one side for visual simplicity
– Arrange attributes left-to-right and top-down
– Underline entity identifiers
– The data you want to store in the DB must appear
somewhere in the ERD
– Create relationships with the most direct entity first
– Minimize redundant relationships
 Primary Key

• Definition
– Uniqueness
• No two instances can have the same value in the
table
– Minimality
• There should not be any redundant attribute in a
key
 Primary Key

• Requirements & preferences

– Known at all times
– Must not have a null value (not null: entity
constraint)
– Should not be changed
– Prefer a short one
– Controlled by the database administrator
 Primary Key

• Guidelines for keys

– Use meaningful keys whenever possible
– Generate a surrogate key for a very large
composite key
– Divide large digits into chunks
• 416-555-1212 instead of 4165551212
– Record time on a 24-hour clock
 Primary Key

• Primary keys in the ER model

– PK of an Entity
• The same as the entity identifier
– PK of a relationship
• Depends upon the cardinality
– 1:1  could be the PK of either side
– 1:N  same PK as the N-side
– M:N  concatenation of PK of both sides
 Primary Key
Acct# Key of owns
Cust# Balance Cust# or Acct#
Name 1 1
Customer owns Account
Address
Phone

Acct# Key of owns

Cust# Balance Acct#
Name 1 N
Customer owns Account
Address
Phone

Acct# Key of owns

Cust# Balance
Name M N Cust# AND Acct#
Customer owns Account
Address
Phone
 Primary Key
A#
A

Aname

R1 X

B# D#

B R3 D
Bname 1 1 Dname

Object RID Primary Key

N N
A A# A#
Z
R2 Y R4 B B# B#
1
M
W C C# C#
D D# D#
C#
C E
E# R1 A#B# B#
R2 B#C# B#
Cname Ename
R3 B#D# B# or D#
R4 D#E# D#E#
 Visualizing ERDs

• Every entity becomes a table

– All attributes of the entity become attributes of the
table

• Every relationship becomes a table as follows:

– Add PK to the relationship table
– Add any non-key attribute of the relationship to the
relationship table
 Ternary Relationship

• An example of N-ary relationships

• A relationship among 3 entity types
• Requirements
– All three entities must always occur at the same time
– We need to process queries using these three
entities
• Types of Cardinality
– Can be 1:1:1, 1:M:1, 1:M:N or M:N:P
Summary of ER Notations
Attribute
Entity

Key Attribute

Weak Entity
Multivalued

Relationship

Composite Attribute

Identifying Relationship
Derived Attribute
 Applying the Concepts
1. Gather & understand 5. Identify & draw
requirements cardinality constraints
2. Identify each entity 6. Assign attributes
3. Draw ERD without 7. Assign keys
attributes 8. Check model
4. Identify each
relationship