Change Language:

Database: Types of Relationships

An association established between common fields (columns) in two tables is called relationship. It is a fundamental concept of relational database.

The entities have a set of properties associated with them. For example each student has a set of properties such as Roll no, Name, Father’s name, Address, Phone no etc. When we create tables associated with entities, the properties become fields of these tables. In a student table, each student has more than one phone numbers associated with him. Such association is also referred to as relationship.

There are three types of relationships.

1. One-to-one relationship
2. One-to-many relationship
3. Many-to-many relationship

1. One-to-One Relationship

In one-to-one relationship, two tables are associated in such a way that each record in first table can have only one matching record in second table, and each record in second table can have only one matching record in first table. Consider the relationship between countries and their capitals given below.

2. One-to-Many Relationship

In one-to-many relationship, two tables are associated in such a way that each record in the first table can have many matching records in second table, but a record in second table has only one matching record in first table.

For example, the student table has unique records and each Roll_no in student table may have more than one phone numbers in “student phone” table, on the other hand when you see records in “student phone” table, each record in. it has only one matching record in “student” table.

3. Many-to-Many Relationship

In many-to-many relationship, two tables are associated in such a way that each record in the first table can have many matching records in second table, and a record in second table can have many matching records in first table.

Suppose two sisters are in the same school. The relationship between two entities is shown above. In the above relationship, each record in the left side has two records in right side. Similarly, each record in the right side has two related records in left side.

Referential Integrity

Referential integrity ensures that the relationships between records in related tables remain consistent For example; if the “student” table and “student phone” table are related then every record in “student phone” table has a corresponding record in the “student” table. The database management system will not allow you to delete record from the “student” table ‘if there are phone numbers related to that student in the “student phone” table.

• [Computer Software] - Types of Databases
• [Computer Software] - What is database?
• [Computer Software] - Database: Data Types of Fields and Field Size
• [General Computer] - High Level Languages > Non-Procedural Languages
• [Microsoft Office] - Creating Database & Tables in MS Access