What is a relation in database terminology?

Question

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

Answer 1

C. J. Date gives the following formal definition in the Database Design and Relational Theory: Normal Forms and All That Jazz:

Definition (relation): A relation r is an ordered pair <H,h>, where h is a set of tuples (the body of r) all having heading H. H is the heading of r and the attributes of H are the attributes of r. The tuples of h are the tuples of r.

Definition (tuple): A tuple with heading H is a set of ordered pairs <A,v> (one such pair for each attribute name A appearing in H), where v is a value. The phrase tuple with heading H can be abbreviated to just tuple, if H is either understood or irrelevant for the purpose at hand.

Definition (heading): A heading H is a set of attribute names.

Answer 2

A relation is a set of unique tuples, where a tuple consists of an entity ID value which is RELATED TO (identifies) one or more attributes. It is NOT a table, which is a different level entirely (implementation rather than design).

I can't make this definition any shorter without leaving something out, but it is so short as to be merely a list of terms. If I make it longer, I will probably confuse the main point which is: "what does the word relation mean in this context? What is being related?"

Answer 3

Put simply, a "relation" is a table, the heading being the definition of the structure and the rows being the data.

Answer 4

A relation is a table, which is a set of data. A table is the result of a query.

Why is a table called a relation? In short, because all the values in the table can be defined by a relation in the sense of set theory.

A table contains a set of data. All the elements in a set are defined by a relation. In set theory, relations are often denoted xRy, where x is related to y by the relation R. For example, (2) R (-2) where the relation R is x is the negative of y. The set of all negative numbers is defined by this relation R, where the domain is all positive numbers, and the range is all negative numbers.

We could also have the binary relation: ('Boston') R (American Cities) where the relation R is defined as x can be defined by y.

We could also have the binary relation: ('Mango') R (Fruit) where the relation R is defined as x is this type of food. And so, x is a value in the domain (the input) of a relation, and y is a value in the range (the output) of a relation.

A database table of all citizens in New York may be represented as

Citizen(Social_Security_Number, Name, Home_Address).

Here, the relation in the sense of set theory, is xRy where the relation R is defined as x is a citizen living in y, where we define y as New York.

A query can also return a new relation (that is, it returns a set of data defined by a new relation). If we want to query the database to find all citizens that have the last name, 'Perrone', we would define our result set based on another relation xRy, namely, x is a citizen living in New York with the last name, y, where we define y as 'Perrone'.

Answer 5

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.

Answer 6

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 7

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 8

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 9

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.