The following system which I want to be developed should not be copied from other existing system. After development, it should not be sold to anyone else.
Index Advisor System:
• Create the GUI interface system using Java language with following functionalities.
o Inputs :
The system will allow user to select postgreSQL database name (database should be big with huge data like database of any e-commerce application) from drop down list boxOn the basis of selected database, it will display all tables within this database User will select all/some tables from displayed tables.
Now, system should automatically take following as inputs to analyze the existing indexes and suggest new indexes which will improve the transaction performance.
• Current and previous workload related to these tables from the log files
• Constraints and indexes created on these tables.
• Generate standard queries for these tables using TPC-H or any other TPC benchmark. Frequency (how many times they are going to be executed) of these queries.
• Read and write operations (DML statements) executed for these tables with frequency.
• Required statistics from data dictionary.
o Process :
Develop an algorithm (develop new algorithm or modify existing algorithm) which will analyze the existing indexes and give suggestions for revised indexes which will improve performance of concurrent transactions.
o Outputs :
Analyze and suggest improved indexes for selected tables.
Show comparison of performance before and after applying the suggested indexes. It should be represented graphically.
Suggest improved table design(if any)
Suggest improved DML or select statements.
Note : For the selected database, the concurrent transaction execution should be improved after applying suggestions from this system. The system should be generalized. i.e., it should give suggestions for any postgreSQL database.