WTGRobotics is a startup company specialized in assembling toy robots. The company purchases robot parts, assembles them, and develop mobile applications to control the movement and sensory of the robot. The company has a database that manages its inventory and assembly. The database has:
Below are some of the processes that WTGRobotics used to manage their inventory and assembly line
When a new order for robots comes, John hood, the assembly manager, will search the inventory for the components of the robots.
If all the parts are found in the PartsInventory table, he will insert a new row in the RobotsInventory table. Giving a unique id to the robot_ID and enter 'ready for assembly' in the status column. He will also delete all the parts needed for this robot from the PartsInventory table and insert them into the RobotPrts table. He will have to give a unique sequential id for each of the rows that he enters to RobotPrts table.
If all or some of the parts are not available in the inventory, John will insert a new row in the RobotsInventory table. He will assign a unique id to the robot_ID and enter 'waiting on parts' in the status column. He will insert the existing parts in the RobotPrts table and follow the procedures explained in the above paragraph. Additionally, he will increase the value of NumMissingPrts column in part type table by 1 for the missing parts. For example if Switch is missing, john will search for the row that represent switch in PartType table and will increase its value by 1
Nancy, the procurement and inventory manager, is responsible for making the needed part available in the inventory. She queries the PartType table to find out how many parts need to be ordered. She orders those parts and reset the NumMissingPrts column to 0. When missing parts arrive, Nancy enters them to the PartsInventory table
Periodically John will search for missing parts in the PartsInventory table. If he find all of the parts needed to complete a robot, he will insert them into the RobotPrts table and remove them from PartsInventory table. Additionally he will change the status column to 'ready for assembly' .
You are asked to help WTGRobotics with automating their processes (make Stored procedures, triggers, and functions). The company uses oracle database and you choose to create a series of Oracle triggers, stored procedures and/or functions to automate some of the tasks John and Nancy are performing.
After preliminary design study you found out that you can automate the below tasks:
| Task # | Description |
|---|---|
| 1 | Write Stored Procedure(s), Trigger(s) and/or Functions(s) to automate (as much as possible) the task of populating robot_ID column when a new robot is added to RobotsInventory table |
| 2 | Write Stored Procedure(s), Trigger(s) and/or Functions(s) to automate (as much as possible) the task of populating status column when a new robot is added RobotsInventory table |
| 3 | Write Stored Procedure(s), Trigger(s) and/or Functions(s) to automate (as much as possible) the task of entering rows in RobotPrts and delete rows from PartsInventory table when parts exist for a robot |
| 4 | Write Stored Procedure(s), Trigger(s) and/or Functions(s) to automate (as much as possible) the task to updating NumMissingPrts column |
| 5 | Write Stored Procedure(s), Trigger(s) and/or Functions(s) to automatically notify Nancy with the change (only need to output the notification to the screen in this project) |
| 6 | Automate the process of inserting the parts in the RobotsPrt table when missing parts arrives to the inventory |
| 7 | Automate the process of updating the RobotsInventory table when all missing parts arrive for a robot |
You are required to write one to two page Microsoft Word report describing how will you achieve each of the above 7 tasks you are also required to deliver a .sql file that contains the code for the stored procedures and triggers. Make sure to clearly indicate the location of each task in the code.