Project Assignment 4

Logistics and General Information

• Turning in your work: Unless otherwise specified, this lab assignment and all subsequent ones will be turned in electronically. We will use submit. Submit is accessed from your cats account. For information on using submit, enter "submit -m" from the console. Please keep in mind that the deadlines for submitting lab assignments are separate from the deadlines for written homework assignments. The following late policy applies to this and subsequent lab assignments:

  Lab assignment work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%. No lab assignment work will be accepted after the late deadline.

• PostgreSQL (lack of) backups: PostgreSQL is not backed up, so anything you need long-term should be saved in the cats file system. Some suggestions for general management of your database application are given below. CATS-Instructional Computing warns: "Although system backups are performed, YOUR STUDENTS MUST BE SURE TO PERFORM THEIR OWN BACKUPS, as I will not restore anything from my own backups except in case of emergency and/or complete system failure." `

• SQL on PostgreSQL: If you decide to test your solutions to the SQL homework assignment exercises using PostgreSQL, or experiment with running some SQL queries on your database, note that PostgreSQL's implementation of SQL may differ somewhat from the SQL standard as covered in the textbook and class. See PostgreSQL feature compatibility list from MySQL's crash-me program.

• PostgreSQL for CS180: Students wishing to use PostgreSQL must connect with their CATS accounts to 'db.ic.ucsc.edu'. Students must use ssh (both versions 1 and 2 will work), as telnet is not enabled. SSH is an encrypted (safer) method of connecting to a remote host, as opposed to telnet which transmits and receives data unencrypted. Free and legitimate SSH software is available for almost all computing platforms. Once connected to db.ic.ucsc.edu, students will have full access to their CATS home directories via AFS, much the same way as when logged onto teach.ic.ucsc.edu and hawking.ic.ucsc.edu.

The assignment

Being the expert that you are (you have taken CMPS180 after all!), you decide that you will use JDBC to handle all connections to the database from within your applications. Fortunately for you, someone has already written a short guide on how to use JDBC on db.ic.ucsc.edu. You may also want to check out the official JDBC documentation from the source itself.

The application can have a text-based interface, although extra points will be awarded if you program a GUI. On start-up, the application should request a user-id and password, or give the option of creating a new account. After the user is authenticated (by supplying a correct username/password or by creating a new account), all transactions to the database will apply to the given user only.

Once logged in, the user is given the following options:

• Search for songs by title. The search is not be exact but rather approximate, i.e., it returns songs that contain the given string, in upper or lower case. For example, a search on the string "dog" should return songs "My doggy ate my project assignment", "Walk the dog", and "Dog". For each matched song, the result includes the song title, the artist's name, the year of release, and the min and max prices for buying the song. Once the results are generated, the user has the option of navigating through the results, and purchasing songs. These two activities are described in more detail below.
• Manage user account. The user is allowed to view/update their information, such as e-mail address, name, address, password, and their registered credit cards. For the latter, the user is allowed to delete a credit card, update the information on an existing card, or insert a new card.

Navigating through search results. The user should be able to navigate through long lists of search results. If you build a text-based application, this means that the user should be able to look at the results in pages, and should be given the option of moving back and forth; if you build a GUI application, then the user should be able to scroll through the results. Of course, once the user is done browsing the results, he/she should be able to close the results view and perform another search.

Purchasing songs. The user can always select to buy a song from the list of results. In this case, the application should switch to a purchase screen, where the user is shown again the song he/she selected, and the available bitrates. Once a bitrate is selected, the user can pay with a pre-registered credit card. At any point in this process, the user should be allowed the option to back-out of the purchase and return to the list of results. If the purchase is completed, then the transaction should be recorded in the database, a notification should be presented on screen, and the user should be returned to the list of results.

In all cases, if an error occurs during the interaction with the application (or during the communication with the database), make sure to give to the user a meaningful error message.

Extra Credit. You will receive extra credit for each of the following features:

• Search for songs by artist name. This is similar to search by song title, except that the search is performed on the artist's name. The format of the results is the same and the user can navigate through the results and buy songs.
• Rate songs and artists. Given a result list of songs, the user can rate the song or artist. After selecting the song (or artist) for rating, the application presents the user with the possible rating options, and also provides the current rating that the user has assigned to the specific song or artist. Once the new rating is entered, the user is transferred to the list of results. As always, the user is given the option to back-out of the process and return to the list of results immediately, without altering anything in the database.
• List purchased songs. The user is given the option to list the songs that he/she has purchased. The result should contain the title of each song, the credit card used, the total amount paid, and the data of the transaction. The user must be able to navigate through a long list of recorded purchases.

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. You also will not receive full credit if you turn in your entire large data files (or large query results in later assignments) when we ask for small samples. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this part of the assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
app.zip	A zip file with all the source files for your application. The README file should include instructions on how to compile and run the java class files.

To submit these files use the syntax:

submit cmps180-np.w04 proj4-part1 README app.zip

or proj4-part1-late if your project part is late.