Understanding `file_managed` table
I am just going to answer your first two questions (about file_managed).  Please ask a separate questions for the second set (about file_usage) and about the relation between the tables.


  What is public:// mean here? Does it translate to some root URL like http://example.com/files or similar?


Navigate to Administration » Configuration » Media » File system. Take a look at the value for "Public file system path" (default is sites/default/files).  this is the path to public:// from your site root.


  When there is a directory in the uri, what do they represent?


The path below the public file system root.

So, given the that the public path is the default, public://foobar/image.jpg, this expands to the path sites/default/files/foobar/image.jpg, or the URL  http://example.com/sites/default/files/foobar/image.jpg