The Entity-Relationship Model (Chapter 3a)

1
The Entity-Relationship Model(Chapter 3a)

• Laku Chidambaram
• W.P. Wood Professor of MIS
• University of Oklahoma

2
Overview

• Database Design The E-R Model
• Entities
• Attributes
• Relationships

3
Database Design
Engineer (A/C Receivable)
Pilot (Order Entry)
Passenger (Inventory)
External View
4
The Entity Relationship Diagram

• An E-R diagram is a graphical representation of
  an organizations data
• Such data includes the people, places, objects,
  events, or concepts (along with their
  characteristics and relationships) that are
  relevant to an organization

5
Entities

• Person, place, object, event, or concept about
  which the organization wishes to collect and
  maintain data
• Nouns are used to describe entities
• Examples
• Employee, Student, Nurse
• City, State, Country
• Sale, Registration, Account
• Entity Type versus Entity Instance

6
Types of Entities

• Strong Entity can exist independently
• Weak Entity cannot exist without the owner
  entity (with which it has an identifying
  relationship)
• Has a primary key that is derived from the owner
  entity in the relationship

EMPLOYEE
DEPENDENT
EMPLOYEE
has
7
Attributes

• Properties or characteristics of entities (or
  relationships)
• Provide the actual data that describe entities
  (or relationships)

EMPLOYEE
E_ID
E_Name
E_Address
8
Unique Attributes

• Candidate keys are ALL attributes that uniquely
  define an instance of an entity
• A primary key is the candidate key that is chosen
  as the most unique of them all!
• A primary key (or identifier) is ideal if it
• does not change over time
• has unique values that are not null
• does not use intelligent features
• (Note may need to substitute simple keys for
  long, i.e., composite, keys)

9
Composite Attributes

• Attributes that are not atomic (or simple),
  i.e., those attributes that can (and need to) be
  broken down further

EMPLOYEE
E_ID
E_Name
E_Address (E_Street, E_City, E-Zip)
10
Multi-valued Attributes

• Some attributes may have multiple values ...
• but they may need to be fixed before
  proceeding

EMPLOYEE E_Skill
EMPLOYEE
SKILL S_Code S_Name
has
11
Derived Attributes

• Those attributes whose values can be calculated
  from the values of other attributes (either in
  the database or in the system).
• Value is not stored in the database (since it
  does not take up any space)
• E.g., Age can be calculated, where date-of-birth
  is stored

12
Relationships

• Refer to the associations (or links) between
  entities
• Use verbs to describe the links
• May have attributes (in some cases)
• Are directional

STUDENT
DORMITORY
Lives in
EMPLOYEE
STORE
STORE
Works in
13
Associative Entities

• A (many-to-many) relationship that is better
  represented as an entity because it
• contains attributes from the entities it links
  and has its own attributes
• has independent meaning to end-users

CUSTOMER
PRODUCT
Orders
CUSTOMER
ORDER
PRODUCT