Create UML diagram and create PL/SQL queries and tables
[url removed, login to view] UML notation, draw a class diagram for the Santas Toy Factory case study. diagram should identify all classes association classes association roles generalizations method names etc [url removed, login to view] the class diagram, produce an Object Relational Schema of design, and then implement this in Oracle 9i. This should include all object_type specifications, bodies member functions & procedures and object tables. You are required to produce listings of all code & make sure that code is well structured & documented. It is expected that you will use a range of ORACLE exceptions in your code to test for errors. You will need to use the SHOW ERRORS command to debug your code & it is advisable to build a script file containing the appropriate DROP TABLE and DROP TYPE commands, as the order of creation is important. In addition to your lecture and tutorial notes you may want to use the “Oracle9i Application Developer's Guide - Object-Relational Features?? available from otn.oracle.com. This part of the assignment requires you to insert data into your object tables. Please read this whole section carefully before attempting any [url removed, login to view] the appropriate tables with data from the Appendix, except any tables concerning toys (see ii, below). The data in the Appendix is sufficient for the testing purposes of this assignment. Write SQL queries to show the contents of these tables. You will need to supply both the code used and a screen shot of the results of the query to show data has been successfully entered. IMPORTANT: You will assume at this stage that Santa has not allocated any toys and all letters remained unactioned. In addition, Santa has not allocated any rewards that exist to any [url removed, login to view] populate the Toy object table(s), create an anonymous PL/SQL block or blocks which utilize the Member Procedure(s) you have defined in part b) to insert different types of toys. You will need to supply both the code used and a screen shot of the results of the query to show data has been successfully entered. d) Santa requires a routine to enable him to assign rewards to elves. Write an anonymous PL/SQL block which prompts Santa for the elves name, the reward name, and checks to see if the elf has enough points to be allocated the reward. If so then the reward_points for the elf are updated and the date of the reward is recorded as the system date. Make use of any Member Procedures / Functions you have created and show the results (screen shot) for the following cases. Your PL/SQL block should also display the number of points the elf has remaining.
e) As the system has been under development, the following children have had their requested toys completed: Peter Smith, Jenny Wren, Micky Mouse and Scooby Doo. Write an anonymous PL/SQL block, which utilises any member procedures / functions you have written, to allocate the above Toys to the correct children. You should ensure that the budget for the current year is also updated. A listing of the PL/SQL Block and a screenshot of the output is required. Santa has awarded elves Sam and Eva with 20 extra reward points. Create an anonymous PL/SQL block to update the appropriate object tables in your database. A listing of the code is required. Write two queries to show the updated data from both object tables and provide a screen shot of the results.g) Write an anonymous PL/SQL block which prompts the user to enter the year and displays to the screen the amount left to spend in the budget for the current year. Again make use of any Member Procedures / Functions that you have previously created. A listing of the PL/SQL Block and a screenshot of the output is required.