Print this chapterPrint this chapter

CS403 Study Guide

Unit 4: The Entity-Relationship Model

4a. discuss the need for an entity-relationship model

  • What are the purpose and the benefit of an E-R model?
  • What are the symbols for entities and relationships, including their syntax and semantics?
  • What is an example of a high level E-R model?

An E-R model stands for Entity-Relationship model. It views the world as a collection of entities (things or objects) and the relationships among them. An entity can be a physical object in the real world or a concept, and is represented by a noun. Relationships or relations are associations between entities. The term 'relation' refers to the manner in which the entities are associated; the term 'relationship' refers to the association itself. Relations are represented by verbs. Thus, an E-R model focuses on the whats of the world, and is well-suited to model data. Actions of the real world are represented by relationships.

The context for discussing E-R models is the database development process. This process is an adaptation of the software development process: database requirements analysis that produces the requirements and conceptual model and the user schemas; database design that produces the database design or logical model and design schemas; database implementation that produces the internal or physical model and the database code. Which database development work product produces an E-R diagram and which stakeholders read it? Can you give a small example of an E-R model? What is a single characteristic that distinguishes an entity?

To review, watch this video on Conceptual Design and read pages 113-116 of Database Design.

 

4b. explain the entity-relationship model and all of its associated symbols

  • What additional information in the domain of application should be represented in an E-R model?
  • Describe additional symbols used in E-R modeling to capture that information?

In addition to the main symbols for an entity and a relation, additional symbols are needed to represent information in real-world domains of application. Entities have names that are nouns. In our daily speaking and writing, we use qualifiers to add information to constrain nouns. Qualifiers, which we call constraints, are represented in an E-R model by attribute types, entity types, entity sets, derived attributes, and key attributes. A key attribute uniquely identifies one and only entity; each entity is uniquely identified by a key attribute value. An entity can be a weak or strong entity. The values of an entity and of attributes are constrained by a domain of values. How large, relatively, is a domain of a set of attributes compared to the domain of a single-valued attribute? It is multiplicatively larger. If an entity has 4 attributes, A1, A2, A3, A4, the domain for the values of the set is the cartesian product of the value sets of the 4 attributes; and the size of the value set is the product of the sizes of the 4 value sets. In practice, large databases can have hundreds of attributes. 

Relationships constrained by relationship type, degree of a relationship, attributes. Attributes constrain an entity; in the same manner, attributes can constrain a relationship. Note that as we study several models, the boundary between entity and relationship can get blurred. Indeed, in the relational model an entity, which is the model calls a table, is a relationship. Also, in some models, relationships are represented as attributes, such as in function modeling. In object modeling, relationships are attributes of an object, which are a reference to another object. In relational modeling, this is called a foreign key attribute or reference.

 

4c. describe relationship constraints

  • What are some other ways of constraining a relationship? Give an example of each.

Typically, an entity represents a group or class of individual instances and is similar to a type. Relationships can also be constrained by attributes. Cardinality and participation are two other ways of constraining a relationship. When two entities are related (say A is related to B), two obvious questions are "how many instances of B are related to a given instance of A?" (this is the perspective from A to B) and "how many instances of A are related to a given instance of B?" (this is the perspective from B to A). If the answer is 1, we say the cardinality is 1:1. If the answer is n, we say the cardinality is 1:n. If the answer to the first question cannot be 0, we say that the existence of an instance of A depends on the existence of an instance of the relationship, or that A totally participates in the relationship. If the answer to the second question cannot be 0, we say that the existence of an instance of B depends on the existence of an instance of the relationship, or that B totally participates in the relationship.

How are cardinality and participation constraints represented in an E-R diagram? What is an identifying relationship; what cardinality characterizes an identifying relationship? To review, watch this video from 43:00 to 51:00.

The above video gives a good overall review of the E-R model. In addition, this unit has several good written resources that you should review. The other resources for Unit 4 provide helpful suggestions and complementary explanations. When you are studying a unit, you should create a small directory of the resources you find helpful, annotate them with a few keywords on important points, and jot down a few notes of what is important to you. They will be very valuable when you review for the final exam. 

An E-R model is a representation of an application domain, in terms of entities and relationships among the entities. Qualifications, properties, restrictions that add detail to the entities and relationships are called constraints. There are entity constraints and relationship constraints. Constraints are E-R representations of some of the semantics of the application domain.

Entity constraints include domain integrity or attribute value constraints, entity integrity or primary key constraint, and referential integrity or foreign key constraints. Relationship constraints include business rules, cardinality and participation constraints, and relationship types. Entity and relationship are types or collections of instances, called entity set and relationship set, respectively. Sometimes a lack of precision blurs the distinction between them.

When we study, it is easy to get lost in details. Here are some important concepts and principles to keep in mind

  • E-R models address:
    1. structure (produce normalized schema)
    2. integrity (focus on data relationships vs. business relationships)
    3. manipulation (OLTP manipulation rather than OLAP)

E-R models, and other formal models, are languages that represent information. E-R symbols can be thought of as pairing to nouns, verbs, active voice, or passive voice in natural languages.

To become familiar with E-R modeling in preparation for the final exam, you should study example E-R models in the resources and practice creating E-R diagrams. Use the examples, exercises, and problems in this resource and Advanced Topics in ER Modeling.

 

Unit 4 Vocabulary

This vocabulary list includes terms that might help you with the review items above and some terms you should be familiar with to be successful in completing the final exam for the course. 

Try to think of the reason why each term is included. 

  • Entity
  • Relation
  • Nouns and verbs
  • Symbols/syntax and semantics
  • Database development process
  • Conceptual design
  • Schema
  • Stakeholders/nontechnical
  • Independent existence
  • Qualifier
  • Types
  • Entity type
  • Attribute type
  • Derived attribute
  • Key attribute
  • Entity set
  • Relationship set
  • Weak entity
  • Strong entity
  • Cartesian product (D1 x D2)
  • Relationship
  • Relationship type
  • Degree of a relationship
  • Attribute vs. relationship
  • Cardinality of a relationship
  • Participation of a relationship
  • Identifying relationship
  • Entity integrity
  • Domain integrity
  • Referential integrity
  • Additional symbols, such as minimum, maximum, for optional and mandatory participation
  • Grammar analogy
  • Business rules
  • 'Existence'
  • 'Connectivity'
  • Disjoint entity types
  • Overlapping entity types
  • Enhanced ER, such as specialization, inheritance, or hierarchy
  • ER to SQL DDL