Data Modeling Using the EntityRelationship ER Data Model

1
Data Modeling Using the Entity-Relationship (ER)
Data Model

• (Based on Chapter 3 in Fundamentals of Database
  Systems
• by Elmasri and Navathe, Ed. 3)

2
Contents

• 1 Using High-Level Conceptual Data Models for
  Database Design2 An Example Database
  Application (COMPANY)3 ER Model
  Concepts 3.1 Entities and Attributes 3.2 Entity
  Types, Value Sets, and Key Attributes 3.3 Relatio
  nships and Relationship Types 3.4 Structural
  Constraints and Roles 3.4 Weak Entity Types4 ER
  Diagrams Notation5 Relationships of Higher
  Degree6 Extended Entity Relationship (EER) Model

3
(No Transcript)
4
2 Example COMPANY Database

• Requirements for the COMPANY Database
• The company is organized into DEPARTMENTs.
• Each department has a name, number, and an
  employee who manages the department.
• We keep track of the start date of the department
  manager.
• A department may have several locations.

5
2 Example COMPANY Database

• Requirements for the COMPANY Database
• Each department controls a number of PROJECTs.
• Each project has a name, number, and is located
  at a single location.

6
2 Example COMPANY Database

• Requirements for the COMPANY Database
• We store each EMPLOYEE's social security number,
  address, salary, sex, and birth date.
• Each employee works for one department but may
  work on several projects.
• We keep track of the number of hours per week
  that an employee currently works on each project.
  
• We also keep track of the direct supervisor of
  each employee.

7
2 Example COMPANY Database

• Requirements for the COMPANY Database
• Each employee may have a number of DEPENDENTs.
• For each dependent, we keep their name, sex,
  birth date, and relationship to the employee.

8
INSERT FIGURE 3.2
9
3 ER Model Concepts

• Entities and Attributes
• Entity Types, Value Sets, and Key Attributes
• Relationships and Relationship Types
• Structural Constraints and Roles
• Weak Entity Types

10
Summary of ER diagram notation
11
Summary of ER diagram notation
12
Summary of ER diagram notation
13
Entities

• An entity is a thing in the real world with an
  independent existence(conceptual or physical).
• Entities are specific objects or things in the
  mini-world that are represented in the database
• for example the EMPLOYEE John Smith, the Research
  DEPARTMENT, the ProductX PROJECT.

14
INSERT FIGURE 3.3
15
Attributes

• Attributes are properties used to describe an
  entity
• for example an EMPLOYEE entity may have a Name,
  SSN, Address, Sex, BirthDate.

16
Particular Entities

• A particular entity (specific entity) will have
  a value for each of its attributes
• for example a specific employee entity may have
• Name'John Smith',
• SSN'123456789',
• Address'731 Fondren, Houston, TX',
• Sex'M',
• BirthDate'09-JAN-55'.

17
Types of Attributes

• Simple versus Composite
• Single-value versus Multi-valued
• Stored versus Derived

18
Simple Attributes

• Each entity has a single atomic value for the
  attribute
• for example SSN or Sex.

19
Composite Attributes

• The attribute may be composed of several
  components
• for example
• Address(Apt, House, Street, City, State,
  ZipCode, Country) or
• Name(FirstName, MiddleName, LastName).
• Composition may form a hierarchy where some
  components are themselves composite.

20
INSERT FIGURE 3.4
21
Single-valued Attributes

• Most attributes have a single value for a
  particular entity such attributes are called
  single-valued.
• For example, Age is a single-valued attribute of
  person.

22
Multi-valued Attributes

• An entity may have multiple values for that
  attribute
• for example
• Color of a CAR or PreviousDegrees of a STUDENT.
• Denoted as Color or PreviousDegrees.

23
Stored VS. Derived Attributes

• In some cases two (or more) attribute values are
  related.
• Some attribute values can be derived from related
  entities.
• For example,
• The value of Age can be determined from the
  current date and the value of that persons
  BirthDate.

24
Null Values

• In some case a particular entity may not have an
  applicable value for an attributes.
• For such situations, a special value called null
  is created.

