entity relationship model - intro to db

8/6/2019 Entity Relationship Model - Intro to DB

1/44

Tanvir AhmedTanvir Ahmed

EntityEntity--RelationshipRelationship

ModelModel


2/44

Tanvir Ahmed

EntityEntity--Relationship ModelRelationship Model

Design ProcessDesign Process ModelingModeling

ConstraintsConstraints

EE--R DiagramR Diagram Design IssuesDesign Issues

Weak Entity SetsWeak Entity Sets

Extended EExtended E--R FeaturesR Features Design of the Bank DatabaseDesign of the Bank Database

Reduction to Relation SchemasReduction to Relation Schemas

Database DesignDatabase Design


3/44

Tanvir Ahmed

ModelingModeling

AA databasedatabase can be modeled as:can be modeled as: a collection of entities,a collection of entities,

relationship among entities.relationship among entities.

AnAn entityentity is an object that exists and isis an object that exists and is

distinguishable from other objects.distinguishable from other objects. Example: specific person, company, event,Example: specific person, company, event,

plantplant

Entities haveEntities have attributesattributes Example: people haveExample: people have namesnames andand addressesaddresses

AnAn entity setentity set is a set of entities of the same typeis a set of entities of the same typethat share the same properties.that share the same properties.

Example: set of all persons, companies, trees, holidaysExample: set of all persons, companies, trees, holidays


4/44

Tanvir Ahmed

Entity SetsEntity Sets customercustomer andand loanloancustomer_id customer_ customer_ customer_ loan_ amount

name street city number


5/44

Tanvir Ahmed

Relationship SetsRelationship SetsAA relationshiprelationship is an association among severalis an association among several

entitiesentities

Example:Example:HayesHayes depositordepositor AA--102102

customercustomer entityentityrelationship setrelationship setaccountaccount entityentityAA relationship setrelationship set is a mathematical relation amongis a mathematical relation among

nn uu 2 entities, each taken from entity sets2 entities, each taken from entity sets{({(ee11,, ee22, , eenn) |) | ee11 EE11,, ee22 EE22, ,, , eenn

EEnn}}

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

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


6/44

Tanvir Ahmed

Relationship SetRelationship Set borrowerborrower


7/44

ER Model BasicsER Model Basics

EntityEntity:: RealReal--world object distinguishable fromworld object distinguishable from

other objects.other objects.An entity is described (in DB)An entity is described (in DB)using a set ofusing a set ofattributesattributes.. Entity SetEntity Set:: A collection of similar entities. E.g., allA collection of similar entities. E.g., all

employees.employees.

All entities in an entity set have the same set ofAll entities in an entity set have the same set ofattributes. (Until we consider ISA hierarchies, anyway!)attributes. (Until we consider ISA hierarchies, anyway!)

Each entity set has aEach entity set has a keykey.. Each attribute has aEach attribute has a domaindomain..

Employees

ssnname

lot


8/44

Tanvir Ahmed

EE--R Model ConstructsR Model Constructs

Entity instanceEntity instance -- person, place, object, event,person, place, object, event,concept (often corresponds to a row in a table)concept (often corresponds to a row in a table) EntityTypeEntityType collection of entities (often correspondscollection of entities (often corresponds

to a table)to a table)

AttributeAttribute -- property or characteristic of an entityproperty or characteristic of an entitytype (often corresponds to a field in a table)type (often corresponds to a field in a table)

Relationship instanceRelationship instance link between entitieslink between entities(corresponds to primary key(corresponds to primary key--foreign keyforeign keyequivalencies in related tables)equivalencies in related tables) RelationshiptypeRelationshiptype category of relationshiplinkcategory of relationshiplink

between entity typesbetween entity types


9/44

Tanvir Ahmed

Sample E-R Diagram (figure 3-1)


10/44

Tanvir Ahmed

Figure 3-2 -- Basic E-R Notation

Entity

symbols

Relationship

symbols

Attribute

symbols

A special

entity that is

also a

relationship


11/44

Tanvir Ahmed

What Should an Entity Be?What Should an Entity Be?SHOULD BE:SHOULD BE:An object that will have many instances inAn object that will have many instances in

the databasethe database

An object that will be composed of multipleAn object that will be composed of multipleattributesattributes

An object that we are trying to modelAn object that we are trying to model

SHOULD NOT BE:SHOULD NOT BE:A user of the database systemA user of the database system

An output of the database system (e.g. aAn output of the database system (e.g. areport)report)


