write a assignment on sale database with SQL queries

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

CSCI 3901 Assignment 5 

Due date: 11:59pm Monday, November 18, 2019 in git.cs.dal.ca at 

https://git.cs.dal.ca/courses/2019-fall/csci-3901/assignment-5/xxxx.git where xxxx is your CSID (this repository already exists, so clone it and then add your code to it). 

Problem 1 5 marks – Referencing the sales database from the database lab, provide SQL queries that answer the following questions: 

1. a) Which customers are in a different city than their sales representative? b) Which orders included sales that are below the manufacturer’s suggested retail price 

(MSRP)? c) List the top 5 products for 2004 with the highest average mark-up percentage per order. 

Include the mark-up. The mark-up is the ratio of (sale price – cost) and cost. d) List the top 3 employees with the greatest average diversity of products in their orders. e) What is the average time needed to ship orders from each office in 2005, relative to the 

order date? 

Problem 2 

Goal Access SQL through Java. Gain some exposure to XML. 

Question You work for the Mini-Me Toy Car company. Management periodically wants a summary of the company’s operation over a period of time. 

Your job is to extract the summary information from the database. You will store the summary information in a file that follows an XML format. Someone else will then use XML tools (notably XSLT) to convert your information into something that management will review. 

Input Your program will obtain the following information from the keyboard in the following order: 

- The starting date for the period to summarize - The ending date for the period to summarize - The name of the file for the output All dates will be in a YYYY-MM-DD format. 

Output Your program will send all of its output to the specified file. 

The data that you extract will be in 3 categories: 

1. Customer information 
2. Report the customer name, address, number of orders in the period, and total 

order value 2. Product information 

1. Report, for each product line, the product line name and for each product in the 

product line report the name, vendor, units sold, and total value of products sold 3. Employee information 

1. Report, for each employee, their name, office, number of customers active in the 

period, and total order value In all of the reporting, do not report any customers or products who have not had any interaction over the reporting period. 

Your output file will be in an XML format. XML uses a set of tags to surround data to let you know what the data is. Some tags can be nested in other tags. 

We will use a simple version of XML. The first line of your XML file should provide information on the version of XML to use. The following line will be sufficient: 

 

Following this first line, we get a set of nested tags to store the data. The starting tag has the format <...> and the matching ending tag has the format <.../> (differing by the ending slash) where ... is the tag name. The outermost tag is year_end_report 

Here is a description of the correct nesting (in a data type definition (DTD) format): 

<!ELEMENT year_end_summary (year, customer_list, product_list) > <!ELEMENT year (start_date, end_date) > <!ELEMENT customer_list (customer*) > <!ELEMENET customer (customer_name, address, num_orders, order_value) > <!ELEMENT address (street_address, city, postal_code, country) > <!ELEMENT product_list (product_set*) > <!ELEMENT product_set (product_line_name, product*) > <!ELEMENT product (product_name, product_vendor, units_sold, total_sales) > <!ELEMENT staff_list (employee*)> <!ELEMENT employee (first_name, last_name, office_city, active_customers, total_sales> 

All items without an ELEMENT line are of type #PCDATA (if that matters to you). The address in customer is just the first address line in the database. 

As an example to read this information, the tags year_end_summary must contain nested tags for each of year, customer_list, product_list, and staff_list_list. The tag customer_list will 

contain zero or more tags with name “customer”, as identified by the * after the “customer” tag in the ELEMENT clause. 

In an XML file, the spacing doesn’t matter. I encourage you to use spacing and tabs to make the XML file readable by a person. 

Information on XML can be found at w3schools.com. 

Sample output (just one entry shown for each section, for brevity): 

 

2003-02-12 </ start_date> 2003-02-19 </ end_date>  

 

Rovelli Gifts

 

Via Ludovico il Moro 22 Bergamo 24100 Italy 1 52151.81  

 

Planes  

1980s Black Hawk Helicopter </product_name > Red Start Diecast </product_vendor > 36 4825.44  

 

Pamela Castillo  

Paris 1 52151.81 </year_end_summary > 

Constraints 

• You may use any data structure from the Java Collection Framework. 
• Write your solution in Java. The solution code must be your own. 
• Use the mysql JDBC connection for Java. 
• If in doubt for testing, I will be running your program on bluenose.cs.dal.ca. Correct operation of your program shouldn’t rely on any packages that aren’t available on that system. 

Notes • Use SQL vs Java as you deem best. 

• Be sure to document your approach and any resources that you use. 
• Look at where the bulk of the marks are in the marking scheme to help focus your efforts. 
• You can run your queries against the csci3901 database on db.cs.dal.ca I will also make the sql file for the database available to you so that you can create your own copy of the database. 
• You are expected to submit 

o Your Java code o External documentation o An argument as to why your solution is ready to be deployed 

Marking scheme 

• Documentation (internal and external) – 3 marks 
• Program design, organization, and style – 2 marks 
• Proper XML file format, including human readability – 3 marks 
• Correct extraction of customer information – 4 marks 
• Correct extraction of product information – 4 marks 
• Correct extraction of staff information – 4 marks 
• Design elements of your program that would make it easy to report additional information for customers, products, or staff with minimal code changes (and avoiding cut-and-paste of code segments) – 2 marks 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme