CSE 532 Theory of Database System
Spring 2006 TA/Project Page

     How to contact me
         Email: zgao (with the domain : @cs.sunysb.edu)
         Office Hours: Tu/Th 4pm -- 5pm Rm 2110, CS Building

Announcements
Projects
Project 1 description
Sample Data for Project 1
Sample Solution to Project 1
Project 2 description
Project 3 description (deadline extended to April 26)
XMLSpy (an XML development environment for project 3)
eXist (an XML database that supports XQuery for project 3)
Any questions? Check out FAQ or Contact me
Useful Links
Our Course Webpage

Announcements:

Here is a sample solution for project 1. Enjoy it.

[Posted on Feb. 1] About the quiz(100/100) on Monday, Jan. 30th: The grading has been finished. If you are interested, here is the a sample solution. Please do not hesitate to email me if you have any question about it.

[Posted on Jan. 30] Hello, I am the TA of CSE 532 in Spring 2006. Nice to meet you all! The best way to catch me is to send me an email: zgao at cs dot sunysb dot edu (please replace at with @ and dot with .).

XMLSpy -- an XML development environment:

XMLSpy can be downloaded from http://www.altova.com/. You can choose to download a Professional-Edition, Home-Edition or even Enterprise-Edition (Note that these links are pointing to 2005 version. There are also 2006 versions that you can have a try :-)). Home Edition is free, and it is enough for our project. For other editions, new customers can request a free 30-day evaluation key-code from within the application. You can choose any version if you can use it to finish project-3.

In this project, you can use XMLSpy to:

Write XML schema: There are very convenient graphic tool in XMLSpy.
Write XML file.
Validate XML file according to schema.

Here are some useful documents: XMLSpy Tutorial (2005 version) in chm format and pdf format, XMLSpy user reference. These documents will be in your XMLSpy installation directory after installing it. You may use them for reference.

eXist -- an XML database that supports XQuery and other things:

Information for eXist can be found here: http://exist.sourceforge.net . And there is a Quick Start Guide.

1. Download and install: If you want to download it and install it on your own computer, stable version of installer is recommended. You can choose any stable version you want if you can use it to finish project-3. After you download it, simply click on the jar file icon, or run it with java –jar eXist-1.0b2.jar (or the snapshot version # if you choose to use, such as java –jar eXist-snapshot-20060316.jar). After installation, you should be able to easily access all help documentations on your computer. In graduate lab 1239, eXist can also be installed in your H drive. Remember to make sure that you have enough space left before installation.

2. Load XML files and execute Xqueries: have a look at QuickStartGuide and XQueryDocs.Notice that eXist doesn’t support query on document fragments constructed within the XQuery, i.e. a query like the following will fail:

let $x := <test>Test</test> return $x/test.

You have to store the fragment into the database first. Look at ManipulatingDatabaseContents for example. You can use the provided xmldb extension functions for that.

3. Execute Xqueries from java: You can read 3. Writing Java Applications with the XML:DB API in Developer's Guide. Here is an example for querying the database. make sure the query path in your xquery is correct. When running your program, add these jar files to your classpath: ~eXist/exist.jar, and all jar files in ~eXist/lib/core/. (Suppose ~eXist is your eXist installation directory.)

Project:

The description for project-3 is now posted. Please read it thoroughly before implementing the project.

In order to connect to oraserv using JDBC, you must add the two driver ojdbc14.jar and orai18n.jar to your classpath (use set CLASSPATH in the DOS command prompt), which was already mentioned before on this project page. If you are working in CS Graduate Lab 1239, these two drivers can be find in C:\Oraclient\ojdbc14.jar and C:\Oraclient\orai18n.jar. If you are working outside CS department, as mentioned before on this project page, you need to download a client, which contains both the required files of ojdbc14.jar and orail8n.jar. Without these two drivers you will get errors when trying connecting. If you are using Netbeans, you can simply check(or add) the drives using Project Panel -> Library (->add JAR), or check Project Property -> Library -> compile, and if ojdbc14.jar and orai18n.jar are not included in the library, you must add them into the libray. Also, if you are using the sample file for JDBC-thin, make sure that you set correctly your own account and password, which were given in class, otherwise you will get a message of "invalid username/password; logon denied". If the connection is successful, you should see the message of "JDBC driver version is ......".

All students in our CSE532 class do not need to register the databases that you received your account/pwd in the class. The database is already ready for your use. You will get errors when you try to register the database. The database registration process is only for the undergraduates.

Connecting to the Oracle Server oraserv using JDBC from insider the dept/gradlab: only Oracle Instant Client is available on the machines which is JDBC-THIN compliant. If anyone tries running their java programs using JDBC-OCI then it will fail to connect to the database.

Conecting to the Oracle server using JDBC from outside the CS department:

Whether you use JDBC thin client or OCI (requires the Oracle-9i thick client) a copy of the client is necessary when connecting from home. If you want to use the 9i thick client, you may download it from here. If you want to use JDBC thin client, you may download the InstantClient for your specific OS from here, and make sure that you download both the Basic-Package and JDBC-supplement-Package(note that they are for Windows, select proper versions for your OS). You will find the required drivers of ojdbc14.jar and orai18n.jar in these packages. Note that there downloads might require a free registration on oracle's technet.

Here is a copy of sample program which have the connection information to the oraserv. Choose the one you are using. Sample program for JDBC-THIN Sample program for JDBC-OCI make sure that you replace your own username and password when necessary.

Here is a checklist of all CLASSPATH issues that need to be addressed before running the JDBC program.

1. Use Windows Explorer to see if Oracle's client libraries are available. If you are working in graduate lab 1239, they should be installed in a directory c:\OracleInstantClient, and you should be able to see 2 driver .jar files called "Ojdbc14.jar" and "orai18n.jar". These are your required drivers for connecting. If you are not working in graduate lab 1239 you need to download these two drives following the above direction.

2. Open the DOS command prompt. This is done by typing on the Windows Start Menu -> Run-> and in the text box "cmd". This opens up the command prompt.

3. Type SET command from the command prompt and check if CLASSPATH is set to the following:
CLASSPATH =.;c:\Oraclient\ojdbc14.jar;c:\Oraclient\orai18n.jar

4. If not, type set CLASSPATH = .;c:\Oraclient\ojdbc14.jar;c:\Oraclient\orai18n.jar. Remember the "." in the classpath is very important. It directs you to search in the parent directory. So your program might fail to execute if it is not set in the classpath.

Also remember if you close the window where you have set the classpath variable you will need to set it again if you want to run your code without errors.

Please check above for the JDBC connecting info.

Also, here are very useful links to turn to when you have some problem with JDBC (a free sign up may be required for first time use of these online resource) :

Oracle JDBC FAQ

Oracle9i JDBC Developer's Guide and Reference

The sample data for project-2 is now posted. Note that the sample data is descriptive and not schematic. You are required to come up with your own schema, and you are supposed not to ask me how to design the schema. :-) Do remember that your database should include at least all the sample data, and do not need to use the format of sample data file as the schema of your database. The sample data file only provides rough data. It might not be a good idea to use it as the schema for this project...:-)

how to do recursive queries in Oracle. Here is one solution.

Example for relational database: table A(person, friend) have tuples (x,y),(y,z),(z,x). Query x's friends and his friends' friends,...so on.

For those who use Oracle 10g : you should use the NOCYCLE option in CONNECT BY clause. Thus one hierachical query can do the job.

For those who use Oracle 9i : Since Oracle 9i doesn't support the NOCYCLE option in CONNECT BY clause, we have to avoid the loop problem by execute the hierachical query on a "tree-like" or "loopless" relation. One possible solution is that, we delete the tuple (z,x) from table A and get another table B which has only (x,y), (y,z) in it.

Now the problem comes to how we know which tuple brings loop. We take the structure as a directed graph with persons as nodes, x has friend y means an edge from x to y. And what we need to do for the recursive query is to start from one person, and get a connected tree so that on the tree, we can execute the hierachical query without loop.

Suppose we start with x, we use another table Level to record distance from x. x's level is 1, and since there is (x, y), y's level is 2, and similarly z's level is 3. Check levels of tuples, we can find that for tuple (z, x), level of the former is higher than the latter's. So this tuple prevent the structure from being a tree. And since x is already there in lower-level layer, we do not need this (z,x) for the query.

This can be done using SQL, but JDBC can do it much more easily. To be fair to whoever use Oracle9i or Oracle10g, the loopless table can be implemented using JDBC. But A HIERACHICAL QUERY IN PURE SQL IS REQUIRED AT THE END TO GIVE OUT ANSWER.

***Note that YOU ARE NOT EXPECTED TO DELETE TUPLES MANUALLY. AND ALL OTHER QUERIES MUST BE IMPLEMENTED IN PURE SQL.

What’s new or special with Oracle SQL? Check Oracle9i SQL Reference. Some other new Object-Relational features, such as SQL Type Inheritance, Object View hierarchies, Type Evolution, etc. please check Application Developer's Guide - Object-Relational Features. (a free sign up required for first time use of these online resource)

When doing your project, if you encountered any error message which you do not understand, you can try to look it up in the Oracle Error messages.(a free sign up required)

About Oracle account and connection problems: Whenever you have any problems with you Oracle account or connection to the database, please send email directly to dbadmin@cs.sunysb.edu, with the time when the problem occurred, the machine you used, and the error message you got, along with your username and password information.

You should have got the account and password directly from Professor Kifer. If you have not got one, you might have missed last week's database class, when professor Kifer gave the account/pwd to every student in the classroom. I do not have any account/pwd by hand and I think professor Kifer is holding all of them. You might need to contact him directly by email: kifer@cs.sunysb.edu, or you might wait to next class to ask him.

Instructions for connecting to Oracle from inside the Department can be found here. If you want to connect to the Oracle Server from Outside the Department, please check here.