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]
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]