2023年11月25日

Python代写｜Manufacturing Data Science 製造數據科學 Assignment 4

本次澳洲代写是Sql脚本的一个assignment

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.