Lecture notes chapter 3: the entity relationship model

The first step in database development is database analysis.

We must represent a conceptual data model, which is independent of any technology

but rather the business process in humanistic terms. In other words, the conceptual data model

represents entities and relationships as represented in the real world.

The conceptual data model that is the most popular is the entity relationship or ER model.

Why is data modeling important.

• Data characteristics understood during data modeling are crucial to design. The facts and rules captured during data modeling to help ensure data integrity.

• Capturing the flow of data through a process provides independence from those processes. While process flow may be unstable over a long period of time, data flow tends to have a longer lifespan. For example, accounting processes may change but not the data that flows into the process.

• Defining flow of data enables maximum flexibility for information.

What is the entity relationship model.

Defined as a logical representation of the data for a business process (p. 87).

An entity relationship diagram is a graphical representation of an entity relationship model.

The entity relationship model is expressed in terms of entities, relationships among those entities,

and the attributes of both the entities and their relationships.

Go over sample entity relationship diagram, figure 3.1, p. 87.

Entity relationship model constructs, p. 89.

The basic constructs of an entity relationship model are entities, relationships, and attributes.

The simplistic yet powerful model allows accurate real world constructs.

An entity is a person, place, object, event, or concept about which the business or organization wishes to maintain data. Some examples are: a person (employee, student ); a place (city, state); object (building, computer); event (sale, class registration); concept (account, class course).

Go over basic symbols on page 90 later.

An entity type is a collection of entities that's share common characteristics or properties, and is given a name. The entity type is given a name and placed inside of a rectangle. An entity instance of an entity type is the actual data; for example, an employee might be the entity type, an entity instance might be a specific employee. Entity type is metadata, entity instance is the data.

Go over inappropriate entity usage on page 92.

Most of your basic entity types should be strong entity types. A strong entity type is one that exists independently of other entity types, meaning that it should always have an identifier, that which uniquely distinguishes each entity instance.

A weak entity type is one whose existence depends on another entity type.

A weak entity type does not have its own identifier, but rather a partial identifier that that relies on an identifier from another entity type. Explain the example on page 93, figure 3 -- 5. The double blocks and double diamond indicate weak relationship. The relationship between a weak entity type and its owner is called an identifying relationship.

Each entity type has a set of attributes associative with it. An attribute is a property or characteristic of an entity type. For example, a student entity type might have the attributes: student ID, student name, address, phone number, major, grade-point average, date of enrollment.

Each entity instance has a value associated with each of the attributes of that entity type. Each entity has an identifying attribute plus other attributes which further describe the entity type.

Some attributes can be further broken down. For example, the address attribute in the entity type student may be broken: street address, apartment, city, state, zip code. This is referred to as a composite attribute. The decision whether or not to divide an attribute into composite attributes depends on whether or not the data is necessary at that level. A simple attribute (or atomic) is one cannot be broken down any further.

A stored attribute is one which data is input; it may be left blank on creation, to be entered later.

A derived attribute is one whose values may be calculated from related attribute values, or data not stored in the database, or attributes from other entities that are related.

An identifier is an attribute or combination of which periods which uniquely identify individual instances of entity type. For example the identifier for the student entity type would be the student ID. For some entity types there is no single attribute which will uniquely identify an entity instance; in these cases two or more attributes from the entity type may serve to uniquely identify -- this is called a composite identifier.

Suggested rules for choosing an identifier for an entity type (p. 97):

• Choose an identifier will not change its value over the life of each instance of the entity type.

• Choose an identifier for each entity instance that will be guaranteed to be valid and not NULL.

• Avoid identifiers that depend on classification or location, which may change in the future.

• Possibly substituting single attribute identifiers for large composite identifiers. These are called surrogate identifiers -- example introducing a unique ID that takes to place of several composite attributes.

Relationships

A relationship is an association of one or more entity types to each other.

The relationship is represented with a diamond. Relationships typically occur because of an event; entity instances are related because action was taken -- therefore, a verb phrase is typically used, and a verb should be in the present tense and descriptive.

Go over the example on page 98 figure 3 -- 10a.

A relationship type is a meaningful situation between entity types. What this is saying is that this relationship allows one to answer questions that cannot be answered giving only one entity type, but only by the combination of the entity types. Use the example from above.

A relationship instance is an association between entity instances where each relationship instance includes exactly one entity from each participating entity type. Example figure 3.10b.

Relationships, like entity types, may have attributes that are unique to the relationship. Figure 3 -- 11.a shows an attribute on a relationship.

If it there is one or more attributes assigned to occur relationship, it is possible that the attribute should be represented as the entity type termed an associative entry. An associative entry is the combination of one or more entity types and contains attributes that are unique to that combination between those entities. The associative entry is represented with a rectangle surrounding the diamond.

For conditions that should exist for conversion of a relationship to an associative entity:

• The relationships for the entity types are one to many.

• The resulting associative entry has independent meeting to the end-user.

• The associative entry has one or more attributes in addition to a unique identifier.

• The associative entry participates in one or more relationships independent of the entities related to it.

Figure 3 -- 11b illustrates this relationship.

Place emphasis on the many to one in the relationship type as opposed to the one to many in the associative entity.

Degrees of relationships

The degree of a relationship is the number of entity types to participate in that relationship.

There are three general types of relationships: unary (degree one), binary (degree 2), ternary (degree 3). Higher degree relationships are possible but not desirable -- too difficult to maintain.

Again -- is very important when you develop an entity relationship model that you understand the rules of what you are modeling as data represents a specific situation and not a generalization. Different businesses could look at the same type a situation very differently, even in the same organization.

Unary relationships

Is a relationship between the instances of a single entity type; also called a recursive relationship. It can be a one-to-one or a one too many or a minute to many relationship. See figure 3 -- 12a one page 102. A more comprehensive example is with Bill of materials structure, figure 3 -- 13b on page 103. an associative entity is shown on figure 3 -- 13c.

Binary relationships

Is a relationship between the instances of two entity types, and is the most common type of relationship encountered in data modeling. Figure 13 -- 12b shows three common types.

Ternary relationships

A ternary relationship is a simultaneous relationship among the instances of three entity types. It is not the same as three binary relationships, as the relationship attributes cannot usually be determined unless all entity types are involved. In this case sum of the whole is more than the parts. Use example 3 -- 12c on page 103.

Since there is an attribute on this relationship, it suggests conversion to an associative entity, on figure 3 -- 14, page 105. Discuss the again conversion of many to one too one too many relationship, and how it works more efficiently to describe the data flow in this manner. Note that there is not an identifier as of yet.

It is strongly recommended that you convert all ternary relationships to associative entities.

Cardinality constraints

A cardinality constraint specifies the number of instances of entity B that must be associated with entity A.

Minimum cardinality is the minimum number of instances of one entity that may be associated with each instance of another entity.

Maximum cardinality is the maximum number of instances of one entity that may be associated with each instance of another entity.

Go over examples one figures 3 -- 15, 3 -- 16.

Go over mandatory one, optional 1,mandatory many, optional many.