SUBJECT: DESIGN OF DATABSE NOTE: PLEASE PROVIDE SOLUTION TO THIS QUESTION THANKS

Question

SUBJECT: DESIGN OF DATABSE

NOTE: PLEASE PROVIDE SOLUTION TO THIS QUESTION

THANKS

Description of Homework 1. Consider the following relations, and for each relation, a) Identify a primary key b) Identify the functional dependencies c) Is the relation in 2NF? If not, find a 2NF decomposition d) Is the relation in 3NF? If not, find a 3NF decomposition e) Is the relation in BCNF? If not, find a decomposition that is in BCNF. Identify any functional dependencies that are not preserved i. Workl(empld, empName, dateHired, jobTitle, jobLevel) Assumption: Each employee has unique empld. ii. Work2(empld, empName, jobTitle, ratingDate, raterName, rating) Assumption: Multiple ratings can be stored for each employee, but a maximum of one rating can be given to an employee on a given date ii. Work3(empld, empName, projectNo, projectName, projBudget, empManager, hoursAssigned) Assumption: Each employee can be assigned to more than one project. iv. Work4(empld, empName, schoolAttended, degreeAwarded, graduationDate) Assumption: More than one degree can be stored for an employee, however an employee never earns the same degree twice Work5(empld, empName, empSSN, dependentName, dependentAddress relationtoEmp) Assumption: An employee may have several dependents, and different dependents can have same name, however no two dependents with same relation to employee has same name v.

Explanation / Answer

1)
i)
a)pk - empId
b)There are not fun. dependencies
c)2NF
d)3NF
e)BCNF
ii)
a)pk - (empId, ratingDate)
b)partial dependencies
empId -> empName
empId -> jobTitle
c) Not in 2NF
d) Not in 3NF
e) Not in BCNF

iii)
a)pk - (empId, projectNo)
b) partial dependencies
empId -> empName
projectNo -> projectName
empId -> empManager
c) Not in 2NF
d) Not in 3NF
e) Not in BCNF

iv) a)pk - (empId, degreeAwarded)
b) partial dependenciess
empId -> empName
c) Not in 2NF
d) Not in 3NF
e) Not in BCNF

v) a)pk - (empId, relationtoEmp)
b)partial dependencies
empId -> empName
c) Not in 2NF
d) Not in 3NF
e) Not in BCNF

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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