Description of Homework Consider the following relations, and for each relation,
ID: 3560226 • Letter: D
Question
Description of Homework
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
1. Work1(empId, empName, dateHired, jobTitle, jobLevel)
Assumption: Each employee has unique empId.
2. Work2(empId, 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.
3. Work3(empId, empName, projectNo, projectName, projBudget, empManager,
hoursAssigned)
Assumption: Each employee can be assigned to more than one project.
4. Work4(empId, empName, schoolAttended, degreeAwarded, graduationDate)
Assumption: More than one degree can be stored for an employee, however an
employee never earns the same degree twice.
5. Work5(empId, 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
Explanation / Answer
1. Work1(empId, empName, dateHired, jobTitle, jobLevel)
Assumption: Each employee has unique empId.
a) primary key : empId
b) Identify the functional dependencies : empId -> empName, dateHired, jobTitle,jobTitle->jobLevel
c) Is the relation in 2NF? : No. Work1(empId, empName, dateHired, jobTitle) and Work2(jobTitle, jobLevel)
d) Is the relation in 3NF? Now Work1 and Work2 are in 3NF also
e) Is the relation in BCNF? Yes
2. Work2(empId, 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.
a) primary key : empId,ratingDate because one rating can be given to an employee on a given date.
b) Identify the functional dependencies : empId -> empName,jobTitle empId,ratingDate -> raterName,rating
c) Is the relation in 2NF? : No. Work2(empId, empName, jobTitle) and Work3(empId, ratingDate, raterName,rating)
d) Is the relation in 3NF? Now Work2 and Work3 are in 3NF also
e) Is the relation in BCNF? Yes
3. Work3(empId, empName, projectNo, projectName, projBudget, empManager,
hoursAssigned)
Assumption: Each employee can be assigned to more than one project.
a) primary key : empId,ratingDate because one rating can be given to an employee on a given date.
b) Identify the functional dependencies : empId -> empName,projectNo projectNo-> projectName, projBudget empId,projectNo -> empManager,hoursAssigned
c) Is the relation in 2NF? : No. Work3(empId,empName) Project(projectNo, projectName, projBudget) EmployeeProject(empId,projectNo,empManager,hoursAssigned) primary key:empId,projectNo
d) Is the relation in 3NF? Now it is
e) Is the relation in BCNF? Yes
4. Work4(empId, empName, schoolAttended, degreeAwarded, graduationDate)
Assumption: More than one degree can be stored for an employee, however an
employee never earns the same degree twice.
a) primary key : empId,degreeAwarded because an employee never earns the same degree twice.
b) Identify the functional dependencies : empId -> empName empId,degreeAwarded -> schoolAttended,graduationDate
c) Is the relation in 2NF? : No. Work4(empId, empName) and Work5(empId, schoolAttended, degreeAwarded, graduationDate)
d) Is the relation in 3NF? No. Work5(empId,degreeAwarded,graduationDate) Work5(empId,degreeAwarded, schoolAttended) empId,degreeAwarded is the primary key
e) Is the relation in BCNF? Yes
5. Work5(empId, 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
Work5(empId, empName, empSSN)
DependentEmployee(empId, dependentId , relationtoEmp) empId, dependentId is the primary key
Dependent(dependentId,dependentName,dependentAddress)