Judy, who is new to town, has decided to join the local chapter of a volunteer club that undertakes several charitable events and outreach programs. It has approximately 150 current members. The club would like to build(create) a database that could be used to maintain a membership directory and print mailing labels. The membership directory should include each member's membership number, first name, last name, street address, city, state, zipcode, phone, fax, e-mail address, and whether the member receives the newsletter via regular mail or e-mail.

The club has several committees, each of which undertakes between three and seven projects per year. All club members must join one, and only one, committee. Each committee has a name and specific mission and works on several projects during the year. These projects are given names, take place in specific locations, and have a begin date as well as an end date. There is also a short description for each project.

This is the logical data model for the volunteer club database.

1. Create the database designed in answer to question 1. Create the table in SQL Server  and copy and paste the table creation script here.

 

 

1. Populate the database created in answer to question 2. Populate the tables in SQL Server and copy and paste the table insertion script here. Insert at least THREE rows of data in each table. Your data should include the following: (1) One or more projects beginning and ending in July 2022; (2) At least one member who receives the newsletter by mail; (3) At least one member who has an email address and one member who does not have an email address.

 

1. Write the queries that will allow the club to:
   1. List the members who receive the newsletter by mail

 

1. List alphabetically (by last name) the first name, last name, and email address for members with email addresses.

 

1. List the names and phone numbers of all club members by committee membership

 

1. List the number of projects that each committee undertook in 2022.

 

1. Find all the committee missions for committees that had a project beginning between July 1^st and July 31^st, 2022.

Committee Club Member comm_code comm_name comm_mission mem_number mem_city mem_state mem_firstName mem_lastName mem_address has / belongs to mem_zipcode mem_phone mem_fax mem_email carries out/ carried out by mem_newsletterDeliveryMethod comm_code (FK) Project proj_code proj_anme proj_location proj_beginDate proj_endDate proj_descripton comm_code (FK)