Please Help Consider the following relations, and for each relation, a) Identify

Question

Please Help

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. Work l(cmpId, empName, dateHired, jobTitle, jobLevel) Assumption: Each employee has unique empId. ii. 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. iii. Work3(empId, empName, projectNo, projectName, projBudget, empManager, hoursAssigned) Assumption: Each employee can be assigned to more than one project. iv. 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. v. Work5(empId, empName, empSSN, dependentName, dependentAddress, rclationtoEmp) 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. Suppose you are given a relation R with four attributes ABCD. For each of the following sets of FDs and super keys, do the following: a. Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF). b. If R is not in BCNF, can you decompose it into a set of BCNF relations that preserve the FDs? Identify any FDs that could not be preserved from the decomposition. i. Super keys: B FDs: C rightarrow D, C rightarrow A, B rightarrow C ii. Super keys: BD FDs: B rightarrow C, D rightarrow A iii. Super keys: ABC, BCD FDs: ABC rightarrow D, D rightarrow A iv. Super keys: A FDs: A rightarrow B, BC rightarrow D, A rightarrow C v. Super keys: AB, BC, CD, AD FDs: AB rightarrow C, AB rightarrow D, C rightarrow A, D rightarrow B

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)

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---