Know and understand characteristics, uses, advantages and disadvantages of a flat file and a relational database

Topic 18 – Databases

Learning Objective

Know and understand the characteristics, uses, advantages and disadvantages of a flat‑file database and a relational database.

Be able to create a simple database structure, design data‑entry forms, import and export data, manipulate data (sorting, searching, calculations, queries) and present data in reports – all in line with the Cambridge IGCSE/A‑Level syllabus (Section 18).

1. Flat‑File Database

1.1 Characteristics

• All data are stored in a single plain‑text file (e.g. .csv, .txt).
• Each line represents one record; fields are separated by a delimiter (comma, tab, pipe, etc.) or by fixed column widths.
• No built‑in relationships between files – each file is independent.
• Structure is essentially a two‑dimensional table (rows × columns).
• File format is generic – can be read by any operating system or program that understands the delimiter.

1.2 Typical Uses

• Very small‑scale applications (a few hundred records) – simple inventories, contact lists, class registers.
• Interchanging data between programmes (spreadsheets, accounting packages, web forms).
• Log files, configuration files, temporary storage.

1.3 Advantages

• Easy to create and edit with any text editor.
• Very low overhead – minimal storage and processing requirements.
• Highly portable; can be moved between operating systems without compatibility problems.

1.4 Disadvantages

• Performance drops sharply as the number of records grows.
• No automatic enforcement of data‑integrity rules (primary keys, foreign keys, data types).
• Redundant data is common → risk of inconsistency.
• Searching, sorting and reporting must be done manually or with external tools.
• Usually single‑user; concurrent access leads to file corruption.

2. Relational Database

2.1 Characteristics

• Data are stored in a collection of related tables (each table = rows × columns).
• Relationships are defined with primary keys (unique identifier for a record) and foreign keys (reference to a primary key in another table).
• Manipulated with Structured Query Language (SQL).
• Supports three relationship types:

  • One‑to‑one
  • One‑to‑many
  • Many‑to‑many (implemented via a junction table)

• Integrity constraints (NOT NULL, UNIQUE, CHECK) and transaction control (ACID) ensure reliable data handling.
• File format is application‑specific (e.g. .mdb, .accdb, .myd) but data can be exported/imported as generic formats such as CSV or SQL dump.

2.2 Typical Uses

• Business systems – inventory, sales, payroll, customer relationship management.
• Web applications that require dynamic data (e‑commerce, forums, e‑learning).
• Research or educational databases with complex queries and reporting.
• Any environment where data consistency, security and scalability are essential.

2.3 Advantages

• Efficient handling of large, complex data sets; queries are optimised by the DBMS.
• Data redundancy is minimised through normalisation.
• Strong data integrity – keys and constraints prevent illegal data.
• Multi‑user access with transaction control (ACID) prevents lost updates.
• Powerful reporting, analysis and ad‑hoc querying using SQL.

2.4 Disadvantages

• More complex to design, implement and maintain.
• Requires dedicated DBMS software (MySQL, PostgreSQL, Microsoft Access, etc.) and adequate hardware.
• Steeper learning curve for users unfamiliar with SQL or database design.
• Initial set‑up time and cost are higher than for flat‑file solutions.

3. Comparison of Flat‑File and Relational Databases

4. Creating a Simple Relational Database (Cambridge‑style example)

4.1 Define the Tables

4.2 Field Sub‑types & Display Formats (Cambridge requirements)

• Short Text – up to 255 characters; display format can be set to “Uppercase”, “Proper Case”, etc.
• Long Text (Memo) – for paragraphs or notes exceeding 255 characters.
• Number – sub‑types: Byte, Integer, Long Integer, Single, Double. Choose the smallest type that can hold the data.
• Date/Time – display formats such as “dd/mm/yyyy”, “mm‑dd‑yyyy”, “Long Date”.
• Currency – automatically shows the currency symbol and two decimal places (e.g., £1,234.56).
• Yes/No (Boolean) – displayed as “Yes/No”, “True/False”, or a check box.
• Set Display Format for each field (e.g., Currency – £, 2 d.p. or Date – dd/mm/yyyy) to meet the syllabus requirement for “generic vs. application‑specific formats”.

4.3 Setting Primary and Foreign Keys

1. Open the table in Design View.
2. Select the field that uniquely identifies each record (e.g., StudentID) and click the Primary Key button.
3. In a related table, add a field with the same data type (e.g., ClassID in Students).
4. Open the Relationships window, drag the primary‑key field onto the matching foreign‑key field, and enforce Referential Integrity.
5. Save the relationship; the DBMS will now prevent a student being assigned a non‑existent class.

5. Form Design – Data‑Entry Forms (Cambridge AO 2)

5.1 Required Controls

• Label – describes each field.
• Text box – free‑text entry (e.g., FirstName).
• Combo box / Drop‑down list – selects from a predefined list (e.g., ClassName).
• Option group (radio buttons) – mutually exclusive choices (e.g., Gender).
• Check box – Yes/No fields.
• Command button – Save, Delete, Close, or custom actions.

5.2 Layout Guidelines

1. Place labels in a single column on the left, aligned vertically.
2. Place the corresponding input controls in a column on the right.
3. Group related controls (e.g., personal details, enrolment details) in separate section headers.
4. Set a logical tab order (Tab → next field) using the Tab Order dialog – this is a required part of the Cambridge specification.
5. Include a navigation bar (record selector, add new, delete) at the bottom of the form.

5.3 Validation (required by the syllabus)

• Required property – mark fields that must be filled (e.g., FirstName, Surname, ClassID).
• Input Mask – enforce a pattern (e.g., 00/00/0000 for dates, (999) 000‑0000 for telephone numbers).
• Validation Rule – restrict values (e.g., Mark >= 0 AND Mark <= 100).
• Validation Text – custom error message shown when a rule is violated.

5.4 Example Sketch (Students Form)

6. Importing and Exporting Data

6.1 Importing a CSV File (step‑by‑step)

1. Prepare the CSV file – first row must contain field names. Example students.csv:

   StudentID,FirstName,Surname,DOB,ClassID
   1,Emma,Smith,2005-03-12,2
   2,James,Brown,2004-11-05,1
   3,Lily,White,2005-07-22,2
   

2. In the DBMS (e.g., Microsoft Access) choose External Data → Import → Text File.
3. Select the file, choose “Delimited”, set the comma as the delimiter, and tick “First Row Contains Field Names”.
4. Map the fields to the destination table (or let Access create a new table with the same structure).
5. Run the import; any errors (duplicate primary keys, type mismatches) are listed in an import‑log file.

6.2 Exporting Query Results to CSV

1. Run the desired SELECT query (e.g., list all students in Class 2).
2. With the query result window active, choose External Data → Export → Text File.
3. Enter a destination name (e.g., Class2_Students.csv), select “Delimited”, choose comma as the delimiter, and finish.

7. Manipulating Data

7.1 Calculated Fields

In a table or query you can create a field whose value is derived from other fields.

TotalPrice: [Quantity] * [UnitPrice]

The column TotalPrice updates automatically whenever Quantity or UnitPrice changes.

7.2 Sorting and Filtering

• Sort – click a column heading in a datasheet or use ORDER BY in SQL (e.g., SELECT * FROM Students ORDER BY Surname ASC;).
• Filter – apply criteria in the query design grid or use WHERE (e.g., WHERE ClassID = 2).

7.3 Simple SELECT Queries (Cambridge level)

7.4 Using Wild‑cards

• * (or % in SQL) – any number of characters. Example: WHERE Surname LIKE 'S*' finds surnames beginning with S.
• ? (or _) – a single character. Example: WHERE Postcode LIKE 'AB? 1??'.

8. Presenting Data – Reports

8.1 Report Basics

• Reports are formatted print‑outs of data, often grouped and summarised.
• Typical sections: Report Header, Page Header, Detail Section, Group Header/Footer, Report Footer.

8.2 Example: Class‑wise Student List

1. Create a query that joins Students and Classes and sorts by ClassName, then Surname.
2. Start the Report Wizard, select the query as the data source.
3. Choose the fields to display (ClassName, FirstName, Surname, DOB).
4. Set Group By on ClassName – this adds a group header for each class.
5. Add a Total in the Report Footer (e.g., total number of students).
6. Apply formatting: bold class headings, column borders, page numbers.

8.3 Formatting Tips (Cambridge marks)

• Use a clear, legible font (e.g., Arial 10 pt) for the detail section.
• Highlight headings with a larger font or bold style.
• Align numeric fields to the right, text fields to the left.
• Include page numbers and a title in the Report Header.
• Preview before printing to ensure no data is truncated.

9. Summary

Flat‑file databases are quick, inexpensive solutions for very small or temporary data sets but lack the mechanisms needed for data integrity, multi‑user access and complex querying. Relational databases, although more demanding to set up, provide robust data integrity, powerful query and reporting capabilities, and scale efficiently to large data volumes. Mastery of both concepts, together with practical skills in table design, form creation, import/export, SQL manipulation and report generation, satisfies the full Cambridge IGCSE/A‑Level syllabus for Section 18.