BUS5DWR Data Wrangling and R Assignment

Overview

Over the past few weeks, you have gained a general understanding of designing databases and how to use SQL to retrieve information from business data. This assignment allows you to demonstrate what you have Learned.

Assignment Requirements

Part 1: Database design

Investigate the following table recording of students and courses at a university and answer the following questions.

Question 1 [12 marks]

Will this table satisfy 1NF, 2NF, and 3NF? Explain why. Give examples for insertion anomaly, deletion anomaly and update anomaly that may happen when using this table to record data.

Question 2 [14 mark]

Using https://erdplus.com, let's redesign it into two or more tables in the third normal form. You are asked to:

1. (8 marks) Present your entity-relationship diagram (ERD).
2. (3 marks) Present the generated relational schema. The primary key and possible foreign keys. of each table should be shown.
3. (3 marks) Present the generated SQL statements to create the tables with the appropriate data type set for each attribute.

Part 2: Database retrieval using SQL

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

Start by exploring the contents of the tables to understand the meaning of the attributes of each table.

Question 3 [4 mark]

State the relationships between the tables to show the constraints imposed in the database.

Question 4 [30 marks]:

The manager wants a better insight into the company business to support his future decisions. He requests the IT department to provide answers to the following questions. You are asked to write SQL code to find the answer for each question.

Fill in below the code in text and screenshot the result table highlighting the columns (limit to the first 8 rows and must show the number of fetched rows and time). Result of one question must be shown in only one return table.

Example (No marks)

Question 4.1 How many vendors did the company place orders with that had a credit rating greater than 2? (2 marks)

Question 4.2 Display names of vendors and their preferred status of vendors who live in Washington province. (2 marks)

Question 4.3 How many distinct orders made in 2012 that have been either shipped by “Truck” or “Cargo Ship”? Display also the total quantity. (3 marks)

Question 4.4 Display vendors name, state and its latest receipt cost in descending order of both standard price and average lead times. (3 marks)

Question 4.5 Calculate the quarter sales of vendors in Washington province. Results must have five columns that named as vendor name, year, quarter, total quantity, and total sales, and displayed in time order. (3 marks)

Question 4.6 Who are the two most used vendors based on quantity of orders? (3 marks)

Question 4.7 The company wants to choose the best vendor for each shipping type to process orders faster that can improve the company sales and reputation. To help the manager suggest these vendors, you are required to propose a criterion/criteria with justification, and write code to find the answer. Your answer must not be an empty table. (4 marks)

Question 4.8 Based on the answer of Question 4.7, display the vendor that have sold the highest orders and lowest lead time that is the best vendor. (3 marks)

Question 4.9 The company wants to know the most popular efficient and price effective vendor. You are required to propose a criterion/criteria to define, and write code to find the answer. Your answer must not be an empty table. (4 marks)

Question 4.10 Based on the answer of Question 4.9, what is the average order fulfillment cost and time? (3 marks)

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank