Consider the below business rules of a consultancy company that has many departments. * Each employee...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
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
Related Book For
Database Systems Design Implementation and Management
ISBN: 978-1337627900
13th edition
Authors: Carlos Coronel, Steven Morris
Posted Date:
Students also viewed these databases questions
-
Why is commercial liability insurance often arranged in layers? What are the advantages and the disadvantages? 2. What is the difference between a following form policy and stand-alone excess...
-
Business Process, Inc. (BPI), a consulting company offering business process reengineering and application system development expertise, wants to develop a prototype of a simple University...
-
The NCAA (National Collegiate Athletic Association) wants to develop a database to keep track of information about college basketball. Each university team belongs to only one conference (e.g., the...
-
Lucy has just been promoted to a managerial position and given a new office. She is very fond of small Persian carpets and Native American paintings and wants to get some carpets and paintings for...
-
Unemployment imposes serious hardship on out-of-work individuals and their families. What are the costs of unemployment to society as a whole? (Keep in mind the production possibilities curve.)
-
On what basis have some tax protesters argued that the federal government has no right to collect income taxes?
-
What are the major differences between rational decision making and bounded rationality?
-
On June 30, 2008, Thorpe Companys total current assets were $250,000 and its total current liabilities were $125,000. On July 1, 2008, Thorpe issued a short-term note to a bank for $25,000 cash....
-
$1,000 at the beginning of the quarter deposits will be made into an account for 19 years and 3 months. What was the interest rate compounded quarterly associated with the account if there was $180,...
-
Letang Company has three divisions (R, S, and T), organized as decentralized profit centers. Division R produces the basic chemical Ranbax (in multiples of 1,000 pounds) and transfers it to Divisions...
-
14 Find the PERIMETER and AREA of the triangle in the diagram below A 5 BORS C 3 2
-
What are the main concepts to consider with the use of queuing theory? Explain why each concept is important. How might queuing improve processes generally in the public sector? How might queuing...
-
What is VALS and what are its components? Provide some examples and works cited?
-
What new network features are being considered for implementation? What area of the current network design needs upgrading? Why? This is where you are going to assess the current network design and...
-
What are specific examples of how Google and Neeva used strategic vision and leadership to meet their long term Information Technology goals. Compare and contrast the two organizations
-
1. Why are groups and teams different. 2. Describe the four developmental stages of groups. Relate each phase by discussing how it applies to an online class. 3. Select the top 10 success factors for...
-
s')= -6x+58 SCS 6-413 +9 d (sin `be) (S) 1 dk JT-x the desallatice of A Find f(t) = 65 -4+ + q B) 5(x) = -6x+5e"
-
7 A 29-year-old, previously healthy man suddenly collapses at a party where legal and illicit drugs are being used. Enroute to the hospital, he requires resuscitation with defibrillation to establish...
-
Describe the three data fragmentation strategies. Give some examples of each.
-
Using SQL statements, give some examples of how you would use the data dictionary to monitor the security of the database.
-
Discuss the following assertion: The web is not capable of performing transaction management.
-
Materials inventory shortage; returns; scrap; spoiled goods An examination of Slurpy Corporation's records reveals the following transactions: Required: Record the entries for each transaction. a. On...
-
JIT and cost control Buskin Bolts, Inc. produces 50,000 units each day and the average number of units in work in process is 200,000. The average annual inventory carrying cost percentage is 20% and...
-
Spoiled goods; loss charged to factory overhead; loss charged to job One of the tennis racquets that Game-Set-Match manufactures is a titanium model (Power Crunch) that sells for $149. The cost of...
Study smarter with the SolutionInn App