CSI235-Lab 5 Database Links,Synonyms and Programming of Distributed Database Systems Solved

This laboratory includes implementation of database links, synonyms, programming of distributed database systems, and transformation of relational tables into BSON documents.

 

                         

 

Tasks 

Task 1  
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.

 

Implement SQL script solution1.sql that 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 relational tables located at the "remote server".

 

(3)     Copy information about all orders and the contents of all orders submitted before 1 March 1992 from the "host server" to the "remote server".

 

(4)     Delete from the "host server" all orders and the contents of all orders submitted before 1 March 1992.

 

(5)     Implement the following queries as SELECT statements.

 

(i)          Find the total number of orders recorded in both the "host server" and the “remote server”.

 

(ii)        Find all order keys and all prices of all orders in both “host server” and the “remote server” where a price is greater than 150000. Sort the results in the ascending order of order keys.

 

(iii)      Find all order keys of all nonempty orders, i.e. the orders that include at least one item. List the order keys together with the total number of items included in each order. Sort the results in the descending order of the total number of items included in each order. Sort all orders with the same total number of items in the ascending order of order keys. Use both "host server" and "remote server".

 

(iv)      Find the part keys of all parts included in at least one order located in the "host server" and in at least one order located in the "remote server".

 

(6)     Drop the synonyms and a database links.

 

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.  

                                                                                                                                                   


Task 2  
Transformation of data stored in the relational tables into data stored in BSON collection. 

 

If you skipped the Prologue 2 section in a specification of Laboratory 5 then it is recommended to read it and to perform the actions described there now.

 

An objective of this task is to implement PL/SQL program (anonymous block or stred procedure or stored function), that lists the contents of the relational tables REGION and NATION as a sequence of invocations of db.task2.insert(...) method. Such sequence invocations can be later used to load data into a collection of BSON documents task2. Note, that ... must be replaced with the correctly formatted data such that processing of db.task2.insert(...) methods will successfully insert the documents into a collection task2.

 

As an example, download and unzip the files customer.zip, part.zip, and supplier.zip available on Moodle in a section SAMPLE DATABASES. You should get the files customer.js, part.js, and supplier.js. The files contains invocations db.tpchr.insert(...) methods that insert into a collection tpchr the transformed data from the relational tables CUSTOMER, ORDERS, PART, and SUPPLIER. Your PL/SQL implementation supposed to generate a sequence of invocations of db.task2.insert(...) methods that can be used to load data from the relational tables REGION and NATION into a collection of BSON documents task2.

 

A PL/SQL implementation technique is up to you. You can implement an anonymous PL/SQL block or stored PL/SQL procedure or stored PL/SQL function. You can reuse the outcomes of Assignment 1, Task 4.  

 

Please note, that the contents of the relational tables REGION and NATION must be transformed into nested BSON documents. A solution that re-implements relational tables as the separate BSON documents scores no marks.

 

Save your implementation in a file solution2.sql.

 

When ready, process SQL script solution2.sql and save a report from processing in a file solution2-1.lst.

 

Your report must include a listing of all PL/SQL statements processed. To achieve that put the following SQLcl commands:

 

SPOOL solution2-1 

SET ECHO ON 

SET FEEDBACK ON 

SET LINESIZE 100 

SET PAGESIZE 200 

SET SERVEROUTPUT ON 

 

at the beginning of SQL script and

 

SPOOL OFF 

 

at the end of SQL script.

 

To verify the correctness of your transformation copy the generated invocations of db.task2.insert(...) methods into a file solution2.js and use load method to create and load the contents of a collection task2 on MongDB server.

 

Next use the methods:

 

db.task2.count(); db.task2.find().pretty(); 

 

to list the total number of documents in a collection task2 and to list the contents of collection task2 in a pretty format.

 

When ready, copy the contents of Terminal window with the results from counting and listing of the document in task2 collection and paste it into a file solution2-

2.lst.

 

 

Deliverables
A file solution2-1.lst with a report from processing of SQL script solution2.sql. A report must list all SQL and PL/SQL statements processed and all error messages. A file solution2-2.lst with a report from processing of the methods db.task2.count() and db.task2.find().pretty().