Entity-Relationship Model

An entity relationship diagram, also called entity relationship model, is a graphical representation of entities and their relationships to each other, typically used in computing in regard to the organization of data within databases or information systems.

An entity relationship diagram is a specialized graphic that illustrates the relationships between entities in a database. Also Known As: ER Diagram, E-R Diagram, ERD.

ENTITIES

Entities are the “things” about which you wish to record information in a database. There are relationships between entities which fall into three types: one-one, one-many, many-many. Any many-many relationship must be resolved into two one-many relationships.

An entity is an object that exists and which is distinguishable from other objects. An entity can be a person, a place, an object, an event, or a concept about which an organization wishes to maintain data. The following are some examples of entities:

Person: STUDENT, EMPLOYEE, CLIENT

Object: COUCH, AIRPLANE, MACHINE

Place: CITY, NATIONAL PARK, ROOM, WAREHOUSE

Event: WAR, MARRIAGE, LEASE

Concept: PROJECT, ACCOUNT, COURSE

It is important to understand the distinction between an entity type, an entity instance, and an entity set. An entity type defines a collection of entities that have same attributes. An entity instance is a single item in this collection. An entity set is a set of entity instances. The following example will clarify this distinction: STUDENT is an entity type; a student with ID number 555-55-5555 is an entity instance; and a collection of all students is an entity set. In the E-R diagram, we assign a name to each entity type. When assigning names to entity types, we follow certain naming conventions. An entity name should be a concise singular noun that captures the unique characteristics of the entity type. An E-R diagram depicts an entity type using a rectangle with the name of the entity inside.

Entity representation of E-R diagram

 

ATTRIBUTES

We represent an entity with a set of attributes. An attribute is a property or characteristic of an entity type that is of interest to an organization. Some attributes of common entity types include the following:

STUDENT = {Student ID, SSN, Name, Address, Phone, Email, DOB}

ORDER = {Order ID, Date of Order, Amount of Order}

ACCOUNT = {Account Number, Account Type, Date Opened, Balance}

CITY = {City Name, State, Population}

 

Naming Conventions for Attributes

1. Each word in a name starts with an uppercase letter followed by lower case letters.

2. If an attribute name contains two or more words, the first letter of each subsequent word is also in uppercase, unless it is an article or preposition, such as “a,” “the,” “of,” or “about”.

Attributes for Student Entity

Figure 2. Attributes for Student Entity

 

E-R diagrams depict an attribute inside an ellipse and connect the ellipse with a line to the associated entity type. Figure 3.3 illustrates some of the possible attributes in an E-R diagram for the entity STUDENT.

 

Notice that not all of the attributes in the figure are marked in the same way. There are actually several types of attributes featured in this figure. These include: simple, composite, single-valued, multi-valued, stored, and derived attributes. In the following subsections, we discuss the distinctions between these types of attributes.

 

Simple and Composite Attributes

A simple or an atomic attribute, such as City or State, cannot be further divided into smaller components. A composite attribute, however, can be divided into smaller subparts in which each subpart represents an independent attribute. Name and Address are the only composite attributes in Figure 2. All other attributes, even those that are subcategories of Name and Address, are simple attributes. The figure also presents the notation that depicts a composite attribute.

 

Single-Valued and Multi-Valued Attributes

Most attributes have a single value for an entity instance; such attributes are called single-valued attributes. A multi-valued attribute, on the other hand, may have more than one value for an entity instance. Figure 3.3 features one multi-valued attribute, Languages, which stores the names of the languages that a student speaks. Since a student may speak several languages, it is a multi-valued attribute. All other attributes of the STUDENT entity type are single-valued attributes.

 

For example, a student has only one date of birth and one student identification number. In the E-R diagram, we denote a multi-valued attribute with a double-lined ellipse. Note that in a multi-valued attribute, we always use a double-lined ellipse, regardless of the number of values.

 

Stored and Derived Attributes

The value of a derived attribute can be determined by analyzing other attributes. For example, in Figure 2 Age is a derived attribute because its value can be derived from the current date and the attribute DateofBirth. An attribute whose value cannot be derived from the values of other attributes is called a stored attribute. As we will learn, a derived attribute Age is not stored in the database. Derived attributes are depicted in the E-R diagram with a dashed ellipse.

 

Key Attribute

A key attribute (or identifier) is a single attribute or a combination of attributes that uniquely identify an individual instance of an entity type. No two instances within an entity set can have the same key attribute value. For the STUDENT entity shown in Figure 2, StudentID is the key attribute since each student identification number is unique. Name, by contrast, cannot be an identifier because two students can have the same name. We underline key attributes in an E-R diagram (as shown in  Figure 3).

Sometimes no single attribute can uniquely identify an instance of an entity type. However, in these circumstances, we identify a set of attributes that, when combined, is unique for each entity instance. In this case the key attribute, also known as composite key, is not a simple attribute, but a composite attribute that uniquely identifies each entity instance.

Key Attribute

Figure 3. Key Attribute

 

Composite Key

Figure 4. Composite Key

 

RELATIONSHIPS

Entities in an organization do not exist in isolation but are related to each other. Students take courses and each STUDENT entity is related to the COURSE entity. Faculty members teach courses and each FACULTY entity is also related to the COURSE entity. Consequently, the STUDENT entity is related to the FACULTY entity through the COURSE entity. E-R diagrams can also illustrate relationships between entities.

 

