5/3/25, 2:44 PM QuestionBank Test

Questions
18M.2.SL.TZ0.3
Jackson City University has a Music Department that provides music lessons to students in a
number of high schools in the city.

The Jackson City University Music Department teachers visit the different schools in the city
to teach students a range of musical instruments.

The following diagram shows an unnormalized table of student data.

a. Explain one benefit of normalizing a database. [3]

b.

Identify three ways that incorrect data could be prevented from being added into the
School_phone_no field.

[3]

c.

Outline what would be necessary to make the above unnormalized table conform to
1st Normal Form (1NF).

[2]

d. Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

about:blank 1/37
5/3/25, 2:44 PM QuestionBank Test

[8]

e. Explain the difference between 2nd Normal Form (2NF) and 3rd Normal Form (3NF).

[3]

18M.2.HL.TZ0.4
Basking Coats is a business that sells textile products such as shirts, coats and trousers. The
company was formed in 1970 and has numerous shops in Europe and South-East Asia. To
ensure their marketing is targeted at appropriate customers, Basking Coats has asked
Singalytics, a data analytics company, to assist them in improving their marketing strategy.

Extract, Transform, Loading (ETL) processes can be used to clean up data for use in a
database warehouse. When ETL is carried out, certain precautions should be taken.

Data in the Singalytics data warehouse is stored with a timestamp.

a. Describe how deviation detection can be used to analyse this data. [2]

b. Outline why data warehouses tend to use unnormalized data sets. [3]

c.

Identify three precautions to be taken before extraction is carried out on the database.

[3]

d. Outline why data warehousing is time dependent. [2]

e.

Explain why Basking Coats could use association analysis to improve the marketing of
its products.

[4]

f.

Basking Coats has decided to use an object-oriented database rather than a relational
database to store its data.

Explain why Basking Coats would use an object-oriented database rather than a
relational database to store its data.

[6]

17N.2.SL.TZ0.2
about:blank 2/37
5/3/25, 2:44 PM QuestionBank Test

A group of art students have taken three examinations in the school year. Each of the three
examinations has a maximum mark of 100. Each student’s total mark for the year is the sum
of the marks from the three examinations.

The students have passed unless their marks meet one or both of the following failing
conditions:

Scoring less than 30 marks on any one of the three examinations.

Scoring a total mark that is less than 150.

Consider the following relation created by the teacher of this group of students.

CLASS_TABLE

a.

With reference to the relation CLASS_TABLE distinguish between data and information.

[3]

b.i. Define the term entity. [1]

b.ii. State the entity for this example relation. [1]

c. Identify an appropriate data type for Student_ID. [1]

d. Explain the role of data validation and data verification. [4]

e. Explain how Total could be validated. [2]

f.i.

Describe the steps in a query that will output the names of all students who earned the
maximum mark on Exam_Two.

[2]

f.ii.

Describe the steps in a query that will output the Student_IDs of all candidates who
passed.
about:blank 3/37
5/3/25, 2:44 PM QuestionBank Test

[4]

17N.2.SL.TZ0.3
Consider the following example relation. It holds data about a number of teachers and
students from different schools who volunteer to support the local community on particular
days.

SCHOOL_VOLUNTEERS_TABLE (School_Name, Code, Address, Date, Num_Volunteers)

The key attributes are underlined.

a.i. State what is meant by redundant data in databases. [1]

a.ii. Explain one issue that can be caused by redundant data in a database. [2]

b.

Identify three characteristics of the 1st Normal Form (1NF) which are evident in this
relation.

[3]

c. Explain why a compound key is used for the SCHOOL_VOLUNTEERS_TABLE relation.

[2]

d. The following shows the normalized SCHOOL_VOLUNTEERS_TABLE relation:

SCHOOLS_TABLE VOLUNTEERS_TABLE
Code, School_Name, Address Code, Date, Num_Volunteers

Discuss whether these relations are in third normal form (3NF). [5]

18N.2.SL.TZ0.3
about:blank 4/37
5/3/25, 2:44 PM QuestionBank Test

A shop sells only fruits and vegetables. Data about the products sold is held in the relation
Greengrocer as follows:

GREENGROCER

Prod_ID is the primary key.

The relation above would be represented using the following notation:

