Database modeling using the entity relationship model continued
This presentation is the property of its rightful owner.
Sponsored Links
1 / 23

Database Modeling Using the Entity-Relationship Model (Continued) PowerPoint PPT Presentation


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

Database Modeling Using the Entity-Relationship Model (Continued). Usage of Weak Entities. Weak Entity can be used when converting a n-ary relationship to a number of binary relationships. The basic algorithm can be found in the previous lecture note.

Download Presentation

Database Modeling Using the Entity-Relationship Model (Continued)

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


Database modeling using the entity relationship model continued

Database Modeling Using the Entity-Relationship Model(Continued)


Usage of weak entities

Usage of Weak Entities

  • Weak Entity can be used when converting a n-ary relationship to a number of binary relationships.

  • The basic algorithm can be found in the previous lecture note.

  • The additional ENTITY CLASS is actually a weak entity.


An example of ternary relationship

SName

ProjName

Quantity

SUPPLY

PartNo

An Example of Ternary Relationship

SUPPLIER

PROJECT

PART


Converted e r diagram

SName

ProjName

Quantity

SUPPLY

SP

SS

SPJ

PartNo

Converted E-R Diagram

N

1

N

1

SUPPLIER

PROJECT

N

1

PART


What if defining a cardinality constraint

SName

ProjName

Quantity

SUPPLY

PartNo

What if defining a cardinality constraint

1

M

SUPPLIER

PROJECT

N

PART


Converted e r diagram1

SName

ProjName

Quantity

SUPPLY

SP

SPJ

PartNo

Converted E-R Diagram

N

1

1

1

SUPPLIER

PROJECT

SS

N

1

PART


Design principles

Design Principles

  • Faithfulness

  • Avoiding redundancy

  • Keep it simple

  • Picking the right kind of element


Faithfulness

Faithfulness

  • Include everything needs to be described.

  • Avoid meaningless descriptions.

  • Enforce as many constraints as possible.

    • Cardinality Ratios.

    • Participation Constraints

    • Existence Dependency.


Cardinality ratios

Cardinality Ratios

  • The cardinality ratios for binary relationships

    • 1:1, 1:N, N:1, and M:N

  • Attributes of 1:1 relationship types can be migrated to either of the participating entity types.

  • Attributes of 1:N or N:1 relationship types can be migrated only to the entity type at N-side of the relationship.

  • Attributes of M:N relationship types must be specified as relationship attributes.


Examples

Starting date

Starting date

Starting date

Examples

Manages

DEPARTMENT

EMPLOYEE

1

1

Manages

DEPARTMENT

EMPLOYEE

1

1

Manages

DEPARTMENT

EMPLOYEE

1

1


Examples1

Hours

Hours

Works_on

Works_on

Examples

N

1

EMPLOYEE

PROJECT

1

N

EMPLOYEE

PROJECT

Note: IF we assumed that each employee can only work on one project, and there

may be many employees working on the same project, THEN:

The attribute Hours can be migrated to EMPLOYEE, but not PROJECT.


Examples2

Hours

Examples

Works_on

EMPLOYEE

PROJECT

N

M

Note: IF we assume that each employee may work on multiple projects at the same time, and there may be many employees working on the same projects, THEN:

The attribute Hours can not be migrated to either PROJECT or

EMPLOYEE.


Participation constraints

Participation Constraints

  • It specifies whether the existence of an entity depends on its being related to another entity via relationship type.

  • Two types

    • total participation (Using double line connecting the participating entity type)

    • partial participation

  • Total participation is also called Existence Dependency.


Examples3

Works_for

Examples

N

1

DEPARTMENT

EMPLOYEE

Note: IF we assume that every employee should be assigned to certain department.

THEN:

There is a total participation constraint defined on EMPLOYEE via the

relationship Works_for.


Database modeling using the entity relationship model continued

Works_for

Examples

N

1

DEPARTMENT

EMPLOYEE

Note: IF we assume that each department should have at least one employee

being assigned, THEN:

There is a total participation constraint defined on DEPARTMENT via the

relationship Works_for.


Database modeling using the entity relationship model continued

Examples

1

1

Manages

DEPARTMENT

EMPLOYEE

Note: IF we assume that should have exactly one manager, but not every employee could be a manager of certain department, THEN:

There is a total participation constraint defined on DEPARTMENT via the

relationship type Manages. However, EMPLOYEE partially participates the relationship type Manages.


Alternative notations for constraints

Alternative Notations for Constraints

  • A pair of integer (min, max) with each participation of entity type E in a relationship type R. ( maxmin 0, max 1)

  • min: at least min; max: at most max

  • min = 0 implies partial participation.

  • min 1 implies total participation.


Examples4

Works_for

Works_for

Examples

(1,1)

(4,N)

DEPARTMENT

EMPLOYEE

This example implies that each employee is assigned to one and only one department. Each department has at least four employees, and there is no upper limit of the number of employees in a department.

N

1

DEPARTMENT

EMPLOYEE


Examples5

Examples

(0,1)

(1,1)

DEPARTMENT

Manages

EMPLOYEE

What does this example imply?

1

1

Manages

DEPARTMENT

EMPLOYEE

What does this example imply?


Examples6

Dependents_of

DEPENDENT

sex

birthdate

Examples

(0,N)

EMPLOYEE

(1,1)

SSN

name

relationship

What does this example imply?


Avoiding redundancy

Avoiding Redundancy

  • Problem with redundancy

    • Wastes space.

    • Encourages inconsistency.

  • Principle: describe and store everything once only.

  • Intuition: something is redundant if it could be hidden from view, and you could still figure out what it is from the other data.


Examples7

Examples

name

name

address

BEERS

ManfBy

MANFS

name

manf

manfadd

BEERS

Question: Which one is better?


Kiss k eep i t s imple s tudent

KISS: Keep It Simple, Student

  • Avoid intermediate concepts if unnecessary.

name

name

BEERS

ManfBy

MANFS


  • Login