Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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. Work1(empId, 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, degree Awarded, 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, 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. 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 Super keys: AB, BC, CD, AD FDs: AB rightarrow C, AB rightarrow D, C rightarrow A, D rightarrow BStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started