Unit exam
Databases: Exam Questions
Exam-style questions across all four lessons. Work through them in order, then reveal mark-scheme style answers using the button on each question.
Section 1 - Database concepts
1
State the difference between a flat-file database and a relational database.
2 marksAll boards
Award 1 mark each for:
A flat-file database stores all data in a single table [1]; a relational database splits data across multiple linked tables [1].
Accept: flat-file causes redundancy; relational uses primary/foreign keys to link tables.
A flat-file database stores all data in a single table [1]; a relational database splits data across multiple linked tables [1].
Accept: flat-file causes redundancy; relational uses primary/foreign keys to link tables.
2
The table below is from a flat-file database used by a school.
| StudentID | StudentName | CourseName | CourseCode | TeacherName |
|---|---|---|---|---|
| 1 | Alice Smith | GCSE Maths | MA11 | Mr Jones |
| 2 | Bob Brown | GCSE Maths | MA11 | Mr Jones |
| 3 | Clara Davis | GCSE CS | CS11 | Ms Patel |
(a) Identify the primary key in this table. [1]
(b) Describe one problem that could occur if Mr Jones changes his name. [2]
(c) Explain how a relational database would solve this problem. [3]
All boards
(a) StudentID [1]
(b) TeacherName appears in multiple rows [1]; if only some rows are updated, the data becomes inconsistent/contradictory [1]. (Update anomaly - accept any valid description)
(c) In a relational database, teacher information would be stored in a separate Teachers table [1]; each course would reference the teacher using a foreign key [1]; the teacher's name is stored only once so a single update keeps all data consistent [1].
(b) TeacherName appears in multiple rows [1]; if only some rows are updated, the data becomes inconsistent/contradictory [1]. (Update anomaly - accept any valid description)
(c) In a relational database, teacher information would be stored in a separate Teachers table [1]; each course would reference the teacher using a foreign key [1]; the teacher's name is stored only once so a single update keeps all data consistent [1].
3
Give three rules that a primary key must satisfy.
3 marksAll boards
Award 1 mark each for any three of:
It must be unique (no two records can share the same value) [1]; it must not be null/empty [1]; it should be stable (its value should not change) [1]; it must identify exactly one record in the table [1].
It must be unique (no two records can share the same value) [1]; it must not be null/empty [1]; it should be stable (its value should not change) [1]; it must identify exactly one record in the table [1].
4
A database stores information about employees and their departments. Suggest an appropriate data type for each of the following fields, giving a reason for each choice.
(a) EmployeeID [2]
(b) IsFullTime [2]
(c) HourlyRate [2]
All boards
(a) Integer [1]; because it is a whole number used for identification, no arithmetic needed, no decimal part [1].
(b) Boolean [1]; because the field can only hold one of two values: TRUE or FALSE / yes or no [1].
(c) Real/Float [1]; because the hourly rate may include pence (decimal portion) - an integer would round down and lose precision [1].
(b) Boolean [1]; because the field can only hold one of two values: TRUE or FALSE / yes or no [1].
(c) Real/Float [1]; because the hourly rate may include pence (decimal portion) - an integer would round down and lose precision [1].
Section 2 - Relational design
5
Explain the purpose of a foreign key in a relational database.
2 marksAll boards
A foreign key is a field in one table [1] that references/matches the primary key of another table, creating a link between them [1].
Accept: allows data to be retrieved from multiple tables; maintains referential integrity.
Accept: allows data to be retrieved from multiple tables; maintains referential integrity.
6
A music streaming service has two entities: Users (UserID, Username, Email) and Playlists (PlaylistID, PlaylistName, UserID).
(a) Identify the primary key and foreign key in the Playlists table. [2]
(b) State the type of relationship between Users and Playlists. [1]
(c) A user can also like many songs, and a song can be liked by many users. What additional table would be needed to store this? State its fields and keys. [3]
All boards
(a) Primary key: PlaylistID [1]; Foreign key: UserID [1].
(b) One-to-many (one user has many playlists) [1].
(c) A junction/link table is needed, e.g. UserLikes [1]; containing UserID (FK) and SongID (FK) [1]; the primary key could be a composite of UserID + SongID [1]. Accept any valid junction table with both foreign keys correctly identified.
(b) One-to-many (one user has many playlists) [1].
(c) A junction/link table is needed, e.g. UserLikes [1]; containing UserID (FK) and SongID (FK) [1]; the primary key could be a composite of UserID + SongID [1]. Accept any valid junction table with both foreign keys correctly identified.
Section 3 - SQL queries
Use the following table for questions 7 to 10:
| StudentID | FirstName | LastName | Age | Year | IsEnrolled | Score |
|---|---|---|---|---|---|---|
| 1001 | Alice | Smith | 15 | 10 | TRUE | 82 |
| 1002 | Bob | Brown | 16 | 11 | TRUE | 67 |
| 1003 | Clara | Davis | 15 | 10 | FALSE | 91 |
| 1004 | Dan | Evans | 16 | 11 | TRUE | 54 |
| 1005 | Eve | Foster | 15 | 10 | TRUE | 78 |
| 1006 | Frank | Green | 17 | 12 | TRUE | 43 |
| 1007 | Grace | Hill | 16 | 11 | FALSE | 88 |
7
Write a SQL query to retrieve the first name and score of all students in Year 10.
2 marksAll boards
SELECT FirstName, Score
FROM Students
WHERE Year = 10
Award 1 mark for correct SELECT with correct fields [1]; 1 mark for correct WHERE clause [1]. Accept any case for SQL keywords.
FROM Students
WHERE Year = 10
8
Write a SQL query to retrieve the full name (both fields) of all enrolled students with a score greater than 70, ordered by score from highest to lowest.
4 marksAll boards
SELECT FirstName, LastName
FROM Students
WHERE IsEnrolled = TRUE AND Score > 70
ORDER BY Score DESC
1 mark: correct SELECT (FirstName, LastName) [1]; 1 mark: IsEnrolled = TRUE in WHERE [1]; 1 mark: Score > 70 in WHERE with AND [1]; 1 mark: ORDER BY Score DESC [1].
FROM Students
WHERE IsEnrolled = TRUE AND Score > 70
ORDER BY Score DESC
9
State the names returned by the following query:
SELECT FirstName, LastName FROM Students WHERE LastName LIKE '%s'
2 marks
All boards
Alice Smith and Dan Evans [1 mark each, or 2 for both]. The pattern '%s' matches any last name ending in the letter s. Smith ends in h - no. Brown - no. Davis - no. Evans ends in s - yes. Foster - no. Green - no. Hill - no. Note: case sensitivity depends on the database system; accept answers assuming case-insensitive matching.
10
Write a SQL query to add a new student to the table: ID 1008, Jade King, age 15, Year 10, enrolled, score 72.
3 marksINSERT INTO Students (StudentID, FirstName, LastName, Age, Year, IsEnrolled, Score)
VALUES (1008, 'Jade', 'King', 15, 10, TRUE, 72)
1 mark: INSERT INTO Students with field list [1]; 1 mark: VALUES with correct values in matching order [1]; 1 mark: strings in quotes, numbers without quotes, correct format overall [1].
VALUES (1008, 'Jade', 'King', 15, 10, TRUE, 72)
11
Frank Green has left school. Write a SQL statement to remove his record from the Students table. State one danger of this type of command.
3 marksDELETE FROM Students WHERE StudentID = 1006
1 mark: correct DELETE FROM … WHERE syntax [1]; 1 mark: correct condition identifying Frank Green (accept StudentID = 1006 or FirstName='Frank' AND LastName='Green') [1]; 1 mark for danger: omitting the WHERE clause would delete all records in the table / the deletion is permanent and cannot be undone [1].
Section 4 - SQL injection
12
Explain what a SQL injection attack is and describe how it could be used to bypass a website's login system.
4 marksAll boards
SQL injection is where malicious SQL code is entered into an input field [1]; and the application inserts the input directly into a SQL query without sanitising it [1]; so the attacker's input is executed as SQL rather than treated as data [1]; an example such as entering
Max 4 marks. Award marks for: definition, mechanism, effect/result, specific example.
' OR '1'='1 makes the WHERE condition always true, returning all rows and bypassing the password check [1].Max 4 marks. Award marks for: definition, mechanism, effect/result, specific example.
13
Explain how parameterised queries prevent SQL injection attacks.
3 marksAll boards
Parameterised queries separate the SQL structure from the user input [1]; the input is passed as a parameter rather than concatenated into the SQL string [1]; so the database always treats the input as a data value, never as SQL code / even if the input contains SQL characters, they cannot affect the query's structure [1].
14
A web application builds a SQL query using this vulnerable code:
query = "SELECT * FROM accounts WHERE username = '" + username + "'"
(a) A user enters the username:
admin'--. Write the full SQL query that would be executed. [2](b) Explain the effect of this input on the query. [2]
(c) Rewrite the Python code using a parameterised query. [2]
All boards
(a)
(b) The -- is a SQL comment character [1]; everything after it (including the closing quote and any password check) is ignored by the database, so the query returns the admin account without checking a password [1].
(c)
SELECT * FROM accounts WHERE username = 'admin'--'
[2 marks - 1 for correct query structure, 1 for showing -- comment correctly](b) The -- is a SQL comment character [1]; everything after it (including the closing quote and any password check) is ignored by the database, so the query returns the admin account without checking a password [1].
(c)
query = "SELECT * FROM accounts WHERE username = ?"
cursor.execute(query, (username,))
[1 mark for ? placeholder, 1 mark for passing username as a separate parameter]
cursor.execute(query, (username,))