14593_ER Data Modeling

8/4/2019 14593_ER Data Modeling

1/29

Given: Supplier , Parts , Projects in E-R

Diagram

Identify Relations(tables) corresponding to E-R Diagram

Identify PK, FK,CK,AK in all tables.


2/29

ER model

The ER model is a conceptual data model

that views the real world as entities and

relationships A basic component of the model is the entity

relation diagram, which is used to visually

represents data objects


3/29

Utility of ER model

It maps well to the relational model

It is simple and easy to understand

The model can be used as a design plan bythe database developer


4/29

Basicconstructs of ER model

ENTITIES

entities are the principal data object about

which information is to be collectedTWOTYPES:

strong: that does not rely on another for

identificationweak: that relies on another for

identification


5/29

relationships

It represents an association between two or

more entities

Example of relationship would be employees are assigned to project

Department manage one or more task


6/29

attributes

Attributes describe the properties of the entitiesto which they are associated


7/29

Classification of attributes

Simple: that cannot be further subdivided

Composite: an attribute composed of

multiple components, each

with

anindependent existence is called a compositeattribute

Single-valued: that holds a single value for a

single entity Multi-valued: that holds multiple values for a

single entity e.g. student entity can havemultiple values for hobbies


8/29

Derived attribute: that represents the value

that is derivable from the value of related

attribute


9/29

Degree of relationship

Degree of a relationship is the number of

entities associated with the relationship


10/29

Connectivityand cardinality

The connectivity of a relationship describes

the mapping of associated entity instances in

th

e relationsh

ip


11/29

Types of connectivity

One to one

One to many

Many to one Many to many


12/29

Mapping Cardinalities

One to one One to many

Note: Some elements in A andB may not be mapped to anyelements in the other set


13/29

Mapping Cardinalities

Many to one Many to many

Note: Some elements in A and B may not be mapped to anyelements in the other set


14/29

E-R Diagrams

n Rectangles represent entity sets.

n Diamonds represent relationship sets.

n Lines link attributes to entity sets and entity sets to relationship sets.

n Ellipses represent attributesl Double ellipses represent multivalued attributes.

l Dashed ellipses denote derived attributes.

n Underline indicates primary key attributes (will study later)


15/29

E-R Diagram WithComposite, Multivalued, and

Derived Attributes


16/29

Relationship Sets with

Attributes


17/29

Extended E-R Features:

Specialization Top-down design process; we designate subgroupings

within an entity set that are distinctive from otherentities in the set.

These subgroupings become lower-level entity setsthat have attributes or participate in relationships thatdo not apply to the higher-level entity set.

Depicted by a triangle component labeled ISA (E.g.customeris aperson).

Attribute inheritance a lower-level entity set inheritsall the attributes and relationship participation of the

higher-level entity set to which it is linked.


18/29

Specialization Example


19/29

Extended ER Features:

Generalization A bottom-up design process combine a

number of entity sets that share the same

features into a higher-level entity set.

Specialization and generalization are simple

inversions of each other; they are

represented in an E-R diagram in the same

way. The terms specialization and generalization

are used interchangeably.


20/29

Design Constraints on a

Specialization/Generalization

condition-defined Example: all customers over 65 years are members ofsenior-citizen entity set; senior-citizen ISA person.

Attribute defined

Example---account is divided into two categorieseithersaving account or current account, depending on the

value of attribute account type.

user-defined

Example---suppose after 3 months of employment ,bank employees are assigned to one of the 4 teams.which person is assigned to which team, it dependsupon the manager.


21/29

Disjoint

an entity can belong to only one lower-level entityset.

Account can be either saving or current but notboth.

Noted in E-R diagram by writing disjointnext to theIS A triangle

Overlapping

an entity can belong to more than one lower-levelentity set ( person can be the manager of more thanone team.)


22/29

Design Constraints on a

Specialization/Generalization (Cont.)

Completeness constraint -- specifies whether anentity in the higher-level entity set must belong to

at least one of the lower-level entity sets within a

generalization / specilization or not.

total : an entity must belong to one of the lower-levelentity sets(eg. Account)

partial: an entity need not belong to one of the lower-level entity sets(eg. Manager of bank)


23/29

Aggregation

nConsider the ternary relationship works_on, whichwe saw earlier

n Suppose we want to record managers for tasks performed by an

employee at a branch


24/29

Aggregation (Cont.)

Relationship sets works_on and manages representoverlapping information Every manages relationship corresponds to a works_on relationshi However, some works_on relationships may not correspond to any

manages relationships

So we cant discard the works_on relationship Eliminate this redundancy via aggregation

Treat relationship as an abstract entity Allows relationships between relationships Abstraction of relationship into new entity

Without introducing redundancy, the following diagramrepresents: An employee works on a particular job at a particular branch An employee, branch, job combination may have an associated

manager


25/29

E-R Diagram foraBanking

Enterprise


26/29

Summary of SymbolsUsed in E-R Notation


27/29

Reduction of an ER dig to

tables We can convert the ER database schema to a

collection of tables

Each table is a collection of rows andcolumns

Row in a table is called tuple and column is

called as an attribute

Each column in a table has unique name


28/29

Tabularrepresentation of

strong entityset Let E be a strong entity set with single valued

attributes a1,a2,,an.

This entity set can be represented by tablecalled E with N columns and each columncorresponds to one of the attributes of E

Each row in table E corresponds to one entityof the entity set E


29/29

Representation of

generalization Table for higher level entity set contains the

attributes of that entity

Table for lower level entity set containsattributes of that entity plus primary keyattributes ofhigher level entities

14593_ER Data Modeling

Documents

Transcript of 14593_ER Data Modeling