Databases (OCR A Level Computer Science): Exam Questions

Explain how record locking can be used to ensure that the ACID principle of isolation is achieved when carrying out multiple transactions.

State two requirements for a database to be in First Normal Form (1NF).

Details of all users that have accessed the robot are stored in a database table called TblAccessLog. This table stores the username and user type of each user. When a user accesses the robot, the current date is added to the DateAccessed field for that user.

A selection of the data from this table is shown here. Username is the key field.

Explain why the structure of TblAccessLog means that this database is not in First Normal Form (1NF).

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below.

The Adverts field indicates if customers will be shown adverts. true indicates that customers will be shown adverts, and false indicates that adverts are not shown.

Write Structured Query Language (SQL) to return the Username and FirstName fields for all customers who see adverts.

When new customers join the streaming service, their name, email address and contact details are captured so that they can be entered into the database.

Identify one method of capturing a new customer’s personal data, describing why this method is suitable.

Sometimes a company may need to move or backup its data they hold about customers.

Identify two methods of exchanging data with other computer systems

A database supports ACID transactions. ACID stands for Atomicity, Consistency, Isolation and Durability

Describe what is meant by a transaction being durable.

OCR Insurance uses a computer system to calculate the price that customers pay for car insurance.

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below.

Write the SQL statement that would show only the CustomerID and Surname fields for customers with the Title “Miss” or “Mrs”.

OCR Insurance uses a computer system to calculate the price that customers pay for car insurance.

Customers’ details are stored in the flat file database table Customer. An extract of the table is shown below.

Describe how the flat file database structure could be altered to efficiently allow each customer to insure multiple cars at the same time. (You may assume each car is insured to only one customer.)

A hotel uses a computer system to keep track of room bookings. The hotel staff are able to query a database to discover which rooms are booked or which rooms are free.

The hotel stores data about rooms, customers and bookings in a database. Each customer can book multiple rooms and each room can be booked multiple times.

Draw an Entity Relationship Diagram for this database.

Explain what is meant by the term ‘referential integrity’ and how this could potentially be broken.