Database Homework Assignment

Due Date:	Monday July 9
Printouts:	One record of the database using the default data-entry layout. First report (containing Desired Loans.) Second report (containing Telephone numbers.) Form letter showing placeholders. Form letter with data filled in. (Just one example.)
Evaluation:	15 points
Related Materials:	The Macintosh Bible Guide to Claris Works 4.0 - Rubin. Chapters 7 and 14 of this book are on reserve at LSM and Alexander libraries. (The Instructor's name is listed as Kathleen Goelz. The course is Intro to Computers, 198:110.) The materials are also available online at this link. Remember to use the HELP features built into Appleworks as you try to learn the software. You may also want to search for tutorials and help files on the web to get info on a particular subject - such as typing "appleworks summary field" into google. Items I quickly found included, online videos, and this series of appleworks lessons. Also, here was two different webpages with some simple help on summary fields: Help1 and Help2. (I found these and other webpages very quickly. Don't assume the ones I picked are the best ones out there. You should search for yourself.)

Preparation:

It is important that you read about and understand the database component of Appleworks before attempting to do this assignment. Some commands of the database software are less intuitive than features you used while doing spreadsheets and graphics. There are a number of key operations to concentrate on. You will need to know how to create, delete and modify fields. You will need to know how to make special types of fields - including fields with default values, calculation fields, summary fields and fields allowing only a certain list of values. You will need to know how to design a layout - including making a new layout, moving the fields around, making room for the data, picking the format to be used to display information in the field and adding graphics to the form. You need to know how to create new records. It is important to be familiar with entering the data, editing the data, deleting incorrect data and copying data from one place to another. You will need to know how to browse the database in different layouts. You will have to be able to create a report layout and select which fields will appear on that report. You need to know how to sort the information in the database so that it appears in different orders. Also, you'll need to know how to find particular records that meet a certain criteria and how to generate subsets of the database; you will want to be aware of the difference between visible, all, omit and new request. Finally, you will need to know how to create placeholders and use mail merge to include data from the database into word processing documents.

You can learn how to do by all of these activities by using the "related materials" itemized at the top of this assignment - including the chapters on reserve in the library, the built-in help within Appleworks and the info you can find on the web using google (or some other search engine.) The section below on "Things to Learn" can be used to help guide your experimentation. As usual, the wisest thing to do is try all of these activities on a practice file before doing work on the real assignment. (Let me put it this way: of all the assignments, this is the one where the experimentation phase is most important. You should spend at least an hour on it, and try all the things listed in the "Things to Learn" section. Students in previous summers who did not do this, spent many many more hours, often very frustrated, as they tried to complete the actual assignment.)

The Assignment:

For this assignment you will be creating a database containing the names of folks who you want to convince to lend you money so you can buy the item you selected to purchase on the Internet. You will be designing a data entry form, learning how to sort the data, creating and printing reports and finally, writing up a form letter that automatically gets filled in using information contained within the database.

The first step is to create the fields that will be needed within the database and to set their type. Here they are:

1. "First name"
2. "Last Name"
3. "Nickname"
4. "Street Address"
5. "State" with a default value of "NJ" which will be filled in for people you do not enter another state for. (Use OPTIONS... when you define the field.)
6. "Zip code"
7. "Telephone Number"
8. "Age"
9. "Gender" which should be a value list containing only "Male" and "Female" for choices. (Value List)
10. "Favorite Memory" an event you associate with that person such as "that time we went fishing" - which may be a very useful field for your form letter (read ahead).
11. "Yearly Salary"
12. "Weekly Salary" which should be automatically calculated as Yearly Salary divided by 52.
13. "Desired Loan" which should be a number indicating how much money you hope this individual will lend you.

The second step is to design your data entry layout. (Go to Layout mode.) You will want to include all the fields on this form. Move the fields around and make an appropriate amount of space within each field so that it easy for the person doing the data entry to use the form. Add borders or other graphics and extra text or directions as necessary. Set a tab order so that the computer jumps from one field to the next correctly when you hit tabs as you are entering data. Your goal is to make the data entry layout form look nice and be easy to use.

The third step is to enter your data. Enter information for 20 to 30 people. (They can be real friends and family or imaginary people.) Remember to use the tab key to go from one field to the next and to use the New Record command (or its shortcut) to start entering the information for each new individual. Make sure that you have people who live in NJ and others who do not. Make sure you have both males and females. Make sure you have some people over 50 years old and others who are younger. Make sure you have some people who make over $75,000 a year and others who do not. Make sure there are a number of cases where one person makes the exact same salary as another. Your first printout should be of the first record you have typed in using the data-entry layout. (Only print one record, not all of them; this is an option when you print.)

