Sequential File Organization and Access in DBMS

Sequential File Organization is a basic technique employed in a database management system DBMS for storing and retrieving data. It is mostly applied when data access is sequential or in a particular order.

In this article, we will describe what sequential file organization is, define some fundamental terms about the method, depict a diagram of the sequential file organization, and give an insight into the process of accessing data sequentially in the method.

Key Terminologies

• File Organization: The way a file's records are kept. It chooses how and in what sequence data is updated, accessed, and stored.
• Record: a group of connected data fields handled collectively. Every record is a single entity or item of data.
• Key field: A record's key field is a particular field that is used to identify and arrange the records. The key field is used to establish the record order in sequential file organization.
• Access method: The process of retrieving and modifying records from a file is called the "access method." The access technique accesses records in sequential order depending on the key field in sequential file organization.

Sequential File Organization and Access in DBMS

Sequential File Organization

Sequential File Organization is another way of filing records where records are arranged one after another in a sequence by putting them in the order of the particular field or key. Programs that involve record series, such as batch processing or report generation, could be optimized in this structure. There are two main types of sequential file organization: Pile File Method and Sorted File Method.

1. Pile File Method

The Pile File Method is the simplest form of sequential file organization. ;No particular order is followed; records are saved in the order they are received. The more records there are, the less effective this strategy can be, despite its simplicity and ease of implementation.

Characteristics of Pile file method

1. At the end of the file, new records are added.
2. There is no particular order maintained.
3. Scanning the entire file is necessary while looking for a specific record.
4. Records deletions might result in gaps.

When inserting a new record in the Pile file method the new record gets directly added to the end of the file whether it is in sorted order or not.

The appended record R3 will now be available at the end of file:

2. Sorted File Method

The Sorted File Method ensures that the entries are sorted by arranging them in file with reference to a key field. This method enhances search flexibility and fast accessing of data since records can be searched faster in a sorted way. But to place things in this manner, may not be easy especially when inserting or deleting certain records.

Characteristics of Sorted file method:

1. A key field is used to determine the order in which records are stored.
2. Because order must be maintained, insertion and deletion procedures might be more complicated.

When a new record is inserted as shown in the figure below:

The new record gets inserted at a position such that the all the records should continue maintaining their sorted manner as shown below:

Sequential Access in DBMS

Reading or writing records in the order that they are stored is known as sequential access. The general sequential access procedure is explained in the subsequent steps:

• File Opened: The records-containing file is opened.
• Initialize Pointer: The beginning of the file is the pointer's initial value.
• Read/Write Records: Each record is read or written sequentially, one after the other.
• Check for End of File: Following the processing of each record, an attempt is made to see if the file's end has been reached.
• Close the File: The file is closed after all activities are finished.

Example : Consider a database table called Inventory that contains the "product_id", "product_name", and "quantity_in_stock" data. You must create an inventory report that includes a list of every product and its quantity.

Steps for Sequential Access

• Open the Inventory Table: To write the data to the file, the file pointer needs to be set to the start of the file; the Inventory table is opened to this end.
• Read every product record: Read each record one by one in the sequence they are stored.
• Extract Data: The identified attributes to select are the product name, the product id, and the quantity in stock according to the defined record.
• Procedure for Report: Create a report of stock-in with the details of the current stock of each of the products in your list.
• Proceed Until File End: Thus, to make it possible for each of the products to be processed during the report, the reporter should move through the Inventory table repeatedly to check on all the records in the table.

Advantages

• Straightforward and simple to put into practice.
• Effective for applications that need to process all records in a certain sequence, or a significant subset in a specific order.
• Perfect for systems that use batch processing.

Disadvantages

• Ineffective for random access since it could take a lot of readings to locate the necessary record.
• If the order is to be preserved, adding new records necessitates rewriting the file.
• Periodic reorganization is necessary since the deletion of records may generate gaps.

Conclusion

Sequential File Organization is a clear and efficient means of putting data into a sequence file because it allows the data to be stored and retrieved in a specified manner. Thus, it can be from time to time convenient in cases where the sequential access of data is required; however, it is not a choice that end-users should make for applications that are going to write or read data at random. This knowledge will enable the identification of the right file organization technique for particular database applications by understanding how the technique of sequential file organization operates and the parameters that define this kind of technique.