Assessment details for ALL students Assessment item 1—Assignment 1 Due date: Friday of Week 5 (Midnight AEST time) ASSESSMENT Weighting: 30% 1 Length: Typically 20-30 hours Objectives This assessment item relates to course learning outcomes 1 and 2 in the course profile. Introduction This assignment consists of the following parts: • Part A – SQL queries (80 marks) • Part B – Form development activity (10 marks) • Part C – Report development activity (10 marks) Students are expected to complete all parts. Important notes: • You must use the Microsoft Access database provided for this assignment. The required database can be downloaded from "Assignment-1 & 2 Specifications" section on MOODLE course website. • Regular access to a system running Microsoft Access is required for this assignment. You cannot obtain a pass in this course without regular access to appropriate software. • This assignment is an individual piece of work. Discussing the assignment with other students is encouraged; however, the document you submit for this assignment must be an individual piece of work, produced by you. • This assignment due date is Friday, Week 5. The sample solution will be released on Friday, Week 7. A penalty of 5% of total marks "Per Calendar Day" will apply to late submissions. Submissions made after release of the sample solution will receive ZERO marks. • A late penalty can be avoided by applying for an extension before the due date through MOODLE course website. • An electronic submission is required for this assignment. After making your submission, download your submitted file to check it has not been corrupted. Before submission, check you have met all criteria in the marking sheet. • This assignment is marked out of 100 marks but will be scaled to a 30% weight. Part A – SQL queries (80 marks) Using the database provided, write SQL queries that satisfy the following information requests. • Your queries should produce the expected result (including column names, grouping, duplicate removal, and ordering) shown in each information request below. • Your queries must order the result as requested. If the information request does not mention a required ordering, your query does not need to order the result. • Where appropriate, your queries must remove duplicates from the result. • Simple queries are preferred. Marks may be lost for unnecessarily complex queries. 1. List the description alphabetically of all Glassware products. (5 marks) Expected result using sample data: PID Code Category Description Price NbrItemsInStock 5 RAJA00002 Glassware Raja Glass Goblet $75.00 6 4 RAJA00001 Glassware Raja Glass Decanter $120.00 0 7 QIAN00002 Glassware Qi Wine Glasses (set of 6) $60.00 9 8 QIAN00003 Glassware Qi Glass Pitcher $39.00 3 6 QIAN00001 Glassware Qi Flutes (set of 6) $50.00 8 2. List Customer name and address (Street, Suburb, State and Postcode) for all Victoria customers, as shown below. List results alphabetically by customer name. A single column result is required. (5 marks) Expected result using sample data: Customers in Victoria Alan Jones (7 William Street Melbourne VIC3000) Bruce Jones (6 Bay Way Elwood VIC 3184) Richard Johnson (9 Lonsdale avenue Melbourne VIC 3000) Hint: You need to merge columns – refer to Help Menu for examples. 3. List customers from the state of QLD or NSW who have not provided an email address (i.e. the email address is null). (5 marks) Expected result using sample data: CID Name Email 11 Beth Anderson 4. List products that were never ordered by customers, order results by Product ID. (5 marks) Expected result using sample data: PID SID Code Category Description Price NbrItemsInStock 8 3 QIAN00003 Glassware Qi Glass Pitcher $39.00 3 9 1 BLLY0004 Cookware Billy 5L Saucepan $99.00 5 5. List any sales order with ordered products in stock but not dispatched yet. Formulate two queries to answer this request: (a) One that uses IN with a subquery. (6 marks) (b) One that uses some sort of join. (5 marks) Note: Your two queries should always produce the same result. Expected result using sample data: PID Description 1 Billy 2L Saucepan 6. List sales orders where 2 or more items of the same product have been requested on that order. Formulate two queries to answer this request: (c) One that uses IN with a subquery. (6 marks) (d) One that uses some sort of join. (5 marks) Note: Your two queries should always produce the same result. Expected result using sample data: SOID Date Total Paid 1 2010-12-23 $1,236.60 2 2011-01-10 $198.00 3 2011-01-13 $1,134.00 4 2011-01-17 $48.00 7. List number of products in each category. (5 marks) Expected result using sample data: category Number of items Cookware 4 Glassware 5 8. List products that were ordered more than once and number of times ordered as shown below. (6 marks) Expected result using sample data: PID Category Description Number of times ordered 1 Cookware Billy 2L Saucepan 2 5 Glassware Raja Glass Goblet 2 9. List sales orders with more than 3 outstanding items (items requested that have not yet been dispatched). (6 marks) Expected result using sample data: SOID TotalItemsRequested NbrItemsOutstanding 1 18 4 10. List all cookware products with one or more "Placed" sales order. (6 marks) Expected result using sample data: Code Description Nbr Placed Orders BLLY00001 Billy 2L Saucepan 1 BLLY00002 Billy 3L Saucepan 1 BLLY00003 Billy 4L Saucepan 1 11. List, alphabetically, the number of "Placed" sales orders for each and every product. (5 marks) Hint: Use "Union". Expected result using sample data: Code Description Nbr of Placed Orders BLLY00001 Billy 2L Saucepan 1 BLLY00002 Billy 3L Saucepan 1 BLLY00003 Billy 4L Saucepan 1 BLLY0004 Billy 5L Saucepan 0 QIAN00001 Qi Flutes (set of 6) 1 QIAN00002 Qi Wine Glasses (set of 6) 1 QIAN00003 Qi Glass Pitcher 0 RAJA00001 Raja Glass Decanter 1 RAJA00002 Raja Glass Goblet 2 12. List total amount paid by the customers 'with' and 'without' sales orders from each state. Sort results by total amount descending order. Hint: Use "Union". (5 marks) Expected result using sample data: State Total Paid QLD 2616.6 NSW 0 NT 0 QLD 0 SA 0 VIC 0 13. Using outer join List both customers, who "have placed orders" and "not placed orders" and number of times each customer placed sales order(s) as shown below. (5 marks) Note: For the purpose of this query, marks will not be awarded for queries that does not use outer join. Expected result using sample data: CID Name Street Suburb State Email No_of_times_Sorder_placed 1 Best Kitchens 1 Beef Highway Parkhurst QLD [email protected] 2 2 Best Kitchens 2 Rum Road Bargara QLD [email protected] 1 3 Sheila Smith 3 Sugar Street Andergrove QLD [email protected] 1 4 Samir Singh 4 River Road Milton QLD [email protected] 1 5 Lee Chin 5 Harbour Close Cremorne NSW [email protected] 0 6 Bruce Jones 6 Bay Way Elwood VIC [email protected] 0 7 Alan Jones 7 William Street Melbourne VIC [email protected] 0 8 Richard Johnson 9 Lonsdale avenue Melbourne VIC [email protected] 0 9 Tony Star 8 Plenty street Sydney NSW [email protected] 0 10 Tony Star 170 Sunshine avenue Adelaide SA [email protected] 0 11 Beth Anderson 60 Plant Street West End QLD 0 12 David Jones 8 Ellen Street Palmerston NT 0 Part B – Form development activity (10 marks) Using Kware database, develop a form to capture new customer details using a suitable title and a current date, which can be used for data entry operation. You are allowed to use any suitable user-friendly layout to develop the form. Strictly, do not copy from others. << Continue next page >> Part C – Report development activity (10 marks) Using the Kware database and Microsoft Access, create a report that provides details relating to customers and their orders. Your report should include a page heading with date, column headings, report totals, and page numbering. A sample report for this part is shown below and your report must resemble it: How and what to submit You must make an electronic submission for this assignment using the appropriate assignment submission link on the course web site. Your submission must consist of a single Microsoft Access file which contains your assignment work as follows: The Microsoft Access file contains the queries, forms and reports clearly labelled with the part and/or question number. e.g. "Part A – Q1" and so on, "Part B – Main Customer Form" and "Part C – Report of Orders Placed by Customers". We will not be able to mark your assignment unless your queries are labelled with correct question numbers.