Question

In a construction business, an employee can work on at least one or many projects at the same time. And each project should have at least one employee or can have multiple employees working on it.

• Draw the entities, the relationship and the cardinalities for the above business rule.
• Identify what kind of relationship is established between the entities
• Don’t do anything if it’s a one-to-one or one-to-many relationship. But if it’s a many-to-many relationship then resolve it so that it could be implemented in a relational model. If it's a many-to-many relationship, show all relationships and cardinalities.

Answer 1

Explanation :

Cardinality and Relationships in Data Models :

In terms of data models, cardinality refers to the relationship between two tables.

Relationship can be of four types as we have already seen in Entity relationship guide:

1. One to One – A single row of first table associates with single row of second table. For example, a relationship between person and passport table is one to one because a person can have only one passport and a passport can be assigned to only one person.
2. One to Many – A single row of first table associates with more than one rows of second table. For example, relationship between customer and order table is one to many because a customer can place many orders but a order can be placed by a single customer alone.
3. Many to One – Many rows of first table associate with a single row of second table. For example, relationship between student and university is many to one because a university can have many students but a student can only study only in single university at a time.
4. Many to Many – Many rows of first table associate with many rows of second table. For example, relationship between student and course table is many to many because a student can take many courses at a time and a course can be assigned to many students.

Complexity of many to many :

Many-to-many (m:n) relationships add complexity and confusion to the data model and to the application development process and thus need to be resolved using some technique to be implemented in a relational data model.

Overcoming the Complexity of many to many :

The key to resolve m:n relationships is to separate the two entities and create two one-to-many (1:n) relationships between them with a third intersect entity. The intersect entity usually contains attributes from both connecting entities.

In order to overcome this situation, a concept called a joining table or a bridging table can be used.

A joining table is a table that sits between the two other tables of a many-to-many relationship. Its purpose is to store a record for each of the combinations of these other two tables. It might seem like a bit of work to create, but it’s simple to do and provides a much better data structure.

In the light of the above discussions, the solution to the given set of business rules are as follows:

The Solution :

The given business rules for the construction business:

1. An employee can work on at least one or many projects at the same time.

2. And each project should have at least one employee or can have multiple employees working on it.

The Entity-Relationship or ER diagram for the above business rule is depicted as follows:

Employees Projects

emp_id ( pk ) MANY TO MANY   project_id ( pk )

emp_name       project_name

emp_address description

projects_involved emp_working ( not null )

In the above diagram, ( pk ) refers to the primary key of the entities.

Clearly in the above ER diagram, the cardinality which refers to the relationship between the 2 entities employees and project is that of many to many as an employee can work in multiple projects and a project can have multiple employees.

The above complexity can be resolved by introducing an intermediary entity called the Job which contains and relates the primary keys of the 2 entities Employees and Projects to a specific job with an unique job_id as the primary key.

The above resolution of the complexity is depicted in the diagram below:

Employees Projects

emp_id ( pk ) JOB project_id ( pk )

emp_name ONE TO ONE Job_id ( pk )   ONE TO ONE project_name

emp_address emp_id    description

projects_involved project_id emp_working ( not null )

In the above diagram the cardinality which refers to the relationship between the entities has been reduced to 2 one to one relationships instead of 1 one to many relationship which now be easily implemented in the data model.

This concludes the answer to all parts of the question along with the necessary explanations.

Please do not forget to like the answer if it helps you. Thank you.