Create An ERD For The Following Scenario: Small Company ABC

Create An ERD For The Following Scenarioa Small Company Abc Wants A D

Create an ERD for the following scenario. A small company ABC wants a database to keep track of internal company information. Given the following information create an ERD. The ABC Company has several departments and several employees. A division operates many departments but each department is operated by only one division. Most employees reside in a single department however, the company has "rovers" that aren't assigned any department. Employees are assigned to projects along with other employees. If a project doesn't have any employees assigned it is cancelled. Each division has a single manager and each department has a single manager. Employees cannot manage more than one division or department. Here is a link to follow but create your own.

Paper For Above instruction

The Entity-Relationship Diagram (ERD) for ABC Company aims to visually represent the complex relationships among its various entities, which include divisions, departments, employees, projects, and managers. This ERD helps in understanding how data is interconnected within the organization, facilitating database design that can efficiently support the company's internal information management.

Entities and Attributes

The primary entities identified are Division, Department, Employee, Project, and Manager. Each has specific attributes necessary for database implementation:

- Division: DivisionID (Primary Key), DivisionName, Description

- Department: DepartmentID (Primary Key), DepartmentName, DivisionID (Foreign Key), ManagerID (Foreign Key)

- Employee: EmployeeID (Primary Key), EmployeeName, DepartmentID (Foreign Key, nullable), IsRover (Boolean)

- Project: ProjectID (Primary Key), ProjectName, Status

- Manager: EmployeeID (Primary Key), Name, ManagedEntity (Division or Department)

Relationships and Constraints

- Division operates many departments: A one-to-many relationship from Division to Department.

- Each department is operated by only one division: Represented by the foreign key DivisionID in Department.

- Most employees reside in a single department: Many-to-one from Employee to Department, with allowance for employees with no department (rovers) indicated by a nullable DepartmentID.

- Employees are assigned to projects along with other employees: Many-to-many relationship between Employee and Project, with an associative entity called Assignment, which may include roles or hours worked.

- Projects without associated employees are canceled: Enforced by the business rule within data validation or application logic.

- Each division has a single manager: One-to-one relationship between Division and Employee, with Employee managing exactly one division.

- Each department has a single manager: One-to-one relationship between Department and Employee, with Employee managing exactly one department.

- Employees cannot manage more than one division or department: Ensured by the constraint that an Employee can be linked as a manager to only one entity.

Diagram Highlights

The ERD diagram visually depicts these entities and relationships with appropriate cardinalities:

- Division (1) — (Many) Department

- Department (1) — (Many) Employee

- Employee (Many) — (Many) Project (via Assignment)

- Division (1) — (1) Employee (Manager)

- Department (1) — (1) Employee (Manager)

- Employee (1) manages at most one division or department, ensuring exclusivity.

Conclusion

This ERD effectively models ABC Company’s organizational structure, detailing how divisions, departments, employees, and projects interact. It emphasizes the importance of constraints such as one-to-one manager relationships and the allowance for 'rovers' employees not assigned to any department. Designing the database based on this ERD will enable accurate and efficient tracking of internal company information, accommodating future scalability and integrity constraints.

References

  • ACM Transactions on Database Systems, 1(1), 9–36.
  • Fundamentals of Database Systems (7th ed.). Pearson.
  • Relational Database Design and Implementation. Morgan Kaufmann.
  • Data Modeling Essentials. Morgan Kaufmann.
  • Database Modeling & Design. Elsevier.
  • Conceptual Database Design: An Entity-Relationship Approach. Benjamin/Cummings.
  • A First Course in Database Systems. Pearson.
  • Database Management Systems. McGraw-Hill.
  • An Introduction to Database Systems. Addison-Wesley.
  • IEEE Computer, 33(10), 68–76.

Related Assignments