The fourth step is to create and print a report (2nd printout) by designing a new report layout, selecting the records you need to print and sorting the database appropriately. (Remember, use the "columnar report" layout option for this.) This report should consist of the First and Last name, the weekly salary and the Desired Loan of all the people who live in NJ. Give this report an appropriate title. The report should be sorted from the person with the highest weekly salary down to the person with the smallest. If there are ties, they should be resolved by sorting by last name in alphabetical order (A-Z). You also want the computer to total up the desired loans from all these people and place this figure at the end of the report. (See "Things to Learn" below for hints on how to get this sum on the report.)

Your fifth step is to make and print another report (3rd printout). This should have the last name, first name and telephone number of certain individuals. Again, pick a good title. This report should be sorted alphabetically by last name, with ties resolved by first name. The only names that should appear on this report are people who live outside of NJ and either earn over $75,000 OR are over 50 years old. [If you are having a hard time reading that - note, it means that not one person listed on this report should be a resident of NJ.]

Your sixth and final step is to write a short form letter. Your will first print the letter (4th printout) showing it with the unfilled mail merge placeholders and a second time (5th printout) showing the data filled in to create one actual form letter. Get the computer to print form letters for all the people who live in NJ and make more than $75,000 a year. (That is, do a Find in the DB before doing the actual Merge.) If more than one form letter is ultimately printed, turn in only the first one with your assignment. What should the letter say? You are trying to convince the people reading this letter to lend you money. You can talk about why you want money and the reasons for picking the product that you did. You want to personalize each letter. Therefore, this form letter should include information from the database. You want to include at least six of the fields as placeholders in your letter. (You can pick any six, but Last name, First Name, State, Nickname, Favorite Memory and Desired Loan can probably be worked into the letter with very little imagination on your part.)

Here is what you need to know about Mail Merge. While you are working on your database in Appleworks, you can also start a new word processing document at the same time by using the New command off the File menu. When you use the necessary Mail Merge command from within the Word Processing component of AppleWorks, remember to specify your database (using the name and location you have saved it at) when asked which one you want to use. After this, picking a field and using the Insert button will get it into your letter. (You will be shown the placeholders, not the actual info as you work on your letter.) Realize that the regular Print command will result in the placeholders being printed (which is what you want for your fourth printout). You use the Merge button on the Mail Merge dialog box when you are ready to generate the personalized form letters (your fifth printout).

In order to complete this assignment there is much you will have to learn. Here are some things to investigate:

