Diagram Conventions   «Prev  Next»

Lesson 2 The entity-relationship (ER) diagram
ObjectiveDefine the function of an ER diagram.

Purpose of ER Diagram in Data Modeling

An Entity-Relationship Diagram (ERD) is a crucial tool used in data modeling for the purpose of visualizing and describing the structured data within a system. It provides a comprehensive graphic representation of the logical structure of a database, and it is particularly beneficial in the design and construction of complex relational databases. The ERD serves multiple purposes, and its functionalities are multidimensional.
  1. Data Structure Visualization: ERDs graphically represent the entities, relationships, and constraints within a database. It showcases the relationships among different entities and their respective attributes, providing a clear and concise view of the data's structure. This graphical representation aids in understanding the complexity of the system and its data interactions.
  2. Database Design: An ERD is an integral part of the database design process. By identifying the entities, attributes, relationships, and constraints, an ERD provides the blueprint necessary to construct a database. It ensures that all necessary data are accounted for, while maintaining the relationships and rules that exist among them.
  3. Documentation: ERDs serve as an excellent documentation tool. The graphical representation of the system's data can be used as a reference point for database administrators, developers, stakeholders, and other personnel who need to understand the system. The ERD can help new team members grasp the existing system's data model more quickly and effectively.
  4. Improving Communication: The ERD serves as a communication tool, facilitating discussions between various stakeholders - from system analysts and designers to end-users. By presenting the database structure visually, it can help bridge the gap between technical and non-technical stakeholders, ensuring everyone has a shared understanding of the system's design.
  5. System Analysis and Enhancement: In addition to the design of a new system, ERDs can be used to analyze existing systems for improvement. They can help identify redundancies, inefficiencies, and missing elements within the system, thus enabling enhancements that can lead to more efficient system operation and better performance.
  6. Aid in Data Integration: ERDs are beneficial when integrating data from different sources or databases. They provide a holistic view of the data structure, making it easier to map and integrate data from disparate sources.

ERD Objectives

Furthermore, an entity-relationship (ER) diagram is a conceptual model that captures an organization's business rules (and any common-sense rules) as concisely as possible. Specific objectives of an ER diagram are to provide:
  1. A unified view of the entities, attributes, and relationships that comprise the overall data structure of the database
  2. A convenient communication tool during database design that enables designers and users of data to view (and review) together the business rules that underlie the overall database structure
  3. A blueprint for generating a relational database using SQL[1]. It provides a set of commands that can be used to add data to a database, retrieve that data, and update it. SQL often pronounced "sequel"; is universally supported by relational database vendors.
  4. Documentation for the duration of the DBLC, serving as a reference should structural changes to the database become necessary
Several types of ER diagrams are available in CASE tools software, all of which function identically. In summary, Entity-Relationship Diagrams are a foundational tool in data modeling. They facilitate the visualization of the data's structure, aid in the design and documentation of databases, improve communication among stakeholders, help analyze and enhance systems, and assist in data integration.

Value Sets (Domains) of Attributes.

Each simple attribute of an entity type is associated with a value set (or domain of values), which specifies the set of values that may be assigned to that attribute for each individual entity.
In Figure 7-2, if the range of ages allowed for employees is between 16 and 70, we can specify the value set of the Age attribute of EMPLOYEE to be the set of integer numbers between 16 and 70. Similarly, we can specify the value set for the Name attribute to be the set of strings of alphabetic characters separated by blank characters, and so on. Value sets are not displayed in ER diagrams, and are typically specified using the basic data types available in most programming languages, such as integer, string, Boolean, float, enumerated type, subrange, and so on. Additional data types to represent common database types such as date, time, and other concepts are also employed.
Two entity types, EMPLOYEE and COMPANY, and some member entities of each.
Figure 7-2 : Two entity types, EMPLOYEE and COMPANY, and some member entities of each.

Mathematically, an attribute A of entity set E whose value set is V can be defined as a function from E to the power set[2] P(V) of V:
A : E --> P(V)

We refer to the value of attribute A for entity e as A(e). The previous definition covers both single-valued and multivalued attributes, as well as NULLs. A NULL value is represented by the empty set. For single-valued attributes, A(e) is restricted to being a singleton set for each entity e in E, whereas there is no restriction on multivalued attributes. For a composite attribute[3] A, the value set V is the power set of the Cartesian product of P(V1), P(V2), ... , P(Vn), where V1, V2, ..., Vn are the value sets of the simple component attributes that form A:
V = P (P(V1) × P(V2) × ... × P(Vn))
The value set provides all possible values. Usually only a small number of these values exist in the database at a particular time. Those values represent the data from the current state of the miniworld. They correspond to the data as it actually exists in the miniworld.

The next lesson describes the three best-known examples.
[1]SQL: SQL is an acronym for Structured Query Language
[2] power set: The power set P (V) of a set V is the set of all subsets of V.
[3]composite attribute: An attribute composed of more than one attribute is called a composite attribute.