Documenting Database Designs with Entity-Relationship Diagrams

1
Documenting Database Designs with
Entity-Relationship Diagrams

• IS 460 Notes
• by Thomas Hilton

2
What is the E-R Model?

• A method of conceptualizing and documenting data
  structures used in organizations
• Static, not dynamic (data, not process)
• Logical/conceptual, not physical

3
Essential Definitions

• a group of instances that share the
  same attributes
• a link between entities
• a simplified, abstract replica of
  some real-world system

• Entity
• Relationship
• Model

Entity-Relationship Diagram
Entity Identification
Key/Attribute identification
Conversations, observations, forms, reports, etc.
Relationship Identification
Table Normalization
4
E-R Symbols

• No Fixed Standard, but Some Common Usage
• Maximum Cardinality1 or M (or a particular
  number)If I have one of these, how many of
  those can I have?
• Minimum Cardinality0 or 1 (or a particular
  number)If I have one of these, how many of
  those must I have?

ENTITY
MaxMin
MinMax
5
Attributes

• Can be represented with a circle or ellipse on
  the entity, but please dont for me
• Instead do thisENTITY KEY-ATT,
  REQ-NON-KEY-ATT, REQ-NON-KEY-ATT, Opt-non-key-att

6
Keys

• one or more attributes whose
  values uniquely identify each instance in an
  entity
• attributes (or attribute
  groups) which could function as primary keys
• candidate keys not chosen as
  the primary key
• key consisting of more than
  one attribute
• one or more attributes whose
  values identify groups of instances within an
  entity
• key replicated in two entities
  to instantiate a relationship between them
• common key which is primary in
  one entity and secondary in the other

• Primary
• Candidate
• Alternate
• Composite
• Secondary
• Common
• Foreign

7
Attribute Constraints

• Instantiate Business Rules
• consistency
  between entities (particularly common keys)
• whats legal and whats not
  (format, size, value ranges, etc.)
• (external and)
  internal consistency within the entity
• 
  transactions, data sources, required approvals,
  legitimate users, etc.

• Referential Integrity
• Domain
• Entity Integrity
• Triggering operations

8
Table Normalization

• Analyze entities into tables that can be
  manipulated without data redundancy and the
  resulting modification anomalies
• splitting the attributes of one entity into
  multiple tables
• All non-key attributes must be fully
  functionally dependent
• on the key, 1NF (non-dependencies)
• the whole key, 2NF (partial dependencies)
• and nothing but the key. 3NF (transitive
  dependencies)

• Definition
• Tool
• Rule

9
Domain-Key Normal Form

• Every constraint on the table is a logical
  consequence of the table's domain constraints and
  key constraints
• Have one theme per table

10
Lets Do An Example!
11
Lets Do An Example!