Thursday, 27 February 2014

Attribute Types of Database Tables

Attribute Types in Database Design

An attribute can be designed to store any values in it according to a particular domain (Domain would mean a set of permitted values. For example, in Phone attribute we are about to store set of phone numbers, in Name attribute of STUDENT table, we are about to store set of Student Names and not any other names) for which the attribute is designed. Apart from various domains, an attribute can be of many types like an attribute with one value, attribute with many values, and so on. Based on this semantics of an attribute, we can derive the following set of attribute types.

1. Simple attributes

Attributes whose value is simple, indivisible values. For example, attribute Salary is simple if salary stores single number as salary. Attribute Department_Name may store single name as value for every record.
* In ER diagram, simple attribute is represented with single ellipse.

2. Composite attributes

Attributes whose value is composite, i.e which can be further divided into meaningful component attributes. For example, according to the user requirement, if the Salary attribute needs to store all the information like Basic Salary, HRA, DA and any other allowances, then salary is a composite attribute with Basic_Salary, HRA, and DA as its component attributes.

 * In ER diagram, composite attributes are represented using links between the composite and component attributes. Here, each attribute is represented with an ellipse.

3. Single-valued attributes

Attribute which accepts only one value for every record is called single-valued attribute. For example, all the employees of any organization will be assigned with single employee number value. Hence, Emp_No attribute is single-valued.
* In ER diagram, single-valued attribute is represented with simple ellipse.

4. Multi-valued attributes

Attribute which may expect one or more values for every entity is called multi-valued attribute. For example, an organization while storing employee details may accept more than one phone number per employee.
* In ER diagram, multi-valued attribute is represented with a double ellipse.

5. Derived attributes

An attribute whose value can be derived from another attribute of the same table or from a set of entities is called Derived attribute. For example, in a table CAR_OWNERS which stores information about the car owners with every car’s individual information, we could include an attribute which is the count of all the cars owned by single owner. As another example, consider the DOJ (Date of Joining) of employee. The employment length of any employee can be calculated from the attribute DOJ’s value.
The value of Derived attribute need not be stored. The reason would be the dynamic nature of the attribute. For example, every year the Age of an employee will change.
* In ER diagram, a derived attribute is represented using dashed ellipse.

6. Descriptive attributes

An attribute which is used for storing information which is part of any relationship set is called descriptive attribute. For example, in a many-to-many relationship set Depositor (which relates the entity sets Customer and Account), we may include an attribute Last_Access_Date to record the last access of the account by the customer. This attribute will be part of table Depositor (which is reduced from relationship set to table).

No comments:

Post a Comment

SQL exercises for beginners one

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