The Entity-Relationship Model

1
The Entity-Relationship Model

• CSC 470
• Fall 08

2
ER Model

• Used to capture the conceptual design of a
  database
• It represents the logical structure of a
  database
• Three basic elements in an ER Model
• Entity types
• Relationship types
• Attributes
• We use UML notation for the ER model

3
Diagrammatic notation

• UML class represents an entity type.
• First section include entity type name.
• Second section lists attributes.
• Third section is not displayed (or used for
  derived attributes).
• Relation types represented as lines connecting
  entity types (classes).

4
Entity Type

• Entity type
• Define a group of objects with same properties or
  attributes.
• Examples EMPLOYEE, CUSTOMER, SUPPLIER entity
  types.
• Entity occurrence (or entity instance)
• Uniquely identifiable object of an entity type.
• Examples Employee John Doe, Customer Mike
  Jordan, Supplier Office Depot.
• Entity set is the set of entity occurrences.

5
ER diagram of Staff and Branch entity types
An entity type is displayed in a rectangular box.
6
Relationship Types

• Relationship type
• Define a set of meaningful associations among
  entity types.
• Examples A Branch HAS some Staff, An Employee
  WORKS ON a Project.
• Relationship occurrence
• Uniquely identifiable association, which includes
  one occurrence from each participating entity
  type.
• Relationship set is the set of relationship
  occurrences (current state of a relationship).

7
Binary and ternary relationships
Binary
Ternary
8
Relationship Types

• Recursive Relationship
• A relationship type where same entity type
  participates more than once in different roles.

9
Attributes

• Attribute
• Property of an entity or a relationship type.
• Attribute Domain
• Set of allowable values for one or more
  attributes.
• Simple Attribute (or single-valued Attribute)
• An entity has a single atomic value for the
  attribute.
• Example Employee with SSN 123-45-6789
• Composite Attribute
• Attribute composed of multiple components.
• Examples Address, Name.
• It can be nested.

10
Attributes

• Multi-valued Attribute
• Attribute that holds multiple values for each
  occurrence of an entity type.
• Example Previous jobs for an employee.
• Derived Attribute
• Attribute that represents a value that is
  derivable from value of a related attribute, or
  set of attributes, not necessarily in the same
  entity type.
• Example Age derived from DOB and Todays date.

11
Keys

• Candidate Key
• Minimal set of attributes that uniquely
  identifies each occurrence of an entity type.
• Primary Key
• Candidate key selected to uniquely identify each
  occurrence of an entity type.
• Composite Key
• A candidate key that consists of two or more
  attributes.

12
ER diagram of Staff and Branch entities and
their attributes
13
Relationship called Advertises with attributes
14
Structural Constraints

• Main type of constraint on relationships is
  called multiplicity.
• Indicate how many instances of an entity type are
  related to an instance of another entity type .
• Binary relationships are generally referred to as
  being
• one-to-one (11)
• one-to-many (1 or 1M)
• many-to-many ( or MN)

15
Multiplicity of Staff Manages Branch (11)
relationship
1
1
16
Multiplicity of Staff Oversees PropertyForRent
(1) relationship type
1
M
1M
17
Multiplicity of Newspaper Advertises
PropertyForRent () relationship
M
M
MM
18
Multiplicity of ternary Registers relationship
1
1
M
A staff/branch value pair registers several
clients