Entity relationships are associations between entities, which you have come to recognize as links between tables. Typically, a relationship is indicated by a verb connecting two entities. Consider the following two related entities with several attributes listed for each (key attributes are indicated with asterisks).
A general statement about the relationship between the EMPLOYEE and PROJECT entities may be put this way:
- Employees are assigned to projects (or)
- Projects are assigned to employees.
Although these statements denote an association between the two entities, we know nothing about how the entities are formally related to one another. Until we know considerably more, we cannot convert them into an ER diagram or, ultimately, into linked tables.
For example, one important thing we do not yet know is if the EMPLOYEE entity should insert its key attribute (EmpID) into the PROJECT entity to establish the relationship, or if the PROJECT entity should insert its key attribute (ProjID) into the EMPLOYEE entity.
We also do not know if every employee must be assigned to a project, or if deleting an employee record from an Employees table will have an adverse effect on records in a linked Projects table.
Determining how entities are related requires careful interpretation of the business rules uncovered during Requirements Analysis (and the application of common-sense rules). Rules tell the database designer the type of relationship that exists between entities, and the type of participation entities enjoy in relationships.
The next lesson briefly describes the types of relationships that can exist between entities.