Semester One Examinations, 2022 BISM1201
Page 1 of 20


This exam paper must not be removed from the venue

Venue ____________________
Seat Number __________
Student Number |__|__|__|__|__|__|__|__|
Family Name ____________________
First Name ____________________


School of Business
Semester One Examinations, 2022
BISM1201 Transforming Business with Information Systems
This paper is for St Lucia Campus students.

Examination Duration: 120 minutes

Planning Time: 10 minutes

Exam Conditions:

•This is a Closed Book examination - no written materials permitted
•Casio FX82 series or UQ approved or labelled calculator only
•During Planning Time - Students are encouraged to review and plan
responses to the exam questions
•This examination paper will be released to the Library

Materials Permitted in the Exam Venue:
(No electronic aids are permitted e.g. laptops, phones)

None

Materials to be supplied to Students:

2 sheets Rough Paper

Additional exam materials (e.g. answer booklets, rough paper) will
be provided upon request.


Instructions to Students:
If you believe there is missing or incorrect information impacting
your ability to answer any question, please state this when writing
your answer.

Students are to submit their answers on this exam paper, submitting the
exam and any rough paper used.


For Examiner Use Only

Question

Mark

Total _________


Semester One Examinations, 2022 BISM1201
Page 2 of 20
Question 1.
(6 marks)
The table in the image below includes details of a small business that you run that produces
toys. You have been recording your sales and customer details in a flat file and would like to
change this to a relational database.
The table shows customer details and their order details. It also shows a customer’s loyalty
level (best customers are “gold”, followed by “silver” and then “bronze”), whereby a customer
can only have one loyalty level, but a loyalty level can have many customers. Additionally, a
customer can make many orders and an order can have many products.
Cust
id
Cust
name
Loyalty
id
Loyalty
level
Discount Order
id
Order
date
Product
id
Product
name
Product
price
Quan
tity
Total
price
1 James
Hold
GO Gold 15% 001 6/04/22 AB Toy AB $5.00 1 $15.00
1 James
Hold
GO Gold 15% 001 6/04/22 CD Toy CD $10.00 1 $15.00
2 Mao
Woo
SI Silver 10% 002 6/04/22 AB Toy AB $5.00 2 $10.00
3 Julia
Solo
GO Gold 15% 003 6/04/22 AB Toy AB $5.00 3 $37.00
3 Julia
Solo
GO Gold 15% 003 6/04/22 CD Toy CD $10.00 2 $37.00
3 Julia
Solo
GO Gold 15% 003 6/04/22 EF Toy EF $2.00 1 $37.00
4 Frank
Con
SI Silver 10% 004 7/04/22 CD Toy CD $10.00 2 $20.00
5 Son
Ming
BR Bronze 5% 005 7/04/22 AB Toy AB $5.00 2 $30.00
5 Son
Ming
BR Bronze 5% 005 7/04/22 CD Toy CD $10.00 2 $30.00
6 Harry
Beat
GO Gold 15% 006 7/04/22 EF Toy EF $2.00 10 $20.00
7 Ami
West
SI Silver 10% 007 8//04/22 EF Toy EF $2.00 1 $2.00
2 Mao
Woo
SI Silver 10% 008 8//04/22 AB Toy AB $5.00 4 $20.00
5 Son
Ming
BR Bronze 5% 009 8//04/22 EF Toy EF $2.00 10 $20.00


Semester One Examinations, 2022 BISM1201
Page 3 of 20
Based on the table above, create a relational database structure in third normal form.
Ensure you identify the entities, attributes, and primary and foreign keys. (150 words
maximum)
Write your answer in this box following this format:
Table name (entity): attribute name (primary key), attribute name (foreign key), attribute name,
attribute name, etc.,

