This first programming assignment, Assignment #2, is due Tuesday April 16th, and consists of designing and building your own simple database and then writing SQLJ code (Java) to work with it.

Building your own database

In all of this work you may find useful links on our database resources page.

Using the list below, keyed to numbers I handed out in class, you are to go to Snell Library and find a few recent issues of the journal listed (or to any other library that has the journal, or to an online copy that NU or you subscribes to). I personally looked over the recent issues of these journals, second floor of Snell Library at the front of the building, so they're really there. If you didn't get a number from me in class, email me right away and I'll give you one.

1. Communications of the ACM
2. Computer
3. Landscape Architecture
4. Nature
5. ACM Transactions on Database Systems
6. Transplantation
7. Newspaper Research Journal
8. Perception
9. IEEE Multimedia
10. ACM Computing Surveys
11. Water Resources Research
12. Proceedings of the National Academy of Sciences (USA)
13. ACM Transactions on Computer-Human Interaction
14. IEEE Internet Computing
15. SIGMOD Record
16. Science
17. Human-Computer Interaction
18. Computer Networks
19. College Student Journal
20. ACM Transactions on Information Systems

Contents of your database

Your goal is to build a database of ten articles. You can shorten and simplify the article author list and the article titles to save yourself typing. For your database to be decently designed, you'll need to use at least two tables. The CAP database will give you some ideas. We'll be going into this in a lot more detail later, but here's a website for those curious about design.

At a minimum, your database should contain:

• journal name
• issue number or issue date
• pages of article
• authors (multiple authors for single papers, typically)
• article title
• year
• rating of article such as good, so-so, poor

You should use actual articles you find in the journal assigned to you. You may shorten the titles of the articles to save typing. You may reduce the number of authors to two. (Your DB must contain some articles with more than one author.) You must make two other radical changes which will make the DB more "interesting":

• Change at least one authors of two of the papers so that they become identical to force some fields to have the same contents, but they'll still differ in title, pages, etc.
• In the same way, give two of the papers identical titles.

You should use at least two tables in your design. The details are up to you.

Defining and loading your DB

To give you experience with a number of aspects of database work, do the following:

1. Define/create your tables in a .sql file that you run within sqlplus by issuing a trivial command, e.g., @defineit, or whatever you call your file.
2. Populate your database by building a data file and using sqlldr to load the data.

You can model your table definitions after examples in your textbook and in the file ~futrelle/3315sp02/toload.sql or look at simple examples such as this item.

The SQLJ program you are to write

Start by writing a very simple access program as well as having commands that update the rating field of an article. Then expand your code to include two different iterator uses, one to "dump" the entire DB and the other to list all the authors. You can model your code after the sources FundamentalExample1.sqlj and FundamentalExample2.sqlj, both in ~futrelle/3315sp02/. I already mentioned the classpath I used: $ORACLE_HOME/jdbc/lib/classes12.zip:$ORACLE_HOME/jdbc/lib/jndi.zip:$ORACLE_HOME/jdbc/lib/nls_charset11.zip:$ORACLE_HOME/sqlj/lib/translator.zip:. where $ORACLE_HOME on our unix systems evaluates to /arch/com/packages/ora8

Define and use one simple PL/SQL function in your code that updates the rating of a chosen article.

What to hand in and how to hand it in

You should run a script to demonstrate how you set up your DB and what commands you used and what your program produced as output. You should also hand in your sql file, your dat file and your sqlj source code. You can do this by emailing the files as attachments or placing them in a directory that I can access. You must also write a short overview file that explains what you did and include that file also. Of course your grade will be much improved by giving your tables and fields meaningful names and by properly commenting your sqlj source code.

Extra credit: Most DB programs are driven by user input of some type. Add a command line input facility that allows a user to type in some very simple input that will then run a function you define to access the DB and return input to the user. An example would be
A Jones
which would return all articles with Author "Jones".

Go to COM3315 home page (Gateway)

Return to Prof. Futrelle's home page