9 Databases
1 a Explain the meaning of the following database terminology.
Table: .................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
Record: ..............................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
Field: .................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
b Identify four basic data types used in a database and provide an example for each one.
1
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
2
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
3
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
4 ......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
Photocopying prohibited
Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
55
318472_09_IGCSE_OLCSPW_055-062.indd 55 24/04/21 1:30 PM
9 Databases
2 A single-table database, ICECREAM, has been set up to store the ice creams available for sale.
The details included about each type of ice cream are:
» Type – for example, 'choc ice', 'lolly' or 'cone'
» Flavour – for example, 'raspberry'
» Size – 'Small', 'Medium' or 'Large'
» Number in stock – for example, '34'
» Re-order level – for example, '20'.
a Write down names for the five fields that would be required. For each field state with a
reason the data type that should be used and give a sample of the contents of that field.
Field 1: .............................................................................................................................................................
Data type: ........................................................................................................................................................
Reason: .............................................................................................................................................................
............................................................................................................................................................................
Sample: .............................................................................................................................................................
Field 2: .............................................................................................................................................................
Data type: ........................................................................................................................................................
Reason: .............................................................................................................................................................
............................................................................................................................................................................
Sample: .............................................................................................................................................................
Field 3: .............................................................................................................................................................
Data type: ........................................................................................................................................................
Reason: .............................................................................................................................................................
............................................................................................................................................................................
Sample: .............................................................................................................................................................
Field 4: .............................................................................................................................................................
Data type: ........................................................................................................................................................
Reason: .............................................................................................................................................................
............................................................................................................................................................................
Sample: .............................................................................................................................................................
Photocopying prohibited
56 Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
318472_09_IGCSE_OLCSPW_055-062.indd 56 4/29/21 12:25 PM
9 Databases
Field 5: .............................................................................................................................................................
Data type: ........................................................................................................................................................
Reason: .............................................................................................................................................................
............................................................................................................................................................................
Sample: .............................................................................................................................................................
b i Explain why a primary key is needed in a database table.
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
ii Give the reason why none of the existing fields are suitable to use as a primary key.
.......................................................................................................................................................................
.......................................................................................................................................................................
iii Give a suitable field name, data type and sample for another field that could be used
as a primary key.
Name: ...........................................................................................................................................................
Data type: .................................................................................................................................................
.......................................................................................................................................................................
Sample: ......................................................................................................................................................
c Build the database table as specified in parts a and b. Write or paste a copy of your table
structure here.
Photocopying prohibited
Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
57
318472_09_IGCSE_OLCSPW_055-062.indd 57 24/04/21 1:30 PM
9 Databases
d Populate the database table with six records. Write or paste a copy of your populated table here.
3 a Explain what is meant by SQL.
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
b Explain the function of each of the following SQL statements.
i SELECT
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
ii FROM
.......................................................................................................................................................................
.......................................................................................................................................................................
Photocopying prohibited
58 Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
318472_09_IGCSE_OLCSPW_055-062.indd 58 24/04/21 1:30 PM
9 Databases
iii WHERE
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
iv SUM
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
c Identify two more SQL statements that you need to know for IGCSE Computer Science.
1
.......................................................................................................................................................................
2
.......................................................................................................................................................................
d Use the six statements from parts b and c to write SQL queries for the database table you
created in Question 2 to:
» Show the types, in alphabetical order, and sizes of all the ice creams that are in stock
» Show the total number of ice creams available to buy
» Count the number of ice creams that have stock below the reorder level.
Write or paste a copy of your SQL queries here.
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
Photocopying prohibited
Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
59
318472_09_IGCSE_OLCSPW_055-062.indd 59 24/04/21 1:30 PM
9 Databases
4 A single-table database, TEACHER, contains the details of the teachers in a school. The database
includes these fields:
Name – the teacher’s family name, for example, 'Yo'
Title – the teacher’s title, for example, 'Mr'
Licence – the teacher’s licence number, for example, 'L1234'
Gender – 'M' or 'F'
Subject – the main subject taught by the teacher, for example, 'Mathematics'
Class – the class the teacher tutors, for example, '2Y'
a i Identify the field that would be most suitable to use as a primary key. Give a reason
for your choice.
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
ii State the data type that could be used for each field.
Name: ...........................................................................................................................................................
Title: ............................................................................................................................................................
Licence: .......................................................................................................................................................
Gender: ........................................................................................................................................................
Subject: .......................................................................................................................................................
Class: ...........................................................................................................................................................
iii Build a database with the six records shown.
b i Identify a field that should be verified. Give a reason for your choice.
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
Photocopying prohibited
60 Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
318472_09_IGCSE_OLCSPW_055-062.indd 60 24/04/21 1:30 PM
9 Databases
ii Identify a field that could be validated. Give a reason for your choice.
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
iii Add your validation to your database.
Here is part of the database table, TEACHER:
Name Title Licence Gender Subject Class
Yo Mr L4579 M Mathematics 3Z
Sing Miss L6713 F Science 2X
Patel Mr L5421 M Geography 4Y
Teo Mrs L7681 F Mathematics 2Z
Young Mrs L6789 F English 3X
Ling Mr L4980 M Science 4X
c i State the output from this SQL query.
SELECT Title, Name
FROM TEACHER
WHERE Subject = 'Science';
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
ii Add the SQL statement to show the results in alphabetical order.
.......................................................................................................................................................................
.......................................................................................................................................................................
.......................................................................................................................................................................
iii Rewrite this SQL statement to include the mathematics teachers as well.
.......................................................................................................................................................................
.......................................................................................................................................................................
Photocopying prohibited
Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
61
318472_09_IGCSE_OLCSPW_055-062.indd 61 6/30/21 11:21 AM
9 Databases
iv Write a new SQL statement to count the number of mathematics teachers in the TEACHER
table.
v Check that the SQL statements work for your database.
d Write an SQL statement to display the names of all the teachers and their classes in
ascending order of class.
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
............................................................................................................................................................................
Check that your SQL statement works as expected.
Photocopying prohibited
62 Cambridge IGCSE and O Level Computer Science Algorithms, Programming and Logic Workbook
318472_09_IGCSE_OLCSPW_055-062.indd 62 24/04/21 1:30 PM
