SOLUTION !!
you have to edit DbTable.php in the installation zip file before installing. You can download installation file from prestashop addons website. See picture for full filepath and what to remove. The problem was with the part "COLLATE=DEFAULT" mentioned in previous post, which was not recognized by my database version MySQL 8.0. I have some information that the best databases to use with prestashop are 5.6 or 10.1.
After removing part shown in the picture, the module installs correctly and the tables in database are also correctly created.
