IT 450 Monmouth University Week 3 Redwood Zoo Database Project use the same things you did for me previously work on the week 3 portion.this shouldnt be ha

IT 450 Monmouth University Week 3 Redwood Zoo Database Project use the same things you did for me previously work on the week 3 portion.this shouldnt be hard, you have to use access. Build some tables and queries. Prof. J. Kretsch
IT450 Team Database Project
May 26, 2020
Week2
The Redwood Zoo is going through a major fund drive and needs a database to track information. The Zoo is
fortunate to have a loyal support network, which means many of their donors pledge relatively often.
Recently, the Zoo has decided to allow donors to earmark which specific fund a pledge can be used for, and
donors are excited about the idea of being able to pledge multiple times to support their favorite areas of the
zoo.
The individuals who donate to the Zoo are referred to as Donors. Redwood Zoo previously organized Donor
information by donor name, address, phone, and email, but have been advised that retaining information
based on the donor name may not reflect accurately individuals with the same name. They have specifically
requested that each donor also be assigned a unique ID by the database.
The donations made by donors are earmarked for a specific Fund. For each Fund, the Zoo wants to record a
unique FundCode, the name of the Fund, and the funding goal or amount of financial support they hope to
realize for each particular fund.
Q: What kind of relationship is there between Donors and Funds?
A Part1: There is a Many-to-Many relationship between Donors and Funds (one Donor can donate to
many funds, and a fund can have money donated by many Donors).
A Part2: that means an associative or junction table is necessary to bridge Donors and Funds.
The amount of money promised to Redwood Zoo by each Donor is referred to as a Pledge. In addition to
identifying the Donor who made the pledge, the retained information must also include the Fund that was
specified by the Donor, the date the pledge was made, the amount pledged, and the payment method (either
Cash, Credit or Check).
Redwood Zoo’s database advisor also suggested that one or more lookup tables might be helpful to allow for
easy use of a dropdown for data entry.
FundCode
FundName
B03
Birds of the Tropics
B11
Bear Central
G05
Gorillas in Our Midst
M04
Monkey Business
M23
Manatee Exhibit
P11
Penguins on Parade
P15
Polar Bear Park
S09
Seal Show
W07
Walrus Exhibit
W13
Whale Watchers
GoalAmt
$5,000.00
$7,500.00
$8,500.00
$6,000.00
$5,000.00
$3,500.00
$10,000.00
$4,500.00
$2,500.00
$12,000.00
The adjacent table shows you Redwood Zoo’s Fund Codes and
Fund Names, along with the Goals established for each fund.
Page 1 of 4
Prof. J. Kretsch
IT450 Team Database Project
I.
May 26, 2020
ERD
Given the information described above, develop an ERD that shows the entities, fields, keys and
relationships. Your ERD (created in LucidChart) should also show the datatypes and field sizes, since that will
allow you to ensure that the tables and fields you build in Access will be consistent with the design.
As suggested above, there are several fields that might benefit from using Lookup fields so that the user can
select from a dropdown list of options rather than remember what exactly to enter. In order to retain dtabase
integrity it is essential for information entered into the database be accurate. Information would not be
accurate if Donors typed in their own version of a FundCode (e.g., “I love Penguins!”), or entered
“Mastercard” for payment method when the Redwood Zoo specified only the choices of Cash, Check Credit.
Week3
II.
ACCESS
Build the database in Microsoft Access following the above rules and information. Ensure that data will display
in its proper form (currency, for example), and that fields are sorted in a way that makes sense. Establish oneto-many relationships (with referential integrity) between your tables. If you realize there is a M:M
relationship between two tables, remember that you handle that with an associative table.
III. DATA
Partial sample data for Funds and names of Donors can be download and edited before importing into your
database. The files Donors.xlsx and Funds.xlsx files are in eCampus/Content under the Redwood category.
You’ll want to first edit these files to add the additional fields to correspond with the fields you specified in
your design of the Donors table and make any necessary changes to the Pledges data to also support your
design (for example, Funds.xlsx assumes your design includes a FundCode and FundName). Make these
updates before importing the data, and double check it once the import is done.
IV.
FORMS
Forms are used to enter and update data in tables. For every table in your database, you should have a form.
Even if you don’t expect a user to enter new data into a form, there is the chance they will need to edit data.
a. Although data will be initially entered into the Funds and Donors tables through the import activity
specified above, you should NOT enter data into any other table(s) except through the forms you
create. That is your single best way to make sure the forms function correctly.
b. All field names and content should display fully but should not be unnecessarily large.
c. Always verify that form fields are consistently sized and aligned (you don’t want a form to look
“ragged” – consistent sizing and alignment will help with that).
d. Forms should be no wider than they need to be.
e. Always include navigation buttons to forms e.g., “Next Customer”, “Previous Customer”. If users will
frequently add Donors to the database, for example, provide a “Add Donor” button.
f. Feel free to improve the formatting of the forms you create, keeping in mind they should all be
consistent.
Page 2 of 4
Prof. J. Kretsch
IT450 Team Database Project
May 26, 2020
1. I suggest you use the Form Wizard to create a form to add new Donors and add or modify Donor
information. Use “frm1Donors” as the form name.
a. Create a professional/meaningful, formatted title in the form header.
b. Resize fields and format as appropriate.
c. Add form navigation and any other buttons.
d. For any fields on the form that a user cannot change (such as a primary key that was created with
AutoNumber) modify that field’s properties so that it is not a tab stop.
e. Save the form (just in case).
f. Add a combo box that will allow the user to determine if a Donor is already in the database.
i. The label for the combo box should instruct them to look for the donor, and if the Donor is
not already in the database as a Donor to select the “Add New Donor” button.
g. Using this form, step through each existing donor and add any missing data.
h. Finally, add yourself as a donor.
2. Repeat the above to create the Funds form (add or modify information about funds). Name it frm2Funds.
a. The only change from above is your combo box will locate specific Funds. A recommended label for
the combo box is “Click to locate the desired Fund:”.
b. What happens if The Redwood Zoo opens a new section and wants to add a new fund with
associated goal? You need to provide a button that will allow the user to do that; of course they
also need buttons to navigate through the existing funds.
3. Finally, you need a form to record Pledges. Name this frm3Pledges.
a. This form will take more work on your part. You MUST be able to enter all pledges into the
database through this form, WITHOUT leaving the form to reference tables to find Donors or
Funds. I’ll suggest that doing this usually requires you to remove relationships connected to the
Pledges table and to edit the table to include lookup fields.
b. Follow the guidelines from frm1Donors for this form as well, although it does not require a combo
box.
i. For the situation where a person making a donation does not yet exist in the Donor table,
my base requirement is that your database Dashboard (you’ll create it next week) will
include instructions stating that any new donors should first be added to the Donor table
before proceeding to record their pledge.
ii. However, note that the above is a work-around. In reality, the Pledge form should allow a
user to look up whether a Donor exists, and if they do not the form should allow for adding
the new donor before proceeding to find the Donor in the donor table (through the Donor
lookup) and then adding their pledge.
iii. If you would like to look into how to implement this and include it in your form, I’ll award
extra credit.
c. Use your form to enter in pledge data. We have 27 donors in the database (26 imported, plus you).
For this week, enter in:
i. Pledge data for at least 15 donors, making sure all 10 funds have been pledged to.
ii. At least 6 of these donors should have donations recorded from both 2019 and 2020.
iii. Add 3 additional donors (bringing the total to 18 donors) whose last donation was in 2018.
Page 3 of 4
Prof. J. Kretsch
IT450 Team Database Project
May 26, 2020
QUERIES (5)
For all queries ensure that field names and content fully display to avoid losing points. All tables in the upper
area of the grid should show all fields and be in correct order according to their relationships.
4. Create a query in design view named 1PledgedOrWhaleWatchers. It should display pledge information
(date, pledge amount, Donor name in one column – combo of first and last name, and name of the Fund)
for donors who pledged less than $150 OR who donated to the WhaleWatchers fund. Verify that your data
will allow some results, and adjust your data if necessary. Sort in ascending order by the name of the Fund.
5. Create a query in design view named 2PledgeCounts. This query should include the FundName (sort in
ascending order) and TotalPledged, but use totals to aggregate the results and display the number of
pledges for each fund. A Fundname should only display once.
6. Create a new query in design view that is named 3PledgesTowardGoals2020. It should display, from the
table that records pledges, each Fund and the total amount pledged to that fund in 2020, as well as the
pledge date. Use an aggregate to sum up the total pledged and criteria to limit to only pledges in 2020.
Sort in ascending order on the FundCode and on the pledge date. Save the query and Close.
7. Create a query in design view named 4PledgesRequest. This query should include the DonorID, first and
last names (don’t display the DonorID), how much they pledged, date of pledge, and the Fund(s) they
pledged to (whichever field you have that displays the name of the fund). Display in descending order by
pledge amount. Set the criteria to display only pledges for the current year 2020. Add a calculated field
named “Request” that displays the results of all donor pledges increased by 20% (this is the amount we’ll
ask these donors to pledge next year). Verify that the pledge amount and Request field display as
currency.
8. Create a new query named 5FundParameter. This is a parameter query to display all fields related to
pledges. Your parameter query should ask users to enter all or part of the fund name, and your query
should include wildcards that will allow the user to either enter any part of the fund name or hit return to
see results for all funds. Test for both options. The user prompt should read Enter Fund Name or hit
Return to show pledges for all funds:
That’s it for this week. I’m extended the due date for this til Monday night, instead of Sunday night.
Next week you will create reports and a special navigation form (what we’ll call the Dashboard) that guides
users to choose what forms, queries and reports they can run.
Questions about the instructions should come through GroupMe so you can all respond with your thoughts
and my response can be seen by all.
Thanks!
Page 4 of 4

Purchase answer to see full
attachment