INFS2200/7903 PROJECT ASSIGNMENT
Task 1 – Constraints
1. After running the script file, you will notice that only some of the constraints listed
in Table 1 were created. Write a SQL statement to find out what constraints have
been created on the tables. (Note: Some table names may need to be in capitals,
e.g., ‘FILM’ instead of ‘film’)
Question: Which constraints in Table 1 have been created on these tables?
2. Write the SQL statements to create all the missing constraints.
Task 2 – Triggers
1. Assume that the film_id should be automatically populated when a new film is
added. Write a SQL statement to create a sequence object to generate values for
this column. The sequence, named FILM_ID_SEQ, should start from 20,010 and
increment by 10.
2. Write a SQL statement to create an Oracle trigger called BI_FILM_ID that binds
the sequence object FILM_ID_SEQ to the film_id column, i.e., the trigger populates
values of FILM_ID_SEQ to the film_id column when a new film is added.
3. Write a SQL statement to create an Oracle trigger BI_FILM_DESP that appends
text to the description of every new film inserted into the database. The text is
based on the rating, the language, and the original language of the film. The format
of the text you append should be as follows (replacing tokens):
<rating>-<seq>: Originally in <original language>. Re-released in <language>.
Here, <seq> is the sequence number of the film with that <rating>, and <original
language> and <language> should be the name of the language from the language
Hint: You might need to use some built-in functions for string manipulation such
as TO_CHAR, CONCAT, SUBSTR, INSTR, etc.
Notes for Task 2.3:
The new description must match the expected output exactly in order to receive
marks. For example,
o Do not add extra space or line break;
o Do not change capitalisation of the rating or the language names.
If either rating, language_id, or original_language_id of the new film is null, then
the trigger should do nothing, i.e., the new film uses the original description.
You do not need to handle the cases where the resulting text after the trigger
exceeds the description length. Let the trigger fail.
Do not use hardcode. Your trigger should be able to handle other languages
beyond those provided to you in the SQL script.
o For example, if the language ‘SQL’ is added to the language table, then the
trigger should be able to handle a movie in ‘SQL’.
Task 3 – Views
1. Write a SQL statement to find the ‘Action’ (category) films with the shortest running
time (length). Your query should output the titles and lengths of the films.
2. Write a SQL statement to create a (virtual) view called MIN_ACTION_ACTORS
that contains all the actors who have acted in the films you obtained in Task 3.1.
The view should include the columns actor_id, first_name, and last_name. (Note:
Each actor should only appear once in the view, even if they may have acted in
3. Write a SQL statement to create a (virtual) view called V_ACTION_ACTORS_2012
that lists the ids, first names and last names of all the actors who have acted in an
‘Action’ film released in the year 2012. (Note: There should be no duplicate rows
in the view, similar to Task 3.2)
Example: Assume the following film is inserted into the database, which is the
th film with a rating ‘PG’ (i.e., there are already 4 films with the rating ‘PG’ in the
database), and the current FILM_ID_SEQ value is 20,010.
INSERT INTO film (title, description, language_id, original_language_id, rating)
VALUES (‘B Movie’, ‘Movie about wasps.’, 1, 2, ‘PG’);
It should produce the following result when the following SQL statement is run:
SQL> SELECT description FROM film WHERE film_id = 20010;
Movie about wasps.PG-5: Originally in Italian. Re-released in English.
4. Write a SQL statement to create a materialized view MV_ACTION_ACTORS_2012
that lists the same information as in Task 3.3.
5. Execute the following two SQL statements and report their query execution time.
Question: Did the materialized view speed up the query processing? Explain your
answer. (Hint: You should look at both the elapsed time and the cost in the query
SELECT * FROM V_ACTION_ACTORS_2012;
SELECT * FROM MV_ACTION_ACTORS_2012;
Note: For any task mentioning the execution time, please run the queries on a
computer with a HDD rather than an SSD, so that the timing difference is noticeable.
All lab computers have HDDs and are appropriate for such task.
Task 4 – Indexes
1. Write a SQL statement to find the first 100 films (in ascending alphabetical order
of the film titles) that take place in a ‘Boat’, i.e., the word ‘Boat’ appears in the film
description. (Note: You should avoid using LIKE in the SQL statement and instead
use string manipulation functions)
2. In order to potentially speed up the query in Task 4.1, a function-based index could
be created on the film table. Write a SQL statement to create an index IDX_BOAT
that best fits the task and justify your choice.
3. Report the execution time of the query statement you wrote in Task 4.1 before and
after creating the index in Task 4.2.
Question: Did the index speed up query processing? Explain your answer. (Hint:
You should look at both the elapsed time and the cost in the query execution plan)
4. Write a SQL statement to count the number of films for which there are at least 40
other films with the same release_year, rating, and special_features values.
5. In order to potentially speed up the query in Task 4.4, indexes should be created
on the release_year, rating, and special_features columns.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx