BUS5DWR Data Wrangling and R

Assignment Requirements
Part 1: Database design
Investigate the following table recording the information of room bookings for an apartment and answer the following questions.

Question 1 [6 marks]
Will this table satisfy all the first three normal forms, i.e. 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.
Answer:

Question 2 [6 mark]
Using https://erdplus.com, let's redesign it into two or more tables in the third normal form. You are asked to:
⦁ Present your entity-relationship diagram (ERD).
⦁ Present the generated relational schema. The primary key and possible foreign keys of each table should be shown.
⦁ Present the generated SQL statements to create the tables with the appropriate data type set for each attribute.
Answer:
a)
b)
c)
Part 2: Database retrieval using SQL
We will use the data recorded in the VendorPurchase schema within the lmban-teaching.database.windows.net.
Start by exploring the contents of the tables and understanding the meaning of the attributes of each table.

Question 3 [2 mark]
State the relationships between the tables to show the constraints imposed in the database.
Answer:

SQL Questions [76 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 retrieve the information. Fill in the table with the result table and code.

Question Returned Table (Screenshot and number of rows returned) Code (plain text) Mark
⦁ What are the products supplied by the Victory Bikes vendor?
Display the product name, standard price, and last receipt cost. The result must be in ascending order of the product names. 5
⦁ What is the total quantity of shell and rim products ordered in Match 2014? 6
⦁ Display names of distinct vendors who supply Lock Washer products with the last receipt date in August 2011. 6
⦁ Find vendors for that we have placed more than 30 orders per year. Display their ID, names, year, and the number of orders. 6
⦁ How many vendors have we never placed any orders? 5
⦁ Find the top five vendors with the highest amount of money we paid in 2013. Display their vendor ID, name, and the total amount we paid. 6
⦁ Display names of vendors that supply both Chain and Nut products. 7
⦁ Find employees who have ordered vendor 'Bike Satellite Inc.' but never ordered vendor 'Knopfler Cycles'. Display their first and last names in a single column. 7
⦁ Find products that have been ordered from more than two different vendors. Display their ID, names, and number of vendors. 6
⦁ For each Chainring product, display all the vendors in descending order of their supplying standard price for the product. 6
⦁ For each vendor that has the PurchasingWebServiceURL, display the products we have ordered in descending order of the total money that we have paid for the products to that vendor. The results should have 3 columns (vendor name, product name, and the total money paid). 8
⦁ For each Paint product, display the vendor that supplies it with the lowest standard price. The results should have 4 columns (product ID, product name, vendor name, and the lowest standard price). 8