What are the three kinds of relationships possible between entities? Describe each one and provide an example

Question

What will be an ideal response?

Answer 1

There are three kinds of relationships possible among entities: One to one, one to many and many to many. In a one to one relationship each record in the primary key table can have zero or one record in the foreign key table. One example is of an employee table split into two tables, one private and one public. For each record in the primary key table, there is exactly one record in the foreign key table. Often the foreign key is also the primary key of the child table.
In a one to many relationship, each record in the primary key table can relate to zero or any number of records in the foreign key table. For example, one Department can contain many employees. Another example might be that each employee could work on many projects.
Many to many relationships are also valid relationships but no DBMS can implement them. In a many to many relationship each record in the primary key table can relate to many records in the foreign key table, just as on a one to many relationship. But, additionally, each record in the foreign key table can relate to many records in the primary key table. An example is the relationship between a Book entity and an Author Entity. Each book can have many authors and each author can write many books. To resolve this, it is necessary to create a linking table such as BookAuthor that resolves the relationship into two one to many relationships.