File Organization Physical Database Design:
Definition and Explanation:
File organization is a technique for storing data on secondary storage devices
so that it can be retrieved easily when needed. It includes the physical order
and layout of records on secondary storage devices. The techniques used to write
and Retrieve data to and from the storage devices are called access methods. The
Operating System provides the basic access methods and performs the actual
input and output operations. On the other hand, DBMS requests to the operating
system to write and retrieve the records to and from the storage devices.
Data of an organization can be organized differently on physical storage media.
The database designer needs to understand the company requirements, several
constraints, including the physical characteristics of the secondary storage
available operating systems and file management software, and user needs for storing and accessing data before deciding which file organization method should
Following is the criteria for selecting file
- Fast access for retrieval.
- Efficient use of storage space.
- Protection from failure or data loss.
- Minimizing need for re-organization.
- Accommodating growth.
- Security from un-authorized use.
File Organization Methods:
Although there are many access methods, but in
field of database processing, two basic types of access methods are used. These
(ii) Indexed Sequential
(i) Sequential File Organization:
In sequential file organization, records are stored in a file in a sequence
according to the value of key field, called the sequence field. There is no
location of individual record. The records are stored on the storage device one
after the other by the value of sequence field. If you want to find out a
particular record, you need to scan all records one by one until the required
record is obtained. The magnetic tape is an example of sequential access
storage. In this storage, the computer accesses a specific record starting from
the beginning of tape up to the required record. It is also not an easy way to
insert a new record in the middle of the file. In sequential file organization,
insertion, deletion, and modification operations can be performed by rewriting
the entire file. It is very slow process for accessing records. The magnetic
tape was used in the early computers.
(ii) Indexed File Organization:
In this type of file organization, records can be stored sequentially or non-sequentially. The location of individual record is also stored along with data
of record. For this purpose, an index is created to keep the track of
locations of individual records. An index is just like a table that is used to
find out the location of a particular record. Indexed file organization is more
effective and fast in accessing than sequential file organization.
Indexed file organization is further divided into two types.
(a) Sequential Indexed:
In this method, records are stored sequentially, but primary key value is used
to create a simple index also called block, index. This technique is most
popular and commonly used for file organization.
(b) Non-Sequential Indexed:
In non-sequential indexed method, records are kept non-sequentially but a full
index or inverted index is created to find out the individual records. The data
in a relation of relational database is often stored using this technique.
According to a property of a relation; tuples of a relation are unordered from
top to bottom. It means that tuples in a relation do not have any particular
In a library system, books are not kept is sequence with Author names or title
of books. Instead, they are kept according to a catalog number (or computerized
index). The author and title are full indexes, which are further used to find
out a catalog number. This catalog number is then used to find the actual
position of book.