Types of Data Access in database

Types of Data Access in database (in view of parallel database data access)

In any relational databases the term Data Access involves reading an entire relational table. This is treated as the only kind of access to data stored in a table. For example, a query for reading (SELECT queries) a table for some values needs to read entire table, if we do not have indexes on those attributes mentioned part of WHERE condition. This type of access can further be classified into the following in view of executing parallel queries;

1. Scanning the entire relation

It means searching for a particular value in a table may need to search in all the records of the table.

Eg_1: SELECT * FROM Employee WHERE EName = ‘Raman’;

Eg_2: SELECT * FROM Employee ORDER BY Phone_No;

The query in Eg_1 needs to access entire relation in searching for the EName value ‘Raman’, if EName is a non-key attribute. When it is a non-key attribute, there may be duplicate values stored in the column EName, i.e, there may be more persons with the same name.

If EName is a key attribute, this query need not scan entire table. When EName is a key, then, only one such value (in our case ‘Raman’) presents in the whole table for the column EName. But, there are two possible cases in accessing the data;

Best Case            : Possibilities for the required data available in first few disc blocks accesses.

Worst Case         : Required data might be available in the last few disc blocks of the table.

The query in Eg_2 requires scanning the entire relation. It actually sorts the data on the attribute mentioned in ORDER BY clause. The result is going to be the entire table in ascending order of Phone_No attribute.

2. Point Queries

It involves associating the particular attribute with a particular value.

Eg_3: SELECT * FROM Employee WHERE Phone_No = 9998881234;

In Eg_3, we are looking for the employee details whose phone number is mentioned in the WHERE clause. This type of query is called as Point Queries (Eg_1 is also a Point Query). Based on the availability of Indexes, it may need to scan entire relation (as discussed for Eg_1).

3. Range Queries

The queries used for locating all the records for which the value of the column specified in the WHERE condition falls in a specific range is called Range Queries.

Eg_4: SELECT * FROM Employee WHERE Age BETWEEN 30 and 45;

This query retrieves all the records whichever satisfies the range 30 to 45 for Age attribute. Again, based on the type of attribute this query may scan entire relation (just same as the above discussion for Scanning the entire relation).