Transcript Chapter 3: The Entity

The Entity-Relationship
Model
R. 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
diamond)
Database Systems, 9th Edition
Entities
Something that users want to track.
© 2000 Prentice Hall
Attributes (properties)
Describe the entity’s characteristics.
Entity: Employee
Attributes: LastName, FirstName,
DateOfHire, DateofBirth
E-R Diagram
Page 54
© 2000 Prentice Hall
Types of Attributes
•
•
•
•
•
Required vs. Optional
Simple vs. Composite
Single-valued vs. Multi-valued
Derived (or Calculated)
Identifier
Provide examples of each type.
Identifier
An attribute or attributes that name or
identify entity instances.
Note: Can either be unique or nonunique
Entity: Employee
Identifier 1: SocialSecurityNumber
Identifier 2: LastName, FirstName
Relationship Types
• One-to-One
e.g., one dean to one university
one head-of-state to one country
• One-to-Many
e.g., one painter paints many paintings
one customer places many orders
• Many-to-Many
e.g., students enroll in courses
Determining the Relationship
type: 1:1, 1:M, M:N
Two 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.
1
M
Teaches
Professor
Class
(1,1)
(0,3)
M
N
Enrolls
Student
(3,6)
Class
(0,N)
Expressing Minimum Cardinality:
Hash Marks and Ovals
Hash mark (or vertical line): indicates a
mandatory relationship (min cardinality of
1)
Oval: indicates an optional relationship (min
cardinality of 0)
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, cardinalities in parentheses
notation, and hash marks / ovals
Weak Entities
An entity whose presence in the
database depends (logically) on
another entity.
An entity that is not weak is called a
strong entity.
Examples of Weak Entities
Employee
1:N
Dependent
Building
1:N
Apartment
ID-Dependent Entity
A weak entity in which its identifer
includes the identifer of another entity
Example:
Building (BuildingName)
Apartment (BuildingName, Apartment#)
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
20
Database Systems, 9th Edition
21
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 Constraint
Specifies 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
24
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.
Relationship Degree
Indicates number of entities or
participants associated with a
relationship
• Unary relationship
(aka Recursive relationship)
– Association is maintained within single
entity
• Binary relationship (most common)
– Two entities are associated
• Ternary relationship
– Three entities are associated
Recursive relationships
Occur when an entity type has a
relationship to itself.
Page 53
© 2000 Prentice Hall
Recursive Relationships
As with binary relationships, three
types of relationships are possible:
– 1:1
– 1:M
– M:N
What are examples of each type?
Page 53
Yet Another ERD Notation
Database Systems, 9th Edition
29
Representing a One-to-Many Relationship
in Access
Database Systems, 9th Edition
Evaluating ER Models
• Important to evaluate because it is easier
and cheaper to correct errors early in the
database development process.
• One technique: consider the ER data model
in the context of possible queries that
might be posed to a database with the
structure implied by the model.
• Developing good ER models is about
creating a model that represents the user’s
model of his or her business environment.
Data Modeling
•
•
•
•
•
Interviewing users
documenting requirements
building a data model
building a database prototype
a process of inference (working
backwards)