Donor
Database:
I want you to set up a donor system for a charity. The information you need in your database is:
|
Element |
Occurrence |
|
Donor Identification Number |
1 |
|
Donor Name |
1 |
|
Donor Street Address |
1 |
|
Donor City |
1 |
|
Donor State |
1 |
|
Donor ZIP |
1 |
|
Donation Information |
1 or many |
|
|
|
|
|
|
|
|
|
|
When we set up a table, the rules for designing a database say that all of the fields in the table must relate directly to the primary key and that you cannot have repeating groups. That means that the data above is going to have to be divided into multiple tables. Remember, a primary key uniquely defines a record in a file or table.
First I am going to set up a Donor Table which sets up the information about the donor.
|
Donor Table |
|
Donor Identification Number - PK |
|
Donor Name |
|
Donor Address |
|
Donor City |
|
Donor State |
|
Donor ZIP |
Now I need to look at the information about the donation. Note that donations are made to drives and my charity runs multiple donation drives.
Next, I am going to set up the Donation Table that will hold the donations given to my charity.
|
Donation
Table |
|
Donor Identification Number - PK |
|
Drive Number - PK |
|
Date of Donation - PK |
|
Amount of Donation |
The amount of donation is an appropriate piece of data to carry on this table because it relates to all three parts of the primary key. The amount of donation is the amount that a particular donor made to a particular drive number of a particular date. The key is unique. The only potential problem is a donor making multiple donations to the drive on the same day. This could be solved by have the date of donation include the time - then it would be unique. It could also be solved by adding a field that acts like a line number - first donation by a donor to a drive on a particular date is given a line number of 1 and the second is given a line number of 2. This field is part of the key. I am going to solve the problem by saying in the rare instance when this happens, I will hold the second donation and enter it the next day.
I still have information about the drive to store. I could not put the name of the drive and the drive chair and the drive goal on the Donation Table because that information relates to the Drive Number and only the Drive Number. It does not relate to the entire primary key.
The next table I am setting up is the Drive Table:
|
Drive Table |
|
Drive Number - PK |
|
Drive Name |
|
Drive Chairperson |
|
Drive Goal |
Your assignment is to set up these tables in Access. You must then populate the tables. Before you populate the tables, look at the queries you must do and choose data that will retrieve one or more of the records with each query. Do the five queries shown below. I want at least two of these done using the query design and at least two done using SQL (tell me which is which).