The lists of bus stops and routes 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 routes 2. List all routes 3. Show a specific route 4. Reset the system 5. Exit Select (1/2/3/4/5): 3¶             ⇓ Show a specific route. Enter the route: 2¶             ⇓ Route 2: South Middle School, Choice Fitness, Hugo, Columbia Mall, Altru, UND, Ralph, BK, Aerospace 1. Enter routes 2. List all routes 3. Show a specific route 4. Reset the system 5. Exit Select (1/2/3/4/5): 2¶             ⇓            ...

• 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%) (Given SQL statements and DB design and implementation)
  It is given below.


• (System reset (given): 05%) (Given interface and SQL)
  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.


• (Bus route and stop input (given): 05%) (Given interface and SQL)
  Enter routes one by one. A route is a bi-directional sequence of bus stops, unique names, separated by commas such as
  
      South Middle School, Choice Fitness, Rydell, Hugo, Columbia Mall, Altru, UND, Ralph, Burger King
  
      (or South Middle School ⇔ Choice Fitness ⇔ Rydell ⇔ Hugo ⇔ Columbia Mall ⇔ Altru ⇔ UND ⇔ Ralph ⇔ Burger King)
  
  Also, a bus stop can not be repeated in the sequence.


• (Listing all data (given): 05%) (Given interface and SQL)
  Display all database table contents.


• (Showing the sequence of the bus stops of a route (given): 05%) (Given interface and SQL)
  Display the sequence of the bus stops of a route.


• (Finding the shortest itinerary: 70%) (Hints: Interface I and SQL I and Interface II and SQL II)
  By giving a start stop (like Altru) and a final stop (like Menards), give the shortest bus itinerary which is a sequence of bus stops (and bus routes) such as
  
      Altru (3) (start) ⇒ UND (3) ⇒ Ralph (3) ⇒ Burger King (3|1) ⇒ Aerospace (1) ⇒ Alerus Center (1) ⇒ Menards (1) (final)
  
  The itinerary is the one with the following features:
  • Commence with the start stop and conclude with the final stop.
  • It is the shortest itinerary; i.e., the one with the lowest number of bus stops among all valid itineraries.
  • An itinerary is a one-way sequence of bus stops, and no bus stops could be repeated.
  • A passenger can only change her/his bus route at a bus transfer stop which is passed by more than one route, and at most 2 routes could be taken in an itinerary.
  • A bus route could only be taken once.
  • Display “Not found!” if no itinerary is found.
  
  Examples are given next:
  
  

  Six routes are as follows: South Middle School, Motel 8, Hugo, Columbia Mall, Altru, KFC, BK, Alerus Center, Ralph Discovery Elementary School, Target, Menards, Aerospace, UND, Union, YMCA, Starbucks, City Hall Subway, Choice Fitness, McDonalds, Columbia Mall, Altru, ACME, Ralph, BK, Aerospace, Scheels Union, City Hall, Discovery Elementary School, Menards, Pizza Hut, Canad Inns, Walmart McDonalds, Choice Fitness, Target, Walmart, UND, Scheels, Subway, Red Lobster, Aerospace Starbucks, BK, Menards, Alerus Center, Ralph, Union, YMCA Six queries are as follows: Walmart to Starbucks: Walmart (5) ⇒ UND (5|2) ⇒ Union (2) ⇒ YMCA (2) ⇒ Starbucks (2) Altru to YMCA: Altru (3) ⇒ ACME (3) ⇒ Ralph (3|6) ⇒ Union (6) ⇒ YMCA (6) Scheels to Hugo: Scheels (3) ⇒ Aerospace (3) ⇒ BK (3|1) ⇒ KFC (1) ⇒ Altru (1) ⇒ Columbia Mall ⇒ Hugo (1) Canad Inns to Starbucks: Canad Inns (4) ⇒ Pizza Hut (4) ⇒ Menards (4|6) ⇒ BK (6) ⇒ Starbucks (6) Target to Hugo: Not found! Canad Inns to Altru: Not found!



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

Database Design and Implementation
Based on the previous descriptions, database design is supplied by the instructor as follows. The following three tables show sample values of a relational bus itinerary database, of which rid, rsid, and sid are the primary keys of the tables route (or r), route_stop (or rs), and stop (or s), respectively. The begin and end are the foreign keys of the table route, and rid and sid are the foreign keys of the table route_stop. The ord is the sequence order number of the bus stop in the route starting from 0.

undcemcs02> source ~/.profile 

undcemcs02> sqlplus C##user.id/password@//65.52.222.73:1521/cdb1 

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 17 03:32:43 2021
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Last Successful login time: Mon Jul 12 2021 02:31:52 -05:00

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

SQL> DROP SEQUENCE  route_seq; 

Sequence dropped.

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

Sequence created.

SQL> DROP TABLE  route; 

Table dropped.

