the entity-relationship model, p. i r. nakatsu. data modeling a data model is the relatively simple...

The Entity-Relationship Model,

P. IR. Nakatsu

Data Modeling• A data model is the relatively

simple representation, usually graphic, of the structure of the database.

• The database designer usually employs data models as communication tools to facilitate interaction among the designer, the applications programmer, and the end user.

Entity-Relationship (ER)Modeling

The ER model serves as the basic database blueprint.

• Entity: anything about which data are to be collected and stored (corresponds to a table; represented by a rectangle)

• Attribute: a characteristic of an entity (corresponds to a column in a table)

• Relationship: describes an association between entities (represented by a line joining the two entities together).

Database Systems, 9th Edition

EntitiesSomething that users want to track.

© 2000 Prentice Hall

Attributes (properties)Describe the entity’s characteristics.

Entity:EmployeeAttributes:LastName, FirstName, DateOfHire, DateofBirth

Figure 4.3 – Depicting Attributes on an ERM

7

Types of Attributes

• Required vs. Optional• Simple vs. Composite• Single-valued vs. Multi-valued • Derived (or Calculated)• Identifier

Provide examples of each type.

Advantages and Disadvantages of Storing Derived Attributes

9

Identifier An attribute or attributes that name or

identify entity instances.Note: Can either be unique or

nonunique

Entity: EmployeeIdentifier 1: SocialSecurityNumberIdentifier 2: LastName, FirstName

Relationship Types• One-to-Onee.g., one dean to one university

one head-of-state to one country• One-to-Manye.g., one painter paints many paintings

one customer places many orders• Many-to-Manye.g., students enroll in courses

Determining the Relationship type: 1:1,

1:M, M:NTwo questions to identify the

relationship type between A and B:– How many instances of B are related

to one instance of A?– How many instances of A are related

to one instance of B?

Cardinality in an ER Diagram

Cardinality expresses the minimum and maximum (min, max) number of entity occurrences associated with one occurrence of the related entity.

Expressing Minimum Cardinality:

Hash Marks and OvalsHash mark (or vertical line): indicates a

mandatory relationship (min cardinality of 1)

Circle: indicates an optional relationship (min cardinality of 0)

Expressing Optional/Mandatory Relationships

On ER Diagram

ER Diagram Problem 1• Acme Insurance Company employs hundreds of

claims adjustors across the U.S. As part of its agreement when joining the company, each adjustor is assigned to a company automobile.

• A claims adjustor works on a number of insurance claims. Sometimes a team of adjustors (two or more adjustors) will work together on a single claim, and there is always at least one adjustor assigned to a claim.

Draw the ER Diagram for this situation. Include

relationship types using crow’s foot notation, cardinalities in parentheses notation, and hash marks / circles to indicate optional/mandatory relationships.

Existence Dependence

17

Existent Dependent Existent Independent

Entity exists in the database only when it is associated with another related entity occurrence.

Entity exists apart from all of its related entities

Referred to as a strong entity or regular entity.

Weak Entity• Conditions

– Existence-dependent – Has a primary key that is partially or

totally derived from parent entity in the relationship

– An entity that is not weak is called a strong entity.

18

Weak Entity Examples

Example 1:Building (BuildingName)Apartment (BuildingName, Apartment#)

Example 2:Employee (EmployeeNo)Dependent (EmployeeNo, DependentNo)

Two Types of Relationships

HAS-A relationships: The term is used because an entity has a relationship with another entity (1:1, 1:M, M:N)

IS-A relationships: This term refers to relationships between super-types and sub-types.

Entity Supertypes and Subtypes

• Entity supertype – Generic entity type related to one or

more entity subtypes– Contains common characteristics

• Entity subtype– Contains unique characteristics of

each entity subtype

Database Systems, 9th Edition 22

Database Systems, 9th Edition 23

Disjoint and Overlapping Subtypes

• Disjoint subtypes (denoted by d)– Each entity instance of the supertype

can appear in only one of the subtypes.• Overlapping subtypes (denoted

by o)– Each entity instance of the supertype

can appear in more than one subtype.

Completeness ConstraintSpecifies whether a supertype occurrence

must be a member of at least one subtype• Partial completeness

– Symbolized by a circle over a single line– Some supertype occurrences are not members

of any subtype• Total completeness

– Symbolized by a circle over a double line– Every supertype occurrence must be a member

of at least one subtype

Database Systems, 9th Edition 26

ER Diagram Problem 2• ABC University is made up of several

departments. Each department employs several professors, and each professor is always assigned to a single department. A rule in the university stipulates that a department must consist of between 4 and 20 professors, inclusive.

• Each department is chaired by a department chair, who is a professor.

Draw the ER Diagram for this situation.