Relationship
A dependency of any kind between tables in a data model is called a relationship.
A relationship is a summary of a stereotyped association and a set of primary and foreign keys.
Every relationship is between a parent and child table, where a parent table must have a primary key defined. The child table creates a foreign key column and foreign key constraint to address the parent table.
A non-identifying association represents a relationship between two independent tables. The foreign key of the child table does not contain all of the primary key columns, in other words, the foreign key(EMP: DEPTNO) of the child table may not be a part of the primary key columns(EMP: EMPNO). (Figure 1)
Figure 1
An identifying relationship is a relation between two dependent tables, where the child table cannot exist without the parent table. All of the primary keys of the parent table (Person, in this example) become both primary and foreign key columns in the child table (Account), in other words, the foreign key(ACCOUNT: SOCIAL_ID) of the child table must be a part of the primary key columns(ACCOUNT: ACCOUNT_NO,SOCIAL_ID). (Figure 2)
Figure 2
A relationship has two roles associated with it. They define the role of one table in association with the other. It is possible to assign more than one relationship between two tables using different roles.
Each relationship creates migrated keys from the parent to the child table.