Chapter 3
This presentation is the property of its rightful owner.
Sponsored Links
1 / 46

Chapter 3 PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on
  • Presentation posted in: General

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:

Download Presentation

Chapter 3

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).


Semantic net of has relationship type

Semantic net of Has relationship type


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.


Binary and ternary relationships

Binary and ternary relationships

Binary:

Ternary:


Quaternary relationship called arranges

Quaternary relationship called Arranges


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.


Recursive relationship called supervises with role names

Recursive relationship called Supervises with role names


Entities associated through two distinct relationships with role names

Entities associated through two distinct relationships with role names


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.


The entity relationship model

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.


Er diagram of staff and branch entities and their attributes

ER diagram of Staff and Branch entities and their 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.


Strong entity type called client and weak entity type called preference

Strong entity type called Client and weak entity type called Preference


Relationship called advertises with attributes

Relationship called Advertises with attributes


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 staff manages branch relationship type

Semantic net of Staff Manages Branch relationship type

One-to-one (1:1) relationship


Multiplicity of staff manages branch 1 1 relationship

Multiplicity of Staff Manages Branch (1:1) relationship

1:1


Semantic net of staff oversees propertyforrent relationship type

Semantic net of Staff Oversees PropertyForRent relationship type

One-to-many (1:*) relationship


Multiplicity of staff oversees propertyforrent 1 relationship type

Multiplicity of Staff Oversees PropertyForRent (1:*) relationship type

1:M


Semantic net of newspaper advertises propertyforrent relationship type

Semantic net of Newspaper Advertises PropertyForRent relationship type

Many-to-many (*:*) relationship


Multiplicity of newspaper advertises propertyforrent relationship

Multiplicity of Newspaper Advertises PropertyForRent (*:*) 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.


Semantic net of ternary registers relationship with values for staff and branch entities fixed

Semantic net of ternary Registers relationship with values for Staff and Branch entities fixed


Multiplicity of ternary registers relationship

Multiplicity of ternary Registers relationship

A staff/branch value pair registers zero or more clients


Summary of multiplicity constraints

Summary of multiplicity constraints


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.


Multiplicity as cardinality and participation constraints

Multiplicity as cardinality and participation constraints


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?


Restructuring er model to remove fan trap

Restructuring ER model to remove Fan Trap

SG37 works at branch B003.


An example of a chasm trap

An Example of a Chasm Trap

At which branch office is property PA14 available?


Er model restructured to remove chasm trap

ER Model restructured to remove Chasm Trap


Semantic net of restructured er model with chasm trap removed

Semantic Net of Restructured ER Model with Chasm Trap Removed


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.


  • Login