Revision Library

revisionlibrary.co.uk

GCSE · Computer Science

P2.8 SQL and Data Handling

OCR J277 · Calculators not allowed · about 85 minutes

Total Marks

Name: _______________________________ Date: ____ / ____ / ______

Answer ALL questions. Show all your working.

A librarian at Chesterfield Library keeps records about members and the books they borrow, stored electronically instead of on paper.

(a)Which term describes a single item of data stored about one attribute (e.g. just a member's Town)?(1)

A) Record
B) Field
C) Table
D) Database

(b)Which term describes all the data stored about one entity (e.g. all the data about one member)?(1)

A) Record
B) Field
C) Table
D) Database

(c)Which term describes a collection of related records, each made up of the same set of fields?(1)

A) Record
B) Field
C) Table
D) Query

(d)Which term describes an organised collection of structured data, typically made up of one or more related tables?(1)

A) Record
B) Field
C) Table
D) Database

(Total for Question 1 is 4 marks)

The librarian's Members table stores data about every person who has joined the library.

MemberID	FirstName	LastName	Town	JoinYear	BooksOut
1001	Aisha	Khan	Preston	2023	2
1002	Ben	Ogunleye	Preston	2022	0
1003	Chloe	Wright	Kendal	2024	5
1004	Dmitri	Novak	Kendal	2023	1
1005	Elin	Pryce	Lancaster	2021	3
1006	Frankie	Osei	Preston	2024	0
1007	Grace	Taylor	Lancaster	2022	4
1008	Harun	Malik	Kendal	2023	2
1009	Isla	Fernandes	Lancaster	2024	0
1010	Jamie	Connolly	Preston	2021	1

(a)State the number of fields in the Members table.(1)

(b)State the number of records in the Members table.(1)

(c)Identify the most suitable field to use as the primary key for this table, and give one reason for your choice.(2)

(Total for Question 2 is 4 marks)

Using the Members table from question 2, answer the following by searching the data manually (without writing any SQL).

(a)State the FirstName of the member whose MemberID is 1005.(1)

(b)State how many members live in the town of Preston.(1)

(c)State how many members currently have 0 books out (BooksOut = 0).(1)

(d)List the MemberIDs of all members who joined in 2023 (JoinYear = 2023).(1)

(Total for Question 3 is 4 marks)

Before switching to a database, the library kept each member's details on a separate paper form filed in a cabinet. Explain why storing this data in a database instead makes it easier to find information about members.

(Total for Question 4 is 3 marks)

The library is designing the Members table and needs to choose an appropriate data type for each field. For each field below, state the most appropriate data type, choosing from: text (string), integer, real (decimal), Boolean.

(a)MemberID(1)

(b)JoinYear(1)

(c)Town(1)

(d)A new field called HasPaid, which records whether a member has paid their annual membership fee.(1)

(Total for Question 5 is 4 marks)

SQL (Structured Query Language) can be used to search a database table. The statement SELECT * FROM tablename; displays every field of every record; SELECT field1, field2 FROM tablename; displays only the named fields. Using the Members table from question 2, write the following SQL statements.

(a)Write an SQL statement to display every field, for every record, in the Members table.(2)

(b)Write an SQL statement to display only the FirstName and LastName fields, for every record, in the Members table.(2)

(Total for Question 6 is 4 marks)

The keyword WHERE lets an SQL statement filter which records are returned, based on a condition. Using the Members table from question 2, write the following SQL statements.

(a)Write an SQL statement to display the FirstName, LastName and Town fields for every member who lives in Lancaster or Kendal.(2)

(b)Write an SQL statement to display all fields for every member who has more than 2 books currently out (BooksOut > 2).(2)

(Total for Question 7 is 4 marks)

State the FirstName and Town that would be output when the following SQL statement is run on the Members table from question 2:

SELECT FirstName, Town FROM Members WHERE JoinYear = 2024;

(Total for Question 8 is 3 marks)

Using the Members table from question 2, write the following SQL statements, each combining a WHERE condition with the AND or OR keyword.

(a)Write an SQL statement to display the FirstName and LastName of every member who lives in Preston AND currently has 0 books out.(2)

(b)Write an SQL statement to display all fields for every member who lives in Lancaster OR has more than 3 books currently out (BooksOut > 3).(3)

