The Databases Course

Exercise No. 6

SQL embedded in C - Practical

Due on February 2nd by midnight

The Example Database Schema: Animals participate in research projects, that may span several years and sites in many countries. Animals may participate in many projects, and may also be moved between sites during the same project. The aid, sid and rid attributes are key attributes.

Animal(aid, species, weight, birthyear)
Sites(sid, name, country, size, climate)
Research(rid, name, subject)
Events(aid, sid, rid, year)

Write a C program with embedded SQL statements to do the following:

The program takes exactly two command line arguments, which are:

ex3 <animal_id> <n>

The output of the program is a list of animal id's and species, sorted by anscending aid, of all the animals that participated in the first (earliest) n projects that animal_id participated in. For example, running 'ex3 a7 3' should give the id's and species of all the animals that were used in all of the earliest three projects a7 took part of.

Recall database tables are usually very large. That is, the input relations and n may be very large. Therefore it is unreasonable to assume that you can store all the first n projects an animals was part of in main memory at the same time. Your program should use only a fixed number of memory cells and cursors. Moreover, your program should not create new database tables or Unix files during its execution.

You may not assume that the tables are clean of null value - make sure that you ignore tuples that contain null values. Recall the 'is null' predicate and 'count()' function definition. Also, check for all possible error conditions - once an error is encountered print an appropriate message to stderr, close the connection to Oracle and exit the program with exit code 1.

Your program should be extremely well documented - explain every step. You can use the example program from class as a starting point. Good luck!

You should submit: