The Entity Relationship Model

1
The Entity Relationship Model

• The Entity Relationship Model

2
Outline

• ER model
• Overview
• Entity types
• Attributes, keys
• Relationship types
• Weak entity types
• uses Integration DEFinition for Information
  Modeling (IDEF1X) notation in Erwin
• EER model
• Subclasses
• Specialization/Generalization
• Schema Design
• Single DB
• View integration in IS

3
Data Modelling Process
Mini-world
requirements collection and analysis
database requirements
conceptual design
conceptual schema
DBMS- independent
data model mapping
logical schema
DBMS- specific
physical design
internal schema
4
Entity-Relationship Model

• This model is used in conceptual design.
• Popular in CASE tools, e.g., SSADM, DataArchitect
• A database can be modelled as
• a collection of entity types, and
• relationships among entity types.
• Result is an ER Schema or an ER Diagram.

5
MiniWorld

• Definition A miniworld is a part of the real
  world that
• we are interested in modelling.

6
Entity

• Definition An entity is an object that exists
  and is distinguishable from other objects.
• Object, exists, distinguishable
• DVD store mini-world example
• Physically exist
• The CUSTOMER Jane Doe.
• The Xtra-vision STORE.
• The EMPLOYEE Juan Gonzales.

7
Entity, cont.

• Abstract or organisational entities that do not
  exist physically
• The Introduction to Databases COURSE.
• The SCHOOL of Computing.
• Events
• The TUTORIAL on Friday, October 31, 2007.
• The EXAMINATION on Tuesday December 15, 2007.

8
Attribute

• Definition An attribute is a property of an
  entity.
• Feature
• example CUSTOMER
• Name
• Date of Birth (DOB)
• example DVD
• Copy number
• Title
• CUSTOMER entity examples
• NameJane, DOB1/1/70
• NameJoe, DOB5/6/75

9
Entity Type

• Definition An entity set is a set of entities of
  the same type.
• Definition An entity type is a description of
  the attributes that a set of entities has in
  common.
• Entity set a set of actual entities
• Entity type the ideal description of a kind of
  entity
• Example A CUSTOMER entity type. A CUSTOMER has
  a name and a date of birth (DOB).

NameJoe, DOB5/6/75
NameJulius, DOB9/7/55
CUSTOMER entities
CUSTOMER entity sets
NameJane, DOB1/1/70
NameFred, DOB8/2/64
10
Entity Type, cont.

• Focus on attributes of an ideal entity
• An entity has a value for every attribute of the
  entity type
• Use a null value to represent the following.
• An unknown attribute value
• A Customers Address is unknown.
• An inapplicable attribute value
• A Customers Phone number is inapplicable if
  that person does not own a phone.

CUSTOMER entities
unknown date of birth
NameJane, DOB_at_
NameFred, DOB8/2/64
11
Distinguishing Entities

• How can entities be distinguished from each
  other?
• Assumption Each entity will have a unique
  combination of attribute values.
• Example, assume the Customer entity type has Name
  and DOB attributes. There may be several
  Customers with a Name of Joe. Also, there may
  be several Customers with a DOB of 5/6/75. But
  there can be at most one Customer with the Name
  Joe and a DOB of 5/6/75.
• Is there a smaller such set of attributes?

CUSTOMER entities
NameJoe, DOB5/6/75
NameJoe, DOB10/7/49
NameFred, DOB5/6/75
12
Keys

• Definition A super key of an entity type is a
  set of one or more attributes whose values
  uniquely determine each entity.
• Assume each Customer has a unique ID. The
  following are super keys. Name, DOB, ID,
  Name, ID, Name, DOB, DOB, ID, ID
• Definition A candidate key is a minimal super
  key.
• Name, DOB is a candidate key of Customer.
• ID is a candidate key of Customer.
• Definition Generally, there are may be several
  candidate keys. One of the keys is selected to be
  the primary key.
• ID is the primary key of Customer.

13
ER Diagram - Diamond Notation

• An entity type is represented with a labelled
  rectangle.
• An attribute is represented with a labelled oval,
  connected to an entity type with a line.

Customer
DOB
Customer
Name
14
Attributes - Diamond Notation

• Single valued versus multi-valued
• E.g., multiple telephone extensions
• Simple attributes versus structured attributes
• E.g., an address is composed of a number, a
  street, a city and a state

Customer
Extension
Street
Address
City
Customer
State
15
Attributes, cont.

• Stored versus derived
• E.g., an age can be derived from a stored
  birthdate
• Additional constraints - not represented in a
  diagram
• Null versus non-null
• Domain constrained
• E.g., a student number must be exactly 9 decimal
  digits

DOB
Customer
Age
16
Keys in ER Diagrams

• An attribute that is part of the (primary) key is
  underlined.
• E.g., ID uniquely identifies customers
• Assume no two customers with the same name live
  at the same address.

ID
Name
Customer
Address
ID
Name
Customer
Address
17
Relationship

• Definition A relationship is an object that
  associates entities.

relationship objects
Finding Nemo copy 1
Joe Jenkins
Babe copy 3
Jane Doe
DVD entities
Customer entities
18
Relationship sets

• Definition A relationship set is a specific set
  of associated objects between entities of the
  same entity types.
• (e1, e2 ,..., en) e1 ? E1 ? e2 ? E2 ? .... ?
  en ? En
• where the relationship is (e1, e2 ,..., en).
• Example
• is the set (Joe Jenkins, Eraserhead copy 1),
  (Joe Jenkins, Babe copy 3)

Eraserhead copy 1
Joe Jenkins
Babe copy 3
Jane Doe
Video Tape entities
Customer entities
19
Relationship types

• Definition A relationship type describes a
  relationship set.
• A relationship type is an ideal form (Plato), a
  kind, a type.
• Example The Rents relationship type relates the
  entity type Customer with the entity type DVD,
  capturing which customers currently rent DVDs.

20
Relationship Types - Diamond Notation

• A relationship type is represented with a
  labelled diamond, and has lines to each of the
  entity types it relates.
• The relationship type could have attributes.
• A relationship type does not have key
  attribute(s) the key comes from entity types
  that it relates.

Customer ID
Address
Rents
DVD
Customer
Name
Location
Return Date
21
DVD Store Database Example

• A DVD is an entity type with attributes
• DVD Number, Film ID (the key attributes), and
  Status
• A Customer is an entity type with attributes
• Customer ID (the key), Name, Address
• Rents is a relationship type with attributes
• Return Date, Amount Paid

Customer
Rents
DVD
22
Example, cont.

• The entity-relationship diagram for the above
  example is

Customer ID
Amount Paid
Status
Name
Rents
Customer
DVD
Address
DVD Number
Film ID
Return Date
Where
Rents
Name
Customer
Entity Type
Attribute
Relationship Type