 |
Navigate
|
 |
|
|
|
Accounts
SQL Syntax Questions (for CS6604)
- How to obtain a UNIX account?
Fill the form http://www.nvc.cs.vt.edu/info/forms/unixacctform.html
and send to the address indicated on the form or drop it directly
in room 421. For individual applicant, form has to be signed by
your academic advisor or instructor as a proof of need for a UNIX
account. You account will usually be activated within a week after
you send your application form.
After obtaining a UNIX account,
you can remotely login to one of the following UNIX machines:
mercury.nvc.cs.vt.edu
neptune.nvc.cs.vt.edu
jupiter.nvc.cs.vt.edu
You
may also use Exceed from our NT lab located at room429 to connect
to the UNIX machines. If you never used Exceed before, ask NT
admin there for help.
- What are installed on our
UNIX system?
Compilers: gcc, g++, c++, cc, CC, perl,
g77, etc.
Databases: Oracle (on jupiter, mercury, neptune
and pluto), DB2 (on earth, jupiter, pluto and saturn), Informix
(on elara, mercury, neptune and thebe), mSQL (on neptune, pluto
and saturn), ObjectStore (on callisto, mercury and pluto).
Middleware products: Orbix/OrbixWeb, Visibroker,
Voyager, J2EE
Java Products: JDK 1.1 ~ 1.3, JSDK 2.0, JCE
2.0, JMF 2.0, XML parser, JSWDK 1.0.1, Java Bean box
Tools and other applications: Netscape, StarOffice
5.2, Acrobat Reader, CSIM package, Ispell, Latex, Latex2html,
etc.
You
can find most of the software under /pkg and/usr/local.
- How to create your own home
page on our UNIX system.
Login to UNIX system.
Create a folder called public_html under
your home directory
Put your web pages there. You have to have a file called index.html
acting as your main page, which has links to other pages.
Your home page can be accessed from Internet as
http://www.nvc.cs.vt.edu/~<your_username> .
- How to obtain a database
account?
Fill the form http://www.nvc.cs.vt.edu/info/forms/dbacctform.html
and send to the address indicated on the form or drop it directly
in room 421. For individual applicant, form has to be signed by
your academic advisor or instructor as a proof of need for a database
account. You only have to apply for Oracle and DB2 accounts. You
will be given the database name when your Oracle and DB2 accounts
are created.
Your
UNIX access automatically gives you the access to Informix and
mSQL databases. You can create your own database under Informix.
To save our resources, please don't create multiple databases
on the same host if it's not required.
- How to change your environment
settings?
Use setenv command to change environment
variables in current session. To keep the changes permanent, modify
.cshrc or.login file in your home directory. Here is a sample
of .cshrc file.
Only add those settings you need. For example, if you need to
use Oracle database in your course, copy and paste the settings
for Oracle database only to your .cshrc file. After making changes
to your .cshrc file., you have to either run "source .cshrc" on
command line to activate this settings in your current working
session or relogin.
General
Database Issues
- How
to access Oracle database?
Apply for an Oracle database account.
Change your .cshrc to include Oracle settings
according to question 5 in the first section.
Login to any machine you have Oracle account on.
Run sqlplus.
Enter your username and password.
- Where can I get some online
ORACLE documentation?
Oracle Technology Network provides access
to Oracle Product and Platform documentation. You should follow
the instructions there to create a member account for yourself
in order to access the online information.
- Dead connections to the Oracle
server?
If you encounter an error such as the following:
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT
specified
What this usually means is
that you are either performing a query, update, or insert on your
table in another connection.
You should "commit;" in all
the open connections you have.
If this doesn't fix your problem,
it could mean that you have a dead connection.
Dead
connections can result if you are running a form, it goes into
an infinite loop, you try to kill it and accidentally press CRTL-Z
instead of CTRL-C. You should run the UNIX command
"ps -ef | grep <your_user_name>" to see if
you've any suspicious back- ground processes running before logging
out of a workstation.
- How to access Oracle database
through java programs?
Here
is a simple program OracleTest.javato
create a Test table in database. Make sure you replace name and
pass with your own username and password in the line con =
DriverManager.getConnection(url, "name", "pass").
- Can I access Oracle database
through JDBC remotely?
Yes,
you can use your java program to access a Oracle database located
on another machine, even if the machine you are running program
from has no Oracle database installed. But you have to have the
driver classes somewhere accessible by your program (a good place
might be under your own home directory).
- Why my java program can't
find JDBC driver?
Check
your CLASSPATH to make sure you have included the driver classes
(classes111.zip) in it. If the problem still persists, you must
be running your program from a machine which has no Oracle installed.
In that case, you can copy classes111.zip from to anywhere under
your home directory and update your CLASSPATH (change CLASSPATH
in .cshrc as well).
- How to access Informix database?
Change
your .cshrc to include Informix settings according to question
5 in the first section.
Login to any host which has Informix installed.
Run isql on command line.
Select Database from the main menu.
Select a your database from the list and press enter.
Select Create if you don't have one yet.
Go back to the main menu and select Table to
create, modify or drop tables from the database.
- How to access Informix database
through JDBC?
Here
are a couple of java programs, CreateInformixDB.javaand
CreateInformixTable.java
, to create informix database and table on elara. You just have
to make minor changes to create your own database and tables and
execute other SQL statements. Remember to replace <your_unix_username>
and <your_unix_password> in the programs with your
own unix username and password.
- How to access DB2 database?
Apply for a DB2 database account.
Change your .cshrc to include DB2 settings according to question
5 in the first section.
Login to any machine you have DB2 account on.
Run db2.
Type connect to <dbname> and enter
on db2 commandline to connect to database <dbname>. If your
database name is cs6604,type connect to cs6604.
Go
to http://www-4.ibm.com/software/data/db2/ for more information
on DB2.
- How
to access DB2 database through JDBC?
Here
is is a sample java program, DB2Test.java,
to build connection to mSQL database. Replace <host>, <username>
and <password> in the program with your own information.
- How
to access mSQL database?
- How
to access mSQL database through JDBC?
Here is a sample java program,
mSQLTest.java,
to build connection to mSQL database. Replace <host>, <username>
and <password> in the program with your own information.
SQL
Syntax Questions (for CS6604)
How can foreign keys be specified
when two tables reference each other?
You cannot reference a column of a
table that hasn't yet been defined. This presents a problem when
two tables reference each other as in the case of the EMPLOYEE table
and the DEPARTMENT table(see page 148of Elmasri). An EMPLOYEE must
work for a DEPARTMENT. A Department must have a manager who is an
EMPLOYEE.
One solution is to create the DEPARTMENT
table first without referencing the EMPLOYEE table. Then, create
the EMPLOYEE table and have the DNO attribute reference DEPARTMENT(DNUMBER).
After both tables have been successfully created, use the following
ALTER command to add the constraint:
alter table DEPARTMENT add (
constraint mgrssn_fk foreign key (mgrssn) references EMPLOYEE(ssn)
initially deferred deferrable
);
mgrssn_fk happens to be my name for
the constraint. You can name it whatever you want.
Please
note that a constraint is not deferrable by default.If you need
to defer a constraint (so that you can insert a group of rows without
worrying about the ordering), then you must specify the constraint
to be deferrable when the constraint is first defined.
How to specify a constraint to be
deferrable or not deferrable?
You can specify table and column constraints
as DEFERRABLE or NOT DEFERRABLE. DEFERRABLE means that the constraint
will not be checked until the transaction is committed. The default
is NOT DEFERRABLE.
If you specify DEFERRABLE, you can
also specify the constraint's initial state as INITIALLY DEFERRED
and thereby start the transaction in DEFERRED mode. Or you can specify
a DEFERRABLE constraint's initial state as INITIALLY IMMEDIATE and
start the transaction in NOT DEFERRED mode.
Example I: The following statement
creates table GAMES with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint
check on the Scores column:
CREATE TABLE games (scores NUMBER
CHECK (scores >= 0));
Example II: To define a unique constraint
on a column as INITIALLY DEFERRED DEFERRABLE, issue the following
statement:
CREATE
TABLE orders
(ord_num
NUMBER CONSTRAINT unq_num UNIQUE (ord_num)
INITIALLY DEFERRED DEFERRABLE);
How to set a constraint to be deferred
during the insertion of a group of records that reference one another?
The SET CONSTRAINTS statement makes
constraints either DEFERRED or IMMEDIATE for a particular transaction
(following the ANSISQL92 standards in both syntax and semantics).
You can use this statement to set the mode for a list of constraint
names or for ALL constraints.
The SET CONSTRAINTS mode lasts for
the duration of the transaction or until another SET CONSTRAINTS
statement resets the mode.
SET CONSTRAINTS ... IMMEDIATE causes
the specified constraints to be checked immediately on execution
of each constrained statement. Oracle first checks any constraints
that were deferred earlier in the transaction and then continues
immediately checking constraints of any further statements in that
transaction (as long as all the checked constraints are consistent
and no other SET CONSTRAINTS statement is issued). If any constraint
fails the check, an error is signaled; at that point, a COMMIT would
cause thewhole transaction to rollback.
Making constraints immediate at the
end of a transaction is a way of checking whether COMMIT can succeed.
You can avoid unexpected rollbacks by setting constraints to IMMEDIATE
as the last statement in a transaction.If any constraint fails the
check, you can then correct the error before committingthe transaction.
|
