PE5

ISTE-230 Introduction to Database & Data Modeling Practice Exercise # 5 – Normalization through 2NF Name: _______Aedan Lahiff______________________________________ All assignments will be graded with regard to the standards that were discussed in class, which can be found in the Standards Content area. For each problem below, given the original relation and functional dependencies, normalize the original and all resulting relations to 2NF. Be sure to use proper relational notation: RELATION(pkattr , attribute, fkattr ). Include reference statements for foreign keys. Problem #1 EMPLOYEE2 empID name deptName salary courseTitle dateCompleted 100 Margaret Simpson Marketing 48,000 SPSS 6/19/2019 100 Margaret Simpson Marketing 48,000 Surveys 10/7/2019 140 Alan Beeton Accounting 52,000 Tax Acc 12/8/2019 110 Chris Lucero Info Systems 43,000 SPSS 1/12/2019 110 Chris Lucero Info Systems 43,000 C++ 4/22/2019 190 Lorenzo Davis Finance 55,000 150 Susan Martin Marketing 42,000 SPSS 6/19/2019 150 Susan Martin Marketing 42,000 Java 8/12/2019 EMPLOYEE2(empID , name, deptName, salary, courseTitle , dateCompleted) Functional Dependencies: empID, courseTitle è name, deptName, salary, dateCompleted empID è name, deptName, salary YOUR ANSWER (Final set of relations normalized to 2NF): EMPLOYEE(empID , name, deptName, salary) EMP_COURSE( empID , courseTitle , dateCompleted) ISTE-230 – Introduction to Database and Data Modeling Page 1 of 4 ©Elissa Weeden, David Patric 2020