X216/13/01

NATIONAL
QUALIFICATIONS
THURSDAY, 7 MAY
09.00 AM – 11.30 AM
INFORMATION
2015 SYSTEMS
ADVANCED HIGHER

Attempt all questions in Section I.

Attempt one sub-section of Section II.
Part A Information Systems Interfaces Page 12 Questions 6 to 9
Part B Online Database Systems Page 20 Questions 10 to 13
For the sub-section chosen, attempt all questions.
Read all questions carefully.
Do not write on the question paper.
Write as neatly as possible.
Each section should be answered in a separate answer book.

PB *X216/13/01* ©
 SECTION I Marks

Answer ALL questions in this section.

 n information system is developed using the Systems Analysis and Design Life
1. A
Cycle.

(a) Explain the term iterative as applied to the Systems Analysis and Design Life
Cycle. 1

A project plan is an important part of the development of an information

(b) 
system.
(i) Explain the importance of a project plan. 1
(ii) State two elements in a project plan. 2

(c) Document sampling is one investigative technique.

(i) Explain the purpose of document sampling. 2
(ii) Name one investigative technique other than document sampling. 1
When the investigations are complete, one result is background
(iii) 
information.
Name one additional result available once the investigations are
complete. 1

(d) The systems specification indicates restrictions on development.

(i) State one possible restriction that may be placed on any information
system development. 1
(ii) Name one other item outlined in the systems specification. 1

[X216/13/01] Page two

 SECTION I (continued) Marks

Various design, testing and evaluation techniques are used to make sure a completed
2. 
information system is correct.

(a) Logical design is produced during the development of an information system.

Describe the term logical design, stating one item that would be included. 2

(b) A process can be described using either Structured English or a graphical design
notation.
Name and describe one graphical design notation used for this purpose. 2

(c) During testing of an information system, different types of testing will be

carried out.
(i) Describe component testing. 1
(ii) Describe acceptance testing. 1
(iii) State one result of systematic testing. 1

Describe the purpose of evaluation in the development of an information

(d) 
system. 2

(e) Name the type of maintenance needed to fix bugs and errors not found during
testing. 1

