Those are good questions, hopefully I can clear things up for you as well as for other users.
When installing the plugin, it performs various functions to increase security while activated. After deactivation, all effects of the plugin are gone, everything goes back to the way it was before the plugin (unless you used it to change the database table prefix names obviously). The plugin creates no database tables, and doesn't change any files.
As for changing the database tables, this only happens if you tell it to. You can choose not to change any table names, or you can follow the link in the plugin to the instructions on how to manually change the table names. Changing the table prefix names from the default is a common security practice in a lot of software, including WordPress.
The problems people have had with this is when they don't have ALTER privileges for the database user, and failed to read the sentence above the button, which says that the database user WordPress is running as requires ALTER privileges. Your wp-config.php must also be writable, which is also stated directly above the button in the plugin.
The only known problem, other than user error, is that some plugins are dependent on the WordPress version, which is hidden by WP Security Scan. There is another way for these plugin authors to do this, and as people tell me a particular plugin doesn't work with WP Security Scan (which 100% of the time is for this reason) I contact the appropriate plugin author and let them know how to change their code so that it both plugins will work together.
At the moment I know of no plugins that currently conflict with WP Security Scan, but if anyone finds one, just let me know and I'll tell the author or you how to change that particular plugin (it's an easy fix).
100% of the issues people have mentioned on the forum fall into 1 of 2 categories:
1)the plugin was in Beta at the time
2)user error (not reading the two lines directly above the button before pressing it)
In any event, you should make regular backups of your files and database. It takes only a few seconds to back up the database, and depending on the file backup method, not long for that either.