Programming Assignment #1 Solution

Objectives              To become familiar with the concept of database application programming and query evaluation/processing.

 

 

Description        "Simple Database Application Program #1" (sdap1.pgc)

Generate 2 separate reports based on the following queries (one report for query #1 and another for query #2):

1.   For each combination of customer and product, compute the maximum and  minimum sales quantities along with the corresponding dates (i.e., dates of those maximum and minimum sales quantities) and the state in which the sale transaction took place. If there are 1 occurrences of the max or min, choose one – do not display all.

For the same combination of product and customer, compute the average sales quantity.

 

2.   For each combination of customer and product, output the maximum sales quantities for NY and NJ and  minimum sales quantities for CT in 3 separate columns. Like the first report, display the  corresponding dates (i.e., dates of those maximum and minimum sales quantities). Furthermore, for NY and NJ, include only the sales that occurred between

2000 and 2005; for CT, include all sales.

 

For this assignment, you can use a simple data structure (e.g., an array) to maintain the list of “information” being captured (we will discuss the type of information you will need to capture and maintain internally for the report over the next couple of lectures).

 

The following is a sample output – quantities displayed are for illustration only (not the actual values). (NOTE – the following output must be generated with a single scan of the ‘sales’ table).

 

CUSTOMER  PRODUCT     MAX_Q          DATE ST        MIN_Q           DATE ST        AVG_Q

========      ========      ===== ==========  ==        ===== ==========  ==        =====

Bloom Pepsi    2893    01/01/2006      NJ        12        09/25/2001      NY      1435

Sam     Milk     159      02/15/2002      NJ        1          03/23/2004      CT       56

Emily   Bread  3087    07/01/2005      NY      2          02/02/2001      NJ        1512

 

CUSTOMER PRODUCT    NY_MAX       DATE NJ_MAX        DATE CT_MIN            DATE

========      =======        ======           ==========  ======           ==========            ======           ==========

Sam   Egg     1908   01/11/2001    234     07/24/2005    2          11/03/2008

Helen Cookies         392     03/31/2002    2342   09/14/2000    11        07/23/2002

Bloom            Butter 7045   09/22/2003    923     03/10/2004    8          09/11/2006

 

Make sure that:

 

1.   “select * from sales” is the ONLY SQL statement allowed in your program.

 

2.   Character string data (e.g., customer name and product name) are left justified.

3.   Numeric data (e.g., Maximum/minimum Sales Quantities) are right justified.

 

4.   The Date fields are in the format of  MM/DD/YYYY (i.e., 01/02/2002 instead of 1/2/2002).

 

 

 

Please remember the following points when you're working on your programming assignments:

 

1. Your program must compile and execute based on the instructions provided in the README file

(i.e., if your programs contain special functions for other compilers and does not compile based

on README, you WILL lose 50% of the grade for the assignment).

 

 

 

2. Programming style is 20% of the grade. Please make sure to provide comments for the

program, functions, etc. as well as in-line comments as needed. Also, make sure to use 

meaningful names for your classes, variables, methods/functions, etc. Use proper indentation.

 

 

 

3.   In the header comments for your program (i.e., at the beginning of your program), please provide:

 

a. General instructions on how to execute your program (e.g., command line for the

program and whatever arguments it requires). This can be a simple copy & paste of the

README file, or you can provide a simplified bullet listing of the steps for compiling and

executing the code. 

 

 

 

 

b. Justification of your choice of data structures for your program – e.g., if you're using a

linked list to maintain whatever information necessary for your program, justify why it's a

data structure of your choice, as opposed to, say, arrays. If you're using other more

sophisticated data structures, please provide a brief description of the data structures

and again justify as to why you chose the data structures for your program.

 

 

 

 

 

c. A detailed description of the algorithm of your program, e.g., how you're computing and

maintaining the aggregates (e.g., min, max, avg) for your query output. You can do this

with a detailed pseudo code.         

 

 

 

4. Remember the only SQL statement allowed in your program is the simple select statement,

"select * from sales". Points will be deducted if you use any other SQL statements in your

programs.       

 

 

 

5.   You are NOT allowed to read in the entire table (‘sales’) and store them in memory before processing the rows. Instead, you need to read each row (one row at a time), process it and discard it.

 

Most importantly, make sure it's your own work! If we determine that your program is a copy of
someone else's, both you and that someone else will receive 0 for the assignment and possibly
 
additional penalties for the course.