1) Consider the relation A, with key and functional dependencies shown below. I. What Normal form is A in right now? Why? II. III. What actions would you take to normalize A to the next higher normal form (describe the steps)? Follow those steps you mentioned in the prior question to normalize A to the next higher normal form. Be sure to show all work. IV. V. Once you have normalized A, what normal forms are each the two new relations in? Why? If any of the remaining relations are not in 3NF, normalize them to 3NF. Be sure to show all work. P101 P102 P103 P103 P104 P105 P106 P111 P115 P116 P116 P117 A (p, r, s, t, u, v) P, r p, rs, t, u, v Key: FD1: FD2: FD3: 2) Given the Project relation below with sample data, write down the Key and Functional Dependencies. Then normalize the relation to 3NF. Be sure to follow all steps and show your work. Projects (ProjectID (Key), Contact Person, Street, City, State, ZipCode, PhaselD (Key), Name, Budget) Sample Data: rs, v tu ProjectID Contact Person Street Fawcett Smith Roberts Smith Grand Smith Smith Smith Alexander Davis Alexander Collins Laughton Laughton Smith City State ZipCode PhaselD Name Lincoln NE 68501 P52 Paving Lincoln NE 68501 | P50 Sidewalk Repairs Fremont NE 68025 P51 Fremont NE 68025 | P50 68505 P51 22nd 68601 P52 NE Columbus NE Bird Omaha NE Sloan Omaha 68007 P51 NE Lincoln Omaha NE Atwood KS Sun St Sun St Atwood KS Main Atwood KS Grand Main Lincoln 68111 P53 68007 P51 67730 P55 67730 P51 67730 | 150 Curb Replacement Sidewalk Repairs Curb Replacement Paving Curb Replacement ADA Crosswalk Curb Replacement ADA Crosswalk Curb Replacement Sidewalk Repairs Budget 1,025 1,575 1,050 1,575 1,050 1,050 1,300 1,500 1,050 1,000 1,100 1,050 Note that a few of the Projects are managed by the same Contact Person. Some cities have more than one ZipCode. Budget is in 1,000's of dollars. 1) Consider the relation A, with key and functional dependencies shown below. I. What Normal form is A in right now? Why? II. III. What actions would you take to normalize A to the next higher normal form (describe the steps)? Follow those steps you mentioned in the prior question to normalize A to the next higher normal form. Be sure to show all work. IV. V. Once you have normalized A, what normal forms are each the two new relations in? Why? If any of the remaining relations are not in 3NF, normalize them to 3NF. Be sure to show all work. P101 P102 P103 P103 P104 P105 P106 P111 P115 P116 P116 P117 A ( p, r, s, t, u, v) P, r p, rs, t, u, v Key: FD1: FD2: FD3: 2) Given the Project relation below with sample data, write down the Key and Functional Dependencies. Then normalize the relation to 3NF. Be sure to follow all steps and show your work. Projects (ProjectID (Key), Contact Person, Street, City, State, ZipCode, PhaselD (Key), Name, Budget) Sample Data: rs, v tu ProjectID Contact Person Street Fawcett Smith Roberts Smith Grand Smith Smith Smith Alexander Davis Alexander Collins Laughton Laughton Smith City State ZipCode PhaselD Name Lincoln NE 68501 P52 Paving Lincoln NE 68501 | P50 Sidewalk Repairs Fremont NE 68025 P51 Fremont NE 68025 | P50 68505 P51 22nd 68601 P52 NE Columbus NE Bird Omaha NE Sloan Omaha 68007 P51 NE Lincoln Omaha NE Atwood KS Sun St Sun St Atwood KS Main Atwood KS Grand Main Lincoln 68111 P53 68007 P51 67730 P55 67730 P51 67730 | 150 Curb Replacement Sidewalk Repairs Curb Replacement Paving Curb Replacement ADA Crosswalk Curb Replacement ADA Crosswalk Curb Replacement Sidewalk Repairs Budget 1,025 1,575 1,050 1,575 1,050 1,050 1,300 1,500 1,050 1,000 1,100 1,050 Note that a few of the Projects are managed by the same Contact Person. Some cities have more than one ZipCode. Budget is in 1,000's of dollars.