The Entity Relationship Model

1
The Entity Relationship Model

• ER Diagramming

2
The Steps of Database Design

• Requirements Analysis
• Conceptual Design
• Logical Design
• Physical Design

3
The Entity Relationship Model

• The top-down approach
• This data model allows us to describe data from a
  real-world enterprise in terms of objects and
  their relationships
• An iterative process
• A team-oriented process, with all business
  managers (or designates) involved
• Must validate with a bottom-up approach

4
Entities

• Entity Type describes the intension for a set
  of entities that share the same attributes. Must
  have an independent existence
• Entity occurrence a distinguishable object
• Entity Set a collection of similar entities

5
Relationships

• Relationship Type describes a set of meaningful
  associations among entity types. Must be uniquely
  identifiable between one or more entities.
• Relationship occurrence a distinguishable
  association among entities
• Relationship Set a collection of associations
• Recursive Relationship when an entity
  associates with itself
• Degrees of a relationship type
• Unary - recursive
• Binary
• Ternary
• N-ary

6
Attribute

• A property of an entity or relationship
• Consists of a
• Description
• Domain
• Classified as
• Simple atomic values with independent existence
• Composite multiple components
• Single-valued
• Multi-valued atypical in relational model
• Derived determined from values in other
  attributes

7
Keys

• Candidate key a minimal set of attributes that
  uniquely identifies each occurrence of an entity
  type
• Primary key a selected candidate key for an
  entity
• Composite key indicates that two or more
  attributes exist in the key

8
Weak Entities (vs Strong Entities)

• Attributes do not allow uniqueness (i.e., no key
  within the entity itself
• Identifiable only in conjunction with the primary
  key of another strong entity in a 11 or 1N
  relationship
• Synonyms Child, Dependent, Subordinate
• Must have total participation on the weak entity
  side

9
Structural Constraints

• Key Constraints
• The number of possible occurrences of an entity
  type associated with another entity type
• Cardinality ratio
• 11
• 1 (1N) 1..0 1..
• (NN)
• Participation Constraints
• Mandatory (Total) every entity must participate
• Optional (Partial) 0 to total participation
• Multiplicity
• The combination of key and participation
  constraints

10
Aggregation

• Differs from relationships, which associate
  entities
• Useful when a relationship set participates in
  another relationship set

11
Alternate diagrammatic methods
12
Diagrammatic translations
staffTotal
staffNo
branchNo
Manages
Staff
Branch
1
N
Name
Name
Manages gt
1..1
0..1
13
Class Hierarchies

• Class Hierarchies
• Entities can be placed in subclasses
• Inheritance by attributes of the subclass
  subclass ISA class
• Queries on a class must consider the subclass
  entities as well
• There can be multiple ISA nodes

14
Class Hierarchies

• Can be viewed in 2 ways
• Class specialized into subclass
• Subclasses generalized by class
• Constraints on ISA hierarchies
• Overlap
• Covering
• Why have them?
• Specific descriptive attributes
• To easily identify all participants

15
Entity Relationship Diagrams

• Fan traps ambiguous ternary relationships
• Who works where?
• Chasm traps missing binary relationships among
  ternary relationships
• The lost project

Staff
Division
Department
lt Employees
Operates gt
Department
Staff
Project
Employees gt
Works on gt
16
ER conversion

• ER diagrams communicate through entities and
  relationships, but are not designed for data
  storage and retrieval
• Converting to a relational model requires a
  step-wise process that considers the conversion
  of both into tables

17
ER Conversion

• Make tables easy to explain
• Identify and apply keys to
• eliminate unnecessary redundancies
• avoid spurious tuples on joins
• Example
• Minimize nulls
• Verify attributes

Steps pSP pPD SP?PD
18
ER Conversion

• Identify all entities, relationships and
  aggregations
• Identify and type all attributes
• Simple or Composite
• Single or Multi-valued
• Derived
• List and assign all attributes to an entity,
  watching for
• Multiple entities for an attribute
• Data as attributes

19
ER Conversion

• Strong Entity Types
• Create table, including all attributes
• Determine primary key
• Weak Entity Types
• Create table, including all attributes and
  primary key from parent(s)
• Determine primary key from owner and weak entity
  attributes

20
ER Conversion

• Relationship types
• 1 relationship
• Copy primary key from table on 1 side to table on
  N side
• Make copied column foreign key
• relationship
• Create table that includes all attributes of
  relationship
• Copy primary keys from each table participating
  in relationship, making them foreign keys
• Create primary key from collection of foreign keys

21
ER Conversion

• 11 relationship
• Total participation on both sides
• Merge tables into one table, or
• Copy primary key from one table to the other and
  make copied column a foreign key
• Partial participation on one side
• Copy primary key of partial participant into
  total participant and make copied column a
  foreign key
• Partial participation on both sides
• Copy primary key from one participant into the
  other and make the copied column a foreign key

22
ER Conversion

• 11 recursive relationship
• Total participation on both sides
• Create one table with two copies of primary key,
  using one as foreign key
• Partial participation on one side
• Create one table with two copies of primary key
  using one as foreign key, or
• Create one table with primary key and one table
  with two copies of primary key, making total
  participant be primary key and partial
  participant be foreign key
• Partial participation on both sides
• Create one table with primary key and one table
  with two copies of primary key, , making one be
  primary key, one be foreign key

23
ER Conversion

• Multi-valued attributes
• Create table with an attribute for multi-values
  and with primary key from parent entity to serve
  as foreign key to parent table
• Make foreign key attributes and multi-value type
  attribute primary key

24
Validation
Division
has
1
N

• Validate with normalization
• Typically BCNF or 3NF
• Validate with data entry
• Check for
• locked loops constraints limit entry due to
  total participation requirements in two or more
  tables
• fan traps ambiguous results due to fan out of
  two or more 1N relationships
• chasm traps suggested relationship where one or
  more tables require partial participation

Staff
N
oversee
N
1
supervising
1
Managers
Division
has
1

Staff
1
owns

Branches
Division
has
1..1
1..
Staff
1..1
Sells
0..
Product Line