The following table is a sample of the data from a company, that store informati

Question

The following table is a sample of the data from a company, that store information about various projects and the employees that work on these projects The data are as follows: Proj Name Proj Mr Empl Hours Proj Budget Start Date Emp Salary Emp manager Emp Dept Rating Jupiter Jupiter Jupiter Maxima Maxima Maxima Smith Smith Smith Lee Lee Lee E101 E105 E110 E101 E110 E120 25 40 10 15 30 15 1000001/15/2015 1000001/15/2015 1000001/15/2015 200000 3/1/2014 2000003/1/2014 2000003/1/2014 60000Levine 55000Jones 43000Levine 60000Levine 43000Levine 45000Jones 10 12 10 10 10 15 You can make the following assumptions: 1. Each project has one manager 2. Many employee are assigned to each project. The attribute Hours tells the number of hours each employee has worked on the corresponding project and the attribute rating is the rating that this employee received for his work on this project. 3. Budget and start date refer to the budget and start date of the project 4. Emp Manager is the manager of the employee and may be different from the project manager Based on this information: 1. 2. 3. 4. 5. Is this relation in 1NF? If not create the 1NF. If yes explain why Write down the functional dependencies Use the functional dependencies to explain what the candidate key is. Transform to 2NF Transform to 3NF

Explanation / Answer

1
A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain. So the table is in 1NF

2
Proj_Name -> Proj_mgr, Proj_Budget, Start_Date
Emp_id -> Emp_Salary,Emp_manager, Emp_Dept
Proj_Name, Emp_id -> hours, Rating

3
F(Proj_Name, Emp_id) -> Proj_mgr, Proj_Budget, Start_Date, Emp_Salary,Emp_manager, Emp_Dept, hours, Rating
So, Proj_Name, Emp_id is the candidate key

4
2NF:
TABLE 1: Proj_Name, Proj_mgr, Proj_Budget, Start_Date
TABLE 2: Emp_id, Emp_Salary,Emp_manager, Emp_Dept
TABLE 3: Proj_Name, Emp_id, hours, Rating

Due to huge rush of questions, we are not able to solve more than 4 questions. Please co-operate

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.