What is relation? It is, in fact, a mathematical concept describing how the elements of two sets relate or correspond to each other.
In relational database theory of E. F Codd, a relation is a set of tuples (d1, d2, … dn) or rows or records in a data table where each element is a member of a data domain. Here, relation is represented as a table.
A Relational model organizes data into table. A relational table has a set of attributes or columns or sometimes referred to as fields and a set of tuples or rows or sometimes referred to as records.
A relational database consists of one or many tables. Each table in a relational database is the basic storage structure of RDBMS.
So, in a database, the relation between the data of one table with the data of another table is called relationship between two tables. It is a logical connection between different tables. An RDBMS enables to relate the data in one table to data in another table by using the foreign keys. A foreign key is a column or set of columns that refers to a primary key in another table. This is the logic behind the term relationship in database.
Relational condition between databases
1) In two relational tables, there must be a common field and the name, size, data-type and format of the common field will be the same.
2) In each data table, at least one field must be defined as primary key.
3) A database cannot contain multiple tables of the same name.
For example, the EMP table contains information about employee and DEPT table contains information about the department of those employees. Here data about different entities is stored in different tables.
Say, you want to know the location of the department where an employee works. In this scenario, you need information from the EMP table and the DEPT table. An RDBMS enables you to relate the data between two tables by using Foreign Key of one table that refers to a Primary Key of another table. Here these two tables EMP and DEPT need a field in common: deptno. By joining these two tables we can result a new table to put our desired information from the two tables together.
We can show the relationship by linking these two tables EMP and DEPT with an SQL statement.
Combined Table
In this combined table the rows are those where the same Deptno is in both tables. The where clause is executing a logic that says select those rows in the EMP table and DEPT table where the Deptno are equal in both.
In database there are three types of relationships.
- One to one
- One to many
- Many to many
One to one relationship
In case of a pure one-to-one relationship, every value of one entity is related to one and only one value of another entity, and vice versa. This type of relationship is rare. For example, a husband is married to only one wife and a wife is married to only one husband without counting polygamists. Another example, there are many teachers in an institution where every teacher teaches on only one subject and no other teacher will be allowed to teach that subject. In this case, one-to-one type relationship exists between teacher and subject entity set.
This type of relationship is possible if the primary key (PK) of one table relates with the primary key (unique foreign key) of another table.
One-to-many relationship
One-to-many relationship is the most common database relationship. It is a cardinality type relationship between two entities. In this type of relationship one record in a table can be associated with one or more records in another table. One record of a table has a matching with one or more than one records of another table. It is possible when a foreign key of a table refers to a primary key of another table.
For example, a state has many cities, but those same cities belong to only one state. One teacher teaches on more than one subjects but each subject cannot be taught by more than one teacher. More than one items of Products table may relate with one Supplier_ID of Supplier table. All these are the example of One-to-many relationship.
One-to-many and Many-to-one relationship are similar but not in directionality. The entity is present on which side of the relationship settles whether it is a one-to-many or many-to-one relationships.
For example, if one teacher teaches many courses then, teacher to course is a one-to-many relationship while course to teacher relationship is many to one.
Many-to-many relationship
Many-to-many relationship is more rare database relationship. It is a type of cardinality that refers to the relationship between two entities. It occurs when multiple records in a table are associated with multiple records in another table. In this case, any record of A table can have more matching records in B table. Again, any record of B table can have more matching records in A table.
It is actually two one-to-many relationships from both tables. This type of relationship is difficult to implement. That is why, a third table is brought to solve and implement the many-to-many relationship in the relational model. Also, it is called a “linkage table” or “junction table”. So, in many-to-many relationship two one-to-many relationships joined together with a “junction table”.
Importance of relationship in database
- Relationships in database enable us to normalize our database. By applying relationships, we can divide our database into separate tables.
- It allows us to create queries easily.
- It helps to create refine table structures and minimize redundant data.
- Relationship in database eliminates duplicate information from database system by splitting information into several tables.
- Relationship between two tables ensures data integrity. Data integrity helps to ensure consistency and accuracy of the data.
Related terms: