1. Create a 'Part_Structure' table as follows (key attributes are underlined): (5 points) Part_Structure Table: 2. Write a Java program (use JDBC to connect to the database) that implements the following function (written in pseudo code) that prints out the subparts of a part using a depth-first search, and the branch with a smaller part number will be searched first (where there is more than branch): (20 points) CALL RECURSION ( GIVENP\# ) ; RECURSION: PROC (UPPER_P\# ) RECURSIVE ; DCL UPPER_P\# ... ; DCL LOWER_P\# ... INITIAL (' ' ); EXEC SQL DECLARE C CURSOR FOR SELECT MINOR_P\# FROM PART_STRUCTURE WHERE MAJOR_P\# = :UPPER_P\# AND MINOR_P\# > :LOWER_P\# ORDER BY MINOR_P\#; print UPPER_P\#; DO "forever"; EXEC SQL OPEN C ; EXEC SQL FETCH C INTO :LOWER_P\# ; EXEC SQL CLOSE C ; IF no "lower P\#" retrieved THEN RETURN ; END IF ; IF "lower P\#" retrieved THEN CALL RECURSION ( LOWER_P\#) ; END IF ; END DO ; END PROC ; Given the value of the input parameter ' P1 ', it should print out the following sequence (in 3. Please further expand the function you implemented in (2) to allow the printing of the number of each unique "leaf part" in a sub-parts-structure tree as the example shown below: (20 points) In this example, given the part ' P1 ' and its sub-parts-structure tree, there are 293+4393= 378 'P6' parts and 418=32 ' P7 ' parts. Therefore, given input value ' P1 ', the function should print out the following (you need to create a table with the data above to test your code): P6 378 P7 32 4. Implement the above two functions in PL/PGSQL. (22 points) See Appendix for PL/PGSQL tutorial. 5. Write a Java Program to simulate an interactive SQL interface like psql. It should allow the user to query and update the tables in the database. Make sure your program prints out the query result (with column headings) or the update status (e.g., the number of rows deleted/updated/inserted) (10 points) 6. (Bonus) Write a Java program to simulate a student information query interface where input can be provided for one or more of the following fields: student name, and/or student number, and/or DoB. Assume information about students are stored in a table Students(Snumber, Sname, DoB). The output should be all the qualified student records. A graphical user interface is NOT required. (8 points, no partial credits will be awarded. Hint: Dynamic SQL)