Database System
Budget: $50 – $55 AUD
Please read the attached file carefully.
Tasks
Task 1
Implementation a database trigger that automatically updates aggregated data.
Download a template for SQL script available in a file solution1.sql and insert into the template the implementations of the actions listed below. All implemented actions must directly follow a respective comment with a specification of an action.
(1) First, the script creates the additional database structures to record information about the total number of orders submitted per country and year. For example, a sample database should contain information about the total number of orders submitted by the customers located in Germany in 1996, next, the total number of orders submitted by the customers located in Germany in 1995, ... next total number if orders submitted by the customers located in France in 1996, next, the total number of orders submitted by the customers located in France in 1997, ... and so on, and so on.
(2) Next, the script stores in a sample database information about the total number of orders submitted per country and year. The information must be derived from the present contents of a sample database.
(3) Next, this script list information about the total number of orders submitted per country and year.
(4) Next, the script creates a statement trigger, that automatically updates information about the total number of orders submitted per country and year whenever a new order is added or whenever an old order is deleted from a sample database.
(5) Next, the script comprehensively tests the trigger. A test must include at least one insertion of a new order and at least one deletion of an old order. The script must list information about the total number of orders submitted per country and year after insertion of a new order and after deletion of an older one.
(6) Finally,thescriptremovesfromadatadictionaryatriggercreatedinastep(4).
When ready, process SQL script solution1.sql and create a report from processing
of the script in a file solution1.lst.
Your report must include a listing of all SQL and PL/SQL statements processed. To
achieve that put the following SQLcl commands:
SPOOL solution1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 200
SET PAGESIZE 400
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Deliverables
A file solution1.lst with a report from the implementation of a script solution1.sql. A report must have no errors and it must list all SQL statements processed.
Tasks
Task 1
Implementation a database trigger that automatically updates aggregated data.
Download a template for SQL script available in a file solution1.sql and insert into the template the implementations of the actions listed below. All implemented actions must directly follow a respective comment with a specification of an action.
(1) First, the script creates the additional database structures to record information about the total number of orders submitted per country and year. For example, a sample database should contain information about the total number of orders submitted by the customers located in Germany in 1996, next, the total number of orders submitted by the customers located in Germany in 1995, ... next total number if orders submitted by the customers located in France in 1996, next, the total number of orders submitted by the customers located in France in 1997, ... and so on, and so on.
(2) Next, the script stores in a sample database information about the total number of orders submitted per country and year. The information must be derived from the present contents of a sample database.
(3) Next, this script list information about the total number of orders submitted per country and year.
(4) Next, the script creates a statement trigger, that automatically updates information about the total number of orders submitted per country and year whenever a new order is added or whenever an old order is deleted from a sample database.
(5) Next, the script comprehensively tests the trigger. A test must include at least one insertion of a new order and at least one deletion of an old order. The script must list information about the total number of orders submitted per country and year after insertion of a new order and after deletion of an older one.
(6) Finally,thescriptremovesfromadatadictionaryatriggercreatedinastep(4).
When ready, process SQL script solution1.sql and create a report from processing
of the script in a file solution1.lst.
Your report must include a listing of all SQL and PL/SQL statements processed. To
achieve that put the following SQLcl commands:
SPOOL solution1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 200
SET PAGESIZE 400
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.
Deliverables
A file solution1.lst with a report from the implementation of a script solution1.sql. A report must have no errors and it must list all SQL statements processed.