Data Modeling with Entity-Relationship Diagrams

Read Chapter Four (Database Management)

Do all the projects.

Complete all the Quizzes.

Instructional Video: Full Screen Ipod Version

Review PowerPoint presentation1 presentation2

Chapter Objectives: In this chapter you should learn

What entity-relationship data modeling is
How to read and draw entity-relationship (E-R) diagrams
How to represent entity classes, attributes, and relationship types in E-R diagrams
How to add cardinality and other constraints to E-R diagrams
How to model video rentals and other BigHit Video information using E-R diagrams
What inheritance is and how to use it to improve data models
How to represent inheritance in entity-relationship diagrams

Entity-Relationship Modeling

E-R data model is a high level conceptual model that describes data as entities, attributes, and relationships.

The data modeling process is iterative: You start putting some ideas together, then the process reveals some problems in your thinking. So, you go back and rework your ideas.

Entity-Relationship Diagrams

E-R diagrams enable designers and users to express their understanding of what the planned database is intended to do and how it might work, and to communicate about the database through a common language.

Examples of E-R Diagram Symbols

Figure 4.11 Examples of E-R Diagram Symbols

Entity Classes and Attributes

Entity Class are represented by rectangles

Attributes are represented by ovals

Key attribute is represented by an underline

Multivalued attributes is represented with an oval with a double border

Derived attribute is represented with an oval dashed border -- Example the AGE from current - birth

Composite attribute is represented with an oval that connects to additional ovals

Figure 4.1 E-R Diagram for Entity Class Customer

This shows an E-R diagram for the entity class Video.

Figure 4.2 E-R Diagram for Entity Class Video

Relationship Types and Constraints

A relationship type is represented by a diamond and two connecting lines. The name of the relationship is shown inside the diamond. Reading the diagram from left to right to yields "a store owns a video"..

The name of the relationship type is a verb or verb phrase that describes the relationship.

The alternate relationship name IsOwnedBy is the name of the relationship type when reading the other direction.

Figure 4.3 E-R Diagram for the Owns Relationship Type

Now we add cardinality constraints. The relationship type is one-to-many. That is, a store may own many videos and a video may be own by no more than one store.

Figure 4.4 E-R Diagram for the Owns Relationship Type Showing Cardinality and Participation Constraints

We can best understand the meaning of these cardinality marks by creating sentences to represent the relationship roles. To create such a sentence, name one entity, then the relationship, then the cardinality, and finally the other entity.

For example, we could create the following two sentences by reading the diagram from left to right and right to left, respectively:

A store may own many (M) videos
A video is owned by no more than one (1) store.

You can create sentences to understand the constraints. We add either "may" or "must" to the sentence depending on whether the participation is optional or mandatory.

A store may own many videos
A video must be owned by one store.

Modeling Video Rentals

Modeling Rentals as a Relationship Type

A relationship between the customer and the video is created when the customer rents the video. The relationship is removed when the customer returns the video.

Figure 4.5 E-R Diagram of Relationship Type Rents

Now we can capture the history with using previous information. The relationship type PreviouslyRented is many-to-many because each customer may have previously rented many videos and each video may have been rented many times. The participations are optional for both customers and videos.

Figure 4.6 E-R Diagram of Relationship Type PreviouslyRented

Figure 4.7 E-R Diagram of a Combined Current and Previous Rental Relationship Type

Modeling Rentals as an Entity Class

Another way to look at this is rental as an entity and not simply a relationship. Note that we have been referring to a "rental" as an object of interest. The use of the noun "rental" suggests that we are referring to an entity.

Each rental entity has three attributes: dateDue, dateRented, and cost.

The relationship types are called Has.

These are the relationship sentences for the diagram.

A customer may have many rentals.
A rental must have one customer.
A rental must have one video.
A video may have no more than one rental.

Figure 4.8 An E-R Diagram Showing Entity Class Rental and Its Relationship Types

We have an entity with no distinguishing attributes that is partly identified by its relationship to other entities. We call this a weak entity and class Rental a weak entity class.

One difficulty with understanding the meaning of Rental entities is the problem of distinguishing between one rental and another solely bases on attributes values.

Figure 4.9 An E-R Diagram Showing Weak Entity Class Rental

The double border on the diamond that relates Rental to Video also marks Video as the owner entity class. The related video is considered the owner of the rental. Without the relationship, the rental cannot exist. The key for a Rental entity is therefore the videoId attribute, which is also the key for its owner video.

Roles in Relationship Types

Roles are particularly important in situations where two entity classes are linked by more than one relationship type and where a relationship type links an entity class to itself.

Below is the traditional marriage relationship. It relates one person to another. This diagram shows the entity class Person, with its key attribute ssn, and the relationship types IsMarriedTo and IsChildOf, with their lines and cardinalities.

Figure 4.10 Relationship Types IsMarriedTo and IsChildOf, with Role Names

The names of the roles wife, husband, child, and parent are shown in the diagram next to the relationship-type lines.

Additional information from the figure is expressed by the following sentences:

A person may be the child of one or two parents
A person may be the parent of zero or more children
A person may be the wife of a husband
A person may be the husband of a wife

An E-R Model for BigHit Video

Recording the History of Rentals

The entity class Customer, Rental, and Video are shown.

Figure 4.12 An E-R Diagram for BigHit Video

To uniquely identify a previous rental, we must add another attribute to the key.
This attribute, called a discriminator or partial key, should uniquely identify the entity among all those related to a specific identifying entity.

Figure 4.13 E-R Diagram for Entity Class PreviousRental

Employee Roles and Cardinalities

The below diagram, specifies than an employee can be the manager of no more than one store, and each store has exactly one manager.

