Transcript Week 5

Database Design
Sections 9 & 10
Modeling Historical Data, conditional
nontranferability, time-related constraints
1
Modeling Over Time
 Any relationship that changes
through time needs special
consideration.
 An attribute such as status may
change over time.
 Examples of time sensitive
attributes are: rental status, fee
payment, country name, or price
2
Change and Time
1. Every update means loss of
information.
2. Time in your model makes the model
more complex.
3. There are often complex join
conditions.
4. Users can work in advance.
3
Why Modeling Time examples
 Track trends in products
 city police department tracks crimes
in each neighborhood
 frequency of crimes during certain times
of the year
 holidays or during really hot or cold
weather
4
Entity DAY or Attribute Date
PURCHASE
on
 Single attribute
entity without M:1
relationships is
usually replaced by
attribute
for
DAY
#date
PURCHASE
* date
5
What is wrong with revision?
6
Entity Day vs. Attribute Date
7
Examples
 When there is an interest in a
particular day not a date.




Is it a holiday?
Is it a work day or weekend?
Is it leap year?
Month end?
8
Entity DAY
DAY
#date
* public holiday indicator
first day of
starts on
for
TASK ASSIGNMENT
*duration in hours
in
of
with
TASK
#id
EMPLOYEE
#name
9
Time-related Contstraints
10
Modeling Change
EMPLOYEE
#id
COUNTRY
#name
of
for
in
as
ASSIGNMENT
#start date
o end date
11
Modeling Change
EMPLOYEE
#id
COUNTRY
#name
#start time
*end time
of
in
life cycle
attributes
for
as
ASSIGNMENT
#start date
o end date
Even a Country has a life cycle
12
Mapping Historical Price
PRODUCT
#id
*name
have
PRICE =
PRICED PRODUCT = HISTORICAL PRICE
for
PRICE
#start date
*price $
o end date
13
Revised previous ERD
14
Example Movie Star Jewelry rental
JEWELRY PIECE
#code
*description
*rental rate
*insured value
rented by
renting
MOVIE STAR
#id
* first name
* last name
15
Possible Solution
16
Example:
17
Review
18
Modeling time constraint
example 1
 Be aware of constraints that can result from
the time dimension. Here is an example:
 Consider a school fair that features several
booths. The manager signs up volunteers
to work different shifts at different booths.
Some volunteers can work for several
hours; others can work fewer hours
depending on their free time. The schedule
has to be determined in advance, so that
the manager knows which times are not
covered by any volunteers.
19
Review example 1-
What are some other rules?
20
Example 2
21
Modeling change: Price
 When do we need to track price?







Determine best price for item
Sale price vs. original price
Grade change
Value of home
Asking price vs. sale price
Stock, bond, or investment
Others
22
Review slide 4
23
24
Price with time
 Price may fluctuate with time
 “The Good Old Days”
 Examples:




Value of Gold or Silver
Real Estate
Value of currency
Gasoline
25
Price with Time
PRODUCT
#id
*name
with
of
Price =
Priced Produce = Historical Price
PRICE
#start date
*price is $
o end date
26
Slide 11
27
Journaling
28
29
Example
 When a student’s grade is changed,
we need to record information on the
teacher who changed the grade and
the reason for the change.
 Start with the ENROLLMENT entity,
which is the resolution of the M:M
between STUDENT and CLASS.
30
Solution 10.2.13

STUDENT
Student / Class
M:M
Enrollment is the
intersection entity
CLASS
ENROLLMENT
#date
*grade
GRADE CHANGE
*old grade
*changed by
#date changed
o reason for change
31
Conventions Review
 Crows feet
 Crows fly East and South
 Divide complex ERD’s into functional areas
 Place Highest volume entities in upper left
corner
 Improve readability
 avoid criss-crossing lines
 increase white spaces so relationships don’t
overlap
 be consistent with font type, size, and styles
Marge Hohly
32
33
34
35
36
37
38
39
Generic Modeling
 Can reduce number of entities in diagram
 Can provide more flexibility in unstable
situations (where business requirements
change often)
 Use a more distant perspective
 Review Slide 3 & 4
 See next slide. What would happen to the
generic model if we had to add 10 new
ARTICLE types, each with their own
attributes?
Marge Hohly
40
41
Generic Modeling
 Have more attributes in fewer entities
 Many mandatory requirements/attributes
become optional
 Structural rules become procedural rules
 Example: PANTS waist size was mandatory,
with ARTICLE waist size becomes optional
 What other businesses would be good
candidates for generic modeling?
Marge Hohly
42
43
44
45
46
47
Relational Database Concepts
 Conceptual model transforms into a
relational database
 A relational database is a database
that is perceived by the user as a
collection of relations or twodimensional tables.
 Table, each employee (instances),
and each column (attribute)
Marge Hohly
48