MskProdComputing
Back to the topic

The mark scheme starts on a new page, so you can print pages one and two for a student and keep the last page yourself. Everything prints in black on white whichever theme you are using.

AQA GCSE CS · 3.7

Relational Databases and SQL

Section A: 10 multiple choice questions, 10 marks. Section B: 4 written questions, 13 marks. Total 23 marks.

Name

Class

Date

Mark  / 23

Section A

Circle the letter of the correct answer. One mark each.

  1. 1What is a primary key?1
    1. AThe field used to sort the table
    2. BThe first field in a table
    3. CA field whose value uniquely identifies each record in a table
    4. DA field that links to another table
  2. 2What is a foreign key?1
    1. AA key used to encrypt the database
    2. BA field holding the primary key of a record in another table
    3. CThe last field in a table
    4. DA field that must be unique
  3. 3Which SQL clause filters which records are returned?1
    1. AWHERE
    2. BORDER BY
    3. CSELECT
    4. DFROM
  4. 4What does SELECT * mean?1
    1. AMultiply the values
    2. BReturn only the primary key
    3. CReturn every field of the matching records
    4. DReturn every record in the database
  5. 5What is wrong with UPDATE Students SET yearGroup = 11?1
    1. AIt has no WHERE clause, so every record would be changed
    2. ByearGroup must be in quotation marks
    3. CUPDATE cannot be used on numbers
    4. DSET is not a valid keyword
  6. 6Which query returns all books with 'python' anywhere in the title?1
    1. ASELECT * FROM Books WHERE title LIKE '%python%'
    2. BSELECT * FROM Books WHERE title = 'python'
    3. CSELECT title FROM Books ORDER BY python
    4. DSELECT * FROM Books WHERE title CONTAINS python
  7. 7Why is customer data split into separate tables rather than stored in one?1
    1. AIt doubles the storage needed
    2. BIt makes queries impossible to write
    3. CIt removes the need for keys
    4. DIt removes redundancy and prevents the same fact becoming inconsistent
  8. 8In SQL, which values need quotation marks?1
    1. AAll values
    2. BText values
    3. CNumeric values
    4. DField names only
  9. 9What happens if you select from two tables without a join condition?1
    1. AThe query returns nothing
    2. BEvery record from the first table is paired with every record from the second
    3. CSQL reports a syntax error
    4. DOnly matching records are returned automatically
  10. 10Which statement adds a new record to a table?1
    1. ASELECT
    2. BADD RECORD
    3. CUPDATE
    4. DINSERT INTO

Section B

Answer in the space provided. The number of marks tells you how many separate points to make.

  1. 1StateState the difference between a primary key and a foreign key.2
  2. 2WriteA Students table has the fields studentID, firstName, surname, yearGroup and tutorGroup. Write an SQL query to return the first name and surname of every student in year 11, sorted by surname.4
  3. 3ExplainExplain why a library database stores borrower details in a separate table from loans, rather than repeating them on every loan record.4
  4. 4WriteWrite an SQL statement to change the year group of the student whose studentID is 4102 to 11.3

Mark scheme

Section A

1C2B3A4C5A6A7D8B9B10D

Section B

1 State the difference between a primary key and a foreign key. 2

One mark for each of the following, to a maximum of 2.

  • A primary key uniquely identifies each record in its own table
  • A foreign key holds the primary key of a record in another table, linking the two

Model answer. A primary key is a field whose value is unique for every record in its own table, so it identifies that record without ambiguity. A foreign key is a field in one table that holds the primary key value of a record in another table, which is what creates the link between the two tables.

2 A Students table has the fields studentID, firstName, surname, yearGroup and tutorGroup. Write an SQL query to return the first name and surname of every student in year 11, sorted by surname. 4

One mark for each of the following, to a maximum of 4.

  • Selects the correct two fields
  • Uses FROM Students
  • Filters on yearGroup with no quotation marks round the number
  • Sorts by surname

Model answer. The query is: SELECT firstName, surname FROM Students WHERE yearGroup = 11 ORDER BY surname. The two field names appear after SELECT because only those columns are wanted, Students follows FROM because that is where the data is, the WHERE clause filters to year 11 with no quotation marks because the value is numeric, and ORDER BY surname sorts the results alphabetically, which is ascending by default.

3 Explain why a library database stores borrower details in a separate table from loans, rather than repeating them on every loan record. 4

One mark for each of the following, to a maximum of 4.

  • Repeating the details would store the same data many times, which is redundancy
  • Wasted storage is one cost
  • If a detail changes it would have to be corrected in every copy
  • Missing one copy would leave the database inconsistent, with no way to tell which version is right

Model answer. Repeating the borrower's name and address on every loan record means storing the same facts dozens of times over, which wastes storage but, far more importantly, creates a maintenance problem. When a borrower moves house, every single copy of their address has to be found and changed. If one is missed the database then holds two different addresses for the same person with nothing to say which is correct, and any report using the data may be wrong. Storing the borrower once in its own table and linking each loan to it by a foreign key means each fact exists in exactly one place, so a change is a single edit and inconsistency is impossible. It also allows a borrower to be recorded before they have taken out any books, which a combined table could not do.

4 Write an SQL statement to change the year group of the student whose studentID is 4102 to 11. 3

One mark for each of the following, to a maximum of 3.

  • Uses UPDATE on the correct table
  • Uses SET to change yearGroup to 11
  • Includes a WHERE clause identifying the single student

Model answer. The statement is: UPDATE Students SET yearGroup = 11 WHERE studentID = 4102. The WHERE clause is essential rather than optional here: without it the statement would set the year group of every student in the table to 11, and it would do so immediately with no confirmation.