12/44

Tanvir Ahmed

Inappropriate entities

System userSystem user System outputSystem output

Appropriate entities

Figure 3-4


13/44

Tanvir Ahmed

AttributesAttributes

AttributeAttribute -- property or characteristic of anproperty or characteristic of anentity typeentity type

Classifications of attributes:Classifications of attributes: Simple versus and Composite AttributeSimple versus and Composite Attribute

SingleSingle--Valued and Multivalued AttributeValued and Multivalued Attribute

Derived AttributesDerived Attributes


14/44

Tanvir Ahmed

AttributesAttributes

An entity is represented by a set of attributes, that isAn entity is represented by a set of attributes, that isdescriptive properties possessed by all members of andescriptive properties possessed by all members of anentity set.entity set.

DomainDomain the set of permitted values for eachthe set of permitted values for eachattributeattribute

Attribute types:Attribute types: SimpleSimple andand compositecomposite attributes.attributes. SingleSingle--valuedvalued andand multimulti--valuedvalued attributesattributes

Example: multivalued attribute:Example: multivalued attribute: phone_numbersphone_numbers DerivedDerivedattributesattributes

Can be computed from other attributesCan be computed from other attributes Example: age, given date_of_birthExample: age, given date_of_birth

Example:

customer = (customer_id, customer_name,customer_street, customer_city )

loan = (loan_number, amount )


15/44


16/44

Tanvir Ahmed

Figure 3-7 -- A composite attribute

An attribute

broken into

component parts


17/44

Tanvir Ahmed

Figure 3-9a Simple key attribute

The key is underlined


18/44

Tanvir Ahmed

Figure 3-9b -- Composite key attribute

The key is composed

of two subparts


19/44

Tanvir Ahmed

Figure 3-8 -- Entity with a multivalued attribute (Skill) and

derived attribute (Years_Employed)

Derived

from date employed and current date

Whats wrong with this?

Multivalued:an employee can have

more than one skill


20/44

Tanvir Ahmed

Degree of RelationshipsDegree of Relationships

Degree of a Relationship is theDegree of a Relationship is thenumber of entity types thatnumber of entity types that

participate in itparticipate in itUnary RelationshipUnary Relationship

Binary RelationshipBinary RelationshipTernary RelationshipTernary Relationship


21/44

Tanvir Ahmed

Degree of relationships from figure 3-2

One entity

related to

another of

the same

entity type

Entities of

two differenttypes related

to each other

Entities of threedifferent types

related to each

other


22/44

Tanvir Ahmed

Unary relationships -- figure 3-12a


23/44

Tanvir Ahmed

Binary relationships figure 3-12b


24/44

Tanvir Ahmed

Ternary relationships figure 3-12c

Note: a relationship can have attributes of its own


25/44

Tanvir Ahmed

Cardinality ConstraintsCardinality Constraints

Cardinality ConstraintsCardinality Constraints -- the number ofthe number ofinstances of one entity that can or must beinstances of one entity that can or must be

associated with each instance of anotherassociated with each instance of anotherentity.entity.


26/44

Tanvir Ahmed

Cardinality of RelationshipsCardinality of Relationships

OneOne toto OneOne Each entity in the relationship will have exactly oneEach entity in the relationship will have exactly one

related entityrelated entity

OneOne toto ManyMany An entity on one side of the relationship can have manyAn entity on one side of the relationship can have many

related entities, but an entity on the other side will haverelated entities, but an entity on the other side will havea maximum of one related entitya maximum of one related entity

ManyMany toto ManyMany Entities on both sides of the relationship can have manyEntities on both sides of the relationship can have many

related entities on the other siderelated entities on the other side


27/44

Tanvir Ahmed


28/44

Tanvir Ahmed

OneOne--ToTo--Many RelationshipMany Relationship

In the oneIn the one--toto--many relationship a loanmany relationship a loanis associated with at most one customeris associated with at most one customerviavia borrowerborrower, a customer is associated, a customer is associated

with several (including 0) loans viawith several (including 0) loans viaborrowerborrower


29/44

Tanvir Ahmed

ManyMany--ToTo--One RelationshipsOne Relationships

In a manyIn a many--toto--one relationship a loan isone relationship a loan isassociated with several (including 0)associated with several (including 0)customers viacustomers via borrowerborrower, a customer is, a customer is

associated with at most one loan viaassociated with at most one loan viaborrowerborrower


30/44

Tanvir Ahmed

