RelationalDBDesign RelationalDBDesign

Relational Constructs   «Prev  Next»
Lesson 8 Calculations and domain types
ObjectiveList domain types that support calculations.

Calculations and Domain Types for Database Systems

An advantage of using non-text data domains for columns in tables is that your RDBMS can perform calculations based on the contents of the columns. For example, if you want to calculate the cost of an order where the customer had a 15% discount coupon, you can create a field to store the discount and multiply the total retail cost of the order by .85 (1 - .15).
The domains that enable you to perform calculations on their contents are

  1. INT,
  3. DATE, and
  4. TIME.

Many relational database management systems also will allow you to perform calculations on DATETIME columns. Yes, it is possible to subtract one date from another and to calculate the number of days between them. This is a handy ability when you want to determine, for example, the length of time between an order’s placement and its shipment. If an order were placed on 03/07/00 and shipped on 03/21/00, the difference of 14 days could indicate that the item ordered was out of stock and required a special order from the producer.

Choosing Data Types

Pay careful attention to which data type you use in your calculations. For example, if you have an INT value of 35 and a DECIMAL value of 5.25, you can handle the result in two ways:
  1. As an INT, in which case the result would be 29 (29.75 with the decimal truncated)
  2. As a DECIMAL, in which case the result would be 29.75

Always consult your RDBMS's documentation to determine the system requirements for handling cross-domain calculations.
Every field in a base table must uniquely and independently store information about the table subject.
Because calculated fields derive their values from other fields in a base table, you should never include them in a base table. Instead, you should only put them in user views[1] described later in this course. User views specify which users are permitted access to specific data in a database. The next lesson describes null values[2].

Calculation Domain - Exercise

Before you move on to the next lesson, click the Exercise link reinforce your understanding of calculated domains.
Calculation Domain - Exercise

[1]User views: Saved queries created with SQL.
[2]null value: An unknown value in a column or field.