Question 1 [40 points]:During a consulting job for a company working on green-energy products, you come across an in-house project management system that relies on a badly designed database. Being a good-hearted person, you decide to fix the database by performing normalization. A sample of the data stored in the current database is displayed in the following page. The database consists of a single table containing six attributes related to projects and employees. The database contains many candidate keys, but the composite primary key is a priori defined as (ProjNo, EmpNo).

a) Redesign the original database so that it satisfies the second normal form. You do not have to list FDs for all candidate keys (there are many of them!!!). Instead, list all the FDs where each individual component of the PK as well as the composite PK are the determinants. Use your FD list to create new tables (see Module 7 Part 1). Give suitable names to the new tables and show how the sample data in Table 1 are now stored in the new tables. There is no need to code the tables in SQL. [10 points]

b) Convert the new design from a) to the third normal form. Remember that you have to check whether or not each new table from a) is in 3NF. If a table is not in 3NF, then you have to split that table so that the 3NF requirements are satisfied (see Module 7 Part 2). In particular, for each table from a), start by listing the transitive dependencies XYZ, where X is a candidate key and Y is a non-candidate key. Similar to what you did in a), focus on cases where X is either ProjNo, EmpNo, or (ProjNo, EmpNo). Give suitable names to the new tables and show how the sample data in Table 1 are now stored in the new tables. There is no need to code the tables in SQL. [10 points]

c) Based on the new database design from b), create a physical database model using Data Modeler where attribute types and the relationships between attributes are clearly specified (i.e., the PK-FK pairs). [10 points]

d) Reflect on the new database model. In particular, explicitly describe by means of examples 3 anomalies (one insertion, one deletion, and one update anomaly) that were present in the original design of the database and that are no longer present in the final design you developed in c). [10 points]

114 ProjNo 15 15 15 15 15 18 18 18 18 22 22 22 22 22 25 Table 1: Sample from the original database ProjName EmpNo Job Type PriceHour WorkHours Solar City 103 Engineer 84.50 23.8 Solar City 101 Database Designer 105 19.4 Solar City 105 Database Designer 105 35.7 Solar City 106 Programmer 35.75 12.6 Solar City 102 Systems Analyst 96.75 23.8 Fuel Cell App Designer 48.10 24.6 Fuel Cell 118 General Support 18.36 45.3 Fuel Cell 104 Systems Analyst 96.75 32.4 Fuel Cell 112 DSS Analyst 45.95 44 Chargers V3 105 Database Designer 105 64.7 Chargers V3 104 Systems Analyst 96.75 48.4 Chargers V3 113 App Designer 48.10 23.6 Chargers V3 111 Clerical Support 18.36 22 Chargers V3 106 Programmer 35.75 12.8 Hybrid EV 107 Programmer 35.75 24.6 Page 2 25 25 25 25 25 25 Hybrid EV Hybrid EV Hybrid EV Hybrid EV Hybrid EV Hybrid EV 115 101 114 108 118 112 Systems Analyst Database Designer App Designer Systems Analyst General Support DSS Analyst 96.75 105 48.10 96.75 18.36 45.95 45.8 56.3 33.1 23.6 30.5 41.4 114 ProjNo 15 15 15 15 15 18 18 18 18 22 22 22 22 22 25 Table 1: Sample from the original database ProjName EmpNo Job Type PriceHour WorkHours Solar City 103 Engineer 84.50 23.8 Solar City 101 Database Designer 105 19.4 Solar City 105 Database Designer 105 35.7 Solar City 106 Programmer 35.75 12.6 Solar City 102 Systems Analyst 96.75 23.8 Fuel Cell App Designer 48.10 24.6 Fuel Cell 118 General Support 18.36 45.3 Fuel Cell 104 Systems Analyst 96.75 32.4 Fuel Cell 112 DSS Analyst 45.95 44 Chargers V3 105 Database Designer 105 64.7 Chargers V3 104 Systems Analyst 96.75 48.4 Chargers V3 113 App Designer 48.10 23.6 Chargers V3 111 Clerical Support 18.36 22 Chargers V3 106 Programmer 35.75 12.8 Hybrid EV 107 Programmer 35.75 24.6 Page 2 25 25 25 25 25 25 Hybrid EV Hybrid EV Hybrid EV Hybrid EV Hybrid EV Hybrid EV 115 101 114 108 118 112 Systems Analyst Database Designer App Designer Systems Analyst General Support DSS Analyst 96.75 105 48.10 96.75 18.36 45.95 45.8 56.3 33.1 23.6 30.5 41.4