There are three type of relationships
1) One to one
2) One to many
3) Many to many
Say we have table1 and table2
For one to one relationship, a record(row) in table1 will have at most one matching record or row in table2
I.e. it mustn’t have two matching records or no matching records in table2.
For one to many, a record in table1 can have more than one record in table2 but not vice versa
Let’s take an example,
Say we have a database which saves information about Guys and whom they are dating.
We have two tables in our database Guys and Girls
Here in above example Guy ID and Girl ID are primary keys of their respective table.
Say Andrew is dating Girl1, Bob – Girl2 and Craig is dating Girl3.
So we are having a one to one relationship over there.
So in this case we need to modify the Girls table to have a Guy id foreign key in it.
Now let say one guy has started dating more than one girl.
i.e. Andrew has started dating Girl1 and say a new Girl4
That takes us to one to many relationships from Guys to Girls table.
Now to accommodate this change we can modify our Girls table like this
Now say after few days, comes a time where girls have also started dating more than one boy i.e. many to many relationships
So the thing to do over here is to add another table which is called Junction Table, Associate Table or linking Table which will contain primary key columns of both girls and guys table.
Let see it with an example
Andrew is now dating Girl1 and Girl2 and
Now Girl3 has started dating Bob and Craig
so our junction table will look like this
It will contain primary key of both the Girls and Boys table.
A relationship type R among n entity types E1, E2, …, En is a set of associations among entities from these types. Actually, R is a set of relationship instances ri where each ri is an n-tuple of entities (e1, e2, …, en), and each entity ej in ri is a member of entity type Ej, 1≤j≤n. Hence, a relationship type is a mathematical relation on E1, E2, …, En, or alternatively it can be defined as a subset of the Cartesian product E1x E2x … xEn . Here, entity types E1, E2, …, En defines a set of relationship, called relationship sets.
Relationship instance: Each relationship instance ri in R is an association of entities, where the association includes exactly one entity from each participating entity type. Each such relationship instance ri represent the fact that the entities participating in ri are related in some way in the corresponding miniworld situation. For example, in relationship type WORKS_FOR associates one EMPLOYEE and DEPARTMENT, which associates each employee with the department for which the employee works. Each relationship instance in the relationship set WORKS_FOR associates one EMPLOYEE and one DEPARTMENT.
We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in to correspond to the regular entity types EMPLOYEE, DEPARTMENT, and PROJECT. The foreign key and relationship attributes, if any, are not include yet; they will be added during subsequent steps. These, include the attributes SUPERENO and DNO of EMPLOYEE, MGRNO and MGRSTARTDATE of DEPARTMENT, and DNUM of PROJECT. We choose ENO, DNUMBER, and PNUMBER as primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT, respectively. Knowledge that DNAME of DEPARTMENT and PNAME of PROJCET are secondary keys is kept for possible use later in the design.
The relation that is created from the mapping of entity types are sometimes called entity relations because each tuyple represents an entity instance.