1. How do you create fields for the database?
2. How do you set the type of the fields?
3. How do you make a field have a default value? (A "default" is a value that automatically gets filled in, if you do not enter something else.)
4. How do you set up a selection of choices to be used when filling in the field? (That is a "value list.")
5. How do you create a calculation field? (That is, one that is filled based on doing a computation using values in other fields.) As an experiment, set up a field for "Salary" and then a calculation field called "Taxes" that is 25% of what is in Salary.
6. After the initial round of creating fields, how can you later go back and add more fields?
7. How can you edit the name of a field? How can you change the type of a field after it has already been set?
8. How do you switch around to the different modes: browse, find, layout and list? What is the purpose of each one of these modes? What mode do you use when you create fields? What mode do you use when you are trying to search for a particular record?
9. How do you create a new layout?
10. How do you delete fields so they are no longer on a particular layout?
11. How do you make more room within a layout. (Stretch out the body.)
12. How do you add a header or footer part to a layout? (Insert Part)
13. How do you move field names and the actual field around within a layout?
14. How do you increase the amount of text space you have to type into for the various fields on a layout?
15. What can you do to add graphics or desired text to a particular layout? (You will probably want to bring up the Show Accents box off of the Windows menu. You can use this to set colors, patterns etc. Note, you can get to Patterns using the second tab on this box. Remember the top leftmost pattern is "clear" which will allow you to draw only the border of an object.)
16. How do you look at the database by switching to different layouts?
17. How do you create a new record of information?
18. How do you switch back and forth from seeing one record on the screen at a time, to seeing multiple records on the screen all at once?
19. How many different ways are there to move around to the different fields in a record? (Clicking mouse, hitting tab key.)
20. How do you change the order that will be followed when you hit the Tab key to jump from one field to the next? (Tab Order.)
21. How do you go back to old records to see or modify them?
22. How do you change the information entered in a field?
23. How do you copy information from one record to another?
24. How do you clear out the information in a field?
25. How do you get rid of a whole record?
26. How do you sort the information in a database?
27. Can you sort in alphabetical order? Numerical order?
28. What is the difference between ascending and descending order when you sort?
29. How do you set your primary field when sorting? How do you set your secondary field? (Sort Order)What purpose does the secondary field serve? How many fields can you specify when sorting?
30. How do you make a simple find request to look up a certain record? (For example, if you have a field called "Name" - can you find the person called "John.")
31. How can you make a find request based on numerical relationships? (For example, maybe you have a field named Age and desire to look up all the people older than 50 years old.)
32. How can you make a find request to handle more complex queries such as finding someone named "John" who is older than 25 in one request.
33. How can you make a request to find everything but what you enter. (Such as, find all the records where the person is not named "John".) Hint: use the omit button.
34. How can you make a find request to deal with the logical concept known as an "And". (Such as, find all the people who are older than 25 AND less than 50 years old.) Hint: use the Visible button.
35. How can you write a find request to deal with the concept of a logical "OR". (Such as, find all the people named either "John" OR "George".) Hint: use the New Request command off the menu.
36. Can you make more complicated requests that use more than one of these ideas at the same time. (For example, find any people not named "John" or "George"; how about finding all the people named "John" who are over 25 but less than 50 years old; can you make a request to find anyone named "John" or "George" who is over 25 years old?) This will be one of the trickier parts of this assignment.
37. Can you create a new layout for a columnar report? How do you pick only certain fields to appear on the report? How do you order the fields? How can you make more space for each of the columns if you need it?
38. How can you put a title on a report? Hint: you can stretch the Header Part on the layout and add text within the extra room.
39. How can you get all the numbers in one column of a report to be added up? (For example, maybe you have a database containing names and weights of a number of people. You want to see their total weight on a report to decide if they can all get on an elevator at the same time.) This is the second tricky part of this assignment. You will want to investigate this carefully. Use the Rubin chapter or search on the web to research this. (Typing "appleworks summary field" in google found these nice pages: Help1 and Help2 .) (Hint: It involves defining a summary field, using the SUM function as the equation for the field, placing the correct field name you want to add up as an argument to SUM, inserting a Trailing Grand Summary Part into your report layout and inserting the new summary field you created within this extra layout part. Note: when in Browse mode the Grand Summary doesn't show on the screen but it will print out when you print using the layout it is on.)
40. Do you remember how to do word processing? Can you figure out the word processing component of Appleworks? Try using it to type a short letter.
41. Can you use the mail merge feature to include placeholders (for database fields) into your document to create what is known as a "form letter"? (Hint: After specifying your database when first doing a mail merge, the Insert button on the small mail merge window will insert placeholders.)
42. Can you use the merge button on the mail merge dialog box to print out form letters after you have created an appropriate subset of the database?

Have you done all the following?

• Are there 13 fields on your data entry layout?
• Have you set the default value for State to "NJ"?
• Is Weekly Salary a calculation field based on Yearly Salary?
• Was the Gender field set up to be a value list of only the two values "Male" and "Female"?
• Have you designed the data entry layout to make it presentable and functional?
• Are there 20 to 30 individuals entered in your database?
• Does your first printout show the data entry layout filled in with your first record of information?
• Do you have some people who live in NJ and others who do not?
• Do you have a mixture of males and females?
• Do you have some people over 50 years old and others younger?
• Do you have people earning more than $75000 a year and others making less?
• Do you have a number of cases of people earning the exact same salary?
• Did you create a new report layout for the Desired Loan (DL) report?
• Are the only fields on this report First Name, Last Name, Weekly Salary and Desired Loan?
• Does the first report have a good title?
• Is the DL report sorted from person earning the most to person making the least? Are the ties in salary resolved by sorting in alphabetic order based on last name?
• Are the only people listed on the DL report residents of NJ?
• Were you able to have the computer total up the Desired Loans from the people listed on this report and display this value at the bottom of the report?
• Is your second printout the DL report?
• Did you create a second report listing Telephone numbers?
• Are the only fields on this Last Name, First name and Telephone number?
• Have you placed a title on this report?
• Is this sorted by last name with ties resolved by first name?
• Are the individuals listed on the Telephone report all residents of a state besides NJ?
• Are the individuals listed on the Telephone report all either over 50 years old, or making over $75000?
• Is your third printout of this Telephone report?
• Have you created a form letter which contains at least six of the fields from the database as placeholders?
• Is your fourth printout of this form letter, showing the placeholders instead of data?
• Did you only generate Form letters for NJ residents making more than $75000?
• Does your fifth printout show the result of generating one filled-in form letter using the merge button of the mail merge dialog box? (Please only turn in one such letter.)