1) Just to be clear, Activity_1 is an alias of the exact same table.
2) I'm not sure why there's a line from Activity to Activity_1. The table is only related to itself through Activity_Relation. You should not have a Parent Activity field on the Activity table - that's duplicating the purpose of the Activity Relation Table.
3) Regarding "manually populating the relation table". Whatever relationships you now have can be ported over programmatically. Any time you "break down" a Goal into responsibilities, or a responsibility into activities, you will be populating both the Activity table and the Activity_Relation table. When a user selects a Goal, for example, and wants to add a new responsibility related to that Goal, you create the Activity Record for the Responsiblity, then create the Activity_Relation record for that combination of Goal and Responsibility. Somewhere else, you'll probably have a screen to select an existing Goal and an existing Responsibility and create a record that relates them.
4) Yes, you can create one big inner join that will return all the info like that, but you have to think in terms of what is on the final record. The join will link each activity to each responsibilty it is connected with, and then to each goal it is connected with. If an activity is linked to two reponsibilities, and one of those reponsiblities is linked to two goals, then there will be three detail records with that activity on them. (This is a strength of the design, not a problem, but you have to be aware of it when you design your reporting.)
Code:
SELECT
TA.ActivityID,
TA.ActivityName,
TR.ActivityID,
TR.ActivityName,
TG.ActivityID,
TG.ActivityName
FROM
(((Activity AS TA
INNER JOIN ActivityRelation AS TX1
ON TA.ActivityID = TX1.ChildID)
INNER JOIN Activity AS TR
ON TR.ActivityID = TX1.ParentID)
INNER JOIN ActivityRelation AS TX2
ON TR.ActivityID = TX2.ChildID)
INNER JOIN Activity AS TG
ON TG.ActivityID = TX2.ParentID)
WHERE {{test that TG is a goal record}};