1.3.2 Databases Exam Questions
Paper 1 · Computer Systems · 13 past paper questions · 35 marks
Exam questions for 1.3.2 Databases
13 OCR past paper questions (35 marks) with mark schemes
Answer each question, reveal the mark scheme, then award yourself marks. Scores and answers are saved in this browser so you can see which topics need more work.
Download Questions & Mark Scheme (PDF)Past paper questions
- Question 1H446/01 · June 20249 marks
Rosa has recently opened a new leisure centre. Currently she has 150 members, however, she is estimating that this will increase to around 10,000 members. Rosa will store members' details in a database. She will also store gym class bookings and appointments with fitness instructors. Rosa is considering using either a flat file database or a relational database. Discuss the benefits and drawbacks of flat file databases and relational databases and which one would be the most suitable for Rosa.
Show mark scheme
Levels of response question (9 marks) AO1: Flat file stores data in single table, often plain text. Benefits: easy to set up, all records in one place. Drawbacks: lots of redundant data, slower searching, difficult to manage/expand. Relational stores data in multiple tables linked by primary/foreign keys. Benefits: less duplication, easier to manage/expand, faster searching, more secure. Drawbacks: more difficult to set up, need technical knowledge for normalisation, more expensive (need DBMS). AO2: With 150 members flat file is suitable. As business expands, flat file problematic - lots of redundant data, inconsistencies. Relational more efficient - membership details stored once, ID used for bookings. Allows future expansion with additional tables. AO3: Relational database most suitable. Although more complex and needs normalisation, stores less repeated data, allows easy maintenance/expansion, easier to keep data secure.Mark yourself: how many marks did you get?/ 9 - Question 2H446/012 marks
State two benefits of using a relational database instead of a flat file database.
Show mark scheme
Relational data allows for less redundancy of data/less repeated data Relational databases improve the consistency of data Relational databases allow for complex queries and/or searches to be performed (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 3H446/012 marks
State the difference between a primary key and a foreign key.
Show mark scheme
A primary key will only appear once in a table/is a unique identifier A foreign key may appear multiple times in a table/may not be unique (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 4H446/01 · June 20232 marks
State two requirements for a database to be in First Normal Form (1NF).
Show mark scheme
Each attribute name is unique Primary key identified No repeated attributes All data in attributes must be atomic (cannot be further split up) (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 5H446/01 · June 20222 marks
A relational database supports ACID transactions. Describe what is meant by a transaction being durable.
Show mark scheme
(Committed) data/transaction is not lost… in case of power / system failure (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 6H446/01 · June 20223 marks
Explain how record locking can be used to ensure that the ACID principle of isolation is achieved when carrying out multiple transactions.
Show mark scheme
The outcome of concurrent transactions is the same as if transactions were completed sequentially Record locking allows one user/process to access/modify record level data at any one time So data that is being used elsewhere cannot be modified // data that is being modified elsewhere cannot be used (3 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 3 - Question 7H446/01 · June 20211 mark
State what is meant by the term 'primary key'.
Show mark scheme
Field that is unique/does not repeat (1 mark)Mark yourself: how many marks did you get?/ 1 - Question 8H446/01 · June 20193 marks
Explain what is meant by referential integrity, giving an example.
Show mark scheme
Ensuring that changes are consistent across a database If a record is removed all references to it are removed A foreign key value must have a corresponding Primary key value in another table Example: a user being removed will result in their reviews being removed (3 marks - 1 per bullet, max 3)Mark yourself: how many marks did you get?/ 3 - Question 9H446/01 · June 20192 marks
Describe what is meant by the term 'Atomic' in the context of ACID transactions.
Show mark scheme
A transaction can only fully complete or not complete / cannot partially complete Example: it should not be possible for the review to be added without the (average) rating being updated (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 10H446/01 · June 20193 marks
State what the letters CID refer to in ACID.
Show mark scheme
C - Consistency I - Isolation D - Durability (3 marks - 1 per letter)Mark yourself: how many marks did you get?/ 3 - Question 11H446/01 · June 20182 marks
An airport holds details of flights in a database using the table Flight. An extract of the table is shown below. The airline wishes to ensure the database is normalised. Describe why the database can be considered to be in First Normal Form.
FlightID FlightNumber DestinationCode DestinationName DepartureDate DepartureTime 1355 OC0089 JFK John F. Kennedy 03/07/18 09:50 1453 CS1573 LHR Heathrow 03/07/18 10.30 1921 OC7750 JFK John F. Kennedy 04/07/18 8.30 1331 AM0045 YHZ Halifax 04/07/18 14.25 1592 HB0326 RTM Rotterdam 04/07/18 19.10 1659 CS0123 LHR Heathrow 04/07/18 07.20 An extract of the table Flight Show mark scheme
No Repeating fields/data Data is atomic Has a primary Key (2 marks - 1 per bullet, max 2)Mark yourself: how many marks did you get?/ 2 - Question 12H446/01 · June 20182 marks
An airport holds details of flights in a database using the table Flight. An extract of the table is shown below. The airline wishes to ensure the database is normalised. Describe why the database can be considered to be in Second Normal Form.
FlightID FlightNumber DestinationCode DestinationName DepartureDate DepartureTime 1355 OC0089 JFK John F. Kennedy 03/07/18 09:50 1453 CS1573 LHR Heathrow 03/07/18 10.30 1921 OC7750 JFK John F. Kennedy 04/07/18 8.30 1331 AM0045 YHZ Halifax 04/07/18 14.25 1592 HB0326 RTM Rotterdam 04/07/18 19.10 1659 CS0123 LHR Heathrow 04/07/18 07.20 An extract of the table Flight Show mark scheme
Is in First Normal Form Every field is dependent on the primary key (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2 - Question 13H446/01 · June 20182 marks
An airport holds details of flights in a database using the table Flight. An extract of the table is shown below. The airline wishes to ensure the database is normalised. Describe why the database can not be considered to be in Third Normal Form.
FlightID FlightNumber DestinationCode DestinationName DepartureDate DepartureTime 1355 OC0089 JFK John F. Kennedy 03/07/18 09:50 1453 CS1573 LHR Heathrow 03/07/18 10.30 1921 OC7750 JFK John F. Kennedy 04/07/18 8.30 1331 AM0045 YHZ Halifax 04/07/18 14.25 1592 HB0326 RTM Rotterdam 04/07/18 19.10 1659 CS0123 LHR Heathrow 04/07/18 07.20 An extract of the table Flight Show mark scheme
Has a transitive relationship / A non-key field depends on another non-key field DestinationName depends on DestinationCode (example) (2 marks - 1 per bullet)Mark yourself: how many marks did you get?/ 2
- 1Relationships: 1:1, 1:M, M:M (requires junction table)
- 2Normalisation: 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies)
- 3SQL: SELECT, FROM, WHERE, JOIN, INSERT, UPDATE, DELETE
- 4Primary key: Unique, not NULL; Foreign key: Links tables together
- •Practice answering questions under timed conditions
- •Check your answers against the mark scheme carefully
- •Focus on understanding the marking points, not just memorising answers
- •Review past paper questions from multiple years to spot patterns