26

In english, we might talk about the relation between, say, Bob and Tim. Perhaps they're cousins. The term "relation" in this context makes sense to me.

In the context of relational databases, I understand what the term refers to, but I don't understand why it is used. I figure that understanding why it is used will help me to better understand the field, so I'd like to understand why it is used.

  • Why is, for example, a Person, considered to be a "relation"? In english, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?
  • I understand that relational model came after the hierarchical and network models (ex. parent, neighbor). But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?
  • What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

(Perhaps this should be multiple questions. I figured that the answers are highly related - perhaps there's just one answer - so I figured it'd make sense for this to be a single question. If I'm wrong, let me know and I'll create separate questions instead.)


Edit: This diagram may be useful to visualize that a relation is relating different domains to one another:

enter image description here

33

First of all, I highly recommend the scientific paper in which Dr. Edgar Frank Codd published the relational framework to the general public in 1970, i.e., A Relational Model of Data for Large Shared Data Banks. There, in section 1.1, “Introduction”, Dr. Codd himself states that:

This paper is concerned with the application of elementary relation theory to systems which provide shared access to large banks of formatted data.


© Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

So, yes, the terms relation and (hence) relational come from a mathematical background. Dr. Codd —who, apart from his academic and research credentials, had about 20 years of first-hand experience in computing and information processing— envisioned the enormous advantages of applying the relation (an abstract construct, naturally) in the field of data administration.

I am not a mathematician but, basically speaking, a relation is an association between sets, a set being a collection of elements (this external resource gives a definition of mathematical relation that may help to understand it from a different perspective). When working with the aid of a SQL database management system (DBMS for brevity), a well-known approximation of a relation is a table, in which case the association takes place between the types of its columns. Evidently, in SQL platforms that do offer DOMAIN support (e.g., Firebird and PostgreSQL), the association occurs between the domains fixed for the columns of the table in question; see the sections below for significant details.

In that respect, I am going to cite again Dr. Codd, who in section 1.3, “A Relational View of Data”, asserts that:

The term relation is used here in its accepted mathematical sense. Given sets S1, S2, ⋯ , Sn, (not necessarily distinct), R is a relation on these n sets if it is a set of n-tuples each of which has its first element from S1, its second element from S2, and so on.1 We shall refer to Sj as the jth domain of R. As defined above, R is said to have degree n. Relations of degree 1 are often called unary, degree 2 binary, degree 3 ternary, and degree n n-ary.

1 More concisely, R is a subset of the Cartesian product S1 × S2 × S3 ⋯ × Sn.


© Association for Computing Machinery. Communications of the ACM, Volume 13, Issue 6 (pp. 377-387), June 1970.

And I agree with other answers in that it is very relevant to point out that Dr. Codd made some adaptations to the mathematical relation in order to get the most out of it regarding data management, and they are explained in the paper referred to before and throughout his extensive bibliography.

Relation and relationship

A situation worth bringing up is that, when dealing with these subjects, there may arise confusion due to the similarities that exist regarding the everyday (non-mathematical, non-technical) definitions of the terms relation and relationship —which, as a non-native English speaker, I find particularly understandable—.

The entity-relationship view and the relational model

Other factor that I think may as well cause confusion (and is closely associated with the technical connotations of the two terms brought up above) is that, when learning to design databases, a student or practitioner is typically first introduced to the methodology proposed by Dr. Peter Pin-Shan Chen in the entity-relationship view of data (published in 1976), which suggests two different implements (i.e., the entity and the relationship) to delineate a conceptual schema, and then, only after the definition of said schema is stable, the student or practitioner is introduced to relational terms and instruments (e.g., the relation) when declaring the logical layout of the pertinent database. Within the conceptual frame of reference, relationship holds connotations that are much more closer to the everyday sense of the word.

Then, perhaps, that circumstance also adds to the relation and relationship issue —but the sequence of firstly defining the conceptual schema and subsequently declaring the corresponding logical design is of course quite appropriate, as I will detail in the following sections—.

Responses to each of your subquestions

I consider that having included those three subquestions is really pertinent because they establish a broader context for the post, so they should not be overlooked. In this way, apart from exclusively addressing why the terms relation and relational are used (which certainly is very significant and is the title of the post, but it is not the entire post), said subquestions can assist in comprehending more of the scope of the relation and the relational model when one is involved in a whole information management project (quite relevant since this a site about database administration) and is therefore working at different levels of abstraction. In this manner, I am going to share my take on those particulars below.

Subquestion no. 1

Why is, for example, a Person, considered to be a "relation"? In English, a relation is a noun that describes how two entities are associated. It doesn't refer to the entities themselves. In the context of relational databases, "relation" refers to the entities themselves. Why?

Conceptual level

