Entity relationship e r model
This presentation is the property of its rightful owner.
Sponsored Links
1 / 58

Entity-Relationship (E-R) Model PowerPoint PPT Presentation


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

Entity-Relationship (E-R) Model. COURSE #. CTITILE. PROF. SCHED. ROOM. COURSE. ENROLL. GRADE. STUDENT. SNAME. CREDITS. STUID. MAJOR. * Sometimes it’s natural to classify the entities in an entity set into subclass, then we will use Class Hierarchies. Class Hierarchies. name. lot.

Download Presentation

Entity-Relationship (E-R) Model

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


Entity relationship e r model

Entity-Relationship (E-R) Model

COURSE#

CTITILE

PROF

SCHED

ROOM

COURSE

ENROLL

GRADE

STUDENT

SNAME

CREDITS

STUID

MAJOR


Class hierarchies

* Sometimes it’s natural to classify the entities in an entity set into subclass, then we will use Class Hierarchies.

Class Hierarchies

name

lot

ssn

Employees

Hourly

wage

Hours

worked

ISA

contractid

Hourly

Emps

Contract

Emps


Aggregation

* A relationship set is an

association between

entity sets.

Sometimes we have to model a relationship between a collection of entities and relationships, then we need to use aggregation.

Aggregation

name

ssn

lot

Employees

monitors

until

Started-on

dname

since

pid

pbudget

did

budget

Sponsors

Projects

Department


Conceptual design using the er model

Conceptual design using the ER model

  • Should a concept be modeled as an entity or an attribute ?

  • Should a concept be modeled as an entity or a relationship ?

  • What are the relationships ? Should we use binary or ternary relationships ?

  • Should we use aggregation ?


Unary relationship

UNARY RELATIONSHIP

  • Unary relationship : is an associate of 1 entity set.

    * Note : an entity set is a group of related entities.

Dr. Lee

Student

* Entity set =

* Relation =

* Attribute =


Binary relationship

BINARY RELATIONSHIP

  • Binary relationship : is an associate of 2 entities sets .

Works_In

Employees

Department

ssn

name

lot

dname

budget

did

* Entity set =

* Relation =

* Attribute =


Ternary relationship

TERNARY RELATIONSHIP

Ternary relationship : is an associate of 3 entities sets .

Works_In

Employees

Department

ssn

name

lot

did

budget

dname

Locations

add

cap

* Entity set =

* Relation =

* Attribute =


Quartery relationship

QUARTERY RELATIONSHIP

Quartery relationship : is an associate of 4 entities sets.

Product

Works_In

Employees

Department

Locations

* Entity set =

* Relation =

* Attribute =


Additional features of the er model

Additional features of the ER model

  • In the ER model allow us to draw important distinctions about the data.

  • One of the features of the ER model is :

    Key Constraints

    Note : A key constraints is also known as Primary key.

    A primary key is a candidate key selected to uniquely identify all other attribute values in any given row, can not contain null entries.


An example of key constraints on manage

An example of Key Constraints on Manage

name

Since

dname

budget

did

ssn

lot

Manages

Department

Employees

ssn = Social Security Number.

did = Department id.


Weak entities

Weak Entities

name

cost

Pname

---------

age

ssn

lot

Policy

Department

Employees

A weak entity : is an entity of which is existence depends on other entities.


Entity versus attribute

Entity versus Attribute

from

to

name

dname

budget

did

ssn

lot

Works-In2

Department

Employees

* Entity set =

* Relation =

* Attribute =


Entity versus relationship

Entity versus Relationship

dname

name

did

budget

ssn

lot

Manages

Department

Employees

since

apptnum

Mgr-Appts

dbudget


Binary versus ternary relationship

Binary versus Ternary Relationship

name

Pname

---------

age

ssn

lot

Covers

Dependents

Employees

Policies

policyid

cost


Using a ternary relationship instead of aggregation

Using a Ternary Relationship instead of Aggregation

