DBMS Q1L3

Lesson 3: DEVELOPING LOGICAL DATA MODEL

Logical Data Model

Documents the information requirements of the business.

Entity-Relationship Diagram (ERD)

A detailed, logical and graphical representation of the entities, associations and data elements for an organization or business area.

Basic modeling notation

• Data entities.

• Relationships.

• Attributes.

Entity

• Information that must be tracked.

• A name for things that you can list.

• Usually a noun.

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

An association or link between two or more distinct sets of data.

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

Entity Types

Strong (prime); Weak (characteristic); Associative (intersection)

Strong (prime)

Exists independently

Weak (characteristic)

Exists because of another (prime) entity

Associative (intersection)

Exists because of two or more entities

Entities Represent Sets

Entities represent a set of instances that are of interest to a particular business.

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.

Identifier (Key)

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

Candidate Identifier

An attribute that could be a key…satisfies the requirements for being an identifier.

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.

Relationship Types

One-to-One; One-to-Many or Many-to-One; Many-to-Many

One-to-One (1:1)

Each entity in the relationship will have exactly one related entity.

One-to-Many (1:M) or Many-to-One (M:1)

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 (M:M)

Entities on both sides of the relationship can have many related entities on the other side.

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

Degree of Relationships

The number of entity types that participate in it. Has three types: Unary, Binary, and Ternary.

Unary Relationships

One entity related to another of the same entity type.

Binary Relationships

Connects exactly two entity types.

Ternary Relationships

Connects three entities, where the relationship itself, captures a specific interaction between them.

Strong entities

Exist independently of other types of entities.

Weak entity

Dependent on a strong entity (identifying owner); cannot exist on its own. And, does not have a unique identifier (only a partial identifier).

Associative Entities

• An entity–has attributes.

• A relationship–links entities together.