Data Modeling Using the EntityRelationship Model

1
Data Modeling Using the Entity-Relationship
Model 1 Data Models A data model is a
collection of concepts that can be used to
describe the 'structure of a database' (i.e. data
types, relationships, and constraints that should
hold on the data). Most data models also
include a set of basic operations for specifying
retrievals and updates on the database. Thus, one
speaks of a relational DBMS, a network DBMS, and
so on, depending on the type of data model a DBMS
supports. In general, a DBMS supports only one
data model as a practical rather than a
theoretical restriction.
2
2 Categories of Data Models High-level or
conceptual data models provide concepts that are
close to the way many users perceive data.
Low-level or physical data models provide
concepts that describe the details of how data is
stored in the computer. Representational or
implementation data model provide concepts that
may be understood by end users but that are not
too far removed from the way data is organized
within the computer.
3
3 Entity-Relationship Model Entity-Relationship
(ER) model is a popular high-level conceptual
data model. The two main components of the ER
model are the concepts of entity and
relationship. Entities models the objects that
are involved. Relationships model the
connections among the entities.
4
3.1 Entities and Attributes Entities and Their
Attributes. An entity is something that exists as
a particular and discrete unit. An entity may be
an object of physical existence (a car, a house,
etc.) or an object of conceptual existence (a
company, a job, etc.) Each entity has
attributes.
5

• An attribute can be
• composite or simple
• single-valued or multi-valued
• stored or derived.
• Composite Versus Simple (Atomic) Attributes.
• Composite attributes can be divided into smaller
  subparts, which represent more basic attributes.
  Simple attributes can not.
• For example, name may be further divided into
  first name and last name. SSN cannot be further
  divided.

6
Single-valued Versus Multivalued Attributes. In
some cases, an attribute can have a set of values
for the same entity. For example, a CollegeDegree
attribute for a person may have B. Sc., M. Sc.
And Ph.D. Stored Versus Derived Attributes e.g.
Age can be derived from Birthday Null values
7
Complex Attributes Composite and multivalued
attributes can be nested in an arbitrary way. We
can represent arbitrary nesting by grouping
components of a composite attribute between
parenthesis () and separating the components with
commas, and by displaying multivalued attributes
between braces . Such attributes are called
complex attributes. Example AddressPhone(Phone(A
reaCode, PhoneNumber), Address(StreetAddress(Num
ber, Street, ApartmentNumber), City, State, Zip)
)
8
3.2 Entity Types, Entity Sets, Keys, and Values
Sets Entity Types and Entity Sets. An entity type
defines a collection (or set) of entities that
have the same attributes. Each entity type is
describe by its name and attributes. The
collection of all entities of a particular entity
type at any point in time is called an entity
set. An entity type describe the schema
(intension) for a set of entities that sharing
the same structure. The set of entities is the
extension of the entity type.
9
In ER diagrams, an entity type is represented as
a rectangular box enclosing the entity type name.
Attribute names are enclosed in ovals and are
attached to their entity type by straight lines.
Composite attributes are attached to their
component attributes by straight lines.
Multivalued attributes are displayed in double
ovals.
10
Key Attributes of an Entity Type. An entity type
usually has an attribute whose values are
distinct for each individual entity in the
collection. Such an attribute is called a key.
Sometimes, several attributes together form a
key, meaning that the combination of the
attribute values must be distinct for each
entity. Superfluous attributes must not be
included in a key, otherwise, it is a superkey.
In an ER diagram, key attributes are
underlined. An entity type may also have no key,
in which case it is called a weak entity
type. Value Sets (Domains) of Attributes. The
set of values that can be assigned to an
attribute is called the domain of that attribute.
11
4 Relationships, Relationship types, Roles, and
Structural Constraints 4.1 Relationship Types,
Sets and Instances Relationships model the
connections among the entities. Just as entities
are classified into entity types, relationships
that relate the same types of entities and that
have the same meaning are grouped into
relationship types. Let E1, E2, , En be a set of
n entity types, a relation type R is a subset of
the Cartesian product E1 E2 En . A
relationship type R is a set of relationship
instances ri, where each ri associates n
individual entities (e1, e2, , en), and each
entity ej is a member of Ej.
12

• A relationship type R among n entity types
  E1, E2, , En defines a set of associations, or a
  relationship set, among entities from these
  types. Number n is called the degree of the
  relationship type R.
• Each Ej is said to participate in the
  relationship type R.
• Each ei in (e1, e2, , en) is said to
  participate in the relationship instance ri (e1,
  e2, , en).

13
In ER diagrams, relationship types are displayed
as diamond-shaped boxes, which are connected by
straight lines to the rectangular boxes
representing the participating entity types. The
relationship name is displayed in the
diamond-shaped box.
14
(No Transcript)
15
Figure 5.14 An E-R diagram for the Student
Registration System.
16
4.2 Relationship Degree, Role Name, and Recursive
Relationships The degree of a relationship type
is the number of participating entity types. The
role name signifies the role that a participating
entity plays in a relationship instance. In some
cases, the same entity type participates more
than once in a relationship type in different
roles. Such relationship types are called
recursive relationships.
17
Constraints on Relationship Types Cardinality
Ratios for Binary Relationships. The cardinality
ratio specifies the number of relationship
instances that an entity can participate in. The
possible cardinality ratios for binary
relationship types are 11, 1N, N1, and MN.
Cardinality ratios for binary relationships are
display on ER diagrams by displaying 1, M, and N
on the diamonds. Participation Constraints and
Existence Dependencies. The participation
constraint specifies whether the existence of an
entity depends on its being related to another
entity via the relationship type.
18
If a entity e can exist only if it participates
in a relationship instance r, the participation
is called total participation (existence
dependency). Otherwise the participation is
partial. In ER diagrams, total participation is
displayed as a thick line connecting the
participating entity type to the relationship,
whereas partial participation is represented by a
single line. Cardinality ratio and participation
constraints together are referred as the
structural constraints.
19
Weak Entity Types Entity types that do not have
key attributes of their own are called weak
entity types. Entities of a weak entity type are
identified by being related to specific entities
of another entity type in combination with some
of their attribute values. We call this other
entity type the identifying or owner entity type,
and we call the relationship the identifying
relationship of the weak entity type. A weak
entity type normally has a partial key (sometimes
called discriminator), which can uniquely
identify weak entities that are related to the
same owner entity. In ER diagrams, both a weak
entity type and its identifying relationship are
distinguished by surrounding their boxes with
double lines. The partial key is underlined with
a dashed or dotted line.
20
ER Diagrams, Naming Conventions, and Design
Issues 1 Summary of Notation for ER Diagrams 2
Proper Naming of Schema Constructs Entity type
and relationship type names are in uppercase
letters, attribute names are capitalized, and
role names are in lowercase letters. Nouns for
entity type names and verbs for relationship
types. Another naming consideration involves
choosing relationship names to make the ER
diagram readable from left to right and from top
to bottom.
21
3.7.4 Alternative Notations for ER Diagrams (min,
max) e must participate in at least min and at
most max relationship instances in R at any point
of time.