CONCEPTUAL DESIGN PHASE: THE ER MODEL

1
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• STEPS IN DEVELOPING AN E-R DIAGRAM
• Determine the entities.
• Determine the attributes for each entity.
• Determine a unique attribute for each entity
  which can be used as primary key.
• Check attributes in different entities, remove
  any duplications to avoid data redundancy --gt
  normalization

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
2
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• Determine the questions the user may ask.
  Determine which entity / entities are involved
  in these questions.
• Determine the relationships between each
  entity. Determine which keys are involved.
• Determine the type of each relationship.
• Draw the schematic diagram of entities,
  attributes and relationships --gt the E-R
  diagram

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
3
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• TYPES OF RELATIONSHIPS
• 1. one - to - one
• the same employee can occur only once in the
  personal info table and only once in the salary
  table
• personal info salary ID no. one - to -
  one ID no. name department home
  address position
• home telephone gross salary

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
4
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• TYPES OF RELATIONSHIPS
• 2. one - to - many
• the same product can occur only once in the
  product info table but can occur many times in
  the sales table
• product info sales barcode one - to -
  many barcode name quantity sold unit
  price sale date and time
• stock amount customer no.

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
5
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• TYPES OF RELATIONSHIPS
• 3. many - to - many
• the same product can occur many times in the
  sales table and can occur many times in the
  orders table
• orders sales barcode many - to -
  many barcode quantity ordered quantity sold
  manufacturer sale date and time
• order date customer no.

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
6
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• EXAMPLE An E-R diagram that represents
  information for a research institute.
• Step 1 Determine the entities and their
  attributes
• researchers projects departmentsid projec
  t no. department codename title namedepart
  ment code sponsor no. head research
  speciality start date secretary
• end date telephone
• assistants total budget office no.id
  supervisorname sponsorsdepartment
  code id no. assistant to which researcher
  name address telephone

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
7
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• Step 2 Identify the constraints
• A researcher can supervise more than one
  project. researchers one - to - many
  projects
• An assistant can only assist one researcher.
  assistants one - to - one
  researchers
• A sponsor can sponsor more than one project.
  sponsors one - to - many projects
• A researcher works for only one department.
  researchers one - to - one departments

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
8
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• Step 3 Identify the keys and form the
  relationships
• researchers projects departments id project
  no. department code name title name depart
  ment code sponsor no. head research
  speciality start date secretary end
  date telephone total budget office no.
• supervisor
• assistants sponsors id id no.
  name name department code
  address assistant to which researcher
  telephone

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring
9
CONCEPTUAL DESIGN PHASE THE E-R MODEL

• Questions
• 1. In the diagram on the previous slide, which of
  the attributes are Primary Keys, which are
  Foreign Keys?
• 2. In which condition would the department code
  be unnecessary in the assistants entity?
• 3. Some of the keys in the diagram have not been
  used in a relationship. Think of other entities
  which would need to use these keys in a
  relationship.

N. Fenmen - CAA292 Database Applications for
Business - 2003 - 2004 Spring