A LEVEL OCR

Computer Science

Unit 4 - Exchanging Data

Homework Workbook

1
Homework 1 – Compression, Encryption and Hashing
Files are compressed using a variety of techniques. An image file is compressed differently
than a folder containing text files.

1. Compression reduces the size of a file by removing unnecessary data.

a) Explain the difference between lossy and lossless compression:

b) Explain why Run-Length Encoding work well to compress a sound file without losing
any of the original data:

c) Sound files can also be encoded using lossy compression techniques. Describe
why these techniques reduce files more significantly than RLE but can have a
detrimental effect on the quality of the file:

2. Encryption is used to obscure data from being understood without authorisation. Files
can be encrypted using various methods.
Two methods of encryption are the Caesar or Vernam cipher.

a) Explain why the Caesar cipher can be easily cracked. [1]

2
 b) The Vernam cipher is proven to be unbreakable. Explain why.

3. (a) Explain the difference between symmetric and asymmetric encryption.

(b) Explain briefly how asymmetric encryption is used.

4. (a) Explain what is meant by hashing.

(b) Explain why paswords and PINs may be hashed, and how a user’s password can
then be verified.

3
Homework 2 – Database Concepts
Define the following terms:
(i) Entity [1]

(ii) Primary key [1]

(iii) Secondary key

[2]

(iv)Foreign key [2]

3. A cinema club shows up to three screenings of films on a given day. They need a
database to keep track of which films have been shown on which dates. It has been
suggested that they use one table (relation) about the films they show, which will hold
the following data:
FilmID, Title, Duration, Male Lead, Female Lead, Date shown, Time shown, Tickets
sold.
(a) Give a reason why this is not a satisfactory solution.

4
(b) Show how the data could be reorganised into four relations, using the notation
Entity name (attribute1, attribute2, attribute3 …)
An underscore indicates the primary key. The entities that have been identified are
Film,
Actor, ActorInFilm and Showing.

(c) Identify one foreign key and one composite key in any of the relations.

(d) Complete the Entity-Relationship diagram below to show the degree of the
relationships between the entities (one-to-many, etc.)

Film ActorInFilm

Actor
Showing

5
 Homework 3 – Relational databases and Normalisation
1. A boat hire company stores details of its customers, boats and rentals in a database.
The relations in the database hold the following data:
Boat (BoatName, type, length, berths)
Customer (CustomerID, Surname, firstname, AddressLine1, AddressLine2, Town,
Postcode, DateOfBirth, email)
BoatHire (CustomerID, BoatName, HireDate, HireEndDate)
(a) The key in the BoatHire relation consists of three attributes.
What is the name given to a key made up of more than one attribute? [1]

(b) The relations in this database are in Third Normal Form.

State the properties of a relation in Third Normal Form. [3]

(c) State, with reasons, why it is important that relations in a database are in
Third Normal Form. [6]

(d) Complete the Entity-Relationship diagram below to show the degree of the
relationships between the entities. [3]

2. A database is to be created to hold data about students at a Sixth Form College and the
subjects they study.
Students study a number of subjects, and each subject has one subject leader.

6
The table below is a first attempt at the design of the database.
Tuto
StudentID Name TutorGroup Subject Level SubjectLeader
r
History A AJH
S1000 Bella 2 CKE Geography A BJG
Economics AS CKE
English AS DRE
S2000 Jane 3 KPR French A FJF
Russian A KPR
English A DRE
S3000 Greg 1 DRE
Geography A BJG

(a) Explain, with reference to the data, why the table is not in First Normal Form (1NF).
[2]

(b) The design is changed to:

Student (StudentID, Name, TutorGroup, Tutor)
StudentSubject (StudentID, Subject, Level, SubjectLeader)
Show how the data given in the table above would be held in these two tables. [3]
Table: Student
Nam
StudentID TutorGroup Tutor
e

Table: StudentSubject
Subjec
StudentID Level SubjectLeader
t

7
(c) A student is not allowed to study the same subject at A and AS Level. [1]

What is the primary key of the table StudentSubject?

(d) The two tables Student and StudentSubject are related. Explain how this is achieved
using a primary and a foreign key. [2]

(e) Explain why the table StudentSubject is not in Second Normal Form (2NF). [2]

(f) Explain why the table Student is not in Third Normal Form (3NF) [2]

8
 Homework 4 – Introduction to SQL
A second-hand car dealer keeps a record of customers who have purchased cars.
The data held on each table includes the following fields:
Customer

Car

(a) Write the entity descriptions for the entities Customer and Car in the format:
Entity(attribute1, attribute2, ….) [2]

(b) Identify primary and foreign keys, where they exist, in both tables. [2]

(c) Draw an entity relationship diagram showing the relationship between

the two tables. [2]

9
(d) Write an SQL statement using keywords SELECT… FROM… WHERE…ORDER BY
to extract a list of the make, model, year registered and price of all 5-door cars costing less
than £2000, sequenced in descending order of price. [4]

(e) Write an SQL statement using keywords SELECT… FROM… WHERE…ORDER BY

to extract a list, sequenced by surname and initials, of customer IDs, surnames, initials and
email addresses of all customers who purchased cars between 2009 and 2011 inclusive,
together with the make of car and price paid. [6]

(f) Write an SQL statement to extract all the details of cars

with make beginning with “V”.
[2]

(g) The Car database is in Third Normal Form. Explain what this means. [2]

10
Homework 5 – Defining and updating tables using SQL.
1. The entities Flight and Passenger are linked in a one-to-many
relationship.
The table Flight has the following compulsory fields:
FlightID CHAR (5) (Primary key field)
Date DATE (dd/mm/yy) (Primary key field)
DestinationID CHAR (4)

The table Passenger has the following compulsory fields:

PassportNo CHAR (8) (Primary key)
Surname VARCHAR (20) (Variable length)
Firstname VARCHAR (20) (Variable length)
FlightID CHAR (6)
Date DATE

(a) What is a composite primary key? Give an example.

(b) Identify a foreign key in the tables above. What is the purpose of the foreign key?

(c) Write an SQL statement to extract the passport numbers, surnames and firstnames
of all passengers on Flight BA401 on 12/11/2016.

(d) Write an SQL statement to create the table Flight.

11
(e) Write an SQL statement to create the table Passenger

(f) Write an SQL statement to add two new columns for Telephone,
16 characters (variable length) and email, 20 characters (variable length) to the
Passenger table.

(g) Write an SQL statement to delete the column email from the Passenger table.

(h) Write an SQL statement to insert a record for passenger Jo Harris, passport
number 12345678.

12
13
Homework 6 – Transaction Processing
1. a. State a method of data capture that would be suitable for each of the following
applications:

Application Data capture method

Multiple choice exam answers

Number plates recorded by speed camera

Bank clerk entering cheque details

(b) (i) What is meant by Electronic Data Interchange (EDI)?

(i) Name an application for which this would be a suitable method of data exchange.

2. (a) Describe briefly a potential problem that could arise in a multi-user database.

(b) What does ACID stand for in the context of databases?

(c) Explain briefly what ACID is, and its purpose.

14
3. Explain what is meant by redundancy in the context of computer systems and why it is
essential for some organisations.

4. Name and briefly describe a serialisation technique which ensures that transactions do
not overlap in time and ensures that updates are not lost.

15