Relational Databases (AQA GCSE Computer Science)

A relational database is being developed to store information about the games that are box available to play at a games café and the advance bookings that have been made for those games. Each game has a unique name.

The database contains two tables: Game and Booking.

The database is currently being tested by the person who has developed it so the database tables only contain a small amount of data that is being used for testing.

The contents of the tables are shown in Figure 5.

Figure 5

Game

Booking

State the field in the Booking table that is a foreign key.

A teacher keeps a record of books box loaned to students.

The teacher uses a relational database containing three tables, BookCopy, Student and Loan. Figure 5 shows some data from the tables.

Figure 5

BookCopy

Student

Loan

Identify two choices to show which of the following statements are benefits of relational databases.

State one field in the Loan table that is a foreign key.

Figure 5 has been included again below

Figure 5

BookCopy

Student

Loan

Barry Tucker has returned their copy of the book Python Basics.

Complete the SQL to delete the loan record for the book PB002.

DELETE FROM .............................

WHERE ...................................

Define the term relational database

Figure 4

Member

Award

A new member joins the youth club. The following SQL is run to add their details to the database:

INSERT INTO
(5, 'Alina', 'Ahmed', '2020-11-30')

Some of the SQL has been replaced by labels.

State the SQL that should have been written in place of the labels and

A relational database is being developed to store information about the games that are box available to play at a games café and the advance bookings that have been made for those games. Each game has a unique name.

The database contains two tables: Game and Booking.

The database is currently being tested by the person who has developed it so the database tables only contain a small amount of data that is being used for testing.

The contents of the tables are shown in Figure 5.

Figure 5

Game

Booking

Due to a change in layout at the café, the Game table with an ID of 2 is no longer suitable for games that can have more than four players. The manager needs to find out the customer, date and time of all bookings made for the game table with an ID of 2 that are for a game that can have more than four players.

Write an SQL query that could be used to find this information for the manager. The results should be shown in date order.

The LengthOfGame field shows the average amount of time it takes to play a game in minutes.

A query to add 10 minutes to the length of time taken for all games that have a Complexity of more than three is shown in Figure 6.

Figure 6

UPDATE Game
SET LengthOfGame = LengthOfGame + 9
WHERE Complexity <= 3

The query contains two errors.

Refine the query in Figure 6 to correct the errors.

Figure 5

BookCopy

Student

Loan

Year 11 students must return their books after they have finished their GCSE exams.

Using the database shown in Figure 5, write an SQL query that lists all the loans for students who are in Year 11.

The query must only return:

• both names of the student

• the ID of the book borrowed

• the deposit paid.

The results must be in ascending order of the students’ last names.

A relational database box has been developed for a youth club to store information about their members and the awards they are given.

The database contains two tables: Member and Award

Figure 4 has been included below.

Figure 4

Member

Award

The youth club needs to produce a report listing the members who have been given the Leader award. The report must include both names of each member and the date the award was presented.

Write an SQL query that could be used to find this information. The results must be in order of the date the awards were presented, starting with the earliest.