SECTION 2 – Paper 2 (CS-2210)
Database
- Worksheet
IGCSE, O & A level Computer Science
O level - Information and Communication Technology
A level – Information Technology
Sir Kashif Ali – 03233772371
Morning Affiliation:
Online Affiliation:
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 1 A radio station keeps a database of all its music CDs. Here is part of this
database
a) How many records are there in the database section?
________________________________________________________________________________
b) If the following structured query language (SQL) was input, write down which data items would be
output
SELECT Reference Number
FROM CDs
WHERE CD length <60 AND number of hit tracks > 1;
________________________________________________________________________________
c) Write down a structured query language (SQL) to select which CDs are special edition or have more
than 10 tracks.
________________________________________________________________________________
d) The following structured query language (SQL) was input, write down which data items would be
output
SELECT Reference Number
FROM CDs
ORDER BY CD length (mins) Descending;
________________________________________________________________________________
1
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 2 A DATABASE was set up showing statistics for some states in the USA. Part of
the database is shown below:
a) How many records are in this section of the database?
________________________________________________________________________________
b) How many fields are in each record?
________________________________________________________________________________
c) The following structured query language (SQL) was input,
SELECT Ref
FROM DATABASE
WHERE Population (millions) = 4.0 OR Number of houses (millions < 4.0);
Write down records will be found
________________________________________________________________________________
d) Write down the structured query language (SQL) to find out which states have an area over 100,000
square miles and where it takes less than 25 minutes to get to work.
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________
2
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
e) What should be the key field in this database? Give a reason for your choice
_______________________________________________________________________________
Question # 3 Part of a HOLIDAY file is shown below.
The following codes represent the meals.
MEALS RO – Room only
BB – Bed and breakfast
HB – Half board
FB – Full board
a) State how many records are show in the table?
________________________________________________________________________________
b) State how many fields are shown in the table?
________________________________________________________________________________
c) The records are sorted into the key field order. State which field is used as the key field and give a
reason why.
________________________________________________________________________________
________________________________________________________________________________
d) Complete the table below to show the data type and field size for each field
FIELD NAME DATA TYPE FIELD SIZE
BOOKING NO
MEALS
NIGHTS
PRICE ($)
3
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
e) State two reasons why the MEALS data have been coded
________________________________________________________________________________
________________________________________________________________________________
f) Suggest two more fields that could be used in this file.
________________________________________________________________________________
g) Give one situation, in each case, when the data would be amended, inserted and deleted.
Amended: _______________________________________________________________________
Inserted: ________________________________________________________________________
Deleted _________________________________________________________________________
h) Data needs to be validated. Using fields from the database as examples, describe two different validation
checks which could be performed on the data.
Name of field: ___________________________________________________________________
Validation Check: ________________________________________________________________
Name of Field: __________________________________________________________________
Validation Check: _______________________________________________________________
Question # 4 four validation checks and four descriptions shown below:
Draw a line to link each validation check to the correct description:
Validation check Descriptions
Presence Check Number between two
given values are accepted
Data is of a particular
Range Check
specified type
Data contains an exact
Type Check
number of characters
Length Check Ensures that some data
have been entered
4
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 5 For each of the four statements in the table, place a tick in the correct column
to show whether it is an example of validation or verification.
Statement Validation Verification
To automatically check the accuracy of a bar code
To check if the data input is sensible
To check if the data input matches the data that has been supplied
To automatically check that all required data field have been
completed
Question # 6 A database, MARKS, was set up record the test results for a class of students.
Part of the database is shown below:
Student Name Class ID Math English Science History Geography
Paul Smith 007 70 56 65 62 59
Ravi Gupta 0009 29 34 36 41 44
Chin Hwee 0010 43 47 50 45 52
John Jones 0013 37 67 21 28 35
Diana Abur 0001 92 88 95 89 78
Rosanna King 0016 21 13 11 27 15
a) Give the number of fields that are in each record
______________________________________________________________________________
b) State which field you would choose for the primary key.
______________________________________________________________________________
Give a reason for choosing this field.
______________________________________________________________________________
5
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
c) The structured query language (SQL) was input, below selects all students with more than 60
marks in History or more than 60 marks in Geography.
SELECT Student Name
FROM MARKS
WHERE History >60 OR Geography >60;
Show what would be output.
______________________________________________________________________________
______________________________________________________________________________
d) Write the structured query language (SQL), show the student name only of all students with
less than 40 marks in both Maths and English
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Question # 7 A database, PROPERTY, was set up to show the prices of properties for sale
and the features of each property. Part of database is shown below:
Property Brochure Number of Number of
Garden Garage Price in $
Type No Bedrooms Bathrooms
Bungalow B17 7 4 Yes Yes 750,000
Apartment A09 2 1 No No 100,000
House H10 4 2 Yes No 450,000
House H13 3 2 Yes No 399,000
Apartment A01 2 2 No Yes 95,000
Apartment A16 1 1 No No 150,000
House H23 3 1 No Yes 250,000
House H46 2 1 Yes Yes 175,000
a) Give the number of fields that are in each record.
______________________________________________________________________________
6
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
b) State which field you would choose for the primary key
______________________________________________________________________________
Give a reason for choosing this field
______________________________________________________________________________
c) State the data type of you would choose for each of the following fields
Garage: _______________________________________________________________________
Number of Bedrooms: ___________________________________________________________
Price in $: _____________________________________________________________________
d) The structured query language (SQL) below was input,
SELECT Prince in $, Brochure No
FROM PROPERTY
WHERE Property type = ‘House” AND (Number of bedrooms >2 AND Number of Bathrooms
>1)
ORDER BY Price in $ Ascending;
Show what would be output:
______________________________________________________________________________
______________________________________________________________________________
e) Write the structured query language (SQL) to select and show the brochure number, property
type and price of all properties with a garage below $200,000.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
7
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 8 A motor boat Hire Company decides to set up a database to keep information
about boats that are available for hire. The database table, BOAT, will contain the following
fields:
Boat name; Model; Engine Power (in hp); Number of Seats; Life Ratt (whether there is a life
ratt kept on the boat); Day Price (price for a day’s hire).
a) Give the data type you would choose for each field.
Boat Name: _____________________________________________________________
Model: _________________________________________________________________
Engine Power: ___________________________________________________________
Number of Seats _________________________________________________________
Life Ratt: _______________________________________________________________
Day Price: ______________________________________________________________
b) State a validation check that you can perform on each of these field. Each validation check
must be different.
Boat Name: _____________________________________________________________
Model: _________________________________________________________________
Number of Seats _________________________________________________________
Day Price: ______________________________________________________________
c) Write the structured query language (SQL) to select and show the Boat Name, Model and Day
Price of a day’s hire for all boats with 4 Seats and an Engine Power of more than 100hp.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
8
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 9 A picture gallery owner has decided to set up a database to keep information
about the pictures he has for sale. The database table, PICTURE, will contain the following
fields:
Title; Artist; Description; Catalogue Number; Size (area in square centimeters); Price;
Arrived (date picture arrived at gallery); sold (whether picture is already sold)
a) State what data type you would choose for each field.
Title: ___________________________________________________________________
Artist: __________________________________________________________________
Description: _____________________________________________________________
Catalogue Number: _______________________________________________________
Size: ___________________________________________________________________
Price: __________________________________________________________________
Arrived: ________________________________________________________________
Sold: ___________________________________________________________________
State which field you would choose for the primary key.
______________________________________________________________________________
b) Give a validation check that you can perform on each of these fields. Each validation check
must be different.
Catalogue Number: _______________________________________________________
Size: ___________________________________________________________________
Price: __________________________________________________________________
Arrived: ________________________________________________________________
c) Write the structured query language (SQL) to select and show the Catalogue Number, Title and
Price of all unsold pictures by the artist “twister”
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
9
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 10 A database PLAYPRODUCTION, was set up to show the performance dates,
prices and number of seats available at a theater specializing in Shakespeare productions.
Performance Number Number Prices Stalls Price Circle
Play
Date Seats stalls Seats Circle Seats $ Seats $
As You Like It 01/07/2016 120 90 20 30
As You Like It 02/07/2016 85 45 30 40
As You Like It 09/07/2016 31 4 30 40
Macbeth 14/07/2016 101 56 25 35
Macbeth 15/07/2016 50 34 25 35
Macbeth 16/07/2016 12 5 35 50
Julius Caesar 22/07/2016 67 111 20 20
Julius Caesar 23/07/2016 21 24 15 15
A Comedy of Errors 30/07/2016 45 36 35 45
a) Give the number of fields that are in each record.
______________________________________________________________________________
b) State the data type you would choose for each of the following fields.
Play:
_____________________________________________________________________________
Number Seats Stalls:
________________________________________________________________
Price Stalls Seats $:
_________________________________________________________________
c) The following structured query language (SQL) was input,
SELECT Play, Performance Date
FROM PLAYPRODUCTION
WHERE Number Seat Stall >100 OR Number Seat Circle >100;
Show what would be output from the query.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
10
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
d) Write the structured query language (SQL) to select all the productions with at least six seats
left in the circle and show the Play, Performance Date and Price Circle Seats in Performance Date
order.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Question # 11 A database, THEATRETOURS, was set up to show the tour dates, towns,
number of seats and prices in local currency for a Shakespeare play.
Town Tour Date Number of Seats Price local Currency
Wigan 18/08/2016 120 15.00
Dumfries 20/08/2016 160 12.50
Turin 25/08/2016 200 17.00
Macon 27/08/2016 75 18.00
Bordeaux 29/08/2016 170 20.00
Algiers 01/09/2016 125 1350.00
Windhoek 05/09/2016 65 90.00
Windhoek 05/09/2016 65 90.00
Port Elizabeth 10/09/2016 200 110.000
a) Explain why none of the fields in the database can be used as a primary key.
______________________________________________________________________________
______________________________________________________________________________
b) State a field that could be added as primary key.
______________________________________________________________________________
Give a reason for choosing this field.
______________________________________________________________________________
11
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
c) Write the structured query language (SQL) to provide a list of tour dates and seat prices in
alphabetic order of town.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
Question # 12 A television (TV) store has a database table, TVSTOCK, for its new range of
television. The table stores the screen size of each TV, whether it will show 3d, whether the
screen is curved or flat, if the internet is available on the TV, if it has a built-in hard disk
drive and the price. Part of the database is shown below:
TVID ScreenSize 3D CurvedFlat Internet HDD Price
TV80CVINT 80 YES CV YES YES $7,000.00
TV65CVINT 65 YES CV YES YES $5,000.00
TV60CVINT 60 YES CV YES YES $4,500.00
TV60FTINT 60 YES FT YES YES $4,000.00
TV55CVINT 55 YES CV YES NO $3,000.00
TV55FTNIN 55 YES FT YES NO $3,500.00
TV55FTNIN 55 YES FT NO NO $3,000.00
TV50CVINT 50 YES CV YES NO $2,500.00
TV50FTNNT 50 YES FT YES NO $2,000.00
TV50FTNIN 50 YES FT NO NO $1,750.00
TV42FTINT 42 YES FT YES NO $1,500.00
TV37FTINT 37 NO FT YES NO $1,200.00
TV20FTNIN 20 NO FT NO NO $800.00
TV15FTNIN 15 NO FT NO NO $400.00
a) State the type of the field TVID and give a reason for your choice.
______________________________________________________________________________
______________________________________________________________________________
12
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
b) Complete the table with the most appropriate data type for each field.
Field Name Data type Validation Check
ScreenSize
3D
CurvedFlat
Internet
HDD
Price
c) Write the structured query language (SQL) to provide a list of all of the curved screen TVs that
have a built-in hard disk drive. Make sure the list only display the TVID, the price and the screen
size in ascending order of price.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
13
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question # 13 A database table. SHEEP, is used to keep a record of the sheep on a farm.
Each sheep has a unique ear tag, EARnnnn, n is a single digit. The farmer keeps a record of
the date of birth, the gender, the current weight of each sheep in kilograms.
a) Identify the four fields required for the database. Give each field a suitable name and data type.
Provide a sample of data that you could expect to see in the field.
Field 1 Name:
_____________________________________________________________________
Data Type:
________________________________________________________________________
Data Sample:
______________________________________________________________________
Field 2 Name:
_____________________________________________________________________
Data Type:
________________________________________________________________________
Data Sample:
______________________________________________________________________
Field 3 Name:
_____________________________________________________________________
Data Type:
________________________________________________________________________
Data Sample:
______________________________________________________________________
Field 4 Name:
_____________________________________________________________________
Data Type:
________________________________________________________________________
Data Sample:
______________________________________________________________________
b) State the field that you would choose as the primary key.
______________________________________________________________________________
c) Write a query (SQL) to identify the ear tags of all the male sheep weighting over 10 kilograms.
Only display the ear tags.
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
14
Compiled and Prepared by Sir Kashif Ali #CSOLEVEL #2210
Question 14: A music streaming service has a new database table named Songs to store details of
songs available for streaming. The table contains the fields:
• SongNumber – the catalogue number, for example AG123
• Title – the title of the song
• Author – the name of the song writer(s)
• Singer – the name of the singer(s)
• Genre – the type of music, for example rock
• Minutes – the length of the song in minutes, for example 3.75
• Recorded – the date the song was recorded.
(a) Identify the field that will be the most appropriate primary key for this table. [1]
______________________________________________________________________________
(b) Complete the table to identify the most appropriate data type for the fields in Songs [2]
Field Data type
SongNumber
Title
Recorded
Minutes
(c) Explain the purpose of the structured query language (SQL) statements. [3]
SUM (Minutes) FROM Songs WHERE Genre = "rock";
COUNT (Title) FROM Songs WHERE Genre = "rock";
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
______________________________________________________________________________
15
