Data Management: Files, Records, Databases, SQL and Integrity
Data management covers how facts are organised for storage and retrieval: characters forming fields, fields forming records, records forming files, and files managed by a database management system and queried with SQL. The exam tests terminology, access methods, keys, and the difference between keeping data valid and keeping it correct.
Files and records
A field is one item of data (Surname), a record is one complete set of fields about one entity, and a file is a collection of related records. Fixed-length fields waste space but allow a record's position to be calculated; variable-length fields save space but need delimiters or an index. Serial access reads records in stored order; sequential access needs the file sorted on the key and reads in that order; random (direct) access jumps straight to a record using its key, which is why transaction files are sorted before batch updating.
Databases
A relational database stores data in tables of rows (records) and columns (fields). A primary key uniquely identifies each row and cannot be null or duplicated; a foreign key is a primary key placed in another table to create a relationship, and referential integrity prevents deleting a parent record that still has children. A DBMS handles storage, concurrent access, security, backup and recovery. Normalisation reduces duplication: 1NF removes repeating groups, 2NF removes partial dependencies on part of a composite key, 3NF removes non-key fields that depend on other non-key fields.
SQL essentials
SELECT columns FROM table WHERE condition ORDER BY column; also INSERT INTO, UPDATE … SET … WHERE, DELETE FROM … WHERE, and aggregates COUNT, SUM, AVG with GROUP BY.
Worked example
Table STUDENT(ID, Name, Class, Age). Task: list the names of students in Form 5 older than 15, alphabetically. SELECT Name FROM STUDENT WHERE Class = 'Form 5' AND Age > 15 ORDER BY Name; Reasoning: SELECT names only the column needed; WHERE filters rows and joins two tests with AND; text values take single quotes while numbers do not; ORDER BY sorts the output. SELECT * would return every column and lose the mark for choosing fields.
Traps
Do not confuse a field with a record, or a primary key with any unique-looking column. Validation checks that data is sensible (range, type, format, presence); verification checks that it was copied correctly (double entry, visual check) - validation cannot prove data is true. SQL strings need quotes, because unquoted text is read as a field name.