One to one relationship examples in Database

There are 3 main types of database relationships in relational database design.

  1. One-to-One relationship
  2. One-to-Many (or Many-to-One) relationship
  3. Many-to-Many  relationship

One-to-One Relationship

One-to-One Relationship exists when a single record in the 1st table is having a relationship with only one record in the 2nd table, and similarly, we can say that a single record in the 2nd table is related to only one record in the 1st table.

One-to-One Relationship Examples

Let’s begin with an ERD design in which one student can have only one scholarship.

Entities

  1. Student
  2. Scholarship

Attributes of Student

  1. Student_id
  2. class

Attributes of Scholarship

  1. scholarship_id
  2. scholarship_type
  3. scholarship_year.

Relationship

One Student can have one scholarship.

one to one relationship examples in database systems
Figure: Entity Relationship Diagram of 1 to 1 Relationship

How to convert ERD into database tables and showing a 1 to 1 relationship?

Now, let’s convert this ERD Design to database tables.

As, we have two entities and one relationship, so we need to make three tables, two tables for two entities and one table for this relationship.

  1. The first table is for “Student” entity
  2. The second table is for a relationship that “student_can_have_scholarship”. We can also handle the situation without creating an extra relationship table in one to one relationships (but not in other types of relationships).
  3. The third  table is for “scholarship” entity
one to one relationship in DBMS
Figure: Database tables representation of 1 to 1 Relationship

Non-Key Attributes: are the attributes without any key.

Primary-Key Attributes: are the attributes with the primary key and used for the unique identification of any row. Any value in the primary key attribute can’t be repeated again and again.

Foreign- Key Attributes are the attributes that set a foreign key. A foreign key is the primary key of one table that is linked with the field of another table.

Detailed information about keys is available in the keys tutorial.

In this example, we have set the primary key with “student_id” in the 2nd table, but we can also set the primary key with “scholarship_id”. Because the values of both attributes are unique and values are not repeating.

Note: We can also handle the situation without creating an extra 3rd table in one to one relationships (but not in another type of relationship).

1 to 1 relationshipexamples in sql server and mysql

Download Slides

  1. Slides in PPT: One to one Database relationship examples ppt slides presentation
  2. Slides in PDF: One to one Database relationship examples PDF

Video Lecture

One-to-one relationship example in SQL | when to use a one-to-one relationship | one-to-one relationship diagram | one to one relationship in MySQL | one to one relationship in the SQL server | one to one relationship in XAMPP PHP Myadmin | one-to-one relationship ER diagram.

Next Articles

Related Posts:

Add a Comment