Entity-Relationship Modelling

1
Entity-Relationship Modelling

• Entity and relationship sets
• Issues for object-based modelling

2
The Entity-Relationship (E-R) Model 1

• Designing a database real-world modelling
• query reflects a real-world situation
• (e.g. loan status of a book)
• entity-relationship model of an enterprise
• reflects its overall logical structure
• entity object that exists and is
  distinguishable from other objects

3
The Entity-Relationship (E-R) Model 2

• entity object that exists and is
  distinguishable from other objects
• cf OOP object doesn't "exist" until an instance
  is created
• entity can be abstract or concrete
• concrete person, book
• abstract marriage, journey
• NB abstract used here not as in OO class
  abstraction

4
The Entity-Relationship (E-R) Model 3

• Entity sets
• entity set set of entities of the same type
• e.g. entity set customer
• all persons having an account at bank
• entity sets can intersect
• e.g. person / customer / employee

5
The Entity-Relationship (E-R) Model 4

• Entity is represented by a set of attributes
• e.g. customer has c-name, ss, street, c-city
• Representation for a particular customer
• (name, Brill),
• (ss, 121-21-2121),
• (street, Putnam),
• (city, San Marcos)
• permissible values for each attribute define its
  domain

6
The Entity-Relationship (E-R) Model 5

• entity set ? variable entity type ? type
  definition
• Sample entity sets
• branch b-name, b-city, assets
• customer c-name, ss, street, c-city
• employee e-name, phone
• account account, balance
• transaction transaction, date , amount

7
The Entity-Relationship (E-R) Model 6

• Relationships
• relationship association among several entities
• e.g. a relationship associates Brill with account
  183
• NB refer to this association as a relationship
• cf mathematical relation
• (a,b,..., z) aÎA, bÎB, ...., zÎZ
• relationship set math relation on 2 or more
  entity sets

8
The Entity-Relationship (E-R) Model 7

• relationship set math relation on 2 or more
  entity sets
• binary relationship if involves two entity sets
• e.g. CustAcc has (customer, account)
• ... relationship set contains (Brill, 183)
• ... can have descriptive attributes associated
  with a relationship e.g. last-access-date on
  CustAcc
• ordering in a relationship may be significant
• cf. WorksFor on employees.
• ? refer to role to distinguish when domain
  doesn't

9
The Entity-Relationship (E-R) Model 8

• How to devise E-R model? no fixed rules
• choice of relationship sets reflects real-world
  semantics
• Compare employee e-name, ss, phone
• with employee e-name, ss
• phone phone, location
• telrel uses (employee, phone)
• Issues
• does every employee have a phone?
• do employees have access to many phones?
• do employees share phones?

10
The Entity-Relationship (E-R) Model 9

• How to devise E-R model?
• What is entity / attribute set ...?
• No easy answer depends on structure of the
  enterprise
• Cf employee e-name, ss, phone
• does not acknowledge employees without phones
• Could associate e-name with independent entity
• where employee ? e-name, ss

11
The Entity-Relationship (E-R) Model 10

• Mapping cardinalities CustAcc has (customer,
  account)
• 1-1 each customer has exactly one account
• no joint accounts
• 1-many a customer can have more than one account
• no joint accounts
• many-1 every customer has exactly one account,
• possibly a joint account
• many-many
• a customer can have more than one account,
• can also have joint accounts

12
The Entity-Relationship (E-R) Model 11

• Existence dependencies
• Can have an entity that exists only if another
  entity exists e.g. a transaction on an account
• delete account
• ? delete transactions from log but not v.v.
• existence of x depends on existence of y
• ? y dominant entity, x subordinate entity

13
The Entity-Relationship (E-R) Model 12

• The notion of identity
• keys for entities and relationships
• "from a db perspective
• difference between individual entites
  relationships must be expressed in terms of their
  attributes
• cf observing objects in different states
• that happen to coincide "from time to time"

14
The Entity-Relationship (E-R) Model 13

• The notion of identity ...
• keys for entities and relationships
• superkey
• set of attributes that (taken together)
  identify an entity
• e.g. ss or ss,c-name for a customer
• but not c-name alone
• ... 2 people can have the same name, but not same
  social security number

15
The Entity-Relationship (E-R) Model 14

• The notion of identity
• superkey without extra attributes is a candidate
  key
• candidate key set of attributes that
• identifies an entity
• and is minimal wrt this property
• normally select a special key from the candidate
  keys
• as means to identify entity call this the
  primary key

16
The Entity-Relationship (E-R) Model 15

• Strong vs weak entity sets
• an entity set with enough attributes to make a
  candidate key is a strong entity set
• an entity set that doesn't have enough attributes
  to make a candidate key is a weak entity set
• e.g. transaction, date, amount
• may coincide on two separate accounts
• weak entity sets are existence dependent on
  strong entity sets e.g. transaction on account

17
The Entity-Relationship (E-R) Model 16

• weak entity sets are existence dependent on
  strong entity sets ...
• i.e where there is a weak entity set
• "there must be something around that can
  distinguish conceptually different entities"
• To distinguish entities in weak entity sets
• 1. identify the strong entity set on which it is
  existence dependent,
• 2. find an attribute that will discriminate
  between entities in the weak entity set if the
  strong entity is known.
• For example (account, transaction) ?
  transaction.

18
The Entity-Relationship (E-R) Model 17

• Keys for relationships
• Keys distinguish entities in entity sets
• what about relationships?
• A key for a relationship is derived from keys for
  its
• constituent entity sets. For instance, if
  R(A,B,...,Z) is a
• relationship set a key for R is
• a piece of info about one or more of the
  entities participating in a relationship in R
  that is sufficient
• to identify the relationship entirely "

19
The Entity-Relationship (E-R) Model 18

• Keys for relationships .
• To construct a key for a relationship
• take union of the sets of attributes
• primary_key(A),
• primary_key(B),
• ... ,
• primary_key(Z),
• descAttr(R)
• and prune via functional dependency.

20
The Entity-Relationship (E-R) Model 19

• Illustrating key construction
• e.g. RCustAcc gives
• attribute(R) ss, account, date
• CustAcc many-1 ? candidate key is ss
• ... customer has at most one account
• the date associated with this account is
  uniquely spec by account
• If CustAcc many-many need ss, account as key.

21
The Entity-Relationship (E-R) Model 21

• Illustrating key construction
• In general
• add such attributes from descAttr(R)
• as can't infer via functional dependency
• e.g. CustBanker relationship has ROLE field
• loan officer / personal banker
• ? can't infer which role banker plays for a
  customer
• and hence must add ROLE attribute to the key

22
Issues for object-based modelling 1

• criterion What is an entity?
• can a relationship be entity?
• identity how to tell the difference between
  entities?
• effect of evolution
• of entity on identity?
• relationships what connections are there
  between entities?

23
Issues for object-based modelling 2

• relationships what connections are there
  between entities?
• existence dynamic vs static instantiation
• dependencies existence and functional
• multiple views what is whole of an entity?
• can we circumscribe
• its set of attributes?

24
Issues for object-based modelling 3

• behaviour do entities have state and behaviour?
• time do entities change, or just come and go?
• hierarchy inheritance, classification
• Note entity-relationship modelling,
  object-oriented modelling, network and relational
  database design all address these issues in quite
  different ways.