RELATIONSHIP
In data analysis terms, a relationship is an association (or link) between entities/tables. Though a table can exists with or without any primary or foreign key but a relationship is established by a foreign key in one entity/table linking to the primary key in another.
Degrees of Relationship (CARDINALITY)
The degree of relationship or cardinality is the number of occurrences in one entity which are associated (or linked) to the number of occurrences in another.
There are three degrees of relationship, known as:
1.) one-to-one (1:1)
In data analysis terms, a relationship is an association (or link) between entities/tables. Though a table can exists with or without any primary or foreign key but a relationship is established by a foreign key in one entity/table linking to the primary key in another.
The degree of relationship or cardinality is the number of occurrences in one entity which are associated (or linked) to the number of occurrences in another.
There are three degrees of relationship, known as:
1.) one-to-one (1:1)
> This is where one occurrence of an entity relates to only one occurrence in another entity.
> Rarely exists in practice.
2.) one-to-many (1:M)
2.) one-to-many (1:M)
> Is where one occurrence in an entity relates to many occurrences in another entity.
> Most Common in practice.
3.) many-to-many (M:N)
> This is where many occurrences in an entity relate to many occurrences in another entity.> Implemented using INTERSECTION TABLES.
> This is where many occurrences in an entity relate to many occurrences in another entity.> Implemented using INTERSECTION TABLES.
> Rarely exists in practice.
> Does not exist in fully normalized tables.
Relationship can also be categorized in below categories:
I.) MANDATORY RELATIONSHIPDictates that foreign key column in the child table:
Dictates that foreign key column in the child table:
How to read physical data model (Entity Relationship Diagram - ERD):
Scenario-1:
1.1) For each A row, B has a minimum or maximum 1 row.
1.2) For each B row, A has a minimum or maximum 1 row.
Scenario-2:
2.1) A to B > Table A can have 0 or any number of Table-B rows.
2.2) B to A > Specific rows of Table-B can be related to one and only one row of Table-A.
Scenario-3:
3.1) A to B > For each Table-A row there is one or many corresponding Table-B rows.
3.2) B to A > Each Table-B row is related or belongs to only one row in Table-A.
Scenario-4:
4.1) A to B > For each Table-A row there is zero or many corresponding Table-B rows.
4.2) B to A > Each Table-B row is related or belongs to only one row in Table-A.
Scenario-5:
5.1) A to B > For each Table-A row there is one or many corresponding Table-B rows.
5.2) B to A > Each Table-B row can have zero or one row in Table-A.
Scenario-6:
RECURSIVE RELATIONSHIP
A-To-A (Relationship-1) > Every employee must have only one supervisor.
A-To-A (Relationship-2) > Every supervisor (Stored in EMPLOYEE Table) may be the supervisor of zero, one or many employer. must have only one .
- Must have a value which must correspond to a row in parent table primary key.
- That value should never be NULL.
Dictates that foreign key column in the child table:
- Must have a value which must correspond to a row in parent table primary key.
- That value could be NULL.
How to read physical data model (Entity Relationship Diagram - ERD):
Scenario-1:
1.1) For each A row, B has a minimum or maximum 1 row.
1.2) For each B row, A has a minimum or maximum 1 row.
Scenario-2:
2.1) A to B > Table A can have 0 or any number of Table-B rows.
2.2) B to A > Specific rows of Table-B can be related to one and only one row of Table-A.
Scenario-3:
3.1) A to B > For each Table-A row there is one or many corresponding Table-B rows.
3.2) B to A > Each Table-B row is related or belongs to only one row in Table-A.
Scenario-4:
4.1) A to B > For each Table-A row there is zero or many corresponding Table-B rows.
4.2) B to A > Each Table-B row is related or belongs to only one row in Table-A.
Scenario-5:
5.1) A to B > For each Table-A row there is one or many corresponding Table-B rows.
5.2) B to A > Each Table-B row can have zero or one row in Table-A.
Scenario-6:
RECURSIVE RELATIONSHIP
- When relationship exists between same entity/table.
- For example assume that there is a field on the EMPLOYEE table called supervisor.
A-To-A (Relationship-1) > Every employee must have only one supervisor.
A-To-A (Relationship-2) > Every supervisor (Stored in EMPLOYEE Table) may be the supervisor of zero, one or many employer. must have only one .
No comments:
Post a Comment