Comprehensive ER Modeling and Business Rules in Database Systems

Entities

An object that will have many instances in the database.

Attributes

Properties or characteristics of an entity or relationship type.

Strong Entity

Exists independently of other types of entities and has its own unique identifier.

Weak Entity

Dependent on a strong entity and cannot exist on its own; does not have a unique identifier.

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).

Relationship Instance

Link between entities (corresponds to primary key-foreign key equivalencies in related tables).

Business Rules

Statements that define or constrain some aspect of the business.

Good Business Rules

Declarative, precise, atomic, consistent, expressible, distinct, and business-oriented.

Identifiers (Keys)

Unique identifiers for entities in a database.

Cardinality Constraints

Rules that define the number of instances of one entity that can or must be associated with each instance of another entity.

Associative (Bridge) Entities

Entities that are used to link two or more entities together.

Mandatory Vs. Optional

Describes whether the relationship between entities is required or not.

Identifying Relationship

Links strong entities to weak entities.

Appropriate Entities

An object that we are trying to model.

Foundation of Data Model

Business rules that assert business structure and govern how data are stored.

Atomic

Refers to a statement that is one single statement.

Expressible

Refers to rules that are structured in natural language.

Distinct

Refers to rules that are non-redundant.

Consistent

Refers to rules that are internally and externally consistent.

Declarative

Refers to rules that state what is required, not how to achieve it.

Attribute

Property or characteristic of an entity or relationship type

Required Attributes

Attributes that must be present

Optional Attributes

Attributes that may or may not be present

Simple Attribute

An attribute that holds a single value

Composite Attribute

An attribute that can be divided into smaller sub-parts

Single-Valued Attribute

An attribute that holds a single value for each entity

Multivalued Attribute

An attribute that can hold multiple values for each entity

Stored Attributes

Attributes that are physically stored in the database

Derived Attributes

Attributes that are calculated from other attributes

Identifier (Key) Attributes

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

Primary Key

Also called an Identifier (Key), it uniquely identifies each record in a database

Composite Identifier

An identifier that requires two or more fields to create a unique identifier for the row

Candidate Identifier

An attribute that could be an identifier and satisfies all of the requirements for being an identifier

Minimum Cardinality

If zero, then optional; if one or more, then mandatory

Maximum Cardinality

The maximum number of instances that can be associated

Naming Attributes

The process of assigning names to attributes following specific conventions

Defining Attributes

State what the attribute is and possibly why it is important

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.

Mandatory Cardinalities

A patient history is recorded for one and only one patient.

Patient History Requirement

A patient must have recorded at least one history, and can have many.

One optional, one mandatory

An employee can be assigned to any number of projects, or may not be assigned to any at all.

Relationship Definition

A relationship-links entities together.

Associative Entity Criteria

All relationships for the associative entity should be many.

Associative Entity Identifier

The associative entity preferably has a unique identifier, and should also have other attributes.

Associative Entity Relationships

The associative entity may participate in other relationships other than the entities of the associated relationship.

Ternary Relationships

Ternary relationships should be converted to associative entities (Many to Many relationships).

Associative Entity Description

An associative entity is like a relationship with an attribute, but it is also considered to be an entity in its own right.