Computer Science

One-to-one Relationship Database Management System

Relationship Database Management System is a consistent association between various tables. A relationship is set up based on collaboration among these tables. The relationship is established by connecting one or more field s of two tables. The fields used to connect two tables normally same name, data type, and size.

 

One-to-one Relationship

One-to-one Relationship

 

Different types of Relationship Database are as follows:

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

One-to-One Relationship Database

  • There is a one-to-one relationship between two tables when
  • values match ed is known as ferns are known as o is established
  • For each record in the first table, there is only one record in the second table.
  • For each record in the second table, there is only one record in the first table.

Example:

An example of the one-to-one relationship is as follows:

The above example has two tables Employee and Loan. The Employee table contains the data of employees and Loan table contains data about loans. Both tables contain EmpID Field as the primary key. Each employee can borrow loan once so the tables are joined with one- record in Loan table and vice versa. Each EmpID occurs once in both tables.

One-to-Many Relationship Database

A one-to-many relationship exists between two tables when:

  • For each record in the first table, there are one or more records in the second table.
  • For each record in the second table, there is only one record in the first table.

The above example has two tables Student and Marks. The Student table contains data of the students and its primary key field is RollNo. The Subject table contains the marks of the students in different subjects and its primary key consists of two fields RollNo and Subject.

The RollNo field also works as a foreign key in Marks table. 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 records in the Subject 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 Database:

  • A many-to-many relationship exists between two tables when:
  • For each record in the first table, there are one or more records in the second table.
  • For each record in the second table, there are one or more records in the first table.
  • A many-to-many relationship is more complex than other types of relationships. It is a collection of two one-to-many relationships. The following steps are performed to create a many-to-many relationship:
  • Create the two tables to be connected using many-to-many relationships.
  • Create a third table known as junction table.
  • Insert the primary fields of two tables in the junction table and create a composite primary key that consists of both fields in the junction table. Create a one-to-many relationship of both tables with junction table.
  • The many-to-many relationship will be established between both tables.

Example An example of the many-to-many relationship is as follows:

The above example has three tables. The Artist table contains data of different artists and its primary key is ArtistID. The Album table contains data from Different Albums. One artist may work in many albums and one album may contain songs of different artists.

Many-to-many relationships should be established between these tables. A junction table Artist Album is created for this purpose. It consists of the primary fields of Artist table and Album table. The junction table is connected with both tables using one-to-many relationship.

Achieving Referential Integrity:

The referential integrity states that if a foreign key exists in a relation, the foreign key value must match the primary key value of some tuple in its parent relation. It preserves the defined relationship between relations when records are added or deleted. It ensures that the data in both relations are consistent.

Achieving Referential Integrity:

The Relationship Database integrity can be achieved by creating a relationship between the relations. In a Relationship Database, one relation is called parent relation and the other is called child relation. The parent relation contains the primary key and child relation contains the foreign key.

The referential integrity ensures the following:

  • A value entered in the foreign key field of the child relation must exist in the primary key field of the parent relation.
  • A record cannot be deleted from a child relation if matching records exist in the parent relation.
  • The primary key value cannot be changed in the parent relation if its corresponding records exist in the child relation.

About the author

Hameed

Hello there, my name is Abdul Hameed IT arrangement The Concepts of Information Technology Introduction to Computers and Information Technology Free Education Computers And Software Education site itseries.net gives free.

The utilization of Information Technology in each field of life is expanding with the progression of time. It is essential for understudies and additionally average citizens to know the most recent ideas of data innovation to contend on the planet. Capability in data innovation is essential to apply it viable in various fields for advancement.

Abdul Hameed
itseries452@gmail.com

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.