这个澳洲作业案例分析是Oracle电影数据库相关的Assignment代写

The goal of this project is to gain practical experience in applying several database management concepts and techniques using the Oracle DBMS.

Your main task is to first populate your database with appropriate data, then design, implement, and test the appropriate queries to perform the tasks explained in the next sections.

You must work on this project individually.

Roadmap: Section 1 describes the database schema for your project and it also provides instructions on downloading the script file needed to create and populate your database.

Section 2 describes the tasks to be completed for this project. Finally, Section 3 provides you with all the necessary submission guidelines. Enjoy the project!

SECTION 1. THE MOVIES DATABASE

The Database: The MOVIES database (Figure 1) captures the information regarding movies and the actors in these movies. The database includes six tables: film, actor, category, language, film_actor, and film_category. Film keeps track of film details. Actor stores information about all actors in the movie industry. Category stores the information about the different types of film categories. Language stores the different languages in which these movies are released. Film_actor and film_category keep track of which actors have acted in which films, and which films are classified under which categories, respectively.

The Script File: Please go to Blackboard and download the supplementary script file for this project “prjScript.sql”.

The Database Constraints: The following table lists all the constraints that should be created on the MOVIES database.

SECTION 2. ASSIGNMENT TASKS

Create and Populate Database: You need to execute the script file “prjScript.sql” to create and populate your database before working on the following tasks. Wait till you see the message “DONE! All data has been inserted.” It should only take one minute. The script will also drop related tables.

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 six 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 six 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 table.

Hint: You might need to use some built-in functions for string manipulation such as TO_CHAR, CONCAT, SUBSTR, INSTR, etc.