Relation in database (DBMS):
Definition and Explanation:
The term relation is derived from the set theory
of mathematics. The relational data model is based on the concept of a relation,
which is physically represented as a table. A table consists of a set of named
columns and a set of unnamed rows. The tables are the most important part of the
database and hold information about objects.
A relation is represented as a two
dimensional table, in which rows of the table indicate the individual records
and columns of the table indicate the attributes;
For example, to store the data of student, a relation is represented by the
'Student' table. This table has column attributes 'Roll-No', 'Name' and 'City'. Each column contains
values of the attribute e.g., the 'Name' column contains only the names of
students. If there are four rows in the table. It means that records of four
students are represented.
Therefore, in relational data model terminology,
we define the following terms as:
Attributes: Each column of a relation has
a heading, called 'field name'. The named columns of the relation are called
attributes. In the above "Student" relation, the attribute names are 'Roll-No',
'Name' and 'City'.
Degree: The number of attributes in a
relation is called its degree. In the "Student" relation, the degree is 3.
Tuple: Each row of the relation is called tuple. Each tuple consists of set of values that are related to a particular
instance of an entity type.
Cardinality: The number of tuples (rows or
records) in the relation is called cardinality. In the 'Student' relation, there
are 4 tuples, so the cardinality of the relation is 4.
Domain: The range of acceptable values for
an attribute is called the domain for the attribute. Two or more attributes may
have the same domain.
A relation that contains a minimum amount of
redundancy and allows users to insert, modify, and delete the rows without
errors, is known as -well-structured relation.
A relation schema is used to describe a relation.
It is made up of a relation name and a list of attributes. For example, if R
is a relation name and its attributes are: A1,A2, .... An, then the relation
schema of R is denoted as; R(A1,A2, .... An).
An example of relation schema for a relation of
degree 3, which describes the students of university, is given below.
STUDENT (Roll-No, Name, City)
Where 'Student' represents the name of relation
(or table) followed by names of attributes (in parenthesis) in the relation.
Properties of Relations:
We have defined that relations are similar to
two-dimensional table, however not all tables are relations. A relation has
several properties that make a relation different from a table. Some of these
properties are discussed below:
1. There is no duplication of tuples in a
A relation is a mathematical set (set of tuples), and sets in mathematics by
definition do not include duplicate elements. So a relation cannot contain any
duplicate tuples. It means that two (or more) identical rows (or records) cannot
exist in a relation. Each row in a relation must be unique. For example, in a
student relation, there cannot two records (rows) for the same student.
Uniqueness in a relation is guaranteed by assigning the primary key for each
2. All attribute values of relation are atomic:
According to this property of relation, each cell
of a relation (table) contains only one value (never a collection of several
values). You can say that each value in a tuple is an atomic value which cannot
be divided into components. The composite and multivalued attributes (or
repeating groups) are not allowed. Multi-valued attributes must be represented
by separate relations. A relation satisfying this condition is said to be
normalized (or equivalent to first normal form). For example, in a student
relation, you must have to write a single telephone number of a student (more
than one telephone numbers separated by commas is not allowed). Similarly,
there should be three attributes for student name such as First-Name,
Middle-Name, and Last-Name.
3. All attribute values of a relation must come
from the same domain:
The values for attributes must be within the
acceptable range. For example, in a student relation all entries in "Marks"
attribute must be from the domain that defines student marks (such as between 0
4. Tuples of a relation are unordered from top to
A relation is defined as a set of tuples.
Mathematically, elements of a set have no order among them. Hence, tuples in
relations do not have any particular order. The rows of a table do have a
top-to-bottom ordering, whereas the tuples of a relation do not. However in a
file, records are physically stored on disk in an order such as one after the
other. Similarly, when a relation is displayed as a table, rows can be displayed
in a certain order.
Tuples ordering is not a part of a relation
definition. Many logical orders can be specified on a relation when data of
relation is retrieved or manipulated. For example, tuples in the student
relation could be logically ordered by values of 'Roll-No', 'Name', 'Marks' or
some other attributes.
5. Attributes of a relation are unordered from
left to right:
Like tuples of a relation, the definition of a
relation does not specify any order of attributes. Each attribute of a relation
has a unique name. The attributes are always referenced by name, not by position
(or column number) in the relation. The columns of a table do have a
left-to-right order, while the attributes of a relation do not. The attributes
(or columns) of a relation may be interchanged.
Type/Kinds of Relation Keys:
An attribute (field) or set of attributes that
uniquely identify the tuples of a relation or to retrieve specific data from the
table of database is called key. Therefore, in relations (tables) of a database,
some attributes of tables are defined as keys for the following purposes.
- for uniquely identifying the tuples of a
- for establishing relationships between
- for sorting records of a relation based on
the data of one or more columns.
- for accessing particular record(s) from a
A brief description about most important kinds of
keys is given below.
A primary key is an attribute or combination of
attributes that uniquely identify each tuple in a relation. In a relational
database, each relation must have a primary key. The primary key is underlined
in relation schema. A primary key must not contain null values (undefined
Some relations may have more than one attribute
that can be used as primary key. For example, "Student" relation may have
attributes 'Roll_No', 'NIC', 'Registration_No', 'Address' and 'Phone_No' etc. In
"Student" relation, 'Roll_No', 'NIC' and 'Registration_No' are attributes that
can uniquely identify the records of students. So any one of these attributes
can be selected as primary key. Therefore, these attributes can be called as
If one of the suitable candidate key is selected
as the primary key of the relation, then the other candidate keys are called the
A key that consists of two or more attributes of
a relation is called composite key. It is also referred to as concatenate key.
For example, in a "Student" relation containing attributes 'Roll_No',
'Class_ID', 'Name', 'Address' and 'Phone_No'. The attributes 'Roll_No' and
'Class_ID' may be selected as composite key, so that the records of all the
classes of a college can be uniquely identified.
A foreign key is an attribute (or combination of
attributes) in a table whose values must match with a primary key in another
table. The table that contains the foreign key is called dependent table.
Similarly, the table to which the foreign key refers to is called the parent
table. The foreign key. is used to link data from one table to another.
For example; two relations "Student" and Marks"
are given below. The 'Roll-No' may be an attribute of 'Marks' relation but not
the primary key of 'Marks'. However, it is the primary key of the 'Student'
relation. In 'Marks' relation, Roll-No attribute will be referred to as foreign
The records of a relation or table are sorted
based on the data of one or more fields. An attribute or combination of
attributes that are selected to sort the records of a table in a specific order
is called sort key. For example, records of "Student" table can be sorted based
on "Name" or 'Marks' attribute or by selecting both 'Name' and 'Address'
attributes as sort key etc.
Representing Relational Database Schemas:
A relational database may consist of many
relations (tables). A schema is description of the overall logical structure of
a database. The relational database schema is represented by giving the name of
the each relation, followed by the attribute names in parenthesis. Normally the
primary key is underlined. For example, a relational database of university may
contain relations 'Student' and 'Faculty', the database schema is written as:
STUDENT (Roll-No, Name, City, .Marks)
FACULTY (FID, Name, Department)
Some attributes of the same name or same domain
may appear in more than one relation. When an attribute appears in more than one
related relation, it usually represents a relationship between tuples of the two
A figure below shows an instance of relational database of university.