Entity-Relationship Model

The entity-relationship (E-R) model helps with the design of databases. Its concepts are widely applicable. We will be dealing with entity sets, relationship sets, and attributes.

An entity is something in the real world that we want to model in our database. Examples are a candidate, a student, a instructor, or a contributor. Entities have properties or attributes. Like the name of a candidate or student. An entity set is a set of entities of the same type that share the same group of attributes. Example: each candidate has an id, a name, and a political party. Part of the design of a database is to identify the entity sets and figure out what attributes each entity set should have. A scheme for an entity set is to specify its name and its attributes. Here are the schema for the Candidate and Contributor entity sets: 

Candidate(can_id, name, party) 
Contributor(contr_id, first, last, city)
Relationships. Let's look at a ternary (3 entity set) relationship. Let E1, E2, and E3 be three entity sets. A relationship in this context is a collection of one entity from each set, (e1, e2, e3), where ei is an entity from set Ei for i=1, 2, 3. A relationship set in this context would be a set of these triples. This is pretty abstract. Let's get more concrete.

We'll look at a binary relationship between the 2 entity sets, Candidate and Contributor. Now we take pairs of entities, one from Candidate and one from Contributor. We make such a pair if the Candidate entity receives a contribution from the Contributor entity. The set of such pairs will be makes the relationship set. Let's call this set, ContributionSet. Our table Contribution has scheme: 

Contribution(can_id, contr_id, date, amt)
This table is (almost) a realization of the relationship set. can_id, contr_id are the keys of Candidate, Contributor so they uniquely determine a candidate and contributor respectively. That's good. And it is ok to have extra attributes like date and amt. These are called "descriptive attributes". The problem with the table is that a contributor can make more than one contribution to the same candidate. Thus the pair (can_id, contr_id) may NOT determine just one row (relationship) in the table. An example is Hillary and contr_id=49. Run the following query: 
    select * from Contribution where can_id=8 and contr_id=49;
It produces two rows. So the entity pair (Hillary, contributor 49) does not determine a unique realationship; the Contribution table cannot be a representation of the relationship set!

E-R Diagrams

Rectangles are used for entity sets. Name on the top line, followed by the attributes on subsequent lines.

Diamonds are used for relationship sets. Put the name of the relationship inside the diamond.

Lines connect entity sets to relationship sets.
Below is an ER diagram of our tiny database.

Mapping Cardinalities for Binary Relations

Consider the two entity sets/tables Candidate and Contributor that are related to each other by the Contribution relation. Let's pick a direction, say from Contributor to Candidate. There are four possible descriptions:
  1. One-to-one. Each entity in Contributor is associated with at most one entity in Candidate. And each entity in Candidate is associated with at most one entity in Contributor.
  2. Many to one. Many entities (0 or more) in Contributor may be associated with an entity in Candidate. Each entity in Contributor is associated with at most one entity in Candidate.
  3. One to many. an entity in Contributor is associated with zero or more entities in Candidate. Each entity in Candidate is associated with at most one entity in Contributor.
  4. Many to many. An entity in Contributor is associated with 0 or more entities in Candidate and an entity in Candidate is associated with 0 or more entities in Contributor.

We can put numbers on the lines in an ER diagram: mn..mx where mn and mx stand for numbers. Remember that each line is associated with an entity set and a relationship set. The number mn is the minimum number of relationships that an entity must have. The number mx is the maximum number number of relationships an entity can have. The upper value can be an *. This means there is no maximum value of relationships. Example:

The numbers on the left tell us about entities in Contributor. Each contributor has between 2 and 5 relationships. That is each contributor makes contributions to between 2 and 5 candidates. (Note: these numbers are not realistic. The numbers that belong there are design numbers. They should describe lows and highs no matter what instance of the database we have). The numbers on the right say that each candidate will have any number of relationships. So each candidate might have any number of contributors.