Entity Relationship Diagrams - PowerPoint PPT Presentation

Loading...

PPT – Entity Relationship Diagrams PowerPoint presentation | free to download - id: 52a876-MzgwO



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Entity Relationship Diagrams

Description:

Title: Entity Relationship Diagrams Author: Alan T. Burns Last modified by: p Created Date: 4/24/2003 7:51:57 PM Document presentation format: On-screen Show (4:3) – PowerPoint PPT presentation

Number of Views:381
Avg rating:3.0/5.0
Slides: 40
Provided by: Ala789
Learn more at: http://prasadsawant.files.wordpress.com
Category:

less

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

Title: Entity Relationship Diagrams


1
Entity Relationship Diagrams
  • Mr.Prasad Sawant
  • MIT PUNE

2
Software Analysis Design
  • Description of requirements of users ? data
    modelling, process modelling
  • Data modelling is expressed using a high level
    model such as ENTITY-RELATIONSHIP (ER)
  • The ER Model represented pictorially (ER
    diagrams)
  • ER Model contains detailed descriptions of
  • 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 or business
    rules that hold?

3
Data modelling vs Process modelling
  • Process modelling (i.e. DFD) shows data stores,
    how, where, when data are used or changed in an
    System
  • Data modelling (i.e ER) shows the definition,
    structure, relationship within the data

4
Conceptual Data Modeling and the E-R Diagram
  • Goal
  • Capture as much of the meaning of the data as
    possible
  • A better design that is scalable and easier to
    maintain

5
Introduction to Entity-Relationship (E-R) Modeling
  • Notation uses three main constructs
  • Data entities
  • Attributes
  • Relationships
  • Entity-Relationship (E-R) Diagram
  • A detailed, logical representation of the
    entities, associations and data elements for an
    organization or business

6
Entity-Relationship (E-R) ModelingKey Terms
  • Entity
  • A person, place, object, event or concept in the
    user environment about which the organization
    wishes to maintain data
  • Represented by a rectangle in E-R diagrams
  • Entity Type
  • A collection of entities that share common
    properties or characteristics
  • Attribute
  • A named property or characteristic of an entity
    that is of interest to an organization

7
Entity-Relationship (E-R) ModelingKey Terms
  • Candidate keys and identifiers
  • Each entity type must have an attribute or set of
    attributes that distinguishes one instance from
    other instances of the same type
  • Candidate key
  • Attribute (or combination of attributes) that
    uniquely identifies each instance of an entity
    type

8
Entity-Relationship (E-R) ModelingKey Terms
  • Identifier
  • A candidate key that has been selected as the
    unique identifying characteristic for an entity
    type
  • Selection rules for an identifier
  • Choose a candidate key that will not change its
    value
  • Choose a candidate key that will never be null
  • Avoid using intelligent keys
  • Consider substituting single value surrogate keys
    for large composite keys

9
Notation Guide
  • ENTITY TYPE
  • WEAK ENTITY TYPE
  • RELATIONSHIP TYPE
  • IDENTIFYING RELATIONSHIP TYPE

10
Notation Guide
  • ATTRIBUTE
  • KEY ATTRIBUTE
  • MULTIVALUED ATTRIBUTE
  • DERIVED ATTRIBUTE
  • COMPOSITE ATTRIBUTE

_____
. . .
11
ER Diagram Basics
Relationship
Attributes
12
Entity Sets
  • A collection of similar entities (e.g. all
    employees)
  • All entities in an entity set have the same set
    of attributes
  • Each attribute has a domain
  • Can map entity set to a relation easily

EMPLOYEES
SSN NAME SAL
321-23-3241 Kim 23,000
645-56-7895 Jones 45,000
13
Entity Type
  • Defines set of entities that have the same
    attributes (e.g. EMPLOYEE)
  • Each Entity Type is described by its NAME and
    attributes
  • The Entity Type describes the Schema or
    Intension for a set of entities
  • Collection of all entities of a particular entity
    type at a given point in time is called the
    Entity Set or Extension of an Entity Type
  • Entity Type and Entity Set are customarily
    referred to by the same name

14
Attributes
  • Key Attributes
  • Attribute Types

Notation
15
Key Attributes Identifier
  • Key (or uniqueness) constraints are applied to
    entity types
  • Key attributes values are distinct for each
    individual entity in the entity set
  • A key attribute has its name underlined inside
    the oval
  • Key must hold for every possible extension of the
    entity type
  • Multiple keys are possible

SSN
EMPLOYEE
16
Null Valued Attributes
  • A particular entity may not have an applicable
    value for an attribute
  • Home-Phone Not known if it exists
  • Height Not known at present time
  • Type of Null Values
  • Not Applicable
  • Unknown
  • Missing

17
Composite Vs. Simple Attributes
  • Composite attributes can be divided into smaller
    parts which represent simple attributes with
    independent meaning
  • Simple Attribute Aircraft-Type
  • Complex Attribute Aircraft-Location which is
    comprised of
  • Aircraft-Latitude
  • Aircraft-Longitude
  • Aircraft-Altitude

Notation
There is no formal concept of
composite attribute in the relational model
18
Single Vs. Multivalued Attributes
  • Simple attributes can either be single-valued
  • or multi-valued
  • Single-valued Gender F
  • Notation
  • Multivalued Degree BSc, MInfTech
  • Notation
  • An attribute in the relational model is
    always
  • single valued - Values are atomic!

