Calculated Fields required for User Views

In SQL, a calculated field^[1] (also called a computed or derived column) is a field whose value is dynamically calculated at runtime based on other columns or expressions, rather than stored explicitly in a database table. Since tables themselves typically store static data, calculated fields are usually implemented in user-defined views.
Why use Calculated Fields in Views?

• Dynamic Data: Calculated fields provide real-time, updated results without physically storing redundant data.
• Data Integrity and Efficiency: By calculating fields in a view, you avoid redundant data storage and simplify maintenance.
• Encapsulation: Using views to present calculated data encapsulates complexity from end-users, improving readability and usability.

Creating a Calculated Field in a View:
A user view is essentially a stored query that presents data from one or multiple tables. To define a calculated field in a view, you typically use arithmetic operations, SQL functions, conditional logic (`CASE` statements), or even aggregate functions.
Here's an example of a SQL view with calculated fields:

CREATE VIEW StudentInvoiceSummary AS
SELECT
    student_id,
    student_name,
    course_fee,
    activity_fee,
    (activity_fee + course_fee) AS total_fee  -- calculated field
FROM
    student_fees;

In this example:

• total_fee is a calculated field that sums activity_fee and course_fee dynamically every time the view is queried.
• It doesn't physically exist in the database; it's computed at runtime when the view is accessed.

Using Complex Calculations: Calculated fields in views aren't limited to arithmetic operations; they can also include functions, conditional statements, aggregations, or SQL functions.

CREATE VIEW StudentInvoice AS
SELECT
    student_id,
    first_name,
    last_name,
    invoice_date,
    payment_due_date,
    DATEDIFF(day, invoice_date, payment_due_date) AS days_until_due -- calculated field
FROM
    invoices
JOIN
    students ON invoices.student_id = students.student_id;

In this second example:
- The calculated field `days_until_due` dynamically computes the number of days until a payment is due each time the view is executed.
Advantages of Calculated Fields in Views:

• Data is always up-to-date since it’s calculated on demand.
• Reduces errors from manual calculations or data redundancy.
• Simplifies database schema management.

Important Considerations:

• Performance might be impacted for complex calculations or queries involving many joins.
• Indexes cannot directly be created on calculated fields within views. To index calculated fields, consider using indexed views or persisted computed columns in tables, if supported by your database system (e.g., SQL Server).

In summary, calculated fields within SQL user views offer a dynamic, efficient, and convenient way to derive and present data, encapsulating logic to simplify user interactions and maintain data integrity.

The tables actually stored in a database (called base tables) cannot have any type of calculations built into their fields. The reason for this is simple: every field in a base table uniquely and independently stores data about the subject of its table. If calculation were permitted in base tables, then the data stored in some fields would depend for their values on data stored in other fields.
User views, however, store their data in virtual tables^[2] that consist of fields loaded into computer memory from base tables. Virtual tables themselves are not stored in the database; rather, the definition of the view is stored and given a name. Users call up that name, and the view is created (from base tables) on the fly. When a user closes the view, it disappears from memory, only to be recreated the next time its name is invoked. Users call up that name, and the view is created (from base tables) on the fly. When a user closes the view, it “disappears” from memory, only to be recreated the next time its name is invoked.

• User view allows Calculations
  A user view (like a database application) allows calculations to be performed on the data contained in its fields. That is because these field calculations within virtual tables have no effect on the data stored in the base tables. One significant reason to create user views, then, is because, through calculations, views can deliver extremely useful information. The next lesson describes the documents created during Requirements Analysis.

Before moving on to the next lesson, click the Exercise link below to check your understanding of calculated fields.
Calculated Fields - Exercise

[1]calculated fields: In database design, calculated fields are virtual columns in a view that derive their values from other columns using formulas or expressions. They provide dynamic, on-demand results without storing redundant data, ensuring information is always current.

[2]virtual table: A table stored in the computer’s memory.