In a given business environment, Person can be considered an entity type depending on how the people who work there (business experts and database designers) conceptualize it. And, yes, in that business environment, there may be different properties of interest with respect to the Person entity type, e.g., Name, BirthDate, Gender, etc.

Moreover, the Person entity type may hold certain relationship (or association or connection) types with itself or other entity types; e.g., Person may be associated with an entity type named UserProfile, which in turn may have its own properties of interest, let us say, Username and Password.

But, (a) the entity types, (b) their corresponding properties, (c) the relationship types between entity types and (d) the relationships between the properties themselves are notions that “belong to” the particular business environment in which they are deemed of significance. They are devices used by database designers that work closely with business experts in order to define a context-specific conceptual schema, at the design phase.

Thus, at the conceptual level we basically work with the structure of the ideas that arise in the real world’s segment of interest, i.e., (1) prototypes of things and (2) prototypes of relationships between prototypes of things, we do not work with (3) relations —employing this last term in the sense of the relational framework of data—.

Logical level

After Person was precisely delineated as an entity type at the conceptual level, and if one wants to implement a relational database that conveys the meaning of Person and all the concepts associated with it, then the facts about entities of that type can be managed by virtue of a mathematical relation at the logical level, and take advantage of the science based operations that can be performed on that abstract construct (i.e., define it, constrain it and manipulate it).

Yes, one can name a certain relation Person when defining the logical arrangement of a database, but that does not transform the “real world” concept of Person into a relation, one approaches it as such because of the benefits that are obtained when managing information about it, e.g., applying relational algebra operations on it to derive new relations (and therefore one is deriving “new” information). Said benefits become more evident taking into account the fact that the entities of a certain type make up a set, and the values of a certain property make up a set too.

And, yes, as mentioned in preceding paragraphs and in other answers as well, one of the paramount aspects of a relation is the connection that exists between its domains —that are typically used to represent the properties of entity or association types that are part of a conceptual schema—. For example, let us say that we have declared the following (ternary) relation:

  • Salary (PersonNumber, EffectiveDate, Amount)

…and let us suppose that, in the business environment in question, the tuple —which (i) stands for a particular entity, i.e., an instance of an entity type from the applicable conceptual schema, and (ii) whose SQL counterpart is a row

  • Salary (x, y, z)

…would carry the meaning

  • “The Salary payed to the Person identified by PersonNumber x on EffectiveDate y corresponds to the Amount of z.

Accordingly —to describe things in an approximate manner—, the connection between the three domains is of prime importance, they are all related (and, yes, a unary relation would involve one domain only). The connection among all the values of a certain domain is very significant too, as they constitute a set of a precise type. Also, the contents of each tuple of the Salary relation must fit in the structure of the assertion illustrated above.

Conceptual-level relationships and logical-level relations

As demonstrated, I have now dealt with database management at two different levels of abstraction, namely conceptual and logical —and there is yet a lower level known as the physical one, which in SQL DBMSs typically involves, e.g., indexes, pages, extents, etc.—.

So, in accordance with the notions explained before, at the logical level one works exclusively with (a) mathematical relations, where (b) the conceptual relationships or associations are represented by (c) the values contained in the tuples of such mathematical relations, and said values are usually delimited via FOREIGN KEY constraints so that they can represent the applicable relationships accurately.

And, yes, associative entities, i.e., instances of relationship types with a many-to-many (M:N) cardinality ratio, can be conveyed by way of the tuples of a single mathematical relation —with the corresponding constraints declared appropriately, of course—.

Subquestion no. 2

I understand that relational model came after the hierarchical and network models. But in those models, the entities also have relations to one another. So why call this model the relational model? Is there a more specific phrase/term? Or maybe we should say that all three models are relational models, but the hierarchical and network models are specific types of relational models?

Network and hierarchical DBMSs preceded their formal theoretical support

It is opportune to point out that the theoretical support around the hierarchical and the network approaches was, in fact, created in terms of previously existing DBMSs, with the aim of, among other aspects, testing and establishing the soundness of (1) said kinds of software and (2) the linked data management practices —an upside-down phenomenon, from my point of view—.

Incomplete in comparison with the relational framework

That being said, although there are hierarchical and network DBMSs that predate the relational model, and even when Dr. Codd referred to each of those approaches as a “model”, none is defined as such in the same way that the relational framework is. The relational paradigm provides scientific constructs for the (i) definition, (ii) restriction and (iii) manipulation of data, and the hierarchical and network approaches lack full theoretical support to cover all of the three sorts of constructs previously mentioned.

Network and hierarchical features

