Consider the below business rules of a consultancy company that has many departments. * Each employee...

Transcribed Image Text:

Consider the below business rules of a consultancy company that has many departments. * Each employee belongs to only one department. Each employee has a unique email address. * Each department has one manager and many employees. * Each project is owned by one department. * An employee can work on many projects with different roles but has one role for each project. * A project can have many employees and they can be from different departments. * The progress of projects is evaluated once a week by department managers. The evaluation of a project on a particular date has a rating, which is an integer between 0 (very bad) to 5 (excellent). Consider the below relational database schema of five relations for managing projects of the company. Possibly due to an ER model not well designed or incorrect mapping from the ER to relational model, the database schema is not fully in 3NF and contains data redundancy. Some example tuple is given to explain the meaning of attributes The Project Management Database Department(deptiD, deptName, manager, empID) Employee(empID, empName, deptiD, email) Project(projID, startYear, deptID) EmpProj(empID, projID, role) Evaluation (projID, manager, evalDate, rating) <2, Production', E5, E4> <E5, 'Ann', 4, abc@usa.com> <P3, 2000, 2> <E2', 'P6', Designer> <P3', 'E5, 11-01-2020', 5> Answer following questions: * For each given relation, write down all functional dependencies according to the business rules. If there are no functional dependencies among attributes, you must state so. Do not write redundant or trivial functional dependencies (e.g., Email => Email). * For each given relation, indicate the primary key (underlined) and explain if there are any other candidate keys. Indicate any foreign keys with an asterisk (*). For each relation, write down its highest normal form and state the reasons why it doesn't meet the requirements for the next higher level normal form. This is not required if the relation is in 3NF. 3. For each given relation not in 3NF, decompose it into relations in 3NF. 4. Following results in Question 3, replace relations after decomposition with the newly created relations (relations without decomposition remain unchanged). Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in 3NF and apply further decomposition if needed until all final relations are in 3NF. Write down the final relational database schema and indicate the primary key (underlined) and foreign key(s) (with an asterisk*) in each relation. Consider the below business rules of a consultancy company that has many departments. * Each employee belongs to only one department. Each employee has a unique email address. * Each department has one manager and many employees. * Each project is owned by one department. * An employee can work on many projects with different roles but has one role for each project. * A project can have many employees and they can be from different departments. * The progress of projects is evaluated once a week by department managers. The evaluation of a project on a particular date has a rating, which is an integer between 0 (very bad) to 5 (excellent). Consider the below relational database schema of five relations for managing projects of the company. Possibly due to an ER model not well designed or incorrect mapping from the ER to relational model, the database schema is not fully in 3NF and contains data redundancy. Some example tuple is given to explain the meaning of attributes The Project Management Database Department(deptiD, deptName, manager, empID) Employee(empID, empName, deptiD, email) Project(projID, startYear, deptID) EmpProj(empID, projID, role) Evaluation (projID, manager, evalDate, rating) <2, Production', E5, E4> <E5, 'Ann', 4, abc@usa.com> <P3, 2000, 2> <E2', 'P6', Designer> <P3', 'E5, 11-01-2020', 5> Answer following questions: * For each given relation, write down all functional dependencies according to the business rules. If there are no functional dependencies among attributes, you must state so. Do not write redundant or trivial functional dependencies (e.g., Email => Email). * For each given relation, indicate the primary key (underlined) and explain if there are any other candidate keys. Indicate any foreign keys with an asterisk (*). For each relation, write down its highest normal form and state the reasons why it doesn't meet the requirements for the next higher level normal form. This is not required if the relation is in 3NF. 3. For each given relation not in 3NF, decompose it into relations in 3NF. 4. Following results in Question 3, replace relations after decomposition with the newly created relations (relations without decomposition remain unchanged). Merge relations with a common primary key and remove any redundant subset relations. Check if the merged relations are in 3NF and apply further decomposition if needed until all final relations are in 3NF. Write down the final relational database schema and indicate the primary key (underlined) and foreign key(s) (with an asterisk*) in each relation.

Answer rating: 100% (QA)

STEP 1 lets analyze each given relation and apply the necessary steps to reach the 3NF Given Relations 1 DepartmentdeptID deptName manager empID Functional dependencies deptID deptName manager empID d... View the full answer