DBMS | File Organization

In this article, we are going to discuss about the file organization, methods of organising a file, introduction of indexing and types of indexing in database management system.
Submitted by Prerana Jain, on July 21, 2018

File Organization

A drawback of normalization is that the number of tables increases in the database and for a simple query some time more than 2-3 tables are required which makes the system slow.

In general database file are very large which may acquire a large number of blocks because of which file access will be slow.

Methods of organising a file in database

There are mainly two methods of organizing a file in the database file system:

1. Sorted file

In this the records of a file are sorted according to some column or field it means fields unsorted with respect to other field or column.

Advantages: Access is very fast as it supports training search.

Disadvantages: Maintenance is costly as insertion and deletion require a number of swaps.

2. Unsorted file

Here, the records are ordered randomly or generally in the order in which they are inserted.

Advantages: Maintenance is easy.

Disadvantages: Access is slow as it requires the linear search.


Indexing is a secondary or alternative method to access the file in a time efficient manner. Indexing can be classified either on sorted or unsorted file or single level or multilevel indexing or sparse or dense indexing.

Structure of index file

Index file consist of two field.

Structure of index file

Search key: Field of file on which index is created.

Block pointer: Pointer to block where key is available.

Types of indexing

Indexing can be classified on the basis of the number of entries in the index and on the basis of the field of a file used for indexing.

  1. Primary Indexing
    Either for sorted or unsorted main file if we design an index file then it will always be sorted. Index file has mainly two columns search key and pointers (either block or recured pointer). Indexing will be done on the primary key. The number of entries in the index file is the number of blocks acquired by the main file. The main file sorted with the primary key. Primary indexing is an example of sparse indexing.
  2. Clustered Indexing
    In this, the indexing is on non-key. The number of entries in the index file is the number of unique values of the non-key attribute in the main file. It is an example of sparse as well as dense indexing. The main file is sorted with respect to non-key.
  3. Sparse Indexing
    When all records of the main file do not get an entry in the index file than it is called sparse indexing. If an entry in the index file is created for some search key value.
    The number of entries in the main file is not equal to Number of entry in the index file.
  4. Dense indexing
    When every value of the attribute on which indexing is done gets an entry in an index file than it is called dense indexing. If an entry in the index is created for every search key value.
    Number of entry in index file = Number of database records.
  5. Secondary Indexing
    The main file is sorted it can be applied on key on non-key. Example of dense indexing as every records pf gets an entry in the index file (Number of entries in the index file is same as of the number of entries in the main file.

Related Tutorials


Comments and Discussions!

Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.