Key differences between flat file and relational database

Advanced SQL. Essential SQL. Interview Questions. The key points to understand about relational and flat file databases are the following: Relational databases differ from older, non-relational databases because information is stored at random in the relational model, then organized as it is retrieved. Older databases typically use more proprietary query approaches, often hard-coded applications, as compared with dynamic queries, submitted by both the user and applications.

SQL relies on the foundation of a relational database. The engine you are working with must support the SQL characteristics to querying. The simplest way to manage data on a computer outside of an application is to store it in a basic file format.

The first solutions for data management used this approach and it is still a popular option for storing small amounts of information without heavy requirements. The first flat file databases represented information in regular, machine parse-able structures within files. Data is stored in plain text, which limits the type of content that can be represented within the database itself.

Sometimes, a special character or other indicator is chosen to use as a delimiter , or marker for when one field ends and the next begins. For example, a comma is used in CSV comma-separated values files, while colons or white-space are used in many data files in Unix-like systems.

Other times, no delimiter is used and instead, fields are defined with a fixed length which can be padded for shorter values.

Each user has attributes like name, user and group IDs, home directory and default shell, each separated by a colon. While flat file databases are simple, they are very limited in the level of complexity they can handle. The system that reads or manipulates the data cannot make easy connections between the data represented. File-based systems usually don't have any type of user or data concurrency features either.

Flat file databases are usually only practical for systems with small read or write requirements. For example, many operating systems use flat-files to store configuration data.

In spite of these limitations, flat-file databases are still widely used for scenarios where local processes need to store and organized small amounts of data. A good example of this is for configuration data for many applications on Linux and other Unix-like systems.

In these cases, the flat-file format serves as an interface that both humans and applications can easily read and manage. Some advantages of this format are that it has robust, flexible tooling, is easily managed without specialized software, and is easy to understand and work with. Hierarchical databases were the next evolution in database management development. They encode a relationship between items where every record has a single parent.

This builds a tree-like structure that can be used to categorize records according to their parent record. This simple relationship mapping provides users with the ability to establish relationships between items in a tree structure. This is very useful for certain types of data, but does not allow for complex relationship management. Furthermore, the meaning of the parent-child relationship is implicit. One parent-child connection could be between a customer and their orders, while another might represent an employee and the equipment they have been allocated.

The structure of the data itself does not distinguish between these relationships. Hierarchical databases are the beginning of a movement towards thinking about data management in more complex terms.

The trajectory of database management systems that were developed afterwards continues this trend. Hierarchical databases are not used much today due to their limited ability to organize most data and because of the overhead of accessing data by traversing the hierarchy. However, a few incredibly important systems could be considered hierarchical databases. Network databases built on the foundation provided by hierarchical databases by adding additional flexibility. Instead of always having a single parent, as in hierarchical databases, network database entries can have more than one parent, which effectively allows them to model more complex relationships.

When talking about network databases, it is important to realize that network is being used to refer to connections between different data entries, not connections between different computers or software. Network databases can be represented by a generic graph instead of a tree.

The meaning of the graph was defined by a schema , which lays out what each data node and each relationship represents. This gave structure to the data in a way that could previously only be reached through inference. A database schema is a description of the logical structure of a database or the elements it contains.

Schemas often include declarations for the structure of individual entries, groups of entries, and the individual attributes that database entries are comprised of. These may also define data types and additional constraints to control the type of data that may be added to the structure. Network databases were a huge leap forward in terms of flexibility and the ability to map connections between information. However, they were still limited by the same access patterns and design mindset of hierarchical databases.

For instance, to access data, you still needed to follow the network paths to the record in question. The parent-child relationship carried over from hierarchical databases also affected the way that items could connect to one another. It is difficult to find modern examples of network database systems.

Setting up and working with network databases required a good deal of skill and specialized domain knowledge. Most systems that could be approximated using network databases found a better fit once relational databases appeared. Relational databases are the oldest general purpose database type still widely used today. In fact, relational databases comprise the majority of databases currently used in production.

Relational databases organize data using tables. Tables are structures that impose a schema on the records that they hold. Each column within a table has a name and a data type. Each row represents an individual record or data item within the table, which contains values for each of the columns. Relational databases get their name from mathematical relationships that use tuples like the rows in a table to represent ordered sets of data. Special fields in tables, called foreign keys , can contain references to columns in other tables.

This allows the database to bridge the two tables on demand to bring different types of data together. Relational databases require a structure to contain them, referred to as a server, in order to store and to interpret data. They also depend on specific tools to view and to interpret the information contained in them.

In relational databases, information is stored in a randomized manner, only to be organized on retrieval. As opposed to flat files, relational databases can address more dynamic queries. There are many advantages to relational databases. Data only needs to be stored a single time, which can eliminate duplication, resulting in a more efficient storage method as well as create an ease in making changes to the data and the data format.

It can also provide an increased level of security, due to the possibility of splitting data into separate tables, limiting access to authorized viewers. Example of database query. Figure 2. EMAP table relationships. Although the current database's total storage capacity is small in comparison with industrial or agency databases, it has many complex parameters and properties.

The development and interactive use of the database throughout both the data processing and interpretative phases fig. Industrial managers now use "data mining" to perform statistical analysis and modeling techniques to uncover patterns and relationships hidden in an organization's database Edelstein, This intelligent data querying and exploration of data resources helps to uncover new relationships rather than merely to provide answers to standard queries, as traditional databases have done.

Data mining likewise implies the necessity for continuous modification of database structures, another feature of the present work. Figure 3. Flow chart showing database development.