Relational Databases  «Prev  Next»

Lesson 9

Relational Database Review

In this module, you learned the difference between
  1. relational and
  2. flat file databases
and the advantages of using relational databases.
You also learned what normalization is and saw an example of how to normalize a table. Now you should be able to normalize your own tables.
Normalization is beneficial in many ways, but the most important reason to normalize your databases is to be able to update changes in one location and have those changes reflected throughout the entire system. This module has shown how tables are related in a database system like those that support SQL. Since these systems are relational, it is important to understand how table relationships are organized. In the case of the overall project for this course, you will apply this approach in determining the way you will get the customer information from the database.

Key Characteristics of a Flat File Database:

Here are the key characteristics of a flat file database:
  1. Single Table Structure:
    • All data is stored in a single, two-dimensional table with rows and columns.
    • Each row represents a record (e.g., a customer, product, or order).
    • Each column represents a field (e.g., name, address, or price).
  2. Simple Data Storage:
    • Data is typically stored in plain text format, like CSV (comma-separated values) or TXT files.
    • This makes them easy to read, create, and edit using common text editors or spreadsheet programs.
  3. No Relationships:
    • Unlike relational databases, flat file databases don't define relationships between tables.
    • This can lead to data redundancy and inconsistencies if information needs to be stored in multiple places.
  4. Limited Data Management:
    • Flat file databases lack built-in features for data integrity, security, and complex queries.
    • They often require manual programming to handle data manipulation and retrieval tasks.
  5. File Access Methods:
    • Data is accessed sequentially (one record at a time) or through direct access (using record numbers).
    • This can be less efficient for large datasets compared to relational databases with indexing and optimization techniques.
Common Uses for Flat File Databases:
  • Simple data storage and retrieval tasks
  • Personal contact lists
  • Inventory management
  • Configuration files
  • Data exchange between systems
Advantages of Flat File Databases:
  • Simple and easy to understand
  • Easy to create and manage
  • No need for specialized database software

Disadvantages of Flat File Databases:
  • Limited scalability for large datasets
  • Lack of data integrity and security features
  • No support for complex relationships between data
  • Limited query and reporting capabilities

A flat file database describes various means to encode a database model as a single file. A flat file can be a plain text file or a binary file and there are no structural relationships between the records. Plain text files usually contain one record per line and there are different conventions for depicting data.
In comma-separated values and delimiter-separated values files, fields can be separated by delimiters such as comma or tab characters. In other cases, each field may have a fixed length; short values may be padded with space characters. Extra formatting may be needed to avoid delimiter collision. More complex solutions are markup languages and programming languages. Using delimiters incurs some overhead in locating them every time they are processed (unlike fixed-width formatting), which may have performance implications.

Data Compression and Performance

However, the use of character delimiters is also a crude form of data compression which may assist overall performance by reducing data volumes. This is sometimes necessary for data transmission purposes. Use of character delimiters which include a length component is comparatively rare but vastly reduces the overhead associated with locating the extent of each field. A spreadsheet or text editor program may be used to implement a flat file database, which may then be printed or used online for improved search capabilities.
  1. Typical examples of flat files are /etc/passwd and /etc/group on Unix-like operating systems.
  2. Another example of a flat file is a name-and-address list with the fields Name, Address, and Phone Number.
  3. A list of names, addresses, and phone numbers written by hand on a sheet of paper is a flat file database. This can also be done with any typewriter or word processor.
  4. A spreadsheet or text editor program may be used to implement a flat file database, which may then be printed or used online for improved search capabilities.

SEMrush Software