CS 33 (Spring 2010): Homework 3: Connectors, Relational Calculus, QBE

Deadline: 10:00pm sharp on April 26, 2010

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

Part 1: Relational Calculus

  1. Solve all parts of Problem 6.15 from the textbook.
    [8 * 3 = 24 points]

Part 2: E-R Diagrams and Database Design

  1. Solve all parts of Problem 7.20 from the textbook.
    [10 points]

  2. Solve Problem 7.21 from the textbook.
    [14 points]

Part 3: 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", 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.

    [32 points]

  2. Consider, yet again, the actors and films example from Homeworks 1 and 2. Assume that the data from the relevant files in ~cs33/data/ have been loaded into the following tables in a MySQL database on "sunapee":

    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