what is derived attribute in dbms

What is derived attribute in DBMS? How would we create table for a derived attribute? How the values for derived attributes are inserted? Define derived attribute, Derived attribute detailed examples

Derived attributes

In ER model, derived attribute is a type of attribute where the value for that attribute will be derived from one or more of the other attributes of the same entity set.

Consider the following entity set for example;

Employee Entity set with derived attribute Experience

In this entity set Employee, Experience is a derived attribute (represented as dashed ellipse). That means the value for Experience will be derived from one or more of the other attributes, in our example, the other attribute is DOJoin (Date of join) attribute.

What does that mean? How would we include the derived attribute in a table? Do we need not enter value for Experience attribute while we insert records into the Employee table? Or, the value will be automatically calculated and stored?

Not exactly. The concept of derived attribute is not about storing values, but about calculating the values. For example, consider the following record of Employee table;

(‘E101’, ‘Ramkumar’, ’10-Feb-2001’, 0)

This record shows that the employee Ramkumar joined on 10^th of February, 2001. At the time of record insertion, it is zero years of experience. What about the value of the experience on ’10-Feb-2004’? It is about 3 years. It clearly shows that the value is not fixed and it is variable whenever the DOJoin is adjusted.

So the solution is, we are not creating our table with derived attributes in it. Instead, the values can be calculated at the time of retrieval and shown to the user. Hence, our record will look like as follows;

(‘E101’, ‘Ramkumar’, ’10-Feb-2001’)

One solution to calculate the experience value is by creating database views. See the example below;

CREATE VIEW v as SELECT Emp_No, EName, DOJoin, MONTHS_BETWEEN(sysdate, DOJoin)/12 as Exp;

The statement will create a view named v. The idea behind the concept view is, every time you access something through the view v, the query is freshly executed and what you get as the result is the correct experience.

Look at the example data below;

The result for “SELECT * FROM Employee;” is as follows;

Emp_No	EName	DOJoin
E101	Manoj	’10-Mar-2001’
E102	Steve	’20-Mar-2005’
E106	Mary	’21-Feb-2008’

Table: Employee (the records are physically stored)

The result for “SELECT * FROM v;” is as follows; (on a date ’25-Mar-2009’)

Emp_No	EName	DOJoin	Exp
E101	Manoj	’10-Mar-2001’	8
E102	Steve	’20-Mar-2005’	4
E106	Mary	’21-Feb-2008’	1

Table: Result of the above query (the records are generated and not physically stored)

The values for Exp column is derived from the DOJoin attribute against the date at which the query is executed.

******************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page