25
Complex Attributes

• In general, composite and multi-valued attributes
  may be nested arbitrarily to any number of levels
  although this is rare.
• For example, PreviousDegrees of a STUDENT is a
  composite multi-valued attribute denoted by
  PreviousDegrees(College, Year, Degree, Field).

26
Entity Types

• Entities with the same basic attributes are
  grouped or typed into an entity type.
• For example, the EMPLOYEE entity type or the
  PROJECT entity type.

27
Entity Sets

• The collection of all entities of a particular
  entity type in the database at any point in time
  is called an entities set.
• The entities set is usually referred to using the
  same name as the entity type.

28
INSERT FIGURE 3.6
29
An Entity Type

• An entity type describes the schema or intension
  for a set of entities that share the same
  structure.
• The collection of entities of a particular entity
  type are grouped into an entity set, which is
  also called the extension of the entity type.

30
Key Attributes of an Entities Type

• An attribute of an entity type for which each
  entity must have a unique value is called a key
  attribute of the entity type.
• For example, SSN of EMPLOYEE.

31
Key Attributes of an Entities Type

• A key attribute may be composite.
• For example, VehicleRegistrationNumber is a key
  of the CAR entity type with components (Number,
  State).

32
Key Attributes of an Entities Type

• An entity type may have more than one key.
• For example, the CAR entity type may have two
  keys
• VehicleIdentificationNumber and
• VehicleRegistrationNumber(Number, State).

33
Value Sets (Domains) of Attributes

• Each simple attributes of an entity type is
  associated with a value set (or domain of
  values), which specifies the set of values that
  may be assigned to that attribute for each
  individual entity.

34
INSERT FIGURE 3.7
35
INSERT FIGURE 3.8
36
Relationships

• A relationship relates two or more distinct
  entities with a specific meaning
• For example,
• EMPLOYEE John Smith works on the ProductX
  PROJECT or
• EMPLOYEE Franklin Wong manages the Research
  DEPARTMENT.

37
Relationship Types

• Relationships of the same type are grouped or
  typed into a relationship type.
• For example,
• the WORKS_ON relationship type in which EMPLOYEEs
  and PROJECTs participate, or
• the MANAGES relationship type in which EMPLOYEEs
  and DEPARTMENTs participate.

38
Relationship Types

• 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.

39
Degree of a Relationship Type

• The degree of a relationship type is the number
  of participating entity types.
• Both MANAGES and WORKS_ON are binary
  relationships.

40
Relationship types

• More than one relationship type can exist with
  the same participating entity types
• for example, MANAGES and WORKS_FOR are distinct
  relationships between EMPLOYEE and DEPARTMENT
  participate.

41
INSERT FIGURE 3.9
42
Relationships of Higher Degree

• Relationship types of degree 2 are called binary
• Relationship types of degree 3 are called ternary
  and of degree n are called n-ary
• In general, an n-ary relationship is not
  equivalent to n binary relationships

43
INSERT FIGURE 3.10
44
Roles Names

• Each entity type that participates in a
  relationship type plays a particular role in the
  relationship.
• The role name signifies the role that a
  participating entity from the entity type plays
  in each relationship instance, and helps to
  explain what the relationship means.

45
Roles Name

• For example,
• in the WORK_FOR relationship type,
• EMPLOYEE plays the role of employee or worker
  and
• DEPARTMENT plays the role of department or
  employer.

46
Recursive Relationships

• A relationship can relate two entities of the
  same entity type
• for example,
• a SUPERVISION relationship type relates one
  EMPLOYEE (in the role of supervisee ) to another
  EMPLOYEE (in the role of supervisor ).
• This is called a recursive relationship type.

47
INSERT FIGURE 3.11
48
Structural constraints on relationships

• Cardinality ratio (of a binary relationship)
• 11, 1N, N1, or MN.
• Participation constraint (on each participating
  entity type)
• total (called existence dependency ) or
• partial.

49
Cardinality ratios for Binary Relationships.

• The cardinality ratio for a binary relationship
  specifies the number of relationship instances
  that an entity can participate in.
