OCR A-Level Computer Science Computer systems (01), June 2025: Question 8

16 marks · Medium difficulty · Short Answer

Explain the limitations of flat file databases, complete an ER diagram for a relational database, state requirements for 2NF and 3NF, and complete a JavaScript function to process quiz answers.

Practise this question

Question

Exam question 8 about an online quiz program. Part (a) asks to explain the limitations of flat file databases for 4 marks. Part (b) provides schemas for four tables: TblCandidate, TblSubject, TblQuiz, and TblResponse, highlighting primary and foreign keys, and asks to complete an Entity Relationship Diagram (ERD) connecting four boxes representing the tables for 3 marks. Part (c)(i) asks to state two requirements for 2NF (2 marks), and (c)(ii) asks for two requirements for 3NF (2 marks). Part (d) describes a JavaScript client-side function named checkAnswers with three parameters, asking to fill in the blanks in a skeleton code template for 5 marks.
Question text

8 Orla is developing a program that will allow candidates to complete quizzes for a range of

subjects. Each quiz has three questions and each one is multiple choice with three possible

answers.

(a) Orla is considering using a flat file database to store data about candidates, and the subjects and

quizzes they sit.

Explain the limitations of using flat file databases.

… [4]

(b) Due to the limitations of flat file databases, Orla decides to create a relational database

containing these four tables:

• TblCandidate – Stores data about the candidates

• TblSubject – Stores data about the subject

• TblQuiz – Stores the questions for each individual quiz and the answers

• TblResponse – Stores the candidate’s responses for a given quiz.

The fields in each table are shown here. PK indicates the primary keys and FK indicates the

foreign keys that are used.

TblCandidate TblSubject TblQuiz TblResponse

CandidateID(PK) SubjectID(PK) QuizID(PK) ResponseID(PK)

CandidateName SubjectName Q1Text Date

eMail Description Q1Answer Q1Response

PhoneNumber Q2Text Q2Response

Q2Answer Q3Response

Q3Text Score

Q3Answer QuizID(FK)

SubjectID(FK) CandidateID(FK)

Complete an Entity Relationship Diagram (ERD) to show the relationships between these four

tables.

TblCandidate TblSubject

TblResponse TblQuiz

26 [3]

(c) In order to determine what tables were required, Orla used database normalisation.

(i) State two requirements for a database to be in Second Normal Form (2NF).

1 …

2 …

[2]

(ii) State two requirements for a database to be in Third Normal Form (3NF).

1 …

2 …

27 [2]

(d) Orla wants a web based version of the quiz. She develops a function called checkAnswers to

calculate the score of each quiz on the client side using JavaScript.

Next to each candidate answer is an empty HTML element. When the user presses submit, the

function, checkAnswers, will check if each answer is correct. If so, it will add the word "Correct"

into the empty HTML element.

The function, checkAnswers takes three parameters:

• responses – an array of the IDs of the HTML elements that contains the user’s responses.

• feedbacks – an array of the IDs of the HTML elements that the text "Correct" is to be

written to if the candidate gets the question right. The array will be the same length as

responses.

• answers – an array of the correct answer for each of the questions. The array will be the

same length as responses.

The function returns the final score out of three for the quiz. It loops through each question to

calculate if the candidate got it correct and processes it appropriately if they did.

Complete the function checkAnswers, using pseudocode or program code.

function checkAnswers(responses, feedbacks, … )

{

var … = 0;

for(var qCount = 0; qCount < responses.length; qCount++)

{

response = document.getElementById(responses[qCount]).innerHTML;

answer = answers[ … ];

feedbackElement = document.getElementById(feedbacks[qCount]);

if (response == answer)

{

score = …

feedbackElement.innerHTML = "Correct";

}

}

… score;

}

[5]

Mark scheme