We define a relationship as an association among several entities. Consider, for example, an association between customers of a bank. If customer Williams has a bank account number 523, then the quality of ownership constitutes a relationship instance that associates the CUSTOMER instance Williams with the ACCOUNT instance 523. We can think of the relationship instance as a verb that links a subject and an object: customer Williams has an account; student John registers for a course; professor Smith teaches a course. A relationship set is a grouping of all matching relationship instances, and the term relationship type refers to the relationship between entity types.

 In an E-R diagram, we represent relationship types with diamond-shaped boxes connected by straight lines to the rectangles that represent participating entity types. A relationship type is a given name that is displayed in this diamond-shaped box and typically takes the form of a present tense verb or verb phrase that describes the relationship. An E-R diagram may depict a relationship as the following example of the relationship between the entities CUSTOMER and ACCOUNT does:

Relationship between CUSTOMER and ACCOUNT entities

Figure 5. Relationship between CUSTOMER and ACCOUNT entities

 

DEGREE OF A RELATIONSHIP

The number of entity sets that participate in a relationship is called the degree of relationship. For example, the degree of the relationship featured in Figure 3.8 is two because CUSTOMER and ACCOUNT are two separate entity types that participate in the relationship. The three most common degrees of a relationship in a database are unary (degree 1), binary (degree 2),and ternary (degree 3). We will briefly define these degrees and then explore each kind of relationship in detail in subsequent sections.

 

Unary Relationship. A unary relationship R is an association between two instances of the same entity type. For example, two students are roommates and stay together in an apartment. Because they share the same address, a unary relationship exists between them for the attribute Address.

 

Binary Relationship. A binary relationship R is an association between two instances of two different entity types. For example, in a university, a binary relationship exists between a student (STUDENT entity) and an instructor (FACULTY entity) of a single class; an instructor teaches a student.

 

Ternary Relationship. A ternary relationship R is an association between three instances of three different entity types. For example, consider a student using certain equipment for a project. In this case, the STUDENT, PROJECT, and EQUIPMENT entity types relate to each other with ternary relationships: a student checks out equipment for a project.

 

CARDINALITY OF A RELATIONSHIP

The term cardinal number refers to the number used in counting. An ordinal number, by contrast, emphasizes the order of a number (1st, 7th, etc.). When we say cardinality of a relationship, we mean the ability to count the number of entities involved in that relationship. For example, if the entity types A and B are connected by a relationship, then the maximum cardinality represents the maximum number of instances of entity B that can be associated with any instance of entity A.

 

However, we don’t need to assign a number value for every level of connection in a relationship. In fact, the term maximum cardinality refers to only two possible values: one or many. While this may seem to be too simple, the division between one and many allows us to categorize all of the permutations possible in any relationship. The maximum cardinality value of a relationship, then, allows us to define the four types of relationships possible between entity types A and B. Figure 3.9 illustrates these types of relationships.

One-to-One Relationship. In a one-to-one relationship, at most one instance of entity B can be associated with a given instance of entity A and vice versa.

One-to-Many Relationship. In a one-to-many relationship, many instances of entity B can be associated with a given instance of entity A. However, only one instance of entity A can be associated with a given instance of entity B. For example, while a customer of a company can make many orders, an order can only be related to a single customer.

Many-to-Many Relationship. In a many-to-many relationship, many instances of entity A can be associated with a given instance of entity B, and, likewise, many instances of entity B can be associated with a given instance of entity A. For example, a machine may have different parts, while each individual part may be used in different machines.

The four types of relationships between entity types A and B

Figure 6. The four types of relationships between entity types A and B.

 

REPRESENTING RELATIONSHIP TYPES

An entity on the one side of the relationship is represented by a vertical line, “I,” which intersects the line connecting the entity and the relationship. Entities on the many side of a relationship are designated by a crowfoot as depicted in Figure 7.

The relationship types based on maximum cardinality.

Figure 7. The relationship types based on maximum cardinality.

The cardinality of relationship represents the minimum/maximum number of instances of entity B that must/can be associated with any instance of entity A.

The relationship types based on minimum cardinality

Figure 7.1. The relationship types based on minimum cardinality

 

UNARY RELATIONSHIP

A unary relationship is an association between two entities of the same entity type.

 

The Unary One-to-one Relationship

Figure 8 displays the E-R diagram of a unary relationship IsMarriedTo. Whenever two people in the entity type PERSON get married, the relationship instance IsMarriedTo is created. The Date of marriage is an attribute of this relationship. Since a person can only be married to one other person, marriage is a one-to-one relationship. Furthermore, since a person can be unmarried, the minimum cardinality of the IsMarriedTo relationship is zero. Figure 13 depicts several relationship instances of this relationship type. Each relationship instance connects two instances in PERSON. The lines allow us to read relationships between entity instances. For example, r1 suggests that person p1 is married to person p5, and so forth.

 

The unary one-to-one relationship

Figure 8. The unary one-to-one relationship.

 

The unary one-to-one relationship

Figure 8.1. The unary one-to-one relationship

 

The unary one-to-many relationship

This relationship instance exists whenever an employee supervises another employee. The relationship Supervises is a one-to-many relationship since an employer can supervise many employees but a supervisee can have only one supervisor. The minimum cardinality for supervising is zero (an employee may not supervise anyone) but the minimum cardinality of being supervised is one (every employee must be supervised).

The unary one-to-many relationship

Figure 9. The unary one-to-many relationship

 

Download link: http://www.liezlabuzo.com/downloads/Chapter 3_Entity_Relationship_Diagram.docx