Greengrocer (Prod_ID, Prod_Name, Prod_Price, Supp_Name, Supp_Contact, Supp_Phone)

a.i. Identify an appropriate data type for Prod_Price. [1]

a.ii.

Construct the steps in a query to output the names of all products supplied by Veggy
Co. with prices in the range from 4.00 to 10.00 inclusive.

[3]

b. Outline the redundant data in this relation. [2]

c.i.

Explain the problems that may arise for the following function performed on the given
relation. In your answer, you should give an appropriate example: Inserting a new tuple
with an item that is supplied by existing supplier.

[2]

c.ii.

Explain the problems that may arise for the following function performed on the given
relation. In your answer, you should give an appropriate example: Deleting a tuple from
about:blank 5/37
5/3/25, 2:44 PM QuestionBank Test

the relation.

[2]

c.iii.

Explain the problems that may arise for the following function performed on the given
relation. In your answer, you should give an appropriate example: Modifying a specific
attribute value in the tuple.

[2]

d. Construct the database in the 3rd normal form (3NF) using the notation

Greengrocer (Prod_ID, Prod_Name, Prod_Price, Supp_Name, Supp_Contact,

Supp_Phone)You must show all your workings.
You must show all your workings. [8]

20N.2.SL.TZ0.3
WineForAll is a retailer that sells wine in its stores. Each store sells wine from a number of
vineyards.

The following extract from the Wine file contains unnormalized data.

about:blank 6/37
5/3/25, 2:44 PM QuestionBank Test

a. Define the term record. [1]

b.

Identify the steps to create a query to find the vineyards and names of fruity wines
where the quantity in stock is between 25 and 35 bottles.

[3]

c.i.

Identify the steps to create a non-persistent derived field called TotalPrice, which
would hold the total value of wine stored for each record.

[2]

c.ii.
about:blank 7/37
5/3/25, 2:44 PM QuestionBank Test

Outline why the inclusion of a derived field will not affect the normalization of a
database.

[2]

d. Construct the 3rd Normal Form (3NF) of the unnormalized Wine file. [6]

e.

Outline why a single-field primary key is not always an appropriate solution for
normalized databases.

[2]

19N.2.SL.TZ0.1
One-to-7 is an international organization that works with teachers and other educators. One
department within this organization provides an online forum for teachers to discuss ideas
for lessons and to share resources.

In order to access this forum teachers are required to submit the following information,
which will be stored in a table in the database.

Figure 1: Online form to register personal details to the One-to-7 forum

Once the submit button on the online form has been selected, the personal data is input into
the database.

Once the teacher is registered they can post comments on the forum.

The One-to-7 database in managed by the database administrator (DBA).

a. Identify one reason why the teacher’s name has been split into two fields. [1]

about:blank 8/37
5/3/25, 2:44 PM QuestionBank Test

b.

Outline one reason why there may be concerns about the amount of personal
information that is requested.

[2]

c. Outline why the transaction needs to be atomic in the context of this scenario. [2]

d.

Explain how transactions are managed to ensure isolation when registered teachers
add comments to a discussion thread on the forum.

[3]

e. Identify two tasks that are carried out by the database administrator (DBA). [2]

f.

The DBA is considering using the email address as the primary key, but is concerned
that many of the 250 000 educators who are registering for this online forum may have
more than one email address. Users may create duplicate accounts, deliberately or
accidently, by using different email addresses as usernames.

Explain the factors that would need to be considered in using a composite primary key
instead of only using the email address.

[4]

19N.2.SL.TZ0.3
Trucking On, a logistics and haulage company based in Marseille, France, keeps track of its
lorries and operations in a database.

Lorries are made up of the truck and a trailer. Trucks are usually coupled with the same trailer
but sometimes trailers are moved to a different truck for part of the year.

Each driver only uses one truck.

The following LORRY table shows information about the lorries.

about:blank 9/37
5/3/25, 2:44 PM QuestionBank Test

Trailer space is measured in cubic metres.

The table can also be represented as:

LORRY (TruckID, Truckmake, Energysource, Driver, Tel, TrailerID,

Trailerspace, Coupled_from, Coupled_to)

The table has been split into two tables following the rules of normalization. The resulting
two tables are shown with the primary key underlined:

LORRY (TruckID, Truckmake, Energysource, Driver, Tel)