19
Derived Vs. Stored Attributes
  • Some attribute values can be derived from
  • related attribute values
  • Age Date - B-day
  • Y-Sal 12 M-Sal

Notation
M-sal
Age
B-days
Y-sal
EMPLOYEE
20
Derived Vs. Stored Attributes
  • Some attribute values can be derived from
    attributed values of related entities
  • total-value sum (qty price)

21
Representing Attributes
  • Parenthesis ( ) for composite attributes
  • Brackets for multi-valued attributes
  • Assume a person can have more than one residence
    and each residence can have multiple telephones
  • AddressPhone
  • ( Phone ( AreaCode,PhoneNum ) ,
  • Address (StreetAddresss (Number,
    Street, AptNo), City,State,PostalCode) )

22
Entity-Relationship (E-R) ModelingKey Terms
  • Relationship
  • An association between the instances of one or
    more entity types that is of interest to the
    organization
  • Association indicates that an event has occurred
    or that there is a natural link between entity
    types
  • Relationships are always labeled with verb phrases

23
Cardinality
  • The number of instances of entity B that can be
    associated with each instance of entity A
  • Minimum Cardinality
  • The minimum number of instances of entity B that
    may be associated with each instance of entity A
  • This is also called modality.
  • Maximum Cardinality
  • The maximum number of instances of entity B that
    may be associated with each instance of entity A

24
Naming and Defining Relationships
  • Relationship name is a verb phrase
  • Avoid vague names
  • Guidelines for defining relationships
  • Definition explains what action is being taken
    and why it is important
  • Give examples to clarify the action
  • Optional participation should be explained
  • Explain reasons for any explicit maximum
    cardinality

25
Naming and Defining Relationships
  • Guidelines for defining relationships
  • Explain any restrictions on participation in the
    relationship
  • Explain extent of the history that is kept in the
    relationship
  • Explain whether an entity instance involved in a
    relationship instance can transfer participation
    to another relationship instance

10.25
26
Relationships
  • Relationship Types and Sets
  • Relationship Degree
  • Entity Roles and Recursive Relationships
  • Relationship Constraints
  • Attributes of Relationship Types

27
Relationship Types and Sets
  • A Relationship is an association among two or
    more entities (e.g John works in Pharmacy
    department)
  • A Relationship Type defines the relationship, and
    a Relationship Set represents a set of
    relationship instances
  • A Relationship Type thus defines the structure of
    the Relationship Set
  • Relationship Type and corresponding Set are
    customarily referred to by the same name

28
Relationship Degree
Departments
  • The degree of a relationship type is the number
    of participating entity types
  • 2 entities Binary Relationship
  • 3 entities Ternary Relationship
  • n entities N-ary Relationship
  • Same entity type could participate in
  • multiple relationship types

Binary
Multiple
Employees
Supplier
Project
Ternary
Part
29
Entity Roles
  • Each entity type that
  • participates in a relationship
  • type plays a particular role
  • in the relationship type
  • The role name signifies the
  • role that a participating
  • entity from the entity type
  • plays in each relationship
  • instance, i.e. it explains what
  • the relationship means

30
Recursive Relationships
  • Same entity type can participate more than once
    in the same relationship type under different
    roles
  • Such relationships are called
  • Recursive Relationships

31
Relationship Constraints
  • What are Relationship Constraints ?
  • Constraints on relationships are determined by
    the UoD, which these relationships are describing
  • Constraints on the relationship type limit the
    possible combination of entities that may
    participate in the corresponding relationship set

32
Kinds of Constraints
  • What kind of constraints can be defined in the ER
    Model?
  • Cardinality Constraints
  • Participation Constraints
  • Together called Structural Constraints

Constraints are represented by specific notation
in the ER diagram
33
Possible Cardinality Ratios
  • The Cardinality Ratio for a binary relationship
    specifies the number of relationship instances
    that an entity can participate in
  • Works-In is a binary relationship
  • Participating entities are
  • DEPARTMENT EMPLOYEE
  • One department can have
  • Many employees -
  • Cardinality Ratio is 1 N

34
Possible Cardinality Ratios
  • 1to-1 (1 1)
  • Both entities can
  • participate in only one
  • relationship instance
  • 1-to-Many, Many-to-1
  • (1 N, N 1)
  • One entity can
  • participate in many
  • relationship instances
  • Many-to-Many (N M)
  • Both entities can participate in
  • many relationship instance

. . .
. . . .
1- to - Many
35
Example Cardinality Constraints
How many Employees can work in a Department?
One employee can work in only one department How
many Employees can be employed by a Department?
One department can employ many employees How
many managers can a department have? One
department can have only one manager How many
departments can an employee manage? One
employee can have manage only one department
36
Representing Cardinality
One employee can work in only one
department One department can employ many
employees One department can have only one
manager One employee can manage only one
department
37
Existence Dependency
  • Existence dependency indicates whether the
    existence of an entity depends on its
    relationship to another entity via the
    relationship type
  • Every employee must work for
  • a department - EMPLOYEE is
  • existentially dependent on DEPARTMENT via the
    Works In relationship type

38
Kinds of participating constraints
  • TOTAL Participation (Existence Dependency)
  • Constraint Every employee must work for a
    department
  • PARTIAL Participation
  • Constraint Not every employee is a manager

39
Representing Participation
Every employee must work for a department Every
department must have a manager Every department
must have employees Not every employee is a
manager
About PowerShow.com