Semester One Examinations, 2022 BISM1201
Page 4 of 20
Question 2.
The table below shows data for movie clubs that you manage. You record the movie club
that each member belongs to. You want to update your records by using a relational
database.
Member details
Member_ID First_name Last_name Address
001 Frank Dee 12 Kers Av
002 Chris Ronald 4 Leeds St
003 James Holden 12 Rocin St
004 Mat Flint 100 Gow St
005 Jesse Ling 16 Flower St
006 Kyle Walker 24 Blue St

Question 2A.
(2 marks)
Consider that a member can only belong to one movie club and a movie club can have
many members. What are the most appropriate foreign key(s) and where should the foreign
key(s) be placed within the table structure? (50 words maximum)
Write your answer in this box

Question 2B.
(2 marks)
You are thinking about allowing members to belong to many movie clubs instead of just one,
so any member can join many clubs, and each club can have many members. Describe how
this alternative design would be different from the design in question 2A. What changes
would you need to make to the above two tables? (Consider any new tables that may be
needed) (50 words maximum)
Write your answer in this box

Club
Club_ID Club_name
S1 SciFi
A1 Adventure
H1 Horror
C1 Comedy
D1 Drama
R1 Romance
Semester One Examinations, 2022 BISM1201
Page 5 of 20
Question 3.
(2 marks)
The table below contains details of employees including their names and their email
address(es) and phone number(s).
Employee_id
(PK)
First_name Last_name Email1 Email2 Phone1 Phone2
002 Marcos Pa m.pa@gmail.com macros@yahoo.com 077988922 077897262
003 Michio Inaros
004 Lucy Xi lucy.xi@hotmail.com 097653222
005 Phillip Chao chao@outlook.com chao@gmail.com
006 Billy Cavani 098763122 097654212

There are problems with the table above. Identify and explain how you would resolve the
issues? (100 words maximum)
Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 6 of 20
Question 4.
(3 marks)
The Entity Relationship Diagram below shows the design of a database.
Customer Invoice Invoice_Line
Vendor Product
Sales Agent

Answer the following questions:
4A. An Invoice can have a minimum of how many Customers? (0.5 marks)
4B. A Vendor can have a maximum of how many Products? (0.5 marks)
4C. Which entity (or entities) is/are a Linking Entity? (1 mark)
4D. Where is a linking entity needed (but currently missing)? (1 mark)
Write your answer in this box
A.
B.
C.
D.

Semester One Examinations, 2022 BISM1201
Page 7 of 20
Question 5.
(2 marks)
A business analyst attempted to draw an Entity Relationship diagram for a community sports
club based on the business rules below. However, there is an error.
Business rules are:
1. A Coach can coach zero, one, or many Teams.
2. A Team can only be coached by one Coach.
3. A Team Manager can manage zero, one, or many Teams.
4. A Team can only be managed by one Team Manager.
5. A Team can have one or many Players.
6. A Player must have one and only one Team.
7. A Team can have zero, one, or many Sponsors.
8. A Sponsor can have zero, one, or many Teams.

Coach Team
Sponsor
Sponsorship
agreement
Players
Manager

Based on the business rules listed, identify which rule is incorrectly modelled in the Entity
Relationship Diagram and explain how it should be correctly modelled. (50 words maximum)
Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 8 of 20
Question 6.
(1 mark)
If you were collecting and storing information about your music collection, “Albums” would be
considered a(n) _______
A. Cell
B. Entity
C. Instance
D. Attribute
Write your answer in this box

