School of Computing and Information Technology Session: Autumn 2021 University of Wollongong Lecturer: Janusz R. Getta CSCI235/CSCI835 Database Systems Laboratory 5 3 May 2021 Scope This laboratory includes implementation of database links, synonyms, programming of distributed database systems, and implementation of BSON documents. The outcomes of the laboratory work are due by Saturday 15 May, 2021, 7.00 pm (sharp). Please read very carefully information listed below. This laboratory contributes to 2% of the total evaluation in the subject. A submission procedure is explained at the end of specification. This laboratory consists of 2 tasks and specification of each task starts from a new page. It is recommended to solve the problems before attending a laboratory class in order to efficiently use supervised laboratory time. A submission marked by Moodle as “late” is treated as a late submission no matter how many seconds it is late. A policy regarding late submissions is included in the subject outline. A submission of compressed files (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) is not allowed. The compressed files will not be evaluated. All files left on Moodle in a state “Draft(not submitted)” will not be evaluated. An implementation that does not compile due to one or more syntactical errors scores no marks. It is expected that all tasks included within Laboratory 5will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for the assessment task.Prologue 1 In this Laboratory you MUST NOT use csora Oracle database server. Download the files dbschema.bmp, dbcreate.sql, dbload.sql, and dbdrop.sql included in a section SAMPLE DATABASES on Moodle. A conceptual schema of a sample database is included in a file dbschema.bmp. Connect to one of data-pc Oracle 19c servers. The database server you are connected now will be called as “the host server”. (1) While connected to the “host server” process the scripts dbcreate.sql and dbload.sql scripts to create a sample database on the “host server” and to load some data. (2) Connect to another data-pc Oracle19c server. This database server will be called as the “remote server”. Recreate the relational tables BANK, BRANCH, and EMPLOYEE on the “remote server”. To do so you can update and re-use a script dbcreate.sql. No report is expected from the implementations of the actions listed in Prologue 1.Tasks Task 1 (1 mark) 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) Copy information about all banks, that have the headquarters located in USA, Canada and Brasil (hq_country) from a database located at the host server to a database located at the remote server. Copy the contents of relational table BANK, BRANCH, and EMPLOYEE only. (4) Drop the smallest number of referential integrity constraints such that it would be possible to remove from a database located at the host server information about all banks, that have headquarters located in USA, Canada and Brasil (hq_country). (5) Remove from a database located at the host server all information copied to the remote server. (6) Implement a query that finds the total number branches that are owned by each bank. List a bank name and the total number of branches owned by a bank. It may happen that some banks may have no branches at all. Then, a name of a bank must be listed with a number zero. (7) Replicate in both databases information about the banks, branches and employees of the banks located in Australia. (8) Implement a query that finds the total number branches that are owned by each bank. List a bank name and the total number of branches owned by a bank. It may happen that some banks may have no branches at all. Then, a name of a bank must be listed with a number zero. (9) List the names of relational tables located at the remote database server.(10)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.Prologue 2 Install VirtualBox on your systems. If you do not remember how you did it in CSIT115 then it is explained in https://documents.uow.edu.au/~jrg/115/cookbook/e1-1- frame.html how to do it. Download from Moodle ova image of a virtual machine with Ubuntu and MongoDB. The links An ova image of Virtual Machine with Oracle 19c (OneDrive) or An ova image of Virtual Machine with Oracle 19c (Cloudstor)are available in a section OTHER RESOURCES. You should get a file: Ubuntu18.04-64bits-MongoDB-4.2.2-08-JAN-2020.ova Start VirtualBox and import ova image of a virtual machine with Ubuntu and MongoDB. You should get a new virtual machine Ubuntu18.04-64bits-MongoDB-4.2.2- 08-JAN-2020. Start a virtual machine Ubuntu18.04-64bits-MongoDB-4.2.2-08-JAN-2020. A password to login as CSCI235 user is: csci235 When logged in, start Terminal program (3rd icon from bottom in a column of icons on the left hand size of a screen). To start MongoDB server, process the following command in Terminal window. mongod –dbpath DATA –port 4000 When MongoDB server is ready then among the other messages you should get a message: … waiting for connection on port 4000 in a large number of messaged displayed by a starting server. Minimize Terminal window. Do not close the window, from now, it is used as a console window by MongoDB server. Open another Terminal window and to start MongDB command line interface, process the following command. mongo –port 4000For a good start, process a command help. Download and unzip a file person.zip available on Moodle below a specification of Laboratory 5. You will get a file person.js that uses insert() method to insert BSON documents to a collection person. To process a script file person.js use load() method in the following way. load(“person.js”); The script loads sample BSON documents into a collection person. To count the total number of the documents in a collection person and to list the documents in a pretty format process the methods: db.person.count(); db.person.find().pretty(); No report is expected from the implementations of the actions listed in Prologue 2.Task 2 (1 mark) Implementation of BSON documents. 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. Consider a conceptual schema given below. A conceptual schema describes a database, that contains information about the departments and the employees working for the departments. An objective of this task is to implement a collection of nested BSON documents whose contents is consistent with a conceptual schema given above. A single BSON document must contain information about one department and at least 2 employees working for a department. A co
llection must consist of at least 2 departments. Create a script solution2.js, that contains 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. A collection task2 must contain information about at least 2 department and least 2 employees per department. Next, use load method to create and to 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 loading, counting, and listing the documents in a collection task2 and paste it into a text file solution2.lst.Deliverables A file solution2.lst with a report from processing of SQL script solution2.js. must contain a report from processing of the methods db.task2.count() and db.task2.find().pretty().Submission Submit the files solution1.lst and solution2.lst through Moodle in the following way: (1) Access Moodle at http://moodle.uowplatform.edu.au/ (2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page (3) When logged select a site CSCI235 (S121) Database Systems (4) Scroll down to a section SUBMISSIONS (5) Click at a link In this place you can submit the outcomes of Laboratory 5 (6) Click at a button Add Submission (7) Move a file solution1.lst into an area You can drag and drop files here to add them. You can also use a link Add… (8) Repeat a step (7) for a file solution2.lst. (9) Click at a button Save changes (10)Click at a button Submit assignment (11)Click at the checkbox with a text attached: By checking this box, I confirm that this submission is my own work, … in order to confirm the authorship of your submission. (12)Click at a button Continue End of specification.
<a class=”btn btn-outline-primary w-100 btn-lg” href=”https://eliteacademicbrokers.com/main.php?get=order”>Order Now</a>
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.