The lists of food items and combos can NOT be saved in or loaded into Java data structures like arrays, ArrayList, LinkedList, HashMap, LinkedHashMap, HashSet, LinkedHashSet, TreeMap, TreeSet, PriorityQueue, Set, Vector, etc. though temporary results or data are allowed to be stored in Java data structures. This requirement will be carefully examined when grading.

• Have to use the Java language, especially JDBC (Java Database Connectivity), mainly and may use Perl or other languages to connect the Web to Java.


• The input data has to be saved in a database, instead of files or temporary storage like arrays. During the testing, the programs will be shut down a couple of times to ensure the data is not saved in temporary storage. In addition, using files to save the data will make the jobs more difficult. Saving data in a database may be difficult in the beginning, but once it is set up, the data management is simple and straightforward.


• All software requires to include user interfaces, and three approaches are available for user interface construction for this exercise. The grading would not be affected by the approach chosen, but user-friendliness will be heavily considered.
  
  
  1. Internet-enabled interface: It is the most popular one and a trend for current IT systems. The system entry page must be located at http://undcemcs02.und.edu/~user.id/280/4/ and all pages must be hosted by http://undcemcs02.und.edu/~user.id/ .
  2. Graphical user interface: It is the most difficult one (e.g., using AWT, Abstract Windowing Toolkit).
  3. Text user interface: It is the least favorite one and an obsolete method. An example is a 1-2-3 system like

         Start Using the System
         1. Enter food items
         2. Enter meal combos
         3. Find the greatest saving
         4. Exit
         Select (1/2/3/4): 2
                 ⇓
         Enter Meal Combos
         1. Enter food items
         2. Enter a price
         3. Enter another combo
         4. Return
         Select (1/2/3/4): 3
                 ⇓
                ...

• For the above Approach a, send the password for displaying the source code online to the instructor at wenchen@cs.und.edu (only one password for all interfaces and all exercises).


