Entity Relationship Modeling

1
Entity Relationship Modeling
2
Overview of Database Design

• Conceptual design ER Model is used at this
  stage, which is then translated to a relational
  schema.
• Schema Refinement (Normalization) Check
  relational schema for redundancies and related
  anomalies.
• Physical Database Design and Tuning Consider
  typical workloads and further refine the database
  design.

3
Conceptual Design

• What are the entities and relationships in the
  enterprise?
• What information about these entities and
  relationships should we store in the database?
• What are the integrity constraints that hold?
• Represent this information pictorially in ER
  diagrams, then map ER diagram into a relational
  schema.

4
ER Diagrams -- the basics

• Attributes things typically used as column
  names, e.g. Name, Age, Height. They are drawn as
  ovals
• Entities real world objects, e.g. Students,
  Courses, Routes, Climbers. Entity sets are drawn
  as rectangles
• Relationships relationships between entities,
  e.g. a student enrolls in a course, a climber
  climbs a route, etc. Relationship sets are drawn
  as diamonds

5
Drawing Entity Sets

• The term entity and entity set are often
  confused. Boxes represent sets of entities.
• To draw an entity set we connect it with its
  attributes and indicate the key by underlining
  it

Routes
6
Drawing Relationships

• Relationship sets are represented by diamonds.
• They connect the entities they relate, and may
  additionally have attributes.

Climbers
Routes
7
The whole diagram

• Note that lines connect entities to attributes
  and relationships to entities and to attributes.
  They do not connect attributes to attributes,
  entities to entities or relationships to
  relationships.

RId
CId
CName
RName
Grade
Skill
Age
Rating
Height
8
Characteristics of Attributes

• Composite Divided into smaller subparts which
  represent more basic attributes with independent
  meanings
• Atomic Not divisible
• Single-valued A single value for a particular
  entity (e.g., age of person)
• Multi-valued Attribute with a set of values for
  the same entity (e.g., degrees) A person may
  have 2 or more degrees
•  

9
Characteristics of Attributes

•       Stored DOB
•       Derived Value of an attribute can be
  derived from other attributes (e.g., age from
  DOB)
•       Null When a value is not applicable for
  an attribute of a particular entity (e.g., apt)
  or values exists but is missing (e.g., weight of
  a person) or it is not known whether the
  attribute value exists (e.g., phone) 

10
Degree of Relationship

•       Degree of relationship of participating
  entity sets in a relationship
• Binary Degree two (e.g., Employee works for
  Department)
• Ternary Degree three
• N-ary
•       Constraints on relationship sets
  Cardinality ratio and participation
•  

11
Degree of Relationship

• Cardinality ratio Expresses the of entities to
  which another entity can be associated via a
  relationship set
•     11Employee manages department
•     1NMothers having children
•     N1Children having mothers
•     MNStudents enrolled courses
•  
• For a binary relationship set R between entity
  sets A and B, the cardinality ratio must be one
  of the following

12
Binary Relationships

• 11 An entity in A is associated with at most
  one entity in B and an entity in B is associated
  with at most one entity in A.
• 1M An entity in A is associated with any number
  of entities in B. An entity in B, however, can be
  associated with at most one entity in A.
• MM An entity in A is associated with any number
  of entities in B and an entity in B is associated
  with any number of entities in A.

13
Binary Relationships

• Relationships involving two entity sets (binary
  relationships) can be classified as 1-to-1,
  1-to-Many (Many-to-1) or Many-to-Many.

1-to-1
1-to Many
14
Modeling Many-to-1 relationships
dname
budget
did
1
Departments
M
15
Recursive Relationships

•   Role Function that an entity plays in a
  relationship is called its role
• Implicit Role All the participating entity sets
  in a relationship set are distinct. Works_For
  relationship set, Employee plays the role of
  employee or worker and Department plays the role
  of department or employer
•    

name
ssn
Departments
Employees
Works_for
1
N
16
Recursive Relationships

• Explicit Role all the participating entity sets
  in a relationship are not distinct
•     Recursive relationship Same entity set
  participates more than once in a relationship in
  different roles
•     Supervision relationship type relates an
  employee to a supervisor, where both employee and
  supervisor entities are the members of the same
  Employee entity set

1
Supervisor
Supervision
Employee
Supervisee
N
17
Participation Constraints

•    Participation Specifies whether the
  existence of an entity depends on its being
  related to another entity via the relationship
  type
•   Two types total and partial     
•   Total Every employee must work for a
  department, then every employee in Employee
  entities must be related to a department entity
  via Works_For relationship (represented by double
  line/thick line)
•  

18
Participation Constraints
name
ssn
Departments

•  
•  Partial We do not expect every employee to
  manage a department--Some employee entities are
  related to a department entity via Manages but
  not necessarily all
• Structural constraint cardinality ratio and
  participation constraints

Employees
Manages
1
1
19
Participation Constraints

• So far, a many relationship is interpreted as
  0 or more, and a 1 as 0 or 1. A thick line
  excludes the 0

Employees
Works In
Departments
M
1
...
Id
DName
Name
...
20
Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another (owner)
  entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set (1
  owner, many weak entities).
• Weak entity set must have total participation in
  this identifying relationship set.

cost
pname
age
ssn
1
M
Dependents
Policy
Employees
21
N-ary relationships

• So far, all of the examples we have used deal
  only with binary relationships. However,
  relationship sets can relate an arbitrary number
  of entity sets.

Student
Project
22
Student
Project
What is the difference between these
two representations?
Student
Project
Advisor
SnrAdv
23
Other constraints

• There are numerous other constraints that one
  might want to be able to state.
• Some schema design (CASE) tools allow you to
  state more than we have shown, but most must be
  translated to business rules.

24
Summary

• ER is a useful technique for designing databases.
  
• A number of CASE tools exist built around ER
  (e.g. ERWin, PowerBuilder, etc.). These
  translate the design automatically into
  Sybase/Oracle/ DDL, some into XML.
• The tools use a notation which differs slightly
  from that presented in class, which is better
  suited to graphical displays.