[Turn over

[X216/13/01] Page three

 SECTION I (continued) Marks

3. 
An online store allows customers to purchase items via their website. The
functional requirements of the website include the following:

• Visitors to the site can browse the online product catalogue.

• I tems selected by a customer are added to (and can be deleted from) a
shopping basket.
•  hen a customer proceeds to the checkout, they must first login to their
W
account (new customers must first create a new customer account); a new
order is then created and item details are copied from the shopping basket.
•  he shopping basket is deleted when a customer pays for the order and
T
completes the checkout process; at this point, the order status is set to
“confirmed”.

(a) An extract from the entity event matrix for the online store is provided below.

Entities

BasketItem

OrderItem
Customer
Product
Basket

Order
Events

Visitor browses online catalogue

First item added to shopping basket
Additional item added to shopping basket
Item removed from shopping basket
Existing customer proceeds to checkout
New customer proceeds to checkout
Customer pays for order and completes checkout
process; order status set to “confirmed”


Copy and complete the matrix using the letters C(create), M(modify),
D(delete) and R(read) to indicate the effect that each event has on the relevant
entities. 7

[X216/13/01] Page four

 SECTION I (continued) Marks

3. (continued)

(b) Based on the full entity event matrix for the system, the entity life history
diagram for the Product entity is produced. This is shown below.

Product

Product Life Remove Product

Edit Details Remove from catalogue Remove category

Modify Price Change Description Change Category

(i) State one essential event that has been omitted from the diagram above. 1
(ii) Use the events in the diagram to explain the terms iteration and selection. 2

[Turn over

[X216/13/01] Page five

 SECTION I (continued) Marks

4. 
A private nursery is developing a relational database to help with the day-to-day
running of their business. Following normalisation to Third Normal Form, they
have identified that the following entities are required.

Child (ChildId, ParentID*, Gender, FirstName, LastName, Age, OtherDetails)

Parent(ParentID, ParentName, ParentGender, Address, TelNo, MobileTelNo,
OtherContactDetails)
MonthlyFee(FeeID, ParentID*, DateDue, AmountDue, AmountPaid)
Register(ChildID*, SessionID*, PresentYorN)
Session(SessionID, StartDate, StartTime, FinishTime)
Incident(IncidentID, ChildID*, IncidentTypeCode*, EventTime, EventDetails)
IncidentType(IncidentTypeCode, IncidentDescription)

Notes:
1. Parent details may be added to the system before their children attend.
2. Each child has only one parent record.
3. Although parents may have more than one child at the nursery, they are
charged a single monthly fee.
4. Each day has several sessions.
5. Incident types include: sickness, behaviour, achievement.
6. In this representation, underlined attributes represent primary keys,
while asterisked attributes represent foreign keys*.

(a) Draw an Entity Relationship Diagram for the information given above,
ensuring that the cardinality and optionality of each relationship are clearly
displayed. Also ensure that any weak entities and weak relationships are clearly
indicated. 8

(b) The data dictionary for the entities in the nursery database is developed.
(i) The Child entity includes the attribute Gender.
Describe a suitable validation for the Gender attribute in a nursery
database system. 1
(ii) The Child entity includes the attribute Age.
Describe a suitable validation for the Age attribute in a nursery database
system. 1

[X216/13/01] Page six

 SECTION I (continued) Marks

4. (continued)

(c) The nursery sends out a monthly summary to each parent. Along with the
payment details, the summary shows the details of the children and the
sessions they attended.
Explain the use made of two components of a relational database system that
are required to produce the monthly summary shown below.

AP Nursery Monthly Summary

12 Ingram Street Total Due: £1234
Fort George Total Paid: £1234
FG1 GF2 Amount Due: £0

Alison Russell
12 Lenora Street
Fort George
FG4 GF9

Date: 24 May 2015

Dear Alison

Please find listed below a summary of the session(s) your child/children

attended in the last month.

Name of Child: Jack

Date 01/04/15 08/04/15 15/04/15 22/04/15 29/04/15

Start 09:00 09:00 09:00 09:00 09:00

Finish 17:00 17:00 17:00 12:00 12:00

Name of Child: Sophie

Date 01/04/15 03/04/15 08/04/15 15/04/15 22/04/15 29/04/15

Start 09:00 09:00 09:00 09:00 09:00

Finish 17:00 17:00 17:00 12:00 12:00

Yours,

N. Teacher
2

(d) Test data values have to be produced as part of the test plan.
The entity Session includes the attributes StartTime and FinishTime.
State three sets of test data for StartTime and FinishTime that would fully
test the implementation of these attributes. Provide a short explanation for
your choice of each set. 3
[X216/13/01] Page seven [Turn over
 SECTION I (continued)

5. 
Mr Jordan Sim booked a flight to Orlando with FlyJet and received a Booking
Confirmation. When he checked in for his flight, he received a Boarding Pass and
Luggage Identification Tag. Each of these documents is shown below.

FlyJet Booking Confirmation Booking Ref 63740

FLYJET
BAGGAGE IDENTIFICATION TAG
PASSENGER: Mr Jordan Sim SIM / JORDAN MR
SQR NBR 152
DEPARTURE: Date 6th April 2015
Time
Airport
15:50
GLA — Glasgow International
TO
MCO ORLANDO

ARRIVAL: Date
Time
6th April 2015
19:30
FJ433
06 APR 2015
Airport MCO — Orlando International
FLIGHT: Code FJ433
Carrier FlyJet
Aircraft Boeing 747—400 TAG ID FJ433060615152

ADDITIONAL INFO: Class Economy Baggage Identification Tag

Meal Option Vegetarian
Frequent Flyer FJ1234567

Booking Confirmation

FLYJET
PASSENGER

SIM / JORDAN MR
GLA MCO
GLA MCO
FLIGHT NO DATE GATE BOARDING TIME SEAT NO.

FJ433 06 APR 31 1510 39F

PASSENGER

SIM / JORDAN MR
FLIGHT NO DATE SEAT NO.

FJ433 06 APR 39F

CARRIER SQR NBR
FLYJET 152
FREQUENT FLYER
BOARDING PASS CARRIER CLASS SQR NBR
FLYJET ECONOMY 152
FJ1234567

Boarding Pass

[X216/13/01] Page eight

 SECTION I (continued) Marks

5. (continued)

The following points should also be noted:

•  ach flight is uniquely identified by combining its FlightCode and the

E
DepartureDate.
•  he same FlightCode is used on different dates; the Aircraft, BoardingGate
T
and BoardingTime may change from day to day.
•  ach passenger on a flight has a unique Booking Ref; some passengers are
E
FrequentFlyers.
•  he departure and arrival details (Time and Airport) together with Carrier are
T
all dependent on the FlightCode.
•  ach passenger is allocated a SeatNumber and SequenceNumber (SQR NBR)
E
at check-in; the SequenceNumber indicates a passenger’s sequence in the
check-in queue.
•  ach passenger is permitted one item of baggage which is allocated a unique
E
TagID.
• The SeatNumber allocated determines the Class of the seat.

(a) Using FlightCode + DepartureDate as its primary key, create a single list of
attributes representing unnormalised form (UNF) of the FlyJet booking and
check-in system. 3

Normalise these attributes to produce a set of entities in Third Normal Form.

(b) 
Show all stages of the normalisation process from 1NF through to 3NF. 12

[END OF SECTION I]

[Turn over

[X216/13/01] Page nine

 [BLANK PAGE]

DO NOT WRITE ON THIS PAGE

[X216/13/01] Page ten

 SECTION II

Attempt ONE sub-section of Section II

Part A Information Systems Interfaces Page 12 Questions 6 to 9

Part B Online Database Systems Page 20 Questions 10 to 13

For the sub-section chosen, attempt all questions.

[Turn over

[X216/13/01] Page eleven

 SECTION II Marks
Part A—Information Systems Interfaces
Answer ALL questions in this part.

6. BikeDesigns is a company that sells custom-designed bikes online. Customers

select the bike components they wish to incorporate into their bike and submit
an order. The company checks that these components are available and then
assembles the components and sends the finished bike to the customer. These
processes are described below.

TAKING AN ORDER
Customers provide their personal details, design their bikes online and then
submit their orders. Details of the bike ordered are stored in the customer
order file. The customer details are stored in the customer file and the
transaction details are stored in the accounts receivable file.

DEALING WITH THE ORDER

Using detail from the customer order file, the company checks its inventory file
to see whether the components needed are in stock. Any parts not in stock are
ordered from a component company whose contact details are obtained from
the suppliers file. Details of any components ordered are stored in the supplier
orders file.

CONSTRUCTING THE BIKE

Any components ordered are delivered from the supplier along with an invoice.
The supplier orders file and the inventory file are updated and details of the
invoice are stored in the accounts payable file. Once assembly of the bike is
complete, the bike is despatched to the customer and the customer order file
updated.

PAYMENTS
Detail of the money due to the bike company and money owed to the
component supplier are obtained from the two account files and sent to the
company bank.

Using the description above, draw a level one data flow diagram for these
(a) 
processes. 11

[X216/13/01] Page twelve

 SECTION II Marks
Part A—Information Systems Interfaces (continued)

6. (continued)

(b) BikeDesigns decides to create a mobile phone app which will allow customers
to design their bikes on their mobile phones or tablet computers.

BikeDesigns
Home > FrameParts > Handlebars

Choose Handlebars

Racing

Straight

Bull Horn

Help Back Continue

(i) Describe two factors which have influenced the decision to create this
app. 2
(ii) A feasibility study for the development of the app is to be undertaken.
State one reason why a feasibility study should be carried out. 1
(iii) The LUCID methodology is used during the development of the app.
State who will be involved at the design foundation stage and describe two
tasks performed. 3
(iv) (A) Describe two ways that the design of the app as shown is suitable
for novice users. 2
(B) Other than economic reasons, state one reason why the company
might consider that it is not necessary to alter the design for
expert/frequent users. 1
(v) Justify why the interface mode is considered to be sensory. 1
[X216/13/01] Page thirteen [Turn over
 SECTION II Marks
Part A—Information Systems Interfaces (continued)

During the development of an information system, a number of different interface

7. 
design methods can be used.

(a) Consider the two systems below which both relate to the Chisholm hotel.

(i) The hotel is looking to introduce a new automated check-in for

customers.

The first screen will present a welcome message.

The customer presses a start button to move to the next screen

which asks for the customer’s name and post code. Having entered
the details, the customer then presses the continue button. The
next screen displays a message saying that the room key cards are
being produced.

Once the room key cards are issued, the system returns to the initial
welcome screen, ready for the next customer.

Name one suitable method to represent the design of the interface for

the new check-in system and justify why it would be an appropriate
choice. 2

(ii) Instead of hanging paintings around the hotel, the management

would like to use digital screens to display artwork produced by
local artists. Each screen will display a total of 12 images. The
images will be displayed one at a time and will change every
5 minutes, cycling through the images in a set sequence.

If they wish to, hotel guests can interact with the system by
touching one of the screens. This will open a new window and
display information about the artist along with a number of options.
These options will allow guests to request information about local
art galleries and art shows.

When the guest is finished viewing the information, the screen

will return to the original image and resume scrolling through the
12 images.

Name one suitable method to represent the design of the interface for

the new digital display system and justify why it would an appropriate
choice. 2

[X216/13/01] Page fourteen

 SECTION II Marks
Part A—Information Systems Interfaces (continued)

7. (continued)

(b) The hotel is developing a new website for its Scottish themed restaurant. The
prototype below shows the proposed layout of one of the pages on the website.

Banner
468x60

Home

Home Haggis Steak Pie Oatcakes Pies Recipes

Scottish Food On-Line Chef in Action

(i) Create a feature set for the website based on the prototype shown above. 3
(ii) Use the prototype shown above to explain the difference between the
terms syntax and semantics. 2
Quantitative feedback from early usability testing of the interface is
(iii) 
shown in the chart below. The vertical axis rates the feedback in the
range 0 to 5, where 0 is a poor rating and 5 is excellent.

Questionnaire Feedback
5·0
4·5
4·0
3·5
3·0
2·5
2·0
1·5
1·0
0·5
0
time to speed of task subjective
learn performance user
satisfaction

Suggest one way of improving the interface by using the feedback,

justifying your answer with reference to the chart. 2

[X216/13/01] Page fifteen [Turn over

 SECTION II Marks
Part A—Information Systems Interfaces (continued)

8. 
Wemyss library has recently introduced self-service kiosks which provide a
number of member services: members can use the kiosks to search for particular
books, check books out of the library, return books, pay any fines that are due or
update their membership details.

(a) State two physical constraints associated with this type of kiosk. 2

(b) When members first use one of the kiosks, they are asked to provide additional
details as indicated below:

Update Member Details

Email Address (optional): aperson@mymail123.com

Return Date Notification 1 day prior to due date

2 days prior to due date
1 week prior to due date

Personal Interests: Select Personal Interests

Personal Interests Animation

Animation Art
Database Development Baking
Crafts
Database Development

Update

Explain why the entry of the Email Address demonstrates a textual

(i) 
interface. 1
 xplain why the entry of Personal Interests demonstrates a graphical
(ii) E
interface. 1

[X216/13/01] Page sixteen

 SECTION II Marks
Part A—Information Systems Interfaces (continued)

8. (b) (continued)

(iii) D
 uring initial usability testing, the entry of Personal Interests was
achieved in a similar way to the entry of Email Address.
Explain how the feedback gathered from user error rates helped to
determine that selection from a drop-down list was a more appropriate
method of input for this information. 2

(c) The kiosks incorporate several features associated with an intelligent interface.
(i) Describe one feature of natural language interaction that would make it
easy for library members to search for particular books. 2
(ii) 
The kiosks automatically detect the height of users and adapt the
interface by providing information appropriately, either at the top or the
bottom of the screen.
(A) State the type of predictive and adaptive interface that is used for
this purpose. 1
(B) Describe how eye tracking could be used to determine the success of
this feature of the kiosks. 1
(C) This feature of the kiosks is especially important for wheelchair
users. State the inspection method which took this into consideration
during usability testing. 1
(iii) Suggest one use that could be made of adaptive menus that is based on a
library member’s list of Personal Interests. 1
(iv) An avatar is used to teach library members how to
use each of the services provided by the kiosks.
State the type of documentation exemplified by the
avatar. 1

(d) The services provided by the new kiosks were introduced as part of a phased
conversion.
Explain why this is an appropriate method of introducing self-service kiosks to
a library. 2

(e) After a period of 6 months, the library uses a survey to gather views on the
kiosks from library members.
Explain how a survey of this type would be carried out. 2

[X216/13/01] Page seventeen [Turn over

 SECTION II Marks
Part A—Information Systems Interfaces (continued)

9. HealthyLife is designing a mobile phone app which will let users track their health
by allowing them to enter various pieces of data such as weight, blood pressure, etc
and obtain various displays.

12:00

My Health App

Weight Glucose B.P.

Diary Charts

Statistics Manage

(a) (i) Explain how low fidelity prototyping could be used in the design of the
app. 2
(ii) Rapid application development (RAD) could be used to develop the
app. Give one advantage of using RAD rather than a graphical design
technique. 1
(iii) Explain the difference between horizontal and vertical prototyping by
referring to the items shown in the prototype shown above. 2
(iv) Assess the suitability of co-discovery as a means of testing this prototype. 2

(b) A walkthrough is carried out as a quality inspection of the app. State who would
carry this out and describe how it would be done. 2

(c) To find out users’ feelings about the app, self reporting logs are used. Explain
why this method of inquiry might not be suitable in this situation. 2

[END OF SECTION II — PART A]

[X216/13/01] Page eighteen
 [Turn over for Part B, Question 10 on Page twenty

DO NOT WRITE ON THIS PAGE

[X216/13/01] Page nineteen

 SECTION II Marks
Part B—Online Database Systems

Answer ALL questions in this part.

10. B
 ikeDesigns is a company that sells custom-designed bikes online. Customers
select the bike components they wish to incorporate into their bike and submit
an order. The company checks that these components are available and then
assembles the components and sends the finished bike to the customer. These
processes are described below.

TAKING AN ORDER
Customers provide their personal details, design their bikes online and then
submit their orders. Details of the bike ordered are stored in the customer
order file. The customer details are stored in the customer file and the
transaction details are stored in the accounts receivable file.

DEALING WITH THE ORDER

Using detail from the customer order file, the company checks its inventory file
to see whether the components needed are in stock. Any parts not in stock are
ordered from a component company whose contact details are obtained from
the suppliers file. Details of any components ordered are stored in the supplier
orders file.

CONSTRUCTING THE BIKE

Any components ordered are delivered from the supplier along with an invoice.
The supplier orders file and the inventory file are updated and details of the
invoice are stored in the accounts payable file. Once assembly of the bike is
complete, the bike is despatched to the customer and the customer order file
updated.

PAYMENTS
Detail of the money due to the bike company and money owed to the
component supplier are obtained from the two account files and sent to the
company bank.

Using the description above, draw a level one data flow diagram for these
(a) 
processes. 11

(b) Describe two E-commerce features needed to allow customers to purchase

bikes. 2

(c) BikeDesigns staff frequently need to update their website. They have a content
management system (CMS) which allows them to do this without learning any
programming skills. Describe two other features of a CMS that would help
their business. 2

[X216/13/01] Page twenty

 SECTION II Marks
Part B—Online Database Systems (continued)

10. (continued)

(d) One of the suppliers of bike components has an information system which is
incompatible with the BikeDesigns information system.
(i) Describe how EDI transaction standardisation and translation software
would help the transfer of an invoice from the supplier to BikeDesigns. 3
 ive two reasons why it might be preferable for BikeDesigns to use
(ii) G
EDI-INT rather than EDI-VAN as a method of EDI communication. 2

(e) BikeDesigns decides to use several social media tools provided by Customer
Relationship Management software. Justify the use of social media in this case. 2

[Turn over

[X216/13/01] Page twenty-one

 SECTION II Marks
Part B—Online Database Systems (continued)

11. Wemyss library has recently introduced self-service kiosks which provide a
number of member services: members can use the kiosks to search for particular
books, check books out of the library, return books, pay any fines that are due or
update their membership details.

(a) T
 he kiosks are connected to an SQL database which stores member and book
details. The new system requires existing members to provide additional
information. As a result, the underlying structure of the member table must
be edited.
Explain why server based database management tools would be an appropriate
choice for library staff who need to edit the structure of the member table. 2

(b) 
When members first use one of the kiosks, they are asked to provide the
additional information required using the HTML form indicated below:

Update Member Details

Email Address (optional): aperson@mymail123.com

Return Date Notification 1 day prior to due date

2 days prior to due date
1 week prior to due date

Personal Interests: Select Personal Interests

Personal Interests Animation

Animation Art
Database Development Baking
Crafts
Database Development

Update

[X216/13/01] Page twenty-two

 SECTION II Marks
Part B—Online Database Systems (continued)

11. (b) (continued)

 he structure of this HTML form includes a form element and several input
T
elements.
(i) Describe, in detail, the purpose of the form element. 2
(ii) 
One of the Return Date Notifications from the form shown on the
opposite page is displayed below.

2 days prior to due date

 rite the HTML code to generate this Return Date Notification option.
W
You should clearly indicate the contents of the type, name and value
attributes. 3

(c) An avatar is used to teach library members how to use

each of the services provided by the kiosks.
State the type of documentation exemplified by the avatar. 1

(d) As a way of providing information about the quality of a book, the kiosks allow
library members to rate any books that they have borrowed.
(i) 
Explain how this feature of Customer Relationship Management
enhances the experience of library members. 2
(ii) 
This functionality is achieved by connecting to the LoveBooks web
services and providing the book ISBN along with the name and home
town of the library members.
(A) Before submitting their first rating, members are asked to read and
accept the terms and conditions that apply.
Explain the significance of this. 2
(B) 
Once the member has added a new rating, the LoveBooks web
server inserts the new rating into its ratings table.
Describe two processes performed by the LoveBooks web server as
new ratings are added to the ratings table. 2

(e) The services provided by the new kiosks were introduced as part of a phased
conversion.
Explain why this is an appropriate method of introducing self-service kiosks to
a library. 2

[X216/13/01] Page twenty-three [Turn over

 SECTION II Marks
Part B—Online Database Systems (continued)

12. Gordon is testing a new app before it is released. The app uses his mobile phone’s
satellite navigation system to track his runs. The app stores the run data in a
database table.

(a) When an Internet connection is available, the app should attempt to copy
recorded data to a database server.
During testing, the error message shown is returned from the server, even
though the correct username and password have been entered.

Connection Error
Couldn’t connect to server. Please
try again.

OK

Give two reasons why the server may return this error. 2

(b) Using the web browser on his phone, Gordon can download summary data
about run history from the database server.
Write the HTML code needed to produce the download button shown below.

Download 2

[X216/13/01] Page twenty-four

 SECTION II Marks
Part B—Online Database Systems (continued)

12. (continued)

Server side scripting is used to extract the data from the database table called
(c) 
hillruns. The contents of the hillruns table are shown below.

trackid rundate trackname distancekm heightgainedm timemins comments

A34179 12/Nov/2014 Pentlands Run 10.45 350 85 Wet and windy

B32334 15/Nov/2014 Moorfoots 12.13 200 90 Quite nice run

G55493 18/Nov/2014 Pentlands Run 10.45 348 81 Misty

A92894 21/Nov/2014 Pentlands Run 10.44 349 83 Sunny and warm

(i) To show all runs with the trackname ‘Pentlands Run’, sorted into order
of timemins, with quickest run listed first, the SQL query below is
used.
Copy and complete this SQL query.

SELECT trackname, rundate, timemins FROM hillruns

WHERE trackname ='Pentlands Run'
;
1

(ii) 
Using a scripting language with which you are familiar, write the
server-side code which will execute this SQL query. 2

(d) The software company wishes to develop the application for a different phone
platform using commercial on-line database software.
(i) State one reason why a feasibility study should be carried out. 1
(ii) State two sources of support for the developers to help deal with
problems that arise during the development of the new version of the
program. 2
Open Source Software is an alternative to commercial Database
(iii) (A) 
Software. Describe its suitability in terms of cost effectiveness for
this development. 1
(B) State two criteria that could be used to evaluate the security of open
source software. 2

[Turn over

[X216/13/01] Page twenty-five

 SECTION II Marks
Part B—Online Database Systems (continued)

13. A table called tent is used to store details of the tents for sale in a camping shop.

(a) Contents of the tent table are shown below:

tent
tentid make description colour range retailprice carddiscountprice

0001 L Gear 2-Berth Red Pop Up £50.00 £24.99

0002 L Gear 2-Berth Grey Festival £55.00 £24.99

0003 VO 3-Berth Green Touring £220.00 £169.99

0004 L Gear 2-Berth Orange Pop Up £50.00 £24.99

0005 VB 3-Berth Red Backpacking £140.00 £119.99

0006 VT 3-Berth Green Touring £230.00 £199.99

0007 VO 3-Berth Red Family £180.00 £169.99

0008 HG Orange Beach £50.00 £24.99

0009 FT 4-Berth Green Family £150.00 £89.99

0010 VO 5-Berth Blue Family £220.00 £169.99

0011 ab 2-Berth £0.00 £0.00

(i) The following SQL query is executed.

SELECT tent.description, tent.retailprice

FROM tent
WHERE tent.description = '3-Berth';

Describe the output produced when this SQL query is applied to the
Tent table. 2
(ii) A
 second SQL query is executed. The output from the query is shown
below.

tentid colour retailprice carddiscountprice

0008 Orange £50.00 £24.99

 rite the SQL statement using the field description which would
W
produce this output. 2

[X216/13/01] Page twenty-six

 SECTION II Marks
Part B—Online Database Systems (continued)

13. (continued)

(b) The database is developed and an additional table called comment is added.
Contents of the comment table are shown below.

comment

userid tentid commentdate time comment rating

1 1 21/04/2013 11:00:00 Wonderful tent 5
1 4 12/12/2013 14:00:00 A good all round tent 5

(i) Complete the SELECT statement used to find the average rating for each
tent.

SELECT comment.tentid,
FROM comment
;
2

(ii) A query is needed to display all tents which have had no comment.
Copy and complete the SQL command needed for this purpose.

SELECT *
FROM tent
WHERE
(SELECT * FROM comment WHERE tent.tentid = comment.tentid);
1

(c) The following tent has been discontinued and its record is to be removed from
the database.

Tent

Tent ID Make Description Colour Range Retail Price Card Discount Price

5 VB 3-Berth Red Backpacking £140.00 £119.99

Write an SQL query which would remove this record. 2

[END OF SECTION II — PART B]

[END OF QUESTION PAPER]

[X216/13/01] Page twenty-seven

 [BLANK PAGE]

DO NOT WRITE ON THIS PAGE