Derived Attributes in DBMS

Example of Derived Attribute

A common example of a derived attribute is "Age" in a database containing birthdates. It's calculated by subtracting the birthdate from the current date when needed, eliminating the need to store it explicitly. Derived attributes save storage space and ensure accuracy by avoiding redundant data entry.

Let's consider one more example of hypothetical database table for an e-commerce platform:

OrderID	ProductID	Quantity	UnitPrice	TotalPrice (Derived Attribute)
1	101	2	$10	$20
2	102	1	$15	$15
3	103	3	$8	$24

In this example:

• OrderID: Unique identifier for each order.
• ProductID: Unique identifier for each product.
• Quantity: The quantity of each product ordered.
• UnitPrice: The price per unit of the product.
• TotalPrice: This is the derived attribute. It represents the total price of the order, calculated by multiplying the quantity of each product by its unit price.

Explanation:

For each row in the table, the TotalPrice is computed by multiplying the Quantity by the UnitPrice. For example, in the first row, the TotalPrice is $20 because 2 units of ProductID 101 were ordered at $10 each. Similarly, in the second row, the TotalPrice is $15 because 1 unit of ProductID 102 was ordered at $15.

The TotalPrice attribute is derived from the Quantity and UnitPrice attributes, and it is calculated dynamically when required rather than stored explicitly in the database. This approach ensures data consistency and reduces redundancy in the database.

Representation of the Derived Attribute

A derived attribute is typically represented in a data model or database schema by specifying its derivation rule or calculation method. This rule or method explains how the derived attribute is computed from other attributes within the schema. It serves as a reference for database management systems to calculate the derived attribute's value when queried or requested. This representation ensures that the derived attribute remains consistent and up-to-date in relation to the attributes from which it is derived.

Difference between Derived and Stored Attributes

Derived Attribute	Stored Attribute
Computed or calculated based on other attributes.	Explicitly stored in the database as provided.
Not physically stored but calculated when needed.	Physically stored in the database with its value.
Depends on other attributes for its value.	Does not depend on other attributes for its value.
Age calculated from birthdate.	Name or address stored as entered.

Features of Derived Attributes

The features of derived attributes are:

1. The values in derived attributes are dependent on the other attributes.
    
2. They require no maintenance as no values are present in them. 
    
3. Oval design is the representation of these attributes.
    
4. The values in these attributes vary in nature, i.e., they are not stored and calculated during the runtime.  
    
5. If the data is not stored in the relational database, it gets stored in the storage space. 

Must Recommended Topic, Schema in DBMS

Implementing Derived Attributes in SQL

Derived attributes in SQL can be calculated using SQL functions like TIMESTAMPDIFF. Let’s understand this through an example.

We have an employee table with information about the following:

Empid: ID of the employee

Ename: Name of the employee

Joindate: Joining date of the employee

yearsincompany: Years for which the employee is working for the company. 

Query to create a table

create table employee(empid int, ename varchar(20), joindate DATE, yearsincompany int);

The yearsincompany is a derived attribute that will be calculated on the basis of other attributes. In our case that is joindate. 

We have inserted a few records in our table:

Insert Query:

insert into employee(empid,ename,joindate) values(101, 'Aman', '2016-01-15');

Likewise, we have inserted a few other records:

Let’s now check the data present in our table:

Select Query:

Select * from employee;

As you can see, there are no values in the column yearsincompany. 

To generate value in this derived attribute column, we have used the SQL function TIMESTAMPDIF. We will be using this function in the MySQL command line. You can also use the below command in the MySQL workbench.  

Query:

Select TIMESTAMPDIFF(YEAR, JOINDATE, CURDATE()) as yearsincompany FROM employee;

To see the complete table: Run the below command on the MySQL command line or MySQL workbench.

Query:

select  EMPID, ENAME, JOINDATE, TIMESTAMPDIFF(YEAR, JOINDATE, CURDATE()) as yearsincompany FROM employee;

Check out this article - File System Vs DBMS

Limitations of Derived Attributes

You should limit the use of derived attributes in your table. Various limitations of derived attributes are as follows:

• Derived attributes are calculated during the runtime of the query execution, which automatically results in a higher accessing time of CPU and data loading. 
   
• It adds coding complexity to the queries to calculate their values and reduces the performance of the database. 
   
• Accessing values through derived attributes takes a longer time to perform. 
   
• They are not directly stored in the database and depend on other attributes.

Frequently Asked Questions

What is derived vs non derived attributes?

Derived attributes are calculated from other attributes, while non-derived (stored) attributes are explicitly stored in the database as provided.

What are derived attributes?

Derived attributes are attributes whose values are derived or calculated from other attributes in a database. For example, in an e-commerce system, TotalPrice can be a derived attribute computed from Quantity and UnitPrice.

Which of these is an example of a derived attribute?

An example of a derived attribute is TotalPrice in an e-commerce database, calculated by multiplying Quantity and UnitPrice.

What are derived key attributes?

Derived key attributes are attributes whose values are derived from a combination of other attributes, often used as keys in a database.

What are derived attributes in ER diagram?

Derived attributes in an Entity-Relationship (ER) diagram are depicted with dashed lines, indicating that they are not physically stored but rather calculated from other attributes.

Conclusion

In this article, we discussed about Derived Attributes in DBMS. Derived attributes play a crucial role in database management systems (DBMS) by providing dynamic and calculated information based on existing data. They enhance data integrity, reduce redundancy, and facilitate efficient data retrieval and analysis.

To learn more about these, please refer to our blogs:

Data Integrity Constraints in DBMS

Relationships in DBMS

DBMS vs. RDBMS and DBMS vs. file system

Also check out the Interview guide for Product Based Companies as well as some of the Popular interview problems from top tech companies like Amazon, Adobe, Google, Uber, Microsoft, etc.

You can also consider our Database Management Course to give your career an edge over others.

Happy Coding!