Part B: (70 marks) 1. ‘Vsmart’ keeps track of their customers and sales information. There are five relations/tables in their database describing customer details, description of items on sale, details of sales, namely, CUSTOMER, ITEMS and SALES. Relational schemas for these relations are given below. CUSTOMER (CID:auto increment, Name: string, Street: string, Suburb: string, State:string Post code:integer,DiscountPercent:number, Email:string) ITEMS(PID: auto increment,Code:string, Category: string, Description: string, Price: currency,No_In_Stock: number) SALES(SID: auto increment, CID:number, Order_Number:number,Order_Date:date, Full_Price: Discount:number, Final_Price:currency, Total_Paid:currency,Status: string) a. Implement above database on MS Access.Name the database as ‘Vsmart’. b. Enter data to the database you created in 1(a). Here you have to enter data into all 3 tables, CUSTOMER, ITEMS and SALES usingthe sample datagiven in the following tables. (10Marks) Vsmart database CUSTOMER CID Name Street Suburb State Post code DiscountPercent Email 1 My Kitchens Townsville 2 Hume Hway Townsville QLD 4702 10 [email protected] 2 My Kitchens Cairns 20 Russ Road Cairns QLD 4870 10 [email protected] 3 Linda Smith 3 Sunset Street Toowoomba QLD 4740 0 [email protected] 4 Tim Terry 4 Lake Road Bundaberg QLD 4670 0 [email protected] 5 Chow Lin 7 The Trongate Alison NSW 2259 0 [email protected] 6 Tom Jones 6 Bay st Southbank VIC 3006 0 [email protected] 7 Ronda Williams 49 Eliza Avenue Clayton VIC 3168 5 [email protected] ITEMS PID Code Category Description Price No_In_Stock 1 BY00001 Kitchenware 2L Saucepan $63.00 4 2 BY00002 Kitchenware 3L Saucepan $73.00 0 3 BY00003 Kitchenware 4L Saucepan $93.00 5 4 RA00001 Glassware Glass Jug $120.00 0 5 RA00002 Glassware Glass Goblet $75.00 6 6 QI00001 Glassware Qi Flutes (set of 6) $50.00 8 7 QI00002 Glassware Qi Wine Glasses (set of 6) $60.00 9 8 QI00003 Glassware Qi Glass Pitcher $39.00 3 9 QI00004 Kitchenware 5L Saucepan $50.00 0 SALES SID CID Order_Number Order_Date Full_Price Discount Final_Price Total_Paid Status 1 1 10000001 2015-12-23 $1,374.00 $137.40 $1,236.60 $1,236.60 Placed 2 2 10000002 2016-01-10 $220.00 $22.00 $198.00 $198.00 Placed 3 1 10000003 2016-01-13 $1,260.00 $126.00 $1,134.00 $1,134.00 Placed 4 3 10000004 2015-11-17 $48.00 $0.00 $48.00 $48.00 Placed 5 4 10000005 2015-11-17 $0.00 $0.00 $0.00 $0.00 Open ITEMS_SOLD SOLD_ID SOID PID Item_Price No_Items_Requested Extended_Price No_Items_Dispatched 1 1 1 $63.00 6 $378.00 6 2 1 2 $73.00 6 $438.00 2 3 1 3 $93.00 6 $558.00 6 4 2 6 $50.00 40 $2000.00 25 5 3 5 $75.00 12 $900.00 12 6 3 4 $120.00 3 $360.00 1 7 4 5 $12.00 4 $48.00 4 8 5 1 $63.00 1 $63.00 0 c. Write SQLqueriesfor the following questions and execute the queries on the Vsmart database you created in 1(a) on MS Access. (60 marks) Note: Your Queries should produce (exactly) the same result as shown under each question including formats/headings etc. Write only the Query you used to obtain the given results. 1. List all the categories of different itemslisted in the ITEMs table. (5 marks) 2. a. List code, description and price of kitchenware items with price less than $80 and no stock in hand. (5 marks) Expected result using sample data b. Rename the three columns in the output as “Item_code”, “Item_description” and “Item_Price”. (5 marks) Expected result using sample data 3. Display Order number, order date and status of all orders placed in year 2015. (5 marks) Expected result using sample data 4. Customer name, Suburb and Oder date of all orders placed by customers from Queensland and their total_paid amount is greater than or equal to $1000. (10 marks ) Expected result using sample data 5. List customer number and total payments made by each customer. Hint: You have to rename the two columns in the output as “Customer_ID” and “Total_payments”. (10 marks) Expected result using sample data 6. List customer ID, names of customers, who placed one or more orders. (10 marks) Expected result using sample data 7. For each order, list order number, description of the items they ordered, number of items requested, number of item dispatched. (10 marks) Hint: Join 3 tables SALES, ITEMS_SOLD and ITEMS tables Expected result using sample data ------------------------------------//-------------------------------------------------