How is data organised in files and relational databases, and how do normalisation and SQL keep it consistent and queryable?
Organisation and structure of data: files, records and fields with key fields and file access methods, relational databases with primary and foreign keys, normalisation to third normal form, and SQL for querying and manipulating data.
An Eduqas Component 2 answer on the organisation and structure of data: files, records and fields with key fields and access methods, relational databases with primary and foreign keys, normalisation to third normal form, and writing SQL queries.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this dot point is asking
Eduqas wants you to describe how data is organised in files (records, fields, key fields, access methods), explain relational databases with primary and foreign keys, normalise a database to third normal form, and write SQL to query and manipulate data. SQL writing and normalisation are examined frequently.
The answer
Files, records, fields and access methods
Relational databases, primary and foreign keys
Normalisation and SQL
Examples in context
Relational databases power almost every information system, school records, online shops, banking, and normalisation is the discipline that keeps that data consistent as it changes. SQL is the universal language for querying them, used daily by developers and analysts. Primary and foreign keys are exactly how the data-dictionary and data-structure designs from the system-design dot point become real tables. The file access methods connect to the storage and hashing topics, and data-protection law (Component 1) governs how this stored personal data may be used.
Try this
Q1. State the purpose of a foreign key. [1 mark]
- Cue. It refers to the primary key of another table, creating a relationship (link) between the two tables.
Q2. Write an SQL statement to select all fields from a Customer table where the City is 'Cardiff'. [2 marks]
- Cue.
SELECT * FROM Customer WHERE City = 'Cardiff';
Q3. What does third normal form (3NF) remove that second normal form does not? [1 mark]
- Cue. Transitive dependencies (a non-key field depending on another non-key field).
Exam-style practice questions
Practice questions written in the style of WJEC Eduqas exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
Eduqas 20206 marksExplain the purpose of a primary key and a foreign key in a relational database, and describe the benefits of normalising a database.Show worked answer →
Primary key (up to 2 marks): a field (or combination of fields) that uniquely identifies each record in a table, so no two records share the same value and records can be reliably found.
Foreign key (up to 2 marks): a field in one table that refers to the primary key of another table, creating a link (relationship) between the two tables and enforcing referential integrity.
Benefits of normalisation (up to 2 marks): it removes data redundancy (data is not duplicated), which saves storage and prevents update, insertion and deletion anomalies, and it keeps data consistent and easier to maintain.
Markers reward the unique-identifier role of the primary key, the link-to-another-table role of the foreign key, and the reduced-redundancy and anomaly-prevention benefits of normalisation.
Eduqas 20215 marksA table `Student(StudentID, Name, CourseID, CourseName)` is to be queried. Write an SQL statement to list the names of all students on the course with `CourseID` of , sorted alphabetically by name, and explain what is meant by third normal form.Show worked answer →
SQL (up to 3 marks):
SELECT Name
FROM Student
WHERE CourseID = 'CS01'
ORDER BY Name;
One mark each for SELECT of the right field, the WHERE condition, and ORDER BY for the sort.
Third normal form (up to 2 marks): a table is in 3NF if it is already in second normal form and has no transitive dependencies, that is, no non-key field depends on another non-key field. Here CourseName depends on CourseID (a non-key field), so it should be moved to a separate Course table to reach 3NF.
Markers reward the correct SELECT/WHERE/ORDER BY query and a correct definition of 3NF (no non-key field dependent on another non-key field), ideally spotting the CourseName transitive dependency.
Related dot points
- System design: designing the inputs, outputs, processing and data structures, the use of flowcharts and pseudocode, the data dictionary, file and interface design, and specifying the hardware and software the proposed system needs.
An Eduqas Component 1 answer on system design: designing inputs, outputs, processing and data structures, using flowcharts and pseudocode, the data dictionary, file and interface design, and specifying the hardware and software the proposed system requires.
- Trees, graphs and hash tables: binary search trees and their traversals (in-order, pre-order, post-order), graphs as adjacency matrices and adjacency lists, and hashing for direct-access tables including collision handling.
An Eduqas Component 1 answer on trees, graphs and hash tables: binary search trees and in-order, pre-order and post-order traversals, representing graphs with an adjacency matrix or adjacency list, and hashing for fast direct access with collision handling.
- Compression, encryption and error checking: lossy and lossless compression (run-length encoding and dictionary methods), symmetric and asymmetric encryption, and error-detection methods (parity, checksums and check digits).
An Eduqas Component 2 answer on compression, encryption and error checking: lossy versus lossless compression with run-length and dictionary methods, symmetric and asymmetric encryption, and error-detection methods including parity, checksums and check digits.
- Representing text, images and sound: character sets (ASCII and Unicode), bitmap images with resolution, colour depth and the file-size calculation, and sampled sound with sample rate, bit depth and the file-size calculation.
An Eduqas Component 2 answer on representing text, images and sound: the ASCII and Unicode character sets, bitmap images with resolution and colour depth and the file-size calculation, and sampled sound with sample rate and bit depth and the file-size calculation.
- Data transmission: serial and parallel transmission, packet switching and the structure of a packet, network protocols and the layers of the TCP/IP stack, and the role of standards in communication.
An Eduqas Component 2 answer on data transmission: serial versus parallel transmission with their trade-offs, packet switching and packet structure, the role of network protocols, and the four layers of the TCP/IP stack.