Question: A software company employs several programmers and software engineers to develop proprietary software. The company uses a matrix structure in which employees may work in
A software company employs several programmers and software engineers to develop proprietary software. The company uses a matrix structure in which employees may work in different teams to complete the development requirements. However, at any given point in time, each employee is assigned to a single team to work on a specific project ie teams are formed to work on a given project As part of their continuous learning initiatives, employees will be assigned to a mentor to help them acquire new skills. Each employee may be assigned to several mentors over their tenure with the company if he or she needs. Likewise, each employee may mentor many employees during their employment if he or she volunteers. We would like to record employees id first and last name, their date of hire, date of birth, position, address, and their phone numbers. An employee may or may not have a phone. Each employee can register up to two phone numbers. We would also like to have a record of all mentormentee relationships; that is for any given person, we want to be able to tell who their mentor was, and when the training started and ended. The company has several ongoing or completed projects. Each employee may have worked on various projects and each project always has at least one programmer or engineer working on it We want to be able to tell who exactly works or worked on a given project, and what their role was. The following data of project will be captured: project id title, starting and ending date, budget, and revenue. The roles performed by each employee in his or her team for a project are also collected. However, an employee can perform a specific role in a project. There are three categories of roles: engineer, programmer, and accountant. For engineers, the field of expertise and potential certifications are captured; for programmers, the programming language and years of experience are collected; and for accountants, whether they are a CPA and the date of certification are collected. These projects are usually won through bids that specific departments propose. A department may propose many projects, but each project is assigned to a single department. For departments, we want to store the department id name, and office number. In addition, we would like to identify each departments current manager. Each department announces two days of the week as inperson workdays. For efficiency reasons, the decision is that when storing the workdays for each department, full name of each day is stored only once throughout the database. When a project is assigned to a department, resources such as budget should be allocated to the department as well. To allocate the budget, a budget plan should be prepared in cooperation with the accounting department. Furthermore, a department may need to work with external people or a third party to get assistance, if necessary. For assigned projects, a department also needs to work with external vendors that supply software or licenses. Finally, for auditing purposes, the company should store data on all projects performed by all departments within the last years. Thus, important data related to a project should be collected in a different place.
Design an ER diagram for the above scenario, following the given business rules. Provide business rules that you identify for developing relationships. In addition, identify if there is any supersubtype relationships.
Step by Step Solution
There are 3 Steps involved in it
1 Expert Approved Answer
Step: 1 Unlock
Question Has Been Solved by an Expert!
Get step-by-step solutions from verified subject matter experts
Step: 2 Unlock
Step: 3 Unlock
