Calculations and Domain Types for Database Systems

In a relational database management system (RDBMS), certain data types or domain types are more suitable for performing calculations on the stored data. The following domain types are commonly used for calculations within a table of a relational database:

1. Numeric data types: These data types are designed to store numerical values and support a wide range of arithmetic operations, including addition, subtraction, multiplication, division, and modulus. Common numeric data types include:
   
   1. INTEGER (or INT): Represents whole numbers, both positive and negative. It can be further categorized as SMALLINT, INTEGER, and BIGINT, depending on the storage size and range of values.
   2. FLOAT and DOUBLE: These data types store approximate numerical values with floating-point precision, suitable for scientific and real-world measurements where absolute precision is not required.
   3. DECIMAL (or NUMERIC): This data type represents exact numeric values with a fixed number of decimal places, making it ideal for financial calculations, where precision is crucial.
2. Date and time data types: These data types allow for the storage and manipulation of date and time values. You can perform calculations on these types, such as adding or subtracting intervals, determining the difference between two dates, and extracting specific components (e.g., day, month, or year). Common date and time data types include:
   
   1. DATE: Represents a date without a time component.
   2. TIME: Represents a time without a date component.
   3. TIMESTAMP: Stores both date and time components, allowing for more complex calculations and comparisons.
   4. INTERVAL: Represents a duration or span of time, used for arithmetic operations involving date and time data types.
3. Boolean data types: Although not primarily used for calculations, Boolean data types store true or false values, and can be used in logical operations such as AND, OR, and NOT, which can be helpful in filtering and conditional evaluations.

While these domain types support calculations in a table of a relational database, it is essential to choose the appropriate data type based on the nature of the data and the required operations to ensure data integrity, efficiency, and accuracy in 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

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:

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

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.