Basic Database Relationship Advice?

Question

I am finding it difficult to get together in a single place the different types of relationships used for different things and would like to find out which I have correct and incorrect and the proper "technical" terms for the different types of relationships. An example:

In database we have:
One to One
One to Many
Many to Many

I beleive (although I may be wrong hense the question) that these types of relationships are collectively termed "Multiplicity" relationships. Is this correct, and if so, are there other names for it.

In programming we have:
Ihneritance
Encapulslation
Polymorphism

I beleive (and may be very wrong here) that these are different types of "Compound" relationships. If they are all the same type of compound relationship then is there other names for it. If they are different, how are they named.

I would also like to know if there are other relationship types in general database / programming world that would be used to some degree without going into obscurity like some sort of unused sci-fi "molocular mumbo jumbo bumbo" relationship. Just keeping to what would be generally useful in the real world.

I think I have a good understanding of the different relationship types, its more the terms used to describe them, and comming up with a brief 2 or 3 line description that really sums it up (assuming someone has allready got a fair understaiding).

Knowing the technical terms properly and clearly will help me should I need to advise others on what to look for elsewhere.

Can anyone offer suggestions on this for me without getting overly technical. Thanks in advance.

Answer 1

Inheritance: "Is A Type Of"
Encapsulation: "Is A Part Of"
Polymorphism: "Call the method having the specified number and type of arguments"

Inheritance is a one-to-many only in those languages that support multiple inheritance, like C++. In C#, it's a one-to-one with classes, but a one-to-many with interfaces.

Encapsulation is a one-to-many: one class object, many members.

Polymorphism is a one-to-many: one method name, many argument signatures.

Answer 2

The terms "One to Many" and family actually aren't part of the relational model at all. They are instead part of the entity-relationship language that's used for describing systems. It happens that entity relationship analysis is a convenient way to build a relational model of a system, and the relational model has the advantage of many efficient (near) implementations (in the form of SQL databases).

There are corresponding notions in the relational model, that map closely to the ideas of one-to-many and similar; namely functional dependence. By carefully designing the features of the fields of particular tables, it's possible to enforce or allow, at the schema level, the "multiplicity" of dependent data. This mostly takes the form of specifying certain columns be unique or nullable, other columns to have foreign key constraints of a particular sort, and specifying the particular range of values a particular column is permitted to take.

• When a relation (in the relational model sense, think 'table') has a functional dependence on a key (some unique combination of columns in a table) in some other relation (even the same relation itself), this happens to mean that there is a unique row in that second relation (called the 'referent' or 'independent') that is associated with the row in the first (called the 'reference' or 'dependent'), although more than one row in the dependent relation may have this property for a given row in the referent, This is how a one-to-many entity-relationship is described in the relational model.
• In the above example, if the columns in the dependent relation that reference the other relation are themselves a key (unique), then instead of being one-to-many, the entity-relationship is one-to-one, since only one row in the dependent table can have that foreign key value.
• If a relation is dependent on two separate relations (or the same relation on two, distinct attributes), then it happens that there is a many-to-one entity relationship from one referent to the dependent, and another one-to-many entity relationship from the dependent to the other referent, which indirectly implies a many-to-many entity relationship between the two independent relations. It's possible to express an association between any combination of rows in the two relations.

The entity-relationship approach of describing systems applies equally to other representations of systems, it maps quite naturally to the compositional aspect of OOP. The fact that ER works so naturally with both OOP and RM is a good reason why the terminology of ER leaks into many object-relational mapping libraries.