# Modeling Change Over Time

## Change Over Time: Objectives

This tutorial covers the following objectives:

• Modeling change over time: Distinguish between using date as an attribute and DAY as an entity in a data model, depending on business requirements
• Solve the problem of keeping characteristics of a date by constructing a model that uses DAY as an entity
• Identify at least three time-related constraints that can result from a time-sensitive model
• Define and give an example of conditional nontransferability in a time-constrained model

## Purpose of Modeling Change Over Time

• Time plays a role in many business models.
• Historical data is often used by businesses to find trends that can point the way to more efficient ways of doing business.
• Modeling time in a business allows such data to be captured.
• Reports provide information that can be derived from the data.
• A well-designed report can provide valuable information that the business can use to improve its operations.

Examples in your school where time is something of importance about which the school needs to keep information, for example:

• Monthly reports on student attendance that determine funding in some schools.
• Late arrivals at school can be recorded to determine absences.
• Lunch schedules.
• Types/quantities of food.

## Entity DAY vs. Attribute Date

• Consider the entity PURCHASE.
• You would include an attribute “date” if you wanted to know when the item was purchased.
• However, if we want to identify trends — such as purchasing coats vs. bathing suits vs. sneakers — we may want to know the temperature during that time.
• If we add the temperature attributes to the PURCHASE entity it creates a problem.
• Remember Third Normal Form: a non-UID attribute cannot have attributes of its own.
• Because high and low temperature are attributes of the date, we need a separate entity DAY.

This would allow us to create reports detailing which products were sold on warm days and on cooler days, allowing trends to be identified.

• Having a separate DAY entity allows us to track more information that may be useful to a business, for example which days were public holidays.

## Change Over Time Constraints

• Be aware of constraints that can result from the need to track dates and times.
• Here is an example:
• Consider a school fair that features several booths.
• The manager signs up volunteers to work different shifts at different booths.
• A booth is staffed by only one volunteer at a time.
• 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.
• Here is a selection of time-related constraints that need to be considered for this model:
• The obvious one: shift “end time” must be later than shift “start time.”
• Shift times may not overlap.
• The “start time” for a shift for a volunteer may not be between any “start time” and “end time” of another volunteer on the same booth.
• The same is true for the “end time.”

Although the time-related constraints seem obvious in real life, they must be enforced by programming logic in the database. Therefore, they must be documented.

## Conditional Non-transferability

• The “start time” for a shift may be updated to a later time, unless the shift has already begun.
• You probably would not allow a shift to be reassigned to another volunteer or another booth, unless the shift had not yet started.
• This is an example of conditional nontransferability.
• Non-transferability: a SHIFT ASSIGNMENT cannot be changed to another BOOTH (or to another VOLUNTEER).
• Nontransferable relationships are represented by a diamond in the ERD.
• Conditional nontransferability: a SHIFT ASSIGNMENT can sometimes be changed – in this case, if the shift has not yet started.
• These relationships cannot be represented in the diagram, but must still be documented.

## Terminology

Key terms used in this tutorial included:

• Conditional non-transferability
• Non-transferability
• Change over time constraint

## Summary

In this tutorial, you should have learned how to:

• Distinguish between using date as an attribute and DAY as an entity in a data model, depending on business requirements
• Solve the problem of keeping characteristics of a date by constructing a model that uses DAY as an entity
• Identify at least three time-related constraints that can result from a time-sensitive model
• Define and give an example of conditional nontransferability in a time-constrained model