SQL> CREATE TABLE  route ( 
  2    rid    INTEGER PRIMARY KEY, 
  3    begin  INTEGER NOT NULL, 
  4    end    INTEGER NOT NULL ); 

Table created.

SQL> DROP SEQUENCE  stop_seq; 

Sequence dropped.

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

Sequence created.

SQL> DROP TABLE  stop; 

Table dropped.

SQL> CREATE TABLE  stop ( 
  2    sid   INTEGER PRIMARY KEY, 
  3    name  VARCHAR(32) NOT NULL ); 

Table created.

SQL> DROP SEQUENCE  route_stop_seq; 

Sequence dropped.

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

Sequence created.

SQL> DROP TABLE  route_stop; 

Table dropped.

SQL> CREATE TABLE  route_stop ( 
  2    rsid  INTEGER PRIMARY KEY, 
  3    rid   INTEGER NOT NULL, 
  4    sid   INTEGER NOT NULL, 
  5    ord   INTEGER NOT NULL ); 

Table created.

SQL> INSERT INTO  stop 
  2    SELECT  stop_seq.NEXTVAL, 'Alerus Center'  FROM DUAL; 

1 row created.

SQL> INSERT INTO  stop 
  2    SELECT  stop_seq.NEXTVAL, 'Starbucks'  FROM DUAL; 

1 row created.

SQL> INSERT INTO  route 
  2    SELECT  route_seq.NEXTVAL, 1, 2  FROM DUAL; 

1 row created.

SQL> INSERT INTO  route_stop 
  2    SELECT  route_stop_seq.NEXTVAL, 1, 1, 0 FROM DUAL; 

1 row created.

SQL> INSERT INTO  route_stop 
  2    SELECT  route_stop_seq.NEXTVAL, 1, 2, 8 FROM DUAL; 

1 row created.

SQL> SELECT * FROM  stop; 

SID            NAME  
---    ------------------- 
  1    Alerus Center           
  2    Starbucks

SQL> SELECT * FROM  route; 

RID    BEGIN    END
---    -----    ---
  1      1       2

SQL> SELECT * FROM  route_stop; 

RSID    RID    SID    ORDER
----    ---    ---    -----
 1       1      1       0
 2       1      2       5

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>

• Assume the 13 bus stops and 3 bus routes are saved in the database as follows:

  Route 100:

  Columbia Mall, Altru Hospital, UND, Burger King, Aerospace School, Menards
  (or Columbia Mall ⇔ Altru Hospital ⇔ UND ⇔ Burger King ⇔ Aerospace School ⇔ Menards)

  Route 200:

  Altru Hospital, Walmart, Alerus Center, Starbucks, UND, Hugo
  (or Altru Hospital ⇔ Walmart ⇔ Alerus Center ⇔ Starbucks ⇔ UND ⇔ Hugo)

  Route 300:

  Menards, Buffalo Wild Wings, Hugo, Columbia Mall, Choice Fitness
  (or Menards ⇔ Buffalo Wild Wings ⇔ Hugo ⇔ Columbia Mall ⇔ Choice Fitness)

  
  

  -- SQL for ListAll.java SQL> SELECT * FROM stop s ORDER BY s.sid; SID NAME ----- -------------------- 20 Choice Fitness 25 Hugo 30 Alerus Center 35 Altru Hospital 40 UND 45 Burger King 50 Mcdonalds 55 Aerospace School 60 Menards 65 Buffalo Wild Wings 70 Starbucks 75 Columbia Mall 80 Walmart SQL> SELECT * FROM route r ORDER BY r.rid; RID BEGIN END ---- ------------ --------- 100 1 6 200 7 12 300 13 17 SQL> SELECT rs.rsid, rs.rid, rs.sid, s.name AS "Stop Name", 2 rs.ord AS "Order" 3 FROM route_stop rs, stop s 4 WHERE rs.sid = s.sid 5 ORDER BY rs.rsid, rs.rid, rs.ord; RSID RID SID Stop Name Order ---- -------- -------- -------------------- -------- 1 100 75 Columbia Mall 0 2 100 35 Altru Hospital 1 3 100 40 UND 2 4 100 45 Burger King 3 5 100 55 Aerospace School 4 6 100 60 Menards 5 7 200 35 Altru Hospital 0 8 200 80 Walmart 1 9 200 30 Alerus Center 2 10 200 70 Starbucks 3 11 200 40 UND 4 12 200 25 Hugo 5 13 300 60 Menards 0 14 300 65 Buffalo Wild Wings 1 15 300 25 Hugo 2 16 300 75 Columbia Mall 3 17 300 20 Choice Fitness 4



