chapter 3
Download
Skip this Video
Download Presentation
Chapter 3

Loading in 2 Seconds...

play fullscreen
1 / 46

The Entity-Relationship Model - PowerPoint PPT Presentation


  • 205 Views
  • Uploaded on

Chapter 3. The Entity-Relationship Model. ER Model. Conceptual Design answers: What are the entities and relationships? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules? ER Model Concepts:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Entity-Relationship Model' - Jims


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
chapter 3

Chapter 3

The Entity-Relationship Model

er model
ER Model
  • Conceptual Design answers:
    • What are the entities and relationships?
    • What information about these entities and relationships should we store in the database?
    • What are the integrity constraints or business rules?
  • ER Model Concepts:
    • Entity types
    • Relationship types
    • Attributes
diagrammatic notation
Diagrammatic notation
  • Use of UML class diagram for displaying ER concepts.
  • UML class represents an entity type.
    • First section include entity type name.
    • Second section lists attributes.
    • Third section is not displayed (or used for derived attributes).
  • Relation types represented as lines connecting entity types (classes).
  • Some UML terminology differs from ER terminology (discussed later).
entity type
Entity Type
  • Entity type
    • Define a group of objects with same properties or attributes.
    • Examples: EMPLOYEE, CUSTOMER, SUPPLIER entity types.
  • Entity occurrence (or entity instance)
    • Uniquely identifiable object of an entity type.
    • Examples: Employee John Doe, Customer Mike Jordan, Supplier Office Depot.
  • Entity set is the set of entity occurrences.
er diagram of staff and branch entity types
ER diagram of Staff and Branch entity types

An entity type is displayed in a rectangular box.

relationship types
Relationship Types
  • Relationship type
    • Define a set of meaningful associations among entity types.
    • Examples: A Branch HAS some Staff, An Employee WORKS ON a Project.
  • Relationship occurrence
    • Uniquely identifiable association, which includes one occurrence from each participating entity type.
  • Relationship set is the set of relationship occurrences (current state of a relationship).
er diagram of branch has staff relationship
ER diagram of Branch Has Staff relationship

Another notation:

Has

Branch

Staff

relationship types1
Relationship Types
  • Degree of a Relationship
    • Number of participating entities in relationship.
  • Relationship of degree :
    • two is binary
    • three is ternary
    • four is quaternary.
relationship types2
Relationship Types
  • Recursive Relationship
    • A relationship type where same entity type participates more than once in different roles.
  • Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship.
attributes
Attributes
  • Attribute
    • Property of an entity or a relationship type.
  • Attribute Domain
    • Set of allowable values for one or more attributes.
  • Simple Attribute (or single-valued Attribute)
    • An entity has a single atomic value for the attribute.
    • Example: Employee with SSN 123-45-6789
  • Composite Attribute
    • Attribute composed of multiple components.
    • Examples: Address, Name.
    • It can be nested.
attributes1
Attributes
  • Multi-valued Attribute
    • Attribute that holds multiple values for each occurrence of an entity type.
    • Example: Previous jobs for an employee.
  • Derived Attribute
    • Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type.
    • Example: Age derived from DOB and Today’s date.
slide17
Keys
  • Candidate Key
    • Minimal set of attributes that uniquely identifies each occurrence of an entity type.
  • Primary Key
    • Candidate key selected to uniquely identify each occurrence of an entity type.
  • Composite Key
    • A candidate key that consists of two or more attributes.
entity type1
Entity Type
  • Strong Entity Type
    • Entity type that is not existence-dependent on some other entity type.
  • Weak Entity Type
    • Entity type that is existence-dependent on some other entity type.
    • It does have a key attribute.
    • Identified by a partial key of the weak entity type and by the primary of the strong entity type they are related to.
structural constraints
Structural Constraints
  • Main type of constraint on relationships is called multiplicity.
  • Multiplicity - number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.
  • Represents policies (called business rules) established by user or company.
structural constraints1
Structural Constraints
  • The most common degree for relationships is binary.
  • Binary relationships are generally referred to as being:
    • one-to-one (1:1)
    • one-to-many (1:* or 1:M)
    • many-to-many (*:* or M:N)
semantic net of newspaper advertises propertyforrent relationship type
Semantic net of Newspaper Advertises PropertyForRent relationship type

Many-to-many (*:*) relationship

structural constraints2
Structural Constraints
  • Multiplicity for Complex Relationships
    • Number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed for the other participating entity types.
multiplicity of ternary registers relationship
Multiplicity of ternary Registers relationship

A staff/branch value pair registers zero or more clients

structural constraints3
Structural Constraints
  • Multiplicity actually consists of two types of restrictions on relationships: cardinality and participation.
  • Cardinality
    • Describes maximum number of possible relationship occurrences for an entity participating in a given relationship type.
  • Participation
    • Determines whether all (total) or only some (partial) entity occurrences participate in a relationship.
problems with er models
Problems with ER Models
  • Problems may arise when designing a conceptual data model called connection traps.
  • Often due to a misinterpretation of the meaning of certain relationships.
  • Two main types of connection traps are called fan traps and chasmtraps.
problems with er models1
Problems with ER Models
  • Fan Trap
    • Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous.
  • Chasm Trap
    • Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences.
an example of a fan trap
An Example of a Fan Trap

At which branch office does staff number SG37 work?

an example of a chasm trap
An Example of a Chasm Trap

At which branch office is property PA14 available?

entity vs attribute
Entity vs. Attribute
  • Should addressbe an attribute of Employee or an entity (connected to Employee by a relationship)?
  • Depends upon the use we want to make of address information, the physical data model, and the semantics of the data:
      • If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued).
      • If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
entity vs attribute1

Employee

ssn

name

dob

Employee

ssn

name

dob

Entity vs. Attribute

from

to

  • Works_In1 does not allow an employee to work in a department for two or more periods.
  • Similar to the problem of wanting to record several addresses for an employee.
  • Solution: Place periods in a separate entity type.

Department

did

dname

budget

0:*

1:1

Works_In1

Department

did

dname

budget

0:*

1:1

Works_In2

1:*

Duration

from

to

entity vs relationship

Employee

ssn

name

dob

Employee

ssn

name

dob

Entity vs. Relationship

since

dbudget

  • First ER diagram OK if a manager gets a separate budget for each dept.
  • What if a manager gets a budget that covers all managed depts?
    • Redundancy of dbudget, which is stored for each dept managed by the manager.
    • Misleading: suggests dbudget tied to managed dept.
  • Solution: Create a separate entity type Mgr_Appts.

Department

did

dname

budget

1:1

0:1

Manages1

Department

did

dname

budget

1:1

0:1

Manages2

0:1

Mgr_Appts

apptnum

since

dbudget

data modeling tools
Data Modeling Tools
  • Several popular tools exist for conceptual design and mapping to relational schema.
    • Examples: ERWin, S-Designer, ER-Studio, MS Visio.
  • Good for documenting database design.
    • But, it may not follow ER notation.
  • Original ER model did not support other concepts
    • Such as specializations/generalizations and aggregations.
    • They are discussed in next chapter.
ad