The main purpose of the alternate primary keys is to make data migration scenarios possible and avoid integer key clashes. For example if I want to combine data from multiple mojoportal sites into one site the rows will get new integer ids on the way into the db because of the auto increment interger primary key columns, but it will be possible to write queries to re-establish correct relationships by updating parent ids based on the guids.
For other tables there are some differnet reasons for guids as alternate primary keys. I plan to implement a content versioning system for example and many features will be able to keep history of content there and they will join on their guids rather than their integer ids. This enables many tables to join to the contentversion table because no guids will ever clash whereas aut increment integer ids are bound to clash.
Similarly, I plan to implement a comment system, content rating, tagging, etc using these guids so I can implement it once and share it across features. Each feature instance will retrieve its own stuff by its guid.