MBA510 Data Relationship Modelling & Analysis Assignment
Assessment 1 Information
Subject Code: MBA510
Subject Name: Data Relationship Modelling & Analysis
Assessment Title: Skills Building
Assessment Type: Practical Coding
Activity Length 100 Minutes (+/-10%)
Weighting: 15%
Total Marks: 15
Submission: In Class (Upload SQL file to LMS and Summary Report via Turnitin)
Due Date: Week 5
Your Task
You are required to:
Complete the SQL queries to find business insights.
Prepare a short report containing succinct answers to the questions in Sections #1 and #2.
Background
ACME Traders is a Small and Medium-sized Enterprise (SME) that imports and exports
specialty foods from around the world. ACME has engaged your Analytics Consultancy to
help them discover insights and provide advice crucial to their plans for supply chain
optimisation and business expansion. As a business analyst, you are to analyse data
stored in ACME’s database. The dataset consists of the following:
- Customers: sole traders or businesses that purchase products from ACME.
- Products: Product information.
- Orders and Order Details: records of Sales Orders between ACME and their Customers.
- Shippers: details of shippers who ship products from ACME to the Customers.
- Employees: ACME Traders employee information.
Assessment Instructions
Section #1: Database Connection & Data Exploration
Part 1: Distributed SQL Database Connection (1 mark)
- Given the connection details, e.g., username, hostname and password, create a connection to
the database from a database client e.g., DBeaver.
Part 2: Identify Tables in Database (1 marks)
- Using the database navigator, note down the tables stored in this database.
2. Draw a simple diagram of the ACME database schema
Part 3: SQL Select Data (1 marks)
For each of the following tables,
Customers
Orders
Run an SQL SELECT statement, selecting all COLUMNS (also called attributes or features) of data
but LIMIT the results of the queries to 50 rows.
Section #2: Develop Business Insights
Part 4: Customer Insights (3 marks)
Part 4 are SQL statements that queries customers, products, and product order details to find
possible patterns in customer purchase behaviours.
Execute the SQL queries, interpret the results of the queries, and answer the following questions:
Who are the top customers in terms of the total dollar value of sales orders?
What were the top 5 product purchases?
Part 5: The Supply Chain (3 marks)
Part 5 are SQL statements that queries shipment and product supplier details to find insights into
the supply chain.
Execute the SQL queries, interpret the results of the queries, and answer the following questions:
Who are the top Suppliers in terms of total value of products?
Who are the top Shippers in terms of the number of orders?
Part 6: Geographical Insights (3 marks)
Part 6 are SQL statements that queries customers, products, and region details to find possible
patterns in the geographical dispersion of customers.
Execute the SQL queries, interpret the results of the queries, and answer the following questions:
Which countries are the top exporters in terms of the total value of goods?
In terms of the total value of goods, what are the top 5 regions for imports?
Part 7: Employee Performances (3 marks)
Part 7 are SQL statements that queries employees, product sales and customer order details to find
possible patterns between employees and product sales.
Execute the SQL queries, interpret the results of the queries, and answer the following questions:
With respect to the total values of the products sold, who are the top performing employees?
What are the top sales items sold by the top performing employees?
Important Study Information
Academic Integrity Policy
KBS values academic integrity. All students must understand the meaning and consequences of
cheating, plagiarism and other academic offences under the Academic Integrity and Conduct Policy.
What is academic integrity and misconduct?
What are the penalties for academic misconduct?
What are the late penalties?
How can I appeal my grade?
Click here for answers to these questions:
http://www.kbs.edu.au/current-students/student-policies/.
Word Limits for Written Assessments
Submissions that exceed the word limit by more than 10% will cease to be marked from the point at
which that limit is exceeded.
Study Assistance
Students may seek study assistance from their local Academic Learning Advisor or refer to the
resources on the MyKBS Academic Success Centre page. Click here for this information.