crow foot diagram
Problem Solutions
1. Use the following business rules to create a Crow’s Foot ERD. Write all appropriate connectivity’s and cardinalities in the ERD.
1.a. A department employs many employees, but each employee is employed by one department.
1.b. Some employees, known as “rovers,” are not assigned to any department.
1.c. A division operates many departments, but each department is operated by one division.
1.d. An employee may be assigned many projects, and a project may have many employees assigned to it.
1.e. A project must have at least one employee assigned to it.
1.f. One of the employees manages each department, and each department is managed by only one employee.
1.g. One of the employees runs each division, and each division is run by only one employee.
The answers to Problem 1 (all parts) are included in Figure P4.1.
Figure P4.1 Problem 1 ERD Solution
As you discuss the ERD shown in Figure P4.1, note that this design reflects several useful features that become especially important when the design is implemented. For example:
The ASSIGN entity is shown to be optional to the PROJECT. This decision makes sense from a practical perspective, because it lets you create a new project record without having to create a new assignment record. (If a new project is started, there will not yet be any assignments.)
The relationship expressed by “DEPARTMENT employs EMPLOYEE” is shown as mandatory on the EMPLOYEE side. This means that a DEPARTMENT must have at least one EMPLOYEE in order to have departmental status. However, DEPARTMENT is optional to EMPLOYEE, so an employee can be entered without entering a departmental FK value. If the existence of nulls is not acceptable, you can create a “No assignment” record in the DEPARTMENT table to be referenced in the EMPLOYEE table if an employee is not assigned to a department.
Note also the implications of the 1:1 “EMPLOYEE manages DEPARTMENT” relationship. The flip side of this relationship is that
1. Use the following business rules to create a Crow’s Foot ERD. Write all appropriate connectivity’s and cardinalities in the ERD.
1.a. A department employs many employees, but each employee is employed by one department.
1.b. Some employees, known as “rovers,” are not assigned to any department.
1.c. A division operates many departments, but each department is operated by one division.
1.d. An employee may be assigned many projects, and a project may have many employees assigned to it.
1.e. A project must have at least one employee assigned to it.
1.f. One of the employees manages each department, and each department is managed by only one employee.
1.g. One of the employees runs each division, and each division is run by only one employee.
The answers to Problem 1 (all parts) are included in Figure P4.1.
Figure P4.1 Problem 1 ERD Solution
As you discuss the ERD shown in Figure P4.1, note that this design reflects several useful features that become especially important when the design is implemented. For example:
The ASSIGN entity is shown to be optional to the PROJECT. This decision makes sense from a practical perspective, because it lets you create a new project record without having to create a new assignment record. (If a new project is started, there will not yet be any assignments.)
The relationship expressed by “DEPARTMENT employs EMPLOYEE” is shown as mandatory on the EMPLOYEE side. This means that a DEPARTMENT must have at least one EMPLOYEE in order to have departmental status. However, DEPARTMENT is optional to EMPLOYEE, so an employee can be entered without entering a departmental FK value. If the existence of nulls is not acceptable, you can create a “No assignment” record in the DEPARTMENT table to be referenced in the EMPLOYEE table if an employee is not assigned to a department.
Note also the implications of the 1:1 “EMPLOYEE manages DEPARTMENT” relationship. The flip side of this relationship is that