Entity Relationship Modeling - PowerPoint PPT Presentation

1 / 25
About This Presentation
Title:

Entity Relationship Modeling

Description:

Drawing Entity Sets. The term 'entity' and 'entity set' are often confused. ... For a binary relationship set R between entity sets A and B, the cardinality ... – PowerPoint PPT presentation

Number of Views:24
Avg rating:3.0/5.0
Slides: 26
Provided by: susanda
Category:

less

Transcript and Presenter's Notes

Title: 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
  • A many-to-1 relationship is modeled by placing an
    arrow in the many ? one direction or M1.
  • For example, consider a relationship between
    Employees and Departments in which a dept is
    managed by a single emp.

15
Modeling Many-to-1 relationships
  • Explicit Notation

dname
budget
did
1
Departments
M
16
Labeled Edges
  • Sometimes a relationship connects the same
    entity, and the role of the entity becomes
    unclear. For example, the following is quite
    common in industry
  • This often indicates the need for recursive
    queries.

17
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
18
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
19
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)
  •  

20
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
21
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
...
22
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
Dependents
Policy
Employees
23
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
24
Student
Project
What is the difference between these
two representations?
Student
Project
Advisor
SnrAdv
25
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.
Write a Comment
User Comments (0)
About PowerShow.com