• Find all possible routes and transfers for the itinerary from the start stop “Hugo” to the final stop “Burger King” by using at most one transfer (interface):
  
  

  -- SQL for FindRoutes.java SQL> SELECT r1.rid AS "Route One", r2.rid AS "Route Two", 2 s3.sid AS "Transfer ID", s3.name AS "Transfer Name" 3 FROM route r1, route_stop rs1, stop s1, 4 route r2, route_stop rs2, stop s2, 5 route r3, route_stop rs3, stop s3, 6 route r4, route_stop rs4, stop s4 7 WHERE 8 ( UPPER(s1.name) = 'HUGO' AND UPPER(s2.name) = 'BURGER KING' AND 9 s1.sid = rs1.sid AND rs1.rid = r1.rid AND 10 s2.sid = rs2.sid AND rs2.rid = r2.rid AND 11 r1.rid = r2.rid AND s3.sid = s1.sid ) OR 12 13 ( ( ( UPPER(s1.name) = 'BURGER KING' AND UPPER(s2.name) = 'HUGO' ) OR 14 ( UPPER(s1.name) = 'HUGO' AND UPPER(s2.name) = 'BURGER KING' ) ) AND 15 s1.sid = rs1.sid AND rs1.rid = r1.rid AND 16 s2.sid = rs2.sid AND rs2.rid = r2.rid AND 17 r1.rid < r2.rid AND 18 s3.sid <> s1.sid AND s3.sid <> s2.sid AND 19 rs3.rid = rs1.rid AND rs4.rid = rs2.rid AND 20 s3.sid = rs3.sid AND s3.sid = rs4.sid ) 21 22 GROUP BY r1.rid, r2.rid, s3.sid, s3.name 23 ORDER BY r1.rid, r2.rid, s3.sid, s3.name; Route One Route Two Transfer ID Transfer Name ----------- ----------- ------------- ---------------- 100 200 35 Altru Hospital 100 200 40 UND 100 300 60 Menards 100 300 75 Columbia Mall



• Find all possible routes and transfers for the itinerary from the start stop “Hugo” to the final stop “Menards” by using at most one transfer (interface). Note that if the itinerary is in a route, the transfer (always the start stop) could be ignored.
  
  

  Route One Route Two Transfer ID Transfer Name ----------- ----------- -------------- ---------------- 100 200 35 Altru Hospital 100 200 40 UND 100 300 75 Columbia Mall 300 300 25 Hugo



• Find all possible routes and transfers for the itinerary from the start stop “UND” to the final stop “Choice Fitness” by using at most one transfer (interface):
  
  

  Route One Route Two Transfer ID Transfer Name ----------- ----------- ------------- ---------------- 100 300 60 Menards 100 300 75 Columbia Mall 200 300 25 Hugo



• List the bus stops between two stops (such as “Altru Hospital” and “UND”) inclusively in a route (such as 12) (interface):
  
  

  -- SQL for ListStops.java SQL> SELECT s.name, rs.ord FROM stop s, route_stop rs 2 WHERE rs.rid=12 AND rs.sid=s.sid AND 3 ( ( rs.ord >= ( SELECT rs1.ord FROM stop s1, route_stop rs1 4 WHERE rs1.rid=rs.rid AND rs1.sid=s1.sid AND 5 UPPER(s1.name)='ALTRU HOSPITAL' ) AND 6 rs.ord <= ( SELECT rs2.ord FROM stop s2, route_stop rs2 7 WHERE rs2.rid=rs.rid AND rs2.sid=s2.sid AND 8 UPPER(s2.name)='UND' ) ) OR 9 ( rs.ord <= ( SELECT rs3.ord FROM stop s3, route_stop rs3 10 WHERE rs3.rid=rs.rid AND rs3.sid=s3.sid AND 11 UPPER(s3.name)='ALTRU HOSPITAL' ) AND 12 rs.ord >= ( SELECT rs4.ord FROM stop s4, route_stop rs4 13 WHERE rs4.rid=rs.rid AND rs4.sid=s4.sid AND 14 UPPER(s4.name)='UND' ) ) ) 15 ORDER BY rs.ord; NAME ORD ------------------ ------ Altru Hospital 2 Columbia Mall 3 Walmart 4 BK 5 Ralph 6 UND 7



• Find the number of bus stops between two stops (e.g., UND and Menards) inclusively in a route (e.g., 100) (interface):
  
  

  -- SQL for FindNumber.java SQL> SELECT ABS( rs1.ord - rs2.ord ) + 1 AS "Number" 2 FROM route r1, route_stop rs1, stop s1, 3 route r2, route_stop rs2, stop s2 4 WHERE UPPER(s1.name) = 'UND' AND s1.sid = rs1.sid AND rs1.rid = 100 AND 5 UPPER(s2.name) = 'MENARDS' AND s2.sid = rs2.sid AND rs2.rid = 100 6 GROUP BY rs1.ord, rs2.ord; Number -------- 4

• 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 four exercises (06%, 09%, 12%, and 13%, respectively) is 40% of the final grade.
  • If not specified, no error checking is required; i.e., you may assume the input is always correct for that case. For example, the bus stops entered will always be unique names.
  • 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 users are enrolled at the same time, an ID may be assigned to both 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.