CS 33 (Spring 2011): Homework 2: Advanced SQL and ODBC

Deadline: 10:00pm sharp on April 11, 2011

Please read through this page entirely and carefully, before beginning your work.

Part 1: Further SQL Problems

  1. Refer to the bank database schema given in Figure 3.19 (page 107) in the textbook. Write a sequence of SQL data modification statements that achieve effect of closing the "North Town" branch of this bank and merging all its assets and liabilities into the "Perryridge" branch. You will need to update multiple tables. Your solution (a) should not assume anything about what cities "North Town" and "Perryridge" are located in, and (b) should not use the MySQL feature of user-defined variables.
    [10 points]

  2. Solve Problem 3.16 (d) and (e) from the textbook. For part (e), note that the size of the payroll of a company is defined to be the total salary it pays out.
    [6 + 6 points]

  3. Solve Problem 3.24 from the textbook.
    [8 points]

Part 2: Connecting to a Database: ODBC and Equivalents

  1. Consider a MySQL-based database that stores information about employees in an organisation and their reporting chain. Write code (in a general-purpose language) that connects to this database, gathers this information into a suitable tree-like internal data structure, and then writes some portion of (or all of) this information onto standard output, using indentation and layout to represent the reporting chain.

    Specifically, your program should take three or four command line arguments, operating as follows. Say your prorgam is named "foo" and is invoked as follows (square brackets denote an optional argument):

    $ foo uname pwd dbname [empname]

    It should then connect, using username "uname" and password "pwd" to a database named "dbname" on the machine "sunapee.cs.dartmouth.edu", and gather information from the relation employee (name, role, manager). Then, it should print several lines to standard output, where each line gives the name of an employee, their role, and the total number of subordinates (direct or indirect) under them, in the format "%s: %s: %d subordinates" (in printf notation). If this employee has no subordinates, then the third field should be omitted. The subordinates of an employee should appear beneath that employee's name, indented using two leading spaces. This rule should be applied recursively: thus, second-level subordinates should appear below their respective immediate managers and should be indented using four leading spaces, etc. Further, all employees that are immediately under a particular manager should be listed in ascending alphabetical order of their names. Finally, the output should begin with the employee whose name is "empname". This fourth argument is optional; if it is missing, then the output should begin with the top-level employee, indicated by a NULL value in the manager field. In the latter case, if there is more than one top-level employee, then you should print all of them (together with their subordinates, as described above) in ascending alphabetical order of their names.

    There is a detailed example in the file ~cs33/data/hw3_empdata.txt, which I urge you to read and understand, very carefully. You must follow the above explicit output formatting instructions as well as the implicit ones in that example.

    [30 points]

  2. Examine the following three files, which contain data about a set of imaginary actors and a set of imaginary films (movies) along with information about which actors appear in which films:

         ~cs33/data/actor.txt,   ~cs33/data/film.txt,   ~cs33/data/actor_film.txt

    The comment lines on top of each file explain the contents adequately. Assume that the data from these have been loaded into the following tables in a MySQL database on the machine "sunapee.cs.dartmouth.edu":

    actor (actor_ID, first_name, last_name)
    film (film_ID, film_name, description, year)
    actor_film (actor_ID, film_ID)

    We would like to be able to merge two of the actors in this database into one: imagine that we have discovered, e.g., that "Matthew Johansson" is really a pseudonym of "Greg Chaplin". However, we should not carry out this merge operation if it turns out that both these actors have appeared in a common film: perhaps we trust our database enough that if this happens, we're sure they're not the same person.

    Write code (in a general-purpose language) to perform the necessary updates on the database. Specifically, your program should read seven command line arguments as follows:

    $ foo uname pwd dbname first1 last1 first2 last2

    Here "foo" is the name of your program, "uname" and "pwd" are a username/password combination used to connect to the database "dbname", and "first1", "last1", "first2" and "last2" are, respectively, the first and last names of actor1 and the first and last names of actor2. Your program should then connect to "dbname", check if the aforementioned common film condition applies and then behave as follows. If the condition applies, it should print (on standard output) a complete list of film names in which both of the given actors appear, in increasing order of film ID. Else (the condition does not apply), it should print nothing and effectively rename actor1 to actor2 in the database, updating the tables actor and actor_film as necessary. If any errors occur (e.g., inability to connect to the database, or update it, or a non-existent actor name), then you should print an appropriate informative error message to standard output and not make any changes to the database.

    [20 points]

What to turn in

Important Notes