Answer All questions. Save the file in Google Classroom as you have done for the other lab assignments. Work that come in late will not be marked. Only the first file sent will be marked. Please don't zip the file. Do not email the file to me. A church in a certain parish would like to store information on all their members who are all living in that community and the activities that their committees coordinate. The church would like to know the date of birth, first name, last date, telephone number, email addresses, gender and mailing address of each member. Member ID is made up of only four character. The church currently has several committees that members may join if they want to give service to the church. Committees have names, and an annual budget. A member may serve on several committees and church would like to know when the member joined and ended their service on the committee. While serving on a committee the church would like to know what position that person holds. MEMBER (MEMBER ID, MEM_NAME, DOB, ADDRESS, EMAIL, PHONE) FOREIGN KEY MEMBER_ID REFERENCES MEMBER COMMITTEE (COM NAME, BUDGET, DURATION, MEETING_TIME) SERVICE (MEMBER ID, COM NAME, POSITION, START_DATE, END_DATE) FOREIGN KEY MEMBER_ID REFERENCES MEMBER FOREIGN KEY COM_NAME REFERENCES COMMITTEE MEMBER MEMBER FIRST LAST NAME NAME ID DK20 AD01 ME05 CK06 BS01 COMMITTEE COMMITTEE NAME FINANCE NEW MEMBERS OUTREACH a. b. DEVON KING ABBAK DEAN MERLE CLARK BOBBY C. d. e. EBANKS KENT SPENCE f. ADDRESS 4 GARDEN ROAD M 6 FLINT ST. 10 KING ST 4 SUPER LANE 12 MARLEY RD GENDER DOB 20,000 10,000 30,000 BUDGET ($) MEETING TIME F F M M EVERY 1ST MONDAY ONCE PER QUARTER EMAIL1 dk@hot.com 3/13/2000 6/20/1950 ad@hot.com 11/20/1980 me@yah.com 7/23/1960 ck@out.com 8/11/1982 Create Table Statements Using appropriate data types Using appropriate Foreign Keys DURATION (Hours) 2 Task 1 - Implement the Database using SQL from the RN shown above. [30 marks] Create a database called CHURCH_WORKS to be used for the rest of the exercises. 1 2 PHONE (876) 567-6543 (876) 234-2134 (876) 997-6578 (876) 657-9876 Devon King served as chairperson of the of the finance committee from 1/1/2020 to present Clark Kent is chairperson of the Outreach Committee from 1/1/2020 to 12/31/2020 and he also serves on the Finance Committee as secretary for the same period. Merle Ebanks is chairperson of the New Members Committee from 1/1/2020 to present and also sits on the Outreach Committee for the same period Bobby Spence served for six months on both the Finance and the New Members Committee from June 1, 2020. Ensure that appropriate fields do not accept null values based on Tables 1, 2 and 3. Delete the Mem_name field and add Mem_FName and Mem_LName [3] [12] [3] [3] [3] [2+2+2] Task 2 -Demonstrate interacting with data [60 marks] a. Develop the appropriate full insert statements for all your tables using the information only in tables 1, 2 and other information given. [12] b. Develop the appropriate partial insert statements for all your tables using the information only in tables 1, 2 and other information given [6] c. Update OUTREACH: Set Meeting Time to Every 3rd Sunday [2] d. Add two new records into the COMMITTEE table (one full and one partial insert) [4] e. Write select all statements for all your tables. [6] f. Delete the partial records that you inserted into the COMMITTEE table. [2] g. Display the member id, first and last name, address, DOB and age for all the female members. Sort by last name in ascending order and DOB in descending order. (The first and last names should be in one column) [6] h. Using a Join: Display all members who served on the committees, give their names, the committee name and the length of time (in months) that they served on these committees. i. Using a subquery: Display the committee with the lowest budget? j. Using the four types of Join: Display the Members Names, their emails, their position, the committee name, meeting time, starting and ending date on the committees. Explain the differences if any. Task 3-Functions [10 marks] a. Using one select statement: Display the highest, lowest, average and total annual budget of all the committees. b. For 2023 all budgets will be increased by 10%. Display the Committee name, the budget as current budget, and the new budget as 2023 Budget. [5] [5] [12] [5] [5]