Homework SourseRegional Labs is a company that conducts research and develo
Regional Labs is a company that conducts research and development work on a contract basis for other companies and organizations. Figure below shows that regional Labs collects about projects and the employees assigned to them. This data is stored in a relation (table) named PROJECT
PROJECT (ProjectID, EmployeeName, EmployeeSalary)
A. assuming that all functional dependencies are apparent in this data, which of the following are true?
1. ProjectID EmployeeName
2. ProjectID EmployeeSalary
3. (ProjectID, EmployeeName) EmployeeSalary
4. EmployeeName EmployeeSalary
5. EmployeeSalary ProjectID
6. EmployeeSalary (ProjectID, EmployeeName)
B. What is the primary key of PROJECT?
C. Are all the non-key attributes (if any) dependent on the primary key?
D. In what normal form is PROJECT?
E. Describe two modification anomalies that affect project
F. Is ProjectID a determinant? if so, based on which function dependencies is part A?
G. Is EmployeeName a determinant? if so, based on which functional dependencies in part A?
H. Is (ProjectID, EmployeeName) a determinant? if so, based on which functional dependencies in part A?
I. Is EmployeeSalary a determinant? if so, based on which functional dependencies in part A?
J. Does this relation contain a trasitive dependency? if so, what is it?
K. Redesign the relation to eliminate modification anomalies.
| ProjectID | EmployeeName | EmployeeSalary |
| 100-A | Eric Jones | 64,000.00 |
| 100-A | Donna Smith | 70,000.00 |
| 100-B | Donna Smith | 70,000.00 |
| 200-A | Eric Jones | 64,000.00 |
| 200-B | Eric Jones | 64,000.00 |
| 200-C | Eric Parks | 58,000.00 |
| 200-C | Donna Smith | 70,000.00 |
| 200-D | Eric Parks | 58,000.00 |
Solution
A. EmployeeSalary (ProjectID, EmployeeName)
B. There is no primary key
C. No
D. 1NF
E. If suppose employee \"Donna Smith\" leaves the company and we do not want his data tobe in the PROJECT table. If we delete his data we will loose the all his details like in which project he was working and what was his salary.This is called Delete anomaly\'F. No, ProjectID is not determinant here.
G. No, EmployeeName is not determinant here, by mentioning EmployeeName we are not able to determine remaining fields of PROJECT.
H. Yes,(ProjectID, EmployeeName) a determinant.\"(ProjectID, EmployeeName) EmployeeSalary\".
I. No, EmployeeSalary is not determinant here, by mentioning EmployeeSalary we are not able to determine remaining fields of PROJECT.
J. Yes. Here ProjectID depend on EmployeeName (ProjectID->EmployeeName), EmployeeName depends on EmployeeSalary (EmployeeName->EmpProjectIDloyeeSalary) but not on ProjectID.
H. We need to split the PROJECT table in to two tables to eliminate the modification anomaly.
EMPLOYEE
EmployeeID
EmployeeName
ProjectID
1
Eric Jones
100-A
2
Donna Smith
100-A
3
Donna Smith
100-B
4
Eric Jones
200-A
5
Eric Jones
200-B
6
Eric Parks
200-C
7
Donna Smith
200-C
8
Eric Parks
200-D
PROJECT
EmployeeID
ProjectID
Salary
1
100-A
64,000.00
2
100-A
70,000.00
3
100-B
70,000.00
4
200-A
64,000.00
5
200-B
64,000.00
6
200-C
58,000.00
7
200-C
70,000.00
| EmployeeID | EmployeeName | ProjectID |
| 1 | Eric Jones | 100-A |
| 2 | Donna Smith | 100-A |
| 3 | Donna Smith | 100-B |
| 4 | Eric Jones | 200-A |
| 5 | Eric Jones | 200-B |
| 6 | Eric Parks | 200-C |
| 7 | Donna Smith | 200-C |
| 8 | Eric Parks | 200-D |
