联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981

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