Computed Values
Database Essentials - Part 11
Division 1
Forward: In this tutorial I explain how to handle computed values of a database table.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Open the following link in a new Tab Window so that you can be toggling between the new tab and this tab as you are reading (link below).
The opened tab window shows a Sale Item table. I assume that you have read the previous parts of the series so as to easily understand this tutorial.
Illustration
The last column is empty. The last column is the multiplication of the unit price column and the quantity column. So, for these two columns in each row, you multiply the value of the unit price with that of the quantity and you put the result in the Total column in the same row. A Total column value is an example of a computed value.
A computed value in a column is a value, which is mathematically (arithmetically) derived from a value of another column or values from other columns. Another example of a computed column can be found in an employee table where the date of birth of each employee is given. In that same table, you can have a column for the age of each employee. The age column would be a computed value. The age of each employee would be computed by subtracting his date of birth from the date today.
How is the value computed? It is not the user (input user) of the computer database that computes the value. You the database designer or someone else writes a computer program that computes the value. Is the computed value saved? Generally the computed value is not saved in the hard disk along side the other entries (ID, unit price, etc.) of the table. The program written would produce the computed values, when the manager (boss) or some other important personnel needs to analyze the table. The non-computed table data are saved with the table in the hard disk.
As you can see from the previous paragraph, it is not necessary to produce the computed value as a user is inputting the other values of the rows of the table. This is to allow the tables to be as simple as possible. Whenever you need the computed value, the program written, will calculate it for you. So computed values are normally not saved. However, if you really think that you need computed values as the user is inputting data for some of your tables and to save the computed values along side the rest of the table data, then write a program, which would do that. Again, that is not popular (not advisable).
Representation in a Diagram
The diagrams I have been using in these tutorials are called UML diagrams. The web page opened above, shows how computed column (values) is represented in UML diagrams. As you design, you draw your UML diagrams. At the design stage you indicate which data attributes (columns) should be computed.
The UML notation is to precede the name of the attribute with a forward slash, and then describe the computation in a note. The note is displayed as a box with folded corner. It is connected to the property (attribute) with a dash line. See the diagram.
That is it for this part of the series; we stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT