Be able to import data from existing files (including .csv, .txt) using specified field names to create tables

ICT 0417 – Databases: Importing Data (and Related Core Concepts)

Learning Objective

Students will be able to import data from existing text files (.csv, .txt) using specified field names to create tables, and will understand the surrounding database and ICT concepts required by the Cambridge IGCSE/A‑Level ICT syllabus (0417).

1. Quick Overview of the Database Unit (Section 18)

• Database structure – tables, fields (columns), records (rows).
• Primary key – unique identifier for each record.
• Foreign key – field that links to a primary key in another table (one‑to‑many relationships).
• Data types – Integer, Decimal, Text, Memo, Date/Time, Boolean.
• Form design – user‑friendly screens for data entry and navigation.
• Queries & calculations – SELECT statements, criteria, aggregate functions, calculated fields.
• Data validation & integrity – field rules, referential integrity, error handling.
• Import / Export – moving data in and out of a database (CSV, TXT, XML, PDF, backup files).
• Security & e‑safety – password protection, user permissions, handling personal data.

2. Preparing Source Files for Import

2.1 Types of Text Files

2.2 Checklist Before Import

1. Open the file in a plain‑text editor (Notepad, VS Code) or spreadsheet to view the raw data.
2. Verify that every record has the same number of fields.
3. If a field contains the delimiter, enclose the whole field in a text qualifier:

   • Double quotes " (most common)
   • Single quotes ' (occasionally used)

4. Save the file in UTF‑8 encoding to avoid character‑set problems.
5. Decide whether the first row is a header row (field names). If not, prepare a list of field names that will be entered during the import wizard.
6. Compress large files (ZIP) if the exam scenario mentions file compression (Section 11.2).

2.3 Field‑Name Rules (Cambridge Syllabus)

• Start with a letter (A‑Z, a‑z).
• Contain only letters, numbers, or underscores (_); no spaces.
• Use camelCase (e.g., firstName) or underscores (e.g., first_name) for readability.
• Avoid reserved words such as SELECT, TABLE, DATE, USER.
• Keep the order identical to the column order in the source file.

3. Importing Data – Step‑by‑Step (Access, LibreOffice Base, MySQL Workbench)

3.1 Start the Import Wizard

• Microsoft Access: External Data → New Data Source → From File → Text File
• LibreOffice Base: File → New → Database → Connect to an existing database → Text
• MySQL Workbench: Server → Data Import → Import from Self‑Contained File (choose .csv and use the LOAD DATA INFILE wizard).

3.2 Choose the Source File

Browse to the .csv or .txt file you prepared.

3.3 Define File Format

• Delimiter – comma, tab, pipe, or a custom character.
• Text qualifier – double quotes (default) or single quotes.
• Header row – tick “First row contains field names” if applicable.
• Encoding – select UTF‑8.

3.4 Map Fields to Database Columns

1. If a header row exists, the wizard shows those names; you may edit them.
2. If no header row, you will be prompted to type each field name (use the rules in 2.3).
3. Adjust any names that conflict with database naming conventions.

3.5 Assign Data Types

3.6 Set Primary & Foreign Keys

• Choose a field that uniquely identifies each record (e.g., StudentID).
• If the table will relate to another, create a foreign key after the import (see Section 4).
• Optionally let the DBMS add an auto‑number field (Identity/Auto‑Increment).

3.7 Review Preview & Finish

• Check the first few rows shown by the wizard – look for mis‑aligned columns or truncated text.
• Correct any issues (delimiter, field length, date format) before clicking Finish.
• The new table appears in the database window, ready for queries, forms, and reports.

4. Building a Simple Relational Structure (One‑to‑Many)

Understanding relationships helps with query design and data integrity, even if full normalization is not required for the exam.

1. Create the parent table (e.g., Students) with a primary key StudentID.
2. Create the child table (e.g., Scores) that includes StudentID as a foreign key.
3. Define referential integrity – enforce that every StudentID in Scores matches a record in Students.
4. Optional: set cascade update/delete rules to keep related records consistent.

Example Diagram (textual)

Students
+-----------+-----------+-----------+
| StudentID | FirstName | LastName  |
+-----------+-----------+-----------+
Scores
+-----------+----------------+-------+
| ScoreID   | StudentID (FK) | Score |
+-----------+----------------+-------+

5. Form Design Basics (Section 18 – Forms)

• Purpose – provide a user‑friendly interface for data entry, editing, and navigation.
• Controls – text boxes, combo boxes, list boxes, option buttons, check boxes, command buttons.
• Layout tips

  • Group related fields together.
  • Set a logical tab order.
  • Label each control clearly (no abbreviations).
  • Apply appropriate input masks or validation rules (e.g., date format, numeric range).

• Accessibility – sufficient colour contrast, readable fonts, and full keyboard navigation.

6. Queries & Calculated Fields (Section 18 – Queries)

6.1 Simple SELECT Query

SELECT StudentID, FirstName, LastName, Score
FROM Scores
WHERE Score >= 80
ORDER BY Score DESC;

6.2 Aggregate (Summary) Query

SELECT StudentID, AVG(Score) AS AvgScore
FROM Scores
GROUP BY StudentID
HAVING AVG(Score) > 70;

6.3 Calculated Field in a Query

SELECT FirstName & " " & LastName AS FullName,
Score,
Score * 0.1 AS BonusPoints
FROM Scores;

6.4 Using Queries in Forms & Reports

• Bind a form to a query to show only filtered records.
• Base a report on a summary query to produce class averages or totals.

7. Data Validation & Integrity (Section 18 – Validation)

• Field validation rules – e.g., >=0 AND <=100 for a score field.
• Input masks – enforce format (e.g., 00/00/0000 for dates).
• Referential integrity – prevents orphan records in child tables.
• Error messages – custom messages guide the user to correct mistakes.

8. Exporting, Backup & Security (Sections 11‑16 & 8)

8.1 Export Formats

• CSV/TXT – for data exchange with other applications.
• PDF – for printable reports (e.g., student transcripts).
• XML – for structured data sharing (occasionally required in coursework).

8.2 Creating a Backup

1. Close the database (or ensure no active users).
2. Use the DBMS’s Backup command (e.g., Access: File → Save As → Access Database (ACCDB) – Backup).
3. Compress the backup file into a ZIP archive if the exam scenario mentions file compression.

8.3 Security & e‑Safety

• Set a strong password for the database file.
• Assign user roles/permissions (read‑only, read‑write) when multiple users are involved.
• When handling personal data (e.g., student names, scores), follow data‑protection guidelines – keep the file on a secure device, do not share publicly.

9. Common Pitfalls & Troubleshooting (Expanded)

10. Full Example: Importing a Student‑Score CSV File (No Header Row)

Source file – studentscores.csv

001,John,Doe,78
002,Jane,Smith,85
003,Emily,Jones,92

Step‑by‑Step (Microsoft Access)

1. External Data → New Data Source → From File → Text File → Browse to studentscores.csv → Import.
2. Choose “Delimited” and click Next.
3. Select Comma as delimiter; leave “First Row Contains Field Names” unchecked.
4. In the “Field Information” grid, type the following field names and data types:

   • StudentID – Long Integer (set as Primary Key)
   • FirstName – Short Text (30)
   • LastName – Short Text (30)
   • Score – Number (Integer)

5. Click Next to review the preview rows; confirm that columns align correctly.
6. Click Finish. The new table StudentsScores appears in the Navigation Pane.

Creating the Relational Structure

1. Create a separate Students table (if not already existing) with StudentID as Primary Key.
2. Open StudentsScores in Design View, change the primary key to an auto‑number field ScoreID, and set StudentID as a foreign key linked to Students.StudentID.
3. Enable referential integrity and optionally choose “Cascade Update” and “Cascade Delete”.

11. Alignment with the Cambridge IGCSE ICT (0417) Syllabus – Quick Review Checklist

Use this checklist before a lesson or exam preparation session to ensure full syllabus coverage.