Also, as stated before, the entity and relationship types are conceptual-level devices, they do not belong to the hierarchical or network approaches, each of which offers particular mechanisms to represent said aspects:

  • The network paradigm entails two devices for data representation, i.e., nodes and arcs (and that characteristic of course implies two different kinds of data manipulation operations) which, when contrasted with the relational model that (as per the information principle) requires only one construct (the relation), makes evident the needless complexity that working in a network fashion involves. For instance, given that it resorts to two representation instruments, the network approach imposes an impractical query bias that hinders data manipulation.

  • For its part, the hierarchical view proposes representing the data by way of (physical!) files made up of records (which in turn consist of fields) organized in a three-like arrangement; i.e., one parent record chained with possibly many child counterparts via pointers, which produces a physical access path with regard to data manipulation. This approach is also unfavourable because it presents a entanglement among conceptual and physical aspects, so the changes in the physical storage arrangements require a reorganization of the data structures, which in turn demands changes in the concerning data manipulation operations.

As shown, the hierarchical and network views impose their constructs on the data to be managed, whereas the relational model proposes administering the data elegantly in its natural structure by means of sets of associated facts (from which n subsequent types of sets, not anticipated at the design phase, can be derived and so on!).

The relational model does not have sub models

And, quite important, neither the hierarchical nor the network views are specific types of relational models, they are simply other paradigms that someone may follow to (a) build DBMSs and to (b) create databases, but please bear in mind that the hierarchical and network approaches are considered obsolete for decades now.

Subquestion no. 3

What if we have standalone entities that don't relate to one another. Say, Person, Door, and Tree. Is the term "relation(al)" still applicable?

Yes, it is perfectly applicable if one is (1) managing information about those entity types by dint of adapted mathematical relations and (2) performing the applicable relational operations at the logical level in a certain database administered with the support of a given relational DBMS.

It does not matter if, at the conceptual level, said entity types hold no relationship types with other entity types (and it is worth noting that an entity type can have a relationship of one-to-zero-one-or-many cardinality ratio with itself), and thus one is not conveying nor enforcing any relationship between the values of the tuples of the relations under consideration.

  • 1
    I don't think being a "non English speaker" is necessary to misunderstand or be confused by the term "relation". Unless you've studied that particular area of mathematics, it's a completely alien definition. To be honest, if I didn't know what a "relation" meant in this context, this answer wouldn't particularly help, interesting though some of it is. – IMSoP May 20 '17 at 16:39
  • 1
    @IMSoP I haven't noticed it before, but my intention was to write "non-native English speaker" so I have completed the relevant excerpt now. On the other hand, I disagree, this answer does particularly help because I have addressed (1) the question's title and (2) all the subquestions contained in the question's body, which contextualize the post more broadly. But, of course, you are entitled to your own opinion. – MDCCL May 22 '17 at 18:51
16

The interesting thing behind 'relational database' is, that it does not (primarily) refer to the relations between tables, as you might expect, but it refers to the relation of multiple properties (columns) in a tuple. A relational database stores those tuples as a row in a table.

It's based on the relational algebra defined by Alfred Tarski in his 1941(!) paper On the calculus of relations. He summarized the history of the term and usage in symbolic logic but defined the operations which in the end became the foundation for SQL.

Codd turned this into a definition for what can be understood as a relational database in his 12 commandments.

10

The term "relational" comes from mathematics and has nothing to do with relationships between entities. I'm not a mathematician (whereas Codd had a PhD in Mathematics) and so won't elaborate, but will point you to this wikipedia article on binary relations. The wikipedia entry on relation (databases) gives additional detail on how Codd adapted the mathematical concepts to apply to data management. As to why this mathematical structure is called a relation, I think it has to do with the idea that there is a "relationship" between the domains that make up the relation. The best source I know of to better understand Codd's original thinking is Fabian Pascal's Practical Database Foundations and Understanding the Real RDM series of papers. Chris Date has also written extensively on the RDM and his Third Manifesto site has a section listing papers and books. His book Relational Theory for Computing Professionals is a good introduction. I hope this helps.

7

It's an intuitive name when you think of them with natural keys. You can think of a cell value as representing an entity.

Relation: Employee
|--------+------------+--------|
| name   | job        | boss   |
|--------+------------+--------|
| Mark   | owner      | NULL   |
| Bob    | manager    | Mark   |
| Jane   | supervisor | Bob    |
| Claire | supervisor | Bob    |
| John   | cashier    | Jane   |
| Jesse  | cashier    | Jane   |
| Jason  | cashier    | Claire |
|--------+------------+--------|
  • The employee name "Jane" is related to the job "supervisor".
  • The employee name "John" is related to the boss "Jane".
  • The job "cashier" is related to the employee names "John", "Jesse", and "Jason".
  • The job "cashier" is related to the bosses "Jane", and "Claire".
  • I find this answer the most intuitive, but not as comprehensive as MDCCL's. The combination of this answer plus MDCCL's answer is very satisfying to me. – Adam Zerner May 20 '17 at 17:51
6

