Can the user define any relationship between Data Extensions that (s)he wants?

Yes, provided there is a field that can be used to define that relationship. This is usually a unique identifier or email address, but any field that you want can be used. Defining relationships is an important topic we cover during onboarding training.

We do recommend Salesforce Marketing Cloud customers, in general, to put good data governance in place, covering data relationships, and define a naming convention for Data Extensions and other assets in their environment.