More on ER Modeling

1
More on E-R Modeling

• MIS 372
• Shaun Simpson
• WSU Vancouver

2
Database Design Overview

• Conceptual Design
• Diagram
• Technology Independent
• Logical (Internal) Design
• Technology Dependent
• Relational Tables
• Physical
• DBMS (Database Management System)
• External
• Views - Program or Application based

3
Why Do Conceptual Modeling?

• Independent of database technology
• Understandable representation of organizational
  data
• Strong foundation for the development process
• Helps to elicit business rules

4
The Entity-Relationship Model

• Picture of the people, places, objects, things,
  events, or concepts, their characteristics and
  relationships, for an organization or business
  area.
• Visual Representation
• Communication Tool

5
The Components of an E-R Diagram...

• Entities
• Person, place, object, event, or concept (Nouns)
• Things about which we wish to collect data
• Relationships
• Association between entities (Verbs)
• Directional

Employee
Works
6
Components of the E-R Diagram

• Cardinality
• 1, M, N
• Generic maximums
• Upper and lower bounds
• Mandatory/Optional
• 0 when optional
• Each employee works in one dept.
• Many employees belong to one dept.

Employee
M
(1,1)
Works
(1,100)
1
Dept.
7
Very Basic Models
M
1
Faculty
teach
Course
(0,1)
(0,4)
Dependent
M
1
Employee
has
(1,1)
(0,N)
N
M
Course
Enroll
Student
(1,7)
(0, M)
8
But, theres more

• Attributes
• Properties or characteristics of entities
• Actual data items we collect

EmpName
Employee
EmpAddr
EmpJob
9
Candidate and Primary Keys

• Attributes that uniquely identify an entity
  instance (i.e., row, record)
• Candidate keys are ALL attributes that uniquely
  define the entity instance
• Primary keys are the chosen ones
• How to choose
• should not change over time (age)
• must have unique, non-null values
• use as few attributes as possible

10
Identifying the primary key

• Select the attribute
• Underline it

EmpName
Employee
EmpAddr
EmpJob
EmpId
11
Practice

• Kisha, against the advice of her friends, was
  simultaneously studying data management and
  Shakespearian drama. She thought the two
  subjects would be an interesting contrast.
  However, the classes are very demanding and often
  enter her dreams. Last night, she dreamt that
  William Shakespeare wanted her to draw a data
  model. He explained, before she woke up in a
  cold sweat, that a play had many characters, but
  the same character never appeared in more than
  one play. Methinks, he said, the same name
  may have appeareth more than the once, but twas
  always a person of a different ilk. He then, she
  hazily recollects, went on to spout about the
  quality of data dropping like gentle rain Draw
  a model to keep old Bill quiet and help Kisha get
  some sleep.

12
Character - Play Example
Character
M
1
Play
has
(1,1)
(1,N)
13
Composite Entities
M
N
Customer
Orders
Products
M
N
1
1
Customer
Orders
Products
14
Multi-valued Attributes

• Attributes that can have multiple values
• Need to fix it before the next phase

EmpSkill
Employee
SkillCode
M
N
Employee
has
Skill
(0,12)
(1,500)
SkillName
15
Practice...

• The Marathoner, a monthly magazine, regularly
  reports the performance of professional marathon
  runners. It has asked you to design a database
  to record the details of all major marathons
  (e.g., Boston, London, Paris). Professional
  marathon runners compete in several races each
  year. A race may have thousands of competitors,
  but only about 200 or so are professional
  runners, the ones The Marathoner tracks. For
  each race, the magazine reports a runners time
  and finishing position and some personal details
  such as name, gender, and age.

16
Runner / Marathon Example
Age
MarathonID
M
N
RunnerID
Runner
places
Marathon
(1,200)
(0,N)
Finish time
Name
17
Weak Entity

• Cannot exist without the entity (parent) with
  which it has a relationship (existence
  dependency).
• Has a primary key that is derived from the parent
  entity in the relationship.

Dependent
M
1
Employee
has
(0,N)
(1,1)
18
Derived Attribute

• Attribute that can be calculated from other
  attributes.
• Need to note it, but it will not take up space in
  the database

EmpName
Employee
EmpAddr
EmpDOB
EmpAge
19
Practice

• A laboratory has several chemists who work on
  various projects and who may use certain kinds of
  equipment on each project. The laboratory
  manager wishes to create a database to track
  chemists, their projects, and the equipment that
  gets used on various projects. For each chemist,
  you should capture their name and phone number.
  For each project, include the project ID and the
  start date. For each piece of equipment, track
  the equipment ID number and cost. Feel free to
  add items if they are needed.

20
Laboratory Example
Equip ID No.
Name
Proj ID No.
M
M
M
1
Chemist
Project
Equipment
Phone
Start Date
Number
Cost
21
Generalization

• Subtypes and Supertypes
• Some entities are subtypes of other entities
• Share some attributes, but not others
• Also called ISA relationship
• Inheritance
• Issues of Exclusivity and Exhaustiveness

22
Super/Sub-Type Relationship
EMPLOYEE
An Example
ISA
ISA
ISA
HOURLY EMPLOYEE
SALARIED EMPLOYEE
COMMISSION-BASED EMPLOYEE
23
Relationship Degree

• Unary
• One entity
• Binary
• Two entities
• Ternary
• Three entities

24
Modeling Hints and Tips

• Use different names for different things
• If an entity has only one attribute, think about
  making the entity an attribute of another entity
• If the data item in question has a unique
  identity and it has attributes, the it should
  probably be an entity.
• Model the data - not the physical artifacts
  (reports)
• Do NOT try to write code or think about the final
  format for the data.
• Dont worry about Ifs, or how to connect entities
  except through relationships.

25
Final Practice

• Valerys Gallery - Order Processing System

Catalog
PreparePacking Slip
Orders
Order
Quantity on Hand
Valid Order
Updated Quantity on Hand
Catalog Description
Packing Slip
Process Order
Invalid Order
Customer
Order
26
Next Class

• The Relational Model
• Converting E-R diagram into Tables
• Normalization
• Relations