Getting Started
Download CS320_Lab06.zip. Import it into your Eclipse workspace (File→Import…→General→Existing projects into workspace→Archive File). You will see a project called CS320_Lab06 in the Package Explorer. (You will also need to have the CS320_Derby project in your workspace, which should already be there from Lab 4.)
Start by creating test.db, which is the books database. Execute the DerbyDatabase class as a Java application: you should see the following output:
Creating tables...
Loading initial data...
Success!
If you refresh your CS320_Lab06 project, you should see the test.db directory.
I HIGHLY encourage you to work on this lab as a team. It contains much functionality that you are going to want to use for your project. Please “explore” the entire application to discover how to initialize a database from CSV files, as well as how to create a “fake” database from the same CSV files from which you will initialize your SQL database.
If you do choose to work as a team, you MUST truly work on the lab in a collaborative fashion. You MUST also indicate with comments at the top of your submission which members of your team contributed to the solution, and the percentage contribution of each team member, including yourself. Make sure that the percentage contributions you assign add up to 100%. Each contributing team member must then submit the same solution to Marmoset, with the exception that each member individually assesses the relative effort of all contributing members. Your individual grade on the lab will be based on the quality of your team’s solution, multiplied by your relative effort.
NOTE: You will each be individually responsible for being able to construct SQL queries, to establish a connection between a Java program and a SQL database, and to write SQL queries embedded in Java code. This WILL come up on the exam.
Task
In the lab skeleton you will find a program called TitleQuery which demonstrates using an ORM interface to find all books that have the title entered by the user (along with the author information).
Your task is very similar to Lab 5, except that rather than directly executing database queries/statements, you will add methods to the IDatabase interface and implement them in FakeDatabase and DerbyDatabase.
Tasks:
Using TitleQuery as a model, write your own programs (separate classes with main() methods) to do the following:
-
Find all books written by the author whose last name is specified by the user. Return the books in the same form as the TitleQuery program.
-
Given the full (first and last) name of an author, a title, an ISBN, and a publish year, insert the book into the database. The program should add a new tuple to the authors table if the author doesn’t already exist. If the author does exist already, the program should use that author’s author_id. Use the SQL insert statement to insert the new tuple(s). NOTE: This is the same set of SQL queries as the the third problem in Lab05. You are welcome to reuse your code for that solution - in fact, if you are working as a team to solve and submit this lab, you are welcome to use the code from the teammate that had the best solution.
Hints
For the first task, add the following method to IDatabase:
public List<Pair<Author, Book>> findAuthorAndBookByAuthorLastName(String lastname);
Implement it in FakeDatabase and DerbyDatabase. Start by implementing the method in FakeDatabase (just have the method in DerbyDatabase throw an UnsupportedOperationException.)
For the second task, do a query to see if the author exists. If it doesn’t, insert the new author into the authors table. (Note: you will want to allow the database to automatically assign an author_id). Then, retrieve the author_id so that you can insert a new tuple into the books relation (again, the database will automatically assign a book_id). Note that the entire operation should be executed as part of a single transaction.
Submitting
When you are done, submit the lab to the Marmoset server using the method below.
From a web browser
Save your project (CS320_lab06_username) to a zip file by right-clicking it and choosing
Export…→Archive File
Upload the saved zip file to the Marmoset server as lab06. The server URL is
