MongoDB - QUery 1-10

Share screenshot for upvote

During onboarding or client serving, there are many forms that the client advisor manager will have to deal with. The role of the internal compliance department is to keep track of the rate of rejection of forms as well as the reasons for the rejection. Currently, excel is used to keep track of the number of cases and the rejection reasons that are tagged to a particular case. However, there could be more than one rejection reason for a single form. The following may be one of the possible business process taken by internal compliance department: 1. Record the form to be checked 2. Assign to a checker 3. Update the observations/recommendations of the checker 4. Division head vet through and approve or reject recommendations The Structure of 'form compliance' (not exhaustive) may be : Attributes FormNo FormRef ClientAdvior Manager Department Remarks Each official form is assigned a form no. eg HR_1206, CM 4356 The reference number appear on the form to identify the form ownership Name of the Manager The department that use the form Date Stamp on which day this form's information is captured. Name of the checker Record the number of supporting documents that comes with the form for checking and validiation. Date Received Checker NumberOfSupporting Document Date Assigned DateChecked DateComplete Status RejectReason Approve/Reject Currently, this is only restricted to 1 rejected reason but by right, can be more than 1. For Seriousness, it takes in an integer 5 to means extremely serious, while 1 means very minor issue. SeriousnessLevel The general queries that the internal compliance department will have for such a database are as follows: 1. How many forms are there in the collection. 2. 2. List the FormRef and Checker for all documents with FormNo "HR_1206". 3. List the FormNo, FormRef, ClientAdviorManager for forms that belongs to accounting department. You should sort your result in ascending order of ClientAdvisorManager FormRef and Date Received. 4. List ClientAdvior Manager, FormNo, FormRef, Department of the first 2 forms which are rejected by John Lim, one of the checkers. 5. List all DateComplete, FormNo, FormRef, ClientAdvior Manager, Department for any form rejected with rejected reason contain the word "Incomplete, sort their information in the descending order DateComplete, descending order of FormRef and ascending order of ClientAdvior Manager, 6. List the Checker and the number of forms not yet checked in the collection. 6. List the Checker and the number of forms not yet checked in the collection. 7. Tabulate for each department, the total number of supporting documents received in the collection. 8. Find the number of forms that has been checked by checker but not approved by the division head yet. Your tasks are : 1. Analyse the common queries mentioned above. 2. Create a new database in MongoDB with appropriate design of collections that can support the queries above, 3. Prepare the necessary sample documents to be inserted into the collection(s) in order to support the queries, the output of the scripts must be representative for all scenarios. 4. Create the scripts that will fulfill the queries required. During onboarding or client serving, there are many forms that the client advisor manager will have to deal with. The role of the internal compliance department is to keep track of the rate of rejection of forms as well as the reasons for the rejection. Currently, excel is used to keep track of the number of cases and the rejection reasons that are tagged to a particular case. However, there could be more than one rejection reason for a single form. The following may be one of the possible business process taken by internal compliance department: 1. Record the form to be checked 2. Assign to a checker 3. Update the observations/recommendations of the checker 4. Division head vet through and approve or reject recommendations The Structure of 'form compliance' (not exhaustive) may be : Attributes FormNo FormRef ClientAdvior Manager Department Remarks Each official form is assigned a form no. eg HR_1206, CM 4356 The reference number appear on the form to identify the form ownership Name of the Manager The department that use the form Date Stamp on which day this form's information is captured. Name of the checker Record the number of supporting documents that comes with the form for checking and validiation. Date Received Checker NumberOfSupporting Document Date Assigned DateChecked DateComplete Status RejectReason Approve/Reject Currently, this is only restricted to 1 rejected reason but by right, can be more than 1. For Seriousness, it takes in an integer 5 to means extremely serious, while 1 means very minor issue. SeriousnessLevel The general queries that the internal compliance department will have for such a database are as follows: 1. How many forms are there in the collection. 2. 2. List the FormRef and Checker for all documents with FormNo "HR_1206". 3. List the FormNo, FormRef, ClientAdviorManager for forms that belongs to accounting department. You should sort your result in ascending order of ClientAdvisorManager FormRef and Date Received. 4. List ClientAdvior Manager, FormNo, FormRef, Department of the first 2 forms which are rejected by John Lim, one of the checkers. 5. List all DateComplete, FormNo, FormRef, ClientAdvior Manager, Department for any form rejected with rejected reason contain the word "Incomplete, sort their information in the descending order DateComplete, descending order of FormRef and ascending order of ClientAdvior Manager, 6. List the Checker and the number of forms not yet checked in the collection. 6. List the Checker and the number of forms not yet checked in the collection. 7. Tabulate for each department, the total number of supporting documents received in the collection. 8. Find the number of forms that has been checked by checker but not approved by the division head yet. Your tasks are : 1. Analyse the common queries mentioned above. 2. Create a new database in MongoDB with appropriate design of collections that can support the queries above, 3. Prepare the necessary sample documents to be inserted into the collection(s) in order to support the queries, the output of the scripts must be representative for all scenarios. 4. Create the scripts that will fulfill the queries required