星期三, 3月 02, 2011

Modeling Data in the Organization

Objectives
  • Define terms
  • Understand importance of data modeling
  • Write good names and definitions for entities, relationships, and attributes
  • Distinguish unary, binary, and ternary relationships
  • Model different types of attributes, entities, relationships, and cardinalities
  • Draw E-R diagrams for common business situations
  • Convert many-to-many relationships to associative entities
  • Model time-dependent data using time stamps

 

 

 
Business Rules
  • Are statements that define or constrain some aspect of the business
  • Are derived from policies, procedures, events, functions
  • Assert business structure
  • Control/influence business behavior
  • Are expressed in terms familiar to end users
  • Are automated through DBMS software

 

 

 
A Good Business Rule Is:
  • Declarative–what, not how
  • Precise–clear, agreed-upon meaning
  • Atomic–one statement
  • Consistent–internally and externally
  • Expressible–structured, natural language
  • Distinct–non-redundant
  • Business-oriented–understood by business people

 

 

 
A Good Data Name Is:
  • Related to business, not technical, characteristics
  • Meaningful and self-documenting
  • Unique
  • Readable
  • Composed of words from an approved list
  • Repeatable
  • Written in standard syntax

 

 

 
Data Definitions
  • Explanation of a term or fact
    • Term–word or phrase with specific meaning
    • Fact–association between two or more terms
  • Guidelines for good data definition
    • A concise description of essential data meaning
    • Gathered in conjunction with systems requirements
    • Accompanied by diagrams
    • Achieved by consensus, and iteratively refined

 

 

 
E-R Model Constructs
  • Entities:
    • Entity instance–person, place, object, event, concept (often corresponds to a row in a table)
    • Entity Type–collection of entities (often corresponds to a table)
  • Relationships:
    • Relationship instance–link between entities (corresponds to primary key-foreign key equivalencies in related tables)
    • Relationship type–category of relationship…link between entity types
  • Attribute–property or characteristic of an entity or relationship type (often corresponds to a field in a table)

 

 

 
An Entity…
  • SHOULD BE:
    • An object that will have many instances in the database
    • An object that will be composed of multiple attributes
    • An object that we are trying to model
  • SHOULD NOT BE:
    • A user of the database system
    • An output of the database system (e.g., a report)

 

 

 
Attributes
  • Attribute–property or characteristic of an entity or relationship type
  • Classifications of attributes:
    • Required versus Optional Attributes
    • Simple versus Composite Attribute
    • Single-Valued versus Multivalued Attribute
    • Stored versus Derived Attributes
    • Identifier Attributes

 

 

 
Identifiers (Keys)
  • Identifier (Key)–an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type
  • Simple versus Composite Identifier
  • Candidate Identifier–an attribute that could be a key…satisfies the requirements for being an identifier

 

 

 
Criteria for Identifiers
  • Choose Identifiers that
    • Will not change in value
    • Will not be null
  • Avoid intelligent identifiers (e.g., containing locations or people that might change)
  • Substitute new, simple keys for long, composite keys

 

 

 
More on Relationships
  • Relationship Types vs. Relationship Instances
    • The relationship type is modeled as lines between entity types…the instance is between specific entity instances
  • Relationships can have attributes
    • These describe features pertaining to the association between the entities in the relationship
  • Two entities can have more than one type of relationship between them (multiple relationships)
  • Associative Entity–combination of relationship and entity

 

 

 
Degree of Relationships
  • Degree of a relationship is the number of entity types that participate in it
  • Unary Relationship
  • Binary Relationship
  • Ternary Relationship

 

 

 
Cardinality of Relationships
  • One-to-One
    • Each entity in the relationship will have exactly one related entity
  • One-to-Many
    • An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity
  • Many-to-Many
    • Entities on both sides of the relationship can have many related entities on the other side

 

 

 
Cardinality Constraints
  • Cardinality Constraints—the number of instances of one entity that can or must be associated with each instance of another entity
  • Minimum Cardinality
    • If zero, then optional
    • If one or more, then mandatory
  • Maximum Cardinality
    • The maximum number

 

 

 
Strong vs. Weak Entities, and Identifying Relationships
  • Strong entity
    • exists independently of other types of entities
    • has its own unique identifier
    • identifier underlined with single line
  • Weak entity
    • dependent on a strong entity (identifying owner)…cannot exist on its own
    • does not have a unique identifier (only a partial identifier)
    • partial identifier underlined with double line
    • entity box has double line
  • Identifying relationship
    • links strong entities to weak entities

 

 

 
Associative Entities
  • An entity–has attributes
  • A relationship–links entities together
  • When should a relationship with attributes instead be an associative entity?
    • All relationships for the associative entity should be many
    • The associative entity could have meaning independent of the other entities
    • The associative entity preferably has a unique identifier, and should also have other attributes
    • The associative entity may participate in other relationships other than the entities of the associated relationship
    • Ternary relationships should be converted to associative entities