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.

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)

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.

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)

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)

Question 3.

(2 marks)

The table below contains details of employees including their names and their email address(es) and phone number(s).

There are problems with the table above. Identify and explain how you would resolve the issues? (100 words maximum)

Question 4.

(3 marks)

The Entity Relationship Diagram below shows the design of a database.

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)

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.

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)

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

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

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)

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)

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)