• The possible cardinality ratios for binary
  relationship types are 11, 1N, N1, MN.

50
INSERT FIGURE 3.12
51
INSERT FIGURE 3.13
52
Participation Constraints

• The participation constraint specifies whether
  the existence of an entity depends on its being
  related to another entity via the relationship
  type.
• There are two types of participation constraints
  total and partial.

53
Relationships as Attributes.

• A relationship type can have attributes
• for example,
• HoursPerWeek of WORKS_ON
• its value for each relationship instance
  describes the number of hours per week that an
  EMPLOYEE works on a PROJECT.

54
Weak Entity Types

• An entity type that does not have a key
  attribute.
• A weak entity type must participate in an
  identifying relationship type with an owner or
  identifying entity type.

55
Weak Entity Types

• Entities are identified by the combination of
• A partial key of the weak entity type
• The particular entity they are related to in the
  identifying entity type

56
Example

• Suppose that a DEPENDENT entity is identified by
  the dependent's first name and birthdate, and
  the specific EMPLOYEE that the dependent is
  related to.
• DEPENDENT is a weak entity type with EMPLOYEE as
  its identifying entity type via the identifying
  relationship type DEPENDENT_OF.

57
INSERT FIGURE 3.14
58
Alternative (min, max) notation for relationship
structural constraints

• Specified on each participation of an entity
  type E in a relationship type R.
• Specifies that each entity e in E participates in
  at least min and at most max relationship
  instances in R.
• Default(no constraint) min0, maxn.
• Must have minltmax, mingt0, maxgt1.
• Derived from the mini-world constraints.

59
Examples (a)

• A department has exactly one manager and an
  employee can manage at most one department.
• Specify (1,1) for participation of DEPARTMENT in
  MANAGES
• Specify (0,1) for participation of EMPLOYEE in
  MANAGES

60
Examples (b)

• An employee can work for exactly one department
  but a department can have any number of
  employees.
• Specify (1,1) for participation of EMPLOYEE in
  WORKS_FOR
• Specify (0,n) for participation of DEPARTMENT in
  WORKS_FOR

61
INSERT FIGURE 3.15
62
ER-to-Relational Mapping

• Step 1
• regular entity type mapped to a relation one key
  of the entity type chosen as primary key for the
  relation.
• Step 2
• For weak entity type, include the key
  attribute(s) of the owner relation Primary key
  is combination of owner key attributes and
  partial key of weak entity type.

63
ER-to-Relational Mapping

• Step 3
• Each binary 11 relationship type mapped to a
  foreign key from one relation referring to other
  relation.
• Step 4
• Each binary 1N relationship type mapped to a
  foreign key in relation at N-side referring to
  relation at 1-side.

64
ER-to-Relational Mapping

• Step 5
• Each binary MN relationship type mapped to a
  relation whose primary key includes the keys of
  both participating relations.
• Step 6
• Each multi-valued attribute mapped to a relation
  R that includes the key of the owner relation.

65
ER-to-Relational Mapping

• Step 7
• Each n-ary relationship mapped to a relation that
  includes the keys of all participating relations.

66
Notes on ER-to-Relational Mapping

• Composite attributes represented by their simple
  components.
• A separate relation is created for each
  multi-valued attribute.

67
Notes on ER-to-Relational Mapping

• Relationships in ER are mapped to foreign key
  attributes.
• A single foreign key needed for 11 or 1N
  relationships
• A extra relation with two foreign keys needed for
  binary MN relationship.
• For n-ary relationship, ngt2, we need an extra
  relation with n foreign keys.

68
Notes on ER-to-Relational Mapping

• EQUIJOIN operations are needed to materialize the
  relationships by combining related tuples
• A single EQUIJOIN needed to materialize 11 or
  1N relationships.
• Two EQUIJOINS needed to materialize binary MN
  relationship
• N EQUIJOINS needed to materialize the full n-ary
  relationship.

69
Notes on ER-to-Relational Mapping

• Primary key of weak entity type includes key of
  owner relation.
• Primary key of relation representing an n-ary
  relationship determined from the structural
  participation constraints.