Assignment #5 PART A StdSSN StdCity SI SEATTLE SI SEATTLE S2 BOTHELL S2 BOTHELL StdClass TUN JUN JUN JUN Offer No 01 02 03 02 OffTerm FALL FALL SPRING FALL OffYear 2006 2006 2007 2006 EnrGrade CourseNo CrsDesc 3.5 DB 3.3 C2 VB 3.1 00 3.4 C2 VB allole Gisiololol 1. What do you mean by anomalies and why it is present in relation? 2. Discuss different type of anomalies in above table StaffBranch Table branch No branchAddress telNo B001 503-555-3618 B001 503-555-3618 stailNo name position salary SI500 Tom Daniels Manager 46000 S0003 Sally Adams Assistant 30000 S0010 Mary Martinez Manager 50000 S3250 Robert Chin Supervisor 32000 S2250 Sally Stern Manager 48000 30415 Art Peters Manager 41000 5002 206-555-6756 8 Jefferson Way, Portland, OR 97201 8 Jefferson Way, Portland, OR 97201 City Center Plaza, Seattle, WA 98122 City Center Plaza, Seattle, WA 98122 16 - 14th Avenue, Seattle, WA 98128 14 - 8th Avenue, New York, NY 10012 B002 206-555-6756 B004 206-555-3131 B003 212-371-3000 1. Do any anomalies exist in above relation? If yes explain each anomaly above relation 2. Normalize the above relation to 3NF PART B Q1: Apply 1st, 2nd and 3rd Normal Form the following scenario Project Management Report Project Code: Project Title: PC010 Pensions System Project Manager: 24,500 M Phillips Project Budget: Employee No. Employee Name Department No. Department Name Hourly Rate S10001 S10030 S21010 S00232 A Smith L Jones P Lewis R Smith L004 LO23 L004 L003 IT Pensions IT Programming 122.00 18.50 21.00 26.00 Total Staff on Project: 4 Average Hourly Rate: 21.88 Calculated Fields Q2: branch No branchAddress 8 Jefferson Way, Portland, OR 97201 te/Nos 503-555-3618, 503-555-2727, 503-555-6534 B001 B002 City Center Plaza, Seattle, WA 98122 206-555-6756, 206-555-8836 B003 14 - 8th Avenue, New York, NY 10012 212-371-3000 B004 16 14th Avenue, Seattle, WA 98128 206-555-3131, 206-555-4112 1. Why is this table not in 1NF? 2. Describe and illustrate the process of normalizing the data shown in this table to third normal form (3NF). 3. Identify the primary, alternate and foreign keys in your 3NF relations. Q3: 9:18 pm A. a) 27/12/20 position hours PerWeek name staff No branch No branchAddress Assistant 16 Ellen Layman S4555 B002 City Center Plaza, Seattle, WA 98122 Ellen Layman Assistant 9 S4555 B004 16 - 14th Avenue, Seattle, WA 98128 Assistant 14 Dave Sinclair S4612 B002 City Center Plaza, Seattle, WA 98122 Assistant Dave Sinclair 10 S4612 B004 16 - 14th Avenue, Seattle, WA 98128 1. Is this relation is in 1st NF? 2. Check if it is in 2NF 3. Normalized the relation in 3NF 4. Identify the primary, (alternate) and foreign keys in your 3NF relations. Q4: branch No branchAddress telno mgr Stoff No name HOOL 8 Jefferson Way, Portland, OR 97201 503-555-3618 S1500 Tom Daniels B002 City Center Plaza, Seattle, WA 98122 206-555-6756 S0010 Mary Martinez B003 14- 8th Avenue, New York, NY 10012 212-371-3000 16 - 14th Avenue, Seattle, WA 98128 206-555-3131 S0145 Art Peters B004 52250 Sally Stem 1. Why is this table not in 3NF? 2. Describe and illustrate the process of normalizing the data shown in this table to third normal form (3NF). 3. Identify the primary, (alternate) and foreign keys in your 3NF relations Submission Guide Lines: 1. Submit assignment in hard form as well as in soft form on slate. 2. Submit till 18 December 2021 End of document Sarens 12o