Description
Problem 1
5 marks – Referencing the sales database from the database lab, provide SQL queries that
answer the following questions:
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) Which product line has the highest profit margin in 2003. Include the profit margin.
The profit of an item is the sales of the item less the cost of the item. The profit margin
is the profit divided by the total base cost.
d) List the 5 employees with the highest sales in 2004. Include their total sales values and
ensure that the top seller is first in the list.
e) Which employees had the value of their 2004 orders exceed the value of their 2003
orders?
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 on
separate input lines:
– The starting date for the period to summarize
– The ending date for the period to summarize
– The name of the file for the output (full path specified with the file name)
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 2 categories:
1. Manager information
a. Report, for each manager, the manager’s name, the city of the manager’s office,
the number of employees who report to the manager, the number of customers
who are served by staff who report to the manager, and the total sales by staff
who report to the manager
2. Product line information
a. Report, for each product line, its name, its description and then for each
customer who ordered some item of the product line, report the customer’s
name and the total value of this product line’s order by this customer in the
given period
In all of the reporting, do not report any employees, customers, orders, or offices that 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_report (year, manager_list, product_line_list) >
<!ELEMENT year (start_date, end_date) >
<!ELEMENT manager_list (manager*) >
<!ELEMENET manager (manager_name, manager_city, staff, customers, sales_value) >
<!ELEMENT producdt_line_list (product_line*)>
<!ELEMENT product_line (product_line_name, product_line_description, customer*) >
<!ELEMENT customer (customer_name, order_value)>
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.
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)…data is not representative
of the database’s contents:
2003-01-01 </ start_date>
2003-12-31 </ end_date>
Diane Murphy
Paris
3
10
145258.23
Classic Cars Longer description of classic cars here
Atelier graphique
23045.84
Signal Gift Stores
169248.55
</year_end_report >
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 timberlea.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 are allowed to use an XML library if it is running on timberlea. However, this XML is
simple enough that you can also generate it with your own code.
• 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 manager information – 3 marks
• Correct extraction of product line information – 3 marks
• Quality of the argument on why your solution is ready to deploy – 2 marks
Problem 3
Goal
Practice some database design.
Question
The database from problem 2 isn’t complete. Although it stores our orders, it doesn’t have
enough information to complete our billing to customers. Your task is to design changes to the
database to augment what it can do.
Specifically, your design change should let us:
1. Create promotions by office as a percentage discount to the order value. Promotions
are identified by a promotion code tied to the order. We will want to track the uptake
of promotions among the orders that use it.
2. Add shipping costs to orders. We will have a small (3-10) number of shipping companies
that we use. Each shipping company will charge a percentage of the order value as the
shipping cost. The shipping cost is set when the order is made. Meanwhile, the
shipping company can change the percentage over time as we renegotiate our contract
with the shippers. For those negotiations, we will want to report on the amount of
activity (number of orders, total order value) sent through each shipper in a given
period of time.
3. Add taxes to the invoices. Each office will have zero or more taxes that will be applied
to any bill issued to a customer whose representative is in the office. The tax amounts
are set when the order is made. The tax percentages can change over time.
When calculating the final value of an order, we first apply any promotional discount. Next, we
add the shipping costs and end with the taxes on the order.
For this question, submit
1. An updated entity-relation diagram with your modifications. The diagram should be in
3NF or better.
2. An SQL file that, when run on the mysql database, would make whatever changes are
needed to the database to realize your design modifications.
3. An SQL file that, when run on the mysql database that has your changes, will populate
the database with 3 sample shipper records and one tax record for each office except
the Paris office, which will have two tax records.
4. A Java class called “OrderManager” that includes two methods:
a. checkout(int orderNumber, int shipperId ) that is called once an order is set in
the database and when we now need to apply the promotional discounts,
shipping costs, and taxes. If the shipperId is 0 then the customer picked up the
order and there is no shipping cost; otherwise, the shipperId will match a shipper
id that you include in your database update.
b. orderValue( int orderNumber ) that returns the total cost to the customer of the
given order number.
CSCI 3901
Marking scheme
• Database design to handle the new information. Looking for correctness, natural fit
with what already exists, minimizing the changes, flexibility, extensibility, and, of course,
normalization – 6 marks
• Sample SQL to create the structures and populate tables – 2 marks
• Checkout and orderValue methods – 4 marks
