Reference no: EM13330836

Consider the following relation schema for table R:

R(ENo;DNo;PNo;EName;DName;PName;Edob;Dmanager;PCity;PCountry; hours; rate) Attributes starting with "E" refer to Employees, those starting with "D" refer to Departments, and those with "P" to Projects. Employees, Departments, and Projects are identified by unique numbers. Each department has only one manager, but an employee can be managers for difierent departments. The number of hours and hourly rate of pay for a stafi to carry out a project are determined by the stafi and the project. There may be multiple projects that
are conducted in a department and multiple departments can be involved in a single project as well. Any employee can be afiliated with and work for any departments in any projects at the same time. Names are not generally unique; however, cities are uniquely named within and across all countries.

Answer the following questions:

(a) Identify the Functional Dependencies in R. Be sure to maximize the number of attributes on the right hand side (RHS) and minimize the number of attributes on the left hand side (LHS) of each FD. You should exclude any trivial FDs in your answer.

(b) Identify the Super Key of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the key. Intermediate steps in this process should be summarized.

(c) Assume that R is in 1NF. Now normalize 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 Normal Forms is not suficient.

While it is recommended to answer this question on just one page, you should use no more than two pages for this question.