• I am a little confused about how foreign keys work, especially in many to many joins.

    Sure; that is confusing. Lots of people have trouble at first.

    I like to think of them a pairs, as in actor P was in movie M, so (P,M) is in the credits (acting credits) table.

    Example: P=Emma Watson, and M="Harry Potter and the Sorcerer's Stone", we add that pair to the credits table.

    Or rather, we add their keys to that table: (nm0914612,tt0241527)

    If there's additional information about that pair, we can add it to that table.

    Example: P=Emma Watson, and M=Harry Potter, we could add the role, R="Hermione Granger" or her salary or ...

    Consider: there's no place for that information in either Emma Watson's entry, nor in the movie's entry: no lists of data.

  • could you go more into the pros and cons of relational vs. non-relational databases?

    I just tried to, but here's a summary: