The Entity-Relationship Model

(read 2.1 - 2.5, 2.8, 2.9.1, 2.9.3, 2.10)

Entity-Relationship Model (ER Model)
a data model in which information stored in the database is viewed as sets of entities and sets of relationships among entities
entity
something that exists and can be distinguished from other entities.
Examples:
entity set
a set of entities of the same type.
attribute
a characteristic of an entity
domain
a set of possible values for an attribute (the type of the attribute). Examples:

Return to Table of Contents

Representation of Entities and Entity Sets

An entity is represented by (consists of) a value for each of its attributes.

Example: an ER database for a fly shop

conceptual scheme (entity sets and their attributes):

Instance:

Return to Table of Contents

Relationships and Relationship Sets

relationship
an association among 2 or more entities

Example: the relationship between a customer and a Sage 2-weight flyrod purchased at the flyshop

relationship set
a set of relationships of the same type

A relationship set is a mathematical relationship on two or more entity sets. A relationship set R on entity sets tex2html_wrap_inline9 is a a subset of:

displaymath7

where tex2html_wrap_inline11 is a relationship.

The value of tex2html_wrap_inline13 is the set of all possible relationships between entities in tex2html_wrap_inline15 , entities in tex2html_wrap_inline17 , ..., and entities in tex2html_wrap_inline19 .

The set tex2html_wrap_inline13 is the cartesian product of tex2html_wrap_inline9 .
Example: the cartesian product of customer and flyrod is:

Any subset of this set is an instance of relationship set Purchased.

A relationship may have attributes, and so a relationship set can have attributes. For example, relationship set Purchased could have a date attribute recording the date of purchase.

Return to Table of Contents

Mapping Constraints

A mapping cardinality is a data constraint that specifies how many entities an entity can be related to in a relationship set.

Example: each customer can purchase as many kinds of flyrods as they want (a constraint on Purchased), but can have only one favorite flyrod/fly combination (a constraint on Fave-Combo).

A binary relationship set is a relationship set on two entity sets. Mapping cardinalities on binary relationship sets are simplest.

Consider a binary relationship set R on entity sets A and B. There are four possible mapping cardinalities in this case:

  1. one-to-one - an entity in A is related to at most one entity in B, and an entity in B is related to at most one entity in A.

  2. one-to-many - an entity in A is related to any number of entities in B, but an entity in B is related to at most one entity in A.

    Example: if the fly shop stocks flies by customer request only, then relationship set Requested-By has one-to-many mapping cardinality because no one else need request a fly once the shop stocks it.

  3. many-to-one - an entity in A is related to at most one entity in B, but an entity in B is related to any number of entities in A.

  4. many-to-many - an entity in A is related to any number of entities in B, but an entity in B is related to any number of entities in A.

    Relationship set Purchased has many-to-many mapping cardinality because a customer can buy many different kinds of flyrods, and each kind of flyrod can be purchased by multiple customers.

The mapping cardinality of a relationship set depends on the real world relationships it is modeling.

Another kind of mapping constraints, called existence dependencies, occur when the existence of an entity in an entity set depends on that entity being related to an entity in another entity set.

Return to Table of Contents

Keys

Issue: distinguishing between entities in an entity set (and relationships in a relationship set).

A superkey is a set of one or more attributes that allow entities (or relationships) to be uniquely identified.

All of the following are superkeys for entity set fly :

This list is not comprehensive.

A superkey must allow entities (or relationships) to be distinguished across all possible instances. This often requires outside knowledge about what attributes mean.

A candidate key is a superkey that has no superkeys as proper subsets. A candidate key is a minimal superkey.

Candidate keys for fly:

and no others.

The primary key is the (one) candidate key chosen (by the database designer or database administrator) as the primary means of uniquely identifying entities (or relationships).

Example: {fly-stock-num} is the most likely primary key.

Return to Table of Contents

Primary Keys of Relationship Sets

The primary key of a relationship set is the primary means of uniquely identifying any relationship in the set.

The primary key of a relationship set depends on the mapping cardinality and the meaning of any attributes of the relationship set.

Case 1: the relationship set has no attributes

  1. one-to-one mapping cardinality: the primary key of any of the entity sets involved is a candidate key for the relationship set, because any entity (from any entity set) can be involved in at most one relationship.

  2. one-to-many mapping cardinality: the primary key of the relationship set is the primary key of the second entity set (the to-many set), because an entity in the second entity set can be involved in at most one relationship.

    Example: the primary key of Requested-By is {fly-stock-num}.

  3. many-to-one mapping cardinality: the primary key of the relationship set is the primary key of the first entity set (the to-many set), because an entity in the first entity set can be involved in at most one relationship.

  4. many-to-many mapping cardinality: the primary key of the relationship set includes the primary keys of all of the entity sets involved.

    Example: the primary key of Purchased (without the date attribute) is {cust-num, flyrod-stock-num}.

Case 2: the relationship set has attributes

Example: the date attribute of Purchased would be included in the primary key, because the same customer could purchase the same make and model of flyrod multiple times. The full primary key is {cust-num, flyrod-stock-num, date}.

Return to Table of Contents

Entity-Relationship Diagrams

The conceptual scheme of an E-R database can be represented graphically by an E-R diagram as follows:

Mapping cardinalities are represented in an E-R diagram as follows

Entity-Relationship Diagram for the Flyshop DBMS:

E-R diagrams are used in designing other kinds of databases (especially relational databases) and in modeling software systems.

Return to Table of Contents

Reducing Entity-Relationship Schema to Tables

Idea: represent the data and relationships in an E-R database in a collection of tables.

Reducing Entity Sets to Tables

The table corresponding to an entity set has a column for each attribute of the entity set and a row for each entity in the entity set. The table has the same name as the entity set.

Example: the table for entity set fly.

fly
pattern size color fly-stock-num
woolly bugger 10 olive 5
gold-ribbed hare's ear 14 natural 2
woolly bugger 2 black 4
Dahlberg Diver 2 natural 1

Discussion: does the order of rows matter?

Reducing Relationship Sets to Tables

The table for a relationship set will have a column for each attribute of the superkey of that relationship set (i.e. one for each attribute of the primary key of each entity set involved in the relationship, and one for each attribute of the relationship set). These attributes are renamed if necessary to avoid name clashes. The table will have one row for each relationship, and will have the same name as the relationship set.

Example: relationship set Requested-By becomes:

Requested-By
cust-num fly-stock-num
1 2
3 5
2 4
2 1

Example: relationship set Purchased (including the date attribute) becomes:

Purchased
cust-num flyrod-stock-num date
1 3 9/4/1994
3 3 9/3/1994
2 2 8/8/1992
3 2 9/2/1994
3 1 9/1/1994

Return to Table of Contents