Show the mark scheme Mark scheme for Question 8. Part (a) awards up to 4 marks for limitations of flat file databases: redundant/repeated data, data inconsistency, increased database size, slower data access/searches, hard to maintain, and difficult to apply ACID. Part (b) awards 3 marks for correct one-to-many relationships: TblCandidate to TblResponse, TblQuiz to TblResponse, and TblSubject to TblQuiz, shown using crow's foot notation. Part (c)(i) awards 2 marks for 2NF: in 1NF, and no partial key dependencies. Part (c)(ii) awards 2 marks for 3NF: in 2NF, and no transitive dependencies. Part (d) awards 5 marks for filling in the missing code tokens: answers, score, qCount, score + 1, and return.

8 (a) 1 mark each to max 4. 4 DNA ‘information’ for ‘data’ –

● Lots of repeated / redundant data penalise once and then FT.

● Can lead to data being inconsistent

● Increases the size of the database

● Data access speed is slow

● Searches are slow/hard

● They are difficult to manage/maintain

● It is difficult to add additional functionality

● Difficult to apply ACID principles

8 (b) 1 mark for each correct relationship: 3 MAX 2 if any extra relationships

● One to many TblCandidate to TblResponse28 added.

● One to Many TblQuiz to TblResponse

● One to Many TblSubject to TblQuiz DNA: words should not be used to

describe relationships.

TBLCandidate TBLSubject

Accept: relationship symbol from

specification only:

TBLResponse TBLQuiz

(c) i 1 mark each to max 2 2

● Data is in 1st normal form

● All non-key fields depend on key field(s)/no partial dependencies

(c) ii 1 mark each to max 2 2

● Data is in 2nd normal form

● All non-key fields don’t depend on another non-key field/ no transitive

dependencies

8 (d) 1 mark for each missing statement to max 5. 5

Allow: for MP5 return via function

● answers call e.g. checkAnswers=

● score

● qCount

● score + 1

● return

function checkAnswers(responses, feedbacks, answers)

{

var score = 0;

for(var qCount = 0; qCount < responses.length; qCount++)

{

response = document.getElementById(responses[qCount]).innerHTML;

answer = answers[qCount];

feedbackElement = document.getElementById(feedbacks[qCount]);

if (response == answer)

{

score = score + 1;

feedbackElement.innerHTML = "Correct";

}

}

return score;

}

How to answer it

Database Design, Normalisation & Client-Side JavaScript

What this question tests

This question assesses fundamental database concepts and client-side web development across four core competencies:

  • Database Architecture: Explaining disadvantages and technical limitations of flat file databases versus relational databases.
  • Relational Modelling: Interpreting schema with Primary Keys (PK) and Foreign Keys (FK) to map Entity Relationship Diagrams (ERDs) using standard Crow's Foot notation.
  • Database Normalisation: Stating formal rules for Second Normal Form (2NF) and Third Normal Form (3NF).
  • DOM Manipulation (JavaScript): Writing algorithm logic, accessing indexed arrays, parsing HTML IDs, updating DOM elements, and returning calculation values.

Part (a) — Limitations of Flat File Databases

4 Marks — Explaining drawbacks of single-table storage architectures

✅ Acceptable Points (Any 4)

  • Lots of redundant / duplicate data stored.
  • Can lead to data inconsistency / integrity anomalies (e.g. updating one instance but missing another).
  • Increases overall file storage size unnecessarily.
  • Data access and search speeds are slow (requires linear scans).
  • Difficult to manage, maintain, or restructure.
  • Hard to implement complex security or access rights.
  • Difficult to apply ACID transaction principles or enforce referential integrity.

❌ Common Traps & Examiner Warnings

  • "DNA information for data": The mark scheme strictly penalises using the colloquial word "information" when referring to "data redundancy" or "data inconsistency". Use precise technical terminology!
  • Vague assertions like "it is bad" or "it crashes easily" receive zero marks without qualified reasons (e.g. data anomaly, locking issues).

🧠 Exam Technique: The "Redundancy Cascade"

Always link Data Redundancy directly to its inevitable consequences: Data Inconsistency (an update anomaly occurs if a student changes their surname or email in one row but not another) and Wasted Storage Space.

Mark Scheme Rule: 1 mark per valid limitation up to max 4. Note: DNA (Do Not Accept) "information" for "data" — penalised once and then follow-through applied.