TRAILER (TrailerID, Trailerspace, Coupled_from, Coupled_to, TruckID)

a. Identify two characteristics that make a database unnormalized. [2]

b. Outline why data redundancy may be a problem in the LORRY table. [2]

c.

Identify the steps to create a query to find the names and telephone numbers of the
drivers who drive lorries that have more than 60 cubic metres of trailer space.

[4]

d.

Normalize the database to 3NF. Use the same format as shown, ensuring that primary
keys are clearly indicated by underlining them.
about:blank 10/37
5/3/25, 2:44 PM QuestionBank Test

[7]

18M.2.SL.TZ0.2
ShowTime is a local theatre that allows online booking for its various shows.
All customers are able to access the database to book a movie of their choice.

However, some of the customers have received incorrect information when their booking
has been made. This has been caused by update anomalies.

ShowTime have introduced a database recovery system in case the database becomes
corrupted.

a. Identify two types of update anomaly. [2]

b. Outline two methods of database recovery that can be used to restore the system.

[4]

c. Identify three tasks that are carried out by the database administrator (DBA). [3]

d.

Customers can choose a credit card as their method of payment. However, some
customers are concerned that their personal information could get shared with
unauthorized third parties.

Explain one way that the DBA at ShowTime can ensure the anonymity of the customers
is maintained.

[3]

19M.2.SL.TZ0.1
Marble Reading Book Stores (MRBS) is a chain of bookstores based in London. The stores
want to keep information about the books they sell, the authors of the books and the
publishers they work with. The assumptions made when the database was created were:

a publisher can publish books from one or more authors

an author can write one or more books.

Three of the tables in the MRBS database are shown below:

PUBLISHER

about:blank 11/37
5/3/25, 2:44 PM QuestionBank Test

AUTHOR

BOOK

The MRBS database undergoes many transactions.

a. Construct the entity-relationship diagram (ERD) for this scenario. [2]

b. Outline why data validation is difficult for the Book_Title attribute. [2]

c. State the result from the following query: SELECT Book_Title [1]
FROM BOOK
WHERE Genre = "Non-fiction"
AND ISBN = '0-98124-612-2'

d. Construct a query to find the titles of the books published by “Orlando Crux”. [4]

e. Outline why atomicity is important within a database. [2]

about:blank 12/37
5/3/25, 2:44 PM QuestionBank Test

f.

Outline how data consistency can be maintained in transactions in this database

system.

[2]

g. Some data in the MRBS database is redundant.

Outline one problem caused by redundant data. [2]

19M.2.SL.TZ0.3

about:blank 13/37
5/3/25, 2:44 PM QuestionBank Test

Armour Hardware Company has the following data about salespersons and the quantities of
items sold.

Each salesperson can sell many different products.

SALES_PERSON

a. Outline two reasons why databases are normalized. [4]

b. Outline why the SALES_PERSON table is not in 1st Normal Form (1NF). [2]

c. Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.

[8]

d.

Outline why it is necessary to ensure that referential integrity is maintained in

databases.

about:blank 14/37
5/3/25, 2:44 PM QuestionBank Test

[2]

e. Outline why a primary key may consist of more than one attribute. [2]

20N.2.SL.TZ0.1
The Driving Licensing Agency stores information about individuals who hold a driving license
and/or own vehicles.
The following rules apply:

Each individual may only hold one driving license.

Each individual may own more than one vehicle.
Each vehicle may be owned by one individual only.

When an individual applies for a driving license, they have to complete a license application
form. The following is an extract from that form:

The data in the form shown above is stored in the Person table. The license application form
also requires an individual’s medical information. This is stored in a table called
PersonMedical.
The following extract is a sample of the medical questions that are asked.

Figure 2: A sample of the medical questions asked on the license application form

about:blank 15/37
5/3/25, 2:44 PM QuestionBank Test

a.

Construct the entity-relationship diagram (ERD) that shows the relationship between
the individual, their driving license, and their vehicle(s).

[2]

b. Explain why Date of birth has been separated into three fields. [3]

c.

Explain two reasons why medical information should not be stored in the Person table.

[6]

d. Outline two issues caused by storing redundant data. [4]

e.

Outline two situations where data stored by the Driving Licensing Agency may need to
be open to interrogation by other parties.

[4]

about:blank 16/37