Question 7.
(1 mark)
What term is used to refer to information stored about a “specific album”?
A. RDMS
B. Record or instance
C. Table
D. Column
Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 9 of 20
Question 8.
(9 marks)
BISMshoes makes and sells running shoes online and in its stores around Australia. Joe
started working at BISMshoes as a business analyst. Joe has found that the business has
typically used its operational relational databases to answer queries and perform basic
analysis. Joe is interested in using data from the business’s legacy systems as well as from
external data, Internet of Things, and social media to help inform decision making.
This is important because the business finds that more and more it needs business
intelligence to help with its decision making and to understand customer behaviour. There is
currently no way to bring the data from all these sources together in a meaningful way.
Question 8A.
(3 marks)
Joe thinks that it is necessary for BISMshoes to consider using a Data Warehouse. Give two
reasons why you would recommend that BISMshoes use a Data Warehouse. (100 words
maximum)
Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 10 of 20
Question 8B.
(3 marks)
After setting up the business intelligence infrastructure, Joe wants some insights into shoe
sales across Australia. In particular, he would like to know how well four types of shoes
(road, trial, track, and mountain) are selling in each state in Australia (the states being VIC,
NSW, QLD, WA, SA, and TAS), along with projected sales for each financial quarter.
Which business intelligence technique would you use to give us this result? Choose
between OLAP cubes or Data Mining/Associations. Explain your choice in the box below:
(100 words maximum)
Write your answer in this box
Choose from the options below
A. OLAP cubes
B. Data Mining/Associations
Explanation
Write your explanation here


Semester One Examinations, 2022 BISM1201
Page 11 of 20
Question 8C.
(3 marks)
Joe is really pleased with the business intelligence infrastructure and is now considering
expanding his analysis. He is now interested in analysing the purchase history and purchase
behaviour of customers. In particular, Joe wants to know if customers purchase two items
together (e.g., running shoes and running socks). With this information, Joe can decide to
keep those items in adjacent locations or offer deals online. This will make sure that
customers who want to purchase those items would not miss them, which in turn may
increase sales.

What type of data mining would you suggest is most appropriate? Choose from:
associations, sequences, classifications, clustering, and forecasting. Explain your choice in
the box below: (100 words maximum)
Write your answer in this box
Choose from the options below
A. Associations
B. Sequences
C. Classifications
D. Clustering
E. Forecasting
Explanation
Write your explanation here

Semester One Examinations, 2022 BISM1201
Page 12 of 20
Question 9.
(2 marks)

Match the terms below with the correct statement.

TERMS STATEMENT
1. Hadoop A. Is useful for finding hidden patterns like
associations and sequences
2. Data Warehouse B. Designed to analyse data derived from
transactional sources (e.g., relational
databases) as well as other sources for
business intelligence and therefore helpful
for strategic decisions that affect the entire
enterprise.
3. Data Mart C. Is a very specific portion of the organisation’s
data (e.g., sales data) used by a single
subject or functional organisation area (e.g.
sales) and therefore helpful for tactical
decisions pertaining to limited parts of the
business.
4. Data Mining D. An open-source software framework for
storing data and running applications on
clusters of off-the-shelf hardware.


Write your answer in this box and circle your chosen match

1. Hadoop matches with (A, B, C or D)
2. Data Warehouse matches with (A, B, C or D)
3. Data Mart matches with (A, B, C or D)
4. Data Mining matches with (A, B, C or D)

Semester One Examinations, 2022 BISM1201
Page 13 of 20
Question 10.

(3 marks)

In the context of Machine Learning, what is meant by the term “supervised learning” and why
do you think most machine learning is supervised? Explain your answer with an example on
when supervised machine learning would be used. (200 words maximum)

Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 14 of 20
Question 11.
(2 marks)

Match the terms below with the correct statement.

TERMS STATEMENT
1. Unstructured data A. Includes a ‘input layer’, ‘hidden layer’ and
‘output layer’. It can be can either be
supervised or unsupervised.

2. Neural networks B. A set of rules are specified which are
followed by the system. They capture the
expertise of a human in a limited domain of
knowledge.
3. Expert systems C. Data that fits neatly within fixed fields and
columns in relational databases and
spreadsheets.
4. Structured data D. Data that doesn’t fits neatly within fixed fields
and columns (e.g., social media data).

Write your answer in this box and circle your chosen match

1. Unstructured data matches with (A, B, C or D)
2. Neural networks matches with (A, B, C or D)
3. Expert systems matches with (A, B, C or D)
4. Structured data matches with (A, B, C or D)