Part (b) — Entity Relationship Diagram (ERD)

3 Marks — Determining cardinality from Primary and Foreign Keys

💡 Key Knowledge: How Foreign Keys Define Cardinality

A foreign key lives in the table on the "Many" (∞ / Crow's Foot) side of a 1:M relationship:

  • TblSubject (PK: SubjectID) → TblQuiz (FK: SubjectID) : One Subject has many Quizzes (1 : M).
  • TblCandidate (PK: CandidateID) → TblResponse (FK: CandidateID) : One Candidate submits many Responses (1 : M).
  • TblQuiz (PK: QuizID) → TblResponse (FK: QuizID) : One Quiz receives many Responses (1 : M).

Visual ERD Representation

TblCandidate
| (One)
↓
∈ (Many)
TblResponse
TblSubject
| (One)
↓
∈ (Many)
TblQuiz

Horizontal connection: TblQuiz (One side: |) to TblResponse (Many side: Crow's Foot ∈).

✅ Awarding 3 Marks

  • Mark 1: One-to-many from TblCandidate to TblResponse .
  • Mark 2: One-to-many from TblQuiz to TblResponse .
  • Mark 3: One-to-many from TblSubject to TblQuiz .

❌ Penalties to Avoid

  • MAX 2 marks if any extra or spurious relationships are drawn (e.g., connecting Candidate directly to Subject).
  • DNA: Words written on lines (e.g. writing "has" or "one-to-many"). Only official Crow's foot / specification symbols are accepted!

Part (c) — Database Normalisation Requirements

4 Marks (2 + 2) — Formal rules for 2NF and 3NF

(c)(i) Requirements for 2NF (2 Marks)

  1. Must be in First Normal Form (1NF) (1 mark).
  2. No partial dependencies / All non-key fields must depend on the whole primary key (1 mark).
Note: Partial dependencies only occur when a relation has a composite primary key.

(c)(ii) Requirements for 3NF (2 Marks)

  1. Must be in Second Normal Form (2NF) (1 mark).
  2. No transitive dependencies / Non-key fields must not depend on other non-key fields (1 mark).
Rule of Thumb: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key."

❌ Common Student Mistakes in Normalisation Questions

Candidates frequently forget that normalisation is progressive. You cannot be in 2NF without already being in 1NF; you cannot be in 3NF without already being in 2NF. Always write this down as your first point!

Part (d) — Client-Side JavaScript Algorithm Completion

5 Marks — Missing statements in client-side scoring logic

Fill in the 5 blank spots in the JavaScript quiz-marking function:

function checkAnswers(responses, feedbacks, answers ) { var score = 0; for(var qCount = 0; qCount < responses.length; qCount++) { response = document.getElementById(responses[qCount]).innerHTML; answer = answers[ qCount ]; feedbackElement = document.getElementById(feedbacks[qCount]); if (response == answer) { score = score + 1 ; // or score++ / ++score feedbackElement.innerHTML = "Correct"; } } return score; }

📐 Step-by-Step Logic Breakdown

  1. Line 1 (Parameter): Function declaration requires 3 parameters matching the spec description: responses , feedbacks , and missing answers .
  2. Line 3 (Accumulator): Variable initialized to 0 must be score since it accumulates marks.
  3. Line 7 (Array Index): Loop variable is qCount , so indexing the array is answers[qCount] .
  4. Line 11 (Increment): Increment accumulator via score = score + 1 (or score++ / score += 1 ).
  5. Line 15 (Output): Function must return final score out of three via return .

✅ Mark Allocation

  • MP1: answers (in parameter list)
  • MP2: score (variable definition)
  • MP3: qCount (array index subscript)
  • MP4: score + 1 (or equivalent increment)
  • MP5: return (allow: checkAnswers = score )

Topics

1.3 Exchanging data · 2.2 Problem solving and programming · 1.3.2 Databases · 1.3.4 Web Technologies · 2.2.1 Programming techniques

Question and mark scheme from the OCR A-Level Computer Science examination, Computer systems (01), June 2025. QuestionVault is an independent revision resource; questions remain the copyright of the awarding body.