(Total for Question 9 is 5 marks)

The keyword ORDER BY sorts the output of an SQL statement by a chosen field; ASC sorts ascending (smallest/earliest first, and is the default if omitted) and DESC sorts descending (largest first). Using the Members table from question 2, write the following SQL statements.

(a)Write an SQL statement to display the FirstName, LastName and BooksOut fields, for every member, sorted so that the member with the most books out appears first.(3)

(b)Write an SQL statement to display the LastName field, for every member, sorted alphabetically from A to Z.(2)

(Total for Question 10 is 5 marks)

Write a single SQL statement, using the Members table from question 2, to display the FirstName, LastName and JoinYear fields for every member who joined before 2023 (JoinYear < 2023), sorted so that the earliest JoinYear appears first.

(Total for Question 11 is 4 marks)

The keyword LIKE, combined with the wildcard %, matches any sequence of characters within a text field (for example, 'A%' matches any value starting with A). Using the Members table from question 2, write the following SQL statements.

(a)Write an SQL statement to display all fields for every member whose LastName begins with the letter O.(2)

(b)A librarian wants to find every member whose Town contains the letters "cas" anywhere within it (for example, to find Lancaster). Write an SQL statement to display the FirstName and Town of these members.(2)

(Total for Question 12 is 4 marks)

The library is adding a new field, EmailAddress, to the Members table when a new member joins.

(a)Explain how a presence check could be used to validate data entered into the EmailAddress field.(2)

(b)Explain how a length check could also be used to validate data entered into the EmailAddress field.(1)

(Total for Question 13 is 3 marks)

The library also keeps a Loans table, which records each time a book is borrowed:

LoanID	MemberID	BookTitle	DueDate
5001	1001	The Hobbit	21/07/2026
5002	1003	Wuthering Heights	18/07/2026
5003	1003	Great Expectations	25/07/2026
5004	1007	1984	20/07/2026

(a)State the primary key of the Loans table.(1)

(b)The MemberID field in the Loans table is a foreign key. Explain what is meant by a foreign key, referring to the Members and Loans tables in your answer.(2)

(c)State one benefit of storing member details in a separate Members table, rather than repeating FirstName, LastName and Town directly inside the Loans table every time a book is borrowed.(2)

(Total for Question 14 is 5 marks)

A small library currently stores all its data in a single flat-file table, with columns MemberID, FirstName, LastName, Town, BookTitle and DueDate, adding a new row every time a member borrows a book (so a member who borrows three books appears in three separate rows, with their FirstName, LastName and Town repeated each time).

Discuss the problems this flat-file approach could cause, and explain how splitting the data into two related tables, as in the Members and Loans tables in question 14, solves these problems.

(Total for Question 15 is 6 marks)

A librarian writes the following SQL statement to search the Members table from question 2:

SELECT FirstName FROM Members WHERE Town = 'Preston' AND BooksOut = 0 OR Town = 'Kendal';

In SQL, AND is evaluated before OR when both appear in the same WHERE clause (unless brackets are used to change the order). State the FirstNames that this statement would output, listing every member that meets the condition.

(Total for Question 16 is 3 marks)

Write a single SQL statement, using the Members table from question 2, that would display the FirstName, LastName and JoinYear fields for every member whose LastName contains the letter sequence "an" and who joined in 2023 or later (JoinYear ≥ 2023), sorted with the earliest JoinYear first.

(Total for Question 17 is 5 marks)

Mark scheme · P2.8 SQL and Data Handling

Question 1

(a) B1 B cao
(a) Answer: B) Field
(b) B1 A cao
(b) Answer: A) Record
(c) B1 C cao
(c) Answer: C) Table
(d) B1 D cao
(d) Answer: D) Database

Question 2

(a) B1 6 cao
(a) Answer: 6
(b) B1 10 cao
(b) Answer: 10
(c) B1 MemberID
(c) B1 Reason, e.g. every value in this field is unique/different for each record, so it can identify one specific member with no duplicates (unlike FirstName, LastName or Town, which could repeat)
(c) Answer: MemberID; because each MemberID value is unique to one record, so it can identify a single member without ambiguity.

Question 3

(a) B1 Elin cao
(a) Answer: Elin
(b) B1 4 cao
(b) Answer: 4
(c) B1 3 cao
(c) Answer: 3
(d) B1 1001, 1004, 1008 (all three, ft consistent with table)
(d) Answer: 1001, 1004, 1008

