Using the fd list in problem 1 identify the fds that

Question

1. Using the following FD list for a simplified expense report database, identify insertion, update, and deletion anomalies if all columns are in one table (big expense report table). There are two candidate keys for the big expense report table: ExpItemNo (expense item number) and the com­ bination of CatNo (category number) and ERNo (expense report number). ExpItemNo is the pri­mary key of the table.

• ERNo ->UserNo, ERSubmitDate, ERStatusDate
• ExpItemNo ->ExpItemDesc, ExpItemDate, ExpItemAmt, CatNo, ERNo
• UserNo ->UserFirstName, UserLastName, UserPhone, UserEmail
• CatNo ->CatName, CatLimit
• ERNo, CatNo ->ExpItemNo
• UserEmail -» UserNo
• CatName ->CatNo

2. Using the FD list in problem 1, identify the FDs that violate 2NF. Using knowledge of the FDs that violate 2NF, design a collection of tables that satisfies 2NF but not 3NF.

3. Using the FD list in problem 1, identify the FDs that violate 3NF. Using knowledge of the FDs that violate 2NF, design a collection of tables that satisfies 3NF.

4. Apply the simple synthesis procedure to produce BCNF tables using the FD list given in prob­ lem 13. Show the results of each step in your analysis.

Answer 1

Use the following equations to complete this assignment such as: 1. Using the subsequent FD list for a simplified expense report database, recognize insertion, update, and deletion anomalies if all columns are in one table (big expense report table). There are 2 candidate keys for the big expense report table: ExpItemNo (expense item number) and the amalgamation of CatNo (category number) and ERNo (expense report no). ExpItemNo is the pri­mary key of the table.