Types of Database relationships explained with definition, symbols diagram, table model examples. Database relationships are used a lot. Also known as the relational database management system. It is shortly called RDBMS.

Types of Database relationships


Database relationships

Two or more tables are related to each other. This is a Database relationship. 

Read about the types of keys in the Database management system.


Join_data
Employee_ID
Join_Date
Room_no
3941
20.04.18
204
3940
11.04.18
205
3943
28.05.18
206

Interview_data
Interview_date
Employee_ID
10.04.18
3941
01.04.10
3940
18.05.18
3943

Here is the Join_data table and Interview_data table. For creating a relational database management system both of the tables must have a common field. Here Employee_IDis a common field.

Some advantages of Database relationships

  1. By creating a table a lot of data can be saved properly.
  2. Easily we can make a relationship between tables so that we can find data easily.
  3. Information can be sent and receive easily from one database to another database. A perfect system is created by this so that different operations applications become easier.

In most areas, this system is used. A relational database management system makes every data collection well preserved.

Difference between Database management system and Relational database management system


Database management system
Relational database management system
Database management system stores data as a file
Relational database management system applications store data as tabular form.
Generally, data is stored in a hierarchical form or as a navigational form.
A primary key which is called the identifier of tables exists and data are represented in tables.
In DBMS normalization is not present.
Normalization exists in this system.
Does not apply securities with regards to the data manipulation process.
RDBMS defines the integrity constraint for the purpose of the acid property. The acid properties are(Atomicity, Consistency, Isolation, and Durability)
There will be no relation between tables because DBMS uses a file system for storing data.
In RDBMS data stored in tables so that, relationships between all data values stored in the form of a table also.
Some uniform methods are applied for accessing stored information.
RDBMS supports tabular data storing structure with the relationship between the data for accessing data.
A distributed database is not supported in this system.
A distributed database is supported.

Some conditions followed for building a relational database management system
  1. The minimum one common field is essential in all the tables. The data type of common field and field size will be the same in all the tables.
  2. There must be a primary key field in any of the tables from all the tables.

Types of Database relationships

  1. One-To-One relation
  2. One-To-many relation
  3. Many-to-many relation

One-To-One database relation

When one record from one table is related to one record of another table then this is called One-To-One relation. Here the primary key of one table is connected with the primary key of another table.

Database relationship
Symbol diagram One-To-One database relation





Example:

Student_Info
ID
Name
Background
9142
Ahmed
Science
8321
Rahman
Commerce
9110
Khan
Science

Student_Section
ID
Section
CGPA
9142
A
3.0
8321
B
3.5
9110
A
3.8

Here we can see in both of the tables have a record ID. For example, the 9142 records in the Student_Background table is only connected with only 9142 records in the Student-Section table.  

The one-To-One relationship used in rare cases. 

One-To-many database relation

If one record from one table is related to more than one record of another table then it is called One-To-many relation. In this case the primary key of one table related to the foreign key with another table.

Database relationship
Symbol diagram: One-To-many database relation




Student_Info
ID
Name
9142
Ahmed
8321
Rahman
9110
Khan

Student_Subject
ID
Sub-ID
Sub_Title
Credit
9142
C101
Data communication
3
8321
B101
Economics
3
9142
C102
Computer networks
3
9110
C103
Digital Electronics
3

Here ID is the primary key of the Student_Info table and in the Student_Subject table ID is the foreign key.
In Student_Info table ID 9142 is connected with multiple records in the Student_Subject table. This relation is One-To-many relations.
It is the most used database relation.

Many-to-many database relation

If more than one record of one table is related to more than one record of another table then it is called Many-To-many relation.

Database relationship
Symbol diagram: Many-To-many database relation



Here for connecting we need an extra table. That is called a junction table. In the junction table, the Primary key of 2 tables is used as a foreign key of the new table.

Teacher_Info
Teacher_ID
Name
T-201
Adnan
T-102
Karim
T-202
Ehsan

Course_Info
Course_ID
Course_Name
C-101
Data Communication
B-101
Economics
C-102
Computer networks

Course_Routine
Teacher_ID
Course_ID
Weekly_schedule
T-201
C-101
SATURDAY
T-201
C-102
SUNDAY
T-102
B-101
THURSDAY
T-202
C-101
SATURDAY

Here Teacher_ID and Course_ID are the primary keys of the first two tables. These two keys are used as foreign keys of the new table Class_Routine which is called junction table.this is a Many-to-many database relation.

So, this is the overall discussion on types of Database relationships.