Started-on

dname

did

budget

pid

pbudget

Sponsors

Departments

Projects

Employees

lot

ssn

name


One to many relationship

ONE TO MANY RELATIONSHIP

customer-name

customer-street

loan-number

amount

customer-city

customer-id

borrower

loan

customer


Many to one relationship

MANY TO ONE RELATIONSHIP

customer-name

customer-street

loan-number

amount

customer-city

customer-id

borrower

loan

customer


One to one relationship

ONE TO ONE RELATIONSHIP

customer-name

customer-street

loan-number

amount

customer-city

customer-id

borrower

loan

customer


E r diagram with composite multivalued and derived attributes

E-R Diagram with Composite, Multivalued, and Derived Attributes

street-name

middle-name

Street-number

apartment-number

last-name

first-name

street

name

city

customer-id

address

state

Customer

zip-code

age

date-of-birth

phone-number


Weak entity sets

WEAK ENTITY SETS

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set.

For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship.

The identifying relationship is many to one from the weak entity set to the identifying entity set, and the participation of the weak entity set in the relationship is total.


Discriminator

DISCRIMINATOR

The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. For example, the discriminator of a weak entity set payment is the attribute payment-number, since, for each loan a payment number uniquely identifies one single payment for that loan. The discriminator of a weak entity set is also called the partial key of the entity set.

Note: although each payment entity is distinct, payments for different loans may share the same payment-number. Thus, payment entity set does not have a primary key; it is a weak entity set.

The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s discriminator.


E r diagram with a weak entity set

E-R DIAGRAM WITH A WEAK ENTITY SET

payment-date

amount

In a E-R diagrams, a doubly outlined box indicates a weak entity set, and a doubly outlined diamond indicates the corresponding identifying relationship. We underline the discriminator of a weak entity set with a dashed line.

loan-number

payment-number

payment-amount

loan-payment

loan

payment

E-R diagram with a weak entity set


Entity relationship model

Entity/Relationship Model

Diagrams to represent designs.

  • Entity like object, = “thing.”

  • Entity set like class = set of “similar” entities/objects.

  • Attribute = property of entities in an entity set, similar to fields of a struct.

  • In diagrams, entity set  rectangle;attribute  oval.

name

phone

ID

Students

height


Relationships

Relationships

  • Connect two or more entity sets.

  • Represented by diamonds.

Taking

Students

Courses


Relationship set

Relationship Set

Think of the “value” of a relationship set as a table.

  • One column for each of the connected entity sets.

  • One row for each list of entities, one from each set, that are connected by the relationship.

    StudentsCourses

    SallyCS180

    SallyCS111

    JoeCS180

    ……


Multiway relationships

Multiway Relationships

Usually binary relationships (connecting two E.S.) suffice.

  • However, there are some cases where three or more E.S. must be connected by one relationship.

  • Example: relationship among students, courses, TA's (and graders).

    Possibly, this E/R diagram is OK:

Taking

Students

Courses

Assisting

TA/Graders


Entity relationship e r model

  • Works in CS180, because each TA (or grader) is a TA of all students. Connection student-TA is only via the course.

  • But what if students were divided into sections, each headed by a TA?

    • Then, a student in CS180 would be related to only one of the TA's for CS180. Which one?

  • Need a 3-way relationship to tell.


Entity relationship e r model

Courses

StudentsCoursesTAs

AnnCS180Jan

SueCS180Pat

BobCS180Jan

………

Enrolls

Students

TAs


Beers bars drinkers example

Beers-Bars-Drinkers Example

  • Our running example for the course.

name

addr

license

Frequents

Serves

Bars

Likes

Beers

Drinkers

name

manf

name

addr


Multiplicity of relationships

Multiplicity of Relationships

Representation of Many-One

  • E/R: arrow pointing to “one.”

    • Rounded arrow = “exactly one.”

Many-many

Many-one

One-one


Example drinkers have favorite beers

Example:Drinkers Have Favorite Beers

name

addr

license

Frequents

Serves

Bars

Likes

Beers

Drinkers

Favorite

name

manf

name

addr


One one relationships

One-One Relationships

Put arrows in both directions.

Design Issue:

Is the rounded arrow justified?

Design Issue:

Here, manufacturer is an E.S.

In earlier diagrams it is an attribute.

Which is right?

Best-seller

Manfs

Beers


Attributes on relationships

Attributes on Relationships

price

  • Shorthand for 3-way relationship:

Sells

Bars

Beers

price

Prices

Sells

Bars

Beers


Entity relationship e r model

  • A true 3-way relationship.

    • Price depends jointly on beer and bar.

  • Notice arrow convention for multiway relationships: “all other E.S. determine one of these.”

    • Not sufficiently general to express any possibility.

    • However, if price, say, depended only on the beer, then we could use two 2-way relationships: price-beer and beer-bar.

    • Or better: just make price an attribute of beer.


Converting multiway to 2 way

Converting Multiway to 2-Way

  • Baroque in E/R, but necessary in certain “object-oriented” models.

  • Create a new connecting E.S. to represent rows of a relationship set.

    • E.g., (Joe's Bar, Bud, $2.50) for the Sells relationship.

  • Many-one relationships from the connecting E.S. to the others.

BBP

The-Bar

The-Beer

The-Price

Bars

Beers

Price


Roles

Roles

Sometimes an E.S. participates more than once in a relationship.

  • Label edges with roles to distinguish.

HusbandWife

d1d2

d3d4

……

Married

husband

wife

Drinkers


Entity relationship e r model

Buddy1Buddy2

d1d2

d1d3

d2d1

d2d4

……

Buddies

  • Notice Buddies is symmetric, Married not.

    • No way to say “symmetric” in E/R.

      Design Question

      Should we replace husband and wife by one relationship spouse?

1

2

Drinkers


More design issues

More Design Issues

1. Subclasses.

2. Keys.

3. Weak entity sets. (Next class.)


Subclasses

Subclasses

Subclass = special case = fewer entities = more properties.

  • Example: Ales are a kind of beer. In addition to the properties (= attributes and relationships) of beers, there is a “color” attribute for ales.


E r subclasses

E/R Subclasses

  • Assume subclasses form a tree (no multiple inheritance).

  • isa triangles indicate the subclass relation.

Beers

name

manf

isa

Ales

color


Different subclass viewpoints

Different Subclass Viewpoints

1. E/R viewpoint: An entity has a component in each entity set to which it logically belongs.

  • Its properties are the union of the properties of these E.S.

    2. Contrasts with object-oriented viewpoint: An object (entity) belongs to exactly one class.

  • It inherits propertiesof its superclasses.

Beers

name

manf

isa

Pete’s Ale

Ales

color


Multiple inheritance

Multiple Inheritance

Theoretically, an E.S. could be a subclass of several other entity sets.

name

manf

name

manf

Beers

Wines

isa

isa

Grape

Beers


Problems

Problems

How should conflicts be resolved?

  • Example: manf means vintner for wines, bottler for beers. What does manf mean for “grape beers”?

  • Need ad-hoc notation to resolve meanings.

  • In practice, we shall assume a tree of entity sets connected by isa, with all “isas” pointing from child to parent.


Entity relationship e r model

Keys

A key is a set of attributes whose values can belong to at most one entity.

  • In E/R model, every E.S. must have a key.

    • It could have more than one key, but one set of attributes is the “designated” key.

  • In E/R diagrams, you should underline all attributes of the designated key.


Example

Example

  • Suppose name is key for Beers.

  • Beer name is also key for ales.

    • In general, key at root is key for all.

Beers

name

manf

isa

Ales

color


Example a multiattribute key

Example: A Multiattribute Key

  • Possibly, the combination of hours + room also forms a key, but we have not designated it as such.

number

hours

Courses

dept

room


  • Login