Semester One Examinations, 2022 BISM1201
Page 15 of 20
Question 12.
(5 marks)

You are preparing a worksheet to calculate staff sales commissions on vehicles they have
sold for a car and truck sales company. Each staff member's Position code (column C) starts
with the first letter of the city where the dealership is (column B), followed by their job
descriptor "SA" (sales agent), then a single digit number representing the staff member’s
seniority level (1 is the most junior and 5 is the most senior).This is then followed by another
code, a T followed by a number, which is a code relevant to HR staff, but not us.

For example:

ASA3T13 can be expressed as Adelaide/Sales Agent/ Level 3/T13 (the HR code)

The Product Code (column D) is the type of vehicle which has been sold. Product codes
from 1 to 10 represent the sale of a car. Anything 11 or above is a truck.

Use the functions LEFT, RIGHT, VLOOKUP, IF, and & to obtain the correct commission (as
displayed in Column E) for the correct sales agent level, from the correct table i.e., the
“Commission rates (cars)” or the “Commission rates (trucks)” tables. Note that the
commission rates differ for cars and trucks, so two tables of the different rates are present.
Control any errors from VLOOKUP using IFERROR to display the message "Error" instead.

Semester One Examinations, 2022 BISM1201
Page 16 of 20
Write your answer in this box

Question 13.
(1 mark)

We want to use VLOOKUP to allocate grades to students (as shown in the image below).
The result of the following formula entered into cell F2 is “#N/A”:

=VLOOKUP(E2,I2:J8,2,FALSE)

What is the correct change to the formula to show the grade? (i.e., 5)
A. Rearrange the VLOOKUP table so that every possible mark is listed
B. Change the column index number i.e., =VLOOKUP(E2,I2:J8,3,FALSE)
C. Change the range lookup to TRUE i.e., =VLOOKUP(E2,I2:J8,2,TRUE)
D. None of these answers
Write your answer in this box

Semester One Examinations, 2022 BISM1201
Page 17 of 20
Question 14.
(3 marks)

Stan enjoys trading cryptocurrencies and is considering selling his “BISMcoin” holdings. He
has 100,000 BISMcoins that are currently valued at $0.1442 per coin. He would like to know
what the price must be in order to sell all of them for a total of $20,000.

He wishes to use Goal Seek to do this.

Configure Goal Seek to accomplish this. Enter the Goal Seek parameters for the following in
the box below:
Write your answer in this box
A. Set Cell =
B. To Value =
C. By Changing cell =


Semester One Examinations, 2022 BISM1201
Page 18 of 20
Question 15.
(6 marks)

Consider the following scenario:
You have purchased an investment property and wish to renovate it before reselling it for a
profit. You have built a table of renovation options, with data about their costs, expected
returns (from advice of your real estate agent), and how much extra value each option
should add to the house.

Configure Solver to choose the combination of options to ensure you increase the property's
value as much as possible, without exceeding your available budget.

Ensure the "Selected" column contains a 1 for any option chosen, and 0 if the option is not
chosen.
Note: The SUMPRODUCT function returns the sum of the products of corresponding
ranges. For example, if Solver selects the “replace oven” option, it will enter a 1 into the
“selected” column on row 2. SUMPRODUCT will multiply $2500 by 1, and then do the same
for all other options selected, effectively adding them all together.


Semester One Examinations, 2022 BISM1201
Page 19 of 20


An image for the Solver Parameters is shown above with the necessary four parts for
completion (A, B, C, D).

For your answer, you must write the details of what you would enter into these four parts of
the Solver window. You must ensure that you make clear what data goes into what part of
the Solver Parameter window (for example “Set Objective is $B$4” or “Subject to Constraints
is $A$6 >=5” etc.).

Semester One Examinations, 2022 BISM1201
Page 20 of 20
Write your answer in this box

A. Set Objective =
B. To =
C. By Changing Variable Cell =
D. Subject to the Constraints =
END OF EXAMINATION