CSE 532 Theory of Database System
Fall 2005 TA/Project Page
How
to contact me
Email: zgao (with the domain
: @cs.sunysb.edu)
Office Hours: Tu/Th 8:30am
-- 9:30 am Rm 1212(Applied Logic Lab), CS Building
Announcements:
-
[Posted
on Oct. 14] Detail
for connection to the databases server using JDBC from outside the department
and from inside the dept/gradlabs is polished and posted again. Please check
the detail in our Project
section carefully.
- [Posted
on Oct. 3] The
sample data
for project-1
is now posted.
Please read the instruction thoroughly and carefully before implementing the
project. Do NOT use the format of the sample data as your own schema. You
are required to come up with your own schema, and you are supposed not to
ask me how to design the schema. :-)
-
[Posted
on Sep. 25] The description for project-1
is now posted. Please read it thoroughly before implementing the project.
Infomation for the project, such as Oracle connection and Oracle SQL,
can be found here.
-
[Posted
on Sep. 11] Statistic about the Quiz(100/100) on Wednesday, Sep. 7th: The
grading has been finished. If you are interested, here is the data: average/highest/lowest
is 84.70/99/43. Totally 67 students took this quiz.
Project:
-
[Posted
on Oct. 23]
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 ......".
-
[Posted
on Oct. 2]
The
sample data
for project-1
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...:-)
- [Posted
on Sep. 25]
how
to do recursive queries in Oracle. Here is one solution(from
Hui Wan). 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.
- [Posted
on Sep. 25]
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)
- [Posted
on Sep. 25]
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)
-
[Posted
on Sep. 25]
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.
-
[Posted
on Sep. 25] 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.
-
[Posted
on Sep. 25] The description for
project-1 is now posted. Please read it thoroughly before implementing
the project.
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 may be 2006 version
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 project2.
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 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 eXist-1.0b2.jar
is recommended. You can choose any stable version you want if you can use it
to finish project2. After you download it, simply click on the jar file icon,
or run it with java –jar eXist-1.0b2.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.)
Frequently
Asked Questions:
-
[Posted
on Oct. 17] Question1 : I have a question about problem
5 (Find the median ranking such that the # of papers above = # of papers
below): Lets say I have the following rankings:
Paper_id Ranking
1
1
2 1
3 2
4 2
5 3
6 3
7 3
What would be the median?
Answer: According
to the math definition, 1/2 of the population will have values less than
or equal to the median and 1/2 of the population will have values equal
to or greater than the median, so the median in your example will be 2.
Consider ranking 2 of paper #4, there are 3 papers with higher or equal
ranking with it and also 3 papers with lower or equal ranking with it.
Question2 : what if its this one?
Paper_id Ranking
1
1
2 1
3 2
4 2
5 2
6 3
7
3
8
4
What
would be the median?
Answer: then
the median ranking will be 2, since there are 4 papers whose ranking is
higher than or equal to ranking 2, and also 4 papers whose ranking is lower
than or equal to ranking 2.
-
[Posted
on Oct.5] Question : I was wondering if I need to
ask the CS admin to create an account for me for Oracle DB connection.
Answer: 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.
-
[Posted
on Oct. 5] Question: In
the project description document, both Authors and Committe members are
specified to have an attribute called "Affiliation". Can you tell
me the semantics of this attribute. What information is it supposed to hold?
Answer: For example, look at this paper infomation as given in the sample
data,
paper 001, "Propositional
argumentation and causal reasoning"
BY Alexander Bochman (HAIT)
Reviewers: Ivan Bratko (JSI), Eugene Freuder(UCC), Marco Gori(USI)
Evaluations: {(3,2),(4,3),(3,3)}
Alexander Bochman is the
author and his affiliation is HAIT(which stands for Holon Academic Institute
of Technology, but you do not need to know this for our project). In this
project, you can simply use a string HAIT as his affiliation attribute value.
Useful
links:(a free sign up required for some links below)