Flat File Database System

Introduction

This article is fully based on how to query a flat file database (in other words text file database). The flat file database querying is quite difficult. I write this article to create a simple application with the ability to manipulate the flat file database (in other words text file).

System Analysis

Flat File Database

This is a basic database system. It makes our work easier compared to writing the data on paper. It saves paper. A flat file database is the simplest form of database system. There is no capability of accessing multiple tables like a RDBMS Because it uses a simple structure, a text file is considered as a table. Every line of the text file is a row of the table and the columns are separated by delimiters like comma (,), tab or some special character. The database does not have a specific data type. It only supports strings.

System Design

The system based on File Input Output processing and Streams. This system is also the same as a flat file database except we can use multiple tables. The similarity is the multiple table querying such as Join queries are not processed currently.

Database

A database is nothing but the folder for the database. The Name the application's startup folder.


Tables

In this system the tables hold all the data. In this System the table structure has the following two parts:

1. Header Row
2. Data Rows

A Header Row consists of the column names. Data Rows consist of the records related to the columns. Here some special symbols are used as delimiters.



Symbols

├: Header Row Begining
┤: Header Row End
└: Data Row Begining
┘: Data Row End
│: Column Separator

Figure: Overview of System

Querying

The system supports 15 or more SQL Type Queries to query the database. The following queries are used in this system.

1) CREATE DATABASE StudentDB

This is the query for creating the database named "StudendDB".

2) LIST DATABASES

This query shows a list of databases in the system.

3) DROP DATABASE StudentDB

This query deletes the database StudentDB.

4) USE StudentDB

This query holds the StudentDB database name for further database manipulation quries such as INSERT, DELETE, UPDATE, SELECT and so on.

5) LIST TABLES

This query shows the tables in the database that are currenlty held for manipulation.

6) DROP TABLE tblMarkSheet

This query deletes the table from the database.

7) CREATE TABLE tblMarkSheet (RollNo,StudentName,ClassStd,Mark1,Mark2,Mark3,Total,Avg)

This query crates a tblMarkSheet table with the columns RollNo, StudentName, ClassStd, Mark1, Mark2, Mark3, Total and Avg. In the background it creates a file named "tblMarkSheet.fdb" in the "StudentDB" folder.

8) 1. INSERT INTO tblMarkSheet

(RollNo,StudentName,ClassStd,Mark1,Mark2,Mark3,Total,Avg) VALUES (1, ANAND,10TH,85,48,59,192,64)

This query inserts a record into the tblMarkSheet table.

2. INSERT INTO tblMarkSheet VALUES (1, ANAND,10TH,85,48,59,192,64)

This query is the same as the previous insert query. The difference is we don't need to provide the column names.

9) 1. DELETE FROM tblMarkSheet WHERE RollNo=10

This query deletes the records with record column values equal to 10 in the tblMarkSheet table.

2. DELETE FROM tblMarkSheet

This query deletes all the records from the tblMarkSheet Table.

10) 1. UPDATE tblMarkSheet SET (ClassStd) VALUES (11TH) WHERE RollNo=1

This query modifies the ClassStd field of the record that holds RollNo as 1.

1.1. UPDATE tblMarkSheet SET (ClassStd) VALUES (11TH)

2. UPDATE tblMarkSheet SET VALUES (1, XXX,5TH,40) WHERE RollNo=1

This query sequentiay updates the record that holds the RollNo as 1. Here we don't need to specify the columns to update, it automatically updates from the sequence and missed column values remain the same.

2.1. UPDATE tblMarkSheet SET VALUES (1, XXX,5TH,40)

Note: The queries 11 of 1.1 and 2.1 are executed without condition.

11) 1. SELECT * FROM tblMarkSheet

2. SELECT * FROM tblMarkSheet WHERE RollNo=1

3. SELECT RollNo,StudentName,Total FROM tblMarkSheet WHERE RollNo>5

4. SELECT RollNo,StudentName,Avg FROM tblMarkSheet WHERE Avg>=60

This query is for viewing the records of the table. Here we can filter the records using relational operators.

Bugs and Flaws in Queries

• In UPDATE and DELETE queries it supports only an Equal (=) condition. It is not supported for other operators like <, >, <=, and so on.
• In a SELECT query the data filter feature is held using a datatable and dataview. Need to provide native support.
• There is no JOIN queries when compared to RDBMS.

Advantage

• It is our own database system, so we can modify the transactions of the database system.
• Compared to other database systems it is quite small and slower but security things are ours. So we feel free about data security.
• We can ensure the security by using cryptography things in this system.
• We can modify the table structure in encoded text. So that no one knows the inner structure of the database. There is no data theft.

Future Enhancement

In the future it will be portable for more security and a fully enhanced database structure. It could support JOIN queries.

Demo Form Design

The demo form has been designed to examine the flat file database operation. It has database creation, table creation, record insertion, record deletion, record updation, loading records and custom query execution. In addition it has IntelliSense TextBox for easy query writing. The form layout is fluid and exactly fit for all screens.