Thursday, December 22, 2011

SQL Server interview questions: - Different types of relationship existing in database designing?

This is one of the most favorite SQL Server interview questions asked by the interviewer during the interview.

We have tried to keep answer as simple as we can. So starts the answer as, basically there are three major relationship models: -
  • One-to-one

                      Figure: - One-to-One relationship ER diagram
  • One-to-many
In this many records in one table corresponds to the one record in other table. Example: - Every one customer can have multiple sales. So there exist one-to-many relationships between customer and sales table.

One “Asset” can have multiple “Maintenance”. So “Asset” entity has one-to-many relationship between them as the ER model shows below.

                     Figure: - One-to-Many Relationship ER diagram
  • Many-to-many
In this, one record in one table corresponds to many rows in other table and also vice-versa. For instance :- In a company one employee can have many skills like java , c# etc and also one skill can belong to many employees.

Given below is a sample of many-to-many relationship. One employee can have knowledge of multiple “Technology”. So in order to implement this we have one more table “Employee Technology” which is linked to the primary key of “Employee” and “Technology” table.

                  Figure: - Many-to-Many Relationship ER diagram
Also see the following video on differences between unique key and primary key in SQL Server as follows:-

Visit for more SQL Server interview questions


Get more from author’s blog for SQL Server interview questions

No comments: