Programming Assignment #2 Solution

Objectives:        •   To become familiar with the concept of database application programming and query processing for complex OLAP/BI queries.

 

 

Description:       "Simple Database Application Program #2" (sdap2.cpp)

•    Generate reports based on the following queries:

1.   For each customer and product, compute (1) the customer's average sale of this product, (2) the average sale of the product for the other customers and (3) the customer’s average sale of the other products.

2.   For customer and product, show the average sales before and after each quarter (e.g., for Q2, show average sales of Q1 and Q3. For “before” Q1 and “after” Q4, display <NULL). The “YEAR” attribute is not considered for this query – for example, both Q1 of 1997 and Q1 of 1998 are considered Q1 regardless of the year.

3.   For customer and product, count for each quarter, how many sales of the previous and how many sales of the following quarter had quantities between that quarter’s average sale and minimum sale. Again for this query, the “YEAR” attribute is not considered.

For this assignment, you can write either 3 separate programs, one for each of the 3 reports, or one program generating all of the 3 reports.

 

Again, the only SQL statement you’re allowed to use for your program is:

 

select * from sales;

That is, no where clauses, no aggregate functions (e.g., avg, sum, count), etc. And, you cannot store the ‘sales’ table in memory.

The following are sample report output (NOTE: the numbers shown below are not the actual aggregate values. You can write simple SQL queries to find the actual aggregate values).

 

 

 

Report #1:

 

CUSTOMER PRODUCT  

 

CUST_AVG         

 

OTHER_CUST_AVG OTHER_PROD_AVG

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

Helen   Bread       ========

243         ============== ==============

268          1493

Emily   Milk          1426       478           926

. . . .                        

Report #2:                            

CUSTOMER PRODUCT

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

Bloom   Bread

Sam      Milk         QUARTER

=======

Q1

Q3           BEFORE_AVG AFTER_AVG

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

<NULL      2434

254       325

. . . .                        

Report #3:                            

CUSTOMER PRODUCT

======== ======= Emily   Bread Bloom   Milk          QUARTER

======= Q4

Q2           BEFORE_TOT AFTER_TOT

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

23     <NULL

45        35

 

sure that:

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

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

 

3.    The Date fields are in the format of MM/DD/YYYY (i.e., 01/02/1992 instead of

1/1/1992).

 

 

Grading:                  •   (80 pts.) Logic/Correctness

 

•    (20 pts.) Programming Style (e.g., comments, indentation, use of functions, etc.). You must include a program header, function header, etc. to clearly state what your program and functions are designed to do. Also for inline comments, please state clearly the purpose of those statements – for you as the programmer and to help others better understand your programming logic.

A program with compilation errors will earn no more than 50 points.

Sample Command Line


 

 

$ sdap2 [sales], where ‘sales’ is an optional argument for the table name.

 

Submission:      Submit your source code (file) (with your name and CWID on it) on Canvas.

 

Please include a “README” file with detailed instructions on how to compile and run the code, especially if you are using a language other than C, C++ or Java.