CSC 459- Exam#2        Name:

(20 Points)1. Identify the key (candidate keys) for each of the following relations and normalize until they are in BCNF. Assume the following dependencies where relevant:

A ---> B          A ---> C        A ---> L          B ---> C 
        A,F ---> G        G ---> F        A,F ---> K 

   a) R1 (A,B,C)


   b) R2 (A,B,D)


   c) R3 (A,G,F)

(30 Points) 2. Assume the following:

• String q = "select fss_num, name, address, age, salary " +
  "from faculty order by name";
• String c = ""jdbc:oracle:thin:@altair.cs.oswego.edu:1521:T111";
• The driver is oracle.jdbc.driver.OracleDriver();
  
  

Answer the following questions about accessing an SQL database using the classes in java.sql package:

1. Write the instruction that would register the driver above with the driver manager.
   
   
2. Write the instruction that creates the connection object for c.
   
   
3. Write the instruction that creates a statement object for the connection object created in b).
   
   
4. Write the instruction that creates a result set object for the statement created in c) using the q string.
   
   
5. Write the code segement that retieves and display the faculty data.
   
   
   
   
   
   
   

(30 Pts.) 3. Assume the following relations:

• EMPLOYEE (e#,ename,salary,age)
• EP (e#,p#)
• PROJECT (p#,pname,leader_e#,starting_date,Completion_date)

Write the SQL queries for the following:

1. Find name,age, and salary of all project leaders.
   
   
   
   
   
   
   
   
   
2. Find the average salary of employees working on the project named DOD.
   
   
   
   
   
   
   
   
   
3. Get the name of all employees that are projectless (i.e. are not currently working on any project).
   
   
   
   
   
   
   
   
   

    EXEC SQL BEGIN DECLARE SECTION;
       int        credit;
       int        count;
       int        id_num;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL DECLARE list_courses CURSOR FOR
        SELECT cdesc,credits,count(sec#)
          FROM course,section
         WHERE course.c#=section.c#
	   and fss#=:id_num


main()
{
    /* assume code for connecting is here as well as the SQL statement
       for trapping errors */

    printf("Please Enter Faculty ID:");scanf("%d",&id_num);

    /* open cursor */
    EXEC SQL OPEN list_courses;

    /* set trap for end of data */
    EXEC SQL WHENEVER NOT FOUND GOTO end_of_fetch;

    /* loop until fetch fails, control is then transferred to end_of_fetch. */
    for ( ; ; )
    {
        EXEC SQL FETCH list_courses INTO :desc,:credit,:count;
        if (credit>3 && count>1) {
           desc.arr[desc.len]='\0'; /* null terminate desc */
           printf("%-30s\n", desc.arr);
        }
    }

  end_of_fetch:
    EXEC SQL CLOSE list_courses;
    EXEC SQL COMMIT WORK RELEASE;  /* Log off the database. */
    printf("\nHave a good day!\n\n");
    exit(0);
}