What is a relation in database terminology?

Question

When someone refers to a relation in a database course, what does that mean?

Answer 1

Amazingly, "relation" in "relational" databases does not refer to the foreign key relationship of one table to another. "A relation is a data structure which consists of a heading and an unordered set of tuples which share the same type," according to Wikipedia on 'Relation (database)'.

In SQL RDBMSes (such as MS SQL Server and Oracle] tables are permently stored relations, where the column names defined in the data dictionary form the "heading" and the rows are the "tuples" of the relation.

Then from a table, a query can return a different relation:

create table t (x number primary key, y number not null);

Table created.

SQL> insert into t values (1, 10);

1 row created.

SQL> insert into t values (2, 20);

1 row created.

SQL> select x from t;

         X
----------
         1
         2

select x from t returned a relation with fewer columns, tuples of fewer elements, than the base table had. And select x, y from t where x = 1 will return a relation with fewer tuples than the base table:

SQL> select x, y from t where x = 1;

         X          Y
---------- ----------
         1         10

An example using inner join:

SQL> create table s (x number primary key, words varchar2(100) not null);

Table created.

SQL> insert into s values (1, 'Hello World!');

1 row created.

SQL> insert into s values (3, 'Will not show');

1 row created.

SQL> select t.x, t.y, s.words
  2  from t
  3  inner join s
  4      on t.x = s.x;

         X          Y WORDS
---------- ---------- ---------------
         1         10 Hello World!

Conceptually, t inner join s on t.x = s.x goes through the following steps:

1. Take the cartesian product of s and t, which is to take each row of s and combine it with each row of t resulting in a tuple with size of s * size of t tuples or rows, each with all the columns from both s and t much like the results of:

   SQL> select * from s, t;

        X WORDS                    X          Y
   

   ---

        3 Will not show            1         10
        3 Will not show            2         20
        1 Hello World!             1         10
        1 Hello World!             2         20
   

(Or select * from s cross join t in the SQL-92 syntax) From the cartesian product containing four tuples/rows with four columns on s.x = t.x trims the tuples down to one, still with four columns:

SQL> select *
  2  from t
  3  inner join s
  4      on t.x = s.x;

         X          Y          X WORDS
---------- ---------- ---------- ---------------
         1         10          1 Hello World!

And select t.x, t.y, s.words shaves one column off of the relation.

Note that the above describes a conceptual or logical model of what is going on. Databases come with query optimizers that are designed to give the results as if all the logical steps had been followed, but manage to skip steps, in the physical implementation of the work and to use supporting physical structures, such as indexes, that are not part of the relational model.

Views are relation definitions that do not store the relation, but define a relation based on other relations, eventually with tables at the bottom. (Except for materialized views, that precompute and store a relation based on other relations.)

Answer 2

There are so far four answers here, and they are all the same, and all wrong. The term "relational" refers to the fact that the records in a table model a mathematical relation.

Answer 3

I can see that other respondents are giving you strict definitions of what can truly be called a "relation" and I don't dispute their correctness. In common usage, however, when someone refers to a "relation" in a database course they are referring to a tabular set of data either permanently stored in the database (a table) or derived from tables according to a mathematical description (a view or a query result).

Answer 4

Practicality, a "Relation" in relational model can be considered as a "Table" in actual RDBMS products(Oracle, SQL Server, MySQL, etc), and "Tuples" in a relation can also be considered as "Rows" or "Records" in a table. The only difference between them is that Relation is a set of tuples and Table is a bag of records. As a set, relation disallows duplicate elements(tuples) and all tuples in it are unordered, but the records in table may be repeated and are always in a particular sequence for both physical storage and human-readable display.

And there are two similar terms which often cause confusion and misunderstanding in database area. Please notice them: the "Relationship" in E/R model and the "Relation" in relational model is absolutely different. When converting an E/R model into a relational model, both entities and relationships in the former are represented (with a little different structure) as relations(tables) in the latter. And the association("reference" or "relationship" also be used) between tables, actually is known as foreign key, is still different with the relationship between entities.

More precisely, you may want to distinguish a relation and a relation variable (relvar). A relation is an abstract structure which contains a set of attributes, and a relvar is the dataset status in a particular moment of this relation. The first one can be considered as the table definition with columns, and the second one is dataset in this table. (Think about Type vs Variable in C or any other procedural programming language and Class vs Object in OOP.)

Following are corresponding terms between relation theory and database practice:

Relation             <-->  Table
Tuple                <-->  Record, Row
Attribute            <-->  Column, Field
Domain of attribute  <-->  Datatype of column

Answer 5

Generally, a relation is how one table "relates" to another. In other words. I have a table of fruit and a table of baskets. The "relationship" would be the members of the fruit table that are related to the basket table such as:

Fruit                  Basket
-----------            ----------
Apple, Apple ID        Apple ID, Quantity
Orange, Orange ID      Orange ID, Quantity
Banana, Banana ID      Banana ID, Quantity
Tomato, Tomato ID

There is a relationship between the Fruit table and the Basket table based on the ID of the fruit. The "ID" is the key to the relationship.

Simple answer I know, but that's the gist of it. And yes, a Tomato is a fruit.

Answer 6

These articles may be of interest to you:

• What is a relational database?
• What is entity-relationship model?

In simple English: relation is data in tabular format with fixed number of columns and data type of each column.

This can be a table, a view, a result of a subquery or a function etc.