Mandatory exercise for INF4180/INF3180 Autumn 2003

Formalities

Delivery is due by Tuesday 21 October at 3 p.m.

Be sure to fill in the form 'Declaration for Mandatory Papers'

The exercise is to be solved by small teams consisting of two students. In addition to the delivery date, there is one more deadline to be met:

Send an e-mail to ragnarn@ifi.uio.no with the names and e-mail addresses of the two team members before midnight Monday 29 September.
(Students who have strong reasons may apply to be allowed to solve the exercise alone. Applications are due at 8 a.m. Monday 29 September.)
Teams of more than 2 students will not be allowed.

The exercise consists of three tasks that all must be solved.

Task 1

The following queries should be solved both by using SQL against the Oracle implementation of the database and by writing Java or C++ programs using the ObjectStore implementation. In addition to the code and answer, also report the time used by the computer to answer each query. (No accurate measurements are required. You may use any technique you want, and it suffices to use an ordinary wrist-watch and report the results with whole seconds as resolution.) Also report why you think the execution time is long or short for different databases and different queries.

Q1

The movies are categorized. Make a list of all categories and the percentage of the movies that are in each category.

Q2

The sum of percentages in Q1 is different from 100. This is due to the fact that some movies are classified into several categories while others are not classified in any category. Find the maximum number, m, of categories any single movie is classified into, and make a list (table) showing the number of movies that are classified into exactly k categories for k = 0, 1, 2, ..., m.

Q3

How many (distinct) directors are there in the database, and how many of them have themselves played an act in every movie they have directed?

Q4

List the full name of all directors who have made more than 5 movies and who have used one and the same actor in all their movies (which may be far more than 6).
Beware! This query is unsuitable for a "trial and error" approach. A wrongly put query could run for hours, so you should both feel confident that your query is correct before you submit it.

Task 2

One of the differences between relational and object databases is the flexibility to design the latter at the conceptual level to facilitate its intended usage. The ObjectStore implementation of the database was designed to be as general and multi purpose as possible.

You are now to create two new database designs that can be implemented in ObjectStore. The first design shall focus on the actors, i.e. all queries are about parts and movies related to one or more actors. The second design is with focus on the directors. In the latter, persons who are not directors should not be stored as objects, store an attribute containing a foreign key to the relation 'Person' in the Oracle implementation instead. The description of your two new database designs should be UML class diagrams at the same detail level as the one in figure 3 in the description of the object-oriented design (of the provided ObjectStore database).

Implement your director-focused database in ObjectStore (make your own database). Include an attribute holding the number of movies this director has directed (It is up to you whether you implement this as a data item or as a method). Populate your database using the Oracle database as your data source.

Answer Q3 and Q4 from Task 1 using your own director-focused database. Report the code and results. Compare the execution times with the results from Task 1 and try to explain any differences.

Task 3

Your final task is to write a program that accesses two databases to answer the following query: Find the six directors having directed the largest number of films (if there is a draw for the sixth place, take them all). For each director, list all available data about her/him and her/his favorite male and female actor. By 'favorite' we here mean those who have played in the largest number of this director's films.

Your solution should use your director-focused ObjectStore database as its main data structure and fetch the detailed information about the actors from the originally provided Oracle database using the foreign keys stored in your own database.

Together with your code and answer, report the execution time for the query.

Data Model and Test Databases etc.

* HELP FILE - FINAL VERSION *

The main help file is ~igorr/oblig.pdf
Here is a link to it: ObligHelp.pdf

The file ~igorr/moviedb-oracle.java contains a program skeleton for using the movie database from java. Here is a link: moviedb-oracle.java

* OTHER USEFUL HELP FILES *

To get started with ObjectStore, this link may be useful: startObjectStore

A previous description of the Oracle version of the test database is found here: OracleInINF212