Module 2: Modeling Data in the Organization

Entity Relationship Model (ERM)

Entity Relationship Model (ERM)

a logical representation of the data for an organization or for a business area.

Entity Relationship Diagram (ERD)

A graphical representation of an entityrelationship model

Entity Types

• collection of entities that share common properties or characteristics.

• Represented by rectangles

Strong Entity

• Exist independently of other entities

• Has its own unique identifier

• Identifier is underlined with a single line

Weak Entity

• Dependent of a Strong Entity

• Cannot exist on its own

• No unique identifier (partial identifier only)

• Entity box and unique identifier have double lines

Identifying Relationships (Line relationships)

links strong and weak entities.

Attributes

• Properties or characteristics of an entity or relationship type

Classification of attributes: (RISSS)

• Required vs Optional

• Identifier Attributes

• Simple vs Composite

• Single-valued vs Multi-valued

• Stored vs Derived

Required Attributes

Must have value for every entity (or relationship) instance with which it is associated

Optional Attribute

May not have value for every entity (or relationship) instance with which it is associated

Simple Attribute

• an attribute that cannot be broken down into smaller components

• Example: First Name or Last Name from FULL NAME

Composite Attributes

• an attribute that has meaningful component parts (can be broken down into simple attributes)

• Example: Employee's Address (Street address, City, State, Postal Code)

Single valued

• an attribute that holds only one value for an entity (or relationship)

• Example:

  • DateOfBirth attribute must have only one value for each entity instance (A person only has one Birth Date)

Multivalued Attributes

• an attribute that may take more than one value for an entity (or relationship)

• Example:

  • DateOfBirth attribute must have only one value for each entity instance (A person only has one Birth Date)

Stored Attributes

• an attribute whose value is physically stored in the database

• Example: Price (this can be used for DERIVED ATTRIBUTES)

Derived Attribute

• an attribute whose value can be calculated from related values from STORED ATTRIBUTES (not physically stored in the database)

• Example: DiscountedPrice may be derived from Price and DiscountRate attributed STORED in the database

Identifier Keys

an attribute (or combination of attributes) that uniquely identifies individual entity instances.

Types of identifier

• Simple Identifier Attribute

• Composite Identifier Attribute

Criteria for Identifier

(DANS)

• Does not change in value

• Avoid intelligent identifiers (e.g., containing locations that may change)

• Not Null

• Substitute new, simple keys for long, composite keys

Relationship Degrees

(TUB)

• Unary

• Binary

• Ternary

Relationship Cardinality

• one-to-one

• one-to-many

• many-to-many

Cardinality Constraints

• Mandatory one

• Mandatory many

• Optional one

• Optional many

Business Rules

(SAD ACE)

• Statements that define or constraint some aspects of the business.

• Assert business structure

• Derived from policies, procedures, events, and functions.

• Automated through DBMS.

• Control/influence business structure

• Expressed in terms of familiar to end users.

Characteristics of Good Business Rules

(BEDDCAP)

• Business-oriented (understood by businesspeople)

• Expressible (structured, natural language)

• Declarative (what, now how)

• Distinct (nonredundant)

• Consistent (internally and externally)

• Atomic (one statement)

• Precise (clear, agreed meaning)

Characteristics of a Good Data NAME

(CRUMWRR)

• Composed of words from an approved list

• Related to business, not technical.

• Unique

• Meaningful and self-documenting

• Written in standard syntax.

• Readable

• Repeatable

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

(GACA)

• Gathered in conjunction with sysreqs

• Accompanied by diagrams

• Concise description

• Achieved by concensus, and iterartively refined.

Entity

• A person, place, an object, and event or a concept in the user environment

Entity Should (HOC)

• Have many instances

• Object that is being modeled

• Composed of multiple properties

Entity Should not (UO)

• -User of the database system

• -Output of the database system

Entity Instance

a single occurence of an entity type

Naming Attributes

• singular noun or noun phrase

• unique

• similar attributes of different entity types should use the same qualifiers and classes

• follow format

Relationship Types

modeled as lines between entity types

Relationhip Instances

actual occurence of a relationship type

Relationship Attributes

describe the features pertaining to the association between the entities in the relationship.

Associative Entity

It is the combination of relationship and an entity