DBMS Notes

mobiprep (6).png

Database Management System

mobiprep (6).png

Data Modeling

mobiprep (6).png

Database Architecture

mobiprep (6).png

Relational Model

mobiprep (6).png

Relational Algebra

mobiprep (6).png

Entity Relationship Model

mobiprep (6).png

Functional Dependencies

mobiprep (6).png

Normalization

mobiprep (6).png

Transaction And Concurrency Control

mobiprep (6).png

Deadlock

mobiprep (6).png

Files and Storage

mobiprep (6).png

Indexing

Heading

Q

1

Define Indexing. Why is it needed?

LRM_EXPORT_207556595493866_20190724_1939

Ans

Indexing is a way to optimize thr performance of a database by minimising the number of disk accesses required when a query is processed. KIt is a data structure techinque which is used to quickly locate and access the data in a database. Indexes are created using a few database columns.

LRM_EXPORT_207556595493866_20190724_1939

Q

2

What is Primary indexing?

LRM_EXPORT_207556595493866_20190724_1939

Ans

"Primary Index is an ordered file which is fixed length size with two fields. The first field is the same a primary key and second, filed is pointed to that specific data block. In the primary Index, there is always one to one relationship between the entries in the index table.

The primary Indexing in DBMS is also further divided into two types.

Dense Index
Sparse Index

Dense Index

In a dense index, a record is created for every search key valued in the database. This helps you to search faster but needs more space to store index records. In this Indexing, method records contain search key value and points to the real record on the disk.

Sparse Index

It is an index record that appears for only some of the values in the file. Sparse Index helps you to resolve the issues of dense Indexing in DBMS. In this method of indexing technique, a range of index columns stores the same data block address, and when data needs to be retrieved, the block address will be fetched.

However, sparse Index stores index records for only some search-key values. It needs less space, less maintenance overhead for insertion, and deletions but It is slower compared to the dense Index for locating records.

LRM_EXPORT_207556595493866_20190724_1939

Q

3

Define Secondary indexing.

LRM_EXPORT_207556595493866_20190724_1939

Ans

"The secondary Index in DBMS can be generated by a field which has a unique value for each record, and it should be a candidate key. It is also known as a non-clustering index.

This two-level database indexing technique is used to reduce the mapping size of the first level. For the first level, a large range of numbers is selected because of this; the mapping size always remains small. "

LRM_EXPORT_207556595493866_20190724_1939

Q

4

Define Clustered Indexing.

LRM_EXPORT_207556595493866_20190724_1939

Ans

In a clustered index, records themselves are stored in the Index and not pointers. Sometimes the Index is created on non-primary key columns which might not be unique for each record. In such a situation, you can group two or more columns to get the unique values and create an index which is called clustered Index. This also helps you to identify the record faster.

Example:

Let's assume that a company recruited many employees in various departments. In this case, clustering indexing in DBMS should be created for all employees who belong to the same dept.

It is considered in a single cluster, and index points point to the cluster as a whole. Here, Department _no is a non-unique key.

LRM_EXPORT_207556595493866_20190724_1939