Computer Science

Types of Relationships in Database With Examples

Referential integrity is a rule that preserves the defined relationship between the table when records are added or deleted. Relationships are also an important part of a database system. Different types of tables with different information are linked with each other to form a database. To learn more about relationships in the database, read that complete article.

Relationships in Database

relationships in database

Different tables in a database can be joined together by establishing a relationship between them. Different types of relationships are as follows:

 

  • One-to-One Relationship
  • One-to-Many Relationship
  • Many-to-Many Relationship

One-to-One Relationship

This type of relationship is used when “for each record in the first table, there is only one record in the second table and for each record in the second table, there is only one record in the first table”.

relationships in database

Example:

For example, we have two tables named Student and Marks. Student table contains the basic information of the students and Marks table contains the marks of the students. Both tables are joined with a one-to-one relationship. It means that for each record in the Student table, there is only one record in Marks table and vice versa. Each Roll No occurs once in both tables.

One-to-Many Relationship

This type of relationship is used when “for each record in the first table, there are one or more records in the second table and for each record in the second table, there is only one record in the first table”.

relationships in database

Example:

For example, we have two tables named Student and Subject. Student table contains the basic information of the students and Subject table contains the marks of the students in different subjects. Both tables are joined with a one-to-many relationship. It means that for each record in the Student table, there can be one or more record in the Subject table and for each record in the Subject table, there can be only one record in Student table. There are more records in the Subject table against one record in Student table because of one student studies many subjects.

Many-to-Many Relationship

This type of relationship is used when “for each record in the first table, there are one or more records in the second table and for each record in the second table, there are one or more records in the first table”.relationships in database

Example:

For example, we have two tables named Authors and Books. Authors table contains the information of the authors and Books table contains the information of the books. Both tables are joined with a many-to-many relationship. It means that for each record in Author table, there can be one or more records in Books table and vice versa. It means that one author may have written one or more books. Similarly, one book may have been written by one or more authors.

Referential Integrity

A rule that preserves the defined relationships in a database between the table when records are added or deleted is called referential integrity. it means that you cannot enter a value in the foreign key field that does not have a corresponding value in the primary key field of the related table.

Referential integrity is applied when the data of an entity is stored in more than one table. When two tables are linked through relationship, one table is called master table and the other is called child table.

The most important advantages of using referential integrity are as follows:

  • If the record does not exist in the master table, it cannot be stored in the child table.
  • A record cannot be deleted from the master table if a related record exists in the child table.

Example:

The following figure shows two tables. The master table contains the Registration No, Name and Class of the students. The child table contains Registration No, Subject and Marks of the students. Both tables are connected through relationship. The Child table is connected to the Master table with Registration No.

In the above example, there are two tables Master and Child. The user cannot enter data in Child table without first entering the corresponding data in Master table.

Suppose the user wants to enter the result of a student with Registration No 96-AG-1940. He has to enter the record in the Master table and then he can enter the details of that student in the Child table.

Similarly, if the user wants to delete a record in Master table, he has to delete the corresponding records in the Child table.

 

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button
Close