Answer All questions. Save the file in Google Classroom as you have done for the other...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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] 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 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] 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] 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]
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
Miller with corporate and personal taxes ____________. Answer options are displayed below it. After Modigliani and Miller's (MM) original no-tax theory, they went on to develop another theory that...
-
How might technology shape business report formats and their delivery in the future?
-
Give the domain and the range of each function. Where arrows are drawn, assume the function continues in the indicated direction. -2 12 2 X
-
Continuing to focus on evidence associated with the act, concealment, and conversion, use the evidentiary material to continue the examination. In addition, as the examiner also start to think of...
-
Blanchard Company's accounts receivable subsidiary ledger reveals the following information: Blanchard Company's receivable collection experience indicates that, on average, losses have occurred as...
-
A customer enters a doctors office to acquire information about swine flu and the potential impact of the influenza. She speaks Creole and is lost in the building. Finally, she is able to locate a...
-
Problem 3-3B Preparing adjusting entries, adjusted trial balance, and financial statements A1 P1 P2 P3 Following is the unadjusted trial balance for Alonzo Institute as of December 31, 2015, which...
-
Philadelphia Swim Club is planning for the coming year. Investors would like to earn a 10% return on the company's $35,000,000 of assets. The company primarily incurs fixed costs to maintain the...
-
Isabel runs her familys small pineapple processing plant, which takes fresh pineapples and cuts them into pineapple rings, spears, and chunks. The capital stock of the firm consists of three machines...
-
What type of bond guarantees that if a contractor goes bankrupt on a project the surety will pay the necessary amount to complete the job?
-
Why is innovation so important to society?
-
Who are the three basic parties involved in any construction bonding arrangement?
-
Over the industry life cycle highlight when product innovation, process innovation and strategic innovation start to predominate. Explain why.
-
The file Banks.csv includes data on a sample of 20 banks. The Financial Condition column records the judgment of an expert on the financial condition of each bank. This outcome variable takes one of...
-
Cleaning Service Company's Trial Balance on December 31, 2020 is as follows: Account name Debit Credit Cash 700 Supplies Pre-paid insurance Pre-paid office rent Equipment Accumulated depreciation -...
-
Joan is a self-employed attorney in New York City. Joan took a trip to San Diego, CA, primarily for business, to consult with a client and take a short vacation. On the trip, Joan incurred the...
-
Carl and Jenny adopt a Russian orphan. The adoption takes 2 years and two trips to Russia and is final in 2012. They pay $6,000 in 2011 and $7,500 in 2012 of qualified adoption expenses, and have AGI...
-
Matthew borrows $250,000 to invest in bonds. During 2012, his interest on the loan is $30,000. Matthew's interest income from the bonds is $10,000. This is Matthew's only investment income. a....
-
What is the adverse selection problem?
-
Automobile insurance companies charge lower rates to married individuals than they do to unmarried individuals. What economic reason is there for such a practice? Is it fair?
-
An advanced degree is required to teach at most colleges. In what sense is this a form of restricting entry through licensure?
Study smarter with the SolutionInn App