Programming Exercise I: An online bookstore using a relational database

Programming Exercise I: An Online Bookstore Using a Relational Database
(Industry-Level, Second-to-None Comprehensive Specifications)

Absolutely no copying others’ works
Development Requirements
When start developing the exercise, follow the two requirements below:

Have to use the Oracle 12c, an object-relational database management system.
The system entry page must be located at http://undcemcs02.und.edu/~userid/513/1/ and all pages must be hosted by http://undcemcs02.und.edu/~userid/ .

Soft Due Date^† and Submission Methods
Due on or before Monday, October 01, 2018 in class and have completed the following tasks:

Turn in
- printouts of all source code: C/C++, (X)HTML, Java, JavaScript, JDBC, Perl, PHP, PL/SQL, Unix shell, SQL (especially create commands), and whatever languages^‡, and
- the password for displaying the source code online (only one password for all interfaces and all exercises).
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.

^†The purpose of exercises is to make you practice what you have learned, so no penalty will be applied if submitted after the due dates. However, you may lag behind if you are not able to submit them by the due dates.

^‡Note that you are allowed to use any languages and tools for this exercise, but the exams will focus on JDBC and Oracle technologies unless otherwise specified.

Objectives
Database-driven web sites are a must for companies nowadays. This exercise has students design and implement a simple database-driven online gamestore by using Oracle 12c, an object-relational database, from the ground up.

Requirements
The gamestore includes the following features:

The data of a game includes
- a unique ASIN (10 characters),
- a title,
- a list of developers, and
- a price.

The data of a developer includes
- a unique ID (assigned by the system automatically),
- a name, and
- a list of games developed by him or her.

The data of a customer includes
- a unique ID (assigned by the system automatically after signing up),
- a name, and
- an account including
  - a list of purchased games (no duplicate games),
  - the corresponding quantities of the purchased games (one quantity for each game), and
  - a total amount spent on the purchased games.

(Developers: 05%)
Developers need to create their accounts by entering their names before they could be entered as the developers of a game.

(System administrator: 30% total)
For the one and only one system administrator:
- (Entering games: 10%) Enter games one by one. User-friendliness will be heavily considered here; for example, the number of developers should not be fixed and the developers’ names should not be entered again and again.
- (Listing all data: 05%) List all hyperlinked game titles, developer names, and customer names.
- (Deleting the developers: 05%) Delete multiple developers at the same time. In addition, the corresponding game data has to be updated.
- (Updating the game prices: 05%) Update the prices of several games at the same time.
- (Adding a developer to a game: 05%) Add a developer to a game. No action is taken if the developer has already been there.

(Customers: 30% total)
For each customer:
- (Customer signing up, in, and out: 10%) Customers can sign up, in, and out.
- (Searching games: 05%) Search the games with multiple case-insensitive names; i.e., display a game if its developers include any one of the names entered. The query is a list of names separated by spaces. Extra spaces before, after, and among the names should be ignored. Display all games if the query is empty.
- (Ranking the search results: 10%) Rank the search results based on the number of names matched. Each search result includes (i) a checkbox, (ii) a hyperlinked title, and (iii) a textbox for a quantity to purchase the game.
- (Purchasing games: 05%) After searching, purchase the selected games at the same time and update the customer’s account accordingly. If the game has been purchased before, add the quantities.
(Generic functions: 15% total)
For both administrator and customers:
- (Showing game data: 05%) List all data of a specific game (including hyperlinked developer names) by clicking its hyperlinked title.
- (Showing developer data: 05%) List all data of a specific developer (including hyperlinked game titles) by clicking his or her hyperlinked name.
- (Showing customer data: 05%) List all data of a specific customer (including hyperlinked purchased game titles) by clicking his or her hyperlinked name. The system administrator can check all customers’ data, but a customer can only check his or her own data.

(Instructor’s requirements: 30% total)
Other than the above system requirements, the instructor has the following requirements:

(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. For example, it is considered not user-friendly if the system repeatedly asks users to enter their names/IDs/passwords.

(System reset: 05%) The system can be reset, which is to clear all data stored in the database and files, 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” on the system entry page.

(Security and plagiarism-proof: 15%) It is for the instructor to find any plagiarism. Each interface includes a button “Display source,” which is to list ALL the source code for implementing the functions of this interface. Only one password is for all exercises and interfaces. The system will be highly suspected if fail to implement this button. Use the following Perl script Check.pl as a template to implement this button and the associated Help button:

`~/public_html/cgi-bin/demo/encrypt/Check.pl`
Get a different public key from the server each time: Enter password: & interface: in plain text (During the demo, the instructor will ask you to change your password on the server.) Convert the password into ciphertext on the client side: (No server-side script is used in this function assuming the client does not own a server.) Send the ciphertext to the server (no plain text is sent except the interface value):
#!/usr/bin/perl use CGI; $query = new CGI; $act = $query->param('act'); $password = $query->param('password'); $interface = $query->url_param('interface'); if ( $act eq "Display source" ) { if ( $password eq "password" ) { print( "Content-type: text/plain\n\n" ); if ( $interface == 1 ) { system( "cat 1.cgi;echo '\n\n\n\n\n';cat 1.pl;echo '\n\n\n\n\n';cat 1.java" ); } elsif ( $interface == 2 ) { system( "cat 2.cgi;echo '\n\n\n\n\n';cat 2.pl;echo '\n\n\n\n\n';cat 2.java" ); } elsif ( $interface == 3 ) { system( "cat 3.cgi;echo '\n\n\n\n\n';cat 3.pl;echo '\n\n\n\n\n';cat 3.java" ); } elsif ( $interface == 4 ) { system( "cat 4.cgi;echo '\n\n\n\n\n';cat 4.pl;echo '\n\n\n\n\n';cat 4.java" ); } elsif ( $interface == 5 ) { system( "cat 5.cgi;echo '\n\n\n\n\n';cat 5.pl;echo '\n\n\n\n\n';cat 5.java" ); } elsif ( $interface == 6 ) { system( "cat 6.cgi;echo '\n\n\n\n\n';cat 6.pl;echo '\n\n\n\n\n';cat 6.java" ); } elsif ( $interface == 7 ) { system( "cat 7.cgi;echo '\n\n\n\n\n';cat 7.pl;echo '\n\n\n\n\n';cat 7.java" ); } else { print( "\n\n\n No such interface: $interface" ); } } else { print( "Content-type: text/html\n\n" ); print( "<center><font size='+2'><b>Wrong password: </b>" ); print( "<i>$password</i></font>" ); print( "<form><input type='button' value='Back' onclick='history.go(-1);return false;' /></center>" ); } } elsif ( $act eq "Help" ) { print ( "Content-type: text/html\n\n" ); system( "cat Help.html" ); } else { print( "Content-type: text/html\n\n" ); print( "<center><font size='+2'><b>No such option: </b>" ); print( "<i>$act</i></font></center>" ); }

~/public_html/cgi-bin/demo/encrypt/Check.pl

#!/usr/bin/perl
use CGI; 
$query     = new CGI;
$act       = $query->param('act');
$password  = $query->param('password');
$interface = $query->url_param('interface');

if ( $act eq "Display source" ) {
  if ( $password eq "password" ) {
    print( "Content-type: text/plain\n\n" );
    if ( $interface == 1 ) {
      system( "cat 1.cgi;echo '\n\n\n\n\n';cat 1.pl;echo '\n\n\n\n\n';cat 1.java" );
    }
    elsif ( $interface == 2 ) {
      system( "cat 2.cgi;echo '\n\n\n\n\n';cat 2.pl;echo '\n\n\n\n\n';cat 2.java" );
    }
    elsif ( $interface == 3 ) {
      system( "cat 3.cgi;echo '\n\n\n\n\n';cat 3.pl;echo '\n\n\n\n\n';cat 3.java" );
    }
    elsif ( $interface == 4 ) {
      system( "cat 4.cgi;echo '\n\n\n\n\n';cat 4.pl;echo '\n\n\n\n\n';cat 4.java" );
    }
    elsif ( $interface == 5 ) {
      system( "cat 5.cgi;echo '\n\n\n\n\n';cat 5.pl;echo '\n\n\n\n\n';cat 5.java" );
    }
    elsif ( $interface == 6 ) {
      system( "cat 6.cgi;echo '\n\n\n\n\n';cat 6.pl;echo '\n\n\n\n\n';cat 6.java" );
    }
    elsif ( $interface == 7 ) {
      system( "cat 7.cgi;echo '\n\n\n\n\n';cat 7.pl;echo '\n\n\n\n\n';cat 7.java" );
    }
    else {
      print( "\n\n\n     No such interface: $interface" );
    }
  }  
  else {
    print( "Content-type: text/html\n\n" );
    print( "<center><font size='+2'><b>Wrong password: </b>" );
    print( "<i>$password</i></font>" );
    print( "<form><input type='button' value='Back' onclick='history.go(-1);return false;' /></center>" );
  }
}
elsif ( $act eq "Help" ) {
  print ( "Content-type: text/html\n\n" );
  system( "cat Help.html" );
}
else {
  print( "Content-type: text/html\n\n" );
  print( "<center><font size='+2'><b>No such option: </b>" );
  print( "<i>$act</i></font></center>" );
}

Modify the password in the Line 09 to the password you pick.

An Example of Web Page Display
Note that this example is not related to this exercise. It is only to show how to display web interfaces.

Evaluations
The following features will be considered when grading:

Specifications:
- The instructor (or your assumed client) has given the exercise specifications as detailedly as possible. 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 specification, 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 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.
- The total weight of exercises is 35% of final grade, 15% for this exercise (ORDB), 10% for Exercise II (app-server database), and 10% for the Exercise III (embedded DB).
- Multiple browser tabs, browsers, or computers will be used to test the systems at the same time to make sure the multi-processing is working well.
- If not specified, no error checking is required; i.e., you may assume the input is always correct for that case. For example, the ASIN entered will always be a unique key of 10 characters.
- 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.
- Firefox 55 or above browser will be used to grade exercises. Note that Internet Explorer, Chrome, and Firefox are not compatible. That is your exercises may work on the IE or Chrome but not Firefox.
- The systems have to be active and the source-code printouts will be kept 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:
- An object-relational 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.
- The SQL DDL commands such as “create table” have to be submitted, where SQL is Structured Query Language and DDL is Data Definition Language.
- From the source code submitted, the database design and programs will be examined. Poor database design or uses will result in a lower grade.
- (-30%) if the object-relational database Oracle 12c is NOT used.
- (-15%) if any of the following Oracle 12c features is NOT used (i) collection types, (ii) user-defined types, and (iii) at least three non-trivial member methods using PL/SQL (like 15+ lines each method).
- (-10%) if the database design is NOT optimal.
- (-05%) if the SQL create commands of database implementation is NOT submitted.
- 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 customers 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.
- One way to build a complex web 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. Database design (E-R modeling or normalization),
  2. Database implementation (SQL),
  3. Interface building (HTML and CSS), and
  4. Button-by-button implementation (JDBC).
- The demonstration above uses several languages such as HTML, CSS, Unix shell, Perl, PHP, and Java. A good programmer is not limited by specific languages. Use the languages when they are the most appropriate for the need; for example, Perl is good at string processing, PHP is designed for web processing, and shell scripts are powerful.
- Many times, simplicity is the same as user-friendliness.
- Security is critical for all websites. Students practice a simple security method in this exercise. However, small or medium -size businesses do not usually implement their own secure payment schemes. Instead they use a third-party payment system such as PayPal or purchase software from company like Cayan and integrate it with their websites.
- The function of automatically sending emails is important for e-commerce systems, but will not be used here since companies complained our students sending out numerous mails because of faulty programs.