Question 4

B1 Every record in a database table is stored in the same structured format, with the same set of fields, unlike paper forms which may be filled in inconsistently (1)
B1 Software can search/query all the records automatically and systematically, checking every record against given criteria (1)
B1 This is much faster and less error-prone than a person having to search through a paper filing cabinet by hand, especially as the number of members grows (1)
Answer: A database stores every member in the same consistent structure, so software can search all records automatically and quickly, which is far faster and less error-prone than manually checking paper forms.

Question 5

(a) B1 Integer
(a) Answer: Integer
(b) B1 Integer
(b) Answer: Integer
(c) B1 Text/string
(c) Answer: Text (string)
(d) B1 Boolean
(d) Answer: Boolean

Question 6

(a) M1 SELECT ... FROM Members structure attempted, with keywords in the correct order
(a) A1 SELECT * FROM Members; oe (trailing semicolon not required)
(a) Answer: SELECT * FROM Members;
(b) M1 SELECT FirstName, LastName ... structure attempted
(b) A1 SELECT FirstName, LastName FROM Members; oe
(b) Answer: SELECT FirstName, LastName FROM Members;

Question 7

(a) M1 SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' structure attempted
(a) A1 SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' OR Town = 'Kendal'; oe
(a) Answer: SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' OR Town = 'Kendal';
(b) M1 SELECT * FROM Members WHERE structure attempted
(b) A1 SELECT * FROM Members WHERE BooksOut > 2; oe
(b) Answer: SELECT * FROM Members WHERE BooksOut > 2;

Question 8

B1 Chloe, Kendal
B1 Frankie, Preston
B1 Isla, Lancaster
Answer: Chloe, Kendal / Frankie, Preston / Isla, Lancaster (any order)

Question 9

(a) M1 SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' structure attempted
(a) A1 SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' AND BooksOut = 0; oe
(a) Answer: SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' AND BooksOut = 0;
(b) M1 SELECT * FROM Members WHERE Town = 'Lancaster' structure attempted
(b) M1 OR BooksOut > 3 correctly appended as a second condition
(b) A1 SELECT * FROM Members WHERE Town = 'Lancaster' OR BooksOut > 3; oe, fully correct
(b) Answer: SELECT * FROM Members WHERE Town = 'Lancaster' OR BooksOut > 3;

Question 10

(a) M1 SELECT FirstName, LastName, BooksOut FROM Members structure attempted
(a) M1 ORDER BY BooksOut included
(a) A1 SELECT FirstName, LastName, BooksOut FROM Members ORDER BY BooksOut DESC; oe (DESC correctly chosen so the highest value is first)
(a) Answer: SELECT FirstName, LastName, BooksOut FROM Members ORDER BY BooksOut DESC;
(b) M1 SELECT LastName FROM Members ORDER BY LastName structure attempted
(b) A1 SELECT LastName FROM Members ORDER BY LastName ASC; oe (ASC may be omitted, since it is the default)
(b) Answer: SELECT LastName FROM Members ORDER BY LastName ASC;

Question 11

M1 SELECT FirstName, LastName, JoinYear FROM Members correctly named
M1 WHERE JoinYear < 2023 correctly included
M1 ORDER BY JoinYear included, placed after the WHERE clause
A1 SELECT FirstName, LastName, JoinYear FROM Members WHERE JoinYear < 2023 ORDER BY JoinYear ASC; oe, fully correct
Answer: SELECT FirstName, LastName, JoinYear FROM Members WHERE JoinYear < 2023 ORDER BY JoinYear ASC;

Question 12

(a) M1 SELECT * FROM Members WHERE LastName LIKE structure attempted
(a) A1 SELECT * FROM Members WHERE LastName LIKE 'O%'; oe
(a) Answer: SELECT * FROM Members WHERE LastName LIKE 'O%';
(b) M1 SELECT FirstName, Town FROM Members WHERE Town LIKE structure attempted
(b) A1 SELECT FirstName, Town FROM Members WHERE Town LIKE '%cas%'; oe
(b) Answer: SELECT FirstName, Town FROM Members WHERE Town LIKE '%cas%';

Question 13

