CIS3010: Assignment 1
For this assignment, we will use the Brewers database chapter 4 that comes with the textbook. The script to create the databases is included as a file in the assignment 1 section of the course website as well as week 2 on the study desk. You will need to run the scripts to create the needed database tables. If you are using your own Oracle insulation the script should work as normal. Please submit the CODE in readable format (NOT SCREEN CAPTURE) and OUTPUT for all programming questions. You will submit this assignment electronically in the ASSESSMENT area of the Study Desk.
Please Read Carefully.
There are 4 questions that valued a total of 100 marks. You need to provide a well-structured and documented solution to the problems.
With your code solution you also need to provide the source code in editable format copy and paste in the submission file (NOT SCREEN CAPTURE).
The resulting output of the running solution you can use the screen capture or text copy and paste into the document. You can use the screen capture features of the OS you are using. For Windows, you can use the ‘print current window capture’ keystroke ‘Alt-Shift-Print Screen’. Depending on your keyboard you may have to use the function key as well. You can also use the default capture tool, search for ‘Snipping Tool’ in the start menu. For Apple Mac use ‘Command (⌘)-Shift-4’ and select screen to capture.
The structures and information about the tables are located in Appendix A (page 423) of the Casteel textbook. The structure of the tables used in this assignment questions is also reproduced at the end of the assignment specifications.
Write an anonymous block that places a substitution variable (&) into a local variable of type varchar2.
You will need to convert the types and round them to the nearest tens unit. For example, 84.4555 would be 84.5.
You should check the value entered in the local variable and output different messages depending on the value provided.
The anonymous block is a simple grade calculator with the following rules.
If a value less than 44.5 output the following message “Resulting Grade for VALUE marks is ‘Fail’”
If the value is 44.5 or more and less than 55.4 output the following message “Resulting Grade for VALUE marks is ‘Makeup Work’”
If the value is 55.5 or more and less 74.5 output the following message “Resulting Grade for VALUE marks is ‘Pass’”
If the value is 74.5 or more and less than 89.5 output the following message “Resulting Grade for VALUE marks is ‘Good Pass’”
If the value is 89.5 or more the output the following message “Resulting Grade for VALUE marks is ‘High Pass’”
If the inputted value is not in the range of 0 – 100 print an error message “ONLY VALUES BETWEEN 0 AND 100 ACCEPTED”
If the value is a string type then the system cannot convert to a numeric form the system should print “ONLY NUMERIC VALUES ACCEPTED”
Sample run and output:
Structured and documented code copied and pasted in the submission documents (not screen capture) to solve the problem listed.
Sample execution screens capture with output for the following test values:
44.44, ‘NULL’, 110, 74.45, 74.445, 90.07, 84.6766666
You will need to run your solution once for every test value and capture the resulting screen output.
NOTE: For Questions 2 – 4 your brewers’ tables should be upgraded to chapter 4 version for the capture of the processing. Download and upgrade your tables with the file located in week 2 on the Study Desk.
This question is designed to test your ability to mix cursors with procedural processing to arrive at a summarised solution. Your attempt should have all three parts in the solution (explicit and implicit cursors, as well as procedural logic, using loops and if statements).
Write an anonymous block that produces the report at the end of question 2. For this question your solution has to include a mixture of implicit and explicit cursors as well as a looping construct to find the summary of data listed below.
The report is to print the average of all orders placed.
The sum of all orders placed. You will have to test to see if the order has been placed or only recorded.
The number of all orders placed where the total for the order is more than the average of orders placed.
The number of all orders placed where the total for the order is less than the average of orders placed.
The total number of orders processed.
The total number of orders recorded but not placed.
The total number of records processed to arrive at the results.
The average total for all orders placed is $25.41.
The Sum total for all orders not placed is $419.82.
Number of records where the order was placed and the total is more than the average: 8
Number of records where the order was placed and the total is less than the average: 1
Total number of records found which match the more or less than average: 9
Total number of orders not placed: 5
Total records processed: 14
Structured and documented code copied and pasted in the submission documents (not screen capture) to solve the problem listed. We may need to execute your code as part of the marking process. The capture of the output from running the submitted code.
Question 3 (30 marks)
The solution to demonstrate the ability to retrieve and manipulate data to form the required output. The solution will require some thinking about the retrieval and storage of information from the tables.
Write an anonymous block that meets the following specification:
‘Customer ID XXXXX ‘First Name Character’. Last Name has TOTALORDERS’
The XXXXX is the customer number, ‘First Name Character’ (only the first character from customers’ first name followed by a ‘full stop’, ‘Last Name’ is the last name of the customer, TOTAL ORDERS is the total number of orders counted for her customer.
‘Processed XX shoppers with a total of YY number of orders recorded.’
Where XX is the total number of orders and YY is the total number of orders placed.
Customer ID 21 J. Carter has 2 orders.
Customer ID 22 M. Somner has 2 orders.
Customer ID 23 K. Ratman has 3 orders.
Customer ID 24 C. Sonnie has 2 orders.
Processed 4 shoppers with a total of 9 number of orders recorded.
PL/SQL procedure successfully completed.
Structured documented code copied and pasted in the submission documents (not screen capture) to solve the problem listed. We may need to execute your code as part of the marking process. The capture of the output from running the submitted code.
Question is designed to test the basic understanding of PL/SQL and the ability to solve a complex process using limited language constructs and procedural programming.
You are to write a PL/SQL anonymous block that processes tables with the use of implicit and explicit cursors to produce a sample report at the end of the question. The solution must follow the guidelines specified below.
The solution is to use only one implicit cursor to find the highest quantity of items on any one order (BB_BASKETITEMS). Use that information to generate the first 2 lines of the sample report. The quantity information is used for the rest of the application. Here we can use the MAX function.
All the other cursors must be explicit and must accept a parameter where the cursor selects a subset of the available records. For example an explicit cursor to process all the customers will access all the records (no parameter) while a cursor to process all the baskets for the customer will require a parameter (IDSHOPPER).
All explicit cursors are to use up to one table join and no subqueries or correlated subqueries.
All explicit cursors are not to make use of SUM/AVG/MAX/MIN/COUNT functions in its design. Any summarised values need to be calculated in the code.
All explicit cursors where a parameter is passed may use only one condition for limiting the records (where clause). No extra AND/OR conditions are to be used to limit the records the cursor processes. You can use the ORDER BY clause to pretty the report output.
The solution will need to use nested loops to manage cursor processing. The product description is limited to 30 characters. You may use other PL/SQL functions such as SUBSTR, TO_CHAR, and so on to manipulate the output strings to generate the report lines.
Structured and documented code copied and pasted in the submission documents (not screen capture) to solve the problem listed. We may need to execute your code as part of the marking process. Capture the output from running the submitted code.
Table Structures with field types
Please ensure that you do not share any information about your assignment with anyone. Do not accidentally leave printouts or USB drives that contain your assignment details where someone else can access them. Make sure not to store your assignments on a computer where another student can access your assignment.
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.