(Solved) : Consider Following Relation Schema Table R R Eno Dno Pno Ename Dname Pname Edob Pcity Pcou Q29635062 . . .
Consider the following relation schema for table R:
R(ENo,DNo,PNo,EName,DName,PName,Edob,PCity,PCountry,hours,rate)
Attributes starting with “E” refer to Employees, those startingwith “D” refer
to Departments, and those with “P” to Projects. Employees,Departments, and
Projects are identified by unique numbers. The number of hours andhourly rate
of pay for an employee to carry out a project are determined byhimself/herself
and the project. There may be multiple projects that are conductedin a de-
partment and multiple departments can be involved in a singleproject as well.
Any employee can be affiliated with a few different departments andwork in
multiple projects at the same time. A project may involve multipleemployees.
Names for employees, departments and projects are not generallyunique. A
project will only be conducted in a single city. Multiple citiesfrom the same
country may appear in the table, however, cities are uniquely namedwithin and
across all countries.
Answer the following questions:
(a) Identify the Functional Dependencies in R. Be sure to onlyinclude func-
tional dependencies that satisfy the following 4 rules: 1) Onlyinclude
non-trivial FDs; 2) Minimize the determinant (LHS), that is, onlyinclude
full FDs; 3) Maximize the RHS; and 4) Only include FDs that cannotbe
derived from other FDs using Armstrongs axioms. Please refer toPage 26
of the Module 10 lecture notes for the details of the aboverequirements.
(b) Identify the candidate key(s) of R based on the FunctionalDependencies.
You need to use the concept of attribute closure to identify thekey(s).
Intermediate steps in this process should be detailed.
(c) Assume that R is in 1NF. Now normalise the relation to 2NF,3NF, and
BCNF. Be sure to indicate the FDs you are removing at each step,and
why. Just giving the decompositions in each of the three NormalForms
is not sufficient.
Expert Answer
Answer to Consider Following Relation Schema Table R R Eno Dno Pno Ename Dname Pname Edob Pcity Pcou Q29635062 . . .
OR