The Basic ER Diagram—A Data Modeling Schema

What Is a Data Modeling Schema?

A data modeling schema is a method that allows us to model or illustrate a database. This device is often in the form of a graphic diagram, but other means of communication are also desirable — non computer-people may or may not understand diagrams and graphics. The ER diagram (ERD) is a graphic tool that facilitates data modeling. The ERD is a subset of "semantic models" in a database. Semantic models refer to models that intend to elicit meaning from data. ERDs are not the only semantic modeling tools, but they

are common and popular. When we begin to discuss the contents of a database, the data model helps to decide which piece of data goes with which other piece of data on a conceptual level. An early concept in databases is to recognize that there are levels of abstraction that we can use in discussing databases. For

example, if we were to discuss the filing of "names," we could discuss this: Abstractly, that is, "we will file names of people we know." or Concretely, that is, "we will file first, middle, and last names (20 characters each) of people we know, so that we can retrieve the names in alphabetical order on last name, and we will put this data in a spreadsheet format on package x." If a person is designing a database, the first step is to abstract and then refine the abstraction. The longer one stays away from the concrete details of logical models (relational, hierarchical, network) and physical realizations (fields [how many characters, the data type, etc.] and files [relative, spreadsheet]), the easier it is to change the model and to decide how the data will eventually be physically realized (stored). When we use the term "field" or "file," we will be referring to physical data as opposed to conceptual data. Mapping is the process of choosing a logical model and then moving to a physical database file system from a conceptual model (the ER diagram). A physical file loaded with data is necessary to actually get data from a database. Mapping is the bridge between the design concept and physical reality. In this book we concentrate on the relational database model due to its ubiquitousness in contemporary database models.

What Is an Entity Relationship (ER) Diagram?

The ER diagram is a semantic data modeling tool that is used to accomplish the goal of abstractly describing or portraying data. Abstractly described data is called a conceptual model. Our conceptual model will lead us to a "schema." A schema implies a permanent, fixed description of the structure of the data. Therefore, when we agree that we have captured the correct depiction of reality within our conceptual model, our ER diagram, we can call it a schema. An ER diagram could also be used to document an existing database by reverse-engineering it; but in introducing the subject, we focus on the idea of using an ER diagram to model a to-be-created database and deal with reverse-engineering later.

Defining the Database — Some Definitions: Entity,Relationship, Attribute

As the name implies, an ER diagram models data as entities and relationships, and entities have attributes. An entity is a thing about which we store data, for example, a person, a bank account, a building. In the original presentation, Chen (1976) described an entity as a "thing which can be distinctly identified." So an entity can be a person, place, object, event, or concept about which we wish to store data. The name for an entity must be one that represents a type or class of thing, not an instance. The name for an entity must be sufficiently generic but, at the same time, the name for an entity cannot be too generic. The name should also be able to accommodate changes "over time." For example, if we were modeling a business and the business made donuts, we might consider creating an entity called DONUT. But how long will it be before this business evolves into making more generic pastry? If it is anticipated that the business will involve pastry of all kinds rather than just donuts, perhaps it would be better to create an entity called PASTRY — it may be more applicable "over time." Some examples of entities include:

1. Examples of a person entity would be EMPLOYEE, VET, or STUDENT.
2. Examples of a place entity would be STATE or COUNTRY.
3. Examples of an object entity would be BUILDING, AUTO, or PRODUCT.
4. Example of an event entity would be SALES, RETURNS, or REGISTRATION.
5. Examples of a concept entity would be ACCOUNT or DEPARTMENT.

In older data processing circles, we might have referred to an entity as a record, but the term "record" is too physical and too confining; "record" gives us a mental picture of a physical thing and, in order to work at the conceptual level, we want to avoid device-oriented pictures for the moment. In a database context, it is unusual to store information about one entity, so we think of storing collections of data about entities — such collections are called entity sets. Entity sets correspond to the concept of "files," but again, a file usually connotes a physical entity and hence we abstract the concept of the "file" (entity set) as well as the concept of a "record" (entity). As an example, suppose we have a company that has customers. You would imagine that the company had a customer entity set with individual customer entities in it. An entity may be very broad (e.g., a person), or it may be narrowed by the application for which data is being prepared (like a student or a customer). Broad entities, which cover a whole class of objects, are sometimes called generalizations (e.g., person), and narrower entities are sometimes called specializations (e.g., student). In later diagrams (in this book) we will revisit generalizations and specializations; but for now, we will concern ourselves with an application level where there are no subgroups (specializations) or supergroups (generalizations) of entities. When we speak of capturing data about a particular entity, we refer to this as

an instance. An entity instance is a single occurrence of an entity. For example, if we create an entity called TOOL, and if we choose to record data about a screwdriver, then the screwdriver "record" is an instance of TOOL. Each instance of an entity must be uniquely identifiable so that each instance is separate and distinctly identifiable from all other instances of that type of entity. In a customer entity set, you might imagine that the company would assign a unique customer number, for example. This unique identifier is called a key. A relationship is a link or association between entities. Relationships are usually denoted by verb phrases. We will begin by expanding the notion of an entity (in this chapter and the next), and then we will come back to the notion of a relationship (in Chapter 4) once we have established the concept of an entity. An attribute is a property or characteristic for an entity. For example, an entity, AUTOMOBILE, may have attributes type, color, vehicle_id, etc.