• For the above Approaches b and c,
  
  
  • Send an email to the instructor at wenchen@cs.und.edu to set up an appointment to demonstrate your exercise to the instructor individually, so misunderstanding would be minimized. The instructor will prepare a set of test data to be used by all students.
    (Zoom at https://und.zoom.us/j/2489867333 will be used for demonstration.)
  
  
  • Submit all source code via Blackboard.

• [Database implementation (given): 05%]
  It is given below.


• [System reset (given): 05%]
  The system can be reset, which is to clear all data stored in the database, so the instructor can test the system by using only his own test data. That is the system has to include a button such as “Clear system” in the system entry page.


• [Food item input (given): 05%]
  The data of a food item includes
  
  
  • a unique name such as French fries and double cheeseburger, and
  • a price like $2.99.


• (Combo input (given): 05%)
  The data of a combo includes
  
  
  • a unique number (assigned by the system automatically after entering the combo),
  • a list of food items such as (French fries, coke, cheeseburger) and (Caesar salad, coke, double cheeseburger), and
  • a price like $7.99, which is less than the sum of all its food items’ prices.


• [Listing all data (given): 05%]
  Display all database table contents.


• [Finding the combos (given): 05%]
  Display the combos each of whose food items contain any of the case-insensitive keywords entered.


• (Finding the largest saving: 30%)
  Display at most 2 combos within an entered budget including
  
  
  • the 1 or 2 combos to purchase,
  • the sum of the prices of all food items in the combos (a),
  • the sum of all the combos’ prices (b),
  • the total saving (= a - b)
  
  with the following features:
  
  
  • the saving is the difference between (a) the sum of the prices of all food items in the combos and (b) the sum of all combos’ prices,
  • the sum of all combos’ prices can not exceed the budget, and
  • no combo can be purchased twice.
  
  One example of the result is as follows:
  
  
  • Combos 1 and 3 to purchase,
  • Food items: $16.73,
  • Combos: $13.99, and
  • Saving: $2.74.


• (Finding the lowest price: 30%)
  For a meal order without duplicate items, like (diet coke, Pepsi, ice cream, French fries, onion ring, cheeseburger, and whopper), show the lowest price including
  
  
  • a list of food items and 0/1/2 combos to purchase (no combo can be purchased twice), and
  • the lowest price.
  
  One example of the result is as follows:
  
  
  • ice cream, whopper, Combos 1 and 3 to purchase, and
  • Total price: $19.73.


• (User-friendliness: 10%)
  User-friendliness will be heavily considered when grading. In the past, some exercises were awkward, which made the grading or browsing difficult.

undcemcs02> sqlplus C##userid/password@//65.52.222.73:1521/cdb1 

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 22 17:37:07 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Fri Oct 23 2020 14:55:00 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0
 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real
 Application Testing options

SQL> DROP SEQUENCE  item_seq; 

Sequence dropped.

SQL> CREATE SEQUENCE  item_seq  START WITH 1 INCREMENT BY 1 CACHE 100; 

Sequence created.

SQL> DROP TABLE  items; 

Table dropped.

SQL> CREATE TABLE  items ( 
  2    iid    INTEGER PRIMARY KEY, 
  3    name   VARCHAR(32) NOT NULL, 
  4    price  NUMBER(5,2) NOT NULL ); 

Table created.

SQL> DROP SEQUENCE  combo_seq; 

Sequence dropped.

SQL> CREATE SEQUENCE  combo_seq  START WITH 1 INCREMENT BY 1 CACHE 100; 

Sequence created.

SQL> DROP TABLE  combos; 

Table dropped.

SQL> CREATE TABLE  combos ( 
  2    no     INTEGER PRIMARY KEY, 
  3    price  NUMBER(5,2) NOT NULL ); 

Table created.

SQL> DROP TABLE  combo_item; 

Table dropped.

SQL> CREATE TABLE  combo_item ( 
  2    no   INTEGER NOT NULL, 
  3    iid  INTEGER NOT NULL, 
  4    PRIMARY KEY( no, iid ), 
  5    FOREIGN KEY( no ) REFERENCES combos( no ) ON DELETE CASCADE, 
  6    FOREIGN KEY( iid ) REFERENCES items( iid ) ON DELETE CASCADE ); 

Table created.

SQL> INSERT INTO  items 
  2    SELECT  item_seq.NEXTVAL, 'diet coke', 0.99  FROM DUAL; 

1 row created.

SQL> INSERT INTO  items 
  2    SELECT  item_seq.NEXTVAL, 'double cheeseburger', 2.00  FROM DUAL; 

1 row created.

SQL> INSERT INTO  combos 
  2    SELECT  combo_seq.NEXTVAL, 1.99  FROM DUAL; 

1 row created.

SQL> INSERT INTO  combo_item  VALUES( 1, 1 ); 

1 row created.

SQL> INSERT INTO  combo_item  VALUES( 1, 2 ); 

1 row created.

SQL> SELECT * FROM  items; 

IID            NAME            PRICE
---    -------------------     -----
  1    diet coke                0.99
  2    double cheeseburger      2.00

SQL> SELECT * FROM  combos; 

 NO    PRICE
---    -----
  1     1.99

SQL> SELECT * FROM  combo_item; 

 NO    IID
---    ---
 1      1
 1      2

SQL> commit; 
SQL> exit; 
Disconnected from Oracle Database 12c Enterprise Edition Release
 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application
 Testing options

undcemcs02>

SELECT * FROM items ORDER BY iid; IID NAME PRICE ----- ------------------- -------- 1 cheese burger 1.99 2 Diet Coke 0.99 3 Pepsi Cola 1.09 4 Whopper 3.00 5 French fries 1.50 SELECT * FROM combos ORDER BY no; NO PRICE ---- ------------ 1 3.50 2 2.00 3 3.30 4 4.50 5 3.00 SELECT * FROM combo_item ORDER BY no, iid; NO IID ---- -------- 1 1 1 2 1 5 2 2 2 5 3 3 3 4 4 3 4 4 4 5 5 1 5 2 5 3

• The following steps may be used to find the largest saving:
  
  
  1. Find the 15 combinations (5 for 1 combo and 10 for 2 combos).
  2. Drop the invalid combinations (exceeding the budget).
  3. Find the saving of each combination, and show the combination having the largest saving.


• The following steps may be used to find the lowest price:
  
  
  1. Find the 16 combinations (1 for 0 combo, 5 for 1 combo, and 10 for 2 combos).
  2. Drop the invalid combinations (like a food item of a combo not in the meal order or one item in both combos).
  3. Add food items to each combination to complete the order.
  4. Find the price of each combination, and show the combination having the lowest price.


• List all possible combinations of 2 combos from the 5 combos:
  
  

  SELECT c1.no, c2.no FROM combos c1, combos c2 WHERE c1.no < c2.no ORDER BY c1.no, c2.no; NO NO -------- ---------- 1 2 1 3 1 4 1 5 2 3 2 4 2 5 3 4 3 5 4 5



• List all possible combinations of 2 combos from the 5 combos and the price of each combination:
  
  

  SELECT c1.no, c2.no, (c1.price + c2.price) AS TOTAL_PRICE FROM combos c1, combos c2 WHERE c1.no < c2.no ORDER BY c1.no, c2.no; NO NO TOTAL_PRICE -------- ---------- ----------- 1 2 5.50 1 3 6.80 1 4 8.00 1 5 6.50 2 3 5.30 2 4 6.50 2 5 5.00 3 4 7.80 3 5 6.30 4 5 7.50



• List all food items in Combos #1 and #2:
  
  

  SELECT i.name FROM items i, combo_item ci WHERE (ci.no = 1 AND ci.iid = i.iid ) OR (ci.no = 2 AND ci.iid = i.iid ); NAME ------------------ cheese burger Diet Coke French fries Diet Coke French fries



• List how many food items both Combos #1 and #2 (and #2 and #5) share:
  
  

  SELECT COUNT(*) FROM combo_item ci1, combo_item ci2 WHERE ci1.no=1 AND ci2.no=2 AND ci1.iid=ci2.iid; COUNT(*) ---------- 2 SELECT COUNT(*) FROM combo_item ci1, combo_item ci2 WHERE ci1.no=2 AND ci2.no=5 AND ci1.iid=ci2.iid; COUNT(*) ---------- 1



• List how many food items in Combos #1 and #2 (and #2 and #5) are not in the list ( cheese burger, Diet Coke, French fries):
  
  

  SELECT COUNT(DISTINCT name) FROM combo_item ci1, combo_item ci2, items i WHERE ci1.no=1 AND ci2.no=2 AND (ci1.iid=i.iid OR ci2.iid=i.iid) AND ( i.name<>'cheese burger' AND i.name<>'Diet Coke' AND i.name<>'French fries'); COUNT(DISTINCT NAME) ------------------- 0 SELECT COUNT(DISTINCT name) FROM combo_item ci1, combo_item ci2, items i WHERE ci1.no=2 AND ci2.no=5 AND (ci1.iid=i.iid OR ci2.iid=i.iid) AND ( i.name<>'cheese burger' AND i.name<>'Diet Coke' AND i.name<>'French fries'); COUNT(DISTINCT NAME) ------------------- 1



• Find the sum of the prices of all food items in each combo:
  
  

  SELECT c.no, c.price, SUM(i.price) AS Original_Price FROM combos c, items i, combo_item ci WHERE c.no = ci.no AND ci.iid = i.iid GROUP BY c.no, c.price ORDER BY c.no; NO PRICE ORIGINAL_PRICE -------- ---------- -------------- 1 3.50 4.48 2 2.00 2.49 3 3.30 4.09 4 4.50 5.59 5 3.00 4.07



• Find each combo’s saving = sum of the prices of all combo’s food items – combo price:
  
  

  SELECT c.no AS Combo, ( SUM(i.price) - c.price ) AS Saving FROM combos c, items i, combo_item ci WHERE c.no = ci.no AND ci.iid = i.iid GROUP BY c.no, c.price ORDER BY c.no; COMBO SAVING -------- ---------- 1 0.98 2 0.49 3 0.79 4 1.09 5 1.07

• Specifications:
  • The instructor (or your assumed client) has given the exercise specifications as many details as he possibly can. If you are confused about the specifications, you should ask in advance. Study the specifications very carefully. No excuses for misunderstanding or missing parts of the specifications after grading.
  • The specifications are not possible to cover every detail. You are free to implement the issues not mentioned in the specifications, but the implementations should make sense. Implemented functions lacking of common sense may cause the instructor to grade your exercise mistakenly, and thus lower your grade.
  • The exercise must meet the specifications. However, exercises with functions exceeding the specifications will not receive extra credits.
  
  
• Grading:
  • This exercise will not be graded if the submission methods are not met. Students take full responsibility if the web site (or user interfaces) is not working.
  • A set of test data will be used by all students. The grades are primarily based on the results of testing. Other factors such as performance, programming styles, algorithms, and data structures will be only considered minimally.
  • Before submitting the exercise, test it comprehensively. Absolutely no extra points will be given after grading.
  • This course is about the Java language, so Java should be used to implement all functions, except the functions related to web (like connecting the Web to Java by using Perl or PHP).
  • The total weight of all three exercises (10%, 15%, and 15%) is 40% of the final grade. Exercises I & II are required, and you can pick either Exercise III or IV.
  • If not specified, no error checking is required; i.e., you may assume the input is always correct for that case. For example, the price entered will always be a valid float number.
  • Feel free to design your own interfaces; user-friendliness will be heavily considered; each function/button will be tested extensively; and from the source code submitted, the programs will be examined.
  • The newest Firefox browser will be used to grade exercises. Note that IE, Edge, Chrome, and Firefox are not compatible. That is your exercises may work on the IE, Edge, or Chrome but not Firefox.
  • The systems have to be active until the end of this semester. They will be re-checked for plagiarism from time to time.
  • The instructor will inform you the exercise evaluations by emails after grading.


• Databases:
  • A database has to be used and try to perform the tasks by using SQL as much as possible because SQL, a non-procedural language, can save you a great deal of programming efforts.
  • There are many advantages of using databases. If database is not used, the problems caused by not-using-transaction must be considered. For example, if two authors are enrolled at the same time, an ID may be assigned to different customers if databases are not used.


• Comments:
  • Make the exercise work first. Do not include extra features, such as user passwords, in the beginning. By the way, you will not receive credits for the extra features.
  • Time management is critical for software development. If you are not able to complete the exercise, display whatever you have accomplished, so the instructor can give partial credit to your exercise.
  • One way to build a website/system from scratch is to design the user interfaces first and then implement the system button by button. By doing this way, it could simplify the construction. The recommended construction steps are
    
    
    1. Examining the specifications very carefully,
    2. Designing the databases (E-R modeling or normalization),
    3. Implementing the databases (SQL),
    4. Building the interfaces (HTML, CSS, and JavaScript),
    5. Implementing the system button by button (JDBC), and
    6. Testing the exercise thoroughly.
    
    
  • Minimum web programming (for connecting the Web to Java) skills will be needed in this exercise. HTML and CSS should be easy to learn. Check CGI 101 for dynamic web programming. Besides, many dynamic web programming examples are given in the class slides. CGI (Common Gateway Interface) is an old technology, but is easy to learn and has no problems implementing most of web features. However, the problem of CGI is the security and we may ignore the security issues at this moment.

Slide 13.0: Programming Exercise IV: My grocery shopping (finding the best price) Slide 13.2: Electronic and mobile commerce systems Home