RelationalDBDesign
RelationalDBDesign

Relational Constructs
«Prev
Next»

## Calculations and Domain Types for Database Systems

### Choosing Data Types

### Calculation Domain - Exercise

Lesson 8 | Calculations and domain types |

Objective | List domain types that support calculations. |

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

The domains that enable you to perform calculations on their contents are

- INT,
- DECIMAL,
- DATE, and
- 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.

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:

- As an INT, in which case the result would be 29 (29.75 with the decimal truncated)
- 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]}.

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

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

Calculation Domain - Exercise

Calculation Domain - Exercise

[1]*User views:* Saved queries created with SQL.

[2]*null value*: An unknown value in a column or field.