Readme

The contents of this file is a description of the files submitted 
for JOHN JOACHIM's Assignment #4 for CSCI-540 (Spring 2000).

The other files submitted for this assignment are the following:

541_4_ddl_1
541_4_ddl_2
541_jdbc.java
java_rmi.html

----- 541_4_ddl_1 ----
This file contains the DDL and all data entered into the STUDENT 
and CLASS tables, as well as all foreign key designations on all 
four tables employed in this assignment.  This file also includes 
the code to set up a database link between the two account.This 
file was loaded and run on my personal Oracle8 account.

------ 541_4_ddl_2 ----
This file contains the DLL and all data entered into the ENROLLED 
and INSTRUCTOR tables.  This file was loaded and run on the cs541 
Oracle account (it was actually run BEFORE 541_4_ddl_1).

------- Java_rmi.html ---
This file is the build log for the assignment.

------ 541_jdbc.java ---
This file is the actual assignment program.  It has been written 
to run as a stand-alone application.  This applet does the 
following tasks:

1)	Connect to the IUPUI DS9 server;
2)	Load the JDBC driver;
3)	User has a choice of (eight) buttons to press - each button 
        initiates one of the (eight) queries from Assignment #2 
        (see actual queries below)
4)	Show results of the query in output window
5)	Run another query if needed by user;
6)	Show any exception errors that arise
7)    Close applet if requested by user

All code has been pre-loaded and already precompiled -- it is 
ready to run.

WARNING: This applet was designed to access the driver on IUPUI's 
DS9 server ONLY !!  Porting this application to other systems 
(including TELNET sessions into DS9) will not work.  When the 
applet opens, a connection to the DS9 server and JDBC driver is 
made (see source code for actual URL).  Moreover, this applet was 
written to access two Oracle accounts (my own, and the CS541 class 
account).  Other accounts may be accessed with this applet, as long 
as the source code will need to be modified approrpiately.

-------------------------------------------------------------------
To run this stand-alone Java application, start a session in DS9.  
At the start prompt, type the following:

	java jdbc_541


Once the application opens, press one of the buttons numbers "1" 
through "8".  

Following are the SEVEN queries this applet can run (denoted by the 
respective number on the applet):

1. Find the names of all CS Majors (Major = "CS") who are enrolled 
   in the course "CS541". 

select Name from Student ,Enrolled 
      where Student.Major ='CS' and 
            Student.Snum = Enrolled.Snum and 
            Enrolled.Classname ='CS541'; 


2. Find the names of all CS Majors (Major = "CS") who are enrolled in 
   the course "CS503" and are senior (Level="SR"). 

select Name from Student, Enrolled 
            where Student.Major='CS' and 
                    Student.Snum=Enrolled.Snum and 
                    Enrolled.Classname='CS503' and 
                    Student.Levels='SR'; 
3. Find the names of all classes that either meet in room SL 210 or are 
   taught by  Proffesor King. 
 
select Class.Name from Class, Instructor 
         where Class.iID = Instructor.iID and 
            (Class.Room = 'SL 210' or  Instructor.Name ='King'); 


4.  Find the names of all pairs of students who are enrolled in some 
    class together.

select Name from Student 
      where Snum in (select Snum from Enrolled 
           where Classname in (select Classname from Enrolled 
                  group by Classname having count(*)>1)); 


5.  Find the names of all pairs of students who are enrolled in two 
    classes that meet at the same time (including pairs of students 
    who are enrolled in the same class). 
 
select Name from Student 
    where Snum in (select Snum from Enrolled 
        where Classname in (select Name from Class 
             where Time in (select Time from Class 
             group by Time having count(*)>1))); 


6.   Find the names of Instructors who teach in every room in which some 
     class is taught in the time period "MW 5:15-7:00 Pm".  

select Instructor.Name from Instructor, Class 
      where Instructor.iID=Class.iID and 
                Class.Time='MW 5:15-7:00 pm'; 


7.   Find the name(s) of the student(s) who is enrolled in all classes. 
 
select Student.name from student 
         where Student.Snum in ( select Enrolled.Snum from Enrolled 
             group by Enrolled.Snum 
                    having count(*) = (select count(*)   from Class) 	  ); 


8.   Find the name(s) of all student(s) who are not enrolled in any 
     class taught by Proffesor King. 

select Name from Student 
   where Snum not in (select Snum from Enrolled 
          where Classname in (select Name  from Class 
                  where iid = (select iID  from Instructor 
                               where name='King')      ) );