Module Code: BUS2DAC

Module Subject: Data Analytics Concepts

Assignment 2: Data retrieval using SQL

Overview

Over the past few weeks, you have gained a general understanding of business databases and how to use SQL to query and manipulate business data. This assignment will provide you with an opportunity to demonstrate what you have learned through a business database.

We will make use of the data contained in BUS2DAC.OrderSupplier schema within the lmban-teaching.database.windows.net server. Please refer to Week 6 Tutorial for instructions on how to access the server via DBeaver if you have not accessed it already.

Specific Requirements

Question 1 (2 marks)

• 1.1 (1 mark) For each table, describe the primary keys and foreign keys if exist.

• 1.2 (1 mark) Describe the four relationships between the five tables.

• • Customers and Orders

One order is sent by one and only one customer. One customer can send multiple orders.

• • Orders and Order Item

One order may have multiple order items. One order items belongs to one and only one order.

• • Products and OrderItem

One product may appear in multiple order items. One order item belongs to one and only one product.

• • Products and Suppliers

One supplier may order multiple products. One product may be ordered by multiple suppliers.

Question 2 (8 marks) Run the following code and state (in natural language that a general audience can understand) the question that the code responses. (Note that these statements will work in SQL Server and may not in other SQL-dialects).

• 2.1 (2 marks)

• 2.2 (2 marks)

• 2.3 (2 marks)

• 2.4 (2 marks)

Question 3: (20 marks) Write SQL queries to RETURN A SINGLE TABLE containing the information required to answer each question.

• 3.1 (2 marks)

• 3.2 (2 marks)

• 3.3 (2 marks)

• 3.4 (2 marks)

• 3.5 (2 marks)

• 3.6 (2 marks)

• 3.7 (2 marks)

• 3.8 (2 marks)

• 3.9 (4 marks)

Marking rubrics

The marker in assessing your work will use the following marking guide.

Question 1:

Full marks for the correct answer. Half of the mark for something close. The relationship should be described in natural language and easy to understand for a general audience who do not know about databases and SQL.

Question 2:

Full marks for each correct description in natural language and easy to understand for a general audience who do not know about databases and SQL. Half of the mark for something close.

Question 3:

Full marks for each faultless SQL statement (i.e., run without errors and return a correct output). Half of the mark for something close. Reasonable justification is clearly described for each proposed criterion in Question 3.9.

“You can order BUS2DAC – Data Analytics Concepts Assignment from our service and can get a completely high-quality custom paper. Our service offers any of the BUS2DAC Academic paper written from scratch, according to all customers’ requirements, expectations and highest standards.”