Database Design

1
Database Design

• Sections 8 12Modeling Historical Data,
  conditional nontranferability, time-related
  constraints

2
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

3
Change and Time

• Every update means loss of information.
• Time in your model makes the model more complex.
• There are often complex join conditions.
• Users can work in advance.

4
Entity DAY or Attribute Date

• Single attribute entity without M1 relationships
  is usually replaced by attribute

5
Entity DAY

6
Modeling Change

7
Modeling Change

COUNTRYname start time end time
EMPLOYEE id
of
in
life cycle attributes
as
for
ASSIGNMENT start date o end date
Even a Country has a life cycle
8
Example 8.1.6

JEWELRY PIECE code descriptionrental
rate insured value
MOVIE STAR id first name last name
rented by
renting
9
Possible Solution 8.1.7

10
8.1.8

11
Review 8.1.10

12
Entity DAY vs. Attribute Date

• Example 10.1.4
• Why cant you just add the attribute for high
  temperature and low temperature to the PURCHASE
  entity?

13
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?

14
Modeling time 10.1.5-10.1.6

• 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.

15
Review 10.1.6

• What are some other rules?

16
Example 10.1.8

17
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

18
Review 10.2.3
19
Price with time

• Price may fluctuate with time
• Examples
• Value of Gold or Silver
• Real Estate
• Value of currency

20
Price with Time

PRODUCT id name
have
Price Priced Produce Historical Price
for
PRICE start date price is o end date
21
10.2.9
22
10.2.13

• When a students 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 MM between STUDENT and CLASS.

23
Solution 10.2.13

Student / ClassMMEnrollment is the
intersection entity