You have already accepted a very long answer that has to say a lot about databases, but let me answer the question you actually asked:

Why the term "relational".

Because a table is a concrete instance of the mathematical object "relation".

Let's see what Wikipedia has to say about the term "relation" (in mathematics, not RDBMS), and then translate it to databases:

Formally, a relation is a set of n-tuples of equal degree. Thus a binary relation is a set of pairs, a ternary relation a set of triples, and so forth. In the language of set theory, a relation between two sets is a subset of their Cartesian product.

Mathematics             | RDBMS
========================|===============
A relation is           | A table is
a set of                | a bunch of 
n-tuples                | rows
of equal degree.        | with the same cell (a.k.a. column) types and sizes.

It continues with set theory. Remember that this is mathematics, so much more abstract than database stuff. The last sentence thus is

a relation between two sets is a subset of their Cartesian product.

This translates to a one table with two columns:

  • Let's call column A "name". Its mathematical set A is the set of all (human) names.
  • Column B I call "city". Its mathematical set B is the set of all cities.
  • The cartesian product A x B (in mathematics) is a new set which contains of all pairs (a.k.a., tupels) (a, b) where a is a member of A, and b is a member of B. I.e., a is a name and b is a city. Examples would be (Alice, New York) or (Bob, Hollywood). But the cartesian product is not only a few of those, but all of them. A relation, to get to the point, is a subset of that cartesian product. In other words, the relation is (defined to be) any amount of pairs (a, b) where a is a name and b is a city, even none at all.

Now I hope all starts to make sense. In a RDBMS, the rows of a table simply pick out the subset of the cartesian product of all possible combinations in those columns. Which is, when using a RDBMS, completely trivial and irrelevant.

But since Computer Science, including relational databases, does have its roots in mathematics, we are blessed with the term "relational" here. It is wholly abstract and has nothing whatsoever to do with relationships between people or what have you.

As an aside, the term "relation" is also sometimes used for "association", and it's exactly the same (here, the underlying sets of the relation being themselves relations as described above (a.k.a., tables)).

N.B.: in mathematics, relations are not about databases, but are something like functions, just more general (please, all you mathematicians, don't start to nitpick now, we are in dba.SE, not math.SE; I am aware that this is the wrong way around :) ). A function like f(x)=x+1 also can be expressed as a set of tuples (1, 2), (2, 3), ..., but it can only have every number once, on the left hand of the tuple. I.e., this would not be a valid function: (1, 2), (1, 3), .... But the latter would be a valid relation; i.e., you can have a Bob in New York and a Bob in Hollywood.

5

Relational databases is based on the relational model of E.F.Codd. The relational algebra describes methods of how to query data. A relation is simply a subset of the crossproduct of some sets (domains).

Example

We have the following sets:

DepIds = {1, 2, 3, ...}
EmpIds = {1, 2, 3, ...}
DepNames = {'Engineering', 'Finance', 'Sales', ...}
FirstNames = {'John', 'Walter', 'Mary', 'Roxane', ...}
LastNames = {'Smith', 'Bondy', 'Taylor', ...}
BirthDates = {..., 1950-01-01, 1950-01-02, ...}
Jobs = {'Accountant', 'Programmer', 'Database Administrator', ...}

Further we have the sets of tuples

departements = { 
    (1, 'Engineering'), 
    (2, 'Finance')}
employees = { 
    (1, 1, 'John', 'Taylor', 1985-03-22, 'Programmer'), 
    (2, 1, 'Walter', 'Bondy', 1997-09-11, 'Database Administrator'), 
    (3, 2, 'Roxane', 'Myers', 1987-12-19, 'Accountant')}

departements is a subset of

    DepIds x DepNames

and so it is a relation.

employees is a subset of

    EmpIds x DepIds x FirstNames x LastNames x BirthDates x Jobs

and so it is a relation, too.

It is evident how relations can be implemented by tables.

Why do mathematicians call a set of tuples a relation?

Usually such properties like '2 less than 3', '4 equals 4', '2 is between 1 and 3.4', '-1 is negative' are called relations.

The relation 'less than' on the set A={1, 2, 3} is defined by the subset

{(1, 2), (1, 3), (2, 3) }

of

A x A = {1, 2, 3} x {1, 2, 3}=
{ (1, 1), (1, 2), (1, 3), 
  (2, 1), (2, 2), (2, 3), 
  (3, 1), (3, 2), (3, 3) } 

In a similar way other relations can be seen as a subset of a crossproduct. 'x less than y', 'x equals y' are binary relations and therefore defined by a set of pairs. 'x between y an z' is a ternary relation' and therefore defined by a set of triples. 'x is negative' is a unary relation and therefore defined by a set of singletons.

The departments tuple set we defined above is a binary relation, the employees relation is a 6-ary relation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.