Exercise (22 points)
This exercise is based on Freebase.com celebrities’ data collected by the authors of the “Programming the Semantic Web” book.
To create the tables in Oracle:
Use the statements in script celebsCreate.sql
To populate the tables:
If you are using UVic’s Oracle installation, execute the statements in celebsStudents-IfOracleUVICused.sql
If you are using an Oracle installation on your PC, import the data for each table using the text files in the zip archive:
Right click on the table name in SQL Developer (left pane), e.g. Celebs, and select “Import Data”. Select the corresponding text file (same name) from the zip archive (uncompress first), then accept all the defaults.
The tables are:
Albums(title)
Movietitles(title)
Celebs (name)
StarredIn (celeb, movie)
Released (celeb, album)
Relationships (celeb1, celeb2, started, ended)
Enemies (celeb1, celeb2)
Answer the following questions (2 points each)
Find the movies where both Tom Cruise and Pen… C… have starred together.
Find all the co-stars of Nicolas Cage.
Find the movies where Tom Cruise co-starred with a celeb he is (or has been) in relationship with. The result should be (costar, movie) pairs.
Hint. “Relationships” is symmetric, (i.e. for each celeb1,celeb2 pair, the inverse pair, celeb2,celeb1 has been also inserted into the table).
Find the movies where a celeb co-starred with another celeb he/she is (or has been) in relationship with. The result should be (celeb1 celeb2 movie) triples.
Find how many movies each celeb has starred in. Order the results by the number of movies (in descending order). Show only the celebs who have starred in at least 10 movies.
Find the celebs that have been in relationship with the same celeb. The result should be (celeb1, celeb2, celeb3) triples, meaning that celeb1 and celeb2 have been in relationship with celeb3.
For each pair of enemies give the number of movies each has starred
in.
The result should be a set of (celeb1 celeb2 n1 n2) quadruples, where n1 and n2 are the number of movies that celeb1 and celeb2 have starred in, respectively. Observe that there might be celebs with zero movies they have starred in.
Hint. Create first a virtual view: celebMovieCounts that gives for each celeb the number of movies he/she has starred in.
Find how many albums each celeb has released. Order the results by the number of albums (in descending order). Show only the celebs who have released at least 2 albums.
Find those celebs that have starred in some movie and have released some album.
For each celeb that has both starred in some movie and released some album give the numbers of movies and albums he/she has starred in and released, respectively. The result should be a set of
(celeb, number_of_movies, number_of_albums) triples.
Find the earliest and the latest relationship (w.r.t the start date) recorded in this database.
Hint. This needs two (similar) queries.
