COP6731 Data Modeling Using ER

Phases of database design (in order)

Requirements collection and analysis > conceptual schema > logical design > physical design

Requirements collection and analysis

Gather the functional requirements and data requirements

Define conceptual schema

A concise description of the data requirements (e.g., entity types, relationships, constraints)

Logical design

The conceptual schema is transformed from the high-level data model into the actual implementation data model (e.g., SQL)

Physical design

Choosing storage and access methods

Purpose of conceptual data modeling

To help understand the meaning (semantics) of the data and to facilitate communication about information requirements

Entity

A real-world object (e.g., employee)

Attribute

A property of an entity (e.g., employee-id, name, age)

Relationship

An association among two or more entities

Entity-relationship (ER) model

A high-level conceptual data model consisting of a collection of entities and the relationships among them

ERD step 1

Identify the entities (e.g., nouns in the domain analysis)

ERD step 2

Find the semantic relationships between entities (e.g., verbs that connect the nouns)

ERD step 3

Draw the entities and relationships

ERD step 4

Determine the cardinality of the relationships

ERD step 5

Identify the attributes

ERD step 6

Identify the attribute(s) that are unique in the entity (the key)

Key in an ERD

Key = candidate key

Entities with physical existence

Person, car, house, employee

Entities with conceptual existence

Company, job, university course

Key attribute

Has a key (uniqueness) constraint; a key can be a set of attributes, and such a composite key must be minimal

Types of attributes

Simple or composite, single-valued or multi-valued, stored or derived

Simple (single) attribute

Composed of a single component with an independent existence

Composite attribute

Composed of multiple components, each with an independent existence

Single-valued attribute

Holds a single value for each occurrence of an entity type

Multi-valued attribute

Holds multiple values for each occurrence of an entity type

Multi-valued attribute example

A person's college degrees (B.S. Computer Science, M.S. Civil Engineering, Ph.D. Computer Science, etc.)

Derived attribute

A value derivable from another attribute or set of attributes (not necessarily in the same entity type); it doesn't exist in the physical database

Derived attribute example 1

birth_date (stored) > age (derived)

Stored attribute

An attribute whose value is actually kept in the database, from which derived attributes are computed

Relationship (in an ER diagram)

An attribute of one entity type refers to another entity type (e.g., department's manager refers to employee)

Relationship set R

A subset of the Cartesian product of the entity sets E1, E2, …, En that participate in R

Degree of a relationship type

The number of participating entity types (unary, binary, ternary, N-ary)

Binary relationship

A relationship between two entity types (e.g., EMPLOYEE WORK_FOR DEPARTMENT)

Ternary relationship

A relationship among three entity types (e.g., supply involves supplier, project, and part)

Unary (recursive) relationship

A relationship between an entity type and itself (e.g., EMPLOYEE SUPERVISION)

Cardinality ratio

The maximum number of relationship instances that an entity can participate in

Cardinality ratio types

1:1, 1:N, N:1, M:N

1:1 relationship example

EMPLOYEE MANAGES DEPARTMENT (one employee manages one department)

1:N relationship example

DEPARTMENT to EMPLOYEE via WORK_FOR (one department has many employees; each employee works for one)

M:N relationship example

EMPLOYEE WORK_ON PROJECT (employees work on many projects; projects have many employees)

Total participation (mandatory)

Participation of entity set E in relationship set R is total if every entity in E participates in at least one relationship in R

Total participation is also called

An existence dependency constraint

Total participation example 1

Every employee must work for a department

Weak entity

An entity type that cannot be uniquely identified by its own attributes alone; depends on another (strong) entity for identification and existence

Weak entity key

Has no primary key of its own, only a partial key (discriminator)

Partial key (discriminator)

The attribute(s) of a weak entity that, combined with the owner's primary key, identify its instances

How is a weak entity identified?

By its partial key plus the primary key of its owning strong entity

Identifying relationship

The relationship between a weak entity type and its parent (owner)

Participation of a weak entity

Always total (existence dependency); every weak entity must participate in the identifying relationship

Weak entity example

DEPENDENT (partial key: Name) related to EMPLOYEE (key: Ssn) through DEPENDS_OF (N:1)

Strong entity

An entity type that has its own key attribute(s) and does not depend on another entity for identification

Mandatory vs. optional relationship

Mandatory = total participation; optional = partial participation (not every entity must participate)