Homework#3ACS575 Database Systems, Spring 2021Due: April 8 (Thursday) Make one file (YourLastName_YourFirstName_ACS575_HW3.zip)) for your homework. Organizeyour submission with four directories, Part I_Q1, Part I_Q2, Part I2 and Part III, For Part I_Q1 and Part1_Q2, include the complete project files including source codes. Clearly number your answer with the question number. This homework description is based on Oracle. You can use any other RDBMS if the DBMS supports allthe functions HW3 required. If you use other DBMS, give the note in your homework submission.Part I. Database Programming with APIsDownload the CPO_schema_data.sql given, review the script file, and run the script file to create thefollowing tables in a SQL Server DBMS: Customer_T(CustomerID, CustomerName, CustomerAddress, CustomerCity,CustomerState, CustomerPostalCode)Product_T(ProductID, ProductLineID, ProductDescription, ProductFinish,ProductStandardPrice)Order_T(OrderID, CustomerID, ORderDate)OrderLine_T(OrderID, PRoductID, OrderedQauntity) We need to develop a database application program for the following tasks.Tasks: First, display the main menu which shows below functions:(1) Query the order frequency of products.(2) Query a customer(3) Query all the orders of a customer(4) Query all the order items of an order(5) Query all products(6) Insert a new product(7) Update a product price(8) Delete a product(9) Save (Commit)(10) ExitWhen the end user chooses a function number in the main menu, your program will conduct thecorresponding function. Implement all the functions ((1) – (10)) above.For the function (1), display product id, product description, product finish and order frequency in thedecreasing order of order frequency) like following:When then function (10), conduct “rollback” first and then exist the program.Show each function in your program is working with the following scenario:(1) Query the order frequency of products.(2) Query a customer (of CustomerID=1)(3) Query all the orders of a customer (with CustomerID=1)(4) Query all the order items of a order (of OrderId=1001)(5) Query all products(6) Insert a new product (with )(7) Update a product price (of ProductID=10 with a new value 200)(8) Delete a product (of ProductID=10)(9) Save (Commit)(10) Exist1. Develop a JDBC program with Oracle DBMS for the application, and show your program is correctlyworking in (1)-(10). A window form based GUI is required. A console-mode user interface is fine for thisassignment2. (Extra 5 % of total grade) Develop a C# program with .NET framework and SQL Server DBMS forthe application, and show your program is correctly working in (1)-(10).NOTE: This programming assignment will be counted for extra 5% of total grade. Include a nice windowform GUI for this application program.For Part II and Part III, Download “movie_actor_schema_data.sql” in your working directory and review it.Execute movie_actor_schema_data.sql in your database account.And then, execute the following ANALYZE statements so that the DBMS can collect some statisticalinformation of each table. ANALYZE TABLE movie COMPUTE STATISTICS;ANALYZE TABLE actor COMPUTE STATISTICS;ANALYZE TABLE casting COMPUTE STATISTICS; Answer the following Part II and Part III questions. For SQL questions, submit the SQLstatements and also the results (e.g., result screen shots).Part II. Physical File Organization1. Write a SQL statement to retrieve the information of actor, casting and movie tables usingUSER_TABLES system catalog(/data dictionary) table. The query result needs to show number of rowsin a table, number of data blocks used for the table, average available free space in the table, number ofrows in the table that are chained from one data block to another, average row length, average freespaceof all blocks on a freelist.For USER_TABLES, refer tohttps://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN262862. Write a SQL statement to retrieve the index information of actor, casting and movie tables usingUSER_INDEXES system catalog(/data dictionary) table. The query result needs to show index name,table name, index type, uniqueness, the index height, number of leaf blocks in the index, average numberof leaf blocks per key and average number of data blocks per key.For USER_INDEXES, refer tohttps://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_2534.htm3. The USER_INDEXES does not show indexed column information. Write a SQL statement to retrieve theindexed column information of actor, casting and movie tables. For that you may needUSER_INDEXES and USER_IND_COLUMNS. In the query result, include index name, index type,table name, uniqueness, column name, and column position.For USER_IND_COLUMNS, refer tohttps://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_2534.htm4. Write a SQL statement to retrieve user account information (username, account_status, default_tablespace,temporary_tablespace, and created date) of your account user USER_USERS For USER_USERS, refer to https://docs.oracle.com/en/database/oracle/oracledatabase/18/refrn/USER_USERS.html#GUID-8BA93748-F8D0-4868-AD67-0CD6EA68EC4E Part III. Query Processing and Query OptimizerIn the following questions, we will examine query execution plans chosen by the optimizer. You canquery the query plans from plan tables or simply can see it using “Explain” icon in Oracle SQL Developer.1. Recall that there is a secondary index on the VOTES column of MOVIE table. Consider following SQL queries for the VOTES based range queries.o S1: SELECT * FROM movie WHERE votes BETWEEN 0 AND 1000;o S2: SELECT * FROM movie WHERE votes BETWEEN 0 AND 50000; Run the explain plan for each S1 and S2 using the instructions given above, and submit the output. Compare the two execution plans. Explain why the index on VOTES is not always used forrange queries based on VOTES attribute.2. Recall that there is a secondary index on the NAME column of ACTOR table. Consider following SQL queries.o S3: SELECT name FROM actor WHERE name like ‘W%’;o S4: SELECT name FROM actor WHERE substr(name, 1, 1) = ‘W’;o S5: SELECT name FROM actor WHERE name LIKE ‘%w’; Rerun the explain plan for each S3, S4 and S5, and submit the output. Compare the execution plans of query S3, S4 and S5. Explain why the index on NAME is notalways used for queries based on NAME attribute.3. Recall that there is a secondary index on the VOTES column of MOVIE table. Consider following SQL queries for the VOTES based range queries.o S6: SELECT votes FROM movie WHERE votes < 1000;o S7: SELECT title FROM movie WHERE votes < 1000; Run the explain plan for each S6 and S7, and submit the output. Compare the two execution plans. Which query uses the Index-only plan? Explain the reason.4. Recall that there are indices on TITLE and VOTES. Consider following two equivalent SQL queries for retrieving titles of movies with votes larger thanthat of Star Wars.o S8: SELECT title FROM movieWHERE votes > (SELECT votes FROM movie WHERE title = ‘Star Wars’);o S9: SELECT m1.title FROM movie m1, movie m2WHERE m1.votes > m2.votes AND m2.title = ‘Star Wars’; Run the explain plan for each S8 and S9, and submit the output. Is there any difference in the execution plans for S8 and S9?5. Consider following SQL queries.o S10: SELECT m.title FROM movie m WHERE m.title= ‘Scrooge ‘o S11: SELECT /*+ FULL(m)*/ m.title FROM movie m WHERE m.title= ‘Scrooge ‘ Run the explain plan for each S10 and S11, and submit the output. Compare the execution plans of query S10 and S1. Explain the difference.