ManyMany--ToTo--Many RelationshipMany Relationship

A customer is associated with severalA customer is associated with several(possibly 0) loans via borrower(possibly 0) loans via borrower

A loan is associated with severalA loan is associated with several(possibly 0) customers via borrower(possibly 0) customers via borrower


31/44

Tanvir Ahmed

Participation of an Entity Set in aParticipation of an Entity Set in aRelationship SetRelationship Set

Total participation (indicated by double line): every entity in the entity set participates in atleast one relationship in the relationship set

E.g. participation of loan in borrower is total

every loan must have a customer associated to it via borrower

Partial participation: some entities may not participate in any relationship in the relationship

set Example: participation of customer in borrower is partial


32/44

Tanvir Ahmed

Cardinality LimitsCardinality Limits

Cardinality limits can also express participation constraints


33/44

Participation ConstraintsParticipation Constraints

Does every department have a manager?Does every department have a manager? If so, this is aIf so, this is a participationconstraintparticipationconstraint: the participationof: the participationof

Departments inManages is said to beDepartments inManages is said to be totaltotal(vs.(vs. partialpartial))..

Every Departments entity must appear in an instance of theEvery Departments entity must appear in an instance of theManages relationship.Manages relationship.

lot

name dname

budgetdid

sincename dname

budgetdid

since

Manages

since

DepartmentsEmployees

ssn

Works_In


34/44

Weak EntitiesWeak Entities

AA weak entityweak entity can be identified uniquely only by consideringcan be identified uniquely only by consideringthe primary key of another (the primary key of another (ownerowner) entity.) entity. Owner entity set and weak entity set must participate in a oneOwner entity set and weak entity set must participate in a one--toto--

many relationship set (one owner, many weak entities).many relationship set (one owner, many weak entities).

Weak entity set must have total participation in thisWeak entity set must have total participation in this identifyingidentifyingrelationship set.relationship set.

lot

name

agepname

DependentsEmployees

ssn

Policy

cost


35/44

Tanvir Ahmed

Strong vs. Weak Entities, andStrong vs. Weak Entities, andIdentifying RelationshipsIdentifying Relationships

Strong entitiesStrong entities exist independently of other types of entitiesexist independently of other types of entities

has its own unique identifierhas its own unique identifier

represented with singlerepresented with single--line rectangleline rectangle Weak entityWeak entity

dependent on a strong entitycannot exist on its owndependent on a strong entitycannot exist on its own Does not have a unique identifierDoes not have a unique identifier

represented with doublerepresented with double--line rectangleline rectangle Identifying relationshipIdentifying relationship

links strong entities to weak entitieslinks strong entities to weak entities

represented with double line diamondrepresented with double line diamond


36/44

Tanvir Ahmed

Figure 3-5: Strong and weak entities

Strong entity Weak entityIdentifying relationship


37/44

Tanvir Ahmed

Figure 4-16 Data model segment for class scheduling


38/44

ISA (`is aISA (`is a))HierarchiesHierarchies

Contract_Emps

name

ssn

Employees

lot

hourly_wages

ISA

Hourly_Emps

contractid

hours_workedAs in C++, or other PLs,attributes are inherited.

If we declare A ISA B, every A

entity is also considered to be a Bentity.

Reasons for usingReasons for using ISAISA::

To add descriptive attributesTo add descriptive attributes specific to aspecific to asubclasssubclass..

To identify entitities that participate in aTo identify entitities that participate in arelationshiprelationship..


39/44

Tanvir Ahmed

Specialization ExampleSpecialization Example


40/44

AggregationAggregation

Used when we haveUsed when we haveto model ato model arelationshiprelationshipinvolving (entitityinvolving (entitity

sets and) asets and) arelationship setrelationship set.. AggregationAggregationallowsallows

us totreataus totreatarelationship setas anrelationship setas an

entity set forentity set forpurposes ofpurposes ofparticipationin(other)participationin(other)relationships.relationships.

pbudgetpiddiddname pname

until

ProjectsDepartments Sponsors

Employees

Monitors

lotname

ssn

since


41/44

Tanvir Ahmed

Cardinality figure 3-2


42/44

Tanvir Ahmed

EE--R Diagram for a Banking EnterpriseR Diagram for a Banking Enterprise


43/44

Tanvir Ahmed

Summary of Symbols (Cont.)Summary of Symbols (Cont.)


44/44

Tanvir Ahmed

entity relationship model - intro to db

Documents