Check out this documentation. Each page type is coupled with at least 2 other tables when retrieving the data within a standard Kentico control (ie: repeater) or API call. For instance the Article page type. When you retrieve the Articles for a site using a repeater, it will call the VIEW view_content_article_joined which joins the cms_tree (to get node info) and the cms_document (to get page info) with the content_article table. Any of the "node" columns live int the cms_tree table. Any of the "document" columns live in the cms_document table.
There is no centralized place to view any of these columns within the UI, you'd have to execute the query from within SSMS.
You can use Eval("ColumnName") to retrieve any of the columns in that view if you do NOT specify a list of columns in the column property. IF you specify a list of columns in the column property, ONLY those columns will return data and your Eval("ColumnName") statement will not return anything.
Transformations are NOT just limited to page types, they can also be used for external sources. In this event, the actual data is not stored within Kentico but you want to use a webpart to display the data. A good example of this would be an RSS feed from an external source. I do not know of any documentation that states which columns are required but I do know if you want to sort by a specific column name or want to display specific data you need to include those columns in your query. The reason it's recommended to limit the columns is because the views bring back over 150 columns.
Hope this helps, good luck!