Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is Derived Attribute in DBMS?
3.
Example of Derived Attribute
4.
Representation of the Derived Attribute
5.
Difference between Derived and Stored Attributes
6.
Features of Derived Attributes
7.
Implementing Derived Attributes in SQL
8.
Limitations of Derived Attributes
9.
Frequently Asked Questions
9.1.
What is derived vs non derived attributes?
9.2.
What are derived attributes?
9.3.
Which of these is an example of a derived attribute?
9.4.
What are derived key attributes?
9.5.
What are derived attributes in ER diagram?
10.
Conclusion
Last Updated: Mar 31, 2024
Medium

Derived Attributes in DBMS

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

In DBMS (Database Management System), various attributes are present in the table. These attributes describe the data present in the entity. To get these data from the database, we use SQL language. SQL stands for Structured Query Language. It is a database language mainly designed to maintain data in RDBMS(Relational Database Management System). 

derived attribute

In this article, we will learn about the Derived Attributes in DBMS. We will implement derived attributes in SQL and will understand this through examples.

What is Derived Attribute in DBMS?

Derived attributes are those whose values are derived from other attributes in the database. It depends on different fields and is not directly stored in the database. 

The data in the derived attribute are generated or copied from the attributes whose values are stored in the database.

The values in these attributes vary and change according to the query on them. In the ER model, i.e., entity relationship model-derived attributes are represented through oval design.

The values calculated in the derived attributes, if not stored in the database, are stored in the storage space. Here storage space refers to the space somewhere on your disk. 

Also read about - Attributes in DBMS

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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:

OrderIDProductIDQuantityUnitPriceTotalPrice (Derived Attribute)
11012$10$20
21021$15$15
31033$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 AttributeStored 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:

Features of Derived Attributes
  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);
Code

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;
Code

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;
Output

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;
Output

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!

Live masterclass