The Databases Course

Exercise No. 7

SQL embedded in C - Practical

Due on January 8th by midnight

The Example Database Schema: Suppliers supply certain parts for certain projects. The information about who supplies what to which projects is found in the shipments table. The tables are:

Suppliers(s#,sname,status,city)
Parts(p#,pname,color,weight,city)
Projects(j#,jname,city)
Shipments(s#,p#,j#)

The s#,p#,j# attributes are key attributes. The tables are available to query using Oracle.

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

Accept a number n from the user, and calculate the most popular city for the first n suppliers (ordered according to supplier names, ascending) to live in. Print this city, and the number of suppliers (of the first n) living there. There may be more than one most popular city - in that case, print them all.

Recall database tables are usually very large. That is, the suppliers relation and the number n may be very large. Therefore it is unreasonable to assume that you can store all the first n tuples 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 values. You should ignore (and not count) suppliers whose 's#' or 'city' fields are missing. Recall the 'is null' predicate and 'count()' function definition.

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: