ITEC 340 - Database I

Homeworks 5

Spring 2005

Families

FamilyID	EnvelopeNo	Address	Zip
1	123	1 Main Street	24142
2	100	12 Franklin Ave	24072
3	102	100 North Tower Ave	24142
4	104	11 MainStreet	24072

Members

LastName	FirstName	FamilyID	Birthday
Jones	Laura	1	01/01/1977
Jones	Fred	1	04/15/1975
Richards	Tom	2	10/12/1982
Smith	Sara	3	11/29/1981
Smith	Frank	3	11/22/1981
Thomas	Katie	3	03/20/1997
Baker	Tom	4	04/04/1978
Baker	Connie	4	09/14/1978
Baker	Tom	4	05/05/2000

ZipCodes

Zip	City	State
24142	Radford	VA
24072	Christiansburg	VA
24060	Blacksburg	VA
24061	Blacksburg	VA
24062	Blacksburg	VA

Donations

DonationID	FamilyID	GiftDate	CampaignID	Amount
1231	4	02-JAN-2005	GT-2005	100.00
1232	4	02-JAN-2005	NBF	100.00
1233	4	02-JAN-2005	HS-2005	100.00
1234	3	02-JAN-2005	GT-2005	25.00
1235	3	02-JAN-2005	FB-2005	25.00
1236	2	02- JAN-2005	GT-2005	50.00
1237	2	02- JAN-2005	NBF	15.00
1238	2	02- JAN-2005	FB-2005	25.00
1239	1	09- JAN-2005	GT-2005	25.00
1240		09- JAN-2005	GT-2005	100.00
1241	2	06-FEB-2005	GT-2005	50.00
1242	2	06-FEB-2005	NBF	15.00
1243	2	06-FEB-2005	HS-2005	10.00
1244	3	06-FEB-2005	FB-2005	25.00

Campaigns

CampaignID	Description	StartDate	EndDate	PledgedCampaign
GT-2005	General Treasury for 2005 year	01-JAN-2005	12-DEC-2005	Yes
FB-2005	Food Bank for 2005 year	01-JAN-2005	12-DEC-2005	No
HS-2005	Homeless Shelter for 2005 year	01-JAN-2005	12-DEC-2005	No
NBF	New Building Fund (5 year campaign)	01-JAN-2005	12-DEC-2009	Yes

Pledges

FamilyID	CampaignID	Amount
4	GT-2005	1200.00
4	NBF	6000.00
2	GT-2005	600.00
2	NBF	900.00
1	NBF	300.00

Additional Information:

1. Some Campaigns have pledge drives and some do not as shown by the PledgedCampaign attribute. This attribute should be limited to YES or NO values.
2. Some members do not pledge but still donate.
3. For Now, gift dates should be limited to a date between 01/01/2005 and 12/31/2010.
4. Birthdays should be limited to a date between 01/01/1890 and 12/31/2010.
5. Campaign Start and End dates should be limited to a date between 01/01/2005 and 12/31/2020.
6. The Description field of the campaign table is required.
7. A donation amount cannot be zero or negative.
8. No two families can have the same Family ID or envelope number.
9. Some donations, such as donation 1240 are anonymous.

Required for Homework 5:

Write an sql script named makeChurch.sql which:

• Implements the tables shown above.
• Constrains the tables to the extent possible given the additional information above and given the obvious Primary and Foreign Key constraints that would be needed.
• Populates the tables with the above data plus additional data of your construction representing at least two additional families with some family members, at least one additional campaign, at least two additional pledges, and at least five additional donations.
• Verify your constraints are working.

Suggestion: You may want to sketch an ERD to help you with FK identification.

Place your .SQL scripts in an RU05 folder in your ITEC340 students submissions folder on Neelix by 5:00 pm on Wednesday, March 2nd.