CIS-202 Chapter 4 - Data Modeling and the Entity-Relationship Model

Entities

-An entity is something that users want to track. Examples of entities are CUSTOMER John Doe, PURCHASE 12345, PRODUCT A4200, SALES_ORDER 1000, SALESPERSON John Smith, and SHIPMENT 123400.

unique/nonunique identifiers

-An identifier may be either unique or nonunique. The value of a unique identifier identifies one, and only one, entity instance. In contrast, the value of a nonunique identifier identifies a set of instances. -EmployeeNumber is normally a unique identifier, but EmployeeName is most likely a nonunique identifier (for example, more than one John Smith might be employed by the company). An entity class must have at least one unique identifier.

Data Model

A data model is a representation of the content, relationships, and constraints on the data needed to support the system requirements. Often, prototypes, or working demonstrations of selected portions of the future system, are created during the requirements phase. Such prototypes are used to obtain feedback from the system users.

Entity-Relationship Data Model

A number of different techniques can be used to create data models. By far the most popular is the entity-relationship model, first published by Peter Chen1 in 1976. The most important elements of the E-R model are entities, attributes, identifiers, and relationships. We now consider each of these in turn.

binary relationship

A relationship between the instances of two entity types: The one-to-one (1:1) relationship The one-to-many (1:N) relationship The many-to-many (N:M) relationship -In a 1:1 relationship, a single entity instance of one type is related to a single entity instance of another type. The LOCKER-ASSIGNMENT relationship associates a single EMPLOYEE with a single LOCKER. -In this relationship, which is called the ITEM-QUOTE relationship, a single instance of ITEM relates to many instances of QUOTATION. According to this sketch, an item has many quotations, but a quotation has only one item. --When discussing 1:N relationships, the terms parent and child are sometimes used. The parent entity is the entity on the one side of the relationship and the child entity is the entity on the many side of the relationship. Thus, in the 1:N relationship between ITEM and QUOTATION, ITEM is the parent and QUOTATION is the child. -This relationship is named ITEM-SOURCE, and it relates instances of ITEM to instances of SUPPLIER. In this case, an item can be supplied by many suppliers, and a supplier can supply many items.

Entity Instance

A single occurrence of an entity type. An entity instance of an entity class is the occurrence of a particular entity, such as CUSTOMER 12345. It is important to understand the differences between an entity class and an entity instance. An entity class is a collection of entities and provides the structure of the entities in that class. There are usually many instances of an entity in an entity class. For example, the class CUSTOMER has many instances—one for each customer represented in the database.

Non-ID-Dependent Weak Entities

All ID-dependent entities are weak entities. However, there are other entities that are weak but not ID-dependent. To understand this kind of weak entity, consider the relationship between the AUTO_MODEL and VEHICLE entity classes in the database of a car manufacturer, such as Ford or Honda. This is an interesting situation. VEHICLE has an identity of its own and therefore is not ID-dependent (note the dashed line in Figure 4-11(b)), yet the VEHICLE is an AUTO_MODEL, and if that particular AUTO_MODEL did not exist the VEHICLE itself would never have existed. Therefore, VEHICLE is now a weak but non-ID-dependent entity. In our E-R diagrams, while we will continue to use rounded corners to indicated ID-dependent weak entities, we will now use square corners on non-ID-independent weak entities. Note that both types of weak entities are shown as tan colored to flag that, whether ID-dependent or not, they are weak entities.

Identifying relationship

As shown in Figure 4-10, in our E-R models we use an entity with rounded corners to represent the ID-dependent entity. We also use a solid line to represent the relationship between the ID-dependent entity and its parent. This type of a relationship is called an identifying relationship. A relationship drawn with a dashed line (refer to Figure 4-7) is used between strong entities (or between a weak entity and a strong entity that it does not depend on) and is called a nonidentifying relationship because the relationship is not used to identify an ID-dependent entity.

IE Crow's Foot model

At least three different versions of the E-R model are currently in use. One of them, called Information Engineering (IE), was developed by James Martin in 1990. This model uses "crow's feet" to show the many side of a relationship, and it is sometimes called the IE Crow's Foot model. It is easy to understand, and we will use it in this text. pg.275 for more info & examples

use cases

Descriptions of the ways users will employ the features and functions of the new information system. A use case consists of a description of the roles users will play when utilizing the new system, together with descriptions of activities' scenarios. Inputs provided to the system and outputs generated by the system are defined.

Relationships

