Data Modeling Synthesis

1
Data Modeling Synthesis

• Diagram Rules and Design Errors

2
Data modeling summary

• Conceptual modeling with ERD symbols
• Entity type attributes relationships
  cardinalities
• Identification dependency self-referencing
  relationships GH
• Verifying the model with diagramming rules
• Completeness (missing symbols)
• Consistency (symbol conflicts)

3
Data modeling summary

• Refining data model transformation tactics (p.
  178)
• Attribute to entity
• Compound to simple attributes
• Entity type expansion
• Weak to strong entity
• Generalization hierarchy

4
Data modeling summary

• Identifying and correcting design errors (p. 181)
• E.g., missing/redundant relationships
• Converting ERD to relational tables (p. 195)
• E.g., M-N relationship rule
• Applying the rules of normalization to develop a
  normalized relational table design

5
Business Rules

• ERDs contain business rules that
• Enforce organizational policies
• Promote efficient communication among key
  stakeholders
• Formal representation on ERD
• Informal representation in documentation
  associated with an ERD
• Use rules language to formally represent in
  relational database after conversion
• Diagram rules of completeness (missing symbols)
• Diagram rules of consistency (symbol conflicts)

6
Formal Representation

• Primary key constraints entity identification
• Named relationships direct connections among
  business entities (business comm.)
• Identification dependency knowledge of other
  entities for identification (business comm.)
• Cardinalities restrict number of related
  entities in a business situation (org. policy)
• Generalization hierarchies classification of
  business entities (org. policy)

7
Informal Representation

• Specify as documentation associated elements of
  an ERD
• Candidate key constraints alternate ways to
  identify business entities
• Reasonable values fixed collection of values or
  consistent with another attribute (e.g. dates)
• Null value constraints support policies about
  completeness of data collection activities
• Default values simplify data entry and provide
  value when unknown

8
Diagram Rules

• Ensure that ERD notation is correctly used
• Similar to syntax rules for a computer language
• Completeness rules no missing symbols
• Consistency rules no conflicts among symbols

9
Completeness Rules

• Primary Key Rule all entity types have a PK
  (direct, indirect, or inherited)
• Naming Rule all entity types, relationships, and
  attributes have a name
• Cardinality Rule cardinality is specified in
  both directions for each relationship
• Entity Participation Rule all entity types
  participate in an at least one relationship
  except for entity types in a generalization
  hierarchy
• Generalization Hierarchy Participation Rule at
  least one entity type in a generalization
  hierarchy participates in a relationship

10
Primary Key Rule Issue

• Primary key rule is simple in most cases
• For some weak entities, the PK rule is subtle
• Weak entity with only one 1-M identifying
  relationship
• Weak entity must have a local key to augment the
  borrowed PK from the parent entity type
• Violation of PK rule if local key is missing

11
PK Rule Violation Example
12
Naming Consistency Rules

• Entity Name Rule entity type names must be
  unique
• Attribute Name Rule attribute names must be
  unique within each entity type and relationship
• Inherited Attribute Rule attribute names in a
  subtype should do not match inherited (direct or
  indirect) attribute names.

13
Relationship Names

• No uniqueness requirement
• Participating entities provide a context for
  relationship names
• Use unique names as much as possible to
  distinguish relationships
• Must provide unique names for multiple
  relationships between the same entity types

14
Connection Consistency Rules

• Relationship/Entity Connection Rule
  relationships connect two entity types (not
  necessarily distinct)
• Relationship/Relationship Connection Rule
  relationships are not connected to other
  relationships
• Redundant Foreign Key Rule foreign keys are not
  used on a (conceptual) model (derived only
  through the application of conversion rules)

15
Identification Dependency Rules

• Weak entity rule weak entities have at least one
  identifying relationship
• Identifying relationship rule at least one
  participating entity type must be weak for each
  identifying relationship
• Identification dependency cardinality rule the
  minimum and maximum cardinality must equal 1 for
  a weak entity in all identifying relationships

16
Example of Diagram Errors
(See P. 154)
17
Corrected ERD
18
Summary

• Data modeling is an important skill
• Crows Foot ERD notation is widely used
• Use the diagram rules to ensure structural
  consistency and completeness
• Understanding the ERD notation is a prerequisite
  to applying the notation on business problems

19
Common Design Errors

• Misplaced relationships wrong entity types
  connected
• Incorrect cardinalities typically using a 1-M
  relationship instead of a M-N relationship
• Missing relationships entity types should be
  connected directly
• Overuse of specialized modeling tools
  generalization hierarchies, identification
  dependency, self-referencing relationships, M-way
  associative entity types
• Redundant relationships derived from other
  relationships

20
Common Design Errors

• Design errors
• More difficult to detect and resolve than diagram
  errors
• Design errors involve the meaning (semantics) of
  ERD components, not just the structure of
  components
• Misplaced relationships
• Connecting the wrong entity types.
• Look for clusters of entity types in which an
  entity type in the center is connected to other
  entity types.

21
Common Design Errors

• Incorrect cardinality
• Typical error ? using 1-M relationship instead of
  a M-N relationship.
• This error can be caused by an omission in the
  requirements.
• Missing relationships
• Sometimes the requirements do not directly
  indicate a relationship
• Consider indirect implications to detect whether
  a relationship is required.

22
Common Design Errors

• Overuse of specialized modeling tools
• Inappropriate usage
• Generalization hierarchies should not be used
  just because an entity can exist in multiple
  states.
• An associative entity type representing an M-way
  relationship should be used when the database
  should record combinations of three (or more)
  objects rather than just combinations of two
  objects.
• Redundant relationships
• Cycles in an ERD may indicate redundant
  relationships. A cycle involves a collection of
  relationships arranged in a loop starting and
  ending with the same entity type.
• In a cycle, a relationship is redundant if it can
  be derived from other relationships.

23
Resolving Design Errors

• Misplaced relationships use entity type clusters
  to reason about connections
• Incorrect cardinalities incomplete requirements
  inferences beyond the requirements
• Missing relationships examine implications of
  requirements
• Overuse of specialized modeling tools only use
  when usage criteria are met
• Redundant relationships Examine relationship
  cycles for derived relationships

24
Example Entity Type Cluster
Reading entity is the hub
25
Summary of Data Modeling Strategies

• Use notation precisely
• Strive for simplicity
• ERD connections
• Avoid over connecting the ERD
• Identify hub(s) of the ERD
• Use specialized patterns carefully
• Justify important design decisions

26
Summary of Basic Conversion Rules A Review

• Each entity type becomes a table.
• Each 1-M relationship becomes a foreign key in
  the table corresponding to the child entity type
  (the entity type near the crows foot symbol).
• Each M-N relationship becomes an associative
  table with a combined primary key.
• Each identifying relationship adds a column to a
  primary key.