(a) B1 A presence check tests whether any data at all has been entered into a field (1)
(a) B1 If the EmailAddress field is left blank when a new record is added, the system rejects the record/prompts the user to enter a value (1)
(a) Answer: A presence check rejects a new record if the EmailAddress field has been left empty, forcing a value to be entered.
(b) B1 A length check tests that the number of characters entered is within an acceptable range (e.g. rejecting an email address that is only 1 or 2 characters long, which is too short to be valid)
(b) Answer: A length check rejects an entry that has too few (or too many) characters to realistically be a valid email address.

Question 14

(a) B1 LoanID cao
(a) Answer: LoanID
(b) B1 A foreign key is a field in one table that is the primary key of another (related) table (1)
(b) B1 The MemberID in the Loans table links each loan record to the matching member record (with that same MemberID) in the Members table (1)
(b) Answer: A foreign key is a field that is the primary key in a different table; MemberID in Loans is a foreign key because it links each loan to the matching member's record in the Members table.
(c) B1 Avoids data redundancy/duplication, since a member's details are not repeated in every loan record (1)
(c) B1 Reduces the risk of inconsistent data, since a member's details only need to be updated in one place (the Members table) rather than in every loan they have (1)
(c) Answer: It avoids repeating each member's details in every loan record, saving storage and making sure a member's details can be updated in one place without risking inconsistent copies.

Question 15

Level 1 (1-2): Basic, undeveloped comments about the flat-file table are given, with little or no reference to how a relational structure would help. Little relevant technical terminology is used.
Level 2 (3-4): At least one problem with the flat-file approach is described with some understanding, and there is some explanation of how splitting the data into two tables helps, but the discussion may be one-sided or lack detail in places. Relevant technical terminology is generally used correctly.
Level 3 (5-6): A well-balanced discussion clearly describes multiple problems with the flat-file approach (e.g. redundancy and update anomalies) and clearly explains how a relational structure with a foreign key solves each problem. Relevant technical terminology is used correctly throughout, and the explanation is well supported and coherent.
Indicative content:
- In the flat-file table, a member's FirstName, LastName and Town are repeated in every row where they borrow a book, wasting storage space (data redundancy)
- If a member moves to a new Town, every row containing that member's loans would need to be updated; if even one row is missed, the table would hold inconsistent/contradictory data for the same member (an update anomaly)
- If a member's only loan record were deleted (e.g. because the book was returned and the row removed), all record of that member's details could be lost from the table (a deletion anomaly)
- Splitting the data into a Members table (with MemberID as primary key) and a Loans table (with LoanID as primary key and MemberID as a foreign key) means each member's FirstName, LastName and Town are stored only once, in a single row of the Members table
- Loans are linked to members via the MemberID foreign key rather than repeating the member's details, so redundancy is removed and storage is saved
- Updating a member's Town now only requires changing one row in the Members table, and every loan automatically remains linked to the correct, up-to-date member record via MemberID, avoiding inconsistency
- A justified conclusion should recognise that the flat-file approach becomes increasingly wasteful and error-prone as the number of loans grows, whereas the relational structure keeps each fact stored exactly once and connects tables using keys

Question 16

M1 Recognises the condition is evaluated as (Town = 'Preston' AND BooksOut = 0) OR Town = 'Kendal', i.e. AND is grouped before OR
A1 Ben and Frankie identified (the Preston members with 0 books out)
A1 Chloe, Dmitri and Harun identified (all three Kendal members, regardless of BooksOut)
Answer: Ben, Frankie, Chloe, Dmitri, Harun (any order)

Question 17

M1 SELECT FirstName, LastName, JoinYear FROM Members correctly named
M1 WHERE LastName LIKE '%an%' correctly included
M1 AND JoinYear ≥ 2023 correctly appended as a second condition
M1 ORDER BY JoinYear ASC included, placed after the WHERE clause
A1 SELECT FirstName, LastName, JoinYear FROM Members WHERE LastName LIKE '%an%' AND JoinYear ≥ 2023 ORDER BY JoinYear ASC; oe, fully correct with clauses in the correct order
Answer: SELECT FirstName, LastName, JoinYear FROM Members WHERE LastName LIKE '%an%' AND JoinYear ≥ 2023 ORDER BY JoinYear ASC;

SQL and Data Handling - Worksheets, Questions and Revision