Entities can be associated with one another in relationships. The E-R model contains relationship classes and relationship instances. Relationship classes are associations among entity classes, and relationship instances are associations among entity instances. A relationship class can involve many entity classes. The number of entity classes in the relationship is known as the degree of the relationship. In Figure 4-4(a), the SUPPLIER-QUOTATION relationship is of degree two because it involves two entity classes: SUPPLIER and QUOTATION. The PARENT relationship in Figure 4-4(b) is of degree three because it involves three entity classes: MOTHER, FATHER, and CHILD. Relationships of degree two, which are the most common, are called binary relationships. Similarly, relationships of degree three are called ternary relationships.

Attributes

Entities have attributes, which describe the entity's characteristics. Examples of attributes include EmployeeName, DateOfHire, and JobSkillCode. In this text, attributes are printed in a combination of uppercase and lowercase letters. The E-R model assumes that all instances of a given entity class have the same attributes. For example, in Figure 4-2 the ITEM entity has the attributes ItemNumber, Description, Cost, ListPrice, and QuantityOnHand.

Identifier

Entity instances have identifiers, which are attributes that name, or identify, entity instances. For example, the ITEM entity in Figure 4-2 uses ItemNumber as an identifier. Similarly, EMPLOYEE instances could be identified by SocialSecurityNumber, by EmployeeNumber, or by EmployeeName.

Data Model

In order to be useful as the basis for a database design, these requirements must be transformed into a data model. When you write application programs, program logic must first be documented in flowcharts or object diagrams—when you create a database, data requirements must first be documented in a data model.

recursive relationship

It is possible for an entity to have a relationship to itself. Figure 4-15 shows a CUSTOMER entity in which one customer can refer many other customers to the business. This is called a recursive relationship (and because it has only one entity, it is also known as a unary relationship). As with binary relationships, recursive relationships can be 1:1, 1:N (shown in Figure 4-15), and N:M. We discuss each of these three types further in Chapter 5.

Minimum Cardinality

Relationships also have a minimum cardinality, which is the minimum number of entity instances that must participate in a relationship instance. Minimum cardinality can be shown in several different ways. One way, illustrated in Figure 4-6, is to place a hash mark across the relationship line to indicate that every instance of the other entity in the relationship must be related to at least one of these entities, and to place an oval across the relationship line to indicate that an instance of the other entity in the relationship need not be related to any of these entities. lucid chart for better example

ID-Dependant Entities

The E-R model includes a special type of weak entity called an ID-dependent entity. With this type of entity, the identifier of the entity includes the identifier of another entity. Consider the entities BUILDING and APARTMENT As you would expect, the identifier of BUILDING is a single attribute, in this case BuildingName. The identifier of APARTMENT, however, is not the single attribute ApartmentNumber but rather the composite identifier (BuildingName, ApartmentNumber). This happens because logically and physically an APARTMENT simply cannot exist unless a BUILDING exists for that APARTMENT to be part of. Whenever this type of situation occurs, an ID-dependent entity exists. In this case, APARTMENT is ID-dependent on BUILDING. The identifier of an ID-dependent entity is always a composite that includes the identifier of the entity that the ID-dependent entity depends on for its existence.

Subtype Entity

The extended E-R model introduced the concept of subtypes. A subtype entity is a special case of another entity called the supertype entity. Students, for example, may be classified as undergraduate or graduate students. In this case, STUDENT is the supertype, and UNDERGRADUATE and GRADUATE are subtypes. Note that the identifier of the supertype is also the identifier of the subtypes. Alternatively, a student could be classified as a freshman, sophomore, junior, or senior. In that case, STUDENT would be the supertype, and FRESHMAN, SOPHOMORE, JUNIOR, and SENIOR would be the subtypes.

Maximum Cardinality

The three types of binary relationships are named and classified by their cardinality, which is a word that means count. In each of the relationships in Figure 4-5, the numbers inside the relationship diamond show the maximum number of entity instances that can occur on each side of the relationship. These numbers are called the relationship's maximum cardinality, which is the maximum number of entity instances that can participate in a relationship instance.

weak vs strong relationships lines

Weak (Non-Identifying) Relationship -Entity is existence-independent of other enties -PK of Child doesn't contain PK component of Parent Entity Strong (Identifying) Relationship -Child entity is existence-dependent on parent -PK of Child Entity contains PK component of Parent Entity -Usually occurs utilizing a composite key for primary key In entity relationship modeling, solid lines represent strong relationships and dashed lines represent weak relationships. a weak entity is ID-dependent - one that is dependent on another entity.

Entity Class

With respect to a database, what term is used to describe an abstract representation of all of the information included in a data structure about a particular type of entity. -Entities of a given type are grouped into an entity class. Thus, the EMPLOYEE entity class is the collection of all EMPLOYEE entities. In this text, entity classes are shown in capital letters.