IFI-studier: Emner: INF3180/INF4180
Mandatory exercise for INF4180/INF3180 Autumn 2003
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
In addition to the delivery date, there is one more deadline to be met:
Send an e-mail to firstname.lastname@example.org with the names and e-mail
addresses of the two team members before midnight Monday 29
(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.
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.
The movies are categorized.
Make a list of all categories and the percentage of
the movies that are in each category.
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.
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?
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).
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.
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
Populate your database using the Oracle database as your data source.
Answer Q3 and Q4 from Task 1 using your own director-focused
Report the code and results.
Compare the execution times with the results from Task 1
and try to explain any differences.
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:
The file ~igorr/moviedb-oracle.java contains a
program skeleton for using the movie database from java.
Here is a link:
*** OTHER USEFUL HELP FILES ***
To get started with ObjectStore, this link may be useful:
A previous description of the Oracle version of the test database
is found here:
End of exercise set