9/2/2014 1yan huang - er chapter 6: er – entity relationship diagram major components of er...

04/11/23 1Yan Huang - ER

Chapter 6: ER – Entity Chapter 6: ER – Entity Relationship DiagramRelationship Diagram

Major components of ER diagramMajor components of ER diagram PracticesPractices


ERER

1976 proposed by Peter Chen1976 proposed by Peter Chen ER diagram is widely used in database ER diagram is widely used in database

designdesign Represent conceptual level of a database Represent conceptual level of a database

systemsystem Describe things and their relationships in high Describe things and their relationships in high

levellevel


Basic ConceptsBasic Concepts

Entity set – an abstraction of similar Entity set – an abstraction of similar things, e.g. cars, students things, e.g. cars, students An entity set contains many entitiesAn entity set contains many entities

Attributes: common properties of the Attributes: common properties of the entities in a entity setsentities in a entity sets

Relationship – specify the relations among Relationship – specify the relations among entities from two or more entity setsentities from two or more entity sets


An ExampleAn Example


RelationshipRelationship

A relationship may be thought as a set as A relationship may be thought as a set as wellwell For binary relationship, it enumerates the pairs For binary relationship, it enumerates the pairs

of entities that relate to each otherof entities that relate to each other For example, entity set For example, entity set M = {Mike, Jack, Tom}M = {Mike, Jack, Tom}

entity set entity set F = {Mary, Kate}.F = {Mary, Kate}. The relationship The relationship set set marriedmarried between M and F may be between M and F may be {<Mike,Mary>,<Tom, Kate>}{<Mike,Mary>,<Tom, Kate>}



A A relationship relationship setset is a mathematical relation is a mathematical relation among among nn 2 entities, each taken from entity 2 entities, each taken from entity setssets

{({(ee11, , ee22, … , … eenn) | ) | ee11 EE11, , ee22 EE22, …, , …, eenn EEnn}}

where (where (ee11, , ee22, …, , …, eenn) is a relationship) is a relationship Example: Example:

(Hayes, A-102) (Hayes, A-102) depositordepositor


Relationship ExampleRelationship Example


Attribute of A Relationship Attribute of A Relationship SetSet



The degree of a relationship = the number The degree of a relationship = the number of entity sets that participate in the of entity sets that participate in the relationshiprelationship Mostly binary relationshipsMostly binary relationships Sometimes moreSometimes more

Mapping cardinality of a relationshipMapping cardinality of a relationship 1 –11 –1 1 – many1 – many many – 1many – 1 Many-manyMany-many


One-One and One-ManyOne-One and One-Many


Many-one and many-manyMany-one and many-many


1- many1- many


Many - 1Many - 1


Many - manyMany - many

04/11/23 15Yan Huang -CSCE4350 - ER

Alternative Cardinality Alternative Cardinality SpecificationSpecification


Note on Mapping Note on Mapping CardinalityCardinality

Both many and 1 include 0Both many and 1 include 0 Meaning some entity may not participate in the Meaning some entity may not participate in the

relationshiprelationship


Total ParticipationTotal Participation

•When we require all entities to participate in the relationship (total participation), we use double lines to specify

Every loan has to have at least one customer


Self RelationshipSelf Relationship

Sometimes entities in a entity set may Sometimes entities in a entity set may relate to other entities in the same set. relate to other entities in the same set. Thus self relationshipThus self relationship

Here employees mange some other Here employees mange some other employeesemployees

The labels “manger” and “worker” are The labels “manger” and “worker” are called called roles roles the self relationshipthe self relationship


More examples on self-More examples on self-relationshiprelationship

People to peoplePeople to people Parent – childrenParent – children Manager – employeeManager – employee Husband – wifeHusband – wife

Word to wordWord to word Root – synonymRoot – synonym

04/11/23 20Yan Huang 350 - ER

AttributesAttributes

Both entity sets and relationships can Both entity sets and relationships can have attributeshave attributes

Attributes may beAttributes may be Composite Composite Multi-valued (double ellipse)Multi-valued (double ellipse) Derive (dashed ellipse)Derive (dashed ellipse)


Another ExampleAnother Example


KeysKeys

A A super keysuper key of an entity set is a set of one of an entity set is a set of one or more attributes whose values uniquely or more attributes whose values uniquely determine each entity.determine each entity.

A A candidate keycandidate key of an entity set is a of an entity set is a minimal super keyminimal super key

Although several candidate keys may Although several candidate keys may exist, one of the candidate keys is selected exist, one of the candidate keys is selected to be the to be the primary keyprimary key..


Key ExamplesKey Examples

Suggest super keys for the following Suggest super keys for the following entity?entity?

What are the candidate keys?What are the candidate keys? Primary key?Primary key?

author

name

birthday

death

description

04/11/23 24Yan Huang - - ER

Ternary RelationshipTernary Relationship


Can We Decompose a Ternary Can We Decompose a Ternary Relationship?Relationship?

Some relationships that appear to be non-Some relationships that appear to be non-binary may be better represented using binary may be better represented using binary relationshipsbinary relationships E.g. A ternary relationship E.g. A ternary relationship parentsparents, relating a , relating a

child to his/her father and mother, is best child to his/her father and mother, is best replaced by two binary relationships, replaced by two binary relationships, fatherfather and and mothermother

Using two binary relationships allows partial Using two binary relationships allows partial information (e.g. only mother being know)information (e.g. only mother being know)

But there are some relationships that are But there are some relationships that are naturally non-binarynaturally non-binary

E.g. E.g. works-on, why?works-on, why?


Converting Ternary to Converting Ternary to binarybinary

In general, any non-binary relationship can be represented In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set.using binary relationships by creating an artificial entity set. Replace Replace R R between entity sets A, B and Cbetween entity sets A, B and C by an entity set by an entity set EE, ,

and three relationship sets: and three relationship sets:

1. 1. RRAA, relating , relating E E and and A A 2. 2.RRBB, relating , relating E E and and BB

3. 3. RRCC, relating , relating E E and and CC Create a special identifying attribute for Create a special identifying attribute for EE Add any attributes of Add any attributes of R R to to E E For each relationship (For each relationship (aaii , b , bii , c , cii) in ) in R,R, create create

1. a new entity 1. a new entity eeii in the entity set in the entity set E E 2. add (2. add (eeii , a , ai i ) to ) to RRAA

3. add (3. add (eeii , b , bii ) to ) to RRBB 4. add ( 4. add (eeii , c , ci i ) to ) to RRCC


Converting Ternary to Converting Ternary to binarybinary


Design an ER DiagramDesign an ER Diagram

Design a database for an on-line reservation Design a database for an on-line reservation system for microscopes in material science system for microscopes in material science lablab

There are two types of users: microscope There are two types of users: microscope administrators and microscope end usersadministrators and microscope end users

Each microscope is located in a specific labEach microscope is located in a specific lab Each request is assigned to an administrator Each request is assigned to an administrator

who can authorize or deny the requestwho can authorize or deny the request Using of some microscope requires the Using of some microscope requires the

presence of an administratorpresence of an administrator Time is divided into 1 hour slots. Each Time is divided into 1 hour slots. Each

reservation can only take one or more time reservation can only take one or more time slotsslots


Weak Entity SetWeak Entity Set

Some entity sets in real world naturally Some entity sets in real world naturally depend on some other entity set depend on some other entity set They can be uniquely identified only if They can be uniquely identified only if

combined with another entity setcombined with another entity set Example:Example:

section1, section2, … become unique only if section1, section2, … become unique only if you put them into a context, e.g. csce4350you put them into a context, e.g. csce4350


Weak Entity Set NotationsWeak Entity Set Notations

Double rectangles for weak entity set

Double diamond for weak entity relationship

Dashed underscore for discriminator


SpecializationSpecialization

A lower-level entity set inherits all the A lower-level entity set inherits all the attributes and relationship participation of attributes and relationship participation of the higher-level entity set to which it is the higher-level entity set to which it is linked.linked.

A lower-level entity set may have A lower-level entity set may have additional attributes and participate in additional attributes and participate in additional relationshipsadditional relationships


SpecificationSpecification

DisjointDisjoint CompletenessCompleteness constraintconstraint (use double (use double

lines)lines) totaltotal : an entity must belong to one of the : an entity must belong to one of the

lower-level entity setslower-level entity sets partialpartial: an entity need not belong to one of : an entity need not belong to one of

the lower-level entity setsthe lower-level entity sets


Design ConsiderationsDesign Considerations

Use of entity sets vs. attributesUse of entity sets vs. attributes Whether we want to keep additional Whether we want to keep additional

informationinformation Use of entity sets vs. relationship setsUse of entity sets vs. relationship sets

Actions among entities are usually represented Actions among entities are usually represented by relationshipsby relationships

Binary versus Binary versus nn-ary relationship sets-ary relationship sets N-nary relationships are usually more natural N-nary relationships are usually more natural

for actions among entity setsfor actions among entity sets Weak entity set vs. strong entity setWeak entity set vs. strong entity set GeneralizationGeneralization


NotationsNotations


ER Practice AgainER Practice Again

Design an ER diagram for an online music Design an ER diagram for an online music store. The database will contain at least store. The database will contain at least the following concepts: songs, artists, the following concepts: songs, artists, bands, albums, and genres. bands, albums, and genres.

State your design assumptions you make State your design assumptions you make to support design decisions. Be sure your to support design decisions. Be sure your assumptions are reasonable.assumptions are reasonable.

04/11/23 38Yan Huang CE4350 - ER

Best Practice Guide for ER Best Practice Guide for ER DesignDesign

Use of entity sets vs. attributesUse of entity sets vs. attributes Use of entity sets vs. relationship setsUse of entity sets vs. relationship sets Binary versus Binary versus nn-ary relationship sets-ary relationship sets Weak entity set vs. strong entity setWeak entity set vs. strong entity set

Choose the natural oneChoose the natural one GeneralizationGeneralization

If specialized entities need to keep additional If specialized entities need to keep additional information and participate in additional information and participate in additional relationshipsrelationships


ER for Banking EnterpriseER for Banking Enterprise

Description handhoutDescription handhout


Read ER DiagramsRead ER Diagrams

Following are some ER diagrams grabbed Following are some ER diagrams grabbed from the webfrom the web

Read to understand/criticizeRead to understand/criticize


??

?

??

?

?

?

?


? ? ?

?

?


?

9/2/2014 1yan huang - er chapter 6: er – entity relationship diagram major components of er...

Documents

relationship set

entity set

yan huang er er

yan huang er keys

yan huang er chapter

yan huang er note

yan huang er attribute

er attributes