Sunday, 19 February 2017

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 10th 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.


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









2 comments:

  1. What to do for cgpa calculation from gpa

    ReplyDelete
  2. Hence it becomes the foremost duty of an administrator to protect the database by constantly monitoring the functions and look for any loophole that will benefit the attacker.oracle dashboards

    ReplyDelete

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...