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