Design (SQA National 5 Computing Science): Exam Questions

A primary school is organising a range of 30 activities for its 550 pupils for the last day of term. The organiser wishes to create and use a database.

The following are essential.

Each pupil selects one activity. They must return a form which contains their name, class and emergency contact details.

The organiser provides class teachers with a list of pupils’ names and chosen activities.

Each activity has a leader and a unique activity name. Activity prices range from £2 to £30. The organiser provides a list for each activity leader, showing each pupil’s name, class and emergency contact details.

The organiser records which pupils have returned a form so that they can search for pupils who have not signed up to an activity.

Complete the entity-relationship diagram on the opposite page for the database by:

• naming the entities

• drawing any missing attributes from either entity

• drawing the relationship between the entities

• naming the relationship between the entities.

Identify the attribute that would be stored as a Boolean field when the database is implemented.

When the database is implemented validation is added to several fields. (i)

(i)The primary school has 14 different class names. For example P1A, P4B, P6/7A.

Describe how validation of this field could be implemented when the database tables are created.

[2]

(ii) State one field where range validation would be appropriate.

[1]

A car retailer has four showrooms.

A relational database is used to store details of the four showrooms and the cars they have for sale.

Design a query that would output the model, number of seats and the showroom manager for all the Jaguar cars located in Glasgow.

A restaurant stores employee details in a database. Each employee is trained as either a chef, a server, a manager or a cleaner. Sample data from the database is shown below.

Complete the missing attribute and attribute type in the table below.

Errors are made when data is entered for new employees.

State one attribute where restricted choice validation could be used to reduce errors.

A band booking company has a range of bands that can be booked for various events. It wants a database to store information on bands and their bookings.

A band can play more than one event. An event can only have one band.

When a band is being booked for an event the form below is completed.

When the band has been booked, the following e-mail is sent to the organiser.

Complete the entity relationship diagram by:

• identifying the key attributes

• drawing the relationship between the entities

• naming the relationship.

Customers’ personal details have been stolen.

State one requirement of the UK General Data Protection Regulation (GDPR) that the company should have implemented to prevent this from happening.

Caledonian Wheels organises a motorsport racing championship. A database is used to store data on racing teams and their individual drivers. The data stored in the database is shown below.

Caledonian Wheels want to share details about all the UK based drivers who have had a successful year so far.

(i) Design a query to search the database for a driver’s full name, driver number and team name who have won more than three races and drive for a United Kingdom based team.

[4]

(ii)Describe how the SQL statement could be tested when the query is created.

[1]

Riley wants to add a new contact on their mobile phone.

State the attribute type that would be used to store the data identified by the arrow above.

Nathan cancels his ClipClop social media account.

Describe one implication for ClipClop under UK General Data Protection Regulation.

The Games Lab is a video game testing company that programmers can send games to for testing. Each game is allocated to a tester who works on several different games. Testers are based in three offices — Scotland, France and Australia — and only accept games rated 12A, 15 and 18.

(i) Use the information provided to complete the entity‑relationship diagram below by:

• identifying any additional key attributes

• drawing any missing attributes

• drawing the relationship between entities

• naming the relationship between the entities.

[4]

(ii) State the validation to be applied to the Rating attribute.

[1]

A database stores data on a local netball league. The Club table stores data of the clubs that play in the league. The Player table stores data on each player.

Sample data from each table is shown below.

Explain the purpose of the foreign key clubID in the Player table.

Design a query that could be used to create a list of names and the division for Goal keepers that are over 30 years old.

A database table stores data about balloon orders.

Balloons are available in a limited number of colours.

State the type of validation that should be implemented to achieve this.

State the attribute type required for the following attributes.

A window cleaning company wishes to create a database to store information about staff and the houses they clean. The company provides examples of information they would like to include.

Each staff member is allocated a number of houses to clean. They clean the windows of the same houses each month.

Use the information provided to complete the entity relationship diagram below by:

• adding the missing key attribute

• drawing the relationship between the entities

• naming the relationship between the entities.

Bright Stars organise and run classes for children. Classes are organised into blocks, each running for 8 weeks.

The table Class stores data about the classes available.

The table Booking stores data about the children and their parents/guardians.

Sample data from each table is shown below.

Spring classes for babies are ending soon. An email needs to be sent to all parents to offer them a place in the Summer classes.

Design a query that could be used to generate a list of names and email addresses of parents with a child who attends a Spring class for babies.

Every book that is published is given a unique reference as shown below.

978-1-471-83603-9

State the data type that would be used to store this reference in a database

Aabish is writing a book about the history of computer animation studios and the characters created by each studio.

As part of her research she makes notes about the studios and characters.

She decides to store this information in a relational database using two entities called character and studio.

Complete the diagram below to show the relationship between the character entity and the studio entity

Aabish populates the database with several studios and hundreds of characters. Sample data from each table is shown below.

Design a query that could be used to create a list of character names and styles created by ‘K. Bell’ at the Japanese branch of Goban studios.

[5]

(ii) Aabish uses the database to identify the characters with the least number of appearances from all studios.

Aabish wants to produce similar output for only the Desney studio characters.

Complete the SQL statement below that would produce this output.

SELECT...................................................................................................

FROM .....................................................................................................

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

ORDER BY................................................................................................

[5]

A property renovation company requires a relational database to store information about the tasks to be carried out on each room of a house.

The functional requirements for the database are identified:

• Store details of each room in the house.

• Store details of each task.

• Output a list of tasks carried out by one of the following trades people: electrician, builder, plasterer, decorator, carpenter, plumber.

• Output a list of tasks completed for a single room.

(i) Complete the entity relationship diagram below by identifying key attributes.

[2]

(ii) Use the functional requirements above to identify the attribute in the ‘Task’ entity that would be implemented using restricted choice validation.

[1]

Explain why the General Data Protection Regulations do not apply to the information that will be stored in this database.