Chapter 3 The EntityRelationship Model - PowerPoint PPT Presentation

Loading...

PPT – Chapter 3 The EntityRelationship Model PowerPoint presentation | free to download - id: 9828b-MzkxY



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Chapter 3 The EntityRelationship Model

Description:

SDLC Revisited Data Modeling is an Analysis Activity. Project Identification. and Selection ... The relationship type is modeled as the diamond and lines ... – PowerPoint PPT presentation

Number of Views:89
Avg rating:3.0/5.0
Slides: 49
Provided by: johnh101
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Chapter 3 The EntityRelationship Model


1
Chapter 3The Entity-Relationship Model
  • Conceptual Data Modeling

2
SDLC Revisited Data Modeling is an Analysis
Activity
Project Identification and Selection
Purpose thorough analysis Deliverable
functional system specifications
Project Initiation and Planning
Analysis
Logical Design
Physical Design
Database activity conceptual data
modeling communication between DB designer user
Implementation
Maintenance
3
Business Rules
  • Statements that define or constrain some aspect
    of the business
  • Assert business structure
  • Control/influence business behavior
  • Expressed in terms familiar to end users
  • Automated through DBMS software

4
A Good Business Rule is
  • Declarative what, not how
  • Precise clear, agreed-upon meaning
  • Atomic one statement
  • Consistent internally and externally
  • Expressible structured, natural language
  • Distinct non-redundant
  • Business-oriented understood by business people

5
Sample E-R Diagram
6
E-R Model Constructs
  • Entity
  • Person customer, vendor, supplier
  • Place location, city, region, territory
  • Object vehicle, monitor, part
  • Event order, registration, renewal
  • Concept expense, flight, project
  • Entity Type versus Entity Instance
  • Entity Type versus System Input, Output, or User

7
Basic E-R Notation
A special entity that is also a relationship
Entity symbols
Attribute symbols
Relationship symbols
8
What Should an Entity Be?
  • SHOULD BE
  • An object that will have many instances in the
    database
  • An object that will be composed of multiple
    attributes
  • An object that we are trying to model
  • SHOULD NOT BE
  • A user of the database system
  • An output of the database system (e.g. a report)

9
Inappropriate entities
10
Attributes
  • Attribute - property or characteristic of an
    entity type
  • Classifications of attributes
  • Simple versus Composite Attribute
  • Single-Valued versus Multivalued Attribute
  • Derived Attributes
  • Identifier Attributes

11
A composite attribute
12
Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
13
E-R Model Constructs
  • Identifier or Key
  • An attribute (or combination of attributes) that
    uniquely identifies individual instances of an
    entity type.
  • Simple Key versus Composite Key
  • Candidate Key
  • an attribute that could be a keysatisfies the
    requirements for being a key

14
Simple key attribute
15
Composite key attribute
16
Criteria for Selecting Identifiers (Keys)
  • Will not change in value.
  • Will not be null.
  • No intelligent identifiers
  • e.g. containing locations or people that might
    change
  • Substitute new, simple keys for long, composite
    keys.

17
Strong vs. Weak Entities, andIdentifying
Relationships
  • Strong entities
  • exist independently of other types of entities
  • has its own unique identifier
  • represented with single-line rectangle
  • Weak entity
  • dependent on a strong entitycannot exist on its
    own
  • Does not have a unique identifier
  • represented with double-line rectangle
  • Identifying relationship
  • links strong entities to weak entities
  • represented with double line diamond

18
Example of a weak entity
19
Basic E-R Notation
20
Relationships
  • Relationship Types vs. Relationship Instances
  • The relationship type is modeled as the diamond
    and lines between entity types
  • Relationships can have attributes
  • These describe features pertaining to the
    association between the entities in the
    relationship
  • Two entities can have more than one type of
    relationship between them (multiple relationships)

21
Relationship type (Completes)
22
Relationship instances
23
Attribute on a relationship
24
Associative Entity
  • Its an Entity Its a Relationship
  • When should a relationship be an Associative
    Entity?
  • All relationships involved are many
  • Associative entity has independent meaning
  • Has one or more non-key attributes
  • Independent relationships

25
An associative entity (CERTIFICATE)
26
Degree of Relationships
  • Degree of a Relationship - number of entity types
    that participate in it.
  • Unary (or Recursive) Relationship
  • Binary
  • Ternary

27
Degree of relationships
28
Binary relationships
29
Unary relationships
30
A unary relationship with an attribute
This has a many-to-many relationship
Represents a bill-of -materials structure
31
An Associative EntityBill of Materials Structure
32
Ternary relationships
33
A ternary relationship as an associative entity
34
Multivalued attribute vs. relationship.
Alternative approaches
35
Cardinality of Relationships
  • One to One
  • Each entity in the relationship will have exactly
    one related entity
  • One to Many
  • An entity on one side of the relationship can
    have many related entities, but an entity on the
    other side will have a maximum of one related
    entity
  • Many to Many
  • Entities on both sides of the relationship can
    have many related entities on the other side

36
Cardinality Constraints
  • The number of instances of one entity that can or
    must be associated with each instance of another
    entity
  • Minimum Cardinality
  • If zero, then optional
  • If one or more, then mandatory
  • Maximum Cardinality
  • Mandatory One - when min max both 1

37
Cardinality notation
38
Cardinality constraints
Mandatory cardinalities
39
Cardinality constraints
One optional, one mandatory cardinality
40
Optional cardinalities with unary degree,
one-to-one relationship
41
Cardinality constraints in a ternary relationship
42
Modeling Time-Dependent Data
  • Time Stamps

43
Multiple Relationships
  • More than one relationship between the same
    entity types

44
Multiple relationship with fixed cardinality
constraint
45
E-R Diagram for Pine Valley
46
Pine Valley Furniture User View 1 Orders for
customers
47
Pine Valley FurnitureUser View 2 Orders for
products
48
Synonyms you should know
Entity relation table Attribute
column Instance row
columns
table
rows
About PowerShow.com