CSCI835 Database Systems | My Assignment Tutor

School of Computing and Information TechnologyUniversity of WollongongSession:Autumn 2021Lecturer: Janusz R. Getta CSCI235/CSCI835 Database SystemsAssignment 317 May 2020ScopeThis assignment includes the tasks related to design of a logical schema of MongoDBdatabase, implementation of JSON Schema, implementation of data manipulations inMongoDB, and application of aggregation framework in MongoDB.The outcomes of the assignment work are due by Saturday 5 June, 2021, 7.00 pm (sharp).Please read very carefully information listed below.This assignment contributes to 20% of the total evaluation in a subject CSCI235.A submission procedure is explained at the end of specification.This assignment consists of 3 tasks and specification of each task starts from a new page.It is recommended to solve the problems before attending the laboratory classes in order toefficiently use supervised laboratory time.A submission marked by Moodle as “late” is treated as a late submission no matter howmany 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) isnot 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 and/or run timeerrors scores no marks.It is expected that all tasks included within Assignment 3 will be solved individuallywithout 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 resultin a FAIL grade being recorded for the assessment task.PrologueInstall VirtualBox on your system, if it is not installed yet. If you do not remember howyou did it in CSIT115 then it is explained inhttps://documents.uow.edu.au/~jrg/115/cookbook/e1-1-frame.htmlhow to do it.Download from Moodle ova image of a virtual machine with Ubuntu and MongoDB.The image is available in a section OTHER RESOURCES. You should get a file:Ubuntu18.04-64bits-MongoDB-4.2.2-08-JAN-2020.ovaStart 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:csci235When logged in, start Terminal program (3rd icon from bottom in a column of icons on theleft-hand size of a screen).To start MongoDB server, process the following command in Terminal window.mongod –dbpath DATA –port 4000When MongoDB server is ready then among many, many, … the other messages youshould get a message:… waiting for connection on port 4000Minimize Terminal window. Do not close the window, from now, it is used as a consolewindow by MongoDB server.Open another Terminal window and to start MongDB command line interface, process thefollowing command.mongo –port 4000For a good start, process a command help.Download to your virtual machine a file customers.zip from a section SAMPLEDATABASES on Moodle to the current folder from where you started mongo client.Unzip a file customers.zip to the current folder.You should get a file customers.js.To create a collection customers and to load the documents into the collection, processa script customers.js at > prompt of mongo client in the following way.load(“customers.js”);A logical schema of a collection customers is given in a file bsonschemap.bmplocated in a section SAMPLE DATABASES on Moodle.Next, you can use the methods:db.customers.find().count() anddb.customers.find().pretty()to count the total number of the documents in a collection orders and to list all documentsin a pretty format.Next try few simple queries.For example, to list in a nice format the contents of a document with an identifier “CACTU”process a method:db.customers.find({“_id”:”CACTU”}).pretty();For example, to list information about the customers and the orders submitted by acustomer who has a customer code “FAMIA” process a method:db.customers.find({“CUSTOMER.customer code”:”FAMIA”}).pretty();No report is expected from implementation of the actions performed so far.Task 1 (10 marks)Logical design and implementation of BSON documentsConsider the following conceptual schema of a sample database, that contains informationabout employees working on projects, departments employing employees, and managersof departments.Transform a conceptual schema given above into a logical schema of BSON documents.To draw a logical schema of BSON documents use a graphical notation presented in thelecture slides 22 BSON DESIGN. You can use UMLet 14.3 to draw a logical schema. Alink to UMlet 14.3 software is available on Moodle in a section OTHER RESOURCES. UseCSCI235Palette. Save a logical schema in a file solution1.bmp.An important objective of the design is to maximize the size and complexity of hierarchicalstructures in a database and in the same moment to eliminate any redundancies from adatabase.A correct transformation of a conceptual schema into a logical schema is worth 5 marks.Next, use a diagram of a logical schema created in the previous step to implement JSONschema, that can be used to validate the documents, that contain information about projects,employees, and departments.Download and unzip a file solution1.zip. You should get a file solution1.js.The file contains the comments with the specifications of the actions listed below. Insertinto a file solution1.js implementation of the actions listed below.(1) First, the script creates a collection task1 using a methoddb.createCollection() with the JSON schema implemented in the previousstep as a validator (see a presentation 21 Validation with JSON Schema).(2) Next, the script inserts into a collection task1 the documents that contain informationabout one project, that involves two employees and about one department that employsthe same two employees. One of the employees is a manager of a department. Thedocuments must contain meaningful data and the types of values associated with thekeys must be consistent with the meanings of the keys. For example, a value associatedwith a key “date of birth” must be of type date. All documents must pass thevalidation.(3) Next, the script inserts one more document, that fails the validation against JSONschema used as a validator for a collection task1.(4) Finally, the script prints the explanations on why one of the documents failed thevalidation. A simple way to print the explanation is to use print(“text’).A correct implementation of a script solution1.js is worth 5 marks.To process a script solution1.js and to create a report solution1.lst fromprocessing of a script, perform the following steps.(1) Use gedit editor to open a file solution1.js with the implementations of theactions listed above.(2) Select the entire contents of gedit window and Copy it into a buffer.(3) Open a new Terminal window and start mongo client in the following way.mongo –port 4000(4) Paste the contents of the buffer copied earlier from gedit window in front of >prompt of mongo client. You may have to press Enter key to process the last datamanipulation in a case when it is not followed by a newline control character.(5) Select the entire contents of the Terminal window and Copy&Paste it into a filesolution1.lst. Save a file solution1.lst.DeliverablesA file solution1.bmp with a logical schema of BSON documents. A filesolution1.lst with a report from processing of MongoDB script solution1.jsthat creates a collection task 1 with JSON validator and inserts the documents into thecollection. Do not forget about the explanations why one of the documents failed thevalidation.Please remember that:– a report without listings of the processed methods scores no marks,– a report that contains any kind of processing errors scores no marks.Task 2 (5 marks)Data manipulationsDownload and unzip a file solution2.zip. You should get a file solution2.js.The file contains the specifications of the following 5 data manipulation operations on acollection customers.(1) Change a location of a customer who has a customer code FISSA from Madrid toBarcelona. Next, display a customer code, city, region, and country of a customer whohas a customer code FISSA.(2) Rename a key “submits” to “orders” for all customers from Poland. Next, displaycustomer code, country, and orders of a customers from Poland.(3) Increase a value of freight by 10% in an order that has order id 274. Next, display acustomer code, and all orders submitted by a customer who submitted and order thathas order is equal to 274.(4) Append to a customer who has a customer code equal to LAUGB the followinginformation about a new order: “order id” : 999,”freight” : 10.5. Nextlist all information about a customer who has a customer code equal to LAUGB.(5) Remove information about a contact title from the description of a customer whosubmitted an order with an order id 333. Next list a complete description of a customerwho submitted an order with an order id 333.Implement the data manipulations listed above in a data manipulation language ofMongoDB. Write your solutions into the empty slots following a specification of each datamanipulation in a file solution2.js. Do not remove the specifications of the datamanipulations and semicolons following the specifications.Implementation of each data manipulation is worth 1 mark.When ready create a report from processing of the data manipulations in the following way.Use gedit editor to open a file solution2.js with the specifications andimplementations of the data manipulations.Select the entire contents of the file and Copy it into a buffer.Open a new Terminal window and start mongo client in the following way.mongo –port 4000Paste the contents of the buffer copied earlier from gedit window in front of > promptof mongo client. You may have to press Enter key to process the last data manipulationin a case when it is not followed by a newline control character.Select the entire contents of the Terminal window and Copy&Paste it into a filesolution2.lst. Save a file solution2.lst.DeliverablesA file solution2.lst with a report from processing of MongoDB scriptsolution2.js with the implementation of the data manipulations listed above.And again, please remember that:– a report without the specifications of the data manipulations and listings of the processeddata manipulations scores no marks,– a report that contains any kind of processing errors scores no marks.Task 3 (5 marks)Query processing and data transformation with aggregation frameworkDrop a collection customers in the following way.db.customers.drop();To re-create a collection customers and to load the documents into the collection,process a scripts customers.js. at > prompt of mongo client in the following way.load(“customer.js”);Download and unzip a file solution3.zip. You should get a file solution3.js.The file contains the comments with the specifications of the following 5 queries and datatransformations.(1) Find the total number of orders shipped to the cities Aachen, or London or Madrid.Display the result in a format {“total orders”:integer-value}.(2) Find all orders shipped via Federal Shiping. Save the results into a collectionshippedvia that consists of the documents like:{“order id”: 111, “employee id”: 1}.Display in a pretty format without document identifiers all documents in a collectionshippedvia.(3) Find the total number of order per each city where the customers are located at. Listthe results in a format:{“total orders”:integer-value,“city”:city-name}.(4) Save information about a customer code, city, and country of all customers fromAachen or London or Madrid into a collection aaloma. Display in a pretty formatwithout document identifiers the contents of a collection aaloma.(5) Find 5 largest values of freight from all orders. List the results in a format:{“order id”: integer-value,“freight”:integer-value}.Use the methods aggregate() and pretty() to implement all the queries and datatransformations and to display the results. Note, that you may need two or more statementsto implement a single task.Implementation of each query/data transformation is worth 1 mark.When ready create MongoDB script file solution3.js with the implementations ofyour queries and create a report from processing of the data manipulations in the followingway.Use gedit editor to open a file solution3.js with the specifications andimplementations of the data manipulations.Select the entire contents of the file and Copy it into a buffer.Open a new Terminal window and start mongo client in the following way.mongo –port 4000Paste the contents of the buffer copied earlier from gedit window in front of > promptof mongo client. You may have to press Enter key to process the last data manipulationin a case when it is not followed by a newline control character.Select the entire contents of the Terminal window and Copy&Paste it into a filesolution3.lst. Save a file solution3.lst.DeliverablesA file solution3.lst with a report from processing of MongoDB scriptsolution3.js with the implementation of the data manipulations listed above.Please remember that:– a report without the specifications of the queries and data manipulations and listings ofthe processed queries and data manipulations scores no marks,– a report that contains any kind of processing errors scores no marks.SubmissionSubmit the files solution1.pdf, solution1.lst, solution2.lst, andsolution3.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 themiddle 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 ofAssignment 3(6) Click at a button Add Submission(7) Move a file solution1.pdf into an area You can drag and drop fileshere to add them. You can also use a link Add…(8) Repeat a step (7) for the files solution1.lst, solution2.lst, andsolution3.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, Iconfirm that this submission is my own work, … in order toconfirm the authorship of your submission.(12)Click at a button ContinueEnd of specification

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER

Leave a Reply

Your email address will not be published. Required fields are marked *