Alumni office: The alumni office of Enormous State University (ESU) wants to keep track of alumni and their charitable gifts to the university. For each alumnus, you want to store a first name, middle name, last name, address information (street address, city, state, and zip code), a home phone number, a mobile phone number, and an e‐mail address. Assume that you do have access to a master zip code table of zip codes, cities, and states, which you will include in your database design. Each alumnus may have multiple degrees. Every time a degree is granted, you want to keep track of to whom the degree was awarded, the year the degree was awarded, the type of degree (BA, MBA, PhD, JD, etc.), which school awarded the degree, and the student’s major. The database should also contain a table listing all the university’s schools (of which there are dozens), including a short name (up to 40 characters) and a full name (up to 100 characters). Each alumnus may make multiple charitable gifts to the university. For each gift, you want to keep track of the date it was made, who made it, and the dollar amount of the gift.
1. Design a database for this situation, creating a database outline and an ER diagram.
2. Use Access to create a database with the structure you have designed, creating all the necessary tables and relationships between them.
3. Use the form wizard to create a form for viewing alumni and their gifts. The header should be “Alumni Information with Gifts,” and every field of the main table for alumni should be shown. The city and state corresponding to the zip code should also be shown, but should be locked to prevent accidental corruption of the zip code table. There should also be a scrolling list of all the alumnus’ gifts, showing the date and amount given.
4. Create a similar form for alumni, but with the scrolling subform instead showing all the alumnus’ degrees, with the short name of the school, the type of degree, the date, and the major. Within the subform, use a combo box for the short name of school field, so that new information can be entered easily. Hint: When using the form wizard, make sure you include the primary key field for schools, and then replace the resulting field in the subform with a combo box that brings up the school’s short name.
5. Use the form wizard to create a form for viewing schools. All attributes associated with a school should be shown, along with a scrolling subform showing, for each degree award, the first name and last name of the recipient, the degree type, the date, and major.
