Programming Exercise I:
A Single-User Online Bookstore Using a Database
(Industry-Level, Second-to-None Comprehensive Specifications)
Development Requirements
When start developing the exercise, follow the three requirements below:
- Have to use the Oracle 12c, the most popular enterprise database management system.
- The exercise has to be Internet-enabled (a trend of current IT systems).
- The system entry page must be located at
http://undcemcs02.und.edu/~user.id/520/1/
and all pages must be hosted by http://undcemcs02.und.edu/~user.id/
.
- The systems have to be active even after being graded until the end of this semester.
They will be re-checked for plagiarism from time to time.
Soft Due Date† and Submission Methods
Due on or before Thursday, September 28, 2023.
Send an email to the instructor at
wenchen@cs.und.edu including
- the password for displaying the source code online (only one password for all exercises and interfaces),
- the SQL
create
commands used, and
- 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 instructor’s Zoom ID is https://und.zoom.us/j/2489867333 .
†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 single-user, database-driven, online bookstore by using Oracle 12c from the ground up.
Requirements
There is one and only one user (system administrator) of this bookstore,
and the bookstore includes the following features:
|
|
|
- The data of an author includes
- a unique ID assigned by the system automatically when added,
- a name, and
- royalties (=
Σbook_price×royalty_rate/author_#
) such as $160.99.
- The data of a book includes
- a unique ISBN (10 characters),
- a title,
- a price,
- a royalty rate like 20%, and
- a list of authors.
- (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.
- (Input: 20% total)
Perform the following input functions:
- (Entering authors: 05%)
Enter authors one by one in the very beginning.
- (Entering books: 15%)
(User-friendliness emphasized)
After entering all authors, enter books one by one by selecting authors instead of typing their names or IDs.
- (Output: 20% total)
Perform the following output functions:
- (Listing all data: 10%)
Display all hyperlinked author names and book titles.
- (Listing all data of an author: 05%)
After clicking on a hyperlinked author name, display all data of the author including an ID, a name, a list of titles of the books authored by her/him, and the royalties paid (Σbook_price×royalty_rate/author_#
).
- (Listing all data of a book: 05%)
After clicking on a hyperlinked book title, display all data of the book including an ISBN, a title, a price, a royalty rate, and a list of the names of its authors.
- (Management: 45% total)
Perform the following functions to the books: searching, deletion, and updating:
- (Searching for the books: 15%)
Search for the books with multiple case-insensitive words; i.e., display a book if its author names include any one of the words entered.
The query is a list of words separated by spaces.
Extra spaces before, after, and among the words should be ignored.
Display all books if the query is empty.
After searching,
- (Deleting several books from the bookstore at the same time: 15%)
Delete several books from the bookstore at the same time, and update the affected authors’ royalties accordingly.
- (Updating the royalty rate of a book: 15%)
Update the royalty rate of a book, and update the affected authors’ royalties accordingly.
- (Instructor’s requirements: 10% total)
Other than the above system requirements, the instructor has the following requirements:
- (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.
For example, it is considered not user-friendly if the system repeatedly asks users to enter their names/IDs/passwords.
- (Plagiarism checking: 05%)
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/520/1/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 on='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 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 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 40% of the final grade, 12% for this exercise (single user), 12% for Exercise II (multiple users), and 16% for Exercise III (Android connected to server-side 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 ISBN 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.
- The newest Firefox browser will be used to grade exercises.
Note that Internet Explorer, Edge, Chrome, and Firefox are not compatible.
That is your exercises may work on the IE, Edge, or Chrome but not Firefox.
- The instructor will inform you the exercise evaluations by emails after grading.
- Databases:
- A 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 displayed, the database design and programs will be examined.
Poor database design or uses will result in a lower grade.
- (-05%) if the database design is NOT optimal.
- (-05%) if the SQL
create
commands of database implementation are 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 authors are enrolled at the same time, an ID may be assigned to different authors 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 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
- Examining the specifications very carefully,
- Designing the databases (E-R modeling or normalization),
- Implementing the databases (SQL),
- Building the interfaces (HTML, CSS, and JavaScript),
- Implementing the system button by button (JDBC), and
- Testing the exercise thoroughly.
- 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 most appropriate for the need; for example, Perl is good at string processing, PHP is designed for web processing, and shell scripts are powerful.
- According to a study, students in computer courses learn much more by building large-scale exercises instead of many small-scale test programs, which give fragmented knowledge contrary to solid understanding of the system.
- Web user interface is a trend for software systems compared to native user interface.
- Web, mobile, and database programming is a must for IT developers, and the only effective way to learn software development is practicing, instead of studying concepts or writing some testing programs.
No pain, no gain 😂
- Remote work is a trend for IT workers.
This course also allows you to learn how to do it by using the VPN (virtual private networks) to connect to our Linux server
undcemcs02.und.edu
, and having the exercises set up at the server and be accessed from the clients.
- CGI (Common Gateway Interface) is used to connect the Web to JDBC.
Though CGI is an old technology, it is simple, so students can jumpstart the exercise implementation as soon as possible.
If students are interested in web programming, check CSCI 457 Electronic and Mobile Commerce Systems.
- Many times, simplicity is the same as user-friendliness.
- The function of automatically sending emails or text messages is important for e/m-commerce systems, but will not be used here since companies complained our students sending out numerous mails or text messages because of faulty programs.
- Security is critical for all websites.
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 Global Payments Integrated and integrate it with their websites.
For an encryption introduction, check Public-key cryptography.