Texts:
1. Identify the candidate keys of R based on the Functional Dependencies. You need to use the concept of attribute closure to identify the keys. Intermediate steps in this process should be summarized.
2. 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 sufficient.
• Please indicate the primary keys for the normalized tables;
• Show the detailed normalization process, rather than only the final normalization result
Consider the following relation schema for table R:
R(ENo, CNo, PNo, EName, ERoom, EPhone, CSize, PAmount)
Relation R contains all the information involved in the modeling in respect to employees, clubs, and projects in a company. Attributes starting with "E" refer to employees, those starting with "C" refer to clubs, and those with "P" to projects. Employees, clubs, and projects are each identified by their unique numbers.
Names for employees are not generally unique. An employee is allocated with only one room and phone number, but a room and a phone number can be shared by a few employees. A room may be associated with a few different phone numbers, but a phone number is only mapped to a single room. Each club is characterized by its own size (i.e., the number of employees belonging to this club). However, multiple clubs may be of the same size. Each project has an amount of funding associated with it. Yet, multiple projects may be supported with the same amount of funding. An employee may be involved in different clubs and different projects. Also, a club may have multiple employees as its members and a project may involve multiple employees.