Figure 4.14 E-R Diagram for Entity Class PreviousRental with Optional Customer Participation

The precise specification of the cardinalities exposes the kinds of issues that can arise from real business practices. It is users' responsibility to determine whether the E-R model adequately represents their enterprise and meets its needs.

Figure 4.15 E-R Diagram for WorksIn and Manages with Modified Cardinality and Participation Constraints

Purchase Orders and the True Meaning of Video

The entity class PurchaseOrder could be defined as weak. The cardinalities of the relationships of class Purchase require that a purchase order have a single supplier and at least one video.

Figure 4.16 Model of Purchase Orders from Figure 4.12

Figure 4.17 Alternative Representation of a Purchase Order

Figure 4.18 E-R Diagram for Suppliers, Purchases, Movies, and Videos

Employees, Time Cards, and Pay Statements

These weak entity classes record when employees work and what they are paid. In both cases, the entities are not uniquely determined by their identifying relationships.

For instance, a pay statement is identified by its related employee, but is not unique for that employee.

It is the combination of the employee's ssn and datePaid that is unique.

Figure 4.19 TimeCard, PayStatement, and their Related Entity Classes from Figure 4.12

Object-Oriented Data Models

The concepts supporting object-oriented design will emerge from the consideration of these different types of videos.

Figure 4.20 E-R Diagram with dvd and videotape Attributes of Entity Class Video

To clearly distinguish between videotapes and DVD's, we can create two new entity classes, one for each media type. We add the appropriate attributes to each class.

Figure 4.21 E-R Diagram Showing Classes DVD and Videotape as Unrelated Classes

The object-oriented approach to representing this information recognizes that there is a commonality between videotape objects and DVD objects.

Figure 4.22 E-R Diagram with Relationship Types for Classes DVD and Videotape

The meaning of inheritance. Each entity of class Video may be a member of either class DVD or class Videotape.

Figure 4.23 Object-Oriented E-R diagram for Entity Classes Video, DVD, and Videotape

A different combination of participation and disjointedness is show in the below figure. This defines an example of inheritance for employees.

The superclass connector is a single line to express an optional participation constraint.

Figure 4.24 E-R Diagram Showing Optional and Overlapping Inheritance for Class Employee

Disjoint Rule (d) - Specifies that if an entity instance (of the super type) is a member of one subtype, it cannot simultaneously be a member of any other subtype.

Overlap Rule (0) - Specifies that an entity instance can simultaneously be a member of two (or more) subtypes.

E-R Model for Video Sales for BigHit Video

Figure 4.24 E-R Diagram Showing Optional and Overlapping Inheritance for Class Employee

BigHit Video Inc. wants to create an information system for online sales of movies in both DVD and videotape format. People will be allowed to register as customers of the online site and to update their stored information. Information must be maintained about customers’ shipping addresses, e-mail addresses and credit cards. In a single sale, customers will be allowed to purchase any quantity of videos. The items in a single sale will be shipped to a single address and will have a single credit card charge.

A customer will be provided with a virtual shopping cart to store items to be purchased. As each item is selected, it is added to the shopping cart. When the customer finishes shopping, he will be directed to a checkout area where payment and shipping information is entered. Once the sale is complete, the customer will be sent a receipt by e-mail.

Table 4.1 Entity Classes and Attributes for Online Movie Sales with Subclasses

Entity Class	Attribute	Constraints or Further Description
Customer	accountId	Key
	lastName	Not null
	firstName
	shippingAddresses	Multi-valued composite with components name, street, city, state, zipcode
	emailAddress
	creditCards	Multi-valued composite with components type, accountNumber, expiration
	password	Not null at least 6 characters
Movie	movieId	Key
	title
	genre
	media	Either “dvd” or “videotape” determines subclass
DVD	languages	Subclass of Movie
	videoFormat
	captioning
Videotape	format	Subclass of Movie
	soundtrack
Sale	saleId	Key
	totalCost
	dateSold
	creditCard	Composite with components type, accountNumber,and expiration
ShoppingCart	cartId	Key
	dateCreated

Table 4.2 Relationship Types for Online Movie Sales

Relationship Type	Entity Class	Entity Class	Cardinality Ratio	Attibutes
Purchases	Customer	Sale	one-to-many
Includes	Sale	Movie	many-to-many	quantity
Selects	Customer	ShoppingCart	one-to-many
Includes	ShoppingCart	Movie	many-to-many	quantity

Figure 4.25 E-R Diagram for Movie Sales

Key Terms

Data dictionary	A table that contains the descriptions of classes and the types, descriptions, and constraints on attributes of an information system
Discriminator or partial key	An attribute of a weak entity class that identifies an entity from among all of those with the same identifying entities. A discriminator is part of the key of the weak entity class
DVD	A high-capacity compact disk that is used for storing video as well as audio and data
Entity-relationship (E-R) diagram	A graphical strategy for representing E-R models
Entity-relationship (E-R) model	A strategy for constructing conceptual data models using diagrams that focus on entity classes, relationship types, and attributes
Identifying relationship type	A to-one relationship type between a weak entity class and its owner entity class that helps to uniquely identify an entity of the weak class
Inheritance (is-a) relationship type	A relationship type in which the subclass (child) is related to a superclass (parent). Each object of the subclass is also an object of the superclass and inherits all of the superclass attributes and relationships
Object-oriented data model	A conceptual data model that divides objects into classes and supports the direct representation of inheritance relationship types. E-R diagramming supports the inclusion of object-oriented features
Weak entity class	An entity class with no key of its own, whose objects cannot exist without being related to other objects. A weak entity class must have at least one identifying relationship type and corresponding owner entity class