Types of Database relationships explained with definition, symbols diagram, table model examples. Database relationships are used a lot. Al...
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.
Some conditions followed for building a relational 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- By creating a table a lot of data can be saved properly.
- Easily we can make a relationship between tables so that we can find data easily.
- 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. |
- 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.
- There must be a primary key field in any of the tables from all the tables.
Types of Database relationships
- One-To-One relation
- One-To-many relation
- 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.
 |
| 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.
 |
| 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.
 |
| 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_ |
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.
No comments