Database System -- 3
Budget: $40 – $50 AUD
Creating a database link, synonym, and programming distributed database system.
If you skipped the Prologue 1 section in a specification of Laboratory 5 then it is recommended to read it and to perform the actions described there now.
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. A script solution1.sql must performs the following actions while connected to the "host server".
(1) Create a database link from the"host server"to the"remote server".
(2) Create a synonym names of the empty relational tables located at the"remote server".
(3) Use the database links to move information about all categories of products, all suppliers located in USA or in UK or in Australia and all products supplied by the suppliers located in USA or in UK or in Australia from the "host server" to the "remote server".
"Move" means, that all information about suppliers and products copied from the "host server" to the "remote server" must be later on removed from the "host server".
(4) Implement a query that finds the total number of products supplied bye a each supplier. If a supplier supplies no products then list a name of a supplier with a number zero. Order the results in the ascending order of the total number of supplied products.
(5) List the names of relational tables located at the"remote server.To do so you have to access a data dictionary view USER_TABLES at the "remote server".
(6) Drop the synonyms and a database link.
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 statements processed. To achieve that put the
following SQLcl commands:
SPOOL solution1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 200
SET PAGESIZE 400
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 that creates the database links, synonyms, and processes the distributed databases. A report must have no errors and it must list all SQL statements processed.
If you skipped the Prologue 1 section in a specification of Laboratory 5 then it is recommended to read it and to perform the actions described there now.
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. A script solution1.sql must performs the following actions while connected to the "host server".
(1) Create a database link from the"host server"to the"remote server".
(2) Create a synonym names of the empty relational tables located at the"remote server".
(3) Use the database links to move information about all categories of products, all suppliers located in USA or in UK or in Australia and all products supplied by the suppliers located in USA or in UK or in Australia from the "host server" to the "remote server".
"Move" means, that all information about suppliers and products copied from the "host server" to the "remote server" must be later on removed from the "host server".
(4) Implement a query that finds the total number of products supplied bye a each supplier. If a supplier supplies no products then list a name of a supplier with a number zero. Order the results in the ascending order of the total number of supplied products.
(5) List the names of relational tables located at the"remote server.To do so you have to access a data dictionary view USER_TABLES at the "remote server".
(6) Drop the synonyms and a database link.
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 statements processed. To achieve that put the
following SQLcl commands:
SPOOL solution1
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 200
SET PAGESIZE 400
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 that creates the database links, synonyms, and processes the distributed